SQL 过滤数据

使用WHERE子句

      数据库表一般包含大量的数据，很少需要检索表中的所有行。通常只会根据特定操作或报告的需要提取表数据的子集。只检索所需数据需要指定搜索条件，搜索条件也称为过滤条件。
      在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤。WHERE子句在表名（FROM子句）之后给出，如下所示：

SELECT prod_name, prod_price
FROM   Products
WHERE  prod_price = 3.49;

      这条语句从products表中检索两个列，但不返回所有行，只返回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

      这个示例使用了简单的相等检验：检查这一列的值是否为指定值，据此过滤数据。不过，SQL不止能测试等于，还能做更多的事情。

我们在做相等检验时看到了第一个WHERE子句，它确定一个列是否包含指定的值。SQL支持列出的所有条件操作符。
                                                                              表1  WHERE子句操作符

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

SELECT prod_name, prod_price
FROM   Products
WHERE  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
8 inch teddy bear  5.99
12 inch teddy bear  8.99
Raggedy Ann  4.99
King doll  9.49
Queen doll  9.49

下一条语句检索所有价格小于等于10美元的产品（因为没有价格恰好是10美元的产品，所以结果与前一个例子相同）：

SELECT prod_name, prod_price
FROM   Products
WHERE  prod_price <= 10;

不匹配检查

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

SELECT vend_id, prod_name
FROM   Products
WHERE  vend_id <> 'DLL01';

输出

vend_id  prod_name
---------- ------------------
BRS01  8 inch teddy bear
BRS01  12 inch teddy bear
BRS01  18 inch teddy bear
FNG01  King doll
FNG01  Queen doll

      如果仔细观察上述WHERE子句中的条件，会看到有的值括在单引号内，而有的值未括起来。单引号用来限定字符串。如果将值与字符串类型的列进行比较，就需要限定引号。用来与数值列进行比较的值不用引号。
下面是相同的例子，其中使用!=而不是<>操作符：

SELECT  vend_id, prod_name
FROM   Products
WHERE  vend_id != 'DLL01';

范围值检查

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

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

       从这个例子可以看到，在使用BETWEEN时，必须指定两个值——所需范围的低端值和高端值。这两个值必须用A ND关键字分隔。BETWEEN匹配范围中所有的值，包括指定的开始值和结束值。
空值检查
      在创建表时，表设计人员可以指定其中的列能否不包含值。在一个列不包含值时，称其包含空值NULL。无值（no value），它与字段包含0、空字符串或仅仅包含空格不同。确定值是否为NULL，不能简单地检查是否= NULL。SELECT语句有一个特殊的WHERE子句，可用来检查具有NULL值的列。这个WHERE子句就是IS NULL子句。其语法如下：
输入

SELECT prod_name
FROM   Products
WHERE  prod_price IS NULL;

      这条语句返回所有没有价格（空prod_price字段，不是价格为0）的产品，由于表中没有这样的行，所以没有返回数据。但是，Customers表确实包含具有NULL值的列：如果没有电子邮件地址，则cust_email列将包含NULL值：

SELECT cust_name
FROM   CUSTOMERS
WHERE  cust_email IS NULL;

输出

cust_name
----------
Kids  Place
The   Toy Store

过滤数据时，一定要验证被过滤列中含NULL的行确实出现在返回的数据中。

高级数据过滤

      为了进行更强的过滤控制，SQL允许给出多个WHERE子句。这些子句有两种使用方式，即以AND子句或OR子句的方式使用。用来联结或改变WHERE子句中的子句的关键字，也称为逻辑操作符（logical operator）。

AND操作符

       要通过不止一个列进行过滤，可以使用A ND操作符给WHERE子句附加条件。下面的代码给出了一个例子：

SELECT prod_id, prod_price, prod_name
FROM   Products
WHERE  vend_id = 'DLL01' AND prod_price <= 4;

       此SQL语句检索由供应商DLL01制造且价格小于等于4美元的所有产品的名称和价格。这条SELECT语句中的WHERE子句包含两个条件，用AND关键字联结在一起。AND指示DBMS只返回满足所有给定条件的行。如果某个产品由供应商DLL01制造，但价格高于4美元，则不检索它。类似地，如果产品价格小于4美元，但不是由指定供应商制造的也不被检索。这条SQL语句产生的输出如下：

prod_id  prod_price  prod_name
------- ---------- --------------------
BNBG02  3.4900  Bird bean bag toy
BNBG01  3.4900  Fish bean bag toy
BNBG03  3.4900  Rabbit bean bag toy

       这个例子只包含一个A ND子句，因此最多有两个过滤条件。可以增加多个过滤条件，每个条件间都要使用AND关键字。

