MySQL in a Nutshell [16]
(author_id INT AUTO_INCREMENT PRIMARY KEY,
author_last VARCHAR(50),
author_first VARCHAR(50),
country VARCHAR(50));
This table doesn’t require too many columns, although we might add other columns to it for an actual bookstore. As mentioned before, as needed, we’ll join the books table to the authors table through the author_id in both tables.
In the authors table, we’ve separated the first and last name of each author into two columns so that we can easily sort and search on the last name. We’ve also added a column for the author’s country of origin so that we can search for works by authors of a particular country when asked by customers. For production use, it might be better to use a country code and then have yet another reference table listing the full names of countries. But we’re trying to keep this tutorial simple and include detail only when it has educational value.
Show Me
Let’s take a moment to admire our work and see what we’ve done so far. To get a list of databases, use the SHOW DATABASES statement:
SHOW DATABASES;
+-----------+
| Database |
+-----------+
| bookstore |
| mysql |
| test |
+-----------+
The result of the SHOW DATABASES statement lists not only the database we’ve created, but also two others. One is the mysql database, which contains data about user privileges and was covered in Chapter 2. The third database is the test database, which is set up by default when MySQL is installed. It’s there as a convenience for you to be able to add tables or run SQL statements for testing.
To see a list of tables in the bookstore database, once we select the bookstore database with the USE statement shown earlier, we would enter the following statement:
SHOW TABLES;
+---------------------+
| Tables_in_bookstore |
+---------------------+
| authors |
| books |
+---------------------+
The result of the SHOW TABLES statement provides a list containing our two tables, just as we expected. If you want to see a list of tables from another database while still using the bookstore database, add a FROM clause to the previous statement:
SHOW TABLES FROM mysql;
This displays a list of tables from the mysql database, even though the default database for the client session is the bookstore database.
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. In that statement, we 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.
Selecting Data
Now that we have one row of data in each of our two tables, let’s