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

最新推荐文章于 2022-04-10 15:35:31 发布

数据与智能

最新推荐文章于 2022-04-10 15:35:31 发布

阅读量420

点赞数

文章标签：字符串人工智能数据库 python mysql

本文链接：https://blog.csdn.net/qq_43045873/article/details/115911567

版权

来源 | Learning SQL Generate, Manipulate, and Retrieve Data， Third Edition

作者 | Alan Beaulieu

译者 | Liangchu

校对 | gongyouliu

编辑 | auroral-L

全文共10750字，预计阅读时间50分钟。

第四章过滤操作

1. 条件评估

1.1 使用圆括号

1.2 使用操作符not

2. 构建条件

3. 条件类型

3.1 相等条件

3.1.1 不等条件

3.1.2 使用相等条件修改数据

3.2 范围条件

3.2.1 between 操作符

3.2.2 字符串范围

3.3 成员条件

3.3.1 使用子查询

3.3.2 使用 not in

3.4 匹配条件

3.4.1 使用通配符

3.4.2 使用正则表达式

4. Null：由四个字母组成的词

有时需要获取表中的所有行，例如：

• 提取表中的所有数据，用于建立新的数据仓库；

• 添加新列后，修改表中的所有行

• 从消息队列表中检索所有行

在这种情况下，SQL语句无需过滤任何行，所以并不需要where语句。但是，大多数情况下，你所关注的只是表中所有行的一个子集。因此，所有SQL数据语句（insert语句除外）都包含一个可选的where子句，该子句包含一个或多个用于限制SQL语句作用的行数的过滤条件。另外，select语句包含的having子句可以包括与分组数据有关的过滤条件。本章探讨可以在select、update和delete语句的where子句中使用的各种类型的过滤条件，至于select语句的having子句的过滤条件，我将在第八章中演示。

1. 条件评估

where子句可以包含一个或多个条件，由操作符and和or分隔。如果多个条件仅由and操作符分隔，则所有条件的计算结果都必须为true，才能将该行包括在结果集中。请看下面的where子句：

WHERE first_name = 'STEVEN' AND create_date > '2006-01-01'

根据这两个条件，只有名为Steven且创建日期在2006年1月1日之后的行才会包含在结果集中。尽管本例仅使用了两个条件，但无论where子句中有多少个条件，如果它们都由and操作符分隔，则它们的计算结果都必须为true，相应的行才能被包含到结果集中。

但是，如果where子句中的所有条件都由or操作符分隔，则只要其中一个条件的计算结果为true，就可以将该行包括在结果集中了。考虑以下两个条件：

WHERE first_name = 'STEVEN' OR create_date > '2006-01-01'

对于一行数据来说，只要符合下面一种情况就可以被包含在结果集中：

• 名字是Steven，创建日期是2006年1月1日之后（不包括当日）。

• 名字是Steven，创建日期是2006年1月1日或之前。

• 名字不是Steven，但是创建日期是2006年1月1日之后（不包括当日）。

下表（4-1）显示了包含两个由or操作符分隔的条件的where子句的所有可能结果：

在前面的示例中，不被包含在结果集中的唯一情况是：此人名字不是Steven，并且创建时间是2006年1月1日或之前。

1.1 使用圆括号

如果where子句包含三个或更多条件，并且同时用到了and和or操作符，则要使用圆括号使得语句的意思更容易理解（对其他数据库服务器以及阅读代码的其他开发者而言）。下面的where子句扩展了前面的示例，该过滤条件选择名字是Steven或姓Young，并且创建日期在2006年1月1日之后的数据行到结果集中：

WHERE (first_name = 'STEVEN' OR last_name = 'YOUNG')
 AND create_date > '2006-01-01'

这里有三个条件，对于数据行来说，第一个或者第二个条件值中至少有一个得是true，第三个条件的计算结果必须是true，该行才能被包括到结果集中。下表（4-2）显示了这三个条件组合的所有可能结果：

如你所见，where子句中的条件越多，服务器需要计算的组合就越多。在这种情况下，八个组合中只有三个组合产生的最终结果为true。

1.2 使用操作符not

希望你能理解前面包含三个条件的示例，接下来继续考虑以下修改：

