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, ...
Get MySQL in a Nutshell, 2nd Edition 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.