SQL检索、过滤数据（二）

1.4  过滤数据

过滤已知的值

1.select  列名  from  表名  where  列名=a   order by  列名    desc；（在同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE之后，否则将会产生错误）

作用：降序列出表明中列名的数据等于a的列名

where子句操作符：

select prod_id from products where prod_id!='BR01';

提示：何时使用引号 如果仔细观察上述WHERE子句中的条件，会看到有的值括在单引号内，而有的值未括起来。单引号用来限定字符串。如果将值与字符串类型 的列进行比较，就需要限定引号。用来与数值列进行比较的值不用引号。

2.  select 列名  from 表名  where  列名<>a；

作用：列出列名不是a的列名的数据

3. select  列名  from  表名  where  列名   between  a  and  b；

作用:列出范围值在a和b中的列名的数据

4.  select 列名1  from  表名  where  列名2  is  null；

作用：列出表中列名2的值是null的列名1的数据

注意：

利用2中的<>得出不匹配的值时，如果数据中有NULL值，检索出来的数据是不会显示NULL值的，因为未知（unknown）有特殊的含义，数据库不知 道它们是否匹配，所以在进行匹配过滤或非匹配过滤时，不会返回这些结果。

可以看到上图中的cust_email中的NULL值在检索的时候没有被显示出来。 

5.  组合where子句

操作符（operator）：用来联结或改变where子句中的子句的关键字，也成为逻辑操作符（logic operator）

and操作符：

select  列名1，列名2 from 表名 where 列名1=a  and 列名2<>b;

作用：过滤出既满足列名1等于a又满足列名2不为b的列名1和列名2的数据

or操作符：

select  列名1，列名2 from 表名 where 列名1=a  or  列名2<>b;

作用：过滤出满足列名1等于a或满足列名2不为b的列名1和列名2的数据（满足一个条件即可）

 

 

WHERE子句可以包含任意数目的AND和OR操作符。允许两者结合以进行复杂、高级的过滤。但当组合使用时，and的优先级要高于or，所以在组合使用的时候要使用圆括号对操作符进行明确分组。（因为圆括号的优先级要高于and和or）

举例如下：

图一中没有加括号，and的优先级高于or，所以这句话的逻辑顺序是筛选出两组数据1：满足vend_id='DLL01'的数据2：同时满足vend_id='BRS01'和prod_price>=10的数据。

图二中加了括号，逻辑是筛选出满足vend_id='DLL01'或'BRS01'且prod_price>=10的数据。

 

in操作符（在WHERE子句中用来指定要匹配值的清单的关键字，功能与OR相当。）

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

 

select prod_name,prod_price,vend_id from products where vend_id in ('DLL01','BRS01') and prod_price>=10 order by prod_name;

等同于

select prod_name,prod_price,vend_id from products where (vend_id='DLL01' or vend_id='BRS01') and prod_price>=10order by prod_name; 

 

IN操作符的优点为：

1.在有很多合法选项时，IN操作符的语法更清楚，更直观。

2.在与其他AND和OR操作符组合使用IN时，求值顺序更容易管理。

3.IN操作符一般比一组OR操作符执行得更快（在上面这个合法选项很少的例子中，你看不出性能差异）。 IN的最大优点是可以包含其他SELECT语句，能够更动态地建立WHERE子句。

 

not操作符

select  列名  from 表名  where  not  列名=a  order  by  列名；

作用：将表中的列名不是a的列名数据升序输出。等价于：select  列名  from 表名  where  列名<>a  order by  列名；

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

 

过滤未知的值

例如，怎样搜索产品名中包含文本bean bag的所有产品？用简单的比较操作符肯定不行，必须使 用通配符。利用通配符，可以创建比较特定数据的搜索模式。在这个例子中，如果你想找出名称包含bean bag的所有产品，可以构造一个通配 符搜索模式，找出在产品名的任何位置出现bean bag的产品。

通配符（wildcard）：用来匹配值的一部分的特殊字符

搜索模式（search partten）：由字面值、通配符或两者组合构成的搜索条件。

通配符本身实际上是SQL的where子句中有特殊含义的字符，SQL支持集中通配符。为在搜索子句中使用通配符，必须使用LIKE操作符，LIKE指示DBMS，后跟的搜索模式利用通配符匹配而不是简单的相等匹配进行比较

通配符只能用于文本字段（串），非文本数据类型字段不能使用通配符搜索

1.%通配符

在搜索串中，%表示任何字符出现任意次数

例1：select prod_id,prod_name from products where prod_name like 'Fish%';

作用：检索products表中以Fish起头的词，%告诉DBMS接受Fish之后的任意字符，不管它有多少字符。

通配符可在搜索模式中的任意位置使用，并且可以使用多个通配符

例2：select prod_id,prod_name from products where prod_name like '%bean bag%';

作用：匹配任何位置上包含文本  bean bag 的值，无论它之前或之后出现什么字符

例3：select prod_name from products where prod_name like 'F%y';

提示：根据部分信息搜索电子邮件地址，将%放在搜索模式中间是很有用的。例如  where  Emai  like b%@qq.com

注意：%除了能匹配一个或多个字符外，还能匹配0个字符。%代表搜索模式中给定位置的0个、1个或多个字符

说明：包括Access在内的许多DBMS 都用空格来填补字段的内容。例如，如果某列有50个字符，而存储的文本为Fish bean bag toy（17个字符），则为填满该列需要在文本后添加33个空格。这样做一般对数据及其使用没有影响，但是可能对%通配符的SQL语句有负面影响，子句where  prod_name  like 'F%y'只匹配以F开头，以y结尾的prod_name。如果值后面跟空格，则不是以y结尾，所以Fish bean bag toy 就不会检索出来。简单的解决方法是给搜索模式再增加一个%号：‘F%y%’还匹配y之后的字符（或空格）。

警告：请注意NULL

通配符%看起来像是可以匹配任何东西，但有个例外，这就是NULL。例子如下：

可以看到值为NULL的没有被匹配出来

2._通配符

下划线通配符的用途与%一样，但它只匹配单个字符，而不是多个字符

例1：SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '_ inch teddy bear';

例2：SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '__ inch teddy bear';

这两句的区别在于下划线，例1中是1个下划线，例2中是2个下划线。图一的结果是匹配一个字符的，图二是匹配两个字符的结果

 3.[ ]通配符

作用：用来指定一个字符集，它必须匹配指定位置（通配符位置）的一个字符

说明：并不是所有DBMS支持[ ],只有微软的Access和SQL Server支持

例：找出所有以J或M起头的cust_contact

select cust_contact from customers where cust_contact like '[JM]%' order by cust_contact;

结果：

此通配符可以用前缀字符~或NOT来否定。例如：

select cust_contact from customers where cust_contact like '[~JM]%' order by cust_contact;

select cust_contact from customers where not  cust_contact like '[JM]%' order by cust_contact;

 

使用通配符的技巧：

通配符搜索一般比之前的其他搜索耗费更长的时间，技巧如下：

1.不要过度使用通配符，如果其他操作符能达到相同的目的，应该使用其他操作符

2.在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处，把通配符置于开始处，搜索起来是最慢的

3.仔细注意通配符的位置，如果放错地方，可能不会返回想要的数据