WHERE NOT (first_name = 'STEVEN' OR last_name = 'YOUNG')
 AND create_date > '2006-01-01'

你有看出什么不同吗？这个例子中，我在第一组条件之前增加了not操作符。所以现在查找的不是“名字是Steven或姓Young，并且创建日期在2006年1月1日之后”的数据行了，而是只检索“名字不是Steven且姓氏不是Young，并且创建日期在2006年1月1日之后”的数据行。下表（4-3）显示了该示例可能的结果：

虽然对于数据库服务器来说，它能够轻而易举地处理包括not操作符的where子句，但是对于人来说，这通常比较麻烦，所以通常情况下人们较少使用它。本例中，可以重写where子句以避免使用not操作符：

WHERE first_name <> 'STEVEN' AND last_name <> 'YOUNG'
 AND create_date > '2006-01-01'

尽管对于数据库服务器来说，上面两个例子没什么差别，但是你可能会觉得后一个例子更好理解一些。

2. 构建条件

既然你已经知道了服务器是如何评估多个条件的，那么让我们回过头看看如何创建单个条件。条件由一个或多个表达式与一个或多个操作符组合而成。表达式可以是以下任一类型：

• 数字；

• 表或视图中的列；

• 字符串，比如'Maple Street'；

• 内置函数，比如concat('Learning', ' ', 'SQL')；

• 子查询；

• 表达式列表，比如('Boston', 'New York', 'Chicago')；

条件中使用的操作符包括：

• 比较运算符，如=、!=、<、>、<>、like、in和between

• 算术运算符，如+、-、*和/

下一节演示如何组合这些表达式和操作符来生成各种类型的条件。

3. 条件类型

有许多方法可以用于过滤不需要的数据，比如查找特定值、值集合或要包含或排除的值范围，还可以在处理字符串数据时使用各种模式搜索技术来查找部分匹配的行。接下来的四小节将详细探讨每种条件类型：

3.1 相等条件

你所编写或遇到的大部分过滤条件的格式都类似于'column = expression'，例如：

title = 'RIVER OUTLAW'
fed_id = '111-11-1111'
amount = 375.25
film_id = (SELECT film_id FROM film WHERE title = 'RIVER OUTLAW')

像这样的条件称为相等条件（equality condition），因为它们将一个表达式等同于另一个表达式。前三个示例将列等同于文字（两个字符串和一个数字），第四个示例将列等同于子查询返回的值。以下查询使用两个相等条件，一个在on子句（连接条件）中，另一个在where子句（过滤条件）中：

mysql> SELECT c.email
 -> FROM customer c
 -> INNER JOIN rental r
 -> ON c.customer_id = r.customer_id
 -> WHERE date(r.rental_date) = '2005-06-14';
+---------------------------------------+
| email |
+---------------------------------------+
| CATHERINE.CAMPBELL@sakilacustomer.org |
| JOYCE.EDWARDS@sakilacustomer.org |
| AMBER.DIXON@sakilacustomer.org |
| JEANETTE.GREENE@sakilacustomer.org |
| MINNIE.ROMERO@sakilacustomer.org |
| GWENDOLYN.MAY@sakilacustomer.org |
| SONIA.GREGORY@sakilacustomer.org |
| MIRIAM.MCKINNEY@sakilacustomer.org |
| CHARLES.KOWALSKI@sakilacustomer.org |
| DANIEL.CABRAL@sakilacustomer.org |
| MATTHEW.MAHAN@sakilacustomer.org |
| JEFFERY.PINSON@sakilacustomer.org |
| HERMAN.DEVORE@sakilacustomer.org |
| ELMER.NOE@sakilacustomer.org |
| TERRANCE.ROUSH@sakilacustomer.org |
| TERRENCE.GUNDERSON@sakilacustomer.org |
+---------------------------------------+
16 rows in set (0.03 sec)

此查询显示2005年6月14日租用电影的每位客户的电子邮件地址。

3.1.1 不等条件

另一种比较常见的条件类型是不等条件（inequality condition），它判断两个表达式不相等。下面是将上一个查询where子句中的过滤条件改为不等条件的语句示例：

mysql> SELECT c.email
 -> FROM customer c
 -> INNER JOIN rental r
 -> ON c.customer_id = r.customer_id
 -> WHERE date(r.rental_date) <> '2005-06-14';
