Table of Contents for

MySQL in a Nutshell, 2nd Edition

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 CONCAT(MONTHNAME(purchase_date), ' ', 
DAYOFMONTH(purchase_date), ', ', 
YEAR(purchase_date)) AS 'Purchase Date'
FROM orders
WHERE sale_id = '1250';
   
+---------------+
| Purchase Date |
+---------------+
| March 1, 2004 |
+---------------+

To put the date together in the typical human-readable format used in the United States, we’re using the CONCAT() function in conjunction with a few date functions. It may be a little confusing at first glance, because we’re inserting a space between the month and the day at the end of the first line and a comma and a space after the day at the end of the second line. As for the date functions, the first one extracts the month from the purchase_date column and formats it so its full name is displayed. The second date function on the second line extracts just the day, after which we explicitly specify a comma. The third date function on the third line extracts just the year.

As you can see in the results, our combination of functions works. However, it’s not the cleanest method by which the date can be assembled. We could use the DATE_FORMAT() function instead:

SELECT DATE_FORMAT(purchase_date, "%M %d, %Y")
AS 'Purchase Date'
FROM orders
WHERE sale_id = '1250';

This is a much more efficient method, and it provides the same output as the previous statement. You just have to know the formatting codes to be able to use this function properly. They’re listed in Chapter 12, along with several more formatting codes and many more date and time functions.