「SQL 数据分析系列」4. 过滤操作

写在前面：

大家好，我是强哥，一个热爱分享的技术狂。目前已有 12 年大数据与 AI 相关项目经验， 10 年推荐系统研究及实践经验。平时喜欢读书、暴走和写作。

业余时间专注于输出大数据、AI 等相关文章，目前已经输出了 40 万字的推荐系统系列精品文章，今年 6 月底会出版「构建企业级推荐系统：算法、工程实现与案例分析」一书。如果这些文章能够帮助你快速入门，实现职场升职加薪，我将不胜欢喜。

想要获得更多免费学习资料或内推信息，一定要看到文章最后喔。

内推信息

如果你正在看相关的招聘信息，请加我微信：liuq4360，我这里有很多内推资源等着你，欢迎投递简历。

免费学习资料

如果你想获得更多免费的学习资料，请关注同名公众号【数据与智能】，输入“资料”即可！

学习交流群

如果你想找到组织，和大家一起学习成长，交流经验，也可以加入我们的学习成长群。群里有老司机带你飞，另有小哥哥、小姐姐等你来勾搭！加小姐姐微信：epsila，她会带你入群。

第三章查询入门

到目前为止，本书已经在前两章中介绍了一些数据库查询的例子（select 语句），所以本章会介绍 select 语句的组成部分以及它们的作用。学习完本章内容之后，你应该就基本了解了如何检索、连接、过滤、分组和排序数据，在本书第四到第十章中会详细介绍这些内容。

查询机制

在分析 select 语句之前，让我们先了解一下 MySQL 服务器（或者其他任何数据库服务器）是如何执行查询的。如果你使用的是 mysql 命令行工具，并且已经输入了用户名和密码进行登录（如果 MySQL 服务器在另一台计算机上运行，还可能需要提供主机名），一旦服务器通过了用户名和密码的验证，就会为用户生成一个数据库连接。该连接由请求它的应用程序（在本例中是 mysql 工具）保持，直到应用程序释放连接（比如键入 quit 命令）或服务器关闭连接（比如服务器关闭时）才结束。MySQL 服务器的每个连接都有被分配一个标识符，当你第一次登录时会显示该标识符：

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 11

 Server version: 8.0.15 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

在本例中，我的连接 ID 是 11。如果出现什么问题，比如要停止运行了数小时的错误查询，那么这个信息对于数据管理员来说可能会很有用，所以你可能需要将这个 ID 记下来。

一旦服务器验证了输入的用户名和密码并向你发送了连接，你就可以执行查询了（以及其他 SQL 语句）。每次向服务器发送查询时，服务器都会在执行语句之前检查以下内容：

• 用户是否有执行该语句的权限？

• 用户是否有访问目标数据的权限？

• 撰写语句的语法是否正确？

如果你能够满足这几项要求，那么你的查询会被交给查询优化器，它的任务是确定执行查询的最有效方式。优化器会查看诸如连接 from 子句中指定的表的顺序以及可用的索引，然后选择一个执行方案，以供服务器执行查询。

注意：了解和影响数据库服务器如何选择执行方案是一个很有趣的话题，有许多人都对它感兴趣。对于使用 MySQL 的读者来说，建议阅读 Baron Schwartz 等人的 High Performance MySQL (O’Reilly)，里面介绍了如何生成索引、分析执行方案、通过查询提示（query hints）影响优化器以及优化服务器的初始参数。如果你使用的是 Oracle 数据库或 SQL Server，也有很多有关调优的书籍可供选择。

一旦服务器执行查询结束，结果集（result set）就会被返回给调用应用程序（本例中也就是 mysql 工具）。我在第一章中提到过，结果集实际上就是另一个包含行和列的表。如果查询没有返回任何结果，那么 mysql 工具将向你显示以下示例末尾的提示消息：

mysql> SELECT first_name, last_name

 -> FROM customer 

-> WHERE last_name = 'ZIEGLER';

Empty set (0.02 sec)

如果查询返回一行或多行数据，那么 mysql 工具将通过添加列标题并使用-、|和+符号在列周围构建边框来进行格式化输出，如下所示：

mysql> SELECT *  

-> FROM category;

+-------------+-------------+---------------------+

| category_id | name | last_update |

+-------------+-------------+---------------------+

| 1 | Action | 2006-02-15 04:46:27 |

| 2 | Animation | 2006-02-15 04:46:27 |

| 3 | Children | 2006-02-15 04:46:27 |

| 4 | Classics | 2006-02-15 04:46:27 |

| 5 | Comedy | 2006-02-15 04:46:27 |

| 6 | Documentary | 2006-02-15 04:46:27 |

| 7 | Drama | 2006-02-15 04:46:27 |

| 8 | Family | 2006-02-15 04:46:27 |

| 9 | Foreign | 2006-02-15 04:46:27 |