+-----------------------------------+
| email |
+-----------------------------------+
| MARY.SMITH@sakilacustomer.org |
| MARY.SMITH@sakilacustomer.org |
| MARY.SMITH@sakilacustomer.org |
| MARY.SMITH@sakilacustomer.org |
| MARY.SMITH@sakilacustomer.org |
| MARY.SMITH@sakilacustomer.org |
| MARY.SMITH@sakilacustomer.org |
| MARY.SMITH@sakilacustomer.org |
| MARY.SMITH@sakilacustomer.org |
| MARY.SMITH@sakilacustomer.org |
...
| AUSTIN.CINTRON@sakilacustomer.org |
| AUSTIN.CINTRON@sakilacustomer.org |
| AUSTIN.CINTRON@sakilacustomer.org |
| AUSTIN.CINTRON@sakilacustomer.org |
| AUSTIN.CINTRON@sakilacustomer.org |
| AUSTIN.CINTRON@sakilacustomer.org |
| AUSTIN.CINTRON@sakilacustomer.org |
| AUSTIN.CINTRON@sakilacustomer.org |
+-----------------------------------+
16028 rows in set (0.03 sec)

此查询返回2005年6月14日以外任何日期租用电影的客户的所有电子邮件地址。在构造不等条件时，你可以选择使用!=或<>操作符。

3.1.2 使用相等条件修改数据

修改数据时通常会用到相等/不相等条件。例如，假设电影租赁公司每年都会移除旧帐户的行数据，那么就需要从rental表中删除出租日期为2004年的行。下面是实现方式：

DELETE FROM rental
WHERE year(rental_date) = 2004;

此语句仅包含一个相等条件。下面的示例使用两个不等条件删除出租日期不在2005年或2006年的所有行数据：

DELETE FROM rental
WHERE year(rental_date) <> 2005 AND year(rental_date) <> 2006;

注意

在编写delete和update语句的示例时，我尽可能保证不会修改任何一行数据。这样在执行这些语句后数据能保持不变，保证select语句的输出始终与本书中显示的一样。

由于MySQL会话在默认情况下处于自动提交模式（参见第十二章），因此如果有某个语句修改了示例数据，则无法回滚（撤消）对该数据所做的任何更改。当然你可以随意操纵你的示例数据，甚至可以完全删除这些数据然后重新运行脚本以填充表数据，但我还是会尽量保持数据不变。

3.2 范围条件

除了检查一个表达式是否等于（或不等于）另一个表达式外，还可以构建条件来检查表达式是否在某个范围内。这种情况通常用于数值型数据或时间数据。考虑以下查询：

mysql> SELECT customer_id, rental_date 
 -> FROM rental 
 -> WHERE rental_date < '2005-05-25'; 
+-------------+---------------------+ 
| customer_id | rental_date | 
+-------------+---------------------+ 
| 130 | 2005-05-24 22:53:30 | 
| 459 | 2005-05-24 22:54:33 | 
| 408 | 2005-05-24 23:03:39 | 
| 333 | 2005-05-24 23:04:41 | 
| 222 | 2005-05-24 23:05:21 | 
| 549 | 2005-05-24 23:08:07 | 
| 269 | 2005-05-24 23:11:53 | 
| 239 | 2005-05-24 23:31:46 | 
+-------------+---------------------+ 
8 rows in set (0.00 sec)

此查询查找2005年5月25日之前所有的电影租赁数据。除了指定租赁日期的上限外，或许还需要指定日期的下限：

mysql> SELECT customer_id, rental_date 
 -> FROM rental 
 -> WHERE rental_date <= '2005-06-16' 
 -> AND rental_date >= '2005-06-14'; 
+-------------+---------------------+ 
| customer_id | rental_date | 
+-------------+---------------------+ 
| 416 | 2005-06-14 22:53:33 | 
| 516 | 2005-06-14 22:55:13 | 
| 239 | 2005-06-14 23:00:34 | 
| 285 | 2005-06-14 23:07:08 | 
| 310 | 2005-06-14 23:09:38 | 
| 592 | 2005-06-14 23:12:46 | 
... 
| 148 | 2005-06-15 23:20:26 | 
| 237 | 2005-06-15 23:36:37 | 
| 155 | 2005-06-15 23:55:27 | 
| 341 | 2005-06-15 23:57:20 | 
| 149 | 2005-06-15 23:58:53 | 
+-------------+---------------------+ 
364 rows in set (0.00 sec)

