Populating Tables with Data

Now that you've created your tables, the next step is to populate them with data. The sample data you are using is taken from the author's childhood memories (that, happily, extend from about 1973 to the present day).

Although there are a variety of ways to get data into MySQL tables, you'll use the SQL INSERT statement because it is the most common way to get data into a table. The INSERT statement creates a new row at the end of a table. The basic format of the command is

INSERT [INTO] [db_name.]table_name (list, of, columns, ...) VALUES (list, of, values, ...) 

For example, to enter information on a slightly good copy of the Canadian classic, Jacob Two-Two Meets the Hooded Fang by Mordecai Richler, you would use this query:

INSERT book (author, title, cond) VALUES ('Mordecai Richler',         'Jacob Two-Two Meets the Hooded Fang',         'good'); 

This INSERT query breaks down into these major parts:

• INSERTThe optional INTO modifier was omitted after the INSERT keyword in this case. In general, shorter commands are better, as long as the readability of the commands is not hindered. Although INSERT INTO is arguably clearer than INSERT, the INSERT command is so commonly used that it is fairly safe to assume that anyone reading the query will understand what you mean.

• book The name of the table in which to insert data immediately follows the INSERT (or INTO) keyword. If no database name is specified, the table name is assumed to be a part of the default database (as set with the USE statement). If no default database has been specified, you would have to specify the name of the database before the name of the tablelibrary.book, for example.

• (author, title, cond) After the name of the table, the list of columns into which the data will be inserted is specified. The list is enclosed in parentheses, and each column in the list is separated by commas. If a column is omitted from the list, the field will be populated by the default value for the column.

  The list of columns is optional. Rather than explicitly specifying the column names, you could specify values for each column in the order the column occurs within the table. This is a dangerous shortcut because it is easy to insert data into the wrong columns.

  Note that the book_id column was not specified in this list; this is intentional. The book_id column is an auto-increment column. When a new row is inserted into the table, as long as the INSERT statement that generates the row does not include the auto-increment column, a value will be automatically inserted into the column. The value generated is equal to the largest existing value in the auto-increment column plus one.

• VALUES (...)One or more sets of data to be inserted into the table can follow the VALUES clause. Each set of data must correspond to the columns defined earlier in the query. Each value in the list is separated by a comma. Text values are enclosed in quotes; numeric values are not.

If you're inserting multiple rows of data, place a comma after the closing parenthesis of each row (if it is the final row, use a semicolon). For example, to insert three more rows of data into the book table, run this command:

INSERT book (author, title, cond) VALUES ('Maurice Sendak',         'In the Night Kitchen',         'mint'),        ('Caroll Spinney',         'How to Be a Grouch',         'poor'),        ('Dr. Seuss', 'Green Eggs and Ham', 'good'); 

In addition to reducing the amount of typing that needs to be done, inserting multiple rows of data with a single SQL query has the advantage of efficiency: MySQL has to do less work than if it had to process three separate queries.

Now that you have entered books to loan, you can start loaning the books to people. Say that Carl wants to borrow In the Night Kitchen. First, you enter him into the person table using the following statement:

INSERT person (name, email)        VALUES ('Carl', '[e-mail]'); 

Then you need to create an entry in the loan table. To do this, you must know the primary keys for Carl and for the book he wants to borrow.

First, look up the primary key for Carl

SELECT person_id FROM person WHERE name = 'Carl'; 

You should get a result something like

+-----------+ | person_id | +-----------+ |         1 | +-----------+ 1 row in set (0.07 sec) 

Note

Because we know there are only a few records in the sample database and there is only one Carl, this query is fine for finding the primary key value. Of course, with larger databases you might have to perform a query using several fields to be sure that you have identified the correct record.

Next, you need to find the primary key for In the Night Kitchen:

SELECT book_id FROM book WHERE title = 'In the Night Kitchen'; 

You should get back a value of 2.

Now you can create an entry in the loan table using the two primary keys that you retrieved and a MySQL function:

INSERT loan (book_id, person_id, date_lent) VALUES (2, 1, '2005-07-25'); 

You'll have to use some of these primary keys several times. Rather than try to remember them, you can store them in user variables. A user variable is simply a name (in a special format) that correlates to a piece of data. The basic form of the user variable is @name. The name portion of a user variable can contain a to z (upper or lowercase), 0 to 9, $, ., and _. A variable can be used in any context where a numeric or string value would normally be used.

To assign a value to a user variable, use the := operator. For example:

SELECT @fish := 'Herring'; 

Running another SELECT on the user variable simply returns the value assigned:

SELECT @fish; 

Knowing about user variables, you can rewrite your queries to be easy to run and less error prone:

SELECT @person_id := person_id FROM person        WHERE name = 'Carl'; SELECT @book_id := book_id FROM book        WHERE title = 'In the Night Kitchen'; SELECT @date := '2005-07-25'; INSERT loan (book_id, person_id, date_lent) VALUES (@book_id, @person_id, @date); 

Notice how the variable names are used instead of the literal values. This allows you to use the same INSERT query over and over to populate the loan table. All you need to do is change the WHERE clauses as you add new loans.

You can further optimize this by combining all of your queries that retrieve data into one query:

SELECT @book_id := book_id,        @person_id := person_id,        @date := '2005-07-25'   FROM book, person  WHERE book.title = 'In the Night Kitchen'    AND person.name = 'Carl'; 

This type of SELECT is called a join. Joins allow a single SELECT statement to retrieve data from two or more tables. In this query, you simply combine your three separate queries into one query that joins two tables. More on joins (and subqueries) is found in Chapter 4, "Retrieving Data: Simple Queries."

Notice that the table name is included with the columnsin this case, book.title. Although MySQL is often able to sort out which columns go with which tables in many cases, a change to a table involved in this query could break the query if you aren't explicit.

Now you just run your INSERT query the same as last time:

INSERT loan (book_id, person_id, date_lent) VALUES (@book_id, @person_id, @date); 

For one final optimization, you can make another change to the queryyou can combine all of the separate queries into a single query that combines an INSERT and a SELECT statement. The syntax is quite simple. Replace the VALUES clause of an INSERT query with a SELECT statement that returns the right number of columns of data, as seen in the following code:

INSERT loan (book_id, person_id, date_lent) SELECT book_id, person_id, '2005-07-25'   FROM book, person  WHERE book.title = 'In the Night Kitchen'    AND person.name = 'Carl'; 

This allows you to eliminate the user variables and intermediate queries.

Why weren't you shown this query to begin with? Well, for novice DBMS users, the preceding query can be a lot to take in at one time. Understanding what separate queries compose the larger query makes it much easier to understand the larger query. If this query was too much for you, just remember a few things for the future:

• Most complex queries can become a set of small and simple queries.

• There is more than one way to do things.

Each of these techniques is useful on its own. If you are a novice user, start with small queries and build up from there. A broken simple query is much easier to fix than a broken complex query.