数据库 \ SQL Server \ SQL 过滤数据

SQL 过滤数据

总点击15
简介:使用WHERE子句    数据库表一般包含大量的数据,很少需要检索表中的所有行。通常只会根据特定操作或报告的需要提取表数据的子集。只检索所需数据需要指定搜索条件,搜索条件也称为过滤条件。

使用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子句操作符


SQL 过滤数据

我们已经看到了检验相等的例子,现在来看看几个使用其他操作符的例子。第一个例子是列出所有价格小于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_priceFROM ProductsWHERE prod_price BETWEEN 5 AND 10;输出


prod_name prod_price------------------- ----------8 inch teddy bear 5.9912 inch teddy bear 8.99King doll 9.49Queen doll 9.49       从这个例子可以看到,在使用BETWEEN时,必须指定两个值——所需范围的低端值和高端值。这两个值必须用A ND关键字分隔。BETWEEN匹配范围中所有的值,包括指定的开始值和结束值。


空值检查


      在创建表时,表设计人员可以指定其中的列能否不包含值。在一个列不包含值时,称其包含空值NULL。无值(no value),它与字段包含0、空字符串或仅仅包含空格不同。确定值是否为NULL,不能简单地检查是否= NULL。SELECT语句有一个特殊的WHERE子句,可用来检查具有NULL值的列。这个WHERE子句就是IS NULL子句。其语法如下:


输入


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


SELECT cust_nameFROM CUSTOMERSWHERE cust_email IS NULL;输出


cust_name----------Kids PlaceThe 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的通配符很有用。但这种功能是有代价的,即通配符搜索一般比前面讨论的其他搜索要耗费更长的处理时间。这里给出一些使用通配符时要记住的技巧。


      不要过度使用通配符。如果其他操作符能达到相同的目的,应该使用其他操作符。在确实需要使用通配符时,也尽量不要把它们用在搜索模式的开始处。把通配符置于开始处,搜索起来是最慢的。仔细注意通配符的位置。如果放错地方,可能不会返回想要的数据。

0人推荐

推荐文章

热门标签

意见反馈 常见问题 官方微信 返回顶部