| 10 | Games | 2006-02-15 04:46:27 |

| 11 | Horror | 2006-02-15 04:46:27 |

| 12 | Music | 2006-02-15 04:46:27 |

| 13 | New | 2006-02-15 04:46:27 |

| 14 | Sci-Fi | 2006-02-15 04:46:27 |

| 15 | Sports | 2006-02-15 04:46:27 |

| 16 | Travel | 2006-02-15 04:46:27 |

+-------------+-------------+---------------------+

16 rows in set (0.02 sec)

此查询返回 category 表中所有行的所有三列数据。在最后一行数据之后，mysql 工具会显示一条消息提示返回了多少行数据，在本例中是 16 行。

查询语句

select 语句由几个组件（即子句 clauses）组成。虽然在使用 MySQL（select 子句）时只有一个组件是必需的，但一条查询通常至少会包含六个可用子句中的两个或三个。下表（3-1）显示了不同的子句及其用处：

 上表（3-1）所示的所有子句均符合 ANSI 规范。下面几节将深入探讨这六个主要查询子句的用法。

select 子句

尽管 select 子句是 select 语句的第一个子句，但它却是最后被数据库服务器处理的子句之一。因为在确定最终结果集中要包含的内容之前，需要先知道最终结果集中可能包含的所有列。因此，为了充分理解 select 子句的作用，你需要稍微了解一下 from 子句。以下是一个查询：

mysql> SELECT * -> FROM language;

+-------------+----------+---------------------+

| language_id | name | last_update |

+-------------+----------+---------------------+

| 1 | English | 2006-02-15 05:02:19 |

| 2 | Italian | 2006-02-15 05:02:19 |

| 3 | Japanese | 2006-02-15 05:02:19 |

| 4 | Mandarin | 2006-02-15 05:02:19 |

| 5 | French | 2006-02-15 05:02:19 |

| 6 | German | 2006-02-15 05:02:19 |

+-------------+----------+---------------------+

6 rows in set (0.03 sec)

在这个查询中，from 子句列出一个表（language），select 子句指示 language 表中的所有列（由*指定）都应该被包含在结果集中。这个查询可以用中文描述如下：

显示 language 表中的所有行列。 

除了通过星号字符（*）指定所有列外，还可以显式使用感兴趣的列，例如：

mysql> SELECT language_id, name, last_update 

-> FROM language;

+-------------+----------+---------------------+

| language_id | name | last_update |

+-------------+----------+---------------------+

| 1 | English | 2006-02-15 05:02:19 |

| 2 | Italian | 2006-02-15 05:02:19 |

| 3 | Japanese | 2006-02-15 05:02:19 |

| 4 | Mandarin | 2006-02-15 05:02:19 |

| 5 | French | 2006-02-15 05:02:19 |

| 6 | German | 2006-02-15 05:02:19 |

+-------------+----------+---------------------+

6 rows in set (0.00 sec)

结果与第一个查询语句的输出相同，因为 language 表中的所有列

（language_id、name 和 last_update）都在 select 子句中显式命名。你也可以选择仅包括 language 表中列的子集：

mysql> SELECT name

 -> FROM language;

+----------+

| name |

+----------+

| English |

| Italian |

| Japanese |

| Mandarin |

| French |

| German |

+----------+

6 rows in set (0.00 sec)

因此，select 子句的作用如下：

select 子句确定查询结果集中应该包括哪些可能的列。

如果你只包含 from 子句后面指定表中包含的列，那么就相当乏味了。所以你可以在 select 子句中添加以下内容：

• 字符，如数字或字符串；

• 表达式，例如 transaction.amount * −1；

• 内置函数调用，例如 ROUND(transaction.amount, 2)；

• 用户自定义的函数调用。

下一个查询演示如何在针对 employee 表的单个查询中应用列、字符、表达式和内置函数调用：

mysql> SELECT language_id,

 -> 'COMMON' language_usage, 

-> language_id * 3.1415927 lang_pi_value, 

-> upper(name) language_name 

-> FROM language;

+-------------+----------------+---------------+---------------+

| language_id | language_usage | lang_pi_value | language_name |

+-------------+----------------+---------------+---------------+

| 1 | COMMON | 3.1415927 | ENGLISH |

| 2 | COMMON | 6.2831854 | ITALIAN |

| 3 | COMMON | 9.4247781 | JAPANESE |

| 4 | COMMON | 12.5663708 | MANDARIN |

| 5 | COMMON | 15.7079635 | FRENCH |

| 6 | COMMON | 18.8495562 | GERMAN |

+-------------+----------------+---------------+---------------+

6 rows in set (0.04 sec)

稍后我们将详细介绍表达式和内置函数，本例是想让你大致了解 select 子句中可以包含哪些内容。如果你只需要执行内置函数或计算简单表达式，可以完全跳过 from 子句。举个例子：

