MySQL in a Nutshell, 2nd Edition

Inserting Data

Now that we’ve set up our first two tables, let’s look at how we can add data to them. We’ll start with the simplest method: the INSERT statement. With the INSERT statement we can add one or more records. Before adding information about a book to our books table, because it refers to a field in our authors table, we need to add the author’s information to the latter. We’ll do this by entering these SQL statements through the mysql client:

INSERT INTO authors
(author_last, author_first, country)
VALUES('Greene','Graham','United Kingdom');

SELECT LAST_INSERT_ID( );

+-------------------+
| LAST_INSERT_ID( ) |
+-------------------+
|                 1 |
+-------------------+

INSERT INTO books
(title, author_id, isbn, genre, pub_year)
VALUES('The End of the Affair', 1,'0099478447','novel','1951');

Our first SQL statement added a record, or row, for Graham Greene, an author who wrote the book The End of the Affair. The standard INSERT syntax names the columns for which the values are to be inserted, as we’re doing here. If you’re going to enter values for all of the columns, you don’t need to name the columns, but you must list the data in the same order in which the columns are listed in the table.

In the second SQL statement, we retrieved the identification number assigned to the row we just entered for the author by using the LAST_INSERT_ID() function. We could just as easily have entered SELECT author_id FROM authors;.

In the third SQL statement, we added data for a Graham Greene book. ...