读书笔记：SQL必知必会【第4课~第6课】

第4课 过滤数据

这一课程讲授如何使用SELECT语句的WHERE子句指定搜索条件。

4.1 使用WHERE子句

数据库表一般包含大量的数据，很少需要检索表中的所有行，通常只会根据特定操作或报告的需要提取表数据的子集，只检索所需数据需要指定搜索条件，也称为过滤条件。

在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤，WHERE子句在表名（FROM子句）之后给出，如下所示：

mysql> SELECT prod_name, prod_price    -> FROM Products    -> WHERE prod_price = 3.49    -> ;+---------------------+------------+| prod_name           | prod_price |+---------------------+------------+| Fish bean bag toy   |       3.49 || Bird bean bag toy   |       3.49 || Rabbit bean bag toy |       3.49 |+---------------------+------------+3 rows in set (0.00 sec)

这条语句从Products表中检索两个列，但不返回所有行，只返回prod_price值为3.49的行。使用了简单地相等检验：检查这一列的值是否为指定值，据此过滤数据。

在同时应用WHERE子句和ORDER BY子句的时候，应当让ORDER BY子句位于WHERE之后，否则会产生错误。

4.2 WHERE子句操作符

下表列出了SQL支持的所有WHERE子句条件操作符：

4.2.1 检查单个值

我们已经看了检验相等的例子，现在看看其他使用操作符的例子：列出所有价格小于10美元的产品：

mysql> SELECT prod_id, prod_price    -> FROM Products    -> WHERE prod_price < 10;+---------+------------+| prod_id | prod_price |+---------+------------+| BNBG01  |       3.49 || BNBG02  |       3.49 || BNBG03  |       3.49 || BR01    |       5.99 || BR02    |       8.99 || RGAN01  |       4.99 || RYL01   |       9.49 || RYL02   |       9.49 |+---------+------------+8 rows in set (0.00 sec)

4.2.2 不匹配检查

这个例子列出所有不是供应商DLL01制造的产品：

mysql> SELECT prod_id, vend_id    -> FROM Products    -> WHERE vend_id != 'DLL01';+---------+---------+| prod_id | vend_id |+---------+---------+| BR01    | BRS01   || BR02    | BRS01   || BR03    | BRS01   || RYL01   | FNG01   || RYL02   | FNG01   |+---------+---------+5 rows in set (0.00 sec)

4.2.3 范围值检查

要检查某个范围的值，可以使用BETWEEN操作符，其语法与其他WHERE子句的操作符稍微不同，因为它需要两个值，即范围的开始值和结束值。例如，BETWEEN操作符可用来检索价格在5美元和10美元之间的所有产品，或在指定的开始日期和结束日期之间的所有日期。

下面的例子检索价格在5美元和10美元之间的所有产品：

mysql> SELECT prod_name, prod_price    -> FROM Products    -> WHERE prod_price BETWEEN 5 AND 10;+--------------------+------------+| prod_name          | prod_price |+--------------------+------------+| 8 inch teddy bear  |       5.99 || 12 inch teddy bear |       8.99 || King doll          |       9.49 || Queen doll         |       9.49 |+--------------------+------------+4 rows in set (0.00 sec)

BETWEEN范围匹配所有的值，包括命令指定的开始值和结束值（本例为5和10）。

4.2.4 空值检查

在创建表时，表设计人员可以指定其中的列能否不包含值，当一个列不包含值，称其包含空值NULL。

确定值是否为NULL，不能简单检查是否=NULL，SELECT语句由一个特殊的WHERE子句，可用来检查具有NULL值的列，这个WHERE子句就是IS NULL子句，语法如下：

mysql> SELECT prod_name    -> FROM Products    -> WHERE prod_price IS NULL;Empty set (0.00 sec)

由于原表中不存在prod_price列空值的项，因此返回empty。

但是，CUSTOMER表中是含有NULL列的：

mysql> SELECT * FROM CUSTOMERS    -> ;+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+| cust_id    | cust_name     | cust_address         | cust_city | cust_state | cust_zip | cust_country | cust_contact       | cust_email            |+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+| 1000000001 | Village Toys  | 200 Maple Lane       | Detroit   | MI         | 44444    | USA          | John Smith         | [e-mail] || 1000000002 | Kids Place    | 333 South Lake Drive | Columbus  | OH         | 43333    | USA          | Michelle Green     | NULL                  || 1000000003 | Fun4All       | 1 Sunny Place        | Muncie    | IN         | 42222    | USA          | Jim Jones          | [e-mail]    || 1000000004 | Fun4All       | 829 Riverside Drive  | Phoenix   | AZ         | 88888    | USA          | Denise L. Stephens | [e-mail] || 1000000005 | The Toy Store | 4545 53rd Street     | Chicago   | IL         | 54545    | USA          | Kim Howard         | NULL                  |+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+5 rows in set (0.00 sec)

