MySQL in a Nutshell

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 INSERT, we can add one or more records at a time. Before adding information on 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('Vernon','Olympia','USA');

After adding an entry for the author, we can insert an entry for the book:

INSERT INTO books
(title, author_id, isbn, genre, pub_year)
VALUES('Eden', LAST_INSERT_ID( ),'0802117287','novel','2003');

With the first SQL statement, we’ve added a record or row for Olympia Vernon, an author I love who wrote the book Eden. The standard INSERT syntax is to name 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. In the second SQL statement, we’ve listed the columns in an order that’s different from their order in the table. That’s acceptable to MySQL; we just have to be sure that our values are in the same order. We are getting the author_id number for the row just inserted in the previous statement by using the LAST_INSERT_ID() function.