《数据库基础语法》3. 通过查询条件实现数据过滤

楔子

我们之前学习了如何使用 SELECT 和 FROM 查询表中的数据，不过在实际应用中通常并不需要返回表中的全部数据，而只需要找出满足某些条件的结果。比如，某个部门中的员工或者某个产品最近几天的销售情况，在 SQL 中，可以通过查询条件实现数据的过滤。

查询条件

在 SQL 语句中，使用关键字 WHERE 指定查询的过滤条件。以下语句只返回 age 为 24 的员工信息：

SELECT * FROM staff WHERE age = 24;

其中，WHERE 位于 FROM 之后，用于指定一个或者多个过滤条件；只有满足条件的数据才会返回，其他数据将被忽略。比如我们筛选所有的字段，但并不是每一行都要，只有当该行的 age 字段所对应的值等于 24 的记录我们才要。

在 SQL 中，WHERE 子句也被称为谓词（Predicate）。

这种通过查询条件过滤数据的操作在关系运算中被称为：选择（Selection），它是针对表进行的水平选择，保留满足条件的行用于生成新的表。以下是选择操作的示意图：

在查询条件中，使用最多的就是数据的比较运算。

比较运算符

比较运算符可以比较两个数值的大小，包括字符、数字以及日期类型的数据。下表列出了 SQL 中的各种比较运算符：

Oracle 中 ^= 运算符也表示不等于。

举个栗子：

-- 查询年龄小于等于 23 的记录
SELECT * FROM staff WHERE age <= 23;

大小比较的话没什么可以说的，我们来看看 BETWEEN。如果想要查找一个范围内的数据，可以使用 BETWEEN 运算符。以下示例查询 age 位于 24 到 26 之间的员工，注意：包含区间的两侧.

-- 表示查找 age 大于等于 24 并且 小于等于 26 的记录
SELECT * FROM staff WHERE age BETWEEN 24 AND 26;

IN 运算符可以用于查找列表中的值，比如以下例子表示查询 id 为 01010011101 或 01010011804 或 01010011844 的员工。

SELECT * FROM staff WHERE id IN ('01010011101', '01010011804', '01010011844');

只要匹配列表中的任何一个值，都会返回结果。IN 运算符还有一个常见的用途就是子查询的结果匹配，我们后续会说。

空值判断

空值（NULL）是 SQL 中的一个特殊值，代表了缺失或者未知的数据。与其他编程语言（例如 C）不同，SQL 中判断一个值是否为空不能使用等于或者不等于。例如，以下查询尝试找出字段 type 为空的员工：

SELECT * FROM staff WHERE type IS NULL;

如果表达式 expression 的值为空，IS NULL 返回真，IS NOT NULL 返回假；如果表达式的值不为空，IS NULL 返回假，IS NOT NULL 返回真。因此我们需要使用 IS 判断

不能使用 = 或者 != 来判断是否为空，将一个值与一个未知的值进行数学比较，结果仍然未知；即使是将两个空值进行比较，结果也是未知。以下运算的结果均为未知，用于查询条件的话不会返回任何结果：

NULL = 5;
NULL = NULL;
NULL != NULL;

复合条件

如果仅仅能够指定单个过滤条件，就无法满足复杂的查询需求；为此，SQL 引入了用于构建复杂条件的逻辑运算符。借助于逻辑代数中的逻辑运算，SQL 提供了三个逻辑运算符：

AND，"逻辑与" 运算符。只有当两边的条件都为真时，结果才为真，返回数据；否则，不返回数据。

-- 查找 title 为 '中级工' 的记录
SELECT * FROM staff WHERE title = '初级工';
/*
01010011095    26    初级工    生产人员
01010009147    30    初级工    生产人员
01010001274    33    初级工    生产人员
01010007889    34    初级工    生产人员
01010006199    33    初级工    生产人员
01010008105    47    初级工    生产人员
01010007064    31    初级工    生产人员
01010007119    46    初级工    生产人员
01010006109    40    初级工    生产人员
01010006735    33    初级工    生产人员
01010002428    29    初级工    生产人员
01010001274    33    初级工    生产人员
01010002428    29    初级工    生产人员
01010009135    29    初级工    生产人员
01010008442    33    初级工    生产人员
 */


-- 查找 title 为 '中级工' 并且 age = 33 的记录
SELECT * FROM staff WHERE title = '初级工' AND age = 33;
/*
01010001274    33    初级工    生产人员
01010006199    33    初级工    生产人员
01010006735    33    初级工    生产人员
01010001274    33    初级工    生产人员
01010008442    33    初级工    生产人员
 */

OR，"逻辑或" 运算符。只要有一个条件为真，结果就为真，返回数据；否则，不返回数据。

-- 查找 id = '01010007477' 的记录
SELECT * FROM staff WHERE id = '01010007477';
/*
01010007477    39    会计师    管理人员
 */