mysql> SELECT version(),

 -> user(),

 -> database();

+-----------+----------------+------------+

| version() | user() | database() |

+-----------+----------------+------------+

| 8.0.15 | root@localhost | sakila |

+-----------+----------------+------------+

1 row in set (0.00 sec)

由于此查询只调用了三个内置函数，没有从任何表中检索数据，因此不需要用到 from 子句。

列别名

尽管 mysql 工具默认为查询返回的列生成标签，但你可能想用自己定义的标签。你可能因为表中列名含糊不清而想给它指定一个新标签，在结果集中包含根据表达式或内建函数调用产生的列的时候，你可能也想给这些列定义新的标签，这些都可以通过在 select 子句的每个元素后面添加列别名来实现。下面是对 language 表查询，与上一个查询相似，只不过为其中三列定义了列别名：

mysql> SELECT language_id,

 -> 'COMMON' language_usage,

 -> language_id * 3.1415927 lang_pi_value,

 -> upper(name) language_name

 -> FROM language;

+-------------+----------------+---------------+---------------+

| language_id | language_usage | lang_pi_value | language_name |

+-------------+----------------+---------------+---------------+

| 1 | COMMON | 3.1415927 | ENGLISH |

| 2 | COMMON | 6.2831854 | ITALIAN |

| 3 | COMMON | 9.4247781 | JAPANESE |

| 4 | COMMON | 12.5663708 | MANDARIN |

| 5 | COMMON | 15.7079635 | FRENCH |

| 6 | COMMON | 18.8495562 | GERMAN |

+-------------+----------------+---------------+---------------+

6 rows in set (0.04 sec)

仔细看一下 select 子句，可以看到如何在第二列、第三列和第四列之后添加了列别名 language_usage、lang_pi_value 和 language_name。你会觉得使用列别名能够让输出变得更容易理解，并且如果你是从 Java 或 Python 内部发出查询（而不是通过 mysql 工具交互式地发出查询），那么这样做更容易编程实现。为了更清楚地指定列别名，可以选择在别名之前使用 as 关键字，如：

mysql> SELECT language_id, 

-> 'COMMON' AS language_usage, 

-> language_id * 3.1415927 AS lang_pi_value, 

-> upper(name) AS language_name 

-> FROM language;

很多人觉得加上 as 关键字（可选的）可以提高可读性，不过在本书的示例中，我并不打算使用它。

去除冗余

在某些情况下，查询可能会返回重复的数据行。例如，如果要检索电影中出现的所有演员的 ID，结果如下：

mysql> SELECT actor_id FROM film_actor ORDER BY actor_id;

+----------+

| actor_id |

+----------+

| 1 |

| 1 |

| 1 |

| 1 |

| 1 |

| 1 |

| 1 |

| 1 |

| 1 |

| 1 |

...

| 200 |

| 200 |

| 200 |

| 200 |

| 200 |

| 200 |

| 200 |

| 200 |

| 200 |

+----------+

5462 rows in set (0.01 sec)

因为有些演员参演了多部电影，所以你会多次看到同一个演员的 ID。在这种情况下，你可能想看到不同的演员 ID，而不是重复出现的演员 ID，所以可以通过将关键字 distinct 直接添加到 select 关键字之后来实现，如下所示：

mysql> SELECT DISTINCT actor_id FROM film_actor ORDER BY actor_id;

+----------+

| actor_id |

+----------+

| 1 |

| 2 |

| 3 |

| 4 |

| 5 |

| 6 |

| 7 |

| 8 |

| 9 |

| 10 |

...

| 192 |

| 193 |

| 194 |

| 195 |

| 196 |

| 197 |

| 198 |

| 199 |

| 200 |

+----------+

200 rows in set (0.01 sec)

结果集现在包含 200 行，每个演员一行，而不是 5462 行。

注意：如果你只是想要一个所有演员的列表，那么可以直接查询 actor 表，而不是读取 film_actor 中的所有行并删除重复项。

如果不希望服务器删除重复的数据，或者你能确定结果集中不会有冗余数据，则可以指定 all 关键字，而不是 distinct。但是，all 关键字实际上是系统默认的，不需要显式命名，因此大多数程序员不会在查询包含 all 关键字。

注意：请记住，生成一组无重复结果需要对数据进行排序，这对于大型结果集来说可能非常耗时，所以不要只是为了确保没有重复数据而使用 distinct。你要做的是好好了解你所使用的数据（是否可能包含重复行），这样就能知道结果集是否可能出现冗余问题，以减少 distinct 的非必要使用。

from 子句

到目前为止，你看到过包含单个表的 from 子句查询。尽管大多数 SQL 书籍都将 from 子句定义为一个或多个表的清单，但本书对它的定义作拓展如下：

from 子句定义了查询所使用的表，以及连接这些表的方式。

这个定义由两个独立却相关的概念组成，我们将在下面几节中探讨这两个概念。