该查询检索2005年6月14日或15日所有的电影租赁数据。

3.2.1 between操作符

当范围既有上限又有下限时，可以选择使用between操作符构建一个查询条件，而不是组合使用两个单独的限制条件：

mysql> SELECT customer_id, rental_date
 -> FROM rental
 -> WHERE rental_date BETWEEN '2005-06-14' AND '2005-06-16';
+-------------+---------------------+
| customer_id | rental_date |
+-------------+---------------------+
| 416 | 2005-06-14 22:53:33 |
| 516 | 2005-06-14 22:55:13 |
| 239 | 2005-06-14 23:00:34 |
| 285 | 2005-06-14 23:07:08 |
| 310 | 2005-06-14 23:09:38 |
| 592 | 2005-06-14 23:12:46 |
...
| 148 | 2005-06-15 23:20:26 |
| 237 | 2005-06-15 23:36:37 |
| 155 | 2005-06-15 23:55:27 |
| 341 | 2005-06-15 23:57:20 |
| 149 | 2005-06-15 23:58:53 |
+-------------+---------------------+
364 rows in set (0.00 sec)

在使用between操作符时，需要注意：应始终先指定范围的下限（在between之后），然后指定范围的上限（在end之后）。如果错误地指定了它们出现的次序，会发生以下情况：

mysql> SELECT customer_id, rental_date
 -> FROM rental
 -> WHERE rental_date BETWEEN '2005-06-16' AND '2005-06-14';
Empty set (0.00 sec)

如你所见，结果没有数据返回，这是因为服务器实际上使用<=和>=操作符从你给出的单个条件生成两个条件，如下：

SELECT customer_id, rental_date
 -> FROM rental
 -> WHERE rental_date >= '2005-06-16' 
 -> AND rental_date <= '2005-06-14'
Empty set (0.00 sec)

由于不可能出现一个既大于2005年6月16日又小于2005年6月14日的日期，所以查询返回一个空集。这里还应该注意到使用between操作符时的第二个注意事项，也就是：范围的上下限是闭合的，也即上限和下限的值本身都被包含在范围内。在本例中，我希望输出返回6月14日或15日租用的所有影片，因此我指定2005-06-14为范围的下限，2005-06-16为上限。由于我没有指定日期的时间部分，因此时间默认为半夜，故而有效范围为2005-06-14 00:00:00到2005-06-16 00:00:00，这将包括6月14日或15日的所有租借情况。

除了日期之外，还可以构建条件来指定数字的范围，这种方式相当容易掌握，如下所示：

mysql> SELECT customer_id, payment_date, amount
 -> FROM payment
 -> WHERE amount BETWEEN 10.0 AND 11.99;
+-------------+---------------------+--------+
| customer_id | payment_date | amount |
+-------------+---------------------+--------+
| 2 | 2005-07-30 13:47:43 | 10.99 |
| 3 | 2005-07-27 20:23:12 | 10.99 |
| 12 | 2005-08-01 06:50:26 | 10.99 |
| 13 | 2005-07-29 22:37:41 | 11.99 |
| 21 | 2005-06-21 01:04:35 | 10.99 |
| 29 | 2005-07-09 21:55:19 | 10.99 |
...
| 571 | 2005-06-20 08:15:27 | 10.99 |
| 572 | 2005-06-17 04:05:12 | 10.99 |
| 573 | 2005-07-31 12:14:19 | 10.99 |
| 591 | 2005-07-07 20:45:51 | 11.99 |
| 592 | 2005-07-06 22:58:31 | 11.99 |
| 595 | 2005-07-31 11:51:46 | 10.99 |
+-------------+---------------------+--------+
114 rows in set (0.01 sec)

10美元到11.99美元之间的所有付款都将被退回。同样，请先指定范围的下限。

3.2.2 字符串范围

虽然日期和数字的范围很容易理解，但同样可以构建搜索字符串范围的条件，这一点可能不是那么好理解。比如：你正在搜索姓氏在某个范围内的客户。下面的查询返回姓氏介于FA和FR之间的客户：