-- 查找 id = '01010007477' 或者 age > 55 的记录
SELECT * FROM staff WHERE id = '01010007477' OR age < 24;
/*
01010007477    39    会计师    管理人员
01010011650    22    中级工    生产人员
01010011859    23    中级工    
01010011676    23    中级工    生产人员
01010011870    23    中级工    生产人员
01010011856    23    中级工    生产人员
01010011870    23    中级工    生产人员
01010011670    23    中级工    生产人员
01010011866    23    中级工    生产人员
01010011650    22    中级工    生产人员
 */

对于逻辑运算符 AND 和 OR，SQL 使用短路运算（short-circuit evaluation）。也就是说，只要前面的表达式能够决定最终的结果，不执行后面的计算。这样能够提高运算效率，因此以下语句不会产生除零错误：

SELECT * FROM people
WHERE 1 = 0 AND 1 / 0 = 1;

SELECT * FROM people
WHERE 1 = 1 OR 1 / 0 = 1;

第一个查询由于 AND 左边的结果为假，肯定不会返回任何结果，因此也就不会计算 1 / 0；第二个查询由于 OR 左边的结果为真，一定会返回结果，同样不会产生除零错误。

NOT，"逻辑非"运算符。用于将判断结果取反，真变为假，假变为真；空值取反后仍然为空值。

NOT 运算符可以结合其他的运算符一起使用，用于对查询条件的结果取反：

• NOT BETWEEN，位于范围之外
• NOT IN，不在列表之中
• NOT LIKE，不匹配某个模式。LIKE 运算符用于字符串的模糊查找，将在后面进行介绍
• NOT EXISTS，子查询中不存在结果。关于子查询和 EXISTS 运算符，将在后面中进行介绍
• NOT IS NULL，不为空。等价于 IS NOT NULL

运算符优先级

将多个逻辑运算符进行组合，可以构造任意复杂的查询条件。不过，需要注意不同的运算符之间的优先级问题。

-- 查看 age 小于 24 或者 age 大于 58 并且 type 不为空的数据
SELECT * FROM staff WHERE age < 24 OR age > 58 AND type IS NOT NULL;

但是我们看到把 type 为 NULL 的也选了进来，可我们明明选择的是 type 不为 NULL 的啊，这个时候我们就要说一下多个逻辑运算符之间的优先级问题了。

/*
A or B and C
这样一个式子，没有括号的话，肯定是先判断 A 和 B，但如果A为真，那么不好意思，B以及后面的那一长串就都不会判断了
所以我们应该使用括号括起来
 */
SELECT * FROM staff WHERE (age < 24 OR age > 58) AND type IS NOT NULL;

但是我们看到把 type 为 NULL 的也选了进来，可我们明明选择的是 type 不为 NULL 的啊，这个时候我们就要说一下多个逻辑运算符之间的优先级问题了。

去除重复值

SQL 使用 DISTINCT 关键字去除查询结果中的重复数据。例如，以下查询返回了 staff 表中所有的 type：

SELECT DISTINCT title FROM staff;
/*
高级工
中级工
工程师
技师
会计师
助理工程师
初级工
助理经济师
助理政工师
高级技师
经济师
政工师
高级工程师
高级经济师
高级工程师(教授级)
助理会计师
 */

首先，DISTINCT 位于 SELECT 之后而不是像其他过滤条件一样位于 WHERE 之后；其次，查询结果中重复的记录只会出现一次。与 DISTINCT 相反的是 ALL，用于返回不去重的结果。我们通常不需要加上 ALL 关键字，因为它是默认的行为。另外，为了消除重复值，数据库系统需要对结果进行排序，然后扫描重复值；因此，大量数据的重复值处理可能会降低查询的速度。

Oracle 中的 UNIQUE 等价于 DISTINCT，MySQL 中的 DISTINCTROW 等价于 DISTINCT。

模糊查找

我们之前介绍了如何利用 WHERE 子句中的查询条件过滤数据，包括比较运算符、逻辑运算符以及空值判断等。同时，我们也提到了 LIKE 运算符可以用于字符串的模糊查找。下面我们就来讨论一下 SQL 中的模糊匹配。

当需要查找的信息不太确定时，例如只记住了某个员工姓名的一部分，可以使用模糊查找的功能进行搜索。SQL 提供了两种模糊匹配的方法：LIKE 运算符 和 正则表达式函数。

LIKE 运算符

下面的语句查找 title 中包括 "工程" 的员工。

-- 记录比较多, 只贴一部分
SELECT id, title FROM staff WHERE title like '%工程%';
/*
01010001563    工程师
01010011697    助理工程师
01010010919    助理工程师
01010011334    助理工程师
01010001538    工程师
01010004588    工程师
01010001056    助理工程师
01010002557    高级工程师
01010002557    高级工程师
01010008848    工程师
01010010241    工程师
01010004397    工程师
01010011331    助理工程师
01010006378    工程师
01010002085    工程师
01010003570    助理工程师
01010004397    工程师
01010008778    助理工程师
01010008961    工程师
01010000221    高级工程师
 */

该语句使用了一个新的运算符：LIKE，LIKE 用于指定一个模式，并且返回匹配该模式的数据。LIKE 运算符支持两个通配符，用于指定模式：