表

当遇到术语"表"的时候，大多数人会想到存储在数据库中的一组相关行。虽然这确实描述了一种类型的表，但我想用一种更广宽泛的方式来使用该术语，即去掉关于数据如何存储的概念，只关注一组相关的行。在这种宽泛的定义下，有下面四种类型的表：

• 永久表（使用 create table 语句创建的表）

• 派生表（子查询返回并保存在内存中的数据表）

• 临时表（存储在内存中的易丢失性数据）

• 虚拟表（使用 create view 语句创建的视图）

这几种类型的表都可以包含在查询的 from 子句中。现在你应该已经熟悉了在 from 子句中包含一个永久表，因此下面我将简要介绍如何在 from 子句中使用另外几种类型的表。

派生（子查询生成）表

子查询是包含在另一个查询中的查询。子查询用圆括号括起来，可以在 select 语句任何部分出现。但是，在 from 子句中，子查询的作用是生成一个派生表，其中的 from 子句可以与其他表进行交互。下面是一个简单的例子：

mysql> SELECT concat(cust.last_name, ', ', cust.first_name) full_name  

-> FROM  

-> (SELECT first_name, last_name, email  

-> FROM customer  

-> WHERE first_name = 'JESSIE'  

-> ) cust;

+---------------+

| full_name |

+---------------+

| BANKS, JESSIE |

| MILAM, JESSIE |

+---------------+

2 rows in set (0.00 sec)

在本例中，针对 customer 表的子查询返回三列，外围查询获得其中的两列。外围查询中，通过别名引用子查询，在本例中，别名是 cust。cust 中的数据在查询期间保存在内存中，然后被丢弃。这是一个特别简单甚至没什么用的例子，不过别担心，我会在第九章中详细介绍子查询的相关内容。

临时表

每个关系数据库都允许定义临时表（尽管其实现并不相同），这些表看起来和永久表差不多，但是插入到临时表中的任何数据最后都不会被保存下来（通常是事务结束时或数据库会话关闭时被丢弃）。下面是一个简单的示例，演示如何临时存储姓氏以 J 开头的演员：

mysql> CREATE TEMPORARY TABLE actors_j

 -> (actor_id smallint(5),  

-> first_name varchar(45),

 -> last_name varchar(45)  

-> );

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO actors_j

 -> SELECT actor_id, first_name, last_name

 -> FROM actor  

-> WHERE last_name LIKE 'J%';

Query OK, 7 rows affected (0.03 sec)

Records: 7 Duplicates: 0 Warnings: 0 

mysql> SELECT * FROM actors_j;

+----------+------------+-----------+

| actor_id | first_name | last_name |

+----------+------------+-----------+

| 119 | WARREN | JACKMAN |

| 131 | JANE | JACKMAN |

| 8 | MATTHEW | JOHANSSON |

| 64 | RAY | JOHANSSON |

| 146 | ALBERT | JOHANSSON |

| 82 | WOODY | JOLIE |

| 43 | KIRK | JOVOVICH |

+----------+------------+-----------+

7 rows in set (0.00 sec)

这七行临时保存在内存中，并将在会话关闭后消失。

注意：大多数数据库服务器在会话结束时也会删除临时表，不过 Oracle 数据库是个例外，它将临时表的定义保留，供未来的会话使用。

视图

视图是存储在数据字典中的查询，它看起来和表现得都像一个表，但实际上并没有与视图相关联的数据（所以我才叫它虚拟表）。对视图发出查询时，查询将与视图定义绑定，以创建最终要执行的查询。

下面是一个查询 employee 表的视图定义，其中包括四个可用列：

mysql> CREATE VIEW cust_vw AS

 -> SELECT customer_id, first_name, last_name, active

-> FROM customer;

Query OK, 0 rows affected (0.12 sec)

创建视图时，并不会产生或存任何数据：服务器只是将 select 语句折叠起来以备将来使用。现在视图既然已经存在，那么可以对其发出查询了，如下所示：

mysql> SELECT first_name, last_name  

-> FROM cust_vw

 -> WHERE active = 0;

+------------+-----------+

| first_name | last_name |

+------------+-----------+

| SANDRA | MARTIN |

| JUDITH | COX |

| SHEILA | WELLS |

| ERICA | MATTHEWS |

| HEIDI | LARSON |

| PENNY | NEAL |

| KENNETH | GOODEN |

| HARRY | ARCE | | NATHAN | RUNYON |

| THEODORE | CULP | |

MAURICE | CRAWLEY |

| BEN | EASTER |

| CHRISTIAN | JUNG |

| JIMMIE | EGGLESTON |

| TERRANCE | ROUSH |

+------------+-----------+

15 rows in set (0.00 sec)

可能出于多个理由创建视图，比如对用户隐藏列、简化复杂的数据库设计等。

表连接