mysql> SELECT last_name, first_name 
 -> FROM customer 
 -> WHERE last_name BETWEEN 'FA' AND 'FR'; 
+------------+------------+ 
| last_name | first_name | 
+------------+------------+ 
| FARNSWORTH | JOHN | 
| FENNELL | ALEXANDER | 
| FERGUSON | BERTHA | 
| FERNANDEZ | MELINDA | 
| FIELDS | VICKI | 
| FISHER | CINDY | 
| FLEMING | MYRTLE | 
| FLETCHER | MAE | 
| FLORES | JULIA | 
| FORD | CRYSTAL | 
| FORMAN | MICHEAL | 
| FORSYTHE | ENRIQUE | 
| FORTIER | RAUL | 
| FORTNER | HOWARD | 
| FOSTER | PHYLLIS | 
| FOUST | JACK | 
| FOWLER | JO | 
| FOX | HOLLY | 
+------------+------------+ 
18 rows in set (0.00 sec)

虽然有五个客户的姓氏以FR开头，但结果中不包括他们，因为像FRANKLIN这样的名字超出了范围。但是，我们可以通过将右侧范围扩大到FRB来将五个客户中的四个包括在内：

mysql> SELECT last_name, first_name
 -> FROM customer
 -> WHERE last_name BETWEEN 'FA' AND 'FRB';
+------------+------------+
| last_name | first_name |
+------------+------------+
| FARNSWORTH | JOHN |
| FENNELL | ALEXANDER |
| FERGUSON | BERTHA |
| FERNANDEZ | MELINDA |
| FIELDS | VICKI |
| FISHER | CINDY |
| FLEMING | MYRTLE |
| FLETCHER | MAE |
| FLORES | JULIA |
| FORD | CRYSTAL |
| FORMAN | MICHEAL |
| FORSYTHE | ENRIQUE |
| FORTIER | RAUL |
| FORTNER | HOWARD |
| FOSTER | PHYLLIS |
| FOUST | JACK |
| FOWLER | JO |
| FOX | HOLLY |
| FRALEY | JUAN |
| FRANCISCO | JOEL |
| FRANKLIN | BETH |
| FRAZIER | GLENDA |
+------------+------------+
22 rows in set (0.00 sec)

使用字符串范围的时候，需要知道字符集中各个字符的顺序（某个字符集中各字符的次序叫做排序顺序(collation)）。

3.3 成员条件

在某些情况下，不需要将表达式限制为特定值或某个范围值，而是限制为一组有限值集合。例如，要定位所有分级为“G”或“PG”的影片：

mysql> SELECT title, rating
 -> FROM film
 -> WHERE rating = 'G' OR rating = 'PG';
+---------------------------+--------+
| title | rating |
+---------------------------+--------+
| ACADEMY DINOSAUR | PG |
| ACE GOLDFINGER | G |
| AFFAIR PREJUDICE | G |
| AFRICAN EGG | G |
| AGENT TRUMAN | PG |
| ALAMO VIDEOTAPE | G |
| ALASKA PHANTOM | PG |
| ALI FOREVER | PG |
| AMADEUS HOLY | PG |
...
| WEDDING APOLLO | PG |
| WEREWOLF LOLA | G |
| WEST LION | G |
| WIZARD COLDBLOODED | PG |
| WON DARES | PG |
| WONDERLAND CHRISTMAS | PG |
| WORDS HUNTER | PG |
| WORST BANGER | PG |
| YOUNG LANGUAGE | G |
+---------------------------+--------+

虽然上例中where子句（两个条件由or组合在一起）的生成并不繁琐，但是想象一下，如果表达式集包含10或20个成员，那么处理将会很麻烦。对于这些情况，可以改用in操作符：

SELECT title, rating
FROM film
WHERE rating IN ('G','PG');

使用in操作符，无论集合中有多少个表达式，都可以由一个条件语句完成。

3.3.1 使用子查询

除了编写自己的表达式集，例如('G','PG')，还可以使用子查询来动态生成一个表达式集。例如，假设标题中包含字符串“PET”的所有影片都适合一家人观看，那么可以对film表进行子查询来检索与这些影片相关联的所有分级，然后检索具有指定分级的所有影片：

