{T}

数据过滤

数据过滤是 SQL 查询的核心技能。通过 WHERE 子句，可以精确筛选出符合条件的数据，减少不必要的数据传输，提升查询效率。

比较运算符

基本比较运算符

运算符含义示例
=等于score = 80
<> 或 !=不等于score <> 80
<小于score < 80
<=小于等于score <= 80
>大于score > 80
>=大于等于score >= 80

特殊比较运算符

运算符含义示例
BETWEEN在范围内score BETWEEN 80 AND 90
IS NULL为空值score IS NULL
IS NOT NULL非空值score IS NOT NULL

注意：不同 DBMS 支持的运算符可能不同。MySQL 不支持 !> 和 !<，Access 不支持 !=。

比较运算示例

sql
-- 查询最大生命值大于 6000 的英雄
SELECT name, hp_max FROM heros WHERE hp_max > 6000;

-- 查询最大生命值在 5399 到 6811 之间的英雄（包含边界值）
SELECT name, hp_max FROM heros WHERE hp_max BETWEEN 5399 AND 6811;

-- 查询最大生命值为空的英雄
SELECT name, hp_max FROM heros WHERE hp_max IS NULL;

-- 查询最大生命值不为空的英雄
SELECT name, hp_max FROM heros WHERE hp_max IS NOT NULL;

逻辑运算符

AND 运算符

AND 用于组合多个条件，所有条件都必须满足：

sql
-- 查询最大生命值 > 6000 且最大法力 > 1700 的英雄
SELECT name, hp_max, mp_max 
FROM heros 
WHERE hp_max > 6000 AND mp_max > 1700 
ORDER BY (hp_max + mp_max) DESC;

OR 运算符

OR 用于组合多个条件，满足任意一个条件即可：

sql
-- 查询最大生命值+最大法力值 > 8000 或（最大生命值 > 6000 且最大法力 > 1700）
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;

NOT 运算符

NOT 用于否定条件：

sql
-- 查询不是法师的英雄
SELECT name, role_main FROM heros WHERE NOT role_main = '法师';
-- 等价于
SELECT name, role_main FROM heros WHERE role_main <> '法师';

IN 运算符

IN 用于匹配一组值中的任意一个：

sql
-- 查询主要定位是法师或射手的英雄
SELECT name, role_main 
FROM heros 
WHERE role_main IN ('法师', '射手');

运算符优先级

优先级从高到低：() > NOT > AND > OR

sql
-- 示例 1：AND 优先于 OR
-- 解析为：hp_max > 6000 OR (mp_max > 1700 AND name = '张飞')
SELECT name, hp_max, mp_max 
FROM heros 
WHERE hp_max > 6000 OR mp_max > 1700 AND name = '张飞';

-- 示例 2：使用括号改变优先级
-- 解析为：(hp_max > 6000 OR mp_max > 1700) AND name = '张飞'
SELECT name, hp_max, mp_max 
FROM heros 
WHERE (hp_max > 6000 OR mp_max > 1700) AND name = '张飞';

复杂条件组合

sql
-- 查询主要定位或次要定位是法师或射手，且上线时间不在 2016-01-01 到 2017-01-01 之间的英雄
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;

通配符过滤

LIKE 操作符与通配符配合使用，实现模糊匹配。

百分号通配符（%）

% 匹配任意字符出现任意次数（包括 0 次）：

sql
-- 查询名字中包含"太"字的英雄
SELECT name FROM heros WHERE name LIKE '%太%';
-- 结果：太乙真人、东皇太一

-- 查询以"张"开头的英雄
SELECT name FROM heros WHERE name LIKE '张%';
-- 结果：张飞、张良

-- 查询以"飞"结尾的英雄
SELECT name FROM heros WHERE name LIKE '%飞';
-- 结果：张飞、露娜-紫霞仙子飞

下划线通配符（_）

_ 匹配单个字符：

sql
-- 查询名字第二个字是"太"的英雄
SELECT name FROM heros WHERE name LIKE '_太%';
-- 结果：东皇太一（太乙真人的"太"是第一个字，不匹配）

通配符对比

通配符含义示例
%匹配任意数量字符LIKE '%太%' 匹配包含"太"
_匹配单个字符LIKE '_太%' 匹配第二个字是"太"

通配符使用注意事项

  1. 性能问题：通配符搜索需要消耗更多资源，尽量少用
  2. 索引失效：以 % 开头的 LIKE 查询会导致索引失效
sql
-- 索引有效
SELECT name FROM heros WHERE name LIKE '张%';

-- 索引失效（全表扫描）
SELECT name FROM heros WHERE name LIKE '%张';
SELECT name FROM heros WHERE name LIKE '%张%';
  1. 大小写敏感：取决于数据库配置
