上周隔壁组出了个小事故一个上线两年的老系统运营想清理一张日志表的过期数据结果直连数据库的同事把DELETE写成了DROP TABLE回车一敲整张表连结构带数据全没了。等发现的时候只能靠备份恢复前后折腾了大半夜线上业务也受了影响。细问之下才知道这位同事学 SQL 完全是零散着学的——今天会写一句SELECT查个数明天照着文档抄一句UPDATE从来没把 SQL 这门“语言”在认知上拆开看过。其实 SQL 远不止“查询”和“增删改查”这么笼统。按它操作的对象和行为业内习惯把它分成四类DDL管结构、DML管数据、DQL管查询、DCL管权限。这个分类不只是考试题里的一问而是你写任何一条 SQL 之前脑子里必须形成的一条边界线。这篇文章我会用一张订单业务中常见的几张表做例子把四类语法完整过一遍顺带讲讲每一类里那些文档里很少写、但线上一定会踩到的坑。适合刚入门数据库的开发者也适合做了好几年 CRUD 想系统梳理一遍的同学。1. 先弄清底层逻辑SQL 不是一门语言而是四把不同的钥匙1.1 分类依据你操作的到底是“结构”还是“数据”很多人背得下“DDL 是数据定义语言”但问他“为什么要把 SQL 拆成这四类”往往答不上来。拆分类目不是组织拍脑袋定的而是因为这几类语句进了数据库之后走的路径、拿的锁、能不能回滚完全不是一回事。DDLData Definition Language管理的是“数据库长什么样”建表、改表、删表、建索引、建视图动的是结构DMLData Manipulation Language管理的是“表里装了什么”插入、修改、删除行数据动的是内容**DQLData Query Language**管理的是“怎么把数据读出来”SELECT无论写多复杂都不改变任何一行数据**DCLData Control Language**管理的是“谁能碰这些数据”用户、权限、角色是前面三类的总闸门。我习惯用一个楼盘的类比数据库是一栋楼DDL 是画图纸、浇筑墙体DML 是往里搬家具、换家具DQL 是打开窗户看看屋里有什么DCL 是给每一层配门禁卡。门禁卡没配好前面三项再熟练也可能出事。1.2 从数据生命周期看四类的关系一张业务表从诞生到退役顺序永远是先DDL定结构再DML填数据日常靠DQL取数分析整个过程靠DCL圈定谁能操作。所以一个完整的入门训练不应该只练SELECT而是应该把四类串起来走一遍。本文后面反复用到一组模拟电商场景的表customers客户表orders订单主表order_items订单明细表典型的数据生命周期是这样的用 DDL 建出三张表定好主键、外键、索引和字段类型用 DML 插入客户、生成订单和明细模拟真实业务写入用 DQL 统计每个客户的订单金额、筛选最近 7 天数据、出报表用 DCL 创建一个只读账号给 BI 报表系统只授SELECT。1.3 一条 SQL 在数据库内部真实走过的路不管是哪一类 SQL提交给数据库后都要经历三件事解析语法检查、权限校验→优化决定走哪个索引、哪种连接方式→执行调用存储引擎读写数据。但三类语句在“权限校验”和“锁处理”上的差异非常大这是一个容易被忽视的点DDL 在 MySQL 里会触发隐式提交执行前的事务会直接提交掉DDL 本身也不在事务保护范围内一旦执行就收不回来DML 可以显式包在事务里执行后可以ROLLBACK反悔DQL 基本不产生写锁但一个特别慢的查询同样可能拖垮整个实例甚至因为长事务、长查询导致 undo log 版本堆积间接影响写入性能。理解了这层机制再看四大分类就不会只停留在“语法不同”的表面。2. DDL动手之前想清楚这个骨架往往要撑很多年2.1 建表不是随便写写完整拆解一段 CREATE TABLE先看一段完整的建表语句这是模拟订单主表的 DDLCREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 订单ID, order_no VARCHAR(32) NOT NULL COMMENT 订单编号, customer_id BIGINT UNSIGNED NOT NULL COMMENT 客户ID, status TINYINT NOT NULL DEFAULT 0 COMMENT 订单状态0待支付 1已支付 2已取消, total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 订单金额, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_customer_id (customer_id), CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT订单主表;这里每个细节都有讲究id BIGINT UNSIGNED AUTO_INCREMENT主键用BIGINT而不是INT是因为订单量一旦上来INT上限 21 亿看着够用但分库分表后全局 id 很容易撞顶。UNSIGNED让范围翻一倍AUTO_INCREMENT是 InnoDB 最省心的自增主键方案order_no VARCHAR(32)业务订单号单独建唯一索引。主键是物理层面的聚簇索引业务唯一键是逻辑标识两者分开避免订单号变更时连带主键变动total_amount DECIMAL(12,2)金额永远不要用FLOAT或DOUBLE。浮点数在二进制里是不精确的算对账、算营收时会出现 0.10.2 不等于 0.3 的问题DECIMAL才是为精确小数设计的created_at和updated_atDEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP可以让绝大多数写入场景不用手工维护时间字段ENGINEInnoDB除非你有特别理由线上 OLTP 表一律 InnoDB支持事务、行锁、崩溃恢复CHARSETutf8mb4不要再用utf8MySQL 的utf8最多存 3 字节遇到 emoji 或者某些生僻汉字会直接报错utf8mb4才是完整实现。2.2 约束不是越多越好要有取舍建表时会看到一整套约束PRIMARY KEY、UNIQUE、NOT NULL、DEFAULT、FOREIGN KEY。它们的目标都是防脏数据但在生产环境要区别对待主键和唯一键必须加这是数据完整性的底线NOT NULLDEFAULT强烈建议加。一个允许NULL的字段后续在查询条件、聚合函数、程序判断里处处要小心NULL的坑处理成本远高于建表时多写几个字外键约束FOREIGN KEY这个要权衡。理论上外键能保证引用完整性但高并发写入时外键会额外触发父表的锁检查影响性能和扩展性。很多互联网团队在核心链路会放弃物理外键改用应用层保证只在低频管理类表保留外键。用我的话说约束是用来保护数据的但也要为性能和运维让路别为了“看起来严谨”堆一堆用不上的约束。2.3 ALTER TABLE上线之后你一定会反复用到它没有哪张表上线后就不动了。字段要加、类型要改、索引要调整这些都是ALTER TABLE-- 新增字段并指定在 status 字段之后 ALTER TABLE orders ADD COLUMN payment_at DATETIME NULL COMMENT 支付时间 AFTER status; -- 修改字段类型或默认值 ALTER TABLE orders MODIFY COLUMN status TINYINT NOT NULL DEFAULT 1 COMMENT 订单状态; -- 重命名字段的同时改定义 ALTER TABLE orders CHANGE COLUMN total_amount total_price DECIMAL(12,2) NOT NULL DEFAULT 0.00; -- 删除字段 ALTER TABLE orders DROP COLUMN payment_at; -- 重命名表 ALTER TABLE orders RENAME TO shop_orders;这里必须提一个经验在 MySQL 里很多ALTER TABLE操作会触发全表重建并且在执行期间对表加排他锁。千万行的大表直接一条ALTER TABLE下去可能锁表几十分钟线上写入全部卡住。生产环境的大表结构变更要借助在线变更工具如gh-ost、pt-online-schema-change在低峰期执行切不可图省事直接怼线上。2.4 DROP、TRUNCATE 与 DELETE 的真正区别这一块是很多人迷糊的重灾区。它们都能让数据“消失”但底层的性质完全不同操作类别删什么能否回滚是否释放表空间是否保留表结构DELETEDML按条件删行可回滚事务内不释放保留TRUNCATEDDL清空所有行不可回滚释放保留DROPDDL表结构 全部数据不可回滚释放不保留文档里常见的表述是“TRUNCATE 是快速清空表”但真正重要的信息是它是 DDL执行即隐式提交没有后悔药。而DROP更彻底连结构都没了。开头那个事故就是把DELETE写成了DROP一步之差恢复成本天上地下。生产环境的兜底习惯任何删除类操作前先确认几件事——当前连的是哪个环境、备份是否可用、有没有先验证影响行数。宁可在测试环境多折腾十分钟也不要在大半夜被电话叫起来。2.5 拿什么验证 DDL 的结果执行完 DDL 别急着走用下面两条语句确认骨架是否符合预期-- 查看建表语句确认字段、约束、索引 SHOW CREATE TABLE orders; -- 通过数据字典确认表信息 SELECT TABLE_NAME, ENGINE, TABLE_ROWS, DATA_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA shopdb AND TABLE_NAME orders;SHOW CREATE TABLE是排查结构问题最好用的工具它会把最终生效的表结构原样输出任何字符集、约束、注释的异常都能一眼看出来。3. DML改数据之前先问自己一句“影响多少行”3.1 INSERT别只会单行插入DML 里的INSERT看似最简单实际在批量场景里很容易用错。常见的有三种形态-- 多行插入一次写多条 INSERT INTO orders (order_no, customer_id, status, total_amount) VALUES (ORD2025001, 1001, 1, 299.00), (ORD2025002, 1002, 0, 158.50); -- 从另一张表批量导入 INSERT INTO orders (order_no, customer_id, status, total_amount) SELECT order_no, customer_id, status, total_amount FROM temp_orders WHERE created_at 2025-01-01; -- 存在唯一键冲突时自动转为更新 INSERT INTO orders (order_no, customer_id, status, total_amount) VALUES (ORD2025001, 1001, 2, 299.00) ON DUPLICATE KEY UPDATE status VALUES(status);第三个写法在同步场景里非常实用它能把“先查再判断再插入或更新”三步压缩成一条语句减少了应用和数据库的交互次数。要注意的是 MySQL 8.0.20 之后VALUES()函数已标记为废弃推荐用别名写法INSERT INTO orders (order_no, customer_id, status, total_amount) VALUES (ORD2025001, 1001, 2, 299.00) AS new ON DUPLICATE KEY UPDATE status new.status;另外我建议大家养成好习惯INSERT语句永远写完整的字段列表不要省略字段名直接给值。表结构一变更省了字段名的语句轻则错位重则把数据写进错误的列。3.2 UPDATEWHERE 就是你的生命线UPDATE是最容易出“生产事故”的语句因为它的语法太简单了简单到让人放松警惕-- 一条正常的条件更新 UPDATE orders SET status 1 WHERE order_no ORD2025002; -- 一旦漏了 WHERE全部订单状态都会被改掉 UPDATE orders SET status 1;我的习惯是执行UPDATE前永远先跑一遍同条件的SELECT确认影响范围-- 先看条件到底命中哪些行 SELECT id, order_no, status FROM orders WHERE order_no ORD2025002; -- 确认无误再执行更新 UPDATE orders SET status 1 WHERE order_no ORD2025002;看起来多了一步实际上是在逼自己检查条件。还有一类常见问题是更新语句里的子查询条件与目标表存在关联比如按订单明细合计金额批量更新订单总价UPDATE orders o JOIN ( SELECT order_id, SUM(amount) AS total FROM order_items GROUP BY order_id ) t ON t.order_id o.id SET o.total_amount t.total;这种关联更新要特别留意JOIN出来的结果如果一对多字段会被覆盖成最后一条而不是你想要的聚合结果。所以先用临时表或子查询把明细归成一行再关联更新。3.3 DELETE删除是代价最贵的操作DELETE和UPDATE一样WHERE 是生命线DELETE FROM order_items WHERE order_id 1001;DELETE真正难缠的地方在于大批量删除。一次删几十万行会带来三个问题持有大量行锁、产生超大事务、主从复制延迟被拉高。生产环境的正确姿势是分批删除-- 每次只处理 1000 行循环执行直到影响行数为 0 DELETE FROM order_items WHERE order_id 1001 LIMIT 1000;每一次删除之间让事务提交掉锁就释放了主从也能喘口气。还要提一个方案层面的选择软删除 vs 物理删除。很多业务表并不真正执行DELETE而是加一个is_deleted TINYINT DEFAULT 0的标记字段查询时默认过滤。这样数据保留了审计轨迹也避免误删后无法恢复。代价是索引设计、统计查询都要多带一个条件。到底选哪种取决于业务对“可追溯性”的要求和团队有没有可靠的备份机制。3.4 事务让 DML 能反悔的保险丝DML 能放进事务这是它和 DDL 最本质的区别之一。最典型的转账场景START TRANSACTION; UPDATE account SET balance balance - 100 WHERE id 1; UPDATE account SET balance balance 100 WHERE id 2; COMMIT; -- 如果第二步失败可以执行 ROLLBACK让第一步的扣款也撤销在默认配置下MySQL 的AUTOCOMMIT是开启的意味着每条 DML 都会自动提交。写多语句的脚本时要显式START TRANSACTION否则中间某条失败前面的已经提交数据就处于半更新状态。与之相关的还有 ACID 特性原子性、一致性、隔离性、持久性。日常开发不用背概念但要理解事务的边界事务内出现异常ROLLBACK 能撤销的是同一个事务里的 DML不包括 DDL。另一个容易被忽略的是并发条件下的“安全更新”。比如扣库存很多人的写法是UPDATE stock SET quantity quantity - 1 WHERE product_id 10;这条语句在数据库层面是原子的问题不大。但如果是“先查余额再判断够不够再扣款”的分步应用代码就会出现并发超扣。真正稳妥的方案是把判断放进 SQLUPDATE account SET balance balance - 100 WHERE id 1 AND balance 100;通过受影响行数判断是否更新成功比“先查后改”靠谱得多。3.5 读懂 DML 的执行反馈执行完UPDATE或DELETE客户端通常会返回类似信息Query OK, 5 rows affected (0.01 sec)这里的rows affected在不同数据库里语义略有差异在 MySQL 默认配置下UPDATE返回的是“被修改的行数”比如把某个值从 1 改成 1可能显示 0 行受影响而有些数据库返回的是“被扫描命中的行数”。判断更新是否命中目标要结合这个数字理解别把“0 rows affected”当成“没数据”有时候它只是值没变化。4. DQL80% 的日常工作量在这里80% 的慢查询也在这里4.1 书写顺序和执行顺序完全是两回事SELECT是四类里面语法最丰富、也是最容易写乱的。一个完整的查询SELECT c.name, COUNT(o.id) AS order_cnt, SUM(o.total_amount) AS total_amount FROM customers c LEFT JOIN orders o ON o.customer_id c.id WHERE o.created_at DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY c.id, c.name HAVING COUNT(o.id) 0 ORDER BY total_amount DESC LIMIT 10;SQL 的书写顺序是SELECT → FROM → JOIN → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT但数据库执行顺序是另一套FROM/JOIN确定数据源把多表连接起来WHERE过滤原始行GROUP BY分组HAVING过滤分组后的结果SELECT投影出需要的列DISTINCT去重ORDER BY排序LIMIT限制行数这个顺序决定了两个实际约束WHERE 阶段不能用 SELECT 里的别名因为别名在第五步才生成而ORDER BY 可以用别名因为排序在投影之后。很多人写SELECT total_amount * 0.9 AS discounted FROM orders WHERE discounted 100;直接报错原因就在这里改法是把表达式再写一遍或者包一层子查询。4.2 JOIN 的本质与 ON 和 WHERE 的微妙区别JOIN是 DQL 里最核心的能力本质就是把两张表按关联条件“拼”成一张宽表。以模拟电商场景为例想查每个客户的订单数SELECT c.name, COUNT(o.id) AS order_cnt FROM customers c LEFT JOIN orders o ON o.customer_id c.id GROUP BY c.id, c.name;这里用LEFT JOIN是为了把“没有下过单的客户”也保留下来右表没有匹配时o.id是NULLCOUNT(o.id)会正确算成 0。但如果把订单表的条件放到 WHERE 里效果就完全不同了SELECT c.name, COUNT(o.id) AS order_cnt FROM customers c LEFT JOIN orders o ON o.customer_id c.id WHERE o.total_amount 100 GROUP BY c.id, c.name;在LEFT JOIN里右表条件写在WHERE中会把右表为NULL的行全过滤掉结果等价于INNER JOIN那些没下单的客户“又消失”了。想让左表的行无条件保留右表的过滤条件要放进ON想只保留两边都匹配的数据才放到WHERE。这个细节是面试和实战里反复踩的坑。JOIN还有一个容易被忽略的敌人笛卡尔积。忘记写ON条件或者条件写得过宽两表数据量相乘查询可能直接跑死。我曾经见过一个同事把 10 万行的表和一个 20 万行的表CROSS JOIN单次查询生成 200 亿行中间结果直接拖垮整个实例。写JOIN之前先想清楚关联条件有没有写全。4.3 聚合与去重COUNT 的三种写法差异聚合函数是 DQL 的统计利器COUNT又是里面最常用、也最容易被写错的COUNT(*)统计行数包含NULLCOUNT(1)统计行数行为和COUNT(*)近似在 MySQL 里两者性能基本相当COUNT(字段)统计该字段非 NULL的行数如果字段全为NULL结果是 0COUNT(DISTINCT 字段)按字段去重后统计。很多人统计“客户数”时用COUNT(phone)恰好phone字段允许NULL结果漏掉了一批人。统计行数优先用COUNT(*)统计某个字段有值的情况才用COUNT(字段)这是最稳妥的习惯。聚合配合GROUP BY时还要注意 SQL 模式下的“隐式分组”问题SELECT里出现的非聚合列如果不在GROUP BY里在某些数据库中会直接报错在 MySQL 宽松模式下则可能随机取值。写分组查询时原则是SELECT 的每个列要么在 GROUP BY 里要么被聚合函数包住。4.4 子查询IN 与 EXISTS 的选择子查询解决了“一张表的查询条件依赖另一张表”的问题。比如查所有属于黄金会员的客户的订单SELECT * FROM orders WHERE customer_id IN ( SELECT id FROM customers WHERE level gold );关于IN和EXISTS流传最广的说法是“外层表大、内层表小用 IN外层表小、内层表大用 EXISTS”。在现在的优化器面前这个经验已经不完全准确很多数据库会自动把IN重写成EXISTS或转成JOIN。相比纠结这两者的性能差异更值得关注的是子查询结果集过大时IN后面的列表会非常长解析和传输都有开销EXISTS是“存在即返回”适合判断“有没有”逻辑上更接近业务语义能改写为JOIN的查询通常比子查询更容易被优化器利用索引。写成JOIN虽然更“绕”但在复杂场景下反而更可控SELECT o.* FROM orders o JOIN customers c ON o.customer_id c.id WHERE c.level gold;4.5 慢查询来了第一步不是加索引而是看 EXPLAINDQL 写得再花哨跑不动也是白搭。任何一条线上慢查询我都建议先在测试库复现然后执行EXPLAIN SELECT c.name, COUNT(o.id) AS order_cnt, SUM(o.total_amount) AS total_amount FROM customers c LEFT JOIN orders o ON o.customer_id c.id WHERE o.created_at 2025-06-01 GROUP BY c.id, c.name;关注几个关键字段type最直观的访问方式。从好到差大体是const、ref、range、index、ALL。看到ALL全表扫描就要警惕key实际用的索引名。为NULL说明没走索引rows预估扫描的行数数量级直接反映查询成本Extra看到Using filesort代表排序没走索引看到Using temporary代表用了临时表这两者在百万级数据下都可能是性能瓶颈。优化的大方向是“少扫描”能加索引的加索引能缩小结果集就缩小结果集能走覆盖索引就别回表。但执行计划只是方向最终要以线上实际效果为准尤其是数据量变化后执行计划可能跟着变。5. DCL离业务代码最远离生产事故最近5.1 用户与登录控制限制范围比设置密码更重要DCL 的第一层是管理用户。最基础的创建用户CREATE USER app_rw% IDENTIFIED BY 密码;这里的app_rw%指的是“允许从任意主机登录”。从便捷性看这没问题但从安全角度%意味着这个账号可以从任何一个 IP 尝试登录攻击面被拉到了最大。生产环境更稳的做法是把%替换成应用服务器所在的网段CREATE USER app_rw192.168.10.% IDENTIFIED BY 密码;一段简单的网段限制能挡掉大量外部风险。5.2 GRANT 与 REVOKE权限粒度从大到小从粗到细创建完用户下一步就是授权。权限可以是全局的、库级的也可以是表级的-- 给应用账号授某个库的增删改查 GRANT SELECT, INSERT, UPDATE, DELETE ON shopdb.* TO app_rw%; -- 给 BI 账号只授一张表的只读权限 GRANT SELECT ON shopdb.orders TO bi_reader%; -- 给 DBA 账号授该库的所有权限 GRANT ALL PRIVILEGES ON shopdb.* TO dba%;GRANT的粒度越细事后能控制的范围就越精确。我见过不少项目图省事给应用账号直接GRANT ALL PRIVILEGES ON *.*一旦应用被注入了恶意 SQL攻击者相当于拿到了整个实例的钥匙。权限给错了收回用REVOKEREVOKE DELETE ON shopdb.* FROM app_rw%;查看某个账号当前有什么权限SHOW GRANTS FOR bi_reader%;这条命令应该在每次权限变更后都执行一遍确认没有多授、漏授。提示GRANT、REVOKE这类语句本身也是 DCL 的核心成员它们很“轻”但决定了其他三类语句能不能执行。5.3 角色权限的“打包复用”如果团队里有十几个只读分析账号一个个授权既费时又容易漏。角色的作用就是把一组权限打包再批量授予用户-- 创建一个只读角色 CREATE ROLE readonly_role; -- 给角色授库级只读权限 GRANT SELECT ON shopdb.* TO readonly_role; -- 把角色授予多个用户 GRANT readonly_role TO bi_reader%; GRANT readonly_role TO data_viewer%;角色的最大价值是后续调整权限时只需要改角色本身所有挂在该角色下的用户自动跟着变。比如要给所有只读用户加上对某张新表的访问权只要GRANT SELECT ON shopdb.new_table TO readonly_role一次就完成不用挨个用户处理。5.4 最小权限原则的一次落地实践DCL 最值得贯彻的原则就是最小权限每个账号只拿完成本职工作所需的最少权限。一个可以参照的实践方案是账号角色需要的权限建议授权范围应用读写账号SELECT、INSERT、UPDATE、DELETE限定业务库不授 DDLBI / 报表账号SELECT只授需要的表或库运维 / DBA 账号ALL PRIVILEGES专人专用绑定网段临时排查账号SELECT、SHOW用完即删我见过最典型的问题不是“权限不够”而是“权限给得太宽”。应用账号拿到了 DDL 权限意味着一旦代码里出现漏洞攻击者可以建表删表BI 账号拿到了 DELETE意味着一次误操作能把源数据干掉。权限收紧的过程虽然是“反效率”的但它是数据库安全里性价比最高的投入。最后补充一点带团队时的实在体会我带新人做数据库基础训练时第一周不会让他们刷一堆复杂的查询题而是要求把一张业务表完完整整走一遍流程用 DDL 建出表结构用 DML 写入测试数据用 DQL 算几个业务指标再用 DCL 创建一个只读账号模拟授权。这个过程走下来对四类 SQL 的印象比看十遍文档都深。还有一个小习惯我坚持了很多年不管操作的是测试环境还是生产环境动手之前先看一眼当前会话连着哪个库、用的是哪个账号。很多误操作发生在深夜赶工时人困马乏连接对象搞错数据库实例一条UPDATE下去悔都来不及。我会默认先在事务里执行、先跑一遍SELECT验证影响范围确认无误后再提交。SQL 四类语法本身不难记真正值钱的是对每一类操作边界的敬畏。DDL 动的是骨架DML 改的是血肉DQL 看的是全貌DCL 守的是大门。把这四条线刻在脑子里绝大多数数据库事故都能提前躲开。
上周隔壁组出了个小事故一个上线两年的老系统运营想清理一张日志表的过期数据结果直连数据库的同事把DELETE写成了DROP TABLE回车一敲整张表连结构带数据全没了。等发现的时候只能靠备份恢复前后折腾了大半夜线上业务也受了影响。细问之下才知道这位同事学 SQL 完全是零散着学的——今天会写一句SELECT查个数明天照着文档抄一句UPDATE从来没把 SQL 这门“语言”在认知上拆开看过。其实 SQL 远不止“查询”和“增删改查”这么笼统。按它操作的对象和行为业内习惯把它分成四类DDL管结构、DML管数据、DQL管查询、DCL管权限。这个分类不只是考试题里的一问而是你写任何一条 SQL 之前脑子里必须形成的一条边界线。这篇文章我会用一张订单业务中常见的几张表做例子把四类语法完整过一遍顺带讲讲每一类里那些文档里很少写、但线上一定会踩到的坑。适合刚入门数据库的开发者也适合做了好几年 CRUD 想系统梳理一遍的同学。1. 先弄清底层逻辑SQL 不是一门语言而是四把不同的钥匙1.1 分类依据你操作的到底是“结构”还是“数据”很多人背得下“DDL 是数据定义语言”但问他“为什么要把 SQL 拆成这四类”往往答不上来。拆分类目不是组织拍脑袋定的而是因为这几类语句进了数据库之后走的路径、拿的锁、能不能回滚完全不是一回事。DDLData Definition Language管理的是“数据库长什么样”建表、改表、删表、建索引、建视图动的是结构DMLData Manipulation Language管理的是“表里装了什么”插入、修改、删除行数据动的是内容**DQLData Query Language**管理的是“怎么把数据读出来”SELECT无论写多复杂都不改变任何一行数据**DCLData Control Language**管理的是“谁能碰这些数据”用户、权限、角色是前面三类的总闸门。我习惯用一个楼盘的类比数据库是一栋楼DDL 是画图纸、浇筑墙体DML 是往里搬家具、换家具DQL 是打开窗户看看屋里有什么DCL 是给每一层配门禁卡。门禁卡没配好前面三项再熟练也可能出事。1.2 从数据生命周期看四类的关系一张业务表从诞生到退役顺序永远是先DDL定结构再DML填数据日常靠DQL取数分析整个过程靠DCL圈定谁能操作。所以一个完整的入门训练不应该只练SELECT而是应该把四类串起来走一遍。本文后面反复用到一组模拟电商场景的表customers客户表orders订单主表order_items订单明细表典型的数据生命周期是这样的用 DDL 建出三张表定好主键、外键、索引和字段类型用 DML 插入客户、生成订单和明细模拟真实业务写入用 DQL 统计每个客户的订单金额、筛选最近 7 天数据、出报表用 DCL 创建一个只读账号给 BI 报表系统只授SELECT。1.3 一条 SQL 在数据库内部真实走过的路不管是哪一类 SQL提交给数据库后都要经历三件事解析语法检查、权限校验→优化决定走哪个索引、哪种连接方式→执行调用存储引擎读写数据。但三类语句在“权限校验”和“锁处理”上的差异非常大这是一个容易被忽视的点DDL 在 MySQL 里会触发隐式提交执行前的事务会直接提交掉DDL 本身也不在事务保护范围内一旦执行就收不回来DML 可以显式包在事务里执行后可以ROLLBACK反悔DQL 基本不产生写锁但一个特别慢的查询同样可能拖垮整个实例甚至因为长事务、长查询导致 undo log 版本堆积间接影响写入性能。理解了这层机制再看四大分类就不会只停留在“语法不同”的表面。2. DDL动手之前想清楚这个骨架往往要撑很多年2.1 建表不是随便写写完整拆解一段 CREATE TABLE先看一段完整的建表语句这是模拟订单主表的 DDLCREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 订单ID, order_no VARCHAR(32) NOT NULL COMMENT 订单编号, customer_id BIGINT UNSIGNED NOT NULL COMMENT 客户ID, status TINYINT NOT NULL DEFAULT 0 COMMENT 订单状态0待支付 1已支付 2已取消, total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 订单金额, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_customer_id (customer_id), CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT订单主表;这里每个细节都有讲究id BIGINT UNSIGNED AUTO_INCREMENT主键用BIGINT而不是INT是因为订单量一旦上来INT上限 21 亿看着够用但分库分表后全局 id 很容易撞顶。UNSIGNED让范围翻一倍AUTO_INCREMENT是 InnoDB 最省心的自增主键方案order_no VARCHAR(32)业务订单号单独建唯一索引。主键是物理层面的聚簇索引业务唯一键是逻辑标识两者分开避免订单号变更时连带主键变动total_amount DECIMAL(12,2)金额永远不要用FLOAT或DOUBLE。浮点数在二进制里是不精确的算对账、算营收时会出现 0.10.2 不等于 0.3 的问题DECIMAL才是为精确小数设计的created_at和updated_atDEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP可以让绝大多数写入场景不用手工维护时间字段ENGINEInnoDB除非你有特别理由线上 OLTP 表一律 InnoDB支持事务、行锁、崩溃恢复CHARSETutf8mb4不要再用utf8MySQL 的utf8最多存 3 字节遇到 emoji 或者某些生僻汉字会直接报错utf8mb4才是完整实现。2.2 约束不是越多越好要有取舍建表时会看到一整套约束PRIMARY KEY、UNIQUE、NOT NULL、DEFAULT、FOREIGN KEY。它们的目标都是防脏数据但在生产环境要区别对待主键和唯一键必须加这是数据完整性的底线NOT NULLDEFAULT强烈建议加。一个允许NULL的字段后续在查询条件、聚合函数、程序判断里处处要小心NULL的坑处理成本远高于建表时多写几个字外键约束FOREIGN KEY这个要权衡。理论上外键能保证引用完整性但高并发写入时外键会额外触发父表的锁检查影响性能和扩展性。很多互联网团队在核心链路会放弃物理外键改用应用层保证只在低频管理类表保留外键。用我的话说约束是用来保护数据的但也要为性能和运维让路别为了“看起来严谨”堆一堆用不上的约束。2.3 ALTER TABLE上线之后你一定会反复用到它没有哪张表上线后就不动了。字段要加、类型要改、索引要调整这些都是ALTER TABLE-- 新增字段并指定在 status 字段之后 ALTER TABLE orders ADD COLUMN payment_at DATETIME NULL COMMENT 支付时间 AFTER status; -- 修改字段类型或默认值 ALTER TABLE orders MODIFY COLUMN status TINYINT NOT NULL DEFAULT 1 COMMENT 订单状态; -- 重命名字段的同时改定义 ALTER TABLE orders CHANGE COLUMN total_amount total_price DECIMAL(12,2) NOT NULL DEFAULT 0.00; -- 删除字段 ALTER TABLE orders DROP COLUMN payment_at; -- 重命名表 ALTER TABLE orders RENAME TO shop_orders;这里必须提一个经验在 MySQL 里很多ALTER TABLE操作会触发全表重建并且在执行期间对表加排他锁。千万行的大表直接一条ALTER TABLE下去可能锁表几十分钟线上写入全部卡住。生产环境的大表结构变更要借助在线变更工具如gh-ost、pt-online-schema-change在低峰期执行切不可图省事直接怼线上。2.4 DROP、TRUNCATE 与 DELETE 的真正区别这一块是很多人迷糊的重灾区。它们都能让数据“消失”但底层的性质完全不同操作类别删什么能否回滚是否释放表空间是否保留表结构DELETEDML按条件删行可回滚事务内不释放保留TRUNCATEDDL清空所有行不可回滚释放保留DROPDDL表结构 全部数据不可回滚释放不保留文档里常见的表述是“TRUNCATE 是快速清空表”但真正重要的信息是它是 DDL执行即隐式提交没有后悔药。而DROP更彻底连结构都没了。开头那个事故就是把DELETE写成了DROP一步之差恢复成本天上地下。生产环境的兜底习惯任何删除类操作前先确认几件事——当前连的是哪个环境、备份是否可用、有没有先验证影响行数。宁可在测试环境多折腾十分钟也不要在大半夜被电话叫起来。2.5 拿什么验证 DDL 的结果执行完 DDL 别急着走用下面两条语句确认骨架是否符合预期-- 查看建表语句确认字段、约束、索引 SHOW CREATE TABLE orders; -- 通过数据字典确认表信息 SELECT TABLE_NAME, ENGINE, TABLE_ROWS, DATA_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA shopdb AND TABLE_NAME orders;SHOW CREATE TABLE是排查结构问题最好用的工具它会把最终生效的表结构原样输出任何字符集、约束、注释的异常都能一眼看出来。3. DML改数据之前先问自己一句“影响多少行”3.1 INSERT别只会单行插入DML 里的INSERT看似最简单实际在批量场景里很容易用错。常见的有三种形态-- 多行插入一次写多条 INSERT INTO orders (order_no, customer_id, status, total_amount) VALUES (ORD2025001, 1001, 1, 299.00), (ORD2025002, 1002, 0, 158.50); -- 从另一张表批量导入 INSERT INTO orders (order_no, customer_id, status, total_amount) SELECT order_no, customer_id, status, total_amount FROM temp_orders WHERE created_at 2025-01-01; -- 存在唯一键冲突时自动转为更新 INSERT INTO orders (order_no, customer_id, status, total_amount) VALUES (ORD2025001, 1001, 2, 299.00) ON DUPLICATE KEY UPDATE status VALUES(status);第三个写法在同步场景里非常实用它能把“先查再判断再插入或更新”三步压缩成一条语句减少了应用和数据库的交互次数。要注意的是 MySQL 8.0.20 之后VALUES()函数已标记为废弃推荐用别名写法INSERT INTO orders (order_no, customer_id, status, total_amount) VALUES (ORD2025001, 1001, 2, 299.00) AS new ON DUPLICATE KEY UPDATE status new.status;另外我建议大家养成好习惯INSERT语句永远写完整的字段列表不要省略字段名直接给值。表结构一变更省了字段名的语句轻则错位重则把数据写进错误的列。3.2 UPDATEWHERE 就是你的生命线UPDATE是最容易出“生产事故”的语句因为它的语法太简单了简单到让人放松警惕-- 一条正常的条件更新 UPDATE orders SET status 1 WHERE order_no ORD2025002; -- 一旦漏了 WHERE全部订单状态都会被改掉 UPDATE orders SET status 1;我的习惯是执行UPDATE前永远先跑一遍同条件的SELECT确认影响范围-- 先看条件到底命中哪些行 SELECT id, order_no, status FROM orders WHERE order_no ORD2025002; -- 确认无误再执行更新 UPDATE orders SET status 1 WHERE order_no ORD2025002;看起来多了一步实际上是在逼自己检查条件。还有一类常见问题是更新语句里的子查询条件与目标表存在关联比如按订单明细合计金额批量更新订单总价UPDATE orders o JOIN ( SELECT order_id, SUM(amount) AS total FROM order_items GROUP BY order_id ) t ON t.order_id o.id SET o.total_amount t.total;这种关联更新要特别留意JOIN出来的结果如果一对多字段会被覆盖成最后一条而不是你想要的聚合结果。所以先用临时表或子查询把明细归成一行再关联更新。3.3 DELETE删除是代价最贵的操作DELETE和UPDATE一样WHERE 是生命线DELETE FROM order_items WHERE order_id 1001;DELETE真正难缠的地方在于大批量删除。一次删几十万行会带来三个问题持有大量行锁、产生超大事务、主从复制延迟被拉高。生产环境的正确姿势是分批删除-- 每次只处理 1000 行循环执行直到影响行数为 0 DELETE FROM order_items WHERE order_id 1001 LIMIT 1000;每一次删除之间让事务提交掉锁就释放了主从也能喘口气。还要提一个方案层面的选择软删除 vs 物理删除。很多业务表并不真正执行DELETE而是加一个is_deleted TINYINT DEFAULT 0的标记字段查询时默认过滤。这样数据保留了审计轨迹也避免误删后无法恢复。代价是索引设计、统计查询都要多带一个条件。到底选哪种取决于业务对“可追溯性”的要求和团队有没有可靠的备份机制。3.4 事务让 DML 能反悔的保险丝DML 能放进事务这是它和 DDL 最本质的区别之一。最典型的转账场景START TRANSACTION; UPDATE account SET balance balance - 100 WHERE id 1; UPDATE account SET balance balance 100 WHERE id 2; COMMIT; -- 如果第二步失败可以执行 ROLLBACK让第一步的扣款也撤销在默认配置下MySQL 的AUTOCOMMIT是开启的意味着每条 DML 都会自动提交。写多语句的脚本时要显式START TRANSACTION否则中间某条失败前面的已经提交数据就处于半更新状态。与之相关的还有 ACID 特性原子性、一致性、隔离性、持久性。日常开发不用背概念但要理解事务的边界事务内出现异常ROLLBACK 能撤销的是同一个事务里的 DML不包括 DDL。另一个容易被忽略的是并发条件下的“安全更新”。比如扣库存很多人的写法是UPDATE stock SET quantity quantity - 1 WHERE product_id 10;这条语句在数据库层面是原子的问题不大。但如果是“先查余额再判断够不够再扣款”的分步应用代码就会出现并发超扣。真正稳妥的方案是把判断放进 SQLUPDATE account SET balance balance - 100 WHERE id 1 AND balance 100;通过受影响行数判断是否更新成功比“先查后改”靠谱得多。3.5 读懂 DML 的执行反馈执行完UPDATE或DELETE客户端通常会返回类似信息Query OK, 5 rows affected (0.01 sec)这里的rows affected在不同数据库里语义略有差异在 MySQL 默认配置下UPDATE返回的是“被修改的行数”比如把某个值从 1 改成 1可能显示 0 行受影响而有些数据库返回的是“被扫描命中的行数”。判断更新是否命中目标要结合这个数字理解别把“0 rows affected”当成“没数据”有时候它只是值没变化。4. DQL80% 的日常工作量在这里80% 的慢查询也在这里4.1 书写顺序和执行顺序完全是两回事SELECT是四类里面语法最丰富、也是最容易写乱的。一个完整的查询SELECT c.name, COUNT(o.id) AS order_cnt, SUM(o.total_amount) AS total_amount FROM customers c LEFT JOIN orders o ON o.customer_id c.id WHERE o.created_at DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY c.id, c.name HAVING COUNT(o.id) 0 ORDER BY total_amount DESC LIMIT 10;SQL 的书写顺序是SELECT → FROM → JOIN → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT但数据库执行顺序是另一套FROM/JOIN确定数据源把多表连接起来WHERE过滤原始行GROUP BY分组HAVING过滤分组后的结果SELECT投影出需要的列DISTINCT去重ORDER BY排序LIMIT限制行数这个顺序决定了两个实际约束WHERE 阶段不能用 SELECT 里的别名因为别名在第五步才生成而ORDER BY 可以用别名因为排序在投影之后。很多人写SELECT total_amount * 0.9 AS discounted FROM orders WHERE discounted 100;直接报错原因就在这里改法是把表达式再写一遍或者包一层子查询。4.2 JOIN 的本质与 ON 和 WHERE 的微妙区别JOIN是 DQL 里最核心的能力本质就是把两张表按关联条件“拼”成一张宽表。以模拟电商场景为例想查每个客户的订单数SELECT c.name, COUNT(o.id) AS order_cnt FROM customers c LEFT JOIN orders o ON o.customer_id c.id GROUP BY c.id, c.name;这里用LEFT JOIN是为了把“没有下过单的客户”也保留下来右表没有匹配时o.id是NULLCOUNT(o.id)会正确算成 0。但如果把订单表的条件放到 WHERE 里效果就完全不同了SELECT c.name, COUNT(o.id) AS order_cnt FROM customers c LEFT JOIN orders o ON o.customer_id c.id WHERE o.total_amount 100 GROUP BY c.id, c.name;在LEFT JOIN里右表条件写在WHERE中会把右表为NULL的行全过滤掉结果等价于INNER JOIN那些没下单的客户“又消失”了。想让左表的行无条件保留右表的过滤条件要放进ON想只保留两边都匹配的数据才放到WHERE。这个细节是面试和实战里反复踩的坑。JOIN还有一个容易被忽略的敌人笛卡尔积。忘记写ON条件或者条件写得过宽两表数据量相乘查询可能直接跑死。我曾经见过一个同事把 10 万行的表和一个 20 万行的表CROSS JOIN单次查询生成 200 亿行中间结果直接拖垮整个实例。写JOIN之前先想清楚关联条件有没有写全。4.3 聚合与去重COUNT 的三种写法差异聚合函数是 DQL 的统计利器COUNT又是里面最常用、也最容易被写错的COUNT(*)统计行数包含NULLCOUNT(1)统计行数行为和COUNT(*)近似在 MySQL 里两者性能基本相当COUNT(字段)统计该字段非 NULL的行数如果字段全为NULL结果是 0COUNT(DISTINCT 字段)按字段去重后统计。很多人统计“客户数”时用COUNT(phone)恰好phone字段允许NULL结果漏掉了一批人。统计行数优先用COUNT(*)统计某个字段有值的情况才用COUNT(字段)这是最稳妥的习惯。聚合配合GROUP BY时还要注意 SQL 模式下的“隐式分组”问题SELECT里出现的非聚合列如果不在GROUP BY里在某些数据库中会直接报错在 MySQL 宽松模式下则可能随机取值。写分组查询时原则是SELECT 的每个列要么在 GROUP BY 里要么被聚合函数包住。4.4 子查询IN 与 EXISTS 的选择子查询解决了“一张表的查询条件依赖另一张表”的问题。比如查所有属于黄金会员的客户的订单SELECT * FROM orders WHERE customer_id IN ( SELECT id FROM customers WHERE level gold );关于IN和EXISTS流传最广的说法是“外层表大、内层表小用 IN外层表小、内层表大用 EXISTS”。在现在的优化器面前这个经验已经不完全准确很多数据库会自动把IN重写成EXISTS或转成JOIN。相比纠结这两者的性能差异更值得关注的是子查询结果集过大时IN后面的列表会非常长解析和传输都有开销EXISTS是“存在即返回”适合判断“有没有”逻辑上更接近业务语义能改写为JOIN的查询通常比子查询更容易被优化器利用索引。写成JOIN虽然更“绕”但在复杂场景下反而更可控SELECT o.* FROM orders o JOIN customers c ON o.customer_id c.id WHERE c.level gold;4.5 慢查询来了第一步不是加索引而是看 EXPLAINDQL 写得再花哨跑不动也是白搭。任何一条线上慢查询我都建议先在测试库复现然后执行EXPLAIN SELECT c.name, COUNT(o.id) AS order_cnt, SUM(o.total_amount) AS total_amount FROM customers c LEFT JOIN orders o ON o.customer_id c.id WHERE o.created_at 2025-06-01 GROUP BY c.id, c.name;关注几个关键字段type最直观的访问方式。从好到差大体是const、ref、range、index、ALL。看到ALL全表扫描就要警惕key实际用的索引名。为NULL说明没走索引rows预估扫描的行数数量级直接反映查询成本Extra看到Using filesort代表排序没走索引看到Using temporary代表用了临时表这两者在百万级数据下都可能是性能瓶颈。优化的大方向是“少扫描”能加索引的加索引能缩小结果集就缩小结果集能走覆盖索引就别回表。但执行计划只是方向最终要以线上实际效果为准尤其是数据量变化后执行计划可能跟着变。5. DCL离业务代码最远离生产事故最近5.1 用户与登录控制限制范围比设置密码更重要DCL 的第一层是管理用户。最基础的创建用户CREATE USER app_rw% IDENTIFIED BY 密码;这里的app_rw%指的是“允许从任意主机登录”。从便捷性看这没问题但从安全角度%意味着这个账号可以从任何一个 IP 尝试登录攻击面被拉到了最大。生产环境更稳的做法是把%替换成应用服务器所在的网段CREATE USER app_rw192.168.10.% IDENTIFIED BY 密码;一段简单的网段限制能挡掉大量外部风险。5.2 GRANT 与 REVOKE权限粒度从大到小从粗到细创建完用户下一步就是授权。权限可以是全局的、库级的也可以是表级的-- 给应用账号授某个库的增删改查 GRANT SELECT, INSERT, UPDATE, DELETE ON shopdb.* TO app_rw%; -- 给 BI 账号只授一张表的只读权限 GRANT SELECT ON shopdb.orders TO bi_reader%; -- 给 DBA 账号授该库的所有权限 GRANT ALL PRIVILEGES ON shopdb.* TO dba%;GRANT的粒度越细事后能控制的范围就越精确。我见过不少项目图省事给应用账号直接GRANT ALL PRIVILEGES ON *.*一旦应用被注入了恶意 SQL攻击者相当于拿到了整个实例的钥匙。权限给错了收回用REVOKEREVOKE DELETE ON shopdb.* FROM app_rw%;查看某个账号当前有什么权限SHOW GRANTS FOR bi_reader%;这条命令应该在每次权限变更后都执行一遍确认没有多授、漏授。提示GRANT、REVOKE这类语句本身也是 DCL 的核心成员它们很“轻”但决定了其他三类语句能不能执行。5.3 角色权限的“打包复用”如果团队里有十几个只读分析账号一个个授权既费时又容易漏。角色的作用就是把一组权限打包再批量授予用户-- 创建一个只读角色 CREATE ROLE readonly_role; -- 给角色授库级只读权限 GRANT SELECT ON shopdb.* TO readonly_role; -- 把角色授予多个用户 GRANT readonly_role TO bi_reader%; GRANT readonly_role TO data_viewer%;角色的最大价值是后续调整权限时只需要改角色本身所有挂在该角色下的用户自动跟着变。比如要给所有只读用户加上对某张新表的访问权只要GRANT SELECT ON shopdb.new_table TO readonly_role一次就完成不用挨个用户处理。5.4 最小权限原则的一次落地实践DCL 最值得贯彻的原则就是最小权限每个账号只拿完成本职工作所需的最少权限。一个可以参照的实践方案是账号角色需要的权限建议授权范围应用读写账号SELECT、INSERT、UPDATE、DELETE限定业务库不授 DDLBI / 报表账号SELECT只授需要的表或库运维 / DBA 账号ALL PRIVILEGES专人专用绑定网段临时排查账号SELECT、SHOW用完即删我见过最典型的问题不是“权限不够”而是“权限给得太宽”。应用账号拿到了 DDL 权限意味着一旦代码里出现漏洞攻击者可以建表删表BI 账号拿到了 DELETE意味着一次误操作能把源数据干掉。权限收紧的过程虽然是“反效率”的但它是数据库安全里性价比最高的投入。最后补充一点带团队时的实在体会我带新人做数据库基础训练时第一周不会让他们刷一堆复杂的查询题而是要求把一张业务表完完整整走一遍流程用 DDL 建出表结构用 DML 写入测试数据用 DQL 算几个业务指标再用 DCL 创建一个只读账号模拟授权。这个过程走下来对四类 SQL 的印象比看十遍文档都深。还有一个小习惯我坚持了很多年不管操作的是测试环境还是生产环境动手之前先看一眼当前会话连着哪个库、用的是哪个账号。很多误操作发生在深夜赶工时人困马乏连接对象搞错数据库实例一条UPDATE下去悔都来不及。我会默认先在事务里执行、先跑一遍SELECT验证影响范围确认无误后再提交。SQL 四类语法本身不难记真正值钱的是对每一类操作边界的敬畏。DDL 动的是骨架DML 改的是血肉DQL 看的是全貌DCL 守的是大门。把这四条线刻在脑子里绝大多数数据库事故都能提前躲开。
分析器开发实战：从文法推导到Python实现)