与简单 from 子句定义的第二个不同之处在于：如果 from 子句中出现多个表，则还必须包括用于连接这些表的条件。这在 MySQL 或其他任何数据库服务器并不是强制要求的，但它是 ANSI 认可的连接多个表的方法，并且在各种数据库服务器中是最有可移植性的方法。在第五章和第十章中，我们将深入探讨如何连接多个表，这里为了满足你的好奇心，我就简单介绍一下：

mysql> SELECT customer.first_name, customer.last_name,

 -> time(rental.rental_date) rental_time  

-> FROM customer  

-> INNER JOIN rental  

-> ON customer.customer_id = rental.customer_id  

-> WHERE date(rental.rental_date) = '2005-06-14';

+------------+-----------+-------------+

| first_name | last_name | rental_time |

+------------+-----------+-------------+

| JEFFERY | PINSON | 22:53:33 |

| ELMER | NOE | 22:55:13 |

| MINNIE | ROMERO | 23:00:34 |

| MIRIAM | MCKINNEY | 23:07:08 |

| DANIEL | CABRAL | 23:09:38 |

| TERRANCE | ROUSH | 23:12:46 |

| JOYCE | EDWARDS | 23:16:26 |

| GWENDOLYN | MAY | 23:16:27 |

| CATHERINE | CAMPBELL | 23:17:03 |

| MATTHEW | MAHAN | 23:25:58 |

| HERMAN | DEVORE | 23:35:09 |

| AMBER | DIXON | 23:42:56 |

| TERRENCE | GUNDERSON | 23:47:35 | | SONIA | GREGORY | 23:50:11 |

| CHARLES | KOWALSKI | 23:54:34 |

| JEANETTE | GREENE | 23:54:46 |

+------------+-----------+-------------+

16 rows in set (0.01 sec)

这个查询同时显示 customer 表（first_name，last_name）和 rental 表（rental_date）中的数据，因此这两个表都包含在 from 子句中。连接这两个表（称为 join）的机制是存储在 customer 和 rental 表中的 customer ID。因此，数据库服务器使用 customer 表中 customer_id 列的值来查找 rental 表中客户的所有租金。两个表的连接条件由 from 子句的 on 子句指定。在本例中，连接条件是 ON customer.customer_id = rental.customer_id。where 子句不是连接的一部分，它只用于限定结果集使得数量保持得相当小，因为 rental 表中有 16000 多行数据。关于连接多个表的详细讨论，请参阅第五章。

定义表别名

当在单个查询中连接多个表时，要在 select、where、group by、having 和 order by 子句中引用列的时候指明所引用的表。在 from 子句之外引用表时，有两种方式：

• 使用完整的表名称，例如 employee.emp_id。

• 为每个表指定一个别名，并在整个查询过程中使用该别名。

在前面的查询中，我在 select 和 on 子句中使用了完整的表名。下面是使用定义表别名的方式时，实现相同查询的语句：

SELECT c.first_name, c.last_name,  

time(r.rental_date) rental_time

FROM customer c  

INNER JOIN rental r

 ON c.customer_id = r.customer_id

WHERE date(r.rental_date) = '2005-06-14';

如果仔细观察 from 子句，可以发现 customer 表被分配了别名 c，rental 表被分配了别名 r，然后，在定义连接条件时，在 on 子句中被用到，并且在 select 子句中为结果集指定要包含的列的时候也用到了它们。我觉得使用别名不仅能让语句更加紧凑，还能避免混淆（只要选择了合理的别名）。此外，还可以在表别名中使用 as 关键字，就跟之前显式指定列别名一样，显式指定表别名：

SELECT c.first_name, c.last_name,

 time(r.rental_date) rental_time

FROM customer AS c  

INNER JOIN rental AS r  

ON c.customer_id = r.customer_id

WHERE date(r.rental_date) = '2005-06-14';

我发现与我共事过的数据库开发人员中，大约有一半的人在列和表别名中使用 as 关键字，而另外一半的人不这样做。

where 子句

在某些情况下，可能需要从表中检索所有行，特别是对于诸如 language 之类的小型表。但是在大多数情况下，并没有检索表中所有数据的必要，而是希望使用某种方法过滤不感兴趣的行，这就是 where 子句起作用的地方。

where 子句用于从结果集中过滤掉不需要的行。

例如，也许你想租一部电影，但是你只对至少能租一周的 G 级电影感兴趣，那么可以使用以下查询：

mysql> SELECT title

 -> FROM film  

-> WHERE rating = 'G' AND rental_duration >= 7;

+-------------------------+

| title |

+-------------------------+

| BLANKET BEVERLY |

| BORROWERS BEDAZZLED |

| BRIDE INTRIGUE |

| CATCH AMISTAD |

| CITIZEN SHREK |

| COLDBLOODED DARLING |

| CONTROL ANTHEM |

| CRUELTY UNFORGIVEN |