因此可以查询空值：

mysql> SELECT cust_name , cust_email    -> FROM Customers    -> WHERE cust_email IS NULL;+---------------+------------+| cust_name     | cust_email |+---------------+------------+| Kids Place    | NULL       || The Toy Store | NULL       |+---------------+------------+2 rows in set (0.00 sec)

第5课 高级数据过滤

这一课讲授如何组合WHERE子句以建立功能更强，更高级的搜索条件，还将学习如何使用NOT和IN操作符。

5.1 组合WHERE子句

第4课介绍的所有WHERE子句在过滤数据时，使用的都是单一的条件，为了进行更强的过滤控制，SQL允许给出多个WHERE子句，这些WHERE子句有两种使用方式，即AND和OR子句的方式。

5.1.1 AND操作符

要通过不止一个列进行过滤，可以使用AND操作符给WHERE子句附加条件：

mysql> SELECT prod_id,prod_price,prod_name    -> FROM Products    -> WHERE vend_id = 'DLL01' AND prod_price <= 4;+---------+------------+---------------------+| prod_id | prod_price | prod_name           |+---------+------------+---------------------+| BNBG01  |       3.49 | Fish bean bag toy   || BNBG02  |       3.49 | Bird bean bag toy   || BNBG03  |       3.49 | Rabbit bean bag toy |+---------+------------+---------------------+3 rows in set (0.00 sec)

这里使用AND子句，将两个WHERE子句的过滤条件连接在一起，返回同时满足两个条件的行。
本例只有一个AND子句，支持两个过滤条件。实际应用也可以增加多个过滤条件，每个条件间都用AND相连。

5.1.2 OR操作符

OR操作符与AND操作符正好相反，它指示DBMS检索匹配任一条件的行：

mysql> SELECT prod_name,prod_price    -> FROM Products    -> WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';+---------------------+------------+| prod_name           | prod_price |+---------------------+------------+| Fish bean bag toy   |       3.49 || Bird bean bag toy   |       3.49 || Rabbit bean bag toy |       3.49 || 8 inch teddy bear   |       5.99 || 12 inch teddy bear  |       8.99 || 18 inch teddy bear  |      11.99 || Raggedy Ann         |       4.99 |+---------------------+------------+7 rows in set (0.00 sec)

5.1.3 求值顺序

WHERE子句可以包含任意数目的AND和OR操作符，允许两者结合进行复杂、高级的过程。
但是，组合AND和OR会带来一个有趣的问题，为了说明这个问题，举下例：假如需要列出价格为10美元及以上，且由DLL01或BRS01制造的所有产品。

错误示范：

mysql> SELECT prod_name,prod_price    -> FROM Products    -> WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' AND prod_price >=10;+---------------------+------------+| prod_name           | prod_price |+---------------------+------------+| Fish bean bag toy   |       3.49 || Bird bean bag toy   |       3.49 || Rabbit bean bag toy |       3.49 || 18 inch teddy bear  |      11.99 || Raggedy Ann         |       4.99 |+---------------------+------------+5 rows in set (0.00 sec)

显然，返回的结果有4行价格小于10美元，为什么会这样呢？明明是我先……咳。

问题在于求值的顺序，和大多数语言一样，SQL在处理OR操作符之前，优先处理AND操作符。

因此，需要使用圆括号对操作符进行分组：

mysql> SELECT prod_name,prod_price    -> FROM Products    -> WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price >=10;+--------------------+------------+| prod_name          | prod_price |+--------------------+------------+| 18 inch teddy bear |      11.99 |+--------------------+------------+1 row in set (0.00 sec)

这就对了，以上。

5.2 IN操作符

IN操作符用来指定条件范围，范围中每个条件都可以进行匹配，IN取一组由逗号分隔，并括在圆括号中的合法值，例子如下 ：

mysql> SELECT prod_name,prod_price    -> FROM Products    -> WHERE vend_id IN('DLL01','BRS01')    -> ORDER BY prod_name;+---------------------+------------+| prod_name           | prod_price |+---------------------+------------+| 12 inch teddy bear  |       8.99 || 18 inch teddy bear  |      11.99 || 8 inch teddy bear   |       5.99 || Bird bean bag toy   |       3.49 || Fish bean bag toy   |       3.49 || Rabbit bean bag toy |       3.49 || Raggedy Ann         |       4.99 |+---------------------+------------+7 rows in set (0.00 sec)

可以看到，这个IN操作符实际上做到了可以用OR操作符的事情，不过，IN操作符有以下优点：

• 合法选项较多时IN更直观。
• 与其他AND和OR组合使用IN时，求值顺序更容易管理。
• IN操作符比一组OR操作符快得多。
• IN的最大优点：可以包含其他SELECT语句，可以更动态地建立WHERE子句。

