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 -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.


{mospagebreak title=The DESCRIBE Command}

The DESCRIBE command shows all the fields in the table and their types. This will show us if the musicians table was created correctly:

mysql> DESCRIBE musicians;

 Field

 Type

 Null

 Key

 Default

 Extra

 player_id

 int(11)

 

PRI

 0

 

 name

 char(50)

 YES

 

 NULL

 

 phone

 char(12)

 YES

 

 NULL

 

3 rows in set (0.00 sec)

This looks OK so far. Let’s create the other two tables: what_they_play and instruments :

mysql> CREATE what_they_play (
    ->   player_id INT,
   
->   
inst_id INT);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE instruments (
   
->  inst_id INT PRIMARY KEY,
   
->  instrument CHAR(40),
   
->  type CHAR(20),
   
->  difficulty INT);
Query OK, 0 rows affected (0.00 sec)

Creating a Non-root User with the GRANT Command

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:

mysql> GRANT SELECT, INSERT, UPDATE, DELETE
    ->   ON musicians_db.*
   
->  TO musicfan@localhost
   
->  IDENTIFIED BY "CrimsonKing";
Query OK, 0 rows affected (0.03 sec)

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> quit
Bye
$ mysql -u musicfan -p
Enter 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

INSERT INTO table_name (field1, field2, …) VALUES (value1, value2, …);

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:

mysql> INSERT INTO musicians (player_id, name, phone)
   -> VALUES (1, "Roger Waters", "555-1212");
Query OK, 1 row affected (0.01 sec)

The SELECT command can tell us if the row was inserted correctly (more on SELECT later).

mysql> SELECT * FROM musicians;
+———–+————–+———-+
| player_id | name         | phone    |
+———–+————–+———-+
|         1 | Roger Waters | 555-1212 |
+———–+————–+———-+
1 row in set (0.00 sec)

Let’s enter the other musicians:

mysql> INSERT INTO musicians (player_id, name, phone)
-> VALUES (2, "Geddy Lee", "555-2323");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO musicians (player_id, name, phone)
-> VALUES (3, "Marshall Mathers III", "555-3434");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO musicians (player_id, name, phone)
-> VALUES (4, "Thom Yorke", "555-4545");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO musicians (player_id, name, phone)
-> VALUES (5, "Lenny Kravitz", "555-5656");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO musicians (player_id, name, phone)
-> VALUES (6, "Mike Diamond", "555-6767");
Query OK, 1 row affected (0.00 sec)

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)

 

Excellent! Our musicians are entered. Now for the commands to enter data into the other two tables. Read along and follow the bouncing ball . . .

mysql> INSERT INTO what_they_play (player_id, inst_id)
   -> VALUES (1, 11), (1, 14), (2, 12), (2, 14), (3, 14),
   -> (4, 7), (4, 11), (4, 14), (5, 11), (5, 14), (6, 9);
Query OK, 11 rows affected (0.00 sec) Records: 11  Duplicates: 0 Warnings: 0 

mysql> SELECT * FROM what_they_play;
+————-+———-+
| player_id  | inst_id  |
+————-+———-+
|              1  |        11 |
|              1  |        14 |
|              2  |        12 |
|              2  |        14 |
|              3  |        14 |
|              4  |          7 |
|              4  |        11 |
|              4  |        14 |
|              5  |        11 |
|              5  |        14 |
|              6  |          9 |
+————-+———-+
11 rows in set (0.00 sec)

Notice that we used an alternative form of the INSERT command to insert multiple rows, in our case all the rows, at the same time.

mysql> INSERT INTO instruments
   
->  (inst_id, instrument, type, difficulty)
   
->  VALUES
   
->  (1, "bagpipes", "reed", 9),
   
->  (2, "oboe", "reed", 9),
   
->  (3, "violin", "string", 7),
   
->  (4, "harp", "string", 8),
   
->  (5, "trumpet", "brass", 5),
   
->  (6, "bugle", "brass", 6),
   
->  (7, "keyboards", "keys", 1),
   
->  (8, "timpani", "percussion", 4),
   
->  (9, "drums", "percussion", 0),
   
->  (10, "piccolo", "flute", 5),
   
->  (11, "guitar", "string", 4),
   
->  (12, "bass", "string", 3),
   
->  (13, "conductor", "for-show-only", 0),
   
->  (14, "vocals", "vocal", 5);
Query OK, 14 rows affected (0.00 sec) Records: 14  Duplicates: 0 Warnings: 0

mysql> SELECT * FROM instruments;

 inst_id

 instrument type

 difficulty

 1 bagpipes

 reed

 9

 2 oboe

 reed

 9

 3 violin

 string

 7

 4 harp

 string

 8

 5 trumpet

 brass

 5

 6 bugle

 brass

 6

 7 keyboards

 keys

 1

 8 timpani

 percussion

 4

 9 drums

 percussion

 0

 10 piccolo

 flute

 5

 11 guitar

 string

 4

 12 bass

 string

 3

 13 conductor

 for-show-only

 0

 14 vocals

 vocal

 5

14 rows in set (0.00 sec)

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.

mysql> SELECT name, phone FROM musicians;
+———————-+———-+
| name                 | phone    |
+———————-+———-+
| Roger Waters         | 555-1212 |
| Geddy Lee            | 555-2323 |
| Marshall Mathers III | 555-3434 |
| Thom Yorke           | 555-4545 |
| Lenny Kravitz        | 555-5656 |
| Mike Diamond         | 555-6767 |
+———————-+———-+
6 rows in set (0.00 sec)

Please check back next week for the continuation of this article.

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye