MySQL in a Nutshell [18]
...
ORDER BY title DESC, pub_year;
A large bookstore will have many editions of Shakespeare’s plays, possibly a few different printings for each play. If we want to limit the number of records displayed, we could add a LIMIT clause to the end of the previous SQL statement:
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 DESC, pub_year
LIMIT 20;
This addition limits the number of rows displayed to the first 20. The count starts from the first row of the result set after the data has been ordered according to the ORDER BY clause. If we want to retrieve the next 10, we would adjust the LIMIT clause to specify the number of rows to skip, along with the number of records to retrieve. So if we want to skip the first 20 rows and list the next 10 rows from our sort, we replace the LIMIT clause in the SQL statement with this one:
...
LIMIT 20, 10;
As you can see, in a two-argument LIMIT clause, the first argument specifies the number of rows to skip or the point to begin (i.e., 20) and the second argument states the number of rows to display (i.e., 10).
If we want to get just a list of titles by Shakespeare, and we are not concerned with which printing or publisher—that is to say, if we want one row for each title and are satisfied with the first row found for each—we could use the GROUP BY clause like this:
SELECT book_id, title
FROM books
JOIN authors USING(author_id)
WHERE author_last = 'Shakespeare'
GROUP BY title;
The result of this SQL statement is a list of titles by Shakespeare from the database, displaying the record identification number of the first one found for each title.
Analyzing and Manipulating Data
With MySQL you can not only retrieve raw data, but also analyze and format the data retrieved. For instance, suppose we want to know how many titles we stock by Leo Tolstoy. We could enter a SELECT statement containing a COUNT( ) function like this:
SELECT COUNT(*)
FROM books
JOIN authors USING(author_id)
WHERE author_last = 'Tolstoy';
+----------+
| COUNT(*) |
+----------+
| 12 |
+----------+
As another example, suppose that after setting up our database and putting it to use we have another table called orders that contains information on customer orders. We can query that table to find the total sales of a particular book. For instance, to find the total revenues generated from, say, William Boyd’s book Armadillo, we would enter the following SQL statement in the mysql client:
SELECT SUM(sale_amount) AS 'Armadillo Sales'
FROM orders
JOIN books USING(book_id)
JOIN authors USING(author_id)
WHERE title = 'Armadillo'
AND author_last = 'Boyd';
+-----------------+
| Armadillo Sales |
+-----------------+
| 250.25 |
+-----------------+
Here we are joining three tables together to retrieve the desired information. MySQL selects the value of the sale_amount column from each row in the orders table that matches the criteria of the WHERE clause. Then it adds those numbers and displays the sum with the column heading given.
For columns that contain date or time information, we can decide on the format for displaying the data using a variety of functions. For instance, suppose that we want to extract from the orders table the date that a customer made a particular purchase, based on his receipt number (e.g., 1250), which in turn is the record identification number, or sale_id. We could simply enter the following statement and get the default format as shown in the last line of results:
SELECT purchase_date AS 'Purchase Date'
FROM orders
WHERE sale_id = '1250';
+---------------+
| Purchase Date |
+---------------+
| 2004-03-01 |
+---------------+
This format (year-month-day) is understandable. However, if we want the month displayed in English rather than numerically, we have to use some date functions:
SELECT