MySQL Cookbook, 2nd Edition

Introduction

This 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:

• Which table to use

• Which columns and rows to retrieve from the table

• How to name the output columns

• 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 earlier in Using mysql as a Calculator, 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 ...