Creating a Database with Perl and DBI (
Page 1 of 4 )
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 -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 the
CREATE DATABASE
command:
mysql> CREATE DATABASE musicians_db; Query OK, 1 row affected (0.01 sec)
The USE 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)
where
n
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 name
and
phone
are strings.
Note There are many different SQL data types and ways in which we can create keys. For all the
infor
mation on this subject, see the online documentation or the recommended textbook.