mysql> SELECT title, rating
 -> FROM film
 -> WHERE rating IN (SELECT rating FROM film WHERE title LIKE '%PET%');
+---------------------------+--------+
| title | rating |
+---------------------------+--------+
| ACADEMY DINOSAUR | PG |
| ACE GOLDFINGER | G |
| AFFAIR PREJUDICE | G |
| AFRICAN EGG | G |
| AGENT TRUMAN | PG |
| ALAMO VIDEOTAPE | G |
| ALASKA PHANTOM | PG |
| ALI FOREVER | PG |
| AMADEUS HOLY | PG |
...
| WEDDING APOLLO | PG |
| WEREWOLF LOLA | G |
| WEST LION | G |
| WIZARD COLDBLOODED | PG |
| WON DARES | PG |
| WONDERLAND CHRISTMAS | PG |
| WORDS HUNTER | PG |
| WORST BANGER | PG |
| YOUNG LANGUAGE | G |
+---------------------------+--------+
372 rows in set (0.00 sec)

子查询返回集合'G'和'PG'，主查询检查是否可以在子查询返回的集合中找到rating列的值。

3.3.2 使用not in

有时你想知道某特定表达式是否存在于一组表达式中，有时你也想知道该表达式是否不存在于该组表达式中。对于这些情况，可以使用not in操作符：

SELECT title, rating 
FROM film 
WHERE rating NOT IN ('PG-13','R', 'NC-17');

此查询查找所有评级不是'PG-13' ，'R'，或'NC-17'的电影记录，它将返回与上个查询一样的327行结果集。

3.4 匹配条件

到目前为止，本章已经向你介绍了识别单个字符串、字符串范围或字符串集合的条件，最后要介绍的条件类型是处理部分字符串匹配。例如，你可能希望查找姓氏以Q开头的所有客户，则可以使用内置函数去取last_name列的第一个字母，如下所示：

mysql> SELECT last_name, first_name
 -> FROM customer
 -> WHERE left(last_name, 1) = 'Q';
+-------------+------------+
| last_name | first_name |
+-------------+------------+
| QUALLS | STEPHEN |
| QUINTANILLA | ROGER |
| QUIGLEY | TROY |
+-------------+------------+
3 rows in set (0.00 sec)

虽然内置函数left()能完成这项工作，但是它的灵活度不高，因此你可以使用通配符来构建搜索表达式，如下一节所示。

3.4.1 使用通配符

搜索部分字符串匹配时，你可能会对下面的内容感兴趣：

• 以特定字符开头/结尾的字符串；

• 以子字符串开头/结尾的字符串；

• 在字符串的任意位置包含特定字符的字符串；

• 在字符串的任意位置包含子字符串的字符串；

• 具有特定格式且不考虑单个字符的字符串。

通过使用下表（4-4）所示的通配符，你可以构建搜索表达式来识别这些匹配项和许多其他部分字符串匹配项：

下划线字符可以代替单个字符，而百分号可以代替多个字符。在使用搜索表达式构建条件的时候，可以使用like操作符，如下所示：

mysql> SELECT last_name, first_name
 -> FROM customer
 -> WHERE last_name LIKE '_A_T%S';
+-----------+------------+
| last_name | first_name |
+-----------+------------+
| MATTHEWS | ERICA |
| WALTERS | CASSANDRA |
| WATTS | SHELLY |
+-----------+------------+
3 rows in set (0.00 sec)

上一个示例中的搜索表达式指定了第二个位置包含A，第四个位置包含T，后跟任意数量的字符，并且以S结尾的字符串。下表（4-5）展示了更多的搜索表达式及其解释：

使用通配符，可以很方便地构建简单的搜索表达式，但是，如果需要处理更复杂的情况，则可以使用多个搜索表达式，如下所示：

mysql> SELECT last_name, first_name
 -> FROM customer
 -> WHERE last_name LIKE 'Q%' OR last_name LIKE 'Y%';
+-------------+------------+
| last_name | first_name |
+-------------+------------+
| QUALLS | STEPHEN |
| QUIGLEY | TROY |
| QUINTANILLA | ROGER |
| YANEZ | LUIS |
| YEE | MARVIN |
| YOUNG | CYNTHIA |
+-------------+------------+
6 rows in set (0.00 sec)

