MySQL in a Nutshell, 2nd Edition

Selecting Data

Now that we have one row of data in each of our two tables, let’s run some queries. We’ll use the SELECT statement to select the data that we want. To get all of the columns and rows from the books table, enter the following:

SELECT * FROM books;

The asterisk, which acts as a wildcard, selects all columns. We did not specify any criteria by which specific rows are selected, so all rows are displayed from the books table. To select specific columns, we name the columns we want. To select specific rows, we add a WHERE clause to the end of the SELECT statement:

SELECT book_id, title, description 
FROM books
WHERE genre = 'novel';

This SQL statement displays just the book’s identification number, the book’s title, and the description of the book from the books table for all books where the genre column has a value of novel. The results will be more meaningful, of course, when we have data on more books in the database. So, let’s assume that we’ve entered data for a few dozen more books, and proceed.

If we want to get a list of novels from the database along with the author’s full name, we need to join the books table to the authors table. We can join the two tables with a JOIN clause like this:

SELECT book_id, title, pub_year, 
CONCAT(author_first, ' ', author_last) AS author
FROM books
JOIN authors USING(author_id)
WHERE author_last = 'Greene';

In the FROM clause, we join the books table to the authors table using the author_id columns in both tables. If the columns had different ...