带新人的时候我经常发现一个现象很多人编程语言学得很溜一到数据库面前就露怯。写个分组报表要找半天资料一条慢查询能把线上数据库拖到报警更别提那些生产环境执行DELETE忘带WHERE条件的“事故现场”。SQL这门语言表面上看就几个关键字但真要用好靠的是理解“数据是以集合形式存在的”这件事而不是闷头背语法。这篇《SQL必知必会上》我会从底层逻辑讲起把查询、过滤、分组、连接、增删改、事务、索引、安全这些核心知识点串成一条线。不吹玄学不堆术语每一节都配实际可跑的例子。适合刚入门想系统打基础的同学也适合干了几年但SQL一直靠百度的开发、测试、运维和数据分析朋友帮你把那些“好像知道但又说不清”的细节一次补齐。1. SQL到底是什么一门“描述结果”的语言1.1 声明式编程你只负责说需求很多刚接触SQL的人会下意识地用写程序的方式去理解它比如“我是不是要写个循环去遍历每一行”这就是最大的认知误区。SQL是声明式语言你只需要描述“我想要什么样的数据”至于数据库怎么去取、走哪个索引、怎么排序那是优化器的工作。打个比方你去餐厅点菜只需要说“来一份宫保鸡丁”。你不需要告诉厨师先切鸡丁还是先炸花生米更不需要指挥他开多大火。SQL就是你对数据库这个“大厨”下的订单。相比之下Java、Python这类命令式语言更像是你自己下厨每一步都得亲力亲为先切菜再炒菜最后装盘。这个认知上的转变特别重要。我见过不少写了几年SQL的人还在用编程思维写查询先查一张表然后写一段代码循环去查另一张表一个个拼接结果。这种写法在数据量小的时候没感觉一旦上了十万行、百万行性能会差得让人怀疑人生。正确的做法是让数据库一次性帮你把结果算出来而不是取回来自己处理。1.2 谁在天天用SQL远不止程序员SQL的适用范围比很多人想象中要大得多。后端开发写接口要查库数据分析师做报表要提数运维排查线上问题要查日志表测试做数据准备得造数据产品经理偶尔也要自己拉数据看看功能效果。可以说只要你的工作和数据沾边SQL就绕不开。尤其这两年各种数据库层出不穷MySQL、PostgreSQL、SQL Server、达梦、OceanBase名字换来换去但核心的SQL语法九成是通用的。你今天学会了标准的SQL写法换任何一个数据库都能快速上手。反过来如果你只会某个数据库的方言换一个环境就抓瞎那反而说明基础没打牢。所以我不建议一上来就死磕某个数据库的安装和配置而是先把标准SQL练扎实。客户端工具、连接池、运维参数这些东西等到真正需要的时候再学一点都不晚。语言本身的思维才是根。1.3 学习路线先会查再会改最后聊优化SQL的内容看着多但主线很清楚。第一优先级是查询也就是SELECT语句日常工作中80%的SQL都是查询。第二优先级是增删改INSERT、UPDATE、DELETE这些操作因为有风险所以更要理解事务和锁。第三优先级才是索引、视图、存储过程、性能优化这些进阶内容。我建议的学习路径是先建立对“集合”的感觉学会用SELECT从单张表里取数然后学会多表连接理解表和表之间的关系接着练分组聚合这是做报表的基础最后再研究索引和优化。这个顺序也是我下面正文的排列顺序跟着走就行。2. 查询基本功SELECT的六段式骨架2.1 先搭骨架FROM和WHERE是地基所有查询都离不开一个基本逻辑从哪张表取过滤哪些行显示哪些列。对应到SQL就是SELECT 列名 FROM 表名 WHERE 过滤条件;比如有一张订单表orders包含订单号、用户ID、金额、下单时间。想查所有金额大于100元的订单就写SELECT order_id, user_id, amount FROM orders WHERE amount 100;这里要强调一个执行顺序的问题虽然SELECT写在最前面但数据库实际上是先执行FROM确定数据来源再执行WHERE把符合条件的行筛出来最后才轮到SELECT决定要显示哪些列。理解这个顺序很重要因为它决定了你写子查询、别名、聚合函数时候的思路。很多人搞不清“为什么WHERE里不能用SELECT里定义的别名”就是因为没想明白执行顺序。2.2 WHERE的常见写法别踩字符串的坑WHERE子句看起来简单写起来全是细节。比较运算是最基本的大于小于等于不等于SQL里不等于有两种写法和!两种都行看团队规范。范围用BETWEEN AND注意它是包含边界的。集合用IN比如查订单状态在某个集合里的记录SELECT order_id FROM orders WHERE status IN (PAID, SHIPPED);字符串匹配用LIKE百分号表示任意多个字符下划线表示任意一个字符。比如查所有姓张的用户SELECT user_name FROM users WHERE user_name LIKE 张%;不过我要提醒一句LIKE以百分号开头的时候比如LIKE %张索引基本就废了数据量大时会全表扫描这是慢查询的重灾区之一。字符串还有一个容易踩的坑数据库里字符串和日期的比较必须加引号。很多人习惯写WHERE create_time 2024-01-01不加引号时数据库会把它当成数字间的减法算出一个数字再和日期比较轻则结果不对重则直接报错。正确写法是WHERE create_time 2024-01-01。2.3 分组聚合报表的命根子做数据分析离不开“按某个维度汇总”的需求按天统计订单量、按用户统计消费总额、按商品类目统计库存。这些场景都要用到GROUP BY。SELECT user_id, SUM(amount) AS total_amount FROM orders WHERE status PAID GROUP BY user_id;这条SQL的意思是把已支付的订单按用户分组然后对每个分组求和得到该用户的消费总额。这里有几个要点。第一SELECT后面出现的列要么是分组列本身要么是聚合函数的结果。如果你SELECT了某个没被分组的列很多数据库会直接报错MySQL旧版本虽然不报错但结果是随机取的这是很多“诡异结果”的根源。第二聚合函数常用的有COUNT、SUM、AVG、MAX、MIN。COUNT统计行数注意COUNT(*)和COUNT(列名)有区别前者统计所有行后者只统计该列不为空的个数。第三如果要对分组后的结果再做过滤不能再用WHERE得用HAVING。因为WHERE是在分组之前执行的而HAVING是在分组之后执行的。比如想找消费总额超过1000元的用户SELECT user_id, SUM(amount) AS total_amount FROM orders WHERE status PAID GROUP BY user_id HAVING SUM(amount) 1000;理解WHERE和HAVING的执行顺序差异就能理解为什么二者不能换用WHERE先筛数据行GROUP BY再分组HAVING最后筛分组。顺序错了逻辑就错了。2.4 排序与分页LIMIT配合ORDER BY才有意义ORDER BY用来排序DESC是降序ASC是升序默认升序。可以指定多个排序字段先按第一个排相同再按第二个排。比如先按金额降序金额相同按下单时间升序SELECT order_id, amount, create_time FROM orders ORDER BY amount DESC, create_time ASC;分页则是LIMIT和OFFSET的活不同数据库语法略有不同。MySQL里写LIMIT 10 OFFSET 20意思是跳过前20行取接下来的10行。这在做列表页的时候是标配。不过分页有个很现实的性能问题深分页。当OFFSET很大的时候比如LIMIT 10 OFFSET 100000数据库还是要先数出前面十万行再丢掉代价非常高。常见优化方法是记住上一页最后一条记录的ID用WHERE id 上一页最大id ORDER BY id LIMIT 10这种方式实现“键集分页”性能会好很多。2.5 去重和空值两个高频面试点去重用DISTINCT比如查所有出现过下单的用户IDSELECT DISTINCT user_id FROM orders;DISTINCT是去重整个结果行不是只去重一列。比如SELECT DISTINCT user_id, status意思是user_id和status组合起来不重复而不是只看user_id。空值处理是SQL里最容易出bug的地方。NULL不等于空字符串也不等于0。判断一个字段是否为空必须用IS NULL或者IS NOT NULL不能写 NULL。因为NULL参与任何比较运算结果都是“未知”用 NULL永远匹配不到任何数据。还有一点聚合函数会自动忽略NULL。比如AVG(score)只对非NULL的分数求平均如果一批数据里有几个NULL平均值会被“抬高”。这个问题在做统计报表的时候特别隐蔽我在工作里见过不止一次因为NULL被忽略导致平均值对不上账的情况。解决办法是先用COALESCE函数把NULL替换成默认值再参与计算。3. 多表查询JOIN与子查询怎么选3.1 四种JOIN拿两个圆圈来类比业务数据很少只存在一张表里。用户信息在users表订单在orders表订单明细在order_items表。要查“每个订单对应的用户姓名”就得把表和表连接起来。JOIN的核心逻辑可以用两个集合的交集来理解。假设左表A是用户右表B是订单连接条件是A.user_id B.user_id。INNER JOIN只取两边的交集也就是有用户也有订单的记录。LEFT JOIN保留左表的全部行右表匹配不上的补NULL。RIGHT JOIN保留右表的全部行左表匹配不上的补NULL。FULL OUTER JOIN两边的全保留MySQL不直接支持但可以用LEFT JOIN UNION RIGHT JOIN模拟。实际工作中LEFT JOIN用得最多因为它能保证“主要的那张表一行不漏”。比如你想统计每个用户有没有下单以用户表为主表做LEFT JOIN没下过单的用户订单字段就是NULL再配合IS NULL就能筛出“未下单用户”。3.2 JOIN实战从三张表拼出报表我举个例子一次完整的报表取数过程。需求是查每个用户的用户名、下单次数、订单总金额只看已支付的订单。涉及三张表users用户表、orders订单表、order_items订单明细表。如果我只用orders表能拿到user_id和amount但拿不到用户名如果再把order_items连进来一个订单有多条明细会导致订单金额被重复累加。这是个经典陷阱。正确做法是先按订单维度聚合成一张临时结果再和用户表连接。用子查询先算出每个用户的订单数、总金额再LEFT JOIN用户表SELECT u.user_name, t.order_count, t.total_amount FROM users u LEFT JOIN ( SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM orders WHERE status PAID GROUP BY user_id ) t ON u.user_id t.user_id;这里我先把订单表按用户分组聚合再和用户表连接避免了“一对多”导致的金额翻倍问题。这种“先聚合再连接”的思路是写多表查询时最需要养成的习惯之一。3.3 子查询和EXISTS什么时候更好用子查询就是嵌套在另一个查询里的查询可以放在SELECT、FROM、WHERE里。WHERE里用子查询有个典型场景查“哪些用户的订单金额超过了所有用户的平均值”。SELECT user_id FROM orders GROUP BY user_id HAVING SUM(amount) ( SELECT AVG(amount) FROM orders );EXISTS则是判断“是否存在”的用法它在某些场景下比IN更高效。比如查“有下单记录的用户”用IN写是SELECT user_id, user_name FROM users WHERE user_id IN (SELECT user_id FROM orders);用EXISTS写是SELECT u.user_id, u.user_name FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.user_id );EXISTS和IN的区别在于处理逻辑不同IN是先把子查询结果集算出来再和外层逐条比较EXISTS是外层每取一行就拿到子查询里检查一次是否存在。理论上当子查询的结果集很小而外层表很大时IN更有优势当子查询的结果集很大而外层表相对小时EXISTS往往更快。实际使用中我建议先关注执行计划而不是凭记忆推断因为不同版本、不同数据的表现可能完全不一样。3.4 JOIN还是子查询一张表帮你决策很多初学者纠结这个问题我就直接给结论。场景推荐方案原因需要展示右表字段JOIN子查询在SELECT里只能返回单值先聚合再关联子查询避免一对多导致数据膨胀判断存在性EXISTS找到一条就停逻辑清晰数据量小都不差优化器会帮你选可读性优先子查询一层层包装符合阅读习惯我的个人习惯是能用JOIN表达清晰的用JOIN需要先做聚合的用子查询判断存在性的用EXISTS。别神化任何一种写法把SQL写清楚、让别人一眼能看懂比追求“高深技巧”重要得多。4. 增删改DML操作的保命指南4.1 INSERT的多种写法查询是基本功增删改是日常工作里风险最高的操作尤其是UPDATE和DELETE一个不小心就是生产事故。INSERT最基础的写法是指定列名建议养成好习惯永远显式写出列名INSERT INTO users (user_name, email, created_at) VALUES (张三, zhangsanexample.com, NOW());不加列名的简写看起来很省事但如果表结构变了插入的数据和字段对不上排查起来特别痛苦。多写几个列名成本很低收益很高。批量插入用一条INSERT带多个VALUES性能远好于循环单条插入INSERT INTO users (user_name, email, created_at) VALUES (张三, zhangsanexample.com, NOW()), (李四, lisiexample.com, NOW());还有一种场景是把一张表的数据挪到另一张表用INSERT INTO ... SELECT这在做临时表、归档表的时候特别常用。注意它的列类型要能对上名称可以不一样。4.2 UPDATE和DELETE先查再看再执行我见过最惊险的一次事故同事要更新某一条配置写了个UPDATE语句WHERE条件写错了结果六百多万行数据全部被改成了一个值。辛亏有备份但还是折腾了大半天。所以UPDATE和DELETE的第一条铁律是先 SELECT 再 UPDATE。实际操作中我每次写这类语句都先把WHERE条件放到SELECT里跑一遍确认影响行数和预期一致再改成UPDATE去执行。虽然多了一步但能拦住九成的事故。-- 第一步确认要影响哪些行 SELECT * FROM orders WHERE user_id 123 AND status PAID; -- 第二步确认无误后执行更新 UPDATE orders SET status REFUNDED WHERE user_id 123 AND status PAID;DELETE同理尤其要注意不带WHERE的DELETE那是清空整张表的操作。如果想清空整张表用TRUNCATE而不是DELETETRUNCATE不写日志、速度更快但它不能按条件筛也不能触发一些基于行删除的逻辑所以使用前更要慎重。4.3 事务让操作要么全成要么全败很多业务操作是需要好几个步骤的比如下单要扣库存、生成订单、记录流水。如果中间某一步失败了前面的操作就得回滚这就要靠事务。事务最核心的特性是ACID原子性、一致性、隔离性、持久性。日常使用中最直观的感受就是BEGIN和COMMIT/ROLLBACK之间的区域是一个整体。以MySQL为例START TRANSACTION; UPDATE inventory SET stock stock - 1 WHERE product_id 100; INSERT INTO orders (order_id, product_id, user_id) VALUES (5001, 100, 88); COMMIT;如果中间任何一步报错执行ROLLBACK就可以把数据状态恢复成事务开始之前的样子。不过要注意事务内的锁要等到提交或回滚才会释放所以事务里不要做耗时太长的操作比如调用外部接口、写文件、等用户输入这些都应该放在事务外面。长事务会导致行锁一直不释放很容易拖垮并发性能。5. 进阶必知视图、索引与SQL优化5.1 视图给查询起个名字视图本质就是一段命名的查询。你把它当成一张虚拟表来用它并不真正存储数据每次查询视图时数据库会去执行底层的SELECT语句。视图常见的用途有两个。一是简化复杂查询把一段多表连接的复杂SQL封装成视图业务方直接SELECT * FROM 视图名就能用省得每个人都要写一遍那么长的JOIN。二是做权限隔离比如只开放部分列的视图给低权限用户底层表结构不暴露。但视图不是银弹。嵌套层数过深的视图性能可能很差因为每一层都可能做一次结果集物化。遇到视图性能问题我的建议是把它当成一个“查询模板”必要时直接展开成底层SQL去看执行计划找到真正的瓶颈。5.2 索引为什么加了索引还是慢索引就像书的目录能帮你快速定位到数据所在的位置。但很多人对索引的理解停留在“加索引会变快”遇到慢SQL第一反应就是乱加索引结果反而更糟。首先索引不是越多越好。每个索引在写数据的时候都要维护索引多了写入性能会下降磁盘空间也会涨。加索引前要问自己这个查询是不是高频过滤条件是不是选择性高的列一张表有几个索引就够用了不需要每个字段都建。其次加索引不等于一定走索引。常见导致索引失效的情况对索引列使用函数比如WHERE YEAR(create_time) 2024使用前导通配符的LIKE比如LIKE %abc列类型不匹配导致隐式转换OR连接的条件里有一个非索引列。这些情况我基本都在实际排查中遇到过尤其是日期函数过滤几乎每个月都能在慢SQL里看到。判断SQL有没有走索引靠的是执行计划。MySQL里在SQL前面加EXPLAIN关键字就能看到访问类型、扫描行数、是否使用索引等关键信息。我排查慢SQL的基本流程就是先看是不是全表扫描再看扫描了多少行最后确认有没有可用的索引被漏掉。5.3 慢SQL排查的基本流程线上遇到数据库性能问题不要上来就改代码先定位到具体的SQL。很多公司有慢查询日志那些超过阈值的SQL会被记录下来。找到慢SQL之后按这个流程排查第一步看是不是数据量涨了。同样的SQL以前一千行没事现在一千万行全表扫描自然会慢。第二步看执行计划确认是否走了索引。第三步看有没有大表和小表做关联关联的字段有没有索引。第四步看是否可以用改写SQL的方式优化比如把子查询改成JOIN或者把OR改成UNION。还有一类慢SQL是“看起来不慢但执行频率超高”单个查询几十毫秒但一秒钟被调用几百次数据库压力自然就起来了。这种场景光优化SQL不够还得考虑加缓存、限流、或者改异步。优化SQL是术优化整体架构才是道。另外多说一句很多同学问“JVM或者Spring Boot能不能设置一个SQL执行10秒自动关闭”。这里要分清楚应用层可以设置连接超时和Statement超时比如MyBatis的defaultStatementTimeout这类似一个“保险丝”防止一条SQL把连接池占死。但超时设置的思路是兜底不是优化手段。一条SQL真的要跑10秒你把它掐断了业务照样失败真正的解法还是把SQL优化到毫秒级。6. 绕不开的安全底线SQL注入与防护6.1 什么是SQL注入一次拼接引发的灾难SQL注入是Web安全里最经典、也最致命的漏洞之一。它的本质是把用户输入的内容直接拼接进了SQL语句导致用户输入的内容被当成SQL代码执行了。举个最典型的例子登录功能如果写成字符串拼接String sql SELECT * FROM users WHERE user_name username AND password password ;假如用户在用户名输入框里填的是admin --那么拼出来的SQL就变成了SELECT * FROM users WHERE user_name admin -- AND password ;注释符--把后面的密码判断直接注释掉了等于不用密码就能以admin身份登录。这就是所谓的“万能密码绕过”的思路本质不是密码有什么万能值而是拼接SQL让条件判断失去了意义。我在搜索引擎热词里看到“SQL注入万能密码绕过”这里特别说明一下这不是什么神奇的技巧而是因为程序没有做参数化查询。真正的防线不是去研究各种注入字符串而是彻底杜绝拼接。6.2 防御三板斧参数化、白名单、最小权限第一板斧是参数化查询这是最根本的防御。用占位符代替用户输入直接拼接进SQLPreparedStatement ps conn.prepareStatement( SELECT * FROM users WHERE user_name ? AND password ? ); ps.setString(1, username); ps.setString(2, password);数据库会把SQL结构和参数分开处理用户输入的内容永远只是“值”不会被当成SQL结构的一部分。原理上参数化等于把“代码和数据”做了隔离就像你和陌生人说话时他说的每句话都被当成普通内容而不是能指挥你的命令。第二板斧是白名单校验。对于排序字段、表名这类无法用占位符的位置靠代码校验允许的取值列表。比如排序字段只能从固定的几个值里选用户传入其它值直接拒绝。第三板斧是最小权限。给数据库账号分配权限时只给够用的权限。只读账号就别给UPDATE和DELETE的权限应用账号尽量不持有DROP和TRUNCATE权限。这样即使被注入破坏范围也被限制了。6.3 框架里的SQLMyBatis动态SQL别乱拼很多人觉得用了MyBatis就安全了其实不是。MyBatis的${}和#{}是两种完全不同的处理方式。#{}是预编译参数占位安全。${}是直接字符串替换等于拼接有注入风险。我见过不少开发图省事把表名、排序字段用${}拼进去一旦这些内容里有用户可控制的部分又回到了注入的老路上。!-- 安全写法用 #{} 传参数 -- select idqueryUser resultTypeUser SELECT * FROM users WHERE user_name #{username} /select !-- 危险写法用 ${} 拼参数务必避免 -- select idqueryUserBad resultTypeUser SELECT * FROM users WHERE user_name ${username} /select有些连接池中间件自带SQL防注入能力比如阿里的Druid就有WallFilter可以拦截危险SQL报错信息里会出现“sql injection violation”这类字样。这个功能确实能挡掉一部分注入但它属于事后防御真正的底线还是代码层面用参数化查询。7. 写在最后SQL高手的思维习惯做了这么多年数据相关的工作我越来越觉得SQL的技术细节其实有限真正的差距在思维习惯上。第一个习惯是“先想集合再想循环”。SQL处理的是集合一个SELECT就是一次集合操作。当你想在一个查询里逐行判断、逐行处理的时候先停下来问问自己能不能用一条集合操作搞定大部分时候答案是可以。第二个习惯是“先看执行计划再猜性能问题”。不要凭感觉说“这个SQL慢是因为没加索引”加索引之前先EXPLAIN一下看看扫描行数和访问类型让数据说话。第三个习惯是“写SQL之前先画表结构”。哪怕是在脑子里面画。先想清楚我要查的数据分布在哪些表里它们的关联关系是什么过滤条件能筛掉多少比例的数据。这个思考过程只要花一两分钟却能省下后面一个小时的排查时间。老实说SQL这门语言看起来简单想写得好需要日积月累的错误和复盘。你在用的时候会发现那些看似不起眼的规则——宁写长别写错、先查再改、尽量参数化——其实是无数生产事故换来的经验。把这篇《SQL必知必会上》里的内容吃透日常的开发、取数、排障需求基本都能拿下。接下来有时间我再把窗口函数、复杂统计、性能调优这些进阶话题单独整理成篇继续聊。