MySQL in a Nutshell [17]
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 labels, we would have to use a different clause or method in the JOIN clause to join the tables (e.g., ON (author_id = writer_id)). Notice in the second line of this SQL statement that we’ve employed a string function, CONCAT( ). With this function you can take bits of data and merge them together with text to form more desirable-looking output. In this case, we’re taking the author’s first name and pasting a space (in quotes) onto the end of it, followed by the author’s last name. The results will appear in the output display as one column, which we’ve given a column heading of author. The keyword AS creates this column title with our chosen name, called an alias.
In the WHERE clause, we’ve specified that we want data on books written by authors with the last name Greene. If the books table did not contain books by Greene, nothing would be displayed. The results of the previous query are as follows:
+---------+-----------------------+----------+---------------+
| book_id | title | pub_year | author |
+---------+-----------------------+----------+---------------+
| 1 | The End of the Affair | 1951 | Graham Greene |
| 2 | Brighton Rock | 1937 | Graham Greene |
+---------+-----------------------+----------+---------------+
As you can see, a second book by Graham Greene was found and both have been displayed. The column heading was changed for the output of the author’s name per the AS clause. We could change the column headings in the display for the other columns with the keyword AS as well. The author alias can be reused in a SELECT statement, but not in the WHERE clause, unfortunately. You can find more information on AS in Chapter 6.
Ordering, Limiting, and Grouping
For times when we retrieve a long list of data, it can be tidier to sort the data output in a specific order. To do this, we can use the ORDER BY clause. Suppose that we want a list of plays written by William Shakespeare from our database. We could enter the following SQL statement to retrieve such a list and to sort the data by the play title:
SELECT book_id, title, publisher
FROM books
JOIN authors USING(author_id)
JOIN publishers USING(publisher_id)
WHERE author_last = 'Shakespeare'
AND genre = 'play'
ORDER BY title, pub_year;
The ORDER BY clause comes at the end, after the WHERE clause. Here the ORDER BY clause orders the data results first by the title column and then, within title, by the pub_year column, or the year that the particular printing of the play was published. By default, data is sorted in ascending alphanumeric order. If we want to order the results in descending order for the titles, we can