• %，百分号可以匹配零个或者多个任意字符
• _，下划线可以匹配一个任意字符

以下是一些模式和匹配的字符串：

• LIKE 'en%'，匹配以 "en" 开始的字符串，例如 "english languages"、"end"
• LIKE '%en%'，匹配包含 "en" 的字符串，例如 "length"、"when are you"
• LIKE '%en'，匹配以 "en" 结束的字符串，例如 "ten"、"when"
• LIKE 'Be_'，匹配以 "Be" 开始，再加上一个任意字符的字符串，例如 "Bed"、"Bet"
• LIKE '_e%'，匹配一个任意字符加 "e" 开始的字符串，例如 "her"、"year"

如果想要执行相反的操作，返回不匹配某个模式的数据，可以使用 NOT LIKE 运算符。

"%" 和 "_" 是 LIKE 运算符中的通配符，如果需要查找的内容自身包含了 "%" 或者 "_" 时，例如想要知道哪些数据包含了 "10%"（百分之十，而不是以 10 开始的字符串），应该如何指定模式呢？这种情况需要用到转义字符（escape character）。

转义字符可以将通配符 "%" 和 "_" 进行转义，把它们当作普通字符使用。默认的转义字符为反斜杠（\）。因此，如果我想选择字符串中包含 "25%" 的，就应该这么写：

select xxx from xxx where xxx like "%25\%%";
--倒数第二个%的前面有\，表示转义，所以\%就是普通的%

SQL Server 支持更多的通配符：'[ad]' 匹配 'a' 和 'd'；'[a-d]' 匹配 'a' 、'b'、'c' 和 'd'；'[^ad]' 匹配除了 'a' 和 'd' 之外的其他字符。

在使用 LIKE 查找数据时，还需要注意的一个问题就是大小写。对于汉字，不需要区分大小写；但是英文字母却有大小写之分，"A" 和 "a" 是两个不同的字符。不过，4 种数据库对此采取了不同的处理方式：

• Oracle 和 PostgreSQL 默认区分 LIKE 中的大小写，PostgreSQL 提供了不区分大小写的 ILIKE 运算符；
• MySQL 和 SQL Server 默认不区分 LIKE 中的大小写。

正则表达式

正则表达式用于检索或者替换符合某个模式（规则）的文本，很多的编程语言和编辑工具都提供了正则表达式搜索和替换，比如文本编辑器 Notepad++。

正则表达式的具体使用不会说的特别细致，网上一大堆可以去搜索。这里简单提一下：

• ^ 匹配字符串的开头
• [a-zA-Z0-9] 匹配大小写字母或数字
• + 表示匹配前面的内容一次或多次
• . 匹配任何一个字符
• \. 匹配点号自身
• {2,4} 匹配前面的内容 2 次到 4次
• $ 匹配字符串的结束

我们看到这个正则跟主流编程语言的正则是比较类似的，但是如何使用正则表达式，我们需要调用哪个函数呢？

Oracle 和 MySQL 支持类似的正则表达式函数：REGEXP_LIKE(source_str, pattern [, match_type])。其中 source_str 表示被搜索的字符串，pattern 表示模式，match_type 指定可选的匹配方式，例如 i 表示不区分大小写，c 表示区分大小写。

但是对于PostgreSQL来讲，是通过波浪线 ~ 来进行匹配的。

• ~ 匹配某个正则表达式，区分大小写；
• ~* 匹配某个正则表达式，不区分大小写
• !~ 不匹配某个正则表达式，区分大小写；
• !~* 不匹配某个正则表达式，不区分大小写。

-- PostgreSQL
SELECT * FROM staff WHERE id ~ '0101001[1-5]{2}[4-9]{2}'
/*
01010011458    30    中级工    生产人员
01010011599    32    高级工    生产人员
01010011356    24    中级工    生产人员
 */
 
-- MySQL
SELECT * FROM staff WHERE REGEXP_LIKE(id, '0101001[1-5]{2}[4-9]{2}')
/*
01010011458    30    中级工    生产人员
01010011599    32    高级工    生产人员
01010011356    24    中级工    生产人员
 */

SQL 支持的正则非常强大，我们可以实现各种复杂的查询。

SQL Server 中没有提供相关的正则表达式函数或者运算符。

小结

在 SQL 中使用 WHERE 子句指定一个或者多个过滤条件，可以查找满足要求的数据。SQL 查询条件中支持各种比较运算符、逻辑运算符以及空值判断等。另外，DISITINCT 关键字可以去除查询结果中的重复记录。

SQL 支持使用模式匹配对文本内容进行模糊查找，主要的方式有两种：LIKE 运算符和正则表达式函数。其中，LIKE 运算符通用性更好，但是只能进行一些简单的模糊匹配；正则表达式函数功能更加强大，但是依赖于不同数据库的实现，虽然函数不同，但是语法都是差不多的。并且，数据库的正则语法非常类似于 Python 语言的正则，走的应该也是Perl风格的。