Creating a Database with Perl and DBI

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 -p
Enter 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 theCREATE DATABASEcommand:

mysql> CREATE DATABASE musicians_db; Query OK, 1 row affected (0.01 sec)

The USE Command

Now that the database namedmusicians_dbis created, we need to tell MySQL that we want to work with it. That is done with theUSEcommand.

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 ismusicians. Recall that it has three fields:player_id, an integer that is the key;name, a character string; andphone, a character string.

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

CREATE TABLE table_name (field_definition, field_definition...)

The value oftable_name  is up to us—we are usingmusicians. 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. Thetype will be one of many different types that MySQL supports includingINT. Strings are specified asCHAR(n)wheren  is the number of characters in the string.

Here is the command to create our table of musicians:

mysql> CREATE TABLE musicians ( 
    ->  player_id INT PRIMARY KEY,
    ->  name CHAR(50),
    ->  phone CHAR(12));

The player_id field is an integer that will be the key into the table. Both nameandphoneare strings.

Note  There are many different SQL data types and ways in which we can create keys. For all the
information on this subject, see the online documentation or the recommended textbook.