O'Reilly logo

MySQL in a Nutshell by Russell J.T. Dyer

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 Tolstoy. We could enter a SELECT statement containing a COUNT() function like this:

SELECT COUNT(*)
FROM books, authors
WHERE author_last = 'Tolstoy'
   AND author_id = authors.rec_id;
   
+----------+
| 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, books, authors
WHERE  title = 'Armadillo'
   AND author_last = 'Boyd'
   AND book_id = books.rec_id
   AND author_id = authors.rec_id;
   
+-----------------+
| Armadillo Sales |
+-----------------+
|          250.25 |
+-----------------+

Here we are joining three tables together to retrieve the desired information. MySQL is selecting 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. Most column names appear in only one table, so MySQL knows what we mean even if we don’t specify the table each ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required