The INSERT Command - Perl

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;

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;

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.