mirror of
https://github.com/cheetahlou/CategoryResourceRepost.git
synced 2025-09-29 06:36:40 +08:00
171 lines
11 KiB
Markdown
171 lines
11 KiB
Markdown
<audio id="audio" title="06丨数据过滤：SQL数据过滤都有哪些方法？" controls="" preload="none"><source id="mp3" src="https://static001.geekbang.org/resource/audio/ae/a2/ae0d6b886ac142fd30a931ad13cea0a2.mp3"></audio>
|
||
|
||
我在上篇文章中讲到过，提升查询效率的一个很重要的方式，就是约束返回结果的数量，还有一个很有效的方式，就是指定筛选条件，进行过滤。过滤可以筛选符合条件的结果，并进行返回，减少不必要的数据行。
|
||
|
||
那么在今天的内容里，我们来学习如何对SQL数据进行过滤，这里主要使用的就是WHERE子句。
|
||
|
||
你可能已经使用过WHERE子句，说起来SQL其实很简单，只要能把满足条件的内容筛选出来即可，但在实际使用过程中，不同人写出来的WHERE子句存在很大差别，比如执行效率的高低，有没有遇到莫名的报错等。
|
||
|
||
在今天的学习中，你重点需要掌握以下几方面的内容：
|
||
|
||
1. 学会使用WHERE子句，如何使用比较运算符对字段的数值进行比较筛选；
|
||
1. 如何使用逻辑运算符，进行多条件的过滤；
|
||
1. 学会使用通配符对数据条件进行复杂过滤。
|
||
|
||
## 比较运算符
|
||
|
||
在SQL中，我们可以使用WHERE子句对条件进行筛选，在此之前，你需要了解WHERE子句中的比较运算符。这些比较运算符的含义你可以参见下面这张表格：
|
||
|
||
<img src="https://static001.geekbang.org/resource/image/3a/e0/3a2667784b4887ef15becc7056f3d3e0.png" alt="">
|
||
|
||
实际上你能看到，同样的含义可能会有多种表达方式，比如小于等于，可以是（<=），也可以是不大于（!>）。同样不等于，可以用（<>），也可以用（!=），它们的含义都是相同的，但这些符号的顺序都不能颠倒，比如你不能写（=<）。需要注意的是，你需要查看使用的DBMS是否支持，不同的DBMS支持的运算符可能是不同的，比如Access不支持（!=），不等于应该使用（<>）。在MySQL中，不支持（!>）（!<）等。
|
||
|
||
我在上一篇文章中使用了heros数据表，今天还是以这张表格做练习。下面我们通过比较运算符对王者荣耀的英雄属性进行条件筛选。
|
||
|
||
WHERE子句的基本格式是：`SELECT ……(列名) FROM ……(表名) WHERE ……(子句条件)`
|
||
|
||
比如我们想要查询所有最大生命值大于6000的英雄：
|
||
|
||
```
|
||
SQL：SELECT name, hp_max FROM heros WHERE hp_max > 6000
|
||
|
||
```
|
||
|
||
运行结果（41条记录）：
|
||
|
||
<img src="https://static001.geekbang.org/resource/image/9f/c1/9f639dfe0bd9dbfc63944447f92e47c1.png" alt="">
|
||
|
||
想要查询所有最大生命值在5399到6811之间的英雄：
|
||
|
||
```
|
||
SQL：SELECT name, hp_max FROM heros WHERE hp_max BETWEEN 5399 AND 6811
|
||
|
||
```
|
||
|
||
运行结果：（41条记录）
|
||
|
||
<img src="https://static001.geekbang.org/resource/image/4b/60/4b11a5f32f3f2807c8278f8d5637d460.png" alt="">
|
||
|
||
需要注意的是`hp_max`可以取值到最小值和最大值，即5399和6811。
|
||
|
||
我们也可以对heros表中的`hp_max`字段进行空值检查。
|
||
|
||
```
|
||
SQL：SELECT name, hp_max FROM heros WHERE hp_max IS NULL
|
||
|
||
```
|
||
|
||
运行结果为空，说明heros表中的`hp_max`字段没有存在空值的数据行。
|
||
|
||
## 逻辑运算符
|
||
|
||
我刚才介绍了比较运算符，如果我们存在多个WHERE条件子句，可以使用逻辑运算符：
|
||
|
||
<img src="https://static001.geekbang.org/resource/image/ae/c1/aeed170c57ae1e5378fbee9f8fb6a8c1.png" alt="">
|
||
|
||
我们还是通过例子来看下这些逻辑运算符的使用，同样采用heros这张表的数据查询。
|
||
|
||
假设想要筛选最大生命值大于6000，最大法力大于1700的英雄，然后按照最大生命值和最大法力值之和从高到低进行排序。
|
||
|
||
```
|
||
SQL：SELECT name, hp_max, mp_max FROM heros WHERE hp_max > 6000 AND mp_max > 1700 ORDER BY (hp_max+mp_max) DESC
|
||
|
||
```
|
||
|
||
运行结果：（23条记录）
|
||
|
||
<img src="https://static001.geekbang.org/resource/image/85/4e/859e7e9fcf28a30f9189ef81cfe7284e.png" alt="">
|
||
|
||
如果AND和OR同时存在WHERE子句中会是怎样的呢？假设我们想要查询最大生命值加最大法力值大于8000的英雄，或者最大生命值大于6000并且最大法力值大于1700的英雄。
|
||
|
||
```
|
||
SQL：SELECT name, hp_max, mp_max FROM heros WHERE (hp_max+mp_max) > 8000 OR hp_max > 6000 AND mp_max > 1700 ORDER BY (hp_max+mp_max) DESC
|
||
|
||
```
|
||
|
||
运行结果：（33条记录）
|
||
|
||
<img src="https://static001.geekbang.org/resource/image/05/ae/052c1fb7031d025d5e0c0027177187ae.png" alt="">
|
||
|
||
你能看出来相比于上一个条件查询，这次的条件查询多出来了10个英雄，这是因为我们放宽了条件，允许最大生命值+最大法力值大于8000的英雄显示出来。另外你需要注意到，当WHERE子句中同时存在OR和AND的时候，AND执行的优先级会更高，也就是说SQL会优先处理AND操作符，然后再处理OR操作符。
|
||
|
||
如果我们对这条查询语句OR两边的条件增加一个括号，结果会是怎样的呢？
|
||
|
||
```
|
||
SQL：SELECT name, hp_max, mp_max FROM heros WHERE ((hp_max+mp_max) > 8000 OR hp_max > 6000) AND mp_max > 1700 ORDER BY (hp_max+mp_max) DESC
|
||
|
||
```
|
||
|
||
运行结果：
|
||
|
||
<img src="https://static001.geekbang.org/resource/image/1a/c9/1a41124faad3aac6e8170a72e65de5c9.png" alt="">
|
||
|
||
所以当WHERE子句中同时出现AND和OR操作符的时候，你需要考虑到执行的先后顺序，也就是两个操作符执行的优先级。一般来说()优先级最高，其次优先级是AND，然后是OR。
|
||
|
||
如果我想要查询主要定位或者次要定位是法师或是射手的英雄，同时英雄的上线时间不在2016-01-01到2017-01-01之间。
|
||
|
||
```
|
||
SQL：
|
||
SELECT name, role_main, role_assist, hp_max, mp_max, birthdate
|
||
FROM heros
|
||
WHERE (role_main IN ('法师', '射手') OR role_assist IN ('法师', '射手'))
|
||
AND DATE(birthdate) NOT BETWEEN '2016-01-01' AND '2017-01-01'
|
||
ORDER BY (hp_max + mp_max) DESC
|
||
|
||
```
|
||
|
||
你能看到我把WHERE子句分成了两个部分。第一部分是关于主要定位和次要定位的条件过滤，使用的是`role_main in ('法师', '射手') OR role_assist in ('法师', '射手')`。这里用到了IN逻辑运算符，同时`role_main`和`role_assist`是OR（或）的关系。
|
||
|
||
第二部分是关于上线时间的条件过滤。NOT代表否，因为我们要找到不在2016-01-01到2017-01-01之间的日期，因此用到了`NOT BETWEEN '2016-01-01' AND '2017-01-01'`。同时我们是在对日期类型数据进行检索，所以使用到了DATE函数，将字段birthdate转化为日期类型再进行比较。关于日期的操作，我会在下一篇文章中再作具体介绍。
|
||
|
||
这是运行结果（6条记录）：
|
||
|
||
<img src="https://static001.geekbang.org/resource/image/70/8e/7048f6ff11215b6d0113b2370103828e.png" alt="">
|
||
|
||
## 使用通配符进行过滤
|
||
|
||
刚才讲解的条件过滤都是对已知值进行的过滤，还有一种情况是我们要检索文本中包含某个词的所有数据，这里就需要使用通配符。通配符就是我们用来匹配值的一部分的特殊字符。这里我们需要使用到LIKE操作符。
|
||
|
||
如果我们想要匹配任意字符串出现的任意次数，需要使用（%）通配符。比如我们想要查找英雄名中包含“太”字的英雄都有哪些：
|
||
|
||
```
|
||
SQL：SELECT name FROM heros WHERE name LIKE '%太%'
|
||
|
||
```
|
||
|
||
运行结果：（2条记录）
|
||
|
||
<img src="https://static001.geekbang.org/resource/image/b1/18/b18de17c2517d7c06c56f324309c4c18.png" alt=""><br>
|
||
需要说明的是不同DBMS对通配符的定义不同，在Access中使用的是（*）而不是（%）。另外关于字符串的搜索可能是需要区分大小写的，比如`'liu%'`就不能匹配上`'LIU BEI'`。具体是否区分大小写还需要考虑不同的DBMS以及它们的配置。
|
||
|
||
如果我们想要匹配单个字符，就需要使用下划线(**)通配符。（%）和（**）的区别在于，（%）代表零个或多个字符，而（_）只代表一个字符。比如我们想要查找英雄名除了第一个字以外，包含‘太’字的英雄有哪些。
|
||
|
||
```
|
||
SQL：SELECT name FROM heros WHERE name LIKE '_%太%'
|
||
|
||
```
|
||
|
||
运行结果（1条记录）：
|
||
|
||
<img src="https://static001.geekbang.org/resource/image/ab/65/ab30c809327a81ee00ce4989e7815065.png" alt="">
|
||
|
||
因为太乙真人的太是第一个字符，而`_%太%`中的太不是在第一个字符，所以匹配不到“太乙真人”，只可以匹配上“东皇太一”。
|
||
|
||
同样需要说明的是，在Access中使用（?）来代替（`_`），而且在DB2中是不支持通配符（`_`）的，因此你需要在使用的时候查阅相关的DBMS文档。
|
||
|
||
你能看出来通配符还是很有用的，尤其是在进行字符串匹配的时候。不过在实际操作过程中，我还是建议你尽量少用通配符，因为它需要消耗数据库更长的时间来进行匹配。即使你对LIKE检索的字段进行了索引，索引的价值也可能会失效。如果要让索引生效，那么LIKE后面就不能以（%）开头，比如使用`LIKE '%太%'`或`LIKE '%太'`的时候就会对全表进行扫描。如果使用`LIKE '太%'`，同时检索的字段进行了索引的时候，则不会进行全表扫描。
|
||
|
||
## 总结
|
||
|
||
今天我对SQL语句中的WHERE子句进行了讲解，你可以使用比较运算符、逻辑运算符和通配符这三种方式对检索条件进行过滤。
|
||
|
||
比较运算符是对数值进行比较，不同的DBMS支持的比较运算符可能不同，你需要事先查阅相应的DBMS文档。逻辑运算符可以让我们同时使用多个WHERE子句，你需要注意的是AND和OR运算符的执行顺序。通配符可以让我们对文本类型的字段进行模糊查询，不过检索的代价也是很高的，通常都需要用到全表扫描，所以效率很低。只有当LIKE语句后面不用通配符，并且对字段进行索引的时候才不会对全表进行扫描。
|
||
|
||
你可能认为学习SQL并不难，掌握这些语法就可以对数据进行筛选查询。但实际工作中不同人写的SQL语句的查询效率差别很大，保持高效率的一个很重要的原因，就是要避免全表扫描，所以我们会考虑在WHERE及ORDER BY涉及到的列上增加索引。
|
||
|
||
<img src="https://static001.geekbang.org/resource/image/fb/50/fbd79c2c90a58891b498e7f29d935050.jpg" alt="">
|
||
|
||
你能说一下WHERE子句中比较运算符、逻辑运算符和通配符这三者各自的作用吗？以heros数据表为例，请你编写SQL语句，对英雄名称、主要定位、次要定位、最大生命和最大法力进行查询，筛选条件为：主要定位是坦克或者战士，并且次要定位不为空，同时满足最大生命值大于8000或者最大法力小于1500的英雄，并且按照最大生命和最大法力之和从高到底的顺序进行排序。
|
||
|
||
欢迎你在评论区写下你的思考，也欢迎点击请朋友读，把这篇文章分享给你的朋友或者同事。
|