Creating a Database with Perl and DBI - The INSERT Command
(Page 3 of 4 )

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 termVALUEScontains 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)

TheSELECTcommand can tell us if the row was inserted correctly (more onSELECTlater).

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

Next: The SELECT Command >>
 

More Perl Articles
More By Apress Publishing

Comments On: Creating a Database with Perl and DBI

· This article is an excerpt from the book "Beginning Perl," published by Apress. We...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter 15 of the book Beginning Perl by James Lee (Apress; ISBN: 159059391X). Check it out today at your favorite bookstore. Buy this book now.