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 and rows, we name the columns we want and add a
WHERE
clause to the end of our
SELECT
statement:
SELECT rec_id, title, description FROM books WHERE genre = 'novel';
This SQL statement displays just the record 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 like this:
SELECT books.rec_id, title, pub_year, CONCAT(author_first, ' ', author_last) AS author FROM books, authors WHERE author_last = 'Vernon' AND author_id = authors.rec_id;
Both tables have columns called rec_id, so we need to specify the table to which we’re referring whenever we refer to rec_id and are joining both tables. ...
Get MySQL in a Nutshell 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.