说实话作为一个数据库打交道多年的从业者我手机和电脑里存了一堆乱七八糟的SQL片段有的是调试时临时贴的有的是从别人博客抄来的还有的是自己踩坑之后赶紧记下来的。最近趁着项目间隙整理了一遍发现这些碎片化的SQL笔记居然能串成一条完整的线——从最基础的建表和去重到窗口函数、慢SQL优化再到SQL注入防御和SQL Server运维覆盖面还挺广。这篇就把它们重新梳理成一份成体系的SQL笔记。无论你是刚接触数据库的新人还是被慢查询、连接故障折磨过的老手应该都能从中找到点有用的东西。我会按实际使用频率来组织每一段都尽量说清楚“为什么这么做”而不是只丢给你一条能跑的语句。1. SQL基础建表、去重与空值的几个高频套路1.1 建表与默认值GUID没那么简单建表这件事看起来简单但越基础的越容易埋坑。比如主键用自增ID还是GUID我见过太多项目在这个选择上反复折腾。自增ID的好处是短、有序、索引友好但在分布式场景或者需要合并多库数据的场景下容易冲突。GUID在SQL Server里是uniqueidentifier能全局唯一可它有个让人头疼的问题默认值怎么写。很多人以为默认值GUID就是简单地写个DEFAULT 00000000-0000-0000-0000-000000000000结果每条记录都是同一个值。正确的写法是使用数据库内置函数生成新值。SQL Server里用NEWID()MySQL里是UUID()PostgreSQL里是gen_random_uuid()-- SQL Server CREATE TABLE orders ( order_id UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY, order_no VARCHAR(32) NOT NULL, created_at DATETIME DEFAULT GETDATE() ); -- MySQL 8.0 -- 注意MySQL的默认值不允许直接用表达式通常靠应用层生成或使用触发器如果你要的是有序GUID可以在SQL Server中用NEWSEQUENTIALID()它比NEWID()对聚集索引更友好能减少页分裂。这一点在插入频繁的表上体验特别明显我是吃过亏的——早期用NEWID()做主键插入速度越来越慢后来检查索引碎片才发现是页分裂太多导致的。1.2 去重三种姿势distinct、group by、row_number“SQL去重”应该是搜索量最大的需求之一。很多人一提去重就是SELECT DISTINCT但它有两个天然短板一是它会对所有查出来的列去重没法只针对某几列去重二是去重后的数据不能方便地挑选“每组的某一条”。实际工作中我更常用下面几种方案GROUP BY加聚合函数适合需要去重后还要统计的场景ROW_NUMBER() OVER(PARTITION BY ...)适合“每个分组保留指定顺序的第一条记录”临时表配合DELETE自连接适合清理表中已有的重复数据举个典型例子订单表里因为上游重复推送出现了多行相同order_no要保留最早一条WITH ranked AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY order_no ORDER BY created_at ASC) AS rn FROM orders ) DELETE FROM orders WHERE (order_no, created_at) IN ( SELECT order_no, created_at FROM ranked WHERE rn 1 );我之前在一个数据清洗任务里用过这个写法第一次跑的时候没加ORDER BY结果保留哪条完全随机后来统一按created_at ASC先排序再删才保证结果是“保留最早一条”。这类细节不踩一次坑很难记住。1.3 空值与类型判断别再让NULL捣乱NULL是SQL里最反直觉的东西。它既不等于空字符串也不等于0NULL NULL的结果都不是TRUE而是UNKNOWN。这就导致很多新手在WHERE条件里用col NULL查不到任何数据。判断空值的标准写法就两个WHERE col IS NULL; WHERE col IS NOT NULL;查询结果里想把NULL显示成别的值SQL Server和MySQL各有一套MySQLIFNULL(col, 默认值)SQL ServerISNULL(col, 默认值)或COALESCE(col, 默认值)通用做法COALESCE(col1, col2, 兜底值)COALESCE在多字段取首个非空值场景下很好用比如一个表里有手机号、微信号、邮箱三个字段业务上想取任意一个非空联系方式展示直接用COALESCE(phone, wechat, email)就行不用写一长串CASE WHEN。还有一个冷门需求出现在DB2里判断一个字符串是否是数字。DB2里可以用TRANSLATE把非数字字符替换掉再比较或者用正则表达式函数。MySQL里则是SELECT col, col REGEXP ^[0-9]$ AS is_numeric FROM temp_table;这个判断在数据处理阶段特别实用能提前筛掉脏数据避免后续做类型转换时直接报错。2. 窗口函数实战TopN、排名与同环比2.1 窗口函数到底解决了什么问题在没接触窗口函数之前实现“每个用户最近一单”这类需求我一般先GROUP BY拿到最大时间再回表关联SQL写得又长又绕。窗口函数出现之后这类问题的写法变得非常直观。窗口函数的核心是它能在不合并行的情况下对每一行计算一个基于“窗口内数据”的结果。你可以把它理解为“在结果集上再加一列计算值而不是改变结果集的行数”。最常见的四类窗口函数排序类ROW_NUMBER()、RANK()、DENSE_RANK()聚合类SUM() OVER()、AVG() OVER()、COUNT() OVER()偏移类LAG(col, n)取前第n行、LEAD(col, n)取后第n行分桶类NTILE(n)平均分成n组2.2 分组TopN与排名的写法分组取TopN是窗口函数最经典的场景。比如查每个部门工资最高的前三名SELECT * FROM ( SELECT *, RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS rk FROM employees ) t WHERE rk 3;RANK和DENSE_RANK的区别经常有人搞混RANK遇到相同值会跳过名次比如两个人并列第1下一个人就是第3DENSE_RANK则不会跳过下一个人还是第2。需要“连续排名”的报表一般用DENSE_RANK。聚合类的窗口函数在计算“累计”时很有用。比如求每个用户截至当前天的累计消费SELECT user_id, order_date, amount, SUM(amount) OVER(PARTITION BY user_id ORDER BY order_date) AS cumulative_amount FROM orders;这里ORDER BY在窗口内不仅控制排序还决定了累加的边界。不加ORDER BY时窗口是整个分组算出来的是分组总和加了ORDER BY后窗口变成从分组第一行到当前行这就是“累计”的逻辑。2.3 窗口函数常见的WRONG用法我见过不少人在窗口函数的边界条件上翻车挑几个最容易错的忘记PARTITION BY没分组就排序会把全表当成一个窗口算出完全不符合业务预期的结果。WHERE和窗口函数混用窗口函数在WHERE之后执行所以不能在WHERE里直接引用窗口函数的别名必须包一层子查询。ORDER BY对聚合窗口的影响前面说过有ORDER BY和没ORDER BY结果完全不同写之前想清楚你到底是想要“累计值”还是“分组总值”。另外要留意数据库版本。MySQL 8.0才支持窗口函数5.7及以下没有这个能力SQL Server从2012开始有大部分窗口函数但IGNORE NULLS这类选项要到2016才好用。如果你还在维护老库遇到这类需求就只能用变量自连接这类土办法替代。3. 慢SQL优化定位、改写与并行3.1 先回答三个问题真的慢吗、卡在哪、怎么改慢SQL优化最容易犯的错是一上来就加索引。索引不是万能的甚至可能让问题更隐蔽。我自己的排查顺序是先确认是不是真的慢再定位瓶颈在哪儿最后才动手改。第一步开启慢查询日志。MySQL里是slow_query_log参数设置long_query_time 1表示超过1秒的SQL都会记录下来SQL Server可以用sys.dm_exec_query_stats配合DMV查询耗时靠前的语句。拿到慢SQL之后用执行计划看它卡在哪。MySQL里是EXPLAINSQL Server是“显示估计的执行计划”主要看几个东西type列从ALL全表扫描到eq_ref、const级别越高性能越好rows列预估扫描行数Extra列出现Using filesort或Using temporary往往是排序和临时表导致的慢3.2 慢SQL典型案例拆解整理几个我实际遇到过的慢SQL套路深分页问题。LIMIT 1000000, 20这种写法数据库会先扫出前1000020行再丢掉前1000000行。数据量一大哪怕有索引也扛不住。改写方案是用“上一页最后一条记录的ID”做条件-- 优化前 SELECT * FROM orders ORDER BY id LIMIT 1000000, 20; -- 优化后 SELECT * FROM orders WHERE id 1000000 ORDER BY id LIMIT 20;隐式类型转换。字符串类型的字段用了数字条件索引直接失效。比如phone是VARCHAR却写WHERE phone 13800138000MySQL会尝试把字段转成数字导致无法走索引。正确的做法是写成WHERE phone 13800138000。索引列上做运算。WHERE DATE(created_at) 2024-01-01看着没毛病但函数包裹了索引列索引直接失效。改成范围查询就好WHERE created_at 2024-01-01 AND created_at 2024-01-023.3 并行优化与参数调优单条SQL已经没法再优化的情况下并行是最后的利器。SQL Server默认会为查询选择合适的并行度但如果服务器的MAXDOP设置不合理反而可能拖慢整体性能。如果一个OLTP系统频繁出现CXPACKET等待可以适度调低并行度-- 在实例级别查看当前设置的MAXDOP SELECT * FROM sys.configurations WHERE name max degree of parallelism;更常见的是批处理任务并行。比如要更新一张千万级大表的某些字段一条UPDATE跑几个小时很正常拆成按主键范围分批并行执行反而更快。我之前用WHILE循环配合TOP (10000)分批更新整体耗时有明显下降同时对在线业务的冲击也小很多。MySQL这边要注意innodb_buffer_pool_size和sort_buffer_size这类参数。数据量大时buffer pool过小会导致频繁读磁盘sort buffer过小则会把排序落到临时表。这些参数不是越大越好要根据物理内存和业务特性来定改完需要观察一段时间。4. SQL注入原理、复现与防御4.1 注入是怎么发生的SQL注入的本质是把用户输入当成了SQL代码的一部分去执行。最常见的原因是拼接字符串比如早期很多教程里的写法SELECT * FROM users WHERE username admin AND password 123456如果用户输入的用户名是admin --整个语句变成SELECT * FROM users WHERE username admin -- AND password 123456--把后面的条件全部注释掉密码校验形同虚设。这就是“万能密码”的基本原理只不过实际利用时会有更多变体。我在测试自己项目的登录功能时就遇到过这类问题。当时用安全扫描工具随便扫了一下直接在登录接口报出了注入风险。原因就是早期图省事写了几条拼接SQL后来全部改成参数化查询才解决。这个教训让我意识到注入风险不只是攻击者的事作为开发方必须主动自查。4.2 常见的注入场景和安全自检热词里提到“fofa查询sql注入”“dvwa sql注入”“sql注入靶场”“ctfshow sql注入生成文件”这些都是安全研究和安全测试领域的常见场景。DVWA是一个知名的漏洞练习平台里面专门有SQL Injection模块可以练习如何发现和利用注入点CTF比赛里也经常出现利用注入写文件的题目。这些靶场平台的存在本身就是为了让安全人员和开发人员能在受控环境中理解注入原理。从开发者的角度我的建议是把这些靶场当作“反面教材”来学习。理解了攻击者的思路才知道为什么参数化查询和输入校验缺一不可。4.3 防御实践与自检清单防御SQL注入没有什么黑科技核心就几条但必须形成习惯所有SQL都要用参数化查询或预编译语句禁止拼接用户输入。这是最有效的一招。Python的pymysql用%s占位符Java的PreparedStatementGo的database/sql都能自动处理参数转义。ORM框架通常能降低注入风险但原生SQL依然要小心。比如Prisma里调用原生SQL时也要用$queryRaw加参数占位符的方式而不是直接拼字符串。输入校验按白名单思路来做该是数字的强制转int该是日期的强制解析格式该是枚举值的就严格匹配枚举。数据库账号遵循最小权限原则。业务账号不该有DROP、FILE、超级管理权限——就算被注入了攻击者也做不了太多破坏。我给自己定了个自检清单每个涉及用户输入的SQL上线前必须确认是不是参数化写法每次代码审查只要看到字符串拼接SQL直接打回。这是最笨但最有效的规矩。5. SQL Server运维实录安装、密码与连接故障5.1 版本选择与安装踩坑SQL Server常见版本有Express、Standard和Enterprise。很多人一开始就纠结用哪个。Express版免费、有10GB数据库大小限制适合学习和轻量应用开发/测试场景完全够用。正式商业项目需要高可用、内存大等高级特性时再考虑Standard或Enterprise。安装上有几个容易被忽略的点用SQL Server 2019/2022镜像安装时实例配置界面要记住实例名。默认实例是MSSQLSERVER命名实例则是计算机名\实例名连接字符串写错一个斜杠就会连不上。SQL Server安装后的默认端口是1433如果是云服务器记得在安全组里放行端口别只顾着改防火墙。SSMSSQL Server Management Studio是官方免费的管理工具微软官网直接下载进入页面后选对应的中文版或英文版即可。不要从第三方下载站走那些打包的可能带私货。5.2 密码到期与卸载清理SQL Server 2012及以后的版本默认开启了密码过期策略经常有DBA遇到“密码已过期必须更改”连不进去。处理办法有两个一是用系统管理员账号登录后在用户属性里勾选“不强制实施密码过期”二是直接改密码。如果是Windows身份验证登录也可以用ALTER LOGIN来关掉过期策略ALTER LOGIN sa WITH CHECK_POLICY OFF;注意关掉策略会影响安全性更好的做法是把密码换成强密码而不是关闭策略。我还见过有人把密码过期问题误认为是服务坏了其实是服务正常只是登录工具里缓存了旧密码。卸载SQL Server是另一个大坑。只通过控制面板卸载经常会留下服务、注册表和磁盘目录残留导致重装时冲突。规范流程是先停掉服务再用控制面板卸载所有SQL Server相关的程序最后手动删除数据目录和注册表残留。我清理的时候会检查这几处服务列表、C:\Program Files\Microsoft SQL Server目录、HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server注册表项。5.3 SolidWorks等软件连不上SQL Server的排查热词里有个具体场景SolidWorks Electrical提示“无法连接到SQL Server”并列出“用户名或密码、服务未启动”等可能原因。这种第三方软件连接数据库失败的排查套路是通用的。我的排查顺序先确认SQL Server服务是否在运行。打开“服务”管理器找SQL Server (MSSQLSERVER)实例确保状态是“正在运行”。服务没起来后面所有排查都是白费。确认TCP/IP协议是否启用。用“SQL Server配置管理器”打开“SQL Server网络配置”把TCP/IP启用。很多软件默认走TCP连接协议禁用必然失败。验证账号密码。第三方软件内置了数据库账号通常是sa或专用账号确认密码没到期、没被锁定。用SSMS在本机测试同一账号能否登录。如果SSMS能连而软件连不上问题大概率出在端口、协议或客户端版本兼容上。其中“用户名或密码”几乎占了这类故障的一半以上。SolidWorks Electrical这个场景我也看到过不少次多半是安装时输入的sa密码不对或者SQL实例名写错换到正确实例名就好。6. 与代码打交道ORM原生SQL、脚本执行与AI辅助6.1 Prisma里调用原生SQL的正确姿势Prisma是Node.js生态里很火的ORM平时用prisma.user.findMany()这类API就行。但在复杂报表、窗口函数、批量更新等场景下ORM的API表达能力不够原生SQL就派上用场了。Prisma提供$queryRaw和$executeRaw两个方法const results await prisma.$queryRaw SELECT id, name, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employees ;注意这里用的是模板字符串语法参数直接通过${value}传进去Prisma会把它们当作绑定参数处理而不是拼进SQL。千万别手贱去写字符串拼接一次拼接就等于把注入漏洞亲手打开。$queryRaw适合查询$executeRaw适合更新和删除两者的返回值和使用限制有区别官方文档里写得很清楚。6.2 Python执行SQL容易忽略的坑Python连接数据库常见的库有pymysql、psycopg2、pyodbc等。踩坑点主要体现在两处一是超时问题。默认情况下很多库的查询超时设置很长甚至没有设置遇到慢查询时Python这边可能早就因为请求堆积而表现异常。我习惯在连接参数里显式设置超时比如PyMySQL的read_timeout和write_timeout以及连接层的connect_timeoutimport pymysql conn pymysql.connect( host10.0.0.10, userapp_user, password******, databaseapp_db, read_timeout10, connect_timeout5 )二是SQLite等轻量库的异常信息容易误导。比如热词里提到的SqliteException(1): while preparing statement, no such column: test_url这个错误不是“没有test_url列”这么简单。它可能出现在你刚ALTER TABLE加列却没提交事务或者查询的列名和实际表结构对不上。排查时先查表结构再确认连接的是不是同一个数据库文件——我就经历过连了旧的库文件、查了半天才发现路径不对。6.3 AI辅助SQL的正确打开方式现在用AI辅助写SQL已经很普遍了。热词里也提到“dbx怎么使用ai辅助sql”这类工具能帮我们快速生成基础语句、解释执行计划、转换不同数据库的方言。我的用法是用AI生成初稿但必须人工理解、验证、改写成符合规范的版本。窗口函数、复杂关联这类场景AI给出的答案经常有边界条件遗漏直接用容易埋雷。一个小技巧是让AI给SQL加注释说清楚每一步的逻辑然后你逐行阅读相当于让AI当你的结对编程伙伴。另外AI对数据库方言的差异理解并不总是准确。SQL Server的TOP、MySQL的LIMIT、Oracle的FETCH FIRST在语法上完全不同生成之后最好在目标数据库上跑一遍执行计划再上线。工具是效率放大器但用工具的这个人懂不懂SQL决定了产物质量的上限。结尾说点题外话整理这份笔记的时候我最大的感受是SQL这个技能貌似简单但真正用得顺手需要大量实战积累。从去重用什么写法、窗口函数的边界条件到慢SQL怎么定位、连接故障怎么排查每一块都是我踩过坑才记住的。如果你现在还在SQL入门阶段别急着背语法先把手头业务的问题写成SQL跑一遍遇到报错就去查执行计划和官方文档这样学到的才是真正能用的东西。后面如果时间允许我打算把这份笔记再扩展一下补充更多关于数据库迁移和报表优化的实战案例。