数据库基础之高级数据过滤

目录

1、组合where子句

1.1 AND操作符

1.2 OR操作符

1.3 求值顺序

2、IN操作符

3、NOT操作符

---

1、组合where子句

首先列举下where中支持的操作符：

表1-1 WHERE子句操作符

操作符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
!<	不小于
>	大于
>=	大于等于
!>	不大于
between	在指定的两个值之间
IS NULL	为NULL值

注意：操作符兼容 表1-1中列出的某些操作符是冗余的（如< >与!=相同，!<相当于>=）。并非所有DBMS都支持这些操作符。

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

1.1 AND操作符

要通过不止一个列进行过滤，可以使用AND操作符给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

1.2 OR操作符

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

事实上，许多DBMS在OR WHERE子句的第一个条件得到满足的情况下，就不再计算第二个条件了（在第一个条件满足时，不管第二个条件是否满足，相应的行都将被检索出来）。

请看如下的SELECT语句：

输入:

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

此SQL语句检索由任一个指定供应商制造的所有产品的产品名和价格。OR操作符告诉DBMS匹配任一条件而不是同时匹配两个条件。如果这里使用的是AND操作符，则没有数据返回（因为会创建没有匹配行的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

1.3 求值顺序

WHERE子句可以包含任意数目的AND和OR操作符。允许两者结合以进行复杂、高级的过滤。

但是，组合AND和OR会带来了一个有趣的问题。为了说明这个问题，来看一个例子。假如需要列出价格为10美元及以上，且由DLL01或BRS01制造的所有产品。下面的SELECT语句使用组合的AND和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操作符前，优先处理AND操作符。当SQL看到上述WHERE子句时，它理解为：由供应商BRS01制造的价格为10美元以上的所有产品，以及由供应商DLL01制造的所有产品，而不管其价格如何。换句话说，由于AND在求值过程中优先级更高，操作符被错误地组合了。 

解决方案:

SELECT prod_name, prod_price FROM Products 
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01')  AND prod_price >= 10; 

提示：

在WHERE子句中使用圆括号 任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符。不要过分依赖默认求值顺序，即使它确实如你希望的那样。使用圆括号没有什么坏处，它能消除歧义。

2、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相同的功能。

为什么要使用IN操作符？

1. 在有很多合法选项时，IN操作符的语法更清楚，更直观。 
2. 在与其他AND和OR操作符组合使用IN时，求值顺序更容易管理。
3. IN操作符一般比一组OR操作符执行得更快。 
4. IN的最大优点是可以包含其他SELECT语句，能够更动态地建立WHERE子句。

3、NOT操作符

WHERE子句中的NOT操作符有且只有一个功能，那就是否定其后所跟的任何条件。因为NOT从不单独使用（它总是与其他操作符一起使用），所以它的语法与其他操作符有所不同。NOT关键字可以用在要过滤的列前，而不仅是在其后。

下面的例子说明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之外的所有东西。