Section 3.0. Introduction
3.0. IntroductionThis chapter focuses on the SELECT statement, which retrieves database information. It shows how to 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. There are so many ways to write SELECT statements that we'll look at only a few of them. Consult the MySQL Reference Manual or a general MySQL text for more information about SELECT syntax and the functions and operators that you can use to extract and manipulate data. SELECT gives you control over several aspects of row 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 earlier in Section 1.28, which discusses how to use mysql as a calculator. Other nontable-based queries are useful for purposes such as determining what version of the server you're running or the name of the default database: mysql> SELECT VERSION(), DATABASE(); +------------+------------+ | VERSION() | DATABASE() | +------------+------------+ | 5.0.27-log | cookbook | +------------+------------+ 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 rows that track mail message traffic between users on a set of hosts. The mail table 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 | +---------------------+---------+---------+---------+---------+---------+ | 2006-05-11 10:15:08 | barb | saturn | tricia | mars | 58274 | | 2006-05-12 12:48:13 | tricia | mars | gene | venus | 194925 | | 2006-05-12 15:02:49 | phil | mars | phil | saturn | 1048 | | 2006-05-13 13:59:18 | barb | saturn | tricia | venus | 271 | | 2006-05-14 09:31:37 | gene | venus | barb | mars | 2291 | | 2006-05-14 11:52:17 | phil | mars | tricia | saturn | 5781 | | 2006-05-14 14:42:21 | barb | venus | barb | venus | 98151 | | 2006-05-14 17:03:01 | tricia | saturn | phil | venus | 2394482 | | 2006-05-15 07:17:48 | gene | mars | gene | saturn | 3824 | | 2006-05-15 08:50:57 | phil | venus | phil | venus | 978 | | 2006-05-15 10:25:52 | gene | mars | tricia | saturn | 998532 | | 2006-05-15 17:35:31 | gene | saturn | gene | mars | 3856 | | 2006-05-16 09:00:28 | gene | venus | barb | mars | 613 | | 2006-05-16 23:04:19 | phil | venus | barb | venus | 10294 | | 2006-05-17 12:49:23 | phil | mars | tricia | saturn | 873 | | 2006-05-19 22:21:51 | gene | saturn | gene | venus | 23992 | +---------------------+---------+---------+---------+---------+---------+ To create and load the mail table, 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 table that you need to create, do so the same way as for the mail table, using the appropriate script in the tables directory. In addition, the text for many of the scripts and programs used in this chapter can be found in the select directory. The files in that directory enable you to try the examples more easily. You can execute many of the statements shown here by running them from within the mysql program, which is discussed in Chapter 1. A few of the examples involve issuing statements from within the context of a programming language. See Chapter 2 for background on programming techniques. |