| DARN FORRESTER |

| DESPERATE TRAINSPOTTING |

| DIARY PANIC |

| DRACULA CRYSTAL |

| EMPIRE MALKOVICH |

| FIREHOUSE VIETNAM |

| GILBERT PELICAN |

| GRADUATE LORD |

| GREASE YOUTH |

| GUN BONNIE |

| HOOK CHARIOTS |

| MARRIED GO |

| MENAGERIE RUSHMORE |

| MUSCLE BRIGHT |

| OPERATION OPERATION |

| PRIMARY GLASS |

| REBEL AIRPORT |

| SPIKING ELEMENT |

| TRUMAN CRAZY |

| WAKE JAWS | | WAR NOTTING |

+-------------------------+

29 rows in set (0.00 sec)

本例中，where 子句过滤了 film 表中 1000 行中的 971 行，产生了 29 行数据输出。where 子句包含了两个过滤条件，但你可以根据需要包含任意多个条件，它们之间使用 and、or 和 not 等操作符分隔（有关 where 子句和过滤条件的完整讨论，请参阅第四章）。

让我们看看如果将两个条件之间的 and 操作符改成 or 会怎么样：

mysql> SELECT title

 -> FROM film

 -> WHERE rating = 'G' OR rental_duration >= 7;

+---------------------------+

| title |

+---------------------------+

| ACE GOLDFINGER |

| ADAPTATION HOLES |

| AFFAIR PREJUDICE |

| AFRICAN EGG |

| ALAMO VIDEOTAPE |

| AMISTAD MIDSUMMER |

| ANGELS LIFE | | ANNIE IDENTITY |

|... |

| WATERSHIP FRONTIER |

| WEREWOLF LOLA |

| WEST LION |

| WESTWARD SEABISCUIT |

| WOLVES DESIRE |

| WON DARES |

| WORKER TARZAN |

| YOUNG LANGUAGE |

+---------------------------+

340 rows in set (0.00 sec)

使用 and 操作符分隔过滤条件时，所有过滤条件的计算结果都必须为 true 才能包含在结果集中；但是使用 or 时，只要满足一个条件为 true，该行就可以被包括到结果集中。因此上面结果集的大小从 29 行跃升到了 340 行。

那么，如果需要在 where 子句中同时使用 and 和 or 操作符又要怎么办？这种情况下就应该使用圆括号对条件进行分组。下一个查询指定在结果集中包括”只有级别为 G 且可租借 7 天或更长时间“或”级别为 PG-13 且租借时长不超过三天”的影片：

mysql> SELECT title, rating, rental_duration

 -> FROM film

 -> WHERE (rating = 'G' AND rental_duration >= 7)

 -> OR (rating = 'PG-13' AND rental_duration < 4);

+-------------------------+--------+-----------------+

| title | rating | rental_duration |

+-------------------------+--------+-----------------+

| ALABAMA DEVIL | PG-13 | 3 |

| BACKLASH UNDEFEATED | PG-13 | 3 |

| BILKO ANONYMOUS | PG-13 | 3 |

| BLANKET BEVERLY | G | 7 |

| BORROWERS BEDAZZLED | G | 7 |

| BRIDE INTRIGUE | G | 7 |

| CASPER DRAGONFLY | PG-13 | 3 |

| CATCH AMISTAD | G | 7 |

| CITIZEN SHREK | G | 7 |

| COLDBLOODED DARLING | G | 7 |

|... |

| TREASURE COMMAND | PG-13 | 3 |

| TRUMAN CRAZY | G | 7 |

| WAIT CIDER | PG-13 | 3 |

| WAKE JAWS | G | 7 |

| WAR NOTTING | G | 7 |

| WORLD LEATHERNECKS | PG-13 | 3 |

+-------------------------+--------+-----------------+

68 rows in set (0.00 sec)

在混合使用不同的运算符时，应该始终使用圆括号来将条件分隔成组，以便开发者、数据库服务器以及以后可能对代码进行修改的其他人都能对代码保持一致的理解。

group by 和 having 子句

到目前为止，所有的查询都仅仅是对原始数据的检索操作，没有执行任何的加工。但是有时候你可能需要数据库服务器在返回结果集之前对数据进行一些修改和提炼，其中一种方式就是使用 group by 子句，它根据列值对数据进行分组。例如，假设你想找到所有租了 40 部及以上电影的顾客，那么你可以编写一个查询，指示服务器按顾客对所有租赁进行分组，计算每个顾客的租赁数量，然后只返回那些租赁数量至少为 40 的顾客，而不是查看 rental 表中所有 16044 行数据。在使用 group by 子句生成行组时，还可能需要用到 having 子句，该子句允许你以与 where 子句相同的方式过滤分组数据。

查询如下：

