用户数据中出现了学生成绩相关的数据库设计正好可以结合 SQL 数据过滤的实操场景来讲解。这里我先从最常用的成绩单查询场景出发讲清楚过滤条件的组合与去重逻辑再看复杂查询的优化技巧。希望这篇的思路和示例代码能直接帮到你。1. 数据过滤的底层逻辑先定位再输出1.1 过滤的本质是行级筛选数据过滤说白了就是在一张“满是数据的大表格”里按照你给出的条件一行一行地把不需要的记录挡在外面只保留符合条件的行。我习惯把它想象成一个漏斗漏斗口宽进来的数据多漏斗颈细筛掉的数据多。WHERE 子句就是那个漏斗颈。这个“逐行筛选”的过程是理解 SQL 过滤的第一步。很多新手会把 WHERE 和 SELECT 的顺序搞混认为先写 SELECT 就先生效其实数据库执行的时候是先从磁盘或内存里把整张表的记录拿出来然后用 FROM 后面的表按 WHERE 条件做逐行判断符合条件的才进入下一步。等到 SELECT 真正输出的时候数据已经是“筛选后剩下的部分”了。我刚入行的时候做过一张学生成绩表表里有 3000 多条记录每天要按班级、科目、分数段导出一份成绩单。当时我图的简单直接用 SELECT 把所有列都输出再放到 Excel 里手工筛选。后来有一次数据量突然翻到 3 万条Excel 直接卡死逼着我老老实实学 WHERE。现在回头看数据过滤这个技能越早主动掌握越好因为它是所有 SQL 查询的骨架。1.2 SQL 执行顺序决定了你能在 WHERE 里干什么SQL 语句表面上是从 SELECT 开始写的但数据库的引擎执行顺序并不是按你书写的顺序来跑的。对于一条最常见的查询来说实际的逻辑顺序大致是FROM确定要从哪张表取数WHERE对 FROM 取到的每一行记录做条件判断GROUP BY按字段分组HAVING对分组后的结果做二次过滤SELECT挑选输出哪些列ORDER BY排序LIMIT / TOP控制返回行数这个顺序看起来枯燥但我建议你把它当成一个“心法”来背。因为它直接决定了一个新手最容易踩的坑WHERE 子句里不能用 SELECT 里定义的列别名。比如SELECT stu_name, score AS s FROM score WHERE s 60;在 MySQL、PostgreSQL、SQL Server 里这条语句通常都会报错“找不到列 s”因为执行 WHERE 的时候SELECT 还没开始处理输出列AS s 还没有诞生。遇到这种情况要么在 WHERE 里直接写原始列名SELECT stu_name, score AS s FROM score WHERE score 60;要么套一层子查询SELECT t.stu_name, t.s FROM ( SELECT stu_name, score AS s FROM score ) t WHERE t.s 60;很多人会觉得套子查询非常啰嗦但当你遇到复杂报表的时候这反而是最干净的写法因为内层查出来的临时结果集本身就是一张“新的表”外层可以随意过滤它。同样的逻辑在 JOIN 里也成立。如果在 LEFT JOIN 之后用 WHERE 去过滤右表的字段会发现 LEFT JOIN 被“改成”了 INNER JOIN。因为 LEFT JOIN 会把右表没有匹配的行保留下来这些行的右表字段全是 NULL而你用 WHERE 一过滤NULL 不满足条件就又被删掉了。这里我后续在讲 NULL 时还会展开但先把这个“执行顺序决定行为”的意识建立起来。2. 条件表达式的编排从单条件到多条件组合2.1 比较运算符与“不等于”的写法差异过滤条件最基础的就是比较运算。、、、、 这五个符号几乎没有歧义谁都能看懂。真正容易出问题的是“不等于”SQL 里有两个写法 和 !。 是 SQL 标准的写法几乎在所有数据库里都能用! 是编程语言风格的写法MySQL 里能用SQL Server 里也能用PostgreSQL 里也能用但有些老牌的数据库或某些兼容模式下就不支持。我自己写的时候一律用 不是因为它多高级纯粹是为了减少换数据库时的意外报错。还得提醒一个容易忽略的值类型问题比较运算符要求两边的类型能互相换算。如果拿字符串类型跟数值比较数据库通常会做隐式转换。比如SELECT * FROM score WHERE score 60;score 是数值列60 是字符串MySQL 会先把 60 转成数值 60再把 score 列转成数值比较。看起来不影响结果但是一旦 score 列里面的值比较复杂或者字段类型本身是 VARCHAR隐式转换就可能造成无法命中索引这个我们在后面性能部分会重点说。2.2 NULL 的坑三值逻辑NULL 是 SQL 里一个非常特殊的存在它不表示 0也不表示空字符串它表示“不知道”或“不存在”。这就引出了一个叫“三值逻辑”的概念在 SQL 里比较运算的结果除了 TRUE 和 FALSE还有 UNKNOWN。所有对 NULL 做普通比较的表达式返回的都是 UNKNOWN被 WHERE 过滤掉。所以SELECT * FROM score WHERE score NULL;这条语句永远一条记录都查不出来。哪怕 score 列里真的有 NULL 值也不会被匹配。正确写法是SELECT * FROM score WHERE score IS NULL;同理如果要筛选“分数不为空”的记录SELECT * FROM score WHERE score IS NOT NULL;我见过很多新手在这里反复踩坑最典型的就是把 IS NULL 直接改成 NULL然后排查半天也找不到原因。这里有一个日常类比NULL 就像你并不知道一个人的联系方式你在通讯录里找“联系方式等于空的人”系统并不知道该怎么匹配“空”不是一个具体的联系人它只能靠“查一下联系人这一栏是否为空”这个特殊动作来确定。除此之外NULL 还有一个算术行为任何数值和 NULL 做加减乘除结果都是 NULL不是原值。比如想要算总分时SELECT score_1 score_2 score_3 AS total_score FROM score;只要 score_2 是 NULL整个 total_score 就是 NULL。遇到这种情况要么用 COALESCE 函数把 NULL 转成 0要么用 IS NULL 先做条件判断把缺失的字段单独挑出来处理。这个细节在做成绩汇总时特别重要否则你会莫名其妙地发现“明明成绩都不低总分却不出数”。2.3 AND / OR 优先级与括号多条件组合时AND 和 OR 的优先级是个老掉牙但永远有人踩的坑。AND 的优先级高于 OR也就是数据库先处理 AND再处理 OR。这跟数学里的“先乘除后加减”逻辑很像。举个例子。我们要筛选“三班且成绩大于 60或者五班且成绩大于 80”的学生SELECT * FROM score WHERE class_id 3 AND score 60 OR class_id 5 AND score 80;这样的写法因为 AND 优先结果是对的。但如果漏掉了想表达的意思比如把“班级为 3 班的成绩大于 60 分或者成绩大于 80 分”写成SELECT * FROM score WHERE class_id 3 AND score 60 OR score 80;它的意思是class_id 3 且 score 60 的所有记录再加上 score 80 的全部记录。后者已经包含了所有班级完全不是“三班 80 分”这种窄范围。要避免这种歧义最稳妥的办法就是加括号SELECT * FROM score WHERE class_id 3 AND (score 60 OR score 80);我自己的习惯是只要条件里同时出现 AND 和 OR一律加括号哪怕有时括号是多余的。写代码是给人看的清晰比聪明重要。数据库不会因为少一个括号就报错但业务同事会因为多了一个错误条件而出错误报表两相比较括号的成本太低了。2.4 BETWEEN、IN、LIKE 的边界行为这三个运算符在实际过滤中非常常用但边界行为很容易被忽略。BETWEEN 是包含边界值的。BETWEEN 60 AND 80 实际上等价于 score 60 AND score 80。注意它不是等价于 score 60 AND score 80。我之前做成绩区间统计用 BETWEEN 60 AND 79 表示“及格”结果 79 分的被算了进去80 分的没被算进去导致统计口径跟领导预期的“80 分以上才算优秀”对不上来回对了好几遍才发现是边界包含的问题。IN 的作用是匹配一组固定值。比如筛选一班、三班、五班SELECT * FROM score WHERE class_id IN (1, 3, 5);这个大家都会用但它有一个非常隐蔽的坑如果 IN 后面的列表里有 NULL倒不会出错但如果子查询返回的值包含 NULL那么配合 NOT IN 就会出大问题。SELECT * FROM score WHERE class_id NOT IN (1, 3, 5);这条看起来是“查不属于 1、3、5 班的数据”但如果把 1、3、5 换成从另一个子查询得来的集合而子查询结果里存在 NULL那么整个 NOT IN 会返回空集——不会报错但一条数据都没有。原因是当 class_id 4而集合里有一个 NULL 时4 不等于 NULL这个判断结果是 UNKNOWNUNKNOWN 会被过滤掉所以一行都留不下来。如果你真的想表达“排除某些班级”而且要安全处理 NULL最好用 NOT EXISTS 替代 NOT IN这个我们在后续章节讲子查询时再提。LIKE 是模糊匹配的关键工具。% 表示任意长度的任意字符_ 表示一个任意字符。比如SELECT * FROM student WHERE stu_name LIKE 张%;会查出所有姓张的学生。如果想查第二个字是“三”的姓名SELECT * FROM student WHERE stu_name LIKE _三%;LIKE 还有一个转义问题如果数据本身包含 % 或 _ 字符比如课程名称叫“C语言_基础”直接写 LIKE %C语言_基础%下划线会被当成通配符匹配出一个奇怪的集合。解决方法是显式指定转义字符SELECT * FROM course WHERE course_name LIKE %C语言\_基础% ESCAPE \;ESCAPE 后面的字符表示紧跟其后的通配符按普通字符处理。这个用法用得不多但真遇到了卡住你半天的情况。因为没人会告诉你数据里还有这些特殊字符你只会奇怪“明明有这条记录为什么查不出来”。3. 去重过滤DISTINCT 与窗口函数的实战选择3.1 DISTINCT 只能做显式去重数据过滤有时候不只是“去掉不满足条件的行”还需要“去掉重复的行”。最直接的就是 DISTINCTSELECT DISTINCT class_id FROM score;这样能查出所有出现的班级不会重复。但 DISTINCT 有一个关键限制它对 SELECT 后面出现的整组列做去重。什么意思呢比如SELECT DISTINCT class_id, subject FROM score;它会把 class_id 和 subject 的组合认为是“一行”来去重。一班语文、一班数学算是两条不同记录因为 subject 不同所以不会被合并。如果你只是想看“有哪些班级”却误写成查两个字段结果会比你预想的多。DISTINCT 还有一个我认为更值得注意的局限它只能去掉整行完全一样的重复项并不能按某列去重并保留一行的完整信息。比如学生选修课表里每个学生有多条选课记录现在想取每个学生最新选课的那条DISTINCT 就完全做不到。这时候就需要窗口函数。3.2 排名窗口函数的“最新记录”过滤窗口函数 ROW_NUMBER() 是处理“分组取最新”场景的利器。它可以在不合并行的情况下给每一行按分组编号。比如学生选课表 enrollstudent_idcourse_nameenroll_date101数学2024-09-01101英语2024-09-10102数学2024-09-02要取每个学生最新的一条选课记录写法如下SELECT student_id, course_name, enroll_date FROM ( SELECT student_id, course_name, enroll_date, ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY enroll_date DESC) AS rn FROM enroll ) t WHERE rn 1;内层查询给每个学生按 enroll_date 倒序编号日期最新的编号为 1外层用 WHERE rn 1 过滤就拿到了每个学生最新的一条记录。这个写法比 DISTINCT 灵活得多因为它保留了所有原始列信息而且筛选条件可以随意改。比如要取每个学生“第二个最早”的记录就把 rn 2 即可。这种技巧在处理数据迁移、对账、埋点日志去重时非常常见。同样用“去重”这个热词时我还推荐另一种思路如果确认数据里存在完全重复的行可以用 GROUP BY 把所有判断重复的列分组再聚合其他列。比如统计班级数SELECT class_id, COUNT(*) AS cnt FROM score GROUP BY class_id;这既能去重又能计数一步到位。难点在于你要搞清楚“去重的键”是什么是单个字段还是多个字段的组合。这个决定了用 DISTINCT、GROUP BY 还是 ROW_NUMBER()。3.3 去重场景的选型建议我在实际业务里总结的选型逻辑很简单只要整行完全重复想直接去掉优先用 DISTINCT要看某个字段值的分布比如有哪些班级、哪些科目用 DISTINCT 或 GROUP BY 均可要按组保留一条明细记录比如每位用户最新登录、每张订单最后状态用 ROW_NUMBER() 外层过滤要去重后还要做聚合统计比如按班级算平均分用 GROUP BY。这套选型逻辑听上去很基础但很多工作了三五年的开发都可能搞混。我见过一个线上报表原意是统计每个商品的最近一次售价有人用 DISTINCT 做出来数据错乱又有人用子查询做出来性能极差最后改成窗口函数一次解决。窗口函数不复杂只是平时没有机会练手建议你在本地数据库里建一张 1 万行的表多试几组 PARTITION BY 和 ORDER BY 组合很快就熟了。4. 过滤语句的性能索引、通配符与执行计划4.1 索引命中三件事等值、范围、前缀过滤条件写得对结果对但跑得很慢同样是个大问题。我在处理慢 SQL 优化时第一件事永远都是看 WHERE 条件能不能命中索引。只要过滤字段上有合适的索引数据库就不用从头到尾扫全表性能能提升一个量级以上。让索引生效的前提主要有三个第一过滤列尽量用等值条件比如 WHERE student_id 101这里 student_id 只要建了索引直接按索引树查找。范围条件也能用索引但效率比等值稍低比如 WHERE score 60 会走索引范围扫描。第二不要在索引列上做函数或运算。比如SELECT * FROM score WHERE YEAR(create_time) 2024;这条语句在 create_time 有索引的前提下依然会慢。因为数据库要对每一行的 create_time 先执行 YEAR() 函数计算再拿结果跟 2024 比原来的索引就用不上了。正确写法是SELECT * FROM score WHERE create_time 2024-01-01 AND create_time 2025-01-01;这样一来create_time 就变成了一个范围条件索引就能正常走。第三避免隐式类型转换。比如字段 score 是 VARCHAR却用 WHERE score 60 去查数据库会尝试把 score 列的值全部转成数值再跟 60 比这就相当于在索引列上做了函数运算。正确做法是让参数的类型和字段类型保持一致如果字段是 VARCHAR就写 WHERE score 60。这三个原则不算高深但做起来需要养成习惯。尤其是你会发现很多 ORM 框架会自动帮程序员把字段包一层函数导致线上 SQL 慢得离谱最后排查发现就是这层函数把索引毁掉的。4.2 LIKE 模糊匹配的前缀优先原则LIKE 是过滤中性能差距最大的一个点。规则很简单WHERE name LIKE 张% -- 前缀匹配能走索引 WHERE name LIKE %三% -- 包含匹配无法走索引 WHERE name LIKE %三 -- 后缀匹配无法走索引数据库的索引本质上是一棵排好序的树前缀是确定的树可以按顺序查找前缀不确定树就没法定位起点只能全表扫描。所以写模糊条件时要尽量把常量放在前面把通配符放在后面优先命中前缀匹配。如果业务需求真的需要包含匹配比如搜索姓名里含某个关键词前缀匹配解决不了有两个思路一是用全文索引MySQL 的 FULLTEXT适合大量文本场景二是尽量缩小其他先决条件的范围比如先按班级、按状态过滤把数据量先减下来再做 LIKE避免在小范围里全表扫。我实际调优过一张 1000 万行的商品表原本搜索词是 %手机%查询耗时 8 秒后来业务方改了需求允许只按前缀搜索查询耗时直接降到 0.05 秒。性能差别就是这么大。在跟业务方提方案时可以优先把精度要求摆在前面想办法让他们接受前缀搜索实在不行再考虑索引方案。4.3 慢查询定位看执行计划写 SQL 的时候谁都说不准优化器到底会怎么跑最好的办法就是直接看执行计划。MySQL 里在查询前面加 EXPLAINSQL Server 里用 SET SHOWPLAN_ALLPostgreSQL 里用 EXPLAIN ANALYZE。例如EXPLAIN SELECT * FROM score WHERE class_id 1 AND score 80;执行计划里最重要的字段是 type从好到差大致是 const、eq_ref、ref、range、index、ALL。ALL 代表全表扫描是性能最差的情况。还有 rows它显示优化器预估扫描的行数这个数字越接近表的总行数越说明过滤没生效。我第一次用 EXPLAIN 是被一个线上报表逼的。一张订单表 800 万行按商户编号和创建时间过滤查询要 20 多秒。EXPLAIN 一看type 是 ALLrows 显示 800 万说明索引完全没走到。后来检查发现过滤字段上根本没有建索引。建了组合索引之后type 变成了 rangerows 降到 5 万查询时间降到 0.3 秒以内。从此以后凡是慢查询我第一时间先看执行计划不看执行计划瞎优化就是盲人摸象。5. 动态过滤条件的安全底线SQL 注入的攻与防5.1 注入是怎么发生的字符串拼接数据过滤既然经常要处理动态条件比如用户在前端输入一个班级名称后端拼接成 SQL 去查那就必须正视 SQL 注入这个问题。虽然听起来像网络安全专题但做数据查询的人如果不懂早晚会把线上数据搞到不可收拾。危险代码长这样以 Python 为例sql SELECT * FROM student WHERE class_name class_name cursor.execute(sql)如果 class_name 是用户传进来的攻击者在表单里填这一段 OR 11拼出来的 SQL 就是SELECT * FROM student WHERE class_name OR 11这个条件永远为真整张表的学生信息都会被查出来。如果攻击者再结合 UNION 查询其他表甚至可以直接读取用户表、订单表后果不堪设想。这个攻击之所以能成功根源在于 “用户输入被当成了 SQL 代码的一部分”执行。过滤条件的本意是只让数据按预期输出但因为拼接输入里的单引号改变了 SQL 的结构。我早期练手写管理系统时曾经在登录功能里直接用字符串拼接验证某个账号密码sql SELECT * FROM user WHERE username username AND password password 当时根本没有安全意识直到后来读到“万能密码”示例才冒冷汗。攻击者在用户名框输入 admin --密码框随便填拼出来的 SQL 变成SELECT * FROM user WHERE username admin -- AND password xxx在 SQL 注释符 -- 之后的所有内容都会被忽略于是这条 SQL 退化成只校验用户名不校验密码。只要知道一个用户名是 admin就能直接登录系统。这就是热词里“万能密码绕过”的原理。它让 SQL 的过滤逻辑彻底失效从一个“按条件筛选”变成了“无条件通过”。5.2 参数化查询才是正解针对 SQL 注入最有效的防御不是写各种过滤函数而是使用参数化查询。它的核心思想是把 SQL 的结构和用户输入的数据分开传递数据库先解析 SQL 结构再把参数当成纯数据注入而不是当成 SQL 代码来解释。Python 的写法是用占位符sql SELECT * FROM student WHERE class_name %s cursor.execute(sql, (class_name,))Java 用 PreparedStatementPreparedStatement ps conn.prepareStatement(SELECT * FROM student WHERE class_name ?); ps.setString(1, className);PHP 用 PDO$stmt $pdo-prepare(SELECT * FROM student WHERE class_name ?); $stmt-execute([$className]);我在给团队做代码评审时有一条硬性规定动态 SQL 一律禁止直接拼接字符串必须用参数化。因为再完善的过滤函数都有可能被绕过单引号、双引号、注释符、十六进制编码等花式变体能玩出各种花样而参数化查询是从语法层面杜绝了注入的可能性。这里有个小技巧如果一个查询语句里既有固定条件又有用户可控的排序字段、表名这种无法参数化的部分那就要单独做一个白名单校验而不是盲目拼接。比如排序字段只能从 name、score、create_time 里选就在代码里先校验后再拼进 SQL。5.3 过滤条件的权限边界最后补充一点数据过滤不只是技术层面的 WHERE 条件还隐含了权限控制。一个普通的业务查询如果用户传了 class_id 1后端应该同时带上当前登录用户的查看权限范围。比如只允许查看自己所属班级的数据那 SQL 就应该是WHERE class_id %s AND region %sdata_filter 的粒度越细越能防止“越权查询”。我见过不少系统功能看着都有但接口没有做行级权限过滤导致一个普通用户传入大范围的条件就能把全公司的数据拉出来。这类问题在数据层面比 SQL 注入更难发现因为 SQL 本身没有语法错误只是少了一个条件。6. 常见问题速查与排坑实录6.1 过滤条件常见错误速查表错误写法问题表现正确写法WHERE score NULL查不到记录WHERE score IS NULLWHERE class_id NOT IN (子查询有 NULL)返回空集WHERE NOT EXISTSWHERE YEAR(create_time) 2024索引失效慢查询WHERE create_time 2024-01-01 AND create_time 2025-01-01WHERE name LIKE %关键词%全表扫描数据量大时极慢尽可能改为前缀匹配或配合其他等值条件缩小范围WHERE class_id 1 OR class_id 2 AND score 80结果集不符合预期WHERE (class_id 1 OR class_id 2) AND score 80WHERE s 60s 是 SELECT 别名报错找不到列WHERE score 60 或嵌套子查询SELECT DISTINCT col1, col2组合去重单列不重复明确需求按需选择列这张表是我做内部分享时一直保留的每次讲数据过滤总有人能从里面找到自己刚踩过的坑。6.2 我踩过的三个真实案例第一个案例是成绩汇总时大量 NULL 导致总分为空。当时导出的成绩表里缺考学生的成绩是 NULL不是 0。我用最简单的方式去算总分结果缺考学生的总分为空整个排名表错乱。后来我在汇总前先做了 COALESCE(score, 0)才算对。第二个案例是 NOT IN 的 NULL 陷阱。查询某个班级的补考名单排除掉一些特殊状态的数据子查询返回值里有一个 NULL整个查询结果为空我一度以为是数据被误删了排查了两个小时才发现是 NULL 的问题。从那以后我写 NOT IN 之前都会下意识检查子查询结果是否存在 NULL。第三个案例是 LIKE 模糊搜索导致报表超时。当时数据量 500 万搜索姓名的 %XX% 写法约 6 秒才能出结果前端一直转圈。后来把搜索需求改成了前缀匹配响应时间降到百毫秒级别同时把搜索词做了长度和白名单限制。有时候不是你 SQL 写得不好而是业务方的需求本来就可以调整优化。6.3 数据过滤的调试方法调试过滤条件时我有几个固定的操作习惯先把 WHERE 条件摘出来单独跑一遍看返回行数是否符合预期。比如疑似多查了数据先确认条件数量再分析哪个条件放得太宽。用 EXPLAIN 看执行计划和预估行数确认过滤条件有没有命中索引。这是排查“慢”的唯一可靠手段不要凭感觉猜。对复杂条件从内到外拆分验证。比如多层子查询先查最内层的结果集再逐层往外查每查一层都停下来看数据是否符合预期。这个习惯能帮你快速定位是子查询写错了、还是内外层关联条件没带全。我自己在处理数据任务时还会用临时表分步处理先 SELECT 到临时表再对临时表做过滤和二次加工。这种方式虽然多写几步但每一步的结果都看得见出了错容易追踪。等逻辑完全验证正确后再考虑合并成一条复杂 SQL 去做性能优化。收尾一个小技巧最后分享一个我常用的实用习惯复杂的过滤条件建议尽量写成“范围条件”的形式不要把逻辑全部堆在 OR 里面。例如筛选指定班级、指定科目、指定分数段优先写成多个 AND 条件拼接而不是每个值都用 OR 穷举。这样优化器更容易命中组合索引SQL 可读性也更强。数据过滤这个主题看起来是最基础的 SQL 功能但真正写好的关键在于对数据本身的敏感度——你知道哪些列可能为 NULL哪些字段存在特殊字符哪些条件能走索引。多练多想多记录排坑经验这些积累会慢慢变成你的肌肉记忆。