5.3 NOT操作符

WHERE子句中的NOT操作符有且只有一个功能，那就是否定其后跟着的任何条件，由于NOT从不单独使用，而是总与其他操作符仪器使用，所以它的语法和其他操作符有所不同。

NOT关键字可以用在要过滤的列前，而不仅是放在后面。

下面的例子说明NOT的用法，列出除了DLL01之外的所有供应商制造的产品：

mysql> SELECT prod_name    -> FROM Products    -> WHERE NOT vend_id = 'DLL01'    -> ORDER BY prod_name;+--------------------+| prod_name          |+--------------------+| 12 inch teddy bear || 18 inch teddy bear || 8 inch teddy bear  || King doll          || Queen doll         |+--------------------+5 rows in set (0.00 sec)

这里，NOT否定了其后的“vent_id=’DLL01’”条件。

第6课 用通配符进行过滤

这一课介绍什么是通配符，如何使用通配符以及怎样使用LIKE操作符进行通配搜索。

6.1 LIKE操作符

前面介绍的所有操作符都是针对已知值进行过滤的，不管是匹配一个值还是多个值，检验大于还是小于已知值，或者检查某个范围的值，共同点是过滤中使用的值都是一致的。但是，并非任何时候这样的过滤方法都是好用的。有时我们需要利用通配符匹配条件查询的值中的一部分字符。

通配符本身实际上是SQL的WHERE子句中有特殊含义的字符，SQL支持几种通配符，而为了使用通配符必须使用LIKE操作符。

6.1.1 百分号（%）通配符

最常使用的通配符是百分号（%），在搜索串中，%表示任何字符出现任意次数。
下例查找出所有以词Fish起头的产品：

mysql> SELECT prod_name    -> FROM Products    -> WHERE prod_name LIKE 'Fish%';+-------------------+| prod_name         |+-------------------+| Fish bean bag toy |+-------------------+1 row in set (0.00 sec)

执行上例使用“Fish%”的子句时，检索的是prod_name列任何以Fish起头的词。
%告诉DBMS，接受Fish之后的任意字符，不管有多少、哪些字符。

对于Access，用的是*而不是%。

通配符可以再搜索模式中的任意位置使用，并且可以使用多个通配符，下例使用两个通配符，位于模式的两端：

mysql> SELECT prod_id , prod_name    -> FROM Products    -> WHERE prod_name LIKE '%bean bag%';+---------+---------------------+| prod_id | prod_name           |+---------+---------------------+| BNBG01  | Fish bean bag toy   || BNBG02  | Bird bean bag toy   || BNBG03  | Rabbit bean bag toy |+---------+---------------------+3 rows in set (0.00 sec)

通配符也可以出现在搜索模式的中间，不赘述。

需要注意的是，%不会匹配NULL行。

6.1.2 下划线（_）通配符

下划线的用途与%医院，但它只能匹配单个字符而不是多个：

mysql> SELECT prod_id , prod_name    -> FROM Products    -> WHERE prod_name LIKE '__inch teddy bear';+---------+-------------------+| prod_id | prod_name         |+---------+-------------------+| BR01    | 8 inch teddy bear |+---------+-------------------+1 row in set (0.00 sec)mysql> SELECT prod_id , prod_name    -> FROM Products    -> WHERE prod_name LIKE '___inch teddy bear';+---------+--------------------+| prod_id | prod_name          |+---------+--------------------+| BR02    | 12 inch teddy bear || BR03    | 18 inch teddy bear |+---------+--------------------+2 rows in set (0.00 sec)

可以看到，每一个下划线占据一个字符位置。
如果使用%的话，则可以返回全部三行：

mysql> SELECT prod_id , prod_name    -> FROM Products    -> WHERE prod_name LIKE '%inch teddy bear';+---------+--------------------+| prod_id | prod_name          |+---------+--------------------+| BR01    | 8 inch teddy bear  || BR02    | 12 inch teddy bear || BR03    | 18 inch teddy bear |+---------+--------------------+3 rows in set (0.00 sec)

6.1.3 方括号（[]）通配符

方括号（[]）通配符用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符。需要注意的是，并非所有DBMS都支持这样的集合，只有Access和SQL Server支持。

Mysql中无法使用，故略过此段。

6.2 使用通配符的技巧：

SQL的通配符很有用，但这种功能是有代价的：通常是更久的处理时间。
一些使用通配符的技巧如下：

• 不要过度使用通配符，其他操作符能达到相同目的的场合，就尽量不用通配符。
• 在确实需要通配符时，也不要把它们用在搜索模式的开始处：把通配符至于所搜索模式的开始处，搜索起来是最慢的。
• 注意通配符的位置。