mysql> SELECT c.first_name, c.last_name, count(*)

 -> FROM customer c

 -> INNER JOIN rental r

 -> ON c.customer_id = r.customer_id

 -> GROUP BY c.first_name, c.last_name  

-> HAVING count(*) >= 40;

+------------+-----------+----------+

| first_name | last_name | count(*) |

+------------+-----------+----------+

| TAMMY | SANDERS | 41 |

| CLARA | SHAW | 42 |

| ELEANOR | HUNT | 46 |

| SUE | PETERS | 40 |

| MARCIA | DEAN | 42 |

| WESLEY | BULL | 40 |

| KARL | SEAL | 45 |

+------------+-----------+----------+

7 rows in set (0.03 sec)

这里我只是简单介绍一下这两个子句，以便你在阅读本书后面的内容时不会感到太陌生，实际上它们比另外四个子句更高级一些。本书第八章会完整描述如何使用以及何时使用 group by 和 having 子句。

order by 子句

一般情况下，查询返回结果集中的行没有特定的顺序。如果希望对结果集进行排序，则需要指示服务器使用 order by 子句对结果进行排序，该子句定义如下：

order by 子句用于对结果集中原始列数据或根据列数据计算的表达式结果进行排序。

比如下面的查询，它返回 2005 年 6 月 14 日租赁过电影的所有客户：

mysql> SELECT c.first_name, c.last_name,

 -> time(r.rental_date) rental_time  

-> FROM customer c  

-> INNER JOIN rental r  

-> ON c.customer_id = r.customer_id

 -> WHERE date(r.rental_date) = '2005-06-14';

+------------+-----------+-------------+

| first_name | last_name | rental_time |

+------------+-----------+-------------+

| JEFFERY | PINSON | 22:53:33 |

| ELMER | NOE | 22:55:13 |

| MINNIE | ROMERO | 23:00:34 |

| MIRIAM | MCKINNEY | 23:07:08 |

| DANIEL | CABRAL | 23:09:38 |

| TERRANCE | ROUSH | 23:12:46 |

| JOYCE | EDWARDS | 23:16:26 |

| GWENDOLYN | MAY | 23:16:27 |

| CATHERINE | CAMPBELL | 23:17:03 |

| MATTHEW | MAHAN | 23:25:58 |

| HERMAN | DEVORE | 23:35:09 |

| AMBER | DIXON | 23:42:56 |

| TERRENCE | GUNDERSON | 23:47:35 |

| SONIA | GREGORY | 23:50:11 |

| CHARLES | KOWALSKI | 23:54:34 |

| JEANETTE | GREENE | 23:54:46 |

+------------+-----------+-------------+

16 rows in set (0.01 sec)

如果希望结果按姓氏的字母顺序排列，可以将 last_name 列添加到 order by 子句中：

mysql> SELECT c.first_name, c.last_name,

 -> time(r.rental_date) rental_time

 -> FROM customer c  

-> INNER JOIN rental r  

-> ON c.customer_id = r.customer_id

 -> WHERE date(r.rental_date) = '2005-06-14' 

 -> ORDER BY c.last_name;

+------------+-----------+-------------+

| first_name | last_name | rental_time |

+------------+-----------+-------------+

| DANIEL | CABRAL | 23:09:38 |

| CATHERINE | CAMPBELL | 23:17:03 |

| HERMAN | DEVORE | 23:35:09 |

| AMBER | DIXON | 23:42:56 |

| JOYCE | EDWARDS | 23:16:26 |

| JEANETTE | GREENE | 23:54:46 |

| SONIA | GREGORY | 23:50:11 |

| TERRENCE | GUNDERSON | 23:47:35 |

| CHARLES | KOWALSKI | 23:54:34 |

| MATTHEW | MAHAN | 23:25:58 |

| GWENDOLYN | MAY | 23:16:27 |

| MIRIAM | MCKINNEY | 23:07:08 |

| ELMER | NOE | 22:55:13 |

| JEFFERY | PINSON | 22:53:33 |

| MINNIE | ROMERO | 23:00:34 |

| TERRANCE | ROUSH | 23:12:46 |

+------------+-----------+-------------+

16 rows in set (0.01 sec)

在大型客户列表中，通常会出现多个姓氏相同的人，所以你可能需要扩展排序条件，以包含这些人的名字。

可以通过在 order by 子句的 last_name 列之后添加 first_name 列来实现：

mysql> SELECT c.first_name, c.last_name,

 -> time(r.rental_date) rental_time

 -> FROM customer c  

-> INNER JOIN rental r  

-> ON c.customer_id = r.customer_id

 -> WHERE date(r.rental_date) = '2005-06-14' 

 -> ORDER BY c.last_name, c.first_name;

+------------+-----------+-------------+

| first_name | last_name | rental_time |

+------------+-----------+-------------+

| DANIEL | CABRAL | 23:09:38 |

| CATHERINE | CAMPBELL | 23:17:03 |

