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:
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', 'firstname.lastname@example.org');
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)
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:
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:
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.