SQL实战指南：从基础查询到性能优化的关键技巧

前两天有个朋友跟我诉苦说SQL语法书翻了两遍SELECT、WHERE、JOIN这些关键字背得滚瓜烂熟可一坐到工位前面对公司的业务库整个人还是懵的——不知道该从哪里下手。他问我为什么我说最难受的不是“不会写”而是“不知道该怎么想到这个写法”。这话我特别有感触。SQL这门语言看起来简单教程里每一章讲一个语法点每个点都有标准答案但真实工作里没人按章节给你出题需求永远是混在一起的而且一上来就是十几张表、几百万行数据。所以这篇东西我不想写成语法手册我想从一个实际干活的视角把平时最常用的SQL语法按“真实会用到的逻辑”串一遍。哪些写法看着一样、实际天差地别哪些坑我是真踩过的该怎么一步步排查一次讲清楚。文章里我会以SQL Server为主因为从热搜词来看问SQL Server相关环境问题的人特别多但绝大多数语法在MySQL、PostgreSQL里同样成立有差异的地方我会单独标出来。无论是刚入门的学生还是被临时拉去写报表的开发这篇文章的目标就一个让你看完之后拿到真实需求时知道第一行该怎么写。1. 基础查询语法SELECT的每个部分都不是摆设1.1 先搞清楚执行顺序才能理解为什么报错写SQL的时候人的直觉是按书写顺序思考先SELECT选列再FROM找表再WHERE过滤。但数据库引擎执行的时候完全是另一套顺序理解这个顺序很多莫名其妙的报错就能想通了。实际执行顺序大致是FROM确定数据来自哪张表多表时先做关联WHERE对原始行做过滤GROUP BY按指定列分组HAVING对分组后的结果做过滤SELECT投影出最终需要的列ORDER BY对结果排序LIMIT/TOP截取前N行所以你会遇到一个经典报错在WHERE里引用SELECT别名比如SELECT name AS n FROM users WHERE n 张三数据库直接告诉你“列名无效”。原因很简单WHERE执行时SELECT还没开始干活别名n根本不存在。换成HAVING就可以因为HAVING在SELECT之前执行的说法其实不严谨更准确地说是HAVING依赖的是分组结果但不同数据库对此的处理有差异。保险的做法是能放WHERE的条件不放HAVING能不用别名的场合就别依赖别名。1.2 WHERE条件里的高频写法IN、BETWEEN、LIKE、IS NULLWHERE是查询里最常用、也最容易写错的地方。几个高频写法平时看着都懂实际代码里翻车率极高。IN可以替代一串OR比如WHERE city IN (北京, 上海, 广州)比写三个OR清晰得多。NOT IN同理但要注意如果IN的子查询结果里包含NULLNOT IN会返回空结果集。这是一个非常隐蔽的坑原因跟NULL比较有关——NOT IN本质上是一串和AND的组合而任何值与NULL比较的结果都是“未知”最终整个条件不成立。我见过不止一次线上查询因为这个问题查出空表。BETWEEN是闭区间WHERE age BETWEEN 18 AND 30包含18和30两端。别记成开区间这个细节在查边界日期时非常容易出问题。LIKE配合%和_做模糊匹配%匹配任意长度字符串_匹配单个字符。比如LIKE 张%查所有张姓LIKE 张_只查张后面跟一个字的名字。需要转义时用ESCAPE关键字比如搜带百分号的文本LIKE %\%% ESCAPE \。空值判断必须用IS NULL或者IS NOT NULL不能写 NULL。NULL不是一个值它表示“未知”所以任何与NULL的等值比较结果都是“未知”不会返回任何行。这个写错的人太多了排查的时候先看是不是这里的问题。1.3 去重和排序DISTINCT不是对单列去重DISTINCT看起来简单但很多人理解有偏差。SELECT DISTINCT category, status不是只对category去重而是对category status的组合去重。也就是说只要两行里这两个字段的值都一样才会被合并成一行。想单独看某个字段有哪些取值直接SELECT DISTINCT category就够了。ORDER BY支持多字段排序ORDER BY create_time DESC, id ASC意思是先按创建时间倒序时间相同再按id正序。注意DESC只作用于它紧挨着的那一列后面那列如果想倒序必须再写一次这个细节很多人想当然写错。取前N条记录不同数据库写法差异很大这里做个对比数据库写法说明SQL ServerSELECT TOP 100 * FROM ordersTOP写在SELECT后面MySQL / PostgreSQLSELECT * FROM orders LIMIT 100LIMIT写在最后Oracle 12cSELECT * FROM orders FETCH FIRST 100 ROWS ONLY老版本用ROWNUMDB2SELECT * FROM orders FETCH FIRST 100 ROWS ONLY与Oracle类似在SQL Server里SELECT TOP 100 * FROM orders ORDER BY create_time DESC会先排序再取前100但如果你只写TOP 100不写ORDER BY数据库返回的是“任意100行”不保证是哪个顺序。这个特性经常被忽略实际业务里“取最新的100条记录”这种需求ORDER BY是必不可少的。2. 多表关联JOIN的写法谁都会踩坑的总在细节里2.1 INNER、LEFT、RIGHT怎么选一切取决于主表多表关联的语法本身不难难的是搞清楚该用哪种JOIN。INNER JOIN取两表交集只有两边都能匹配上的行才会出现在结果里LEFT JOIN保留左表全部行右表匹配不上就补NULLRIGHT JOIN反过来保留右表全部行FULL OUTER JOIN两边都保留MySQL原生不支持但可以用UNION模拟。实际开发里90%的业务场景都能表述成“以某张表为主补充其他表的字段”。这种需求直接用LEFT JOIN。比如查订单列表要带出用户姓名和商品名称订单表是主表用户和商品是补充信息写成FROM orders o LEFT JOIN users u ON o.user_id u.id LEFT JOIN products p ON o.product_id p.id一次搞定。INNER JOIN适合只关心两边都有的数据比如查“已支付订单对应的用户”如果订单没匹配上用户比如用户被删了这条订单就不该出现在结果里用INNER JOIN更合适。用LEFT JOIN反而会带出一堆用户为NULL的脏数据还得额外加WHERE过滤。2.2 ON里的条件和WHERE里的条件结果可能完全不同这是多表关联里最经典、也最容易踩的坑之一。看下面两条SQL-- 写法一过滤条件放在ON里 SELECT * FROM orders o LEFT JOIN users u ON o.user_id u.id AND u.status active -- 写法二过滤条件放在WHERE里 SELECT * FROM orders o LEFT JOIN users u ON o.user_id u.id WHERE u.status active两条SQL的结果大概率不一样。写法一是先限定右表users只取status为active的记录再与左表做左连接左表的订单记录全部保留用户不活跃的时候用户字段显示NULL。写法二是先做完整的左连接再用WHERE过滤右表字段此时user_id匹配失败的行u.status为NULL会被过滤掉实际上等于把LEFT JOIN变成了INNER JOIN。我遇到过线上统计报表数据对不上的情况最后定位到就是这里有人想把“只统计活跃用户”这个条件加进去图方便直接丢进WHERE结果未匹配的订单全被吞了数据少了10%。排查方法很简单——删掉WHERE里的副表条件看行数是否变化。如果行数明显变多说明过滤条件放错了位置。记住一个口诀想让主表数据“完整保留”对副表的过滤条件放ON想最终只返回符合条件的结果放WHERE但这时候你应该考虑用INNER JOIN。2.3 实战排查LEFT JOIN之后行数翻倍问题出在哪LEFT JOIN后结果行数突然翻倍是让很多人头疼的问题。这里分享一个完整的排查链路。假设我有订单表orders和订单明细表order_items一条订单可能包含多个商品所以order_items里同一个order_id会有多行。执行下面的关联SELECT o.id, o.order_no, i.product_name, i.quantity FROM orders o LEFT JOIN order_items i ON o.id i.order_id如果订单表有1000行明细表有1500行结果出来2500行甚至更多第一反应不是怀疑SQL写错了而是检查order_items里是否有重复的数据。分钟级别就能排查完单独查主表行数SELECT COUNT(*) FROM orders确认基准行数。查关联键在副表里是否唯一SELECT COUNT(*), COUNT(DISTINCT order_id) FROM order_items如果两个数不相等说明副表存在一对多关系。找出重复的具体数据SELECT order_id, COUNT(*) FROM order_items GROUP BY order_id HAVING COUNT(*) 1看哪些订单被撑大了。判断业务上是否合理。如果一条订单本来就该有多个明细那行数翻倍是正常的一对多展开如果order_id本应唯一却出现了多条那就是副表脏数据需要清洗。这个排查链路适用于绝大多数“关联结果行数异常”的场景。另外关联字段的类型也要注意如果一边是int一边是varchar数据库通常会自动做隐式转换但一旦转换导致索引失效查询性能就会明显下降这个我们放到后面优化部分细说。3. 聚合与分组GROUP BY真正难住人的是逻辑不是语法3.1 聚合函数对NULL的处理直接影响统计口径常用聚合函数就五个COUNT、SUM、AVG、MAX、MIN。语法上谁都写过但它们对NULL的处理方式如果不清楚统计结果口径就歪了。COUNT(*)统计行数不管这一行是不是全NULL都算。COUNT(列名)只统计该列非NULL的行数。举个例子一张表有100行email列有20行为NULLCOUNT(*)返回100COUNT(email)返回80。很多报表里“用户数”对不上就是这里有人写错了。SUM和AVG都会忽略NULL。AVG不是把NULL当0处理而是直接跳过。三行数据分别是1、2、NULLAVG的结果是1.5不是1。如果你业务上想把NULL当0参与平均需要自己先COALESCE(列, 0)再算。MAX和MIN同样忽略NULL——这里有个很有意思的点MIN遇到全是NULL的列返回NULL而不是0。聚合函数遇到NULL的行为易错点COUNT(*)计入与COUNT(列)混淆COUNT(列)不计入统计口径偏小SUM忽略不会因NULL报错AVG忽略容易误以为NULL按0算MAX / MIN忽略全NULL时返回NULL3.2 HAVING和WHERE的执行时机过滤顺序决定结果WHERE在分组之前过滤原始行HAVING在分组之后过滤分组结果。这个区别语法上很好背但实际写的时候经常用错。举个例子订单表orders包含order_date、category、amount三个字段。需求是“统计2024年每个商品分类的销售额只保留销售额超过1万元的分类”。年份条件是针对原始行的必须放WHEREWHERE order_date 2024-01-01 AND order_date 2025-01-01这一步先把2024年之外的订单甩掉。销售额超过1万是针对分组后的结果的必须放HAVINGHAVING SUM(amount) 10000。如果把金额条件放WHERE里WHERE SUM(amount) 10000直接报错——WHERE执行时还没有聚合结果SUM根本不可用。如果把年份条件放HAVING里语法能跑但2023年的订单也会被拉进来一起分组再过滤不仅结果错效率还低。所以这个知识点不止是语法问题还关系到查询效率和正确性。3.3 一个完整案例统计每个分类的销售额假设订单明细表order_items结构如下列名类型说明idint主键categoryvarchar商品分类amountdecimal成交金额order_datedatetime下单时间需求统计2024年1月到6月每个分类的订单总数、成交总金额、平均每单金额按成交总金额倒序。SELECT category, COUNT(*) AS order_count, SUM(amount) AS total_amount, AVG(amount) AS avg_amount FROM order_items WHERE order_date 2024-01-01 AND order_date 2024-07-01 GROUP BY category ORDER BY total_amount DESC写这条SQL时注意GROUP BY的列是categorySELECT里非聚合列也只能是category。有人会想我再把某个具体的商品名称select出来行不行不行MySQL的ONLY_FULL_GROUP_BY模式下直接报错SQL Server也会告诉你column invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause。道理很简单——分组之后一个组里可能有多个不同的商品名称数据库不知道该返回哪一个。如果想在结果里带出更多明细字段要么把它们加进GROUP BY要么用窗口函数要么先分组算好再关联回明细表。后面窗口函数部分会讲更优雅的解法。4. 窗口函数处理“既要明细又要聚合”的标准答案4.1 窗口函数和聚合函数的本质区别窗口函数是SQL语言里被低估的一块但它解决的是聚合函数解决不了的典型问题保留明细行的同时在每行旁边展示一个聚合值或排名值。聚合函数的特征是多行输入、一行输出GROUP BY之后行数被压缩窗口函数的特征是多行输入、多行输出每一行都会保留聚合结果作为一个新列附加回来。理解了这个区别你就知道什么时候该用窗口函数了——比如“每个用户最近的订单”“每个分类销售排前三的商品”“按时间累计的销售额”这些需求用GROUP BY做起来极其别扭用窗口函数就是一两行的事。窗口函数的基本结构是函数名 OVER (PARTITION BY 分组列 ORDER BY 排序列)。PARTITION BY负责分组相当于GROUP BY的作用但不会压缩行数ORDER BY负责组内排序。4.2 ROW_NUMBER、RANK、DENSE_RANK三个排名的差异排名类窗口函数有三个长得像结果差别很大。假设一组数据按分数排序三个人分别是95、95、90ROW_NUMBER()不看并列直接给行号结果是1、2、3RANK()并列占用名次下一名跳号结果是1、1、3DENSE_RANK()并列不跳号结果是1、1、2实际场景里取“每个用户最近一笔订单”“每个分类销量最高的商品”用ROW_NUMBER()最合适因为它能保证每条记录行号唯一。外面套一层查询过滤行号等于1即可。SELECT order_id, user_id, order_date FROM ( SELECT order_id, user_id, order_date, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY order_date DESC) AS rn FROM orders ) t WHERE rn 1这个写法在“分组Top N”里是标准答案。注意子查询里用窗口函数算出行号外层过滤rn 1不能用WHERE直接过滤窗口函数结果因为WHERE的执行顺序在窗口函数之前直接写WHERE ROW_NUMBER() OVER(...) 1会报错。4.3 PARTITION BY ORDER BY组合更多玩法窗口函数不止能排名还能做累计值。比如每个用户的累计消费金额SQL可以这样写SELECT user_id, order_date, amount, SUM(amount) OVER(PARTITION BY user_id ORDER BY order_date) AS cumulative_amount FROM orders这里SUM(amount) OVER(...)默认从分组第一行累加到当前行得到一个逐行递增的累计值。去掉PARTITION BY只留ORDER BY它就会从整个结果集的第一行累加到当前行。想要更精准的窗口范围可以用ROWS BETWEEN比如ROWS BETWEEN 2 PRECEDING AND CURRENT ROW表示当前行和前两行适合做移动平均。还有LAG()和LEAD()可以取上一行或下一行的值常用于环比计算——比如算每个用户本次订单与上次订单的时间间隔就是LAG(order_date) OVER(PARTITION BY user_id ORDER BY order_date)然后做日期差。窗口函数的支持范围SQL Server从2005年开始支持MySQL从8.0开始支持PostgreSQL一直支持得不错Oracle也老早就有了。如果公司数据库是MySQL 5.7窗口函数用不了只能用子查询变量来模拟这也是为什么有些老开发不熟悉窗口函数的原因——但新项目基本都该用上。5. 插入、更新、删除语法越简单越容易在细节上翻车5.1 INSERT的三种写法各有各的适用场景往表里插入数据最基本的写法是INSERT INTO table (col1, col2) VALUES (v1, v2)。我强烈建议每次写INSERT都把列名写出来不要偷懒省略。省略列名的写法INSERT INTO table VALUES (...)依赖表结构的列顺序表结构一调整这个语句就崩了更重要的是带有列名的写法可读性高得多看一眼就能知道哪个值对应哪个字段。多行插入在SQL Server和MySQL里都能直接写INSERT INTO table (col1, col2) VALUES (v1, v2), (v3, v4), (v5, v6)。大批量插入时这条语句比逐行INSERT效率高得多因为减少了SQL解析和网络往返的消耗。从另一张表复制数据用INSERT INTO table (col1, col2) SELECT col1, col2 FROM source_table WHERE ...。这个写法在做表迁移、建临时表时非常好用。注意SELECT出来的列顺序、类型要和INSERT的列一一对应否则会发生隐式转换转不过去就直接报错。5.2 UPDATE的两个高频雷区漏写WHERE和TOP的位置UPDATE语句在国内开发里出事故率常年居高不下原因就一条忘记写WHERE。UPDATE users SET status 1执行完全表用户状态都变了连个后悔药都没有除非开着事务。我的习惯是UPDATE之前先写一条等价的SELECT确认要影响的行数。把UPDATE users SET status 1先改成SELECT COUNT(*) FROM users WHERE ...看看会有多少行命中再改回UPDATE执行。这个习惯多花十几秒但能避免大多数灾难性的误操作。第二个雷区在SQL Server里非常隐蔽UPDATE TOP (10) users SET status 1表示更新前10行但如果后面不加ORDER BYSQL Server不会保证更新的是哪10行——它是按物理顺序随便取的。想更新“创建时间最老的10个用户”必须写成UPDATE TOP (10) u SET u.status 1 FROM ( SELECT TOP 10 id FROM users ORDER BY create_time ASC ) t INNER JOIN users u ON t.id u.id先通过子查询把要更新的主键算出来再关联更新这样既能保证选出来的是哪10行也能防止误更新。MySQL的LIMIT里同样有顺序问题需要先用子查询排序好再更新。5.3 DELETE、TRUNCATE、DROP三兄弟的差异删除数据有三种手段破坏力递增别混用。DELETE是DML操作逐行删除可以带WHERE条件锁定删除范围配合事务可以回滚删除后表的自增列不会重置。TRUNCATE TABLE是DDL操作清空全表数据不带WHERE不能回滚自增列会重置。DROP TABLE更彻底把表结构一起删掉删完只剩空气。操作可带WHERE可回滚自增列删除范围DELETE可以事务内可不重置行数据TRUNCATE不可以不可重置全表数据DROP不可以不可无表表结构数据实际业务里清空一张表的历史数据但保留表结构用TRUNCATE比DELETE快得多因为它不记录每一行的删除日志。但正因为速度快且不可恢复操作前一定要确认备份。5.4 养成事务习惯先查后改出错能回头写修改类SQL时一定要养成事务习惯。SQL Server里可以这样操作BEGIN TRAN UPDATE users SET status 1 WHERE department 技术部 -- 先别提交手动检查影响了多少行 -- 如果有问题ROLLBACK TRAN -- 确认无误COMMIT TRANMySQL里用START TRANSACTION和COMMIT/ROLLBACK。这个习惯的好处在于提交之前你拥有一次反悔的机会。我先执行UPDATE然后立刻SELECT验证结果发现问题就ROLLBACK问题不大再COMMIT。不要跟我说“我执行得很小心”线上环境所有备份都不如这一个习惯靠谱。6. 写SQL的安全底线注入漏洞是怎么来的6.1 万能密码背后的原理SQL注入是Web开发里最常见的安全漏洞原理一句话就能说清把用户的输入直接拼进SQL字符串用户就能通过精心构造的输入改变SQL逻辑。经典的万能密码长这样。假设登录SQL是SELECT * FROM users WHERE username admin AND password 123456如果程序直接拼接用户输入用户在密码框里输入 OR 11拼接后变成SELECT * FROM users WHERE username admin AND password OR 11条件11恒为真整条WHERE条件被绕过用户不需要知道密码就能登录。如果再输入 OR 11 --后面的内容被注释符吃掉整个判断完全失效。这就是“万能密码”的真实原理。这类漏洞的价值在于它展示了一个非常重要的原则——用户输入永远不能被当作SQL代码来执行。你永远不知道用户会输入什么字符串所以要永远假设它是恶意数据。6.2 为什么参数化查询是底线防注入最根本的解法不是过滤特殊字符而是使用参数化查询。参数化查询的核心思想是把SQL结构和数据分离开——SQL模板先编译好用户输入只能作为参数值传递数据库会把参数当作“数据”而非“代码”处理。Java里用PreparedStatementC#里用SqlParameterPython的数据库驱动用?占位符操作上都差不多cursor.execute( SELECT * FROM users WHERE username ? AND password ?, (username, password) )不管用户输入什么传到数据库里都只是一个字符串值永远不可能改变SQL语句本身的结构。这个方案对所有主流数据库都有效也是各大安全规范里反复强调的标准做法。如果代码里还在用字符串拼接SQL且拼的包含用户输入那不管前面做了什么过滤都等于没锁门。6.3 权限最小化让注入就算发生也伤不到根本安全不是一道门而是一串防御。就算应用层面被注入成功数据库账号的权限如果足够小损失就能控制住。应用连接数据库的账号原则上只给它业务上必需的权限——只读的报表系统就只给SELECT后端管理系统按需给INSERT、UPDATE、DELETE绝不给DDL权限CREATE、DROP、ALTER。不要图省事让应用账号拥有DBA权限更不要用sa或root跑业务应用。执行计划缓存、存储过程这些高级玩法先不说单单一条“注入之后能不能删库”就足够决定系统生死。7. 语法写对之后执行计划与慢SQL优化还藏着半本书7.1 什么情况下必须看执行计划SQL语法正确不代表它跑得高效。数据量小的时候差别看不出来一旦表里几百万行写法和写法之间的性能差距就是毫秒和秒级的差距。这时候再靠肉眼猜“为什么这么慢”是猜不出来的必须看执行计划。SQL Server Management StudioSSMS里有一个“显示估计的执行计划”按钮图标是一个带放大镜的表格DBeaver里连接SQL Server选中SQL语句右键选择“执行计划”或者用快捷键也能看到同样的信息。执行计划会以树状图展示数据库每一步的操作表扫描、聚集索引扫描、索引查找、键查找、哈希匹配、嵌套循环……你重点看两件事一是哪个操作耗时占比最高二是有没有出现“Scan”而不是“Seek”。表扫描/聚集索引扫描通常意味着查询在逐行遍历整张表数据量一大必然慢。索引查找意味着数据库通过索引直接定位到目标数据是高效路径。如果看到一条慢SQL的操作类型是Scan下一步就该分析它的WHERE条件为什么没用上索引。7.2 三个最常见的慢SQL写法第一个是SELECT *。业务代码里有时图省事查全列但把不用的列也捞回来数据库就必须回表去取这些列的数据增加IO开销。改成只select需要的列不仅减少回表还能让覆盖索引发挥作用。第二个是对索引列套函数。比如在create_time列上建了索引查询却写成WHERE DATE(create_time) 2024-01-01数据库无法使用索引因为索引里存的是原始值不是DATE()之后的值。正确写法是范围查询WHERE create_time 2024-01-01 AND create_time 2024-01-02。第三个是隐式类型转换。表里phone列是varchar类型查询写成WHERE phone 13800138000数据库会尝试把varchar转成数字再比较结果索引失效。查询时类型保持和列一致或者主动转换问题就能避免。7.3 索引为什么能提速覆盖索引又是什么索引的本质是给数据加了一层有序的查找结构可以类比书的目录没有目录你只能一页页翻有目录你直接翻到指定页码。数据库索引最常见的B树结构能把这个查找复杂度从全表扫描的线性级别降到对数级别。但有个细节很多人忽略索引能帮你快速定位到行可如果你select的列不在索引里数据库还要根据主键“回表”——就是回到原始数据页里把其他列取出来。回表一次、两次还好回表一万次性能就崩了。覆盖索引就是“索引本身就包含了查询需要的所有列”查询时只走索引就能拿到所有数据不需要回表。比如经常按user_id查status就可以建索引(user_id, status)那么SELECT status FROM table WHERE user_id 123这条SQL直接从索引里拿值速度极快。索引也不是建得越多越好。每次INSERT、UPDATE、DELETE都需要同步维护索引结构索引太多会拖慢写入速度占用额外存储。建索引要针对“高频查询条件”和“排序/分组字段”建而不是给每个列都来一个。这篇文章从基础查询语法一路聊到了索引优化算是把日常接触最多的SQL知识点串完了。我在实际干活里感受最深的一点是SQL语法本身不难难的是遇到问题时的排查思路。行数不对先查关联逻辑速度太慢先看执行计划修改数据先开事务查询接口先参数化——这些习惯比多背十个语法点都管用。如果你现在正被某个SQL问题卡住不妨按这个思路重新走一遍大概率能少走不少弯路。