| HERMAN | DEVORE | 23:35:09 |

| AMBER | DIXON | 23:42:56 |

| JOYCE | EDWARDS | 23:16:26 |

| JEANETTE | GREENE | 23:54:46 |

| SONIA | GREGORY | 23:50:11 |

| TERRENCE | GUNDERSON | 23:47:35 |

| CHARLES | KOWALSKI | 23:54:34 |

| MATTHEW | MAHAN | 23:25:58 |

| GWENDOLYN | MAY | 23:16:27 |

| MIRIAM | MCKINNEY | 23:07:08 |

| ELMER | NOE | 22:55:13

 | | JEFFERY | PINSON | 22:53:33 |

| MINNIE | ROMERO | 23:00:34 |

| TERRANCE | ROUSH | 23:12:46 |

+------------+-----------+-------------+

16 rows in set (0.01 sec)

当包含多个列时，在 order by 子句中各列的出现顺序决定对各列进行排序的次序。上面的例子中，如果在 order by 子句中切换了两列的顺序，则 Amber Dixon 将出现在结果集的第一行。

升序或降序排序

排序时，可以通过 asc 和 desc 关键字指定升序或降序。默认情况下为升序，因此如果要使用降序排序，则需要添加 desc 关键字。例如，以下查询按租赁时间降序显示 2005 年 6 月 14 日租赁电影的所有顾客：

mysql> SELECT c.first_name, c.last_name,  

-> time(r.rental_date) rental_time

 -> FROM customer c  -> INNER JOIN rental r  

-> ON c.customer_id = r.customer_id  

-> WHERE date(r.rental_date) = '2005-06-14' 

 -> ORDER BY time(r.rental_date) desc;

+------------+-----------+-------------+

| first_name | last_name | rental_time |

+------------+-----------+-------------+

| JEANETTE | GREENE | 23:54:46 |

| CHARLES | KOWALSKI | 23:54:34 |

| SONIA | GREGORY | 23:50:11 |

| TERRENCE | GUNDERSON | 23:47:35 |

| AMBER | DIXON | 23:42:56 |

| HERMAN | DEVORE | 23:35:09 |

| MATTHEW | MAHAN | 23:25:58 |

| CATHERINE | CAMPBELL | 23:17:03 |

| GWENDOLYN | MAY | 23:16:27 |

| JOYCE | EDWARDS | 23:16:26 |

| TERRANCE | ROUSH | 23:12:46 |

| DANIEL | CABRAL | 23:09:38 |

| MIRIAM | MCKINNEY | 23:07:08 |

| MINNIE | ROMERO | 23:00:34 |

| ELMER | NOE | 22:55:13 |

| JEFFERY | PINSON | 22:53:33 |

+------------+-----------+-------------+

16 rows in set (0.01 sec)

降序排序通常用于排行查询，例如“show me the top five account balances”。MySQL 包含一个 limit 子句，它允许你对数据进行排序，并只显示其中前 X 行。

根据表达式排序

如果使用 select 子句中的列进行排序，可以选择按列在 select 子句中的位置而不是按名称引用列，这对于根据表达式进行排序（比如前一个示例）很有用。比如，order by 子句使用 select 子句中的第三个元素指定降序排序：

mysql> SELECT c.first_name, c.last_name,  

-> time(r.rental_date) rental_time  

-> FROM customer c  

-> INNER JOIN rental r

 -> ON c.customer_id = r.customer_id  

-> WHERE date(r.rental_date) = '2005-06-14'  

-> ORDER BY 3 desc;

+------------+-----------+-------------+

| first_name | last_name | rental_time |

+------------+-----------+-------------+

| JEANETTE | GREENE | 23:54:46 |

| CHARLES | KOWALSKI | 23:54:34 |

| SONIA | GREGORY | 23:50:11 |

| TERRENCE | GUNDERSON | 23:47:35 |

| AMBER | DIXON | 23:42:56 |

| HERMAN | DEVORE | 23:35:09 |

| MATTHEW | MAHAN | 23:25:58 |

| CATHERINE | CAMPBELL | 23:17:03 |

| GWENDOLYN | MAY | 23:16:27 |

| JOYCE | EDWARDS | 23:16:26 |

| TERRANCE | ROUSH | 23:12:46 |

| DANIEL | CABRAL | 23:09:38 |

| MIRIAM | MCKINNEY | 23:07:08 |

| MINNIE | ROMERO | 23:00:34 |

| ELMER | NOE | 22:55:13 |

| JEFFERY | PINSON | 22:53:33 |

+------------+-----------+-------------+

16 rows in set (0.01 sec)

使用这个功能的时候还请务必小心，因为在 select 子句中添加列而不更改 order by 子句中的数字可能会导致意想不到的结果。就我个人而言，我在编写特殊查询时可能会按位置引用列，但在编写代码时，我总是按名称引用列。