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. ...
Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.