OR操作符

      OR操作符与A ND操作符正好相反，它指示DBMS检索匹配任一条件的行。事实上，许多DBMS在OR WHERE子句的第一个条件得到满足的情况下，就不再计算第二个条件了（在第一个条件满足时，不管第二个条件是否满足，相应的行都将被检索出来）。
请看如下的SELECT语句：

SELECT prod_name, prod_price
FROM   Products
WHERE  vend_id = 'DLL01' OR vend_id = ‘BRS01’;

       此SQL语句检索由任一个指定供应商制造的所有产品的产品名和价格。OR操作符告诉DBMS匹配任一条件而不是同时匹配两个条件。如果这里
       使用的是A ND操作符，则没有数据返回（因为会创建没有匹配行的WHERE子句）。这条SQL语句产生的输出如下：

prod_name  prod_price
------------------- ----------
Fish bean bag toy  3.4900
Bird bean bag toy  3.4900
Rabbit  bean bag toy  3.4900
8 inch teddy bear  5.9900
12 inch teddy bear  8.9900
18 inch teddy bear  11.9900
Raggedy  Ann  4.9900

求值顺序

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

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.4900
Bird bean bag toy  3.4900
Rabbit bean bag toy  3.4900
18 inch teddy bear  11.9900
Raggedy  Ann  4.9900

      请看上面的结果。返回的行中有4行价格小于10美元，显然，返回的行未按预期的进行过滤。为什么会这样呢？原因在于求值的顺序。SQL（像多数语言一样）在处理OR操作符前，优先处理A ND操作符。当SQL看到上述WHERE子句时，它理解为：由供应商BRS01制造的价格为10美元以上的所有产品，以及由供应商DLL01制造的所有产品，而不管其价格如何。换句话说，由于A ND在求值过程中优先级更高，操作符被错误地组合了。此问题的解决方法是使用圆括号对操作符进行明确分组。请看下面的SELECT语句及输出：

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.9900

      这条SELECT语句与前一条的唯一差别是，将前两个条件用圆括号括了起来。因为圆括号具有比A ND或OR操作符更高的求值顺序，所以DBMS首先过滤圆括号内的OR条件。这时，SQL语句变成了选择由供应商DLL01或BRS01制造的且价格在10美元及以上的所有产品，这正是我们希望的结果。
      任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符。不要过分依赖默认求值顺序，即使它确实如你希望的那样。使用圆括号没有什么坏处，它能消除歧义。

IN操作符

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

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.9900
18 inch teddy bear  11.9900
8 inch teddy bear  5.9900
Bird bean bag toy  3.4900
Fish bean bag toy  3.4900
Rabbit bean bag toy  3.4900
Raggedy Ann  4.9900

      此SELECT语句检索由供应商DLL01和BRS01制造的所有产品。IN操作符后跟由逗号分隔的合法值，这些值必须括在圆括号中。
       你可能会猜测IN操作符完成了与OR相同的功能，恭喜你猜对了！下面的SQL语句完成与上面的例子相同的工作：
输入

SELECT prod_name, prod_price
FROM   Products
WHERE  vend_id = 'DLL01' OR vend_id = 'BRS01'
ORDER  BY prod_name;

输出

prod_name  prod_price
------------------- ----------
12 inch teddy bear  8.9900
18 inch teddy bear  11.9900
8 inch teddy bear  5.9900
Bird bean bag toy  3.4900
Fish bean bag toy  3.4900
Rabbit bean bag toy  3.4900
Raggedy Ann  4.9900

为什么要使用IN操作符？其优点为：
   ● 在有很多合法选项时，IN操作符的语法更清楚，更直观。
   ● 在与其他A ND和OR操作符组合使用IN时，求值顺序更容易管理。
   ● IN操作符一般比一组OR操作符执行得更快（在上面这个合法选项很少的例子中，你看不出性能差异）。
   ● IN的最大优点是可以包含其他SELECT语句，能够更动态地建立WHERE子句。第11课会对此进行详细介绍。

NOT操作符

       WHERE子句中的NOT操作符有且只有一个功能，那就是否定其后所跟的任何条件。因为NOT从不单独使用（它总是与其他操作符一起使用），所以它的语法与其他操作符有所不同。NOT关键字可以用在要过滤的列前，而不仅是在其后。WHERE子句中用来否定其后条件的关键字。下面的例子说明NOT的用法。为了列出除DLL01之外的所有供应商制造的产品，可编写如下的代码：

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

     这里的NOT否定跟在其后的条件，因此，DBMS不是匹配vend_id为DLL01，而是匹配非DLL01之外的所有东西。上面的例子也可以使用<>操作符来完成，如下所示：

SELECT  prod_name
FROM  Products
WHERE  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

      为什么使用NOT？对于这里的这种简单的WHERE子句，使用NOT确实没有什么优势。但在更复杂的子句中，NOT是非常有用的。例如，在与IN操作符联合使用时，NOT可以非常简单地找出与条件列表不匹配的行。

