SQL 过滤

在实际的数据库应用中，我们很少需要查询表中的所有数据。更多时候，我们需要根据特定的条件来筛选数据，只获取我们真正需要的那部分信息。这就是数据过滤的作用。我们在前面的学习中其实已经接触过过滤了， 例如选出专业是计算机的学生，选出id小于10的学生等等

什么是数据过滤

数据过滤是指通过设置特定条件来限制查询结果的过程。在SQL中，我们主要通过WHERE子句来实现数据过滤。虽然有些情况下我们可能需要处理表中的所有行（比如清空临时表、批量更新所有记录），但大多数情况下，我们都希望将注意力集中在数据的一个子集上。

想象一下，你有一个学生信息表，里面包含全校几千名学生的信息。如果你只想查找计算机专业的学生，或者只想找2020年入学的新生，这时候就需要使用过滤条件来缩小查询范围。

条件的逻辑评估

AND操作符

当WHERE子句包含多个条件时，我们可以使用AND操作符来连接它们。使用AND连接的条件必须全部为真，这一行数据才会被包含在结果集中。

|
SELECT * FROM 学生表 
WHERE 专业 = '计算机' AND 入学年份 = 2020;

这个查询只会返回专业是计算机且入学年份是2020年的学生。如果一个学生是计算机专业但入学年份是2019年，或者入学年份是2020年但专业不是计算机，都不会出现在结果中。

OR操作符

与AND不同，当条件用OR操作符连接时，只要其中任何一个条件为真，这一行就会被包含在结果集中。

|
SELECT * FROM 学生表 
WHERE 专业 = '计算机' OR 专业 = '软件工程';

这个查询会返回所有计算机专业或软件工程专业的学生。

下面是OR操作符的真值表：

使用括号控制优先级

当查询包含三个或更多条件，并且同时使用AND和OR操作符时，你应该使用括号来明确表达你的意图。这不仅有助于数据库正确理解你的查询，也让代码更容易阅读。

|
SELECT * FROM 学生表 
WHERE 状态 = '在读' 
  AND (专业 = '计算机' OR 入学年份 < 2020);

这个查询查找所有在读的学生，并且要么是计算机专业，要么是2020年之前入学的。

NOT操作符

NOT操作符用于否定一个条件的结果。虽然数据库能够正确处理NOT操作符，但它通常会让查询变得难以理解。

|
-- 使用NOT的查询
SELECT * FROM 学生表 
WHERE 状态 = '在读' 
  AND NOT (专业 = '计算机' OR 入学年份 < 2020);
 
-- 等价的、更易理解的查询
SELECT * FROM 学生表 
WHERE 状态 = '在读' 
  AND 专业 != '计算机' 
  AND 入学年份 >= 2020;

构建查询条件

在深入了解不同类型的条件之前，我们需要理解条件是由什么组成的。一个完整的条件由一个或多个表达式以及操作符组成。 表达式可以是以下任何一种：

而操作符则包括：

相等与不等条件

相等条件

相等条件是最常见的过滤条件类型，它的形式是「列名 = 值」。这种条件用于查找某列的值等于特定值的记录。

|
-- 查找计算机专业的学生
SELECT * FROM 学生表 WHERE 专业 = '计算机';
 
-- 查找学号为20200001的学生
SELECT * FROM 学生表 WHERE 学号 = '20200001';
 
-- 查找学分为3的课程
SELECT * FROM 课程表 WHERE 学分 = 3;
 
