我第一次觉得自己“会写SQL”是在一个深夜。当时线上一个订单统计报表的数据对不上我盯着一条十几行的大SQL来回看了两个小时最后发现问题出在JOIN的关联条件少了一个等值字段结果生成了大量重复行。从那以后我养成了一个习惯不管多简单的数据库操作先想清楚它会返回多少行、为什么是这么多行。这些年我带过的实习生和转行的朋友里十个有八个不是被语法难住而是被“不知道自己在操作什么”卡住。所以这篇我就从最底层讲起把SQL核心操作按实战顺序串一遍建表、增删改查、查询进阶、去重、窗口函数、慢SQL优化、安全红线一篇全部走完。这篇内容适合谁刚入门数据库的开发者、准备面试的应届生、数据分析岗位上手SQL的人以及那些“能跑但总觉得不踏实”的选手。我不会给你列一堆冷门炫技语法而是把生产环境里真正高频的用法讲透每条都配可以直接抄走的SQL再附上一些只有踩过坑才写得出来的经验。看到最后你会发现SQL的框架其实非常小难的是每个细节背后的“为什么”。1. 动手之前先搞清楚SQL到底在操作什么1.1 数据库就是一堆Excel只是比Excel严肃很多人学SQL卡住的第一步不是语法而是脑子里没有结构。我给你一个最直白的类比数据库里的库database就像是一个Excel工作簿表table就是工作簿里的工作表行row是每一行记录列column是每个字段。你写下的任何一条SQL本质上都是在回答四个问题之一数据往哪里存我要查哪些数据我要改哪些数据我要删哪些数据把这四件事想清楚SQL语法就只是把想法翻译成数据库能听懂的话而已。以一张学生表为例idnameagescorecreated_at1张三2095.502024-01-01 10:00:002李四2188.002024-01-02 11:00:00这张表里id是主键用来唯一标识每一行记录name是学生名字age是年龄score是成绩created_at是这条记录创建的时间。这个简单的信息结构就是绝大多数业务系统里最难设计、也最容易出问题的地方后面所有例子我都会围绕这张学生表展开。1.2 字段类型选错后面全是雷建表之前必须决定每一列的数据类型这个决定会深远影响后续开发和性能。最常见的几类字段类型我按实战使用频率排一下整数类INT、BIGINT。一般业务表的主键、数量、状态码都用它量级大的场景用BIGINT。小数类DECIMAL(精度, 小数位)比如 DECIMAL(5,2) 表示最多5位数字且保留2位小数取值范围最大就是 999.99。字符串类VARCHAR(长度)。长度按业务上限来定别一上来就 VARCHAR(1000) 怼上去浪费存储还会影响索引效率。日期时间类DATETIME 和 TIMESTAMP。业务时间戳建议统一用DATETIME跨时区场景再研究TIMESTAMP的细节。布尔类MySQL 里一般用 TINYINT(1)用 0 和 1 表示真伪。这里有一条我提醒了无数次的原则金额永远不要用FLOAT或DOUBLE。浮点数在计算机里是近似值0.1 加 0.2 可能等于 0.30000000000000004这在账务系统里会出大事。金额一律用 DECIMAL这是老程序员用事故换来的教训新手别再去碰一遍。1.3 主键、自增和工具选择主键的选型MySQL 系列最常见的方案是自增整数主键id INT AUTO_INCREMENT PRIMARY KEYAUTO_INCREMENT 让数据库自动给你发号保证每一行都有唯一编号。这里我建议不要用业务字段比如身份证号、手机号当主键业务字段可能会变一旦变了就要连锁更新一堆关联表代价极大。主键就该是一个和业务无关、永远不变的纯编号。工具方面新手建议先用图形化工具建立直观感受。我自己常用的是 DBeaver开源免费跨平台和 Navicat更傻瓜化两者各有拥趸看个人习惯就好。但命令行也不要丢服务器上排查问题的时候你只有命令行可用。工具只是放大镜SQL本身才是手艺。2. 建表和改数据INSERT、UPDATE、DELETE的正确姿势2.1 建表CREATE TABLE 的完整写法与每个关键字的含义先看我实际建表时最喜欢用的模板CREATE TABLE student ( id INT AUTO_INCREMENT PRIMARY KEY COMMENT 主键, name VARCHAR(50) NOT NULL COMMENT 姓名, age INT COMMENT 年龄, score DECIMAL(5,2) COMMENT 成绩, created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT学生表;逐行解释一下每个关键字的用途NOT NULL 表示这个字段不能为空name 这种核心字段一定要加上不然数据库里会出现一堆没有名字的学生记录后面查起来极其痛苦。DEFAULT CURRENT_TIMESTAMP 表示插入数据时如果不指定 created_at就自动填当前时间。这个习惯能帮你省掉大量业务代码。ENGINEInnoDB 是 MySQL 的默认存储引擎支持事务和外键绝大多数业务场景选它都没错。CHARSETutf8mb4 是字符集。为什么不用 utf8因为 MySQL 的 utf8 最多存 3 字节遇到 emoji 或者生僻字就会报错utf8mb4 才是真正的四字节 UTF-8。这个坑我帮不止一个团队填过基本都是上线后写入报错才发现。2.2 加数据INSERT 的基本操作单条插入INSERT INTO student (name, age, score) VALUES (张三, 20, 95.5);多条插入INSERT INTO student (name, age, score) VALUES (李四, 21, 88.0), (王五, 22, 77.5);注意我只列了 name、age、score 三个字段id 和 created_at 会自动填写这正是刚才建表时设计的好处。这个习惯也推荐给你INSERT 时尽量显式列出字段名数据对应关系一目了然以后表结构加了字段也不会因为位置错乱而出现诡异的数据。2.3 改数据UPDATE 的命根子是 WHEREUPDATE student SET score 90 WHERE id 1;这句话的意思是把 id1 这个学生的成绩改成 90。这里我要用最大的字号强调一句WHERE 是 UPDATE 的命根子。一旦忘了写 WHERE就是全表更新。我在生产环境真实见过一次事故运维同学想改一条配置SQL 少了 WHERE整个表几万条记录的金额字段全变成了同一个值。因为没有备份最后只能从 binlog 一点点回放恢复折腾了一整夜。所以我在团队里定了一条规矩写 UPDATE 之前先用同样的 WHERE 跑一遍 SELECT确认影响的行数符合预期再执行 UPDATE。多花十秒钟能省十个小时。2.4 删数据DELETE、TRUNCATE、DROP 三种选择DELETE FROM student WHERE id 1;DELETE 可以带 WHERE逐行删除如果放在事务里还可以回滚。TRUNCATE TABLE student 是把整张表清空速度快但不可回滚。DROP TABLE student 更狠连表结构一起销毁。这三个的区别一定要背下来因为面试爱问线上也真的会因为用错而出事。另外有个小知识点DELETE 清空表之后表的自增计数器不会重置再插入数据 id 会接着原来的编号继续走TRUNCATE 才会重置计数器。我在面试里问过很多人能答上来的不多这是个容易忽略的细节。2.5 事务给批量操作上一个“后悔药”当你需要同时改多张表或者一张表的多行数据时就要用事务包起来START TRANSACTION; UPDATE account SET balance balance - 100 WHERE id 1; UPDATE account SET balance balance 100 WHERE id 2; COMMIT;这就是经典的转账场景A 扣钱和 B 加钱必须同时成功或者同时失败。如果第一条执行完、第二条还没执行时程序崩了事务会在数据库层面保证不会出现“钱扣了但没到账”的中间状态。在 COMMIT 之前你可以随时 ROLLBACK 撤销相当于一个后悔药。这个“要么全成功、要么全失败”的性质就是事务的原子性也是为什么所有银行系统都把事务看得比命还重。3. SELECT查询把80%的常用操作练成肌肉记忆3.1 查询的基本框架SELECT、FROM、WHERESELECT 是整个 SQL 里使用频率最高的语句没有之一。最基本的框架长这样SELECT id, name, score FROM student WHERE score 90;SELECT 后面跟的是你要的列FROM 后面是数据来源的表WHERE 后面是过滤条件。可以把 SELECT 理解为“我要从这张表里捞哪些列”WHERE 理解为“只留下哪些行”。新手容易犯的一个毛病是走到哪儿都 SELECT *开发环境无所谓生产环境我强烈不建议一张宽表可能有几十个字段其中还有几个长文本大字段全捞出来网络传输慢、内存占用高还会让查询无法利用覆盖索引。正确姿势是只 SELECT 需要的列这既是性能意识也是安全意识。3.2 模糊匹配和范围过滤LIKE、IN、BETWEENWHERE 里除了 、、、、、不等于这些比较符还有三个高频操作符-- 名字以“张”开头的学生 SELECT * FROM student WHERE name LIKE 张%; -- 年龄在20、22这两个值之中的学生 SELECT * FROM student WHERE age IN (20, 22); -- 成绩在80到90之间含边界 SELECT * FROM student WHERE score BETWEEN 80 AND 90;LIKE 里 % 表示任意多个字符_ 表示任意一个字符。这里提前埋一个后面要说的坑LIKE 的前缀模糊比如 %张没法用索引数据量大时会慢到怀疑人生。IN 的括号里可以列多个值BETWEEN 是闭区间两个边界都包含写条件的时候心里要有数。3.3 排序和分组ORDER BY、GROUP BY、聚合函数排序很简单SELECT * FROM student ORDER BY score DESC, id ASC;DESC 是降序ASC 是升序默认就是升序。多个排序字段时先按第一个排相同再按第二个。分组是 SELECT 里面最需要动脑的一步比如我想统计每个班级有多少人、平均分是多少SELECT class_id, COUNT(*) AS student_count, AVG(score) AS avg_score FROM student GROUP BY class_id;COUNT 数行数AVG 求平均此外还有 SUM 求和、MAX 求最大、MIN 求最小。加了 GROUP BY 之后SELECT 后面只能出现分组字段和聚合函数的结果这是一个小白高频报错点。老手也会在这里翻车有时候分组的维度没想清楚聚合出来的一列数字看起来对但一拆细看就错了所以分组前先问自己“我到底想按什么维度看问题”。3.4 HAVING 和 WHERE过滤时机完全不同想过滤掉平均分低于 80 的班级很多人会顺手把条件写在 WHERE 里然后报错。正确写法是用 HAVINGSELECT class_id, AVG(score) AS avg_score FROM student WHERE score 60 GROUP BY class_id HAVING AVG(score) 80;两者的本质区别是时机WHERE 在分组之前过滤行HAVING 在分组之后过滤组。WHERE 不能使用聚合函数因为它在聚合发生之前执行HAVING 可以。一句话记法先有行再有组后有组过滤。这个先后关系想清楚了基本就不会再写错。3.5 多表关联JOIN 的三种姿势和实战场景单表查询练熟之后就要面对多表关联。生产环境里数据几乎不可能是孤岛用户表、订单表、商品表之间全靠外键关系串起来。SELECT s.name, c.class_name FROM student s INNER JOIN class c ON s.class_id c.id;INNER JOIN 只保留两边都匹配得上的行LEFT JOIN 保留左表全部行右表匹配不上就补 NULLRIGHT JOIN 则反过来。实际工作中 LEFT JOIN 用得最多因为它能很方便地找出“左边有、右边没有”的数据。一个我经常给团队讲的例子找出从来没下过单的用户。SELECT u.id, u.name FROM user u LEFT JOIN orders o ON u.id o.user_id WHERE o.id IS NULL;这个套路也可以改成 LEFT JOIN GROUP BY HAVING 的组合统计每个用户的下单数量再筛出数量为 0 的用户。JOIN 的坑也不少最常见的是关联字段类型不一致导致隐式类型转换索引用不上小表没感觉一上大数据量就慢。建表时把关联字段的类型设计成一致能省掉后面一大堆性能问题。4. 进阶写法去重、窗口函数和分页的高级姿势4.1 DISTINCT 能去重但场景一复杂就不够用最简单的去重是 DISTINCTSELECT DISTINCT class_id FROM student;但开发中遇到更多的场景是“按某个维度去重同时保留每个分组里的一条记录”。比如每个用户有多次登录记录我想取每个用户最新一次登录。这种需求 DISTINCT 就无能为力了因为 DISTINCT 只能去除完全相同的行没法表达“取每组的最新一条”。这时候要轮到窗口函数登场。4.2 ROW_NUMBER()取每个分组最新一条的标准解法窗口函数是 SQL 进阶里性价比最高的一块MySQL 8.0、PostgreSQL、SQL Server、Oracle 都原生支持。取每个用户最新登录记录的标准写法是SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_time DESC) AS rn FROM login_log ) t WHERE t.rn 1;拆开看PARTITION BY user_id 表示按用户分组ORDER BY login_time DESC 表示组内按时间倒序排ROW_NUMBER() 给每个组内从上到下编上 1、2、3……编号最后外层只需取 rn 1就是每个用户最新的一条记录。这个模式我愿称之为去重需求里的“万金油”因为它不仅能去重还能顺便表达“保留哪一条”的业务规则——是最新一条、还是最早一条、还是分数最高一条改 ORDER BY 就行。比用子查询加 MAX 凑出来的写法干净太多执行效率也更高。4.3 RANK 和 DENSE_RANK排名题的标答窗口函数家族里还有两个经常一起出现的RANK 和 DENSE_RANK。三者的区别拿成绩排名举例ROW_NUMBER()不管并列就是 1、2、3、4。RANK()成绩相同并列且跳号比如 1、1、3。DENSE_RANK()并列但不跳号比如 1、1、2。经典面试题“取每个分类下销量最高的前三名商品”标准解法SELECT category, product, sales FROM ( SELECT category, product, sales, DENSE_RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS rk FROM product_sales ) t WHERE t.rk 3;这类“每个分组取Top N”的问题在面试题库里出现率极高用窗口函数是最优雅的答案。顺带一提窗口函数和 GROUP BY 最大的区别是GROUP BY 会把多行压成一行窗口函数则是保留每一行在旁边多算一列结果。理解这一点你就明白为什么窗口函数在报表场景特别好用。4.4 分页查询LIMIT、OFFSET 与深分页的坑分页是另一个绕不开的日常操作SELECT * FROM student ORDER BY id LIMIT 10 OFFSET 20;LIMIT 10 表示最多返回 10 条OFFSET 20 表示跳过前 20 条。这套语法在 MySQL 里还可以写成 LIMIT 20, 10建议团队统一用前者可读性更好。分页有个隐藏杀手叫深分页当 OFFSET 到几十万甚至上百万时数据库要先把前面的记录全部扫一遍再丢掉查询会越来越慢。数据量大时的经典优化是“延迟关联”SELECT s.* FROM student s INNER JOIN (SELECT id FROM student ORDER BY id LIMIT 100000, 10) tmp ON s.id tmp.id;先用子查询只取主键完成偏移再回原表捞整行扫描量大大降低。业务上更优雅的方案是“游标分页”利用上一页最后一条记录的 id 作为条件WHERE id 上一页最大id ORDER BY id LIMIT 10。这种写法翻页越深性能越稳但前提是排序字段稳定唯一通常就用主键。5. 慢SQL是怎么来的索引、执行计划和优化习惯5.1 慢SQL的第一课全表扫描 vs 索引如果说前面的内容是在教“怎么写对”这一节就是教“怎么写快”。同一个查询在别人的库上毫秒返回在你的库上可能要好几秒最核心的差别就是走没走索引。索引的原理可以类比字典的检字表你要查“数据库”这个词不会从第一页开始逐页翻而是先通过拼音或偏旁找到这个字所在的页码。数据库的 B 树索引就是那张“检字表”。没有索引时数据库只能全表扫描一行一行看数据量从一万行涨到一亿行扫描时间可能涨一万倍这就是慢SQL最常见的来源。给字段加索引的语法很简单CREATE INDEX idx_name ON student(name);加了之后等值查询、范围查询的速度会有肉眼可见的提升。但索引不是越多越好这一点后面我会专门说。5.2 EXPLAIN 执行计划让数据库告诉你它打算怎么跑判断一条 SQL 慢不慢、慢在哪最直接的方法是看执行计划。MySQL 里就是一句EXPLAIN SELECT * FROM student WHERE name 张三;重点看四列type访问类型。出现 ALL 就是全表扫描这是最需要警惕的出现 ref、range、const 都说明索引起了作用。key实际使用到的索引名是 NULL 就说明没走索引。rows预估扫描的行数这个数字越大越危险。Extra有时会出现 Using filesort 或 Using temporary表示排序或分组在临时表里进行数据量大时也要优化。我对团队的要求是上线的查询至少过一眼 EXPLAIN不能接受 type 为 ALL 且 rows 特别大的 SQL。养成这个习惯之后慢SQL数量会直线下降因为大多数性能问题根本不用等到压测看一眼执行计划心里就有数了。5.3 我实测过的几种典型慢SQL场景下面这些场景都是我实际排查过的一个个列出来SELECT * 捞了一堆不需要的宽表字段尤其是长文本字段把网络传输的瓶颈直接拉满。对索引列做函数运算比如 WHERE YEAR(order_time) 2024索引直接失效。正确写法是改成范围条件WHERE order_time 2024-01-01 AND order_time 2025-01-01。前导模糊匹配 LIKE %关键词%没法匹配索引的开头等于全表扫描能改成 LIKE 关键词% 的尽量改或者换搜索引擎方案。隐式类型转换varchar 列和数字做比较数据库要先把列转成数字索引失效。关联字段类型不一致也会触发同样的问题。OR 连接多个条件如果涉及多个列索引策略会变得复杂某些情况下会退化成全表扫描可以改写为 UNION ALL或者排查是否能用 IN 替代。深分页问题就是刚才讲过的 OFFSET 越大越慢。在 WHERE 条件里做了字符串拼接或其他运算比如 WHERE name CONCAT(张,三)同样会导致索引失效。每一类都不是语法错误运行时也“正确”但就是慢。排查慢SQL时第一反应不应该是“加索引”而是先看 SQL 本身有没有破坏索引生效的写法改掉之后很可能连索引都不用加。5.4 索引的取舍不是越多越好索引能加速查询但每一次 INSERT、UPDATE、DELETE数据库都要同步维护索引索引太多写入就会变慢磁盘占用也会涨。所以加索引要克制我给自己定的原则是只给高频查询的 WHERE 条件列和 JOIN 关联列建索引。优先选区分度高的列比如订单号、状态码这种像性别这种只有两种值的列建索引收益很低一般不建。联合索引遵循最左前缀原则建了 (a, b) 的联合索引单独查 a 能用到单独查 b 用不到。单表索引数量控制在 5 个以内超出就要反思表设计是不是有问题。5.5 优化慢SQL的正确顺序最后总结一下排查顺序先改写 SQL去掉多余列、改成等值条件、消除函数运算再看执行计划确认瓶颈然后才考虑加索引最后如果还是慢就要回到数据模型层面看看是不是表结构设计不合理、是否需要拆表或冗余字段。顺序一旦反了很容易出现“索引加了一堆问题反而更严重”的尴尬。6. 安全底线SQL注入、连接池和上线前的自检清单6.1 SQL注入是什么一句拼接毁掉一张表SQL 写得再快如果安全底线没守住一切都是零。SQL注入是 Web 安全里历史最悠久、危害最大的漏洞类型之一原理很简单程序把用户输入直接拼进了 SQL 字符串。比如登录功能写成SELECT * FROM user WHERE name 用户输入 AND password 用户输入;如果用户在输入框里填了特殊内容比如一段以引号结尾再追加其他命令的字符串拼出来的 SQL 就可能变成多条命令最坏的情况下整张表都没了。我讲这个案例给每个团队成员听不是为了教怎么写攻击语句而是为了让人真正理解为什么业务代码里禁止字符串拼接SQL是一条不能碰的红线。6.2 防御的核心参数化查询和预编译防御 SQL 注入的第一道防线也是唯一必须执行的一道是参数化查询也叫预编译。以 Python 的数据库驱动为例cursor.execute(SELECT * FROM user WHERE name %s AND password %s, (name, password))SQL 语句和数据是分开传的数据库先把 SQL 结构编译好再把你传的值当成纯参数绑定进去。用户输入永远只是“值”永远不会被当成“代码”解析。无论用户填什么乱七八糟的内容最终都只是字符串数据而已。再聊一下纵深防御应用层连接数据库的账号原则上只给必要权限SELECT、INSERT、UPDATE、DELETE不给 DROP、TRUNCATE、CREATE 这类高危权限。即使发生注入攻击者能造成的破坏范围也会小很多。权限最小化这个习惯应该写进每个团队的开发规范。6.3 连接池为什么每个服务都离不开它每个应用访问数据库都绕不开连接池这个概念。原因是建立一条数据库连接的成本很高握手、鉴权、分配资源这些开销可能比 SQL 本身执行还耗时。如果每一个请求都新建连接、用完再关闭高并发下基本是在给数据库服务器添堵。连接池的思路就是提前创建一批连接放在池子里谁要用谁借用完归还。常见的连接池组件有 HikariCP、Druid 等配置里核心就三个参数最大连接数、最小空闲数、连接超时时间。这里有个新手容易犯的错误以为最大连接数越大越好直接配成几千。实际上数据库能同时处理的连接有限开太多连接大家都在排队等数据库响应反而更慢。我一般建议先按应用实例的 CPU 核心数给一个基准值再通过压测调整。连接池是“池”不是“水库”够用就行。6.4 上线前的SQL自检清单因为踩过的坑实在太多我给自己整理了一份上线前自检清单每次发版前都要过一遍UPDATE 和 DELETE 是否都带了 WHERE没带的一律打回。有没有用 EXPLAIN 看执行计划type 是否为 ALLSELECT 是否捞了多余的大字段代码里有没有字符串拼接 SQL有就改成参数化查询。涉及多表更新的逻辑有没有用事务包起来COMMIT 和 ROLLBACK 分支是否都覆盖应用账号的权限是否最小化高危权限是否已经回收这份清单看似简单每一条背后都是真实的事故和漫长的排查。我宁愿每次上线前多花十分钟也不愿意在生产环境里花一晚上收拾残局。SQL 学到什么程度算“上手”我的标准是给你一张陌生的业务表你能独立完成建表、录入、按各种条件筛选输出、统计报表、排查慢查询并且在写 UPDATE 和 DELETE 的时候条件反射地检查 WHERE。能做到这几点你已经超过了相当一部分贴着“开发”标签的人。最后再分享一个小技巧本地装一个开源的 MySQL 或者 SQLite拿这篇里的每一条 SQL 亲手敲一遍再故意把 WHERE 去掉看看会发生什么。亲手踩一次坑比看十篇文章都记得牢。