过滤数据
数据库表一般包含大量的数据,很少需要检索表中的所有行。通常只会根据特定操作或报告的需要提取表数据的子集。只检索所需数据需要指定 搜索条件( search criteria ),搜索条件也称为过滤条件( filter condition )。
使用 WHERE 子句
在SELECT语句中,数据根据WHERE子句中指定的搜索条件进行过滤。WHERE子句在表名(FROM子句)之后给出,如下所示:
SELECT prod_name,prod_priceFROM ProductsWHERE prod_price=3.49
这条语句从products表中检索两个列,但不返回所有行,只返回prod_price值为3.49的行,如下所示:
警告: WHERE 子句的位置
在同时使用ORDER BY和WHERE子句时,应该让ORDER BY位于WHERE之后,否则将会产生错误
范围值检查
要检查某个范围的值,可以使用BETWEEN操作符。其语法与其他WHERE子句的操作符稍有不同,因为它需要两个值,即范围的开始值和结束值。例如,BETWEEN操作符可用来检索价格在 5 美元和 10 美元之间的所有产品,或在指定的开始日期和结束日期之间的所有日期。
下面的例子说明如何使用BETWEEN操作符,它检索价格在 5 美元和 10 美元之间的所有产品:
SELECT prod_name,prod_price
FROM Products
WHERE prod_price BETWEEN 5 AND 10;
从这个例子可以看到,在使用BETWEEN时,必须指定两个值 —— 所需范围的低端值和高端值。这两个值必须用AND关键字分隔。BETWEEN匹配范围中所有的值,包括指定的开始值和结束值。
空值检查
在创建表时,表设计人员可以指定其中的列能否不包含值。在一个列不包含值时,称其包含空值NULL。
NULL
无值( no value ),它与字段包含 0 、空字符串或仅仅包含空格不同。
SELECT prod_name
FROM Products
WHERE prod_price IS NULL;
高级数据过滤
组合where语句
以上介绍的所有WHERE子句在过滤数据时使用的都是单一的条件。为了进行更强的过滤控制, SQL 允许给出多个WHERE子句。这些子句有两种使用方式,即以AND子句或OR子句的方式使用。
操作符( operator )
用来联结或改变WHERE子句中的子句的关键字,也称为 逻辑操作符( logical operator )。
AND 操作符
SELECT prod_id,prod_price,prod_nameFROM ProductsWHERE vend_id='DLL01' AND prod_price <= 4;
此 SQL 语句检索由供应商DLL01制造且价格小于等于 4 美元的所有产品的名称和价格。这条SELECT语句中的WHERE子句包含两个条件,用AND关键字联结在一起。AND指示 DBMS 只返回满足所有给定条件的行。如果某个产品由供应商DLL01制造,但价格高于 4 美元,则不检索它。类似地,如果产品价格小于 4 美元,但不是由指定供应商制造的也不被检索。这条 SQL 语句产生的输出如下:
这个例子只包含一个AND子句,因此最多有两个过滤条件。可以增加多个过滤条件,每个条件间都要使用AND关键字。
OR 操作符
OR操作符与AND操作符正好相反,它指示 DBMS 检索匹配任一条件的行。事实上,许多 DBMS 在OR WHERE子句的第一个条件得到满足的情况下,就不再计算第二个条件了(在第一个条件满足时,不管第二个条件是否满足,相应的行都将被检索出来)。
SELECT prod_name,prod_priceFROM ProductsWHERE vend_id='DELL01' OR vend_id='BRS01';
此 SQL 语句检索由任一个指定供应商制造的所有产品的产品名和价格。OR操作符告诉 DBMS 匹配任一条件而不是同时匹配两个条件。如果这里使用的是AND操作符,则没有数据返回(因为会创建没有匹配行的WHERE子句)。这条 SQL 语句产生的输出如下:
求值顺序
WHERE子句可以包含任意数目的AND和OR操作符。允许两者结合以进行复杂、高级的过滤。
但是,组合AND和OR会带来了一个有趣的问题。为了说明这个问题,来看一个例子。假如需要列出价格为 10 美元及以上,且由DLL01或BRS01制造的所有产品。下面的SELECT语句使用组合的AND和OR操作符建立了一个WHERE子句:
SELECT prod_name,prod_priceFROM ProductsWHERE vend_id='DELL01' OR vend_id='BRS01'AND prod_price>=10;
请看上面的结果。返回的行中有 4 行价格小于 10 美元,显然,返回的行未按预期的进行过滤。为什么会这样呢?原因在于求值的顺序。
SQL (像多数语言一样)在处理OR操作符前,优先处理AND操作符。当 SQL 看到上述WHERE子句时,它理解为:由供应商BRS01制造的价格为 10美元以上的所有产品,以及由供应商DLL01制造的所有产品,而不管其价格如何。换句话说,由于AND在求值过程中优先级更高,操作符被错误
地组合了。
此问题的解决方法是使用圆括号对操作符进行明确分组。请看下面的SELECT语句及输出:
SELECT prod_name,prod_price
FROM Products
WHERE (vend_id=‘DELL01’ OR vend_id=‘BRS01’)
AND prod_price>=10;
这条SELECT语句与前一条的唯一差别是,将前两个条件用圆括号括了起来。因为圆括号具有比AND或OR操作符更高的求值顺序,所以 DBMS 首先过滤圆括号内的OR条件。这时, SQL 语句变成了选择由供应商DLL01或BRS01制造的且价格在 10 美元及以上的所有产品,这正是我们希望的结
果。
提示:在 WHERE 子句中使用圆括号
任何时候使用具有AND和OR操作符的WHERE子句,都应该使用圆括号明确地分组操作符。不要过分依赖默认求值顺序,即使它确实如你希望的那样。使用圆括号没有什么坏处,它能消除歧义。
IN 操作符
IN操作符用来指定条件范围,范围中的每个条件都可以进行匹配。IN取一组由逗号分隔、括在圆括号中的合法值。下面的例子说明了这个操作符;
SELECT prod_name,prod_priceFROM ProductsWHERE vend_id IN ('DELL01','BRS01')ORDER BY prod_name;
此SELECT语句检索由供应商DLL01和BRS01制造的所有产品。IN操作符后跟由逗号分隔的合法值,这些值必须括在圆括号中。
你可能会猜测IN操作符完成了与OR相同的功能,恭喜你猜对了!下面的 SQL 语句完成与上面的例子相同的工作:
SELECT prod_name,prod_priceFROM ProductsWHERE vend_id='DELL01' OR vend_id='BRS01'ORDER BY prod_name;
为什么要使用IN操作符?其优点为:
在有很多合法选项时,IN操作符的语法更清楚,更直观。
在与其他AND和OR操作符组合使用IN时,求值顺序更容易管理。
IN操作符一般比一组OR操作符执行得更快(在上面这个合法选项很少的例子中,你看不出性能差异)。
IN的最大优点是可以包含其他SELECT语句,能够更动态地建立WHERE子句。
IN
WHERE子句中用来指定要匹配值的清单的关键字,功能与OR相当。
NOT操作符
WHERE子句中的NOT操作符有且只有一个功能,那就是否定其后所跟的任何条件。因为NOT从不单独使用(它总是与其他操作符一起使用),所以
它的语法与其他操作符有所不同。NOT关键字可以用在要过滤的列前,而不仅是在其后。
NOT
WHERE子句中用来否定其后条件的关键字。
SELECT prod_nameFROM ProductsWHERE NOT vend_id='DLL01'ORDER BY prod_name;
这里的NOT否定跟在其后的条件,因此, DBMS 不是匹配vend_id为DLL01,而是匹配非DLL01之外的所有东西。
用通配符进行过滤
LIKE操作符
前面介绍的所有操作符都是针对已知值进行过滤的。不管是匹配一个值还是多个值,检验大于还是小于已知值,或者检查某个范围的值,其共同点是过滤中使用的值都是已知的。
但是,这种过滤方法并不是任何时候都好用。例如,怎样搜索产品名中包含文本 bean bag 的所有产品?用简单的比较操作符肯定不行,必须使用通配符。利用通配符,可以创建比较特定数据的搜索模式。在这个例子中,如果你想找出名称包含 bean bag 的所有产品,可以构造一个通配符搜索模式,找出在产品名的任何位置出现 bean bag 的产品。
通配符( wildcard )
用来匹配值的一部分的特殊字符
搜索模式( search pattern )
由字面值、通配符或两者组合构成的搜索条件。
通配符本身实际上是 SQL 的WHERE子句中有特殊含义的字符, SQL 支持几种通配符。为在搜索子句中使用通配符,必须使用LIKE操作符。LIKE指示 DBMS ,后跟的搜索模式利用通配符匹配而不是简单的相等匹配进行比较。
百分号( % )通配符
最常使用的通配符是百分号(%)。在搜索串中,%表示任何字符出现任意次数。例如,为了找出所有以词Fish起头的产品,可发布以下SELECT语句:
SELECT prod_id,prod_nameFROM ProductsWHERE prod_name LIKE 'FISH%';
此例子使用了搜索模式’Fish%’。在执行这条子句时,将检索任意以Fish起头的词。%告诉 DBMS 接受Fish之后的任意字符,不管它有多少字符。
说明:区分大小写
根据 DBMS 的不同及其配置,搜索可以是区分大小写的。如果区分大小写,则’fish%'与Fish bean bag toy就不匹配。
通配符可在搜索模式中的任意位置使用,并且可以使用多个通配符。下面的例子使用两个通配符,它们位于模式的两端:
SELECT prod_id,prod_nameFROM ProductsWHERE prod_name LIKE '%bean bag%';
搜索模式’%bean bag%'表示匹配任何位置上包含文本bean bag的值,不论它之前或之后出现什么字符。
通配符也可以出现在搜索模式的中间,虽然这样做不太有用。下面的例子找出以F起头、以y结尾的所有产品:
SELECT prod_nameFROM ProductsWHERE prod_name LIKE 'F%y';
提示:根据部分信息搜索电子邮件地址
有一种情况下把通配符放在搜索模式中间是很有用的,就是根据邮件地址的一部分来查找电子邮件,例如WHERE email LIKE
b%@。
需要特别注意,除了能匹配一个或多个字符外,%还能匹配 0 个字符。%代表搜索模式中给定位置的 0 个、 1 个或多个字符。
警告:请注意 NULL
通配符%看起来像是可以匹配任何东西,但有个例外,这就是NULL。子句WHERE prod_name LIKE '%'不会匹配产品名称为NULL的行。
下划线( _ )通配符
另一个有用的通配符是下划线()。下划线的用途与%一样,但它只匹配单个字符,而不是多个字符。
说明: DB2 通配符
DB2 不支持通配符。
SELECT prod_id, prod_nameFROM ProductsWHERE prod_name LIKE '__ inch teddy bear';
说明:请注意后面所跟的空格
与上例一样,可能需要给这个模式添加一个通配符
这个WHERE子句中的搜索模式给出了后面跟有文本的两个通配符。结果只显示匹配搜索模式的行:第一行中下划线匹配12,第二行中匹配18。8inch teddy bear产品没有匹配,因为搜索模式要求匹配两个通配符而不是一个。对照一下,下面的SELECT语句使用%通配符,返回三行产品:
SELECT prod_id, prod_nameFROM ProductsWHERE prod_name LIKE '% inch teddy bear';
与%能匹配 0 个字符不同,_总是刚好匹配一个字符,不能多也不能少。