该查询查找所有姓氏以Q或者Y开头的顾客。

3.4.2 使用正则表达式

如果你觉得带通配符的字符串仍然不够灵活，那么可以使用正则表达式来构建搜索表达式。正则表达式本质上也是一种搜索表达式。如果你不熟悉SQL，但使用过Perl等编程语言，那么你可能已经非常熟悉正则表达式了。如果你从未使用过正则表达式，那么你可能需要查阅Jeffrey E. F. Friedl的Mastering Regular Expressions (O’Reilly)，因为这本书涉及的内容太多了，所以本书不予介绍。

在MySQL中使用正则表达式实现前面的查询（查找姓氏以Q或Y开头的所有客户），如下所示：

mysql> SELECT last_name, first_name
 -> FROM customer
 -> WHERE last_name REGEXP '^[QY]';
+-------------+------------+
| last_name | first_name |
+-------------+------------+
| YOUNG | CYNTHIA |
| QUALLS | STEPHEN |
| QUINTANILLA | ROGER |
| YANEZ | LUIS |
| YEE | MARVIN |
| QUIGLEY | TROY |
+-------------+------------+
6 rows in set (0.16 sec)

regexp操作符接受一个正则表达式（本例中为'^[QY]'），并将其应用于条件左侧的表达式（last_name列）。查询现在只包含一个使用正则表达式的条件，而不是两个使用通配符的条件。

Oracle Database和Microsoft SQL Server也都支持正则表达式。对于Oracle Database，可以使用regexp_like函数而不是上一个示例中所示的regexp操作符，而SQL Server允许在like操作符中使用正则表达式。

4. Null：由四个字母组成的词

我尽可能将null这个令人感到模糊和恐惧的内容拖到后面讲，所以现在是时候面对这个问题了。null表示没有值，比如，在员工离职前，employee表中的end_date列应该是空值也就是null，在这种情况下，给end_date分配值是没有意义的。当然null的使用方式比较灵活，可以有不同的适用场景：

• 无合适值

例如，在ATM机上发生的交易并不需要employee表的employee ID。

• 值未知

例如，在创建客户行时还不知道他的federal ID。

• 值未定义

例如，为尚未添加到数据库的产品创建帐户。

注意

一些理论家认为，对于不同的情况应该提供不同的表达式来表示，但是大多实践专家认为这样做会带来更多困扰。

使用null时，应记住：

• 表达式可以为null，但不能等于null；

• 两个null永远不可判等。

要测试表达式是否为null，需要使用is null操作符，如下所示：

mysql> SELECT rental_id, customer_id 
 -> FROM rental 
 -> WHERE return_date IS NULL; 
+-----------+-------------+ 
| rental_id | customer_id | 
+-----------+-------------+ 
| 11496 | 155 | 
| 11541 | 335 | 
| 11563 | 83 | 
| 11577 | 219 | 
| 11593 | 99 | 
... 
| 15867 | 505 | 
| 15875 | 41 | 
| 15894 | 168 | 
| 15966 | 374 | 
+-----------+-------------+ 
183 rows in set (0.01 sec)

此查询查找所有没有归还的电影的租借情况。下面是使用=null而不是is null实现相同查询的语句：

mysql> SELECT rental_id, customer_id 
 -> FROM rental 
 -> WHERE return_date = NULL; 
Empty set (0.01 sec)

如你所见，该查询解析并执行后没有返回任何行，没有经验的SQL程序员常常会犯这种错误，并且数据库服务器也不会产生该错误相关的警告信息，所以在构建有关null的查询时应该谨慎。

如果要查看是否已将值指定给某列，可以使用is not null操作符，如下所示：

mysql> SELECT rental_id, customer_id, return_date 
 -> FROM rental 
 -> WHERE return_date IS NOT NULL; 
