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:
Many useful queries are quite simple and don't specify all those things. For example, some forms of SELECT don't even name a tablea 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.
Using the mysql Client Program
Writing MySQL-Based Programs
Record Selection Techniques
Working with Strings
Working with Dates and Times
Sorting Query Results
Generating Summaries
Modifying Tables with ALTER TABLE
Obtaining and Using Metadata
Importing and Exporting Data
Generating and Using Sequences
Using Multiple Tables
Statistical Techniques
Handling Duplicates
Performing Transactions
Introduction to MySQL on the Web
Incorporating Query Resultsinto Web Pages
Processing Web Input with MySQL
Using MySQL-Based Web Session Management
Appendix A. Obtaining MySQL Software
Appendix B. JSP and Tomcat Primer
Appendix C. References