MySQL Cookbook

3.1 Introduction

This chapter focuses on the SELECT statement that is used for retrieving information from a database. It provides some essential background that shows various ways you can use SELECT to tell MySQL what you want to see. You should find the chapter helpful if your SQL background is limited or if you want to find out about the MySQL-specific extensions to SELECT syntax. However, there are so many ways to write SELECT queries that we'll necessarily touch on just a few. You may wish to consult the MySQL Reference Manual or a MySQL text for more information about the syntax of SELECT, as well as the functions and operators that you can use for extracting and manipulating data.

SELECT gives you control over several aspects of record retrieval:

• Which table to use

• Which columns to display from the table

• What names to give the columns

• Which rows to retrieve from the table

• How to sort the rows

Many useful queries are quite simple and don't specify all those things. For example, some forms of SELECT don't even name a table—a fact used in Recipe 1.32, which discusses how to use mysql as a calculator. Other non-table-based queries are useful for purposes such as checking what version of the server you're running or the name of the current database:

mysql> SELECT VERSION( ), DATABASE( );
+-------------+------------+
| VERSION( )   | DATABASE( ) |
+-------------+------------+
| 3.23.51-log | cookbook   |
+-------------+------------+

However, to answer more involved questions, normally you'll need to pull information from one or more tables. Many of the examples in this chapter use a table named mail, which contains columns used to maintain a log of mail message traffic between users on a set of hosts. Its definition looks like this:

CREATE TABLE mail
(
    t       DATETIME,   # when message was sent
    srcuser CHAR(8),    # sender (source user and host)
    srchost CHAR(20),
    dstuser CHAR(8),    # recipient (destination user and host)
    dsthost CHAR(20),
    size    BIGINT,     # message size in bytes
    INDEX   (t)
);

And its contents look like this:

+---------------------+---------+---------+---------+---------+---------+
| t                   | srcuser | srchost | dstuser | dsthost | size    |
+---------------------+---------+---------+---------+---------+---------+
| 2001-05-11 10:15:08 | barb    | saturn  | tricia  | mars    |   58274 |
| 2001-05-12 12:48:13 | tricia  | mars    | gene    | venus   |  194925 |
| 2001-05-12 15:02:49 | phil    | mars    | phil    | saturn  |    1048 |
| 2001-05-13 13:59:18 | barb    | saturn  | tricia  | venus   |     271 |
| 2001-05-14 09:31:37 | gene    | venus   | barb    | mars    |    2291 |
| 2001-05-14 11:52:17 | phil    | mars    | tricia  | saturn  |    5781 |
| 2001-05-14 14:42:21 | barb    | venus   | barb    | venus   |   98151 |
| 2001-05-14 17:03:01 | tricia  | saturn  | phil    | venus   | 2394482 |
| 2001-05-15 07:17:48 | gene    | mars    | gene    | saturn  |    3824 |
| 2001-05-15 08:50:57 | phil    | venus   | phil    | venus   |     978 |
| 2001-05-15 10:25:52 | gene    | mars    | tricia  | saturn  |  998532 |
| 2001-05-15 17:35:31 | gene    | saturn  | gene    | mars    |    3856 |
| 2001-05-16 09:00:28 | gene    | venus   | barb    | mars    |     613 |
| 2001-05-16 23:04:19 | phil    | venus   | barb    | venus   |   10294 |
| 2001-05-17 12:49:23 | phil    | mars    | tricia  | saturn  |     873 |
| 2001-05-19 22:21:51 | gene    | saturn  | gene    | venus   |   23992 |
+---------------------+---------+---------+---------+---------+---------+

To create the mail table and load its contents, change location into the tables directory of the recipes distribution and run this command:

% mysql cookbook < mail.sql

This chapter also uses other tables from time to time. Some of these were used in previous chapters, while others are new. For any that you need to create, do so the same way as for the mail table, using scripts in the tables directory. In addition, the text for many of the scripts and programs used in the chapter may be found in the select directory. You can use the files there to try out the examples more easily.

Many of the queries shown here can be tried out with mysql, which you can read about in Chapter 1. Some of the examples issue queries from within the context of a programming language. See Chapter 2 for background on programming techniques.