第3章 查询入门
# 第3章 查询入门
到目前为止，在前两章中，你已经零零散散地看到了一些数据库查询（也就是select语句）的示例。现在，是时候更深入地研究select语句的各个部分，以及它们是如何相互作用的了。
# 查询机制
在剖析select语句之前，了解MySQL服务器（或者说，任何数据库服务器）是如何执行查询的，或许会很有意思。假设你正在使用mysql命令行工具（我假定你是这样），那么你已经通过提供用户名和密码（如果MySQL服务器在不同的计算机上运行，可能还需要提供主机名）登录到了MySQL服务器。一旦服务器验证你的用户名和密码正确，就会为你生成一个数据库连接供你使用。这个连接由请求它的应用程序（在这种情况下是mysql工具）持有，直到该应用程序释放连接（即你输入quit命令），或者服务器关闭连接（即服务器关机时）。每个到MySQL服务器的连接都会被分配一个标识符，你首次登录时会看到这个标识符：
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 6.0.3-alpha-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
2
3
4
在这个例子中，我的连接ID是11。如果出了问题，比如一个格式错误的查询运行了好几个小时，这些信息对你的数据库管理员可能会有用，所以你或许想要把它记下来。
一旦服务器验证了你的用户名和密码，并给你分配了连接，你就可以执行查询（以及其他SQL语句）了。每次将查询发送到服务器时，服务器在执行语句之前会检查以下几点：
- 你是否有权限执行该语句？
- 你是否有权限访问所需的数据？
- 你的语句语法是否正确？
如果你的语句通过了这三项检查，那么你的查询会被交给查询优化器（query optimizer），其任务是确定执行查询的最有效方式。优化器会考虑诸如连接from子句中指定表的顺序、有哪些索引可用等因素，然后选择一个执行计划，服务器会依据这个执行计划来执行你的查询。
理解并影响数据库服务器选择执行计划的方式是一个很有趣的话题，你们中的许多人可能都希望深入探究。对于使用MySQL的读者，你可以考虑阅读Baron Schwartz等人所著的《高性能MySQL》（High Performance MySQL，http://oreilly.com/catalog/9780596101718/ ，O’Reilly出版）。通过这本书，你将学到如何生成索引、分析执行计划、通过查询提示影响优化器，以及调整服务器的启动参数等内容。如果你使用的是Oracle数据库或SQL Server，市面上有数十本相关的调优书籍可供参考。
一旦服务器完成查询的执行，结果集（result set）就会返回给调用它的应用程序（这里还是mysql工具）。正如我在第1章中提到的，结果集就是另一个包含行和列的表。如果你的查询没有返回任何结果，mysql工具会显示如下示例末尾的消息：
mysql> SELECT emp_id, fname, lname
-> FROM employee
-> WHERE lname = 'Bkadfl';
Empty set(0.00 sec)
2
3
4
如果查询返回了一行或多行数据，mysql工具会通过添加列标题，并用-、|和+符号在列周围绘制边框来格式化结果，如下一个示例所示：
mysql> SELECT fname, lname
-> FROM employee;
+-----------+-----------+
| fname | lname |
+-----------+-----------+
| Michael | Smith |
| Susan | Barker |
| Robert | Tyler |
| Susan | Hawthorne |
| John | Gooding |
| Helen | Fleming |
| Chris | Tucker |
| Sarah | Parker |
| Jane | Grossman |
| Paula | Roberts |
| Thomas | Ziegler |
| Samantha | Jameson |
| John | Blake |
| Cindy | Mason |
| Frank | Portman |
| Theresa | Markham |
| Beth | Fowler |
| Rick | Tulman |
+-----------+-----------+
18 rows in set (0.00 sec)
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
这个查询返回了employee表中所有员工的名字和姓氏。在显示完最后一行数据后，mysql工具会显示一条消息，告诉你返回了多少行数据，在这个例子中是18行。
# 查询子句
select语句由几个组件或子句组成。在使用MySQL时，虽然只有一个子句是必需的（select子句），但通常你会在六个可用子句中至少使用两到三个。表3 - 1展示了不同的子句及其用途。
表3 - 1 查询子句
| 子句名称 | 用途 |
|---|---|
select | 确定查询结果集中应包含哪些列 |
from | 指定从哪些表中获取数据以及表之间的连接方式 |
where | 过滤掉不需要的数据 |
group by | 用于根据列的相同值对行进行分组 |
having | 过滤掉不需要的组 |
order by | 根据一个或多个列对最终结果集的行进行排序 |
表3 - 1中显示的所有子句都包含在ANSI规范中；此外，MySQL还有一些独特的子句，我们将在附录B中进行探讨。以下各节将深入研究这六个主要查询子句的用法。
# select子句
尽管select子句是select语句的第一个子句，但它却是数据库服务器最后评估的子句之一。这是因为在确定最终结果集中应包含哪些内容之前，你需要知道所有可能包含在最终结果集中的列。因此，为了充分理解select子句的作用，你需要对from子句有一定的了解。下面从一个查询开始：
mysql> SELECT *
-> FROM department;
+---------+----------------+
| dept_id | name |
+---------+----------------+
| 1 | Operations |
| 2 | Loans |
| 3 | Administration |
+---------+----------------+
3 rows in set (0.04 sec)
2
3
4
5
6
7
8
9
10
在这个查询中，from子句列出了一个表（department），select子句表示department表中的所有列（用*表示）都应包含在结果集中。这个查询用英语描述如下：
显示department表中的所有列和所有行。
除了通过星号指定所有列之外，你还可以显式指定感兴趣的列，例如：
mysql> SELECT dept_id, name
-> FROM department;
+---------+----------------+
| dept_id | name |
+---------+----------------+
| 1 | Operations |
| 2 | Loans |
| 3 | Administration |
+---------+----------------+
3 rows in set (0.01 sec)
2
3
4
5
6
7
8
9
10
结果与第一个查询相同，因为select子句中指定了department表中的所有列（dept_id和name）。你也可以选择只包含department表中列的一个子集：
mysql> SELECT name
-> FROM department;
+----------------+
| name |
+----------------+
| Operations |
| Loans |
| Administration |
+----------------+
3 rows in set (0.00 sec)
2
3
4
5
6
7
8
9
10
因此，select子句的作用如下：
select子句决定在查询结果集中应包含所有可能列中的哪些列。
如果你只能在select子句中包含from子句中指定表的列，那就太无趣了。然而，你可以通过在select子句中包含以下内容来让查询更丰富：
- 字面量，如数字或字符串
- 表达式，如
transaction.amount * -1 - 内置函数调用，如
ROUND(transaction.amount, 2) - 用户定义函数调用
下一个查询展示了在对employee表的单个查询中，如何同时使用表列、字面量、表达式和内置函数调用：
mysql> SELECT emp_id,
-> 'ACTIVE',
-> emp_id * 3.14159,
-> UPPER(lname)
-> FROM employee;
+--------+----------+------------------+--------------+
| emp_id | ACTIVE | emp_id * 3.14159 | UPPER(lname) |
+--------+----------+------------------+--------------+
| 1 | ACTIVE | 3.14159 | SMITH |
| 2 | ACTIVE | 6.28318 | BARKER |
| 3 | ACTIVE | 9.42477 | TYLER |
| 4 | ACTIVE | 12.56636 | HAWTHORNE |
| 5 | ACTIVE | 15.70795 | GOODING |
| 6 | ACTIVE | 18.84954 | FLEMING |
| 7 | ACTIVE | 21.99113 | TUCKER |
| 8 | ACTIVE | 25.13272 | PARKER |
| 9 | ACTIVE | 28.27431 | GROSSMAN |
| 10 | ACTIVE | 31.41590 | ROBERTS |
| 11 | ACTIVE | 34.55749 | ZIEGLER |
| 12 | ACTIVE | 37.69908 | JAMESON |
| 13 | ACTIVE | 40.84067 | BLAKE |
| 14 | ACTIVE | 43.98226 | MASON |
| 15 | ACTIVE | 47.12385 | PORTMAN |
| 16 | ACTIVE | 50.26544 | MARKHAM |
| 17 | ACTIVE | 53.40703 | FOWLER |
| 18 | ACTIVE | 56.54862 | TULMAN |
+--------+----------+------------------+--------------+
18 rows in set (0.05 sec)
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
我们将在后面详细介绍表达式和内置函数，但我想让你感受一下select子句中可以包含哪些内容。如果你只需要执行一个内置函数或计算一个简单的表达式，则可以完全省略from子句。例如：
mysql> SELECT VERSION(),
-> USER(),
-> DATABASE();
+--------------------------+-----------------------+------------+
| version() | user() | database() |
+--------------------------+-----------------------+------------+
| 6.0.3-alpha-community | lrngsql@localhost | bank |
+--------------------------+-----------------------+------------+
1 row in set (0.05 sec)
2
3
4
5
6
7
8
9
由于这个查询只是调用了三个内置函数，并没有从任何表中检索数据，所以不需要from子句。
# 列别名
虽然mysql工具会为查询返回的列生成标签，但你可能想要指定自己的标签。如果表中的某列命名不佳或存在歧义，你可能会想要为其指定一个新标签，而对于结果集中由表达式或内置函数调用生成的列，你几乎肯定会想要指定自己的标签。你可以通过在select子句的每个元素后添加列别名（column alias）来实现。下面是前面针对employee表的查询，为其中三列应用了列别名：
mysql> SELECT emp_id,
-> 'ACTIVE' status,
-> emp_id * 3.14159 empid_x_pi,
-> UPPER(lname) last_name_upper
-> FROM employee;
+--------+----------+------------------+----------------+
| emp_id | status | empid_x_pi | last_name_upper |
+--------+----------+------------------+----------------+
| 1 | ACTIVE | 3.14159 | SMITH |
| 2 | ACTIVE | 6.28318 | BARKER |
| 3 | ACTIVE | 9.42477 | TYLER |
| 4 | ACTIVE | 12.56636 | HAWTHORNE |
| 5 | ACTIVE | 15.70795 | GOODING |
| 6 | ACTIVE | 18.84954 | FLEMING |
| 7 | ACTIVE | 21.99113 | TUCKER |
| 8 | ACTIVE | 25.13272 | PARKER |
| 9 | ACTIVE | 28.27431 | GROSSMAN |
| 10 | ACTIVE | 31.41590 | ROBERTS |
| 11 | ACTIVE | 34.55749 | ZIEGLER |
| 12 | ACTIVE | 37.69908 | JAMESON |
| 13 | ACTIVE | 40.84067 | BLAKE |
| 14 | ACTIVE | 43.98226 | MASON |
| 15 | ACTIVE | 47.12385 | PORTMAN |
| 16 | ACTIVE | 50.26544 | MARKHAM |
| 17 | ACTIVE | 53.40703 | FOWLER |
| 18 | ACTIVE | 56.54862 | TULMAN |
+--------+----------+------------------+----------------+
18 rows in set (0.00 sec)
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
查看列标题，你会发现第二、第三和第四列现在有了更合理的名称，而不是简单地用生成该列的函数或表达式来标记。查看select子句，你可以看到列别名status、empid_x_pi和last_name_upper是如何添加在第二、第三和第四列之后的。我想你会认同，有了列别名后，输出结果更易于理解。如果你是在Java或C#中而不是通过mysql工具交互式地发出查询，那么在编程处理时也会更方便。为了让列别名更加突出，你还可以选择在别名前使用as关键字，如下所示：
mysql> SELECT emp_id,
-> 'ACTIVE' AS status,
-> emp_id * 3.14159 AS empid_x_pi,
-> UPPER(lname) AS last_name_upper
-> FROM employee;
2
3
4
5
许多人认为使用可选的as关键字可以提高可读性，不过在本书的示例中，我选择不使用它。
# 去除重复项
在某些情况下，查询可能会返回重复的数据行。例如，如果要检索所有拥有账户的客户的ID，会看到如下结果：
mysql> SELECT cust_id
-> FROM account;
+---------+
| cust_id |
+---------+
| 1 |
| 1 |
| 1 |
| 2 |
| 2 |
| 3 |
| 3 |
| 4 |
| 4 |
| 4 |
| 5 |
| 6 |
| 6 |
| 7 |
| 8 |
| 8 |
| 9 |
| 9 |
| 9 |
| 10 |
| 10 |
| 11 |
| 12 |
| 13 |
+---------+
24 rows in set (0.00 sec)
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
由于有些客户拥有多个账户，所以对于每个客户拥有的每个账户，都会看到一次该客户的ID。在这种情况下，可能想要的是拥有账户的不同客户的集合，而不是看到account表中每一行对应的客户ID。可以通过在select关键字后直接添加distinct关键字来实现，如下所示：
mysql> SELECT DISTINCT cust_id
-> FROM account;
+---------+
| cust_id |
+---------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
+---------+
13 rows in set (0.01 sec)
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
现在结果集中包含13行数据，每个不同的客户对应一行，而不是24行（每个账户对应一行）。
如果不希望服务器去除重复数据，或者确定结果集中不会有重复数据，可以指定ALL关键字，而不是指定DISTINCT。不过，ALL关键字是默认值，无需显式指定，所以大多数程序员在查询中不会包含ALL。
请记住，生成去重的结果集需要对数据进行排序，对于大型结果集来说，这可能会很耗时。不要仅仅为了确保没有重复数据就陷入使用
DISTINCT的陷阱；相反，应该花时间了解正在处理的数据，这样就能知道是否可能出现重复数据。
# from子句
到目前为止，你所见到的查询语句中，from子句都只包含一个表。虽然大多数SQL书籍将from子句简单定义为一个或多个表的列表，但我想对其定义进行拓展，如下：
from子句用于定义查询所使用的表，以及将这些表连接在一起的方式。
这个定义由两个相互独立但又相关的概念构成，我们将在接下来的部分进行探讨。
# 表
当提到“表（table）”这个术语时，大多数人会想到存储在数据库中的一组相关行。虽然这确实描述了一种表，但我想以更宽泛的方式使用这个词，不去考虑数据的存储方式，只关注相关行的集合。有三种不同类型的表符合这个更宽泛的定义：
- 永久表（即使用
CREATE TABLE语句创建的表） - 临时表（即子查询返回的行）
- 虚拟表（即使用
CREATE VIEW语句创建的表）
这些表类型都可以包含在查询的from子句中。现在，你应该已经熟悉在from子句中使用永久表了，所以接下来我简要介绍一下其他可以在from子句中引用的表类型。
# 子查询生成的表
子查询（subquery）是包含在另一个查询中的查询。子查询被括号包围，可以出现在SELECT语句的不同部分；不过，在from子句中，子查询的作用是生成一个临时表，该临时表对其他所有查询子句可见，并且可以与from子句中指定的其他表进行交互。下面是一个简单的示例：
mysql> SELECT e.emp_id, e.fname, e.lname
-> FROM (SELECT emp_id, fname, lname, start_date, title
-> FROM employee) e;
+--------+-------------+-----------------+
| emp_id | fname | lname |
+--------+-------------+-----------------+
| 1 | Michael | Smith |
| 2 | Susan | Barker |
| 3 | Robert | Tyler |
| 4 | Susan | Hawthorne |
| 5 | John | Gooding |
| 6 | Helen | Fleming |
| 7 | Chris | Tucker |
| 8 | Sarah | Parker |
| 9 | Jane | Grossman |
| 10 | Paula | Roberts |
| 11 | Thomas | Ziegler |
| 12 | Samantha | Jameson |
| 13 | John | Blake |
| 14 | Cindy | Mason |
| 15 | Frank | Portman |
| 16 | Theresa | Markham |
| 17 | Beth | Fowler |
| 18 | Rick | Tulman |
+--------+-------------+-----------------+
18 rows in set (0.00 sec)
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
在这个例子中，针对employee表的子查询返回了五列数据，而外部查询引用了这五列中的三列。外部查询通过子查询的别名（在这个例子中是e）来引用子查询。这是一个简单但不太实用的在from子句中使用子查询的示例；第9章将详细介绍子查询。
# 视图
视图（view）是存储在数据字典中的查询。它的外观和行为都像表，但视图不关联实际数据（这就是我称它为虚拟表的原因）。当你针对视图发出查询时，你的查询会与视图定义合并，生成最终要执行的查询。
为了演示，下面是一个查询employee表并调用内置函数的视图定义：
mysql> CREATE VIEW employee_vw AS
-> SELECT emp_id, fname, lname,
-> YEAR(start_date) start_year
-> FROM employee;
Query OK, 0 rows affected (0.10 sec)
2
3
4
5
创建视图时，不会生成或存储额外的数据：服务器只是将SELECT语句保存起来供以后使用。现在视图已经创建好了，你可以针对它发出查询，如下：
mysql> SELECT emp_id, start_year
-> FROM employee_vw;
+--------+------------+
| emp_id | start_year |
+--------+------------+
| 1 | 2005 |
| 2 | 2006 |
| 3 | 2005 |
| 4 | 2006 |
| 5 | 2007 |
| 6 | 2008 |
| 7 | 2008 |
| 8 | 2006 |
| 9 | 2006 |
| 10 | 2006 |
| 11 | 2004 |
| 12 | 2007 |
| 13 | 2004 |
| 14 | 2006 |
| 15 | 2007 |
| 16 | 2005 |
| 17 | 2006 |
| 18 | 2006 |
+--------+------------+
18 rows in set (0.07 sec)
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
创建视图有多种原因，包括对用户隐藏某些列以及简化复杂的数据库设计。
# 表连接
对简单from子句定义的第二个拓展是，如果from子句中出现多个表，那么用于连接这些表的条件也必须包含在内。这不是MySQL或其他任何数据库服务器的强制要求，但这是美国国家标准学会（ANSI）认可的连接多个表的方法，并且在各种数据库服务器中具有最强的可移植性。我们将在第5章和第10章深入探讨连接多个表的方法，不过，为了满足你的好奇心，这里先给出一个简单的示例：
mysql> SELECT employee.emp_id, employee.fname,
-> employee.lname, department.name dept_name
-> FROM employee INNER JOIN department
-> ON employee.dept_id = department.dept_id;
+--------+-------------+-----------------+------------------+
| emp_id | fname | lname | dept_name |
+--------+-------------+-----------------+------------------+
| 1 | Michael | Smith | Administration |
| 2 | Susan | Barker | Administration |
| 3 | Robert | Tyler | Administration |
| 4 | Susan | Hawthorne | Operations |
| 5 | John | Gooding | Loans |
| 6 | Helen | Fleming | Operations |
| 7 | Chris | Tucker | Operations |
| 8 | Sarah | Parker | Operations |
| 9 | Jane | Grossman | Operations |
| 10 | Paula | Roberts | Operations |
| 11 | Thomas | Ziegler | Operations |
| 12 | Samantha | Jameson | Operations |
| 13 | John | Blake | Operations |
| 14 | Cindy | Mason | Operations |
| 15 | Frank | Portman | Operations |
| 16 | Theresa | Markham | Operations |
| 17 | Beth | Fowler | Operations |
| 18 | Rick | Tulman | Operations |
+--------+-------------+-----------------+------------------+
18 rows in set (0.05 sec)
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
上述查询显示了来自employee表（emp_id、fname、lname）和department表（name）的数据，因此这两个表都包含在from子句中。连接这两个表的机制（称为连接，join）是employee表中存储的员工部门隶属关系。因此，数据库服务器会根据employee表中dept_id列的值，在department表中查找对应的部门名称。两个表的连接条件位于from子句的ON子句中；在这个例子中，连接条件是ON employee.dept_id = department.dept_id。同样，关于连接多个表的详细讨论，请参考第5章。
# 定义表别名
当在单个查询中连接多个表时，在SELECT、WHERE、GROUP BY、HAVING和ORDER BY子句中引用列时，你需要一种方法来确定引用的是哪个表。在from子句之外引用表时，你有两种选择：
- 使用完整的表名，例如
employee.emp_id。 - 为每个表分配一个别名，并在整个查询中使用该别名。
在前面的查询中，我在SELECT和ON子句中使用了完整的表名。下面是使用表别名的相同查询：
SELECT e.emp_id, e.fname, e.lname,
d.name dept_name
FROM employee e INNER JOIN department d
ON e.dept_id = d.dept_id;
2
3
4
仔细观察from子句，你会发现employee表被赋予了别名e，department表被赋予了别名d。这些别名随后在定义连接条件的ON子句以及指定结果集中要包含的列的SELECT子句中使用。我希望你会认同，使用别名可以使语句更简洁，同时不会造成混淆（只要你选择的别名合理）。此外，你可以像前面演示列别名那样，在表别名中使用AS关键字：
SELECT e.emp_id, e.fname, e.lname,
d.name dept_name
FROM employee AS e INNER JOIN department AS d
ON e.dept_id = d.dept_id;
2
3
4
我发现，与我合作过的数据库开发人员中，大约有一半会在列别名和表别名中使用AS关键字，另一半则不会。
# WHERE子句
本章到目前为止展示的查询，都是从员工表（employee）、部门表（department）或账户表（account）中选择每一行数据（本章前面演示DISTINCT的部分除外）。然而在大多数情况下，你并不想检索表中的每一行数据，而是希望能有一种方法，过滤掉那些不感兴趣的行。这正是WHERE子句的作用。
WHERE子句是用于从结果集中过滤掉不需要的行的机制。
例如，假设你只想从员工表中检索担任主管出纳（Head Teller）的员工数据。以下查询使用WHERE子句仅检索这四位主管出纳的数据：
mysql> SELECT emp_id, fname, lname, start_date, title
-> FROM employee
-> WHERE title = 'Head Teller';
+--------+--------+----------+------------+--------------+
| emp_id | fname | lname | start_date | title |
+--------+--------+----------+------------+--------------+
| 6 | Helen | Fleming | 2008-03-17 | Head Teller |
| 10 | Paula | Roberts | 2006-07-27 | Head Teller |
| 13 | John | Blake | 2004-05-11 | Head Teller |
| 16 | Theresa | Markham | 2005-03-15 | Head Teller |
+--------+--------+----------+------------+--------------+
4 rows in set (1.17 sec)
2
3
4
5
6
7
8
9
10
11
12
在这个例子中，WHERE子句过滤掉了员工表18行数据中的14行。这个WHERE子句只包含一个过滤条件，但你可以根据需要包含多个条件；各个条件之间使用AND、OR和NOT等运算符分隔（关于WHERE子句和过滤条件的完整讨论，请参阅第4章）。下面是对前面查询的扩展，添加了第二个条件，即只包含入职日期在2006年1月1日之后的员工：
mysql> SELECT emp_id, fname, lname, start_date, title
-> FROM employee
-> WHERE title = 'Head Teller'
-> AND start_date > '2006-01-01';
+--------+--------+----------+------------+--------------+
| emp_id | fname | lname | start_date | title |
+--------+--------+----------+------------+--------------+
| 6 | Helen | Fleming | 2008-03-17 | Head Teller |
| 10 | Paula | Roberts | 2006-07-27 | Head Teller |
+--------+--------+----------+------------+--------------+
2 rows in set (0.01 sec)
2
3
4
5
6
7
8
9
10
11
第一个条件（title = 'Head Teller'）过滤掉了18行中的14行，第二个条件（start_date > '2006-01-01'）又过滤掉了2行，最终结果集中只剩下2行数据。我们来看看，如果将两个条件之间的运算符从AND改为OR会怎样：
mysql> SELECT emp_id, fname, lname, start_date, title
-> FROM employee
-> WHERE title = 'Head Teller'
-> OR start_date > '2006-01-01';
+--------+-------------+-----------------+------------+---------------------------------+
| emp_id | fname | lname | start_date | title |
+--------+-------------+-----------------+------------+---------------------------------+
| 2 | Susan | Barker | 2006-09-12 | Vice President |
| 4 | Susan | Hawthorne | 2006-04-24 | Operations Manager |
| 5 | John | Gooding | 2007-11-14 | Loan Manager |
| 6 | Helen | Fleming | 2008-03-17 | Head Teller |
| 7 | Chris | Tucker | 2008-09-15 | Teller |
| 8 | Sarah | Parker | 2006-12-02 | Teller |
| 9 | Jane | Grossman | 2006-05-03 | Teller |
| 10 | Paula | Roberts | 2006-07-27 | Head Teller |
| 12 | Samantha | Jameson | 2007-01-08 | Teller |
| 13 | John | Blake | 2004-05-11 | Head Teller |
| 14 | Cindy | Mason | 2006-08-09 | Teller |
| 15 | Frank | Portman | 2007-04-01 | Teller |
| 16 | Theresa | Markham | 2005-03-15 | Head Teller |
| 17 | Beth | Fowler | 2006-06-29 | Teller |
| 18 | Rick | Tulman | 2006-12-12 | Teller |
+--------+-------------+-----------------+------------+---------------------------------+
15 rows in set (0.00 sec)
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
从输出结果可以看出，所有四位主管出纳都包含在结果集中，同时还包括了2006年1月1日之后入职的其他员工。在员工表的18名员工中，有15名员工至少满足这两个条件中的一个。因此，当使用AND运算符分隔条件时，所有条件都必须为真，该行才会被包含在结果集中；而使用OR运算符时，只要有一个条件为真，该行就会被包含在结果集中。
那么，如果你在WHERE子句中需要同时使用AND和OR运算符，应该怎么做呢？很高兴你提出这个问题。你应该使用括号将条件分组。下面的查询指定，结果集中只包含2006年1月1日之后入职的主管出纳，以及2007年1月1日之后入职的出纳：
mysql> SELECT emp_id, fname, lname, start_date, title
-> FROM employee
-> WHERE (title = 'Head Teller' AND start_date > '2006-01-01')
-> OR (title = 'Teller' AND start_date > '2007-01-01');
+--------+-------------+----------+------------+--------------+
| emp_id | fname | lname | start_date | title |
+--------+-------------+----------+------------+--------------+
| 6 | Helen | Fleming | 2008-03-17 | Head Teller |
| 7 | Chris | Tucker | 2008-09-15 | Teller |
| 10 | Paula | Roberts | 2006-07-27 | Head Teller |
| 12 | Samantha | Jameson | 2007-01-08 | Teller |
| 15 | Frank | Portman | 2007-04-01 | Teller |
+--------+-------------+----------+------------+--------------+
5 rows in set (0.00 sec)
2
3
4
5
6
7
8
9
10
11
12
13
14
当混合使用不同运算符时，你应该始终使用括号将条件分组，这样你、数据库服务器以及之后任何修改你代码的人都能清楚理解代码逻辑。
# GROUP BY和HAVING子句
到目前为止，所有查询检索到的都是未经处理的原始数据。然而，有时你可能希望挖掘数据中的趋势，这就需要数据库服务器在返回结果集之前对数据进行一定的处理。GROUP BY子句就是这样一种机制，它用于根据列值对数据进行分组。例如，你可能不想查看员工及其所属部门的列表，而是希望查看各部门及其对应的员工数量。在使用GROUP BY子句时，你还可以使用HAVING子句，它允许你像WHERE子句过滤原始数据那样，对分组数据进行筛选。
下面快速看一个查询示例，该查询统计每个部门的员工数量，并返回员工数量超过两名的部门名称：
mysql> SELECT d.name, count(e.emp_id) num_employees
-> FROM department d INNER JOIN employee e
-> ON d.dept_id = e.dept_id
-> GROUP BY d.name
-> HAVING count(e.emp_id) > 2;
+------------------+----------------+
| name | num_employees |
+------------------+----------------+
| Administration | 3 |
| Operations | 14 |
+------------------+----------------+
2 rows in set (0.00 sec)
2
3
4
5
6
7
8
9
10
11
12
我在此简要提及这两个子句，是为了让你在本书后续内容中遇到它们时不会感到意外，但它们比其他四个SELECT子句稍微复杂一些。因此，我建议你等到第8章，再去全面了解如何使用以及何时使用GROUP BY和HAVING子句。
# ORDER BY子句
一般来说，查询返回的结果集中的行是没有特定顺序的。如果你希望结果集按照特定顺序排列，就需要使用ORDER BY子句指示服务器对结果进行排序：
ORDER BY子句是一种机制，它可以使用原始列数据或基于列数据的表达式对结果集进行排序。
例如，下面再次查看之前对account表的查询：
mysql> SELECT open_emp_id, product_cd
-> FROM account;
+-------------+------------+
| open_emp_id | product_cd |
+-------------+------------+
| 10 | CHK |
| 10 | SAV |
| 10 | CD |
| 10 | CHK |
| 10 | SAV |
| 13 | CHK |
| 13 | MM |
| 1 | CHK |
| 1 | SAV |
| 1 | MM |
| 16 | CHK |
| 1 | CHK |
| 1 | CD |
| 10 | CD |
| 16 | CHK |
| 16 | SAV |
| 1 | CHK |
| 1 | MM |
| 1 | CD |
| 16 | CHK |
| 16 | BUS |
| 10 | BUS |
| 16 | CHK |
| 13 | SBL |
+-------------+------------+
24 rows in set (0.00 sec)
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
如果你想分析每个员工的数据，按open_emp_id列对结果进行排序会很有帮助；要实现这一点，只需在ORDER BY子句中添加该列：
mysql> SELECT open_emp_id, product_cd
-> FROM account
-> ORDER BY open_emp_id;
+-------------+------------+
| open_emp_id | product_cd |
+-------------+------------+
| 1 | CHK |
| 1 | SAV |
| 1 | MM |
| 1 | CHK |
| 1 | CD |
| 1 | CHK |
| 1 | MM |
| 1 | CD |
| 10 | CHK |
| 10 | SAV |
| 10 | CD |
| 10 | CHK |
| 10 | SAV |
| 10 | CD |
| 10 | BUS |
| 13 | CHK |
| 13 | MM |
| 13 | SBL |
| 16 | CHK |
| 16 | CHK |
| 16 | SAV |
| 16 | CHK |
| 16 | BUS |
| 16 | CHK |
+-------------+------------+
24 rows in set (0.00 sec)
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
现在，查看每个员工开设的账户类型变得更加容易。不过，如果能确保每个不同员工的账户类型显示顺序一致，那就更好了；你可以通过在ORDER BY子句中，在open_emp_id列之后添加product_cd列来实现这一点：
mysql> SELECT open_emp_id, product_cd
-> FROM account
-> ORDER BY open_emp_id, product_cd;
+-------------+------------+
| open_emp_id | product_cd |
+-------------+------------+
| 1 | CD |
| 1 | CD |
| 1 | CHK |
| 1 | CHK |
| 1 | CHK |
| 1 | MM |
| 1 | MM |
| 1 | SAV |
| 10 | BUS |
| 10 | CD |
| 10 | CD |
| 10 | CHK |
| 10 | CHK |
| 10 | SAV |
| 10 | SAV |
| 13 | CHK |
| 13 | MM |
| 13 | SBL |
| 16 | BUS |
| 16 | CHK |
| 16 | CHK |
| 16 | CHK |
| 16 | CHK |
| 16 | SAV |
+-------------+------------+
24 rows in set (0.00 sec)
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
结果集现在先按员工ID排序，再按账户类型排序。列在ORDER BY子句中的顺序是很重要的。
# 升序与降序排序顺序
在进行排序时，你可以通过asc和desc关键字来指定升序或降序排序。默认是升序排序，所以只有在你希望使用降序排序时，才需要添加desc关键字。例如，以下查询列出了所有账户，并按可用余额进行排序，余额最高的账户排在最前面：
mysql> SELECT account_id, product_cd, open_date, avail_balance
-> FROM account
-> ORDER BY avail_balance DESC;
+------------+------------+------------+---------------+
| account_id | product_cd | open_date | avail_balance |
+------------+------------+------------+---------------+
| 29 | SBL | 2004-02-22 | 50000.00 |
| 28 | CHK | 2003-07-30 | 38552.05 |
| 24 | CHK | 2002-09-30 | 23575.12 |
| 15 | CD | 2004-12-28 | 10000.00 |
| 27 | BUS | 2004-03-22 | 9345.55 |
| 22 | MM | 2004-10-28 | 9345.55 |
| 12 | MM | 2004-09-30 | 5487.09 |
| 17 | CD | 2004-01-12 | 5000.00 |
| 18 | CHK | 2001-05-23 | 3487.19 |
| 3 | CD | 2004-06-30 | 3000.00 |
| 4 | CHK | 2001-03-12 | 2258.02 |
| 13 | CHK | 2004-01-27 | 2237.97 |
| 8 | MM | 2002-12-15 | 2212.50 |
| 23 | CD | 2004-06-30 | 1500.00 |
| 1 | CHK | 2000-01-15 | 1057.75 |
| 7 | CHK | 2002-11-23 | 1057.75 |
| 11 | SAV | 2000-01-15 | 767.77 |
| 10 | CHK | 2003-09-12 | 534.12 |
| 2 | SAV | 2000-01-15 | 500.00 |
| 19 | SAV | 2001-05-23 | 387.99 |
| 5 | SAV | 2001-03-12 | 200.00 |
| 21 | CHK | 2003-07-30 | 125.67 |
| 14 | CHK | 2002-08-24 | 122.37 |
| 25 | BUS | 2002-10-01 | 0.00 |
+------------+------------+------------+---------------+
24 rows in set (0.05 sec)
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
降序排序通常用于排名查询，例如“给我显示账户余额前五的账户”。MySQL包含一个limit子句，它允许你对数据进行排序，然后只保留前X行数据，丢弃其余部分；有关limit子句以及其他非ANSI扩展的讨论，请参阅附录B。
# 通过表达式进行排序
使用列数据对结果进行排序固然很好，但有时你可能需要按数据库中未存储的内容进行排序，而且这些内容可能在查询中任何地方都未出现。你可以在order by子句中添加一个表达式来处理这种情况。例如，假设你想按客户联邦身份证号码（个人是社会保障号码，企业是公司身份证号码）的最后三位数字对客户数据进行排序：
mysql> SELECT cust_id, cust_type_cd, city, state, fed_id
-> FROM customer
-> ORDER BY RIGHT(fed_id, 3);
+---------+--------------+------------+-------+-------------+
| cust_id | cust_type_cd | city | state | fed_id |
+---------+--------------+------------+-------+-------------+
| 1 | I | Lynnfield | MA | 111-11-1111 |
| 10 | B | Salem | NH | 04-1111111 |
| 2 | I | Woburn | MA | 222-22-2222 |
| 11 | B | Wilmington | MA | 04-2222222 |
| 3 | I | Quincy | MA | 333-33-3333 |
| 12 | B | Salem | NH | 04-3333333 |
| 13 | B | Quincy | MA | 04-4444444 |
| 4 | I | Waltham | MA | 444-44-4444 |
| 5 | I | Salem | NH | 555-55-5555 |
| 6 | I | Waltham | MA | 666-66-6666 |
| 7 | I | Wilmington | MA | 777-77-7777 |
| 8 | I | Salem | NH | 888-88-8888 |
| 9 | I | Newton | MA | 999-99-9999 |
+---------+--------------+------------+-------+-------------+
13 rows in set (0.24 sec)
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
此查询使用内置函数right()提取fed_id列的最后三个字符，然后根据该值对行进行排序。
# 通过数字占位符进行排序
如果你要使用select子句中的列进行排序，可以选择通过列在select子句中的位置来引用列，而不是通过列名。例如，如果你想使用查询返回的第二列和第五列进行排序，可以这样做：
mysql> SELECT emp_id, title, start_date, fname, lname
-> FROM employee
-> ORDER BY 2, 5;
+-----------+----------------------------------+----------------+-------------+--------------+
| emp_id | title | start_date | fname | lname |
+-----------+----------------------------------+----------------+-------------+--------------+
| 13 | Head Teller | 2004-05-11 | John | Blake |
| 6 | Head Teller | 2008-03-17 | Helen | Fleming |
| 16 | Head Teller | 2005-03-15 | Theresa | Markham |
| 10 | Head Teller | 2006-07-27 | Paula | Roberts |
| 5 | Loan Manager | 2007-11-14 | John | Gooding |
| 4 | Operations Manager | 2006-04-24 | Susan | Hawthorne |
| 1 | President | 2005-06-22 | Michael | Smith |
| 17 | Teller | 2006-06-29 | Beth | Fowler |
| 9 | Teller | 2006-05-03 | Jane | Grossman |
| 12 | Teller | 2007-01-08 | Samantha | Jameson |
| 14 | Teller | 2006-08-09 | Cindy | Mason |
| 8 | Teller | 2006-12-02 | Sarah | Parker |
| 15 | Teller | 2007-04-01 | Frank | Portman |
| 7 | Teller | 2008-09-15 | Chris | Tucker |
| 18 | Teller | 2006-12-12 | Rick | Tulman |
| 11 | Teller | 2004-10-23 | Thomas | Ziegler |
| 3 | Treasurer | 2005-02-09 | Robert | Tyler |
| 2 | Vice President | 2006-09-12 | Susan | Barker |
+-----------+----------------------------------+----------------+-------------+--------------+
18 rows in set (0.00 sec)
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
你可能需要谨慎使用此功能，因为在select子句中添加列但未更改order by子句中的数字，可能会导致意外结果。就我个人而言，在编写临时查询时，我可能会按位置引用列，但在编写代码时，我总是按名称引用列。
# 知识测试
以下练习旨在加深你对select语句及其各种子句的理解。答案请参阅附录C。
# 练习3 - 1
检索所有银行员工的员工ID、名字和姓氏。先按姓氏排序，再按名字排序。
# 练习3 - 2
检索所有状态为“ACTIVE”且可用余额大于2500美元的账户的账户ID、客户ID和可用余额。
# 练习3 - 3
编写一个针对account表的查询，返回开设账户的员工的ID（使用account.open_emp_id列）。每个不同的员工只包含一行数据。
# 练习3 - 4
填写这个多数据集查询中的空白处（用<#>表示），以实现此处显示的结果：
mysql> SELECT p.product_cd, a.cust_id, a.avail_balance
-> FROM product p INNER JOIN account <1>
-> ON p.product_cd = <2>
-> WHERE p.<3> = 'ACCOUNT'
-> ORDER BY <4>, <5>;
+----------------+-----------+-----------------------+
| product_cd | cust_id | avail_balance |
+----------------+-----------+-----------------------+
| CD | 1 | 3000.00 |
| CD | 6 | 10000.00 |
| CD | 7 | 5000.00 |
| CD | 9 | 1500.00 |
| CHK | 1 | 1057.75 |
| CHK | 2 | 2258.02 |
| CHK | 3 | 1057.75 |
| CHK | 4 | 534.12 |
| CHK | 5 | 2237.97 |
| CHK | 6 | 122.37 |
| CHK | 8 | 3487.19 |
| CHK | 9 | 125.67 |
| CHK | 10 | 23575.12 |
| CHK | 12 | 38552.05 |
| MM | 3 | 2212.50 |
| MM | 4 | 5487.09 |
| MM | 9 | 9345.55 |
| SAV | 1 | 500.00 |
| SAV | 2 | 200.00 |
| SAV | 4 | 767.77 |
| SAV | 8 | 387.99 |
+----------------+-----------+-----------------------+
21 rows in set (0.09 sec)
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31