Home arrow Perl Programming arrow Page 3 - Creating a Database with Perl and DBI

The INSERT Command - Perl

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

TABLE OF CONTENTS:
  1. Creating a Database with Perl and DBI
  2. The DESCRIBE Command
  3. The INSERT Command
  4. The SELECT Command
By: Apress Publishing
Rating: starstarstarstarstar / 8
March 20, 2008

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More Perl Programming Articles          >>> More By Apress Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PERL PROGRAMMING ARTICLES

- Perl Turns 25
- Lists and Arguments in Perl
- Variables and Arguments in Perl
- Understanding Scope and Packages in Perl
- Arguments and Return Values in Perl
- Invoking Perl Subroutines and Functions
- Subroutines and Functions in Perl
- Perl Basics: Writing and Debugging Programs
- Structure and Statements in Perl
- First Steps in Perl
- Completing Regular Expression Basics
- Modifiers, Boundaries, and Regular Expressio...
- Quantifiers and Other Regular Expression Bas...
- Parsing and Regular Expression Basics
- Hash Functions

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: