SQL必知必会 - 用where和操作符过滤数据

目录

一、WHERE语句

1. where语句的作用

2. where子句的操作符表

二、运用WHERE语句检查

1. 不匹配检查  <>

2. 范围值检查 BETWEEN

3.空值检查   IS NULL

三、运用操作符高级过滤 AND / OR / IN / NOT

1 操作符 AND

2  操作符 OR

3  AND和OR的求值顺序

4  操作符 IN

5  操作符 NOT

四、用通配符过滤数据

1 操作符LIKE 及其通配符% _ [] ^

① 百分号通配符 %

② 下划线通配符 _

③ 方括号通配符 [ ]

2 通配符的使用要点

---

一、WHERE语句

1. where语句的作用

• 用于搜索指定条件的数据，即过滤数据。
• where子句加在被提取表名的后面，被提取表名的后面，被提取表名的后面！用于返回限定结果的行
• e.g. 选取name和price两列，再返回name列的所有数据中，price为3.9的所有行 ：

SELECT prod_name, prod_price FROM Products WHERE prod_price = 3.49；

2. where子句的操作符表

操作符                说明	操作符                说明
=                       等于	<>                   不等于
<                       小于	!=                    不等于
<=                  小于等于	>                       大于
!<                     不小于	!>                     不大于
BETWEEN          两个值之间	IS NULL                空值

子句操作符使用时，结合WHERE语句，用来条件限定

• e.g1. 查价格小于10 :

SELECT prod_name, prod_price FROM Products WHERE prod_price < 10;

• e.g2. 查价格大于2880 :

SELECT prod_name, prod_price FROM Products WHERE prod_price > 2880；

二、运用WHERE语句检查

1. 不匹配检查  <>

• 即用 <>   查找出不相等的行结果

e.g1. 查找出该两列中，name不是AAAA的所有结果 ：

SELECT prod_name, prod_price FROM Products WHERE prod_name <> 'AAAA'；

使用<>的注意点

1. 输入文本字符串时，用的是单引号
2. 注意题目的场景需求，使用<>时，会排除掉“=”的情况 

          e.g2.查询没有英语成绩分数的学生明细记录

               （该题用NOT IN，而不是<>  因为用<>查询会忽略值NULL的数据）

SELECT * FROM student_table
WHERE id NOT IN ( SELECT stu_id FROM score_table
                         WHERE subject_name = '英语');  

2. 范围值检查 BETWEEN

• 包含限定范围用的开始值与结束值，两个端点值之间用 AND隔开。
• e.g. 限定价格在9到27 :

SELECT prod_name,prod_price FROM Products WHERE prod_price BETWEEN 9 AND 27;

3.空值检查   IS NULL

• 空值即，不包含任何字段的单元格，包括0值、回车、空格，都不在单元格的文本内容中
• 注：如选取列（prod_name）的限定条件列（prod_price）中，不存在为空的单元格，则不返回数据。

e.g1. 用 WHERE + IS NULL 返回没有数据的行

SELECT prod_name FROM Products WHERE prod_price IS NULL;

e.g2. 用 WHERE + IS NULL 返回所有有数据的行，即不是空值的行

SELECT prod_name FROM Products WHERE prod_price IS NOT NULL;

三、运用操作符高级过滤 AND / OR / IN / NOT

通过组合运用操作符 AND / OR / IN / NOT，实现高级数据过滤

1 操作符 AND

说明：叠加对已查询出的列的多个过滤条件，每个条件之间用AND连接。形如 SELECT ... FROM ... WHERE ... AND ... AND ... AND ... ;

e.g. 仅检索出供应商为DLL01制造的，并且价格小于等于4的所有产品ID、产品名称与产品价格，按照产品ID列排序。

• 从products表中查询出了prod_id,prod_price,prod_name这几列，再对其添加多个过滤条件。
• 过滤条件1：过滤出vend_id列中，字符为DLL01的行，
• 过滤条件2：prod_price要小于等于4。
• ORDER BY 跟在WHERE 子句后面

代码如下：

SELECT prod_id,prod_price,prod_name FROM products 
       WHERE vend_id='DLL01' AND prod_price<=4 
         ORDER BY prod_id;

2  操作符 OR

说明：与AND操作符对应，指定程序检索任一条件的行，系统一般检索出位置在第一个的条件后，就不再检索写于后面的条件。

e.g1. 检索供应商名称是DLL01或者BRS01的所有产品id、产品价格和产品名称。代码如下：

SELECT prod_id,prod_name,prod_price FROM products 
       WHERE vend_id='DLL01' OR vend_id='BRS01';

e.g2 表OrderItems有prod_id和quantity两列。将两个 SELECT 语句用单个 SELECT 语句结合起来。其中，一个 SELECT 语句过滤数量为 100 的行，另一个 SELECT 语句过滤 id 以 BNBG 开头的产品，最后按产品 id 对结果进行升序排序。 

select prod_id,quantity
from OrderItems
where quantity = 100 or prod_id like 'BNBG%'
order by prod_id;

3  AND和OR的求值顺序

说明：AND/OR操作符可以两者组合，实现更加复杂的过滤。

注：

对括号的优先顺序

• WHRE子句中，会优先处理AND子句，无论AND位于OR的前还是后！所以需要添加必要的括号对其明确分组。如 A OR B AND C 会被系统理解为 ( B AND C ) OR A
• 括号具有比AND更高的优先级别

e.g. 过滤出所有价格为10及以上的产品名称，并且制造商为DLL01或BRS01。代码如下：

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

对三值逻辑的优先顺序

 

4  操作符 IN

说明：

• 用来指定条件范围清单，范围中的每个条件，都作为匹配，功能与OR类似。
• 表现形式为 IN(条件1，条件2，...)  相当于一个并集
• 子查询中，父查询中用的IN运算符一般是 单列多值嵌套查询

e.g. 显示出由 DLL01 或 BRS01 制造的所有产品名称与价格，按产品名称列排序

SELECT prod_name,prod_price FROM products
       WHERE  vend_id IN('DLL01','BRS01')
ORDER BY prod_name;

IN操作符的优点：

• 更清楚直观，缩小必要输入的字符长度
• 组合使用时，更易管理WHERE的子句顺序
• 程序运行上，IN操作符比OR更快
• IN可以包含其他SELECT语句，建立WHERE子查询

select cust_id
from Orders
where order_num in(
select order_num 
from OrderItems
where item_price>=10)

5  操作符 NOT

说明：可以否定后面跟着的任何条件，可用于排除要过滤的列

e.g1.查询出所有DLL01以外制造商制造的所有产品,并按产品名称排序

SELECT prod_name FROM products 
       WHRER NOT vend_id='DLL01'
       ORDER BY prod_name;

p.s. NOT的效果可同样用 <> 符号实现

SELECT prod_name FROM products 
       WHRER vend_id <> 'DLL01'
       ORDER BY prod_name;

 e.g2. 从exam_record数据表中,计算所有用户完成SQL类别,高难度试卷得分的截断平均值（去掉一个最大值和一个最小值后的平均值） 

SELECT tag, difficulty,
       ROUND(AVG(r.score), 1) AS clip_avg_score
FROM exam_record r
LEFT JOIN examination_info i USING(exam_id)
WHERE difficulty  = 'hard' AND tag = 'SQL'
AND r.score NOT IN (SELECT MAX(score) FROM exam_record)
AND r.score NOT IN (SELECT MIN(score) FROM exam_record);

四、用通配符过滤数据

1 操作符LIKE 及其通配符% _ [] ^

通配符要点：

• 创建较为特定的数据搜索模式，作为匹配值的一部分的特殊字符。搜索模式即 '  '
• 与 LIKE 操作符 一起搭配使用
• LIKE的否定用法：WHERE prod_name NOT LIKE 'FISH'
• 通配符只能用于文本搜索，搜索文本时，注意区分大小写
• 通配符可以在搜索模式的任意位置，并且可以使用多个通配符

① 百分号通配符 %

%含义： 

• 任何字符出现任意次数，即可以用于匹配多个字符 
• 包括0次，%可以匹配0个字符即空格。但不能匹配为空值的单元格
• 相当于excel中的通配符 *，理解上是一样的          

e.g1. 找出所有以FISH开头的产品

SELECT prod_name FROM products 
       WHERE prod_name LIKE  'FISH%' ;

e.g2. 找出所有以F开头、以y结尾的产品

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

e.g3. 找出所有以F开头、以y结尾的产品，并且包括产品名称以空格结尾的行

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

② 下划线通配符 _

_ 含义 ：与%用法相同，但只能匹配单个字符，而不是多个字符

③ 方括号通配符 [ ]

 [ ] 要点：

• 用来指定一个字符集，表示匹配括起来的任意一个字符，即“或者...或者...”。
•  方括号里只能匹配括号中的单个字符，即若果括号里输入了2个字母，则分别匹配这两个字母。
• [^...] 表示否定 ，与NOT同理

e.g1. 从Customers表的cust_contact列中，找出所有名字以J或M开头的联系人,并按该列排序

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

 e.g2. 从Customers表的cust_contact列中，找出所有名字里以J或M开头以外的所有联系人,并按该列排序

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

 ④ 其他通配符

• ^        表示匹配输入字符串的 开始位置
• [^...]   表示不能匹配括号内的任意单个字符
• a |  b  中间的竖线 “  | ” 表示匹配 a 或者匹配 b 

2 通配符的使用要点

• 尽量不将其放在搜索模式的开头，会降低程序运行速度。
• 细心使用，不要过度添加通配符

3 关系运算符（拓展）

关系代数运算符 - 集合运算符 ：

交集  ∩

并集 ∪

差集  -   

附：表否定的同类查找语句

e.g.查看除复旦大学以外的所有用户明细

select device_id,	gender,	age	,university
from user_profile
where university <>'复旦大学'；--用<>表示否定

SELECT device_id,gender,age,university FROM user_profile 
WHERE university NOT IN ('复旦大学')；--用 NOT结合IN

select device_id,	gender,	age	,university
from user_profile
where university != '复旦大学';--用！=表示否定