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 ...
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.