sql
-- 可能匹配不到 'LIU BEI'
SELECT name FROM heros WHERE name LIKE 'liu%';

-- 强制区分大小写
SELECT name FROM heros WHERE name LIKE BINARY 'liu%';

NULL 值处理

NULL 的特殊性

NULL 表示"未知"或"不存在"，不等于任何值，包括 NULL 本身：

sql
-- 错误：不能使用 = 判断 NULL
SELECT name FROM heros WHERE hp_max = NULL;  -- 错误！

-- 正确：使用 IS NULL
SELECT name FROM heros WHERE hp_max IS NULL;

-- 正确：使用 IS NOT NULL
SELECT name FROM heros WHERE hp_max IS NOT NULL;

NULL 与聚合函数

sql
-- COUNT(*) 计算所有行，包括 NULL
SELECT COUNT(*) FROM heros;

-- COUNT(column) 忽略 NULL 值
SELECT COUNT(hp_max) FROM heros;

-- AVG、SUM、MAX、MIN 忽略 NULL 值
SELECT AVG(hp_max) FROM heros;

NULL 安全比较

sql
-- MySQL 使用 <=> 安全等于运算符
SELECT name FROM heros WHERE hp_max <=> NULL;

-- 使用 COALESCE 提供默认值
SELECT name, COALESCE(hp_max, 0) AS hp FROM heros;

-- 使用 IFNULL 提供默认值（MySQL）
SELECT name, IFNULL(hp_max, 0) AS hp FROM heros;

实用过滤技巧

日期过滤

sql
-- 查询特定日期
SELECT name, birthdate FROM heros WHERE birthdate = '2016-03-24';

-- 查询日期范围
SELECT name, birthdate FROM heros WHERE birthdate BETWEEN '2016-01-01' AND '2016-12-31';

-- 使用日期函数
SELECT name, birthdate FROM heros WHERE YEAR(birthdate) = 2016;
SELECT name, birthdate FROM heros WHERE DATE(birthdate) = '2016-03-24';

字符串过滤

sql
-- 精确匹配
SELECT name FROM heros WHERE name = '张飞';

-- 大小写不敏感
SELECT name FROM heros WHERE LOWER(name) = 'zhang fei';

-- 去除空格后匹配
SELECT name FROM heros WHERE TRIM(name) = '张飞';

数值过滤

sql
-- 整数比较
SELECT name, hp_max FROM heros WHERE hp_max > 6000;

-- 浮点数比较（注意精度问题）
SELECT name, height FROM player WHERE height BETWEEN 1.90 AND 2.00;

-- 使用 ROUND 处理精度
SELECT name, height FROM player WHERE ROUND(height, 2) = 1.93;

过滤最佳实践

1. 选择性高的列优先

sql
-- 好：选择性高的列（值分布分散）
SELECT * FROM heros WHERE name = '张飞';

-- 差：选择性低的列（值分布集中）
SELECT * FROM heros WHERE role_main = '战士';

2. 避免在 WHERE 中使用函数

sql
-- 差：索引失效
SELECT name FROM heros WHERE YEAR(birthdate) = 2016;

-- 好：使用范围查询
SELECT name FROM heros 
WHERE birthdate >= '2016-01-01' AND birthdate < '2017-01-01';

3. 合理使用 IN 和 EXISTS

sql
-- IN 适合子查询结果集较小的情况
SELECT name FROM heros 
WHERE role_main IN ('法师', '射手', '战士');

-- EXISTS 适合子查询需要关联外部表的情况
SELECT name FROM heros h1
WHERE EXISTS (
    SELECT 1 FROM heros h2 
    WHERE h2.role_main = h1.role_main 
    AND h2.hp_max > 7000
);

4. 使用覆盖索引

sql
-- 好：只查询索引列
SELECT id, name FROM heros WHERE name LIKE '张%';

-- 差：查询非索引列
SELECT * FROM heros WHERE name LIKE '张%';

总结

比较运算符

类型运算符
比较=, <>, !=, <, <=, >, >=
范围BETWEEN, IN
空值IS NULL, IS NOT NULL
模糊LIKE

逻辑运算符

运算符含义优先级
NOT非高
AND与中
OR或低

通配符

通配符含义
%任意数量字符
_单个字符

最佳实践

  1. 使用索引列：优先在索引列上过滤
  2. 避免函数：不在 WHERE 中对列使用函数
  3. 注意 NULL：使用 IS NULL 判断空值
  4. 通配符优化：避免以 % 开头的 LIKE
  5. 合理组合：使用括号明确优先级