Login

Creating a Database with Perl and DBI

In this second part of a four-part series on Perl and the DBI, you’ll learn how to create a database and how to use a collection of very important commands. This article is excerpted from chapter 15 of the book Beginning Perl by James Lee (Apress; ISBN: 159059391X).

Creating a Database

The first step in creating a database is to start the MySQL server and then issue a MySQL command to create the database—let’s call the database musicians_db,
since it will contain information about our favorite musicians. First, let’s log into the MySQL command line interface (CLI):

$ mysql -u root -pEnter password: RootDown

A few lines of information about the MySQL server will be printed, and then we will see the MySQL prompt:

mysql>

SQL CASE SENSITIVITY

Before we start working with the MySQL database, we should take a moment and talk about the case sensitivity rules for SQL commands. Unlike Perl, SQL commands are not normally case sensitive; however, the parts of the command that refer to what we programmers have created are. This may sound confusing, but it is quite simple, and best described with an example.

Later on in this chapter we will be working with a table named musicians. This table will be named by us–this is the name we have chosen, and it is a good name, since it has information about a number of musicians. One of the fields in this table will be name, another good label since it contains the name of the
musician for which we have information.

An SQL command to show the names in the table will look like this:

SELECT name FROM musicians;

The two uppercase pieces of this command are the SQL parts of the command. The lowercase pieces are the parts of the database that we have created. The SQL parts of the command are case insensitive, so the command could have been written as follows:

select name from musicians;

However, the parts that we have named are not case insensitive (that means they are case sensitive–double negatives are confusing!). So this command would not work:

SELECT NAME FROM MUSICIANS;

For clarity in this chapter, we will use all uppercase terms for the SQL parts of a command and all lowercase for the parts that we defined.

The CREATE DATABASE Command

The first step in working with a MySQL database is to create one. Creating a database is as simple as executing the
CREATE DATABASE
command:

Now that the database named
musicians_db
is created, we need to tell MySQL that we want to work with it. That is done with the
USE
command.

mysql> USE musicians_db;Database changed

The CREATE TABLE Command

Now that the database has been created and we have selected it as the one we are using, we need to create some tables. The first table to create is
musicians
. Recall that it has three fields:
player_id
, an integer that is the key;
name
, a character string; and
phone
, a character string.

The command to create a table is, not unsurprisingly,
CREATE TABLE
.4 The syntax for this command will resemble this:

CREATE TABLE table_name (field_definition, field_definition…)

The value of
table_name
is up to us—we are using
musicians
. The field definitions within the parentheses is a comma-separated list of information that follows this basic form:

field_name type

We get to choose the field names. The
type will be one of many different types that MySQL supports including
INT
. Strings are specified as
CHAR(n)
wheren
is the number of characters in the string.

It is important to create a non-root user to access the database—performing normal non-MySQL-admin activities using the root
user is a bad idea for security reasons. So let’s create a user that will be allowed to perform basic queries on the
musicians_db
database:

You can trust us when we say that this command creates a user named
musicfan
with a password “CrimsonKing”5 and grants this user permission to select, insert, update, and delete records from the database. Or, you can check out the documentation and read all about the
GRANT
command.

We are going to start inserting data into our
musicians_db
database, so we need to log out as the
root
user and log back into MySQL as the newly created
musicfan
user:

mysql> quitBye
$ mysql -u musicfan -pEnter password: CrimsonKing

mysql>

{mospagebreak title=The INSERT Command}

Now comes the time to insert data into the table. We will use the SQL command named INSERT. The basic syntax of this command is

We first tell MySQL into what table we are inserting a row of data. Then, within parentheses, we indicate which fields in the table will be given values. The second set of parentheses after the term
VALUES
contains a list of values that are plugged in memberwise into the fields indicated in the first set of parentheses.

Roger Waters is deserving of a row of data in our table, so let’s insert him as key 1, including his phone number:

Now that the three tables have been created and populated with data, we can talk about how we can pull information out of the database.

{mospagebreak title=The SELECT Command}

The SELECT command allows us to query the database, and it reports back to us the information that matches the criteria we specify from the table we indicate. We have seen several
SELECT
commands in this form:

mysql> SELECT * FROM musicians;

player_id

name

phone

1

Roger Waters

555-1212

2

Geddy Lee

555-2323

3

Marshall Mathers III

555-3434

4

Thom Yorke

555-4545

5

Lenny Kravitz

555-5656

6

Mike Diamond

555-6767

6 rows in set (0.00 sec)

This
SELECT
asks for
*
from the table named
musicians
. The
*
means “all fields” in the order that they are in the table. We can explicitly ask for the fields by listing them comma separated instead of using the star.

mysql> SELECT player_id, name, phone FROM musicians;

player_id

name

phone

1

Roger Waters

555-1212

2

Geddy Lee

555-2323

3

Marshall Mathers III

555-3434

4

Thom Yorke

555-4545

5

Lenny Kravitz

555-5656

6

Mike Diamond

555-6767

6 rows in set (0.01 sec)

The fields we select can be in any order.

mysql> SELECT name, phone, player_id FROM musicians;

name

phone

player_id

Roger Waters

555-1212

1

Geddy Lee

555-2323

2

Marshall Mathers III

555-3434

3

Thom Yorke

555-4545

4

Lenny Kravitz

555-5656

5

Mike Diamond

555-6767

6

6 rows in set (0.00 sec)

We can request specific fields—we don’t need to show all the fields available.