用通配符进行过滤
      通配符，用来匹配值的一部分的特殊字符。通配符有什么用呢？例如，怎样搜索产品名中包含文本bean bag的所有产品？用简单的比较操作符肯定不行，必须使用通配符。利用通配符，可以创建比较特定数据的搜索模式。在这个例子中，如果你想找出名称包含bean bag的所有产品，可以构造一个通配符搜索模式，找出在产品名的任何位置出现bean bag的产品。
      搜索模式是由字面值、通配符或两者组合构成的搜索条件。通配符本身实际上是SQL的WHERE子句中有特殊含义的字符，SQL支持几种通配符。为在搜索子句中使用通配符，必须使用LIKE操作符。LIKE指示DBMS，后跟的搜索模式利用通配符匹配而不是简单的相等匹配进行比较。通配符搜索只能用于文本字段（串），非文本数据类型字段不能使用通配符搜索。

百分号（%）通配符

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

SELECT prod_id,  prod_name
FROM   Products
WHERE  prod_name  LIKE 'Fish%';

输出

prod_id   prod_name
------- ------------------
BNBG01  Fish bean bag toy

      此例子使用了搜索模式'Fish%'。在执行这条子句时，将检索任意以Fish起头的词。%告诉DBMS接受Fish之后的任意字符，不管它有多少字符。通配符可在搜索模式中的任意位置使用，并且可以使用多个通配符。下面的例子使用两个通配符，它们位于模式的两端：

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

       搜索模式'%bean bag%'表示匹配任何位置上包含文本bean bag的值，不论它之前或之后出现什么字符。通配符也可以出现在搜索模式的中间，虽然这样做不太有用。下面的例子找出以F起头、以y结尾的所有产品：

SELECT  prod_name
FROM    Products
WHERE   prod_name  LIKE 'F%y';

      需要特别注意，除了能匹配一个或多个字符外，%还能匹配0个字符。%代表搜索模式中给定位置的0个、1个或多个字符。通配符%看起来像是可以匹配任何东西，但有个例外，这就是NULL。子句WHERE prod_name LIKE '%'不会匹配产品名称为NULL的行。

下划线（_）通配符
      另一个有用的通配符是下划线（_）。下划线的用途与%一样，但它只匹配单个字符，而不是多个字符。如果使用的是Microsoft Access，需要使用?而不是_。

举一个例子：

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

       这个WHERE子句中的搜索模式给出了后面跟有文本的两个通配符。结果只显示匹配搜索模式的行：第一行中下划线匹配12，第二行中匹配18。8 inch teddy bear产品没有匹配，因为搜索模式要求匹配两个通配符而不是一个。对照一下，下面的SELECT语句使用%通配符，返回三行产品：

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
BNR3   18 inch teddy bear

与%能匹配0个字符不同，_总是刚好匹配一个字符，不能多也不能少。

方括号（[ ]）通配符
       方括号（[]）通配符用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符。
       与前面描述的通配符不一样，并不是所有DBMS都支持用来创建集合的[]。只有微软的Access和SQL Server支持集合。为确定你使用的DBMS是否支持集合，请参阅相应的文档。

例如，找出所有名字以J或M起头的联系人，可进行如下查询：

SELECT cust_contact
FROM   Customers
WHERE  cust_contact LIKE '[JM]%'
ORDER  BY cust_contact;

输出

cust_contact
-----------------
Jim     Jones
John   Smith
Michelle   Green

      此语句的WHERE子句中的模式为'[JM]%'。这一搜索模式使用了两个不同的通配符。[JM]匹配任何以方括号中字母开头的联系人名，它也只能匹配单个字符。因此，任何多于一个字符的名字都不匹配。[JM]之后的%通配符匹配第一个字符之后的任意数目的字符，返回所需结果。

     此通配符可以用前缀字符^（脱字号）来否定。例如，下面的查询匹配不以J或M起头的任意联系人名（与前一个例子相反）：

SELECT  cust_contact
FROM    Customers
WHERE   cust_contact LIKE '[^JM]%'
ORDER   BY cust_contact;

说明：Access中的否定集合

        如果使用的是Microsoft Access，需要用!而不是^来否定一个集合，因此，使用的是[!JM]而不是[^JM]。当然，也可以使用NOT操作符得出相同的结果。^的唯一优点是在使用多个WHERE子句时可以简化语法：

SELECT  cust_contact
FROM    Customers
WHERE   NOT cust_contact LIKE '[JM]%'
ORDER   BY cust_contact;

      正如所见，SQL的通配符很有用。但这种功能是有代价的，即通配符搜索一般比前面讨论的其他搜索要耗费更长的处理时间。这里给出一些使用通配符时要记住的技巧。
      不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处。把通配符置于开始处，搜索起来是最慢的。仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。