-- 使用子查询作为条件值
SELECT * FROM 选课表 
WHERE 课程编号 = (

下面是一个更复杂的例子，展示了如何在连接查询中同时使用连接条件和过滤条件：

|
SELECT c.课程名称, s.学生姓名
FROM 选课表 sc
INNER JOIN 课程表 c ON sc.课程编号 = c.课程编号
INNER JOIN 学生表 s ON sc.学号 = s.学号
WHERE c.学分 = 3;

这个查询显示了所有选修3学分课程的学生信息。

不等条件

不等条件用于查找某列的值不等于特定值的记录。在SQL中，你可以使用!=或<>操作符来表示不等关系。

|
-- 查找非计算机专业的学生
SELECT * FROM 学生表 WHERE 专业 != '计算机';
 
-- 或者使用<>操作符
SELECT * FROM 学生表 WHERE 专业 <> '计算机';

在数据修改中使用相等条件

相等条件和不等条件经常用于数据修改操作。例如，假设你需要删除所有已毕业且毕业时间在2020年的学生记录：

|
DELETE FROM 学生表 
WHERE 状态 = '已毕业' AND YEAR(毕业时间) = 2020;

这个语句包含了两个相等条件：一个用于查找已毕业的学生，另一个检查毕业年份是否为2020年。

范围条件

除了检查某个表达式是否等于（或不等于）另一个表达式外，你还可以构建条件来检查表达式是否落在某个特定的范围内。这种类型的条件在处理数值或时间数据时特别常见。

基本范围查询

考虑下面的查询示例：

|
SELECT 学号, 姓名, 年龄, 入学日期
FROM 学生表
WHERE 入学日期 < '2020-01-01';

这个查询查找所有在2020年之前入学的学生。除了指定范围的上限，你还可能想要指定下限：

|
SELECT 学号, 姓名, 年龄, 入学日期
FROM 学生表
WHERE 入学日期 < '2020-01-01'
  AND 入学日期 >= '2018-01-01';

这个版本的查询检索所有在2018年或2019年入学的学生。

BETWEEN操作符

当你需要同时指定范围的上限和下限时，可以选择使用BETWEEN操作符，而不是使用两个单独的条件：

|
SELECT 学号, 姓名, 年龄, 入学日期
FROM 学生表
WHERE 入学日期 BETWEEN '2018-01-01' AND '2020-01-01';

使用BETWEEN操作符时需要注意以下几点：

1. 顺序很重要：你应该始终将范围的下限放在BETWEEN后面，上限放在AND后面。如果顺序颠倒，查询将不会返回任何结果。

|
-- 错误的写法，不会返回任何结果！！！！！！
SELECT 学号, 姓名, 年龄, 入学日期
FROM 学生表
WHERE 入学日期 BETWEEN '2020-01-01' AND '2018-01-01';

2. 范围是包含性的：BETWEEN操作符包含边界值。上面的查询实际上等同于：

|
SELECT 学号, 姓名, 年龄, 入学日期
FROM 学生表
WHERE 入学日期 >= '2018-01-01'
  AND 入学日期 <= '2020-01-01';

数值范围

除了日期，你也可以对数字建立范围条件。数值范围相对容易理解：

|
SELECT 课程编号, 课程名称, 学分, 学费
FROM 课程表
WHERE 学费 BETWEEN 3000 AND 5000;

这个查询返回所有学费在3000到5000元之间的课程。记住要先指定较小的数值。

字符串范围

虽然日期和数字的范围容易理解，但你也可以构建用于搜索字符串范围的条件，这可能有点难以可视化。

假设你想查找姓氏在某个字母范围内的学生。比如，你想找到所有姓氏以L到Z开头的学生：

|
SELECT 学号, 姓名, 专业
FROM 学生表
WHERE 姓名 BETWEEN 'L' AND 'Z';

要使用字符串范围，你需要了解字符集中字符的顺序（字符在字符集中的排序顺序称为排序规则）。

这里有一个更实际的例子，假设我们有学生的身份证号，想查找身份证号在某个范围内的学生：

|
SELECT 学号, 姓名, 身份证号
FROM 学生表
WHERE 身份证号 BETWEEN '320000000000000000' AND '329999999999999999';

这个查询查找所有江苏省（身份证前两位是32）的学生。

成员条件

在某些情况下，你不是想将表达式限制为单个值或值的范围，而是想将其限制为有限的值集合。例如，你可能想要查找所有专业为计算机, 软件工程, 数据科学或人工智能的学生。

使用多个OR条件

最直接的方法是使用多个OR条件：

|
SELECT 学号, 姓名, 专业, 年级
FROM 学生表
WHERE 专业 = '计算机' 
   OR 专业 = '软件工程'
   OR 专业 = '数据科学' 
   OR 专业 = '人工智能';

虽然这个WHERE子句（四个条件用OR连接）并不难写，但想象一下如果这个值集合包含10个或20个成员会怎么样。对于这种情况，你可以使用IN操作符：

IN操作符

|
SELECT 学号, 姓名, 专业, 年级
FROM 学生表
WHERE 专业 IN ('计算机', '软件工程', '数据科学', '人工智能');

使用IN操作符，无论集合中有多少个表达式，你都可以用单个条件来处理。

使用子查询

除了手动编写值集合，你还可以使用子查询来动态生成一个集合。例如，假设所有的技术类专业都有一个共同的专业类别代码「TECH」，你可以使用子查询从专业表中检索这些专业代码，而不是显式地列出它们：

|
SELECT 学号, 姓名, 专业, 年级
FROM 学生表
WHERE 专业 IN (
    SELECT 专业代码 
    FROM 专业表 
    WHERE 专业类别 = 'TECH'
);

子查询返回一组值，主查询检查专业列的值是否可以在子查询返回的集合中找到。 这种方法的优点是，即使专业表中添加了新的技术类专业，你的查询也会自动包含它们，而不需要修改查询语句。

NOT IN操作符

有时候你想查看某个特定表达式是否存在于表达式集合中，有时候你想查看表达式是否不存在。对于这种情况，你可以使用NOT IN操作符：

|
SELECT 学号, 姓名, 专业, 年级
FROM 学生表
WHERE 专业 NOT IN ('计算机', '软件工程', '数据科学', '人工智能');

这个查询查找所有非技术类专业的学生。

同样，你也可以将NOT IN与子查询结合使用：

|
SELECT 学号, 姓名, 专业, 年级
FROM 学生表
WHERE 专业 NOT IN (
    SELECT 专业代码 
    FROM 专业表 
    WHERE 专业类别 = 'TECH'
);

匹配条件

到目前为止，你已经了解了用于识别精确字符串, 字符串范围或字符串集合的条件；最后一种条件类型涉及部分字符串匹配。 例如，你可能想要查找所有姓氏以「张」开头的学生。你可以使用内置函数来截取姓名列的第一个字符：

|
SELECT 学号, 姓名, 专业
FROM 学生表
WHERE LEFT(姓名, 1) = '张';

虽然内置函数LEFT()可以完成工作，但它不能给你太多的灵活性。相反，你可以使用通配符来构建搜索表达式。

使用通配符

当搜索部分字符串匹配时，你可能对以下几种情况感兴趣：

• 以某个字符开头/结尾的字符串
• 以某个子字符串开头/结尾的字符串
• 在字符串中任何位置包含某个字符的字符串
• 在字符串中任何位置包含某个子字符串的字符串
• 具有特定格式的字符串，无论单个字符是什么

你可以使用下表中显示的通配符构建搜索表达式来识别这些和许多其他部分字符串匹配：

下划线字符代表单个字符，而百分号可以代表可变数量的字符。当构建使用搜索表达式的条件时，你使用LIKE操作符：

|
SELECT 姓名
FROM 学生表
WHERE 姓名 LIKE '张_';

这个查询查找所有姓张的学生（姓名为两个字符，第一个字符是「张」）。

下面是一些搜索表达式的示例：

你可以使用最后一个例子来查找符合身份证号格式的客户，比如：

|
SELECT 学号, 姓名, 身份证号
FROM 学生表
WHERE 身份证号 LIKE '______19______';

这个查询查找所有出生在20世纪（身份证号第7-8位是19）的学生。

复杂的通配符查询

通配符对于构建简单的搜索表达式效果很好；如果你的需求更复杂一些，你可以使用多个搜索表达式：

|
SELECT 学号, 姓名, 专业
FROM 学生表
WHERE 姓名 LIKE '王%' OR 姓名 LIKE '李%';

这个查询查找所有姓王或姓李的学生。

使用正则表达式(简单了解)

如果你发现通配符没有提供足够的灵活性，你可以使用正则表达式来构建搜索表达式。正则表达式本质上是一种增强版的搜索表达式。如果你是SQL新手但用过Perl等编程语言，那么你可能已经非常熟悉正则表达式了。

下面是使用MySQL正则表达式实现前面查询（查找所有姓王或姓李的学生）的写法：

|
SELECT 学号, 姓名, 专业
FROM 学生表
WHERE 姓名 REGEXP '^[王李]';

REGEXP操作符接受一个正则表达式（在这个例子中是'^[王李]'）并将其应用到左侧表达式（姓名列）。现在查询包含了一个使用正则表达式的条件，而不是两个使用通配符的条件。

这里是一些更多的正则表达式示例：

|
-- 查找电话号码以138开头的学生
SELECT 学号, 姓名, 电话
FROM 学生表
WHERE 电话 REGEXP '^138';
 
-- 查找邮箱以edu.cn结尾的学生
SELECT 学号, 姓名, 邮箱
FROM 学生表  
WHERE 邮箱 REGEXP '\.edu\.cn$';
 
-- 查找姓名包含数字的学生（处理一些特殊情况）
SELECT 学号, 姓名, 专业
FROM 学生表
WHERE 姓名 REGEXP '[0-9]';

NULL值：数据库中的特殊存在

NULL表示值的缺失；例如，在学生毕业之前，学生表中的毕业日期列应该是NULL。没有任何值可以分配给毕业日期列，因为在这种情况下这样做是没有意义的。 NULL是一个有点棘手的概念，因为NULL有不同的含义：

• 不适用：比如某个交易是在ATM机上进行的，那么员工ID列就不适用
• 值尚未知道：比如在创建客户记录时还不知道身份证号
• 值未定义：比如为尚未添加到数据库中的课程创建选课记录

测试NULL值

要测试表达式是否为NULL，你需要使用IS NULL操作符：

|
SELECT 学号, 姓名, 专业, 导师ID
FROM 学生表
WHERE 导师ID IS NULL;

这个查询返回所有还没有分配导师的学生。

下面是使用= NULL而不是IS NULL的相同查询：

|
SELECT 学号, 姓名, 专业, 导师ID
FROM 学生表
WHERE 导师ID = NULL;

上述查询可以解析和执行，但不会返回任何行。这是SQL新手常犯的错误，而且数据库服务器不会提醒你这个错误，所以在构建测试NULL的条件时要小心。

测试非NULL值

如果你想查看某一列是否已分配值，可以使用IS NOT NULL操作符：

|
SELECT 学号, 姓名, 专业, 导师ID
FROM 学生表
WHERE 导师ID IS NOT NULL;

这个版本的查询返回所有已经分配了导师的学生。

NULL值的陷阱

在结束NULL话题之前，让我们研究一个更多的潜在陷阱。假设你被要求识别所有不是由张教授（导师ID为101）指导的学生。你的第一反应可能是这样做：

|
SELECT 学号, 姓名, 专业, 导师ID
FROM 学生表
WHERE 导师ID != 101;

虽然这些学生确实不是由张教授指导的，但如果你仔细查看数据，你会发现还有一些不是由张教授指导的学生没有列在这里。那些学生就是还没有分配导师的学生，他们的导师ID列是NULL。

要正确回答这个问题，你需要考虑导师ID列中可能包含NULL的情况：

|
SELECT 学号, 姓名, 专业, 导师ID
FROM 学生表
WHERE 导师ID != 101 OR 导师ID IS NULL;

现在结果集包括了所有不是由张教授指导的学生，包括那些还没有分配导师的学生。

这可以用一个更清晰的方式来表达：

|
SELECT 学号, 姓名, 专业, 导师ID
FROM 学生表
WHERE 导师ID IS NULL OR 导师ID != 101;

实际应用示例

让我们看一个更实际的例子。假设你想找出所有需要关注的学生记录：

|
-- 查找信息不完整的学生
SELECT 学号, 姓名, 专业, 电话, 邮箱, 导师ID
FROM 学生表
WHERE 电话 IS NULL 
   OR 邮箱 IS NULL 
   OR 导师ID IS NULL;

这个查询帮助你识别哪些学生的信息需要补充完善。

NULL在计算中的表现

NULL值在算术运算中也有特殊的行为：

|
-- 任何包含NULL的算术运算结果都是NULL
SELECT 学号, 姓名, 
       (期中成绩 + 期末成绩) / 2 AS 平均成绩
FROM 成绩表
WHERE 学号 = '20200001';

如果期中成绩或期末成绩中任何一个是NULL，那么平均成绩的计算结果也会是NULL。在这种情况下，你可能需要使用COALESCE或ISNULL函数来处理NULL值：

|
-- 使用COALESCE处理NULL值
SELECT 学号, 姓名, 
       (COALESCE(期中成绩, 0) + COALESCE(期末成绩, 0)) / 2 AS 平均成绩
FROM 成绩表
WHERE 学号 = '20200001';

知识检测

以下练习测试你对过滤条件的理解。 假设我们有以下课程选择数据：

练习1

以下过滤条件会返回哪些选课ID？

|
WHERE 选课日期 < '2023-09-05' AND (课程类型 = '选修' OR 学分 > 3)

练习2

以下过滤条件会返回哪些选课ID？

|
WHERE 学号 IN ('2023001', '2023003') AND NOT (课程类型 = '选修' OR 学分 > 3)

练习3

构建一个查询，检索所有在2023年9月3日或之后选课的记录。

|
SELECT * FROM 选课表
WHERE 选课日期 >= '2023-09-03';

练习4

构建一个查询，查找所有学号以「2023」开头且选择了必修课程的学生记录。

|
SELECT * FROM 选课表
WHERE 学号 LIKE '2023%' AND 课程类型 = '必修';

|
SELECT * FROM 选课表
WHERE 学号 REGEXP '^2023' AND 课程类型 = '必修';