+-----------+-------------+---------------------+ 
| rental_id | customer_id | return_date | 
+-----------+-------------+---------------------+ 
| 1 | 130 | 2005-05-26 22:04:30 | 
| 2 | 459 | 2005-05-28 19:40:33 | 
| 3 | 408 | 2005-06-01 22:12:39 | 
| 4 | 333 | 2005-06-03 01:43:41 | 
| 5 | 222 | 2005-06-02 04:33:21 | 
| 6 | 549 | 2005-05-27 01:32:07 | 
| 7 | 269 | 2005-05-29 20:34:53 | 
... 
| 16043 | 526 | 2005-08-31 03:09:03 | 
| 16044 | 468 | 2005-08-25 04:08:39 | 
| 16045 | 14 | 2005-08-25 23:54:26 | 
| 16046 | 74 | 2005-08-27 18:02:47 | 
| 16047 | 114 | 2005-08-25 02:48:48 | 
| 16048 | 103 | 2005-08-31 21:33:07 | 
| 16049 | 393 | 2005-08-30 01:01:12 | 
+-----------+-------------+---------------------+ 
15861 rows in set (0.02 sec)

这个查询返回归还的所有租金，包含了表中的大多数行（16044行中有15861行）。

接着介绍有关null的一个易犯错误。假设你被要求查找2005年5月至8月期间未归还的所有租金。你的第一反应可能是：

mysql> SELECT rental_id, customer_id, return_date 
 -> FROM rental 
 -> WHERE return_date NOT BETWEEN '2005-05-01' AND '2005-09-01'; 
+-----------+-------------+---------------------+ 
| rental_id | customer_id | return_date | 
+-----------+-------------+---------------------+ 
| 15365 | 327 | 2005-09-01 03:14:17 | 
| 15388 | 50 | 2005-09-01 03:50:23 | 
| 15392 | 410 | 2005-09-01 01:14:15 | 
| 15401 | 103 | 2005-09-01 03:44:10 | 
| 15415 | 204 | 2005-09-01 02:05:56 | 
... 
| 15977 | 550 | 2005-09-01 22:12:10 | 
| 15982 | 370 | 2005-09-01 21:51:31 | 
| 16005 | 466 | 2005-09-02 02:35:22 | 
| 16020 | 311 | 2005-09-01 18:17:33 | 
| 16033 | 226 | 2005-09-01 02:36:15 | 
| 16037 | 45 | 2005-09-01 02:48:04 | 
| 16040 | 195 | 2005-09-02 02:19:33 | 
+-----------+-------------+---------------------+ 
62 rows in set (0.01 sec)

虽然这62笔租金确实是在5月到8月之外的日期退回的，但如果仔细观察数据，你会发现结果集中所有行的退回日期非空。那183笔从未归还的租金（也就是return_date为null）又怎么处理？有人可能会说，这183行在5月和8月之间也没有退回，因此它们也应该包含在结果集中。故而要正确回答问题，你需要考虑某些行在return_date列中可能包含null的情况：

mysql> SELECT rental_id, customer_id, return_date 
 -> FROM rental 
 -> WHERE return_date IS NULL 
 -> OR return_date NOT BETWEEN '2005-05-01' AND '2005-09-01'; 
+-----------+-------------+---------------------+ 
| rental_id | customer_id | return_date | 
+-----------+-------------+---------------------+ 
| 11496 | 155 | NULL | 
| 11541 | 335 | NULL | 
| 11563 | 83 | NULL | 
| 11577 | 219 | NULL | 
| 11593 | 99 | NULL | 
... 
| 15939 | 382 | 2005-09-01 17:25:21 | 
| 15942 | 210 | 2005-09-01 18:39:40 | 
| 15966 | 374 | NULL | 
| 15971 | 187 | 2005-09-02 01:28:33 | 
| 15973 | 343 | 2005-09-01 20:08:41 | 
| 15977 | 550 | 2005-09-01 22:12:10 | 
| 15982 | 370 | 2005-09-01 21:51:31 | 
| 16005 | 466 | 2005-09-02 02:35:22 | 
| 16020 | 311 | 2005-09-01 18:17:33 | 
| 16033 | 226 | 2005-09-01 02:36:15 | 
| 16037 | 45 | 2005-09-01 02:48:04 | 
| 16040 | 195 | 2005-09-02 02:19:33 | 
+-----------+-------------+---------------------+ 
245 rows in set (0.01 sec)

结果集现在包括了5月至8月之外退回的62份租金，以及183份尚未退回的租金，总共245行数据。在处理你不熟悉的数据库时，最好知道表中哪些列允许null值，这样你就可以对过滤条件采取适当的措施，以防止需要的数据被漏掉。