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.
| 
No comments:
Post a Comment