1. 先搞清楚DML的管辖范围和DDL、DCL不是一回事很多零基础的朋友刚开始学MySQL最大的困惑不是语句本身难写而是搞不清这一堆命令到底谁管谁。建表的时候用CREATE TABLE授权的时候用GRANT往表里塞数据又不知道叫什么名字打开教程一看一会儿DDL一会儿DML一会儿DCL直接看晕。我这里用一句话给你定位DML全称是Data Manipulation Language数据操作语言管的是表里面“已经存在的数据怎么增、怎么删、怎么改、怎么查”。它不负责表的定义也不负责权限控制那些分别属于DDLData Definition Language数据定义语言和DCLData Control Language数据控制语言的范畴。1.1 一张表分清SQL的三大类为了让你心里有个整体坐标系先看这张对照表后面章节所有的内容都不会跑出这个框架类别全称管的范围典型语句DDLData Definition Language数据库、表、索引、视图的结构定义与修改CREATE、ALTER、DROP、TRUNCATEDMLData Manipulation Language表内部数据的增删改查INSERT、UPDATE、DELETE、SELECTDCLData Control Language用户权限、访问控制GRANT、REVOKE有人可能会问SELECT不是查询吗为什么也算DML这里有个知识点容易有争议。从严格的SQL标准来说SELECT属于DQLData Query Language数据查询语言但在MySQL官方文档和绝大多数入门教学体系里都把SELECT放进DML一起讲。原因很实际你写业务代码的时候90%以上的场景是把SELECT和INSERT、UPDATE、DELETE混在一起用的比如先查一下库存再决定要不要更新查完发现数据不对再删掉重来。把SELECT单独抽出去反而割裂了学习路径所以这套教程沿用MySQL的习惯四兄弟一起讲。1.2 为什么这一章对你最重要DDL说白了就是建房子建一次基本不动DCL是发钥匙只在分配权限时用。但DML是你每天都在敲的语句——注册用户往表里插一条记录改头像要更新一条记录注销账号要删除一条记录打开首页要查一堆记录。可以这么说DDL决定数据长什么样DML决定数据怎么活。你要是把DML学扎实了后面学索引优化、事务隔离、读写分离都是在DML这条主线上做文章。反过来DML不熟你连排查问题的资格都没有——报错你都看不懂是语法错误还是数据问题。我见过不少半路转行的朋友上来先啃索引原理什么B树、聚簇索引背得滚瓜烂熟结果让他写一条既改库存又记流水账的SQL手忙脚乱。原因就是DML的基本功不牢。所以这一章别求快跟着示例一条一条敲敲完用SELECT验证结果比光看不练强十倍。2. INSERT插入数据三种写法覆盖九成场景INSERT是DML里的“加法”负责往表里塞新数据。大部分刚入门的人只知道INSERT INTO 表名 VALUES (...)这一种写法但实际开发中这种写法恰恰是使用频率最低、最容易踩坑的。先建一张演示表后面所有示例都在这张表上操作你可以直接在本地MySQL里跟着敲CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, age INT DEFAULT 18, city VARCHAR(50), score DECIMAL(5,2) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这张表模拟了一个学生基础信息表id是自增主键age有默认值18score是带两位小数的成绩字段。下面三种写法你按场景选。2.1 全字段插入最直观但最脆弱的写法INSERT INTO student VALUES (1, 张三, 20, 北京, 89.5);这种写法不用指定字段名但要求VALUES里必须包含表结构里所有字段的值一个不多一个不少顺序还得和建表时完全一致。它的脆弱点在于只要表结构一变这条SQL就可能立刻失效。比如你后来往表中间加了一个字段那么所有按旧结构写的INSERT都会报列数不匹配再比如你依赖id自增却非要在VALUES里写死1下次插入就可能撞主键。按我以前带新人的经验这种写法最容易在测试环境“跑通了”到生产环境一执行就报错。因为测试表和生成表的字段经常不一样。所以它只适合你临时手工往表里塞几条数据验证一下写正式代码不要用它。2.2 指定字段插入正式开发的首选INSERT INTO student (name, age, city, score) VALUES (李四, 22, 上海, 92.0);这里只写了name、age、city、score四个字段id完全交给了自增机制。没提到的字段MySQL会按建表时定义的规则处理有DEFAULT就用默认值允许NULL就填NULLNOT NULL且没有默认值就会直接报错。这种写法有几个明显好处不依赖字段顺序VALUES里的值只和你括号里写的字段顺序对应。结构变更更友好新增一个可有可无的字段只要INSERT里没提它SQL照样能跑。语义清晰别人一看就知道你在给哪些列赋值代码可读性高很多。所以我在项目里对团队的要求很明确除了手动排查临时数据一律使用指定字段插入。2.3 批量插入一条语句搞定多行数据INSERT INTO student (name, age, city, score) VALUES (王五, 21, 广州, 85.0), (赵六, 23, 深圳, 78.5), (孙七, 20, 杭州, 90.0);一次性插入多行用逗号分隔这是批量导入数据最常用的手段。相比一条一条执行单行INSERT这种写法把多次网络往返合并成一次效率成倍提升。尤其在你用程序批量导入Excel、CSV数据时能把几千条数据分批次拼成这种多行VALUES几百条一批性能非常可观。还有两种变体也提一下。一种是INSERT INTO ... SET写法INSERT INTO student SET name周八, age19, city成都, score88.0;这种写法的好处是字段和值一一对应有点像UPDATE适合插入字段少的时候看坏处是不支持批量插入多行字段多起来语句会很冗长。另一种是插入的同时处理重复键冲突INSERT INTO student (id, name, age, city, score) VALUES (1, 张三, 20, 北京, 89.5) ON DUPLICATE KEY UPDATE score 89.5;这个语句专门用来处理“主键或唯一键冲突”的场景如果id1的记录不存在就正常插入如果已经存在就执行UPDATE后面的逻辑。日常业务里“有则更新、无则插入”的需求非常多比如统计流水、同步用户信息用这个语句省掉你先SELECT判断再决定INSERT还是UPDATE的啰嗦流程。2.4 插入数据时最容易撞的四个坑操作过程中有几个坑我建议你提前记下来第一中文乱码。插入中文数据后查出来是乱码九成是表的字符集不是utf8mb4或者你的客户端连接字符集不对。建表时统一用utf8mb4连接后先执行SET NAMES utf8mb4;基本能杜绝这个问题。第二自增列不要手动指定。除非你有明确业务原因要在指定id否则别在INSERT里写自增主键的值。让MySQL自己分配可以避免主键冲突也能保证id连续性不受干扰。第三NOT NULL字段漏填。比如student表的name字段是NOT NULLinsert时漏了nameMySQL直接报Field name doesnt have a default value。看到这个错别懵就是某字段既不能为NULL又没有默认值你还没给它赋值。第四字段值超长度。VARCHAR(50)你非要塞60个字符MySQL在严格模式下会直接报错在非严格模式下会截断。强烈建议开启严格模式宁可直接报错也不能让数据悄悄被截断。插入完了怎么确认别全靠猜一条SELECT就搞定SELECT * FROM student;看到数据一条条列出来说明INSERT成功这就算完成了第一次完整的数据闭环。3. UPDATE更新数据最危险的一条SQL没有之一要说DML里哪条语句最危险我毫不犹豫投UPDATE一票。DELETE至少你心里知道它是危险的写的时候会多留个心眼而UPDATE看着人畜无害经常一条下去把全表数据改得面目全非。3.1 带不带WHERE是两个世界先看基础语法UPDATE student SET score 90.0 WHERE id 1;这条语句把id等于1的学生的成绩改成了90.0。关键在于WHERE它限定了“只更新哪些行”。但是如果你把WHERE忘掉UPDATE student SET score 90.0;恭喜你全表所有学生的成绩都变成了90.0。没有WHERE条件的UPDATE就等于告诉MySQL“这张表里所有行都给我改成这个值”。大多数初学者第一次犯这个错都是在测试环境数据本身不重要顶多笑一下删掉重来。但我在工作中真见过因为没写WHERE直接UPDATE了线上所有用户余额的——几个小时后用户发现余额不对全公司排查最终靠备份恢复数据过程极其痛苦。所以每次写UPDATE养成先写WHERE再写SET的习惯哪怕你确定就是要更新全表也要先写个WHERE 11之类的条件让自己看见。3.2 多字段更新与计算更新UPDATE一次可以同时更新多个字段字段之间用逗号分隔UPDATE student SET age age 1, city 南京 WHERE name 张三;这条语句把张三的年龄加1岁城市改成南京。注意age age 1这种写法它表示“在当前age的基础上加1”这是非常常见的一类需求比如积分加1、库存减1、登录次数累加都可以直接用这种计算赋值。这里有一个并发环境下的经典要点如果直接用SET stock stock - 1在并发很高的情况下数据库的行锁会保证这个操作的原子性不会出现两个请求同时读到stock10然后都写回9的情况。反过来如果写成“先SELECT拿到stock程序里减1再UPDATE写回”并发高时容易丢失更新。能一步在SQL里做的计算就不要拆成两步在程序里做这是我从实战里得出的经验。UPDATE也可以配合子查询比如“把成绩低于平均分的学生城市改成‘待定’”UPDATE student SET city 待定 WHERE score (SELECT AVG(score) FROM student);这个例子告诉你UPDATE的WHERE条件不限于简单的等值判断它可以像SELECT一样使用各种运算符、子查询、函数。逻辑能力有多强UPDATE就能写多复杂。3.3 配合LIMIT限制更新范围MySQL的UPDATE还支持LIMIT子句这个特性在别的数据库里少见但在实际运维和数据处理时特别有用UPDATE student SET score 80.0 WHERE city 北京 LIMIT 3;这条语句会把北京的学生里找出前3条按物理存储顺序只更新这3条的成绩。这种写法在按批次修正数据时很好用比如你有一百万条脏数据要修复每次只更新1000条可以分批执行避免一次性锁太多行导致线上业务卡顿。注意LIMIT通常要配合ORDER BY才可控不然你根本不知道更新的是哪几条UPDATE student SET score 80.0 WHERE city 北京 ORDER BY id ASC LIMIT 3;这样就明确规定了“按id从小到大排序后取前3条”可预测、可复现。3.4 更新前先查询一条能保命的经验我个人的实操习惯是在做重要UPDATE之前永远先用SELECT预览一下将要影响的范围SELECT * FROM student WHERE city 北京 ORDER BY id ASC LIMIT 3;确认这条WHERE条件圈出来的记录确实是你想要的那些再执行对应的UPDATE。这一步多花十秒钟可能帮你省掉十个小时的恢复工作。另外提醒一句UPDATE执行的返回结果会显示“Query OK, 1 row affected”这个1就是受影响的行数。如果你明明知道条件里有几十条匹配结果却是0 row affected那就要回头检查WHERE条件是不是写错了或者数据本身和预期不一致。4. DELETE删除数据与TRUNCATE的差别别把路走窄了DELETE负责删数据但它比你想象中要“温柔”得多。很多新手以为DELETE就是彻底把数据抹掉了其实远不是这么回事。4.1 DELETE的基础用法WHERE依然是命根子DELETE FROM student WHERE id 5;这条语句删除id等于5的学生记录。和UPDATE一个道理不带WHERE的DELETE会把整个表的数据全部清空DELETE FROM student;清空之后表结构还在自增计数器也不会重置你下次再插入数据id会从上次的最大值继续往上走。这一点和TRUNCATE有本质差别下面单独说。从安全角度看DELETE比UPDATE更凶险因为删除之后要恢复只能靠备份或日志。所以我在教新人的时候反复强调执行DELETE之前你的流程里必须包含“备份”这一步。小表直接CREATE TABLE student_bak_20250101 AS SELECT * FROM student;大表就做逻辑备份或者导出到文件。有了备份再删心里才有底。4.2 DELETE与TRUNCATE一场经典的对比很多人知道TRUNCATE也能清空表但不知道两者有什么区别这里给一张对比表对比项DELETETRUNCATE是否可加WHERE可以按条件删除不可以只能全表清空是否记录行明细逐行删除记录每一行直接释放数据页不逐行记录是否重置自增id不重置重置是否支持事务回滚在事务内可以回滚执行后无法按行回滚执行速度慢数据量大时很慢快几乎瞬间完成是否触发删除触发器会触发不会触发举两个典型例子删除部分数据比如只删除成绩低于60分的学生必须用DELETEDELETE FROM student WHERE score 60;清空一张临时表、历史日志表并且希望自增id从1重新开始用TRUNCATE更合适TRUNCATE TABLE student;这条语句会快速把所有行删掉同时把自增计数器归零。如果表很大TRUNCATE比DELETE快好几个数量级原因是TRUNCATE直接释放整张表的数据页而DELETE是一条一条标记得删除。4.3 DROP和DELETE别混drop删的是表本身在入门阶段还有一个词经常和DELETE混在一起——DROP。这里明确一下DROP TABLE student;DROP删的是整张表包括表结构和数据表直接不存在了。DELETE删的是表中的行表本身还在。三者层级关系是DROP TRUNCATE DELETE。顺序千万别弄反也别在同学面前说出“我drop一条数据”这种外行话。4.4 删除数据前为什么建议先SELECT确认和UPDATE一样我强烈建议在DELETE前先用SELECT预览SELECT * FROM student WHERE score 60;然后再执行DELETE FROM student WHERE score 60;这两条SQL的WHERE条件完全相同只是前面的查询命令换成了删除命令。很多成熟团队甚至会在代码规范里规定涉及生产数据的DELETE必须经过审批审批材料里必须包含SELECT预览结果。删除之后同样用SELECT验证SELECT * FROM student WHERE score 60;结果为空说明删除生效。强迫自己养成“删除三步曲”——备份、预览、执行后验证以后你会感谢这个习惯。5. SELECT查询数据DML里的重头戏虽然标题是“DML语句定义及常见用法示例”但SELECT在实际工作中和增删改是绑定的没有查询能力前面学的INSERT、UPDATE、DELETE都像在盲人摸象。所以这一章花一半篇幅讲SELECT完全值得。5.1 SELECT的基本骨架别让*符号害了你最简单粗暴的查询SELECT * FROM student;这条语句把student表所有字段、所有行全查出来。星号代表“所有列”在表结构简单、数据量小的时候用起来很爽但有两个隐患数据量大时SELECT * 会白白传输你根本用不到的字段浪费内存和带宽。表结构一旦变更比如加了几个大字段程序里用“按位置取值”的代码就会出错。所以在实际项目里更推荐按需查询SELECT id, name, score FROM student;这样只拿你关心的三个字段代码意图清晰执行效率也更高。5.2 WHERE过滤从全表扫描到精确定位查询的核心就是过滤。刚刚学的比较运算符、逻辑运算符在这里全面派上用场SELECT id, name, score FROM student WHERE score 85 AND city 上海;这条语句筛选出上海且成绩不低于85分的学生。WHERE后面可以接等值判断、范围判断、、、、、模糊匹配LIKE、集合判断IN、NOT IN、空值判断IS NULL、IS NOT NULL。常见几个例子一并展示-- 查询成绩在80到90之间的学生 SELECT id, name, score FROM student WHERE score BETWEEN 80 AND 90; -- 查询城市是北京或上海的学生 SELECT id, name, city FROM student WHERE city IN (北京, 上海); -- 查询名字以“张”开头的学生 SELECT id, name FROM student WHERE name LIKE 张%; -- 查询没有填写成绩的学生 SELECT id, name FROM student WHERE score IS NULL;这里要特别提醒一个新手常犯的错误空值判断必须用IS NULL不能用 NULL。在SQL里score NULL永远不会返回结果因为NULL表示“未知”和任何值比较的结果都是NULL而NULL在WHERE里被当作假。我见过不少人在这个问题上卡了一整天。5.3 排序与分组让数据说话查询查出来的数据往往是无序的要让它有逻辑用ORDER BYSELECT id, name, score FROM student ORDER BY score DESC, id ASC;先按成绩从高到低排成绩相同的按id从低到高排。DESC是降序ASC是升序默认就是升序所以ORDER BY score等价于ORDER BY score ASC。分组是另一个高频操作结合聚合函数使用SELECT city, COUNT(*) AS student_count, AVG(score) AS avg_score FROM student GROUP BY city;这条语句按城市分组统计每个城市的学生人数和平均分。GROUP BY后面跟的字段必须出现在SELECT列表中或者被聚合函数包裹这是SQL的规则违反了会报错。如果要筛掉某些分组用HAVING它不是WHERE的替代品而是作用在分组结果上的过滤器SELECT city, AVG(score) AS avg_score FROM student GROUP BY city HAVING AVG(score) 85;这条只显示平均分不低于85分的城市分组。5.4 LIMIT分页查数据不能一次全倒出来数据量一大没人会一次性把几十万行全部查出来展示到页面上。LIMIT负责“取前N条”或者“分页跳过若干条”-- 取成绩最高的前5名 SELECT id, name, score FROM student ORDER BY score DESC LIMIT 5; -- 跳过第1页的5条取第2页的5条 SELECT id, name, score FROM student ORDER BY id ASC LIMIT 5 OFFSET 5;第一句很好理解第二句里OFFSET 5表示“跳过前5条”然后LIMIT 5表示“取5条”合起来就是6到10条。很多编程框架生成分页SQL时会自动套LIMIT和OFFSET你看到LIMIT 10, 20这种写法也别慌它的意思是OFFSET 10、LIMIT 20也就是跳过10条取20条。分页查询有个性能坑提前告诉你页码越深OFFSET越大MySQL要扫描并丢弃前面的所有行性能急剧下降。后续学索引优化时你会接触到“基于游标分页”的替代方案现阶段先把LIMIT语义搞清楚就行。6. DML操作与事务数据一致性从哪来增删改查都学完了但还差最后一个环节这些操作能不能被撤销能不能保证多条语句“一起成功或者一起失败”6.1 为什么DML离不开事务想象一个最典型的场景转账。从A账户扣100元往B账户加100元这需要两条UPDATE语句UPDATE account SET balance balance - 100 WHERE user_id A; UPDATE account SET balance balance 100 WHERE user_id B;第一条执行成功、第二条执行失败会怎样A的钱少了100B的钱没增加系统整体就乱了。事务Transaction就是来解决这个问题的把多条DML操作打包成一个整体要么全部成功要么全部回滚不存在“做了一半”的状态。6.2 三个关键词BEGIN、COMMIT、ROLLBACKMySQL里事务控制的核心就三个命令BEGIN; -- 开启一个事务 UPDATE account SET balance balance - 100 WHERE user_id A; UPDATE account SET balance balance 100 WHERE user_id B; COMMIT; -- 确认提交让改动永久生效如果在执行中途发现错了不想让任何改动生效BEGIN; UPDATE account SET balance balance - 100 WHERE user_id A; UPDATE account SET balance balance 100 WHERE user_id B; ROLLBACK; -- 回滚撤销刚才的所有改动ROLLBACK之后这两条UPDATE的影响全部消失相当于从来没执行过。还有不同状态的COMMIT和ROLLBACK在事务里的位置很讲究。默认情况下MySQL是自动提交模式也就是每条DML语句执行完就自动提交不需要你手动COMMIT。但一旦你写了一个BEGIN开启事务后续所有DML语句都不会自动提交直到你执行COMMIT或ROLLBACK为止。在程序开发中我建议的项目级规范是凡是涉及多张表联动修改的DML操作必须显式开启事务并在finally或defer逻辑里做好回滚兜底。你以后用Python的pymysql、Java的JDBC或MyBatis都会看到事务的身影事务是DML的安全网。6.3 DDL自动提交一个容易被忽略的坑有一类语句会“偷偷”把事务结束掉同时把当前事务里还没提交的改动直接提交这类语句主要是DDL比如CREATE TABLE、ALTER TABLE、DROP TABLE、TRUNCATE TABLE。举一个极其常见的悲剧场面BEGIN; UPDATE account SET balance balance - 100 WHERE user_id A; UPDATE account SET balance balance 100 WHERE user_id B; -- 某同事不小心在这中间执行了 -- ALTER TABLE account ADD COLUMN remark VARCHAR(100); -- 你以为还能ROLLBACK实际上事务已经被隐式提交了 ROLLBACK;执行ALTER TABLE时MySQL会先把当前事务自动提交然后执行DDL。所以前两条UPDATE已经永久生效了ROLLBACK根本救不回来。这正是我为什么说INSERT、UPDATE、DELETE的表结构尽量在业务开始前一次性做好不要在事务运行到一半时做ALTER。否则数据一致性和你的预期会差得很远。6.4 用演示走一遍事务的完整流程回到student表模拟一个最简单的操作把张三的成绩5再给李四的成绩3然后验证两个改动是否同时生效。SELECT * FROM student WHERE name IN (张三, 李四); BEGIN; UPDATE student SET score score 5 WHERE name 张三; UPDATE student SET score score 3 WHERE name 李四; COMMIT; SELECT * FROM student WHERE name IN (张三, 李四);如果你在COMMIT之前把第二条UPDATE故意写错比如字段名写成scroeMySQL会报错此时事务还没有提交你可以立刻ROLLBACK张三第一条改动也随之撤销。这就是“要么全成功、要么全回滚”的直观体验。事务这部分内容远不止这些隔离级别、锁机制、MVCC都是后面章节的重头戏。但现阶段你只需要记住多条DML组合操作时用事务把它们捆在一起必要的时候用ROLLBACK兜底。把这一条贯彻到位你就已经比很多凭感觉写增删改的开发者专业了。从INSERT到UPDATE从DELETE到SELECT再到用事务把DML操作拧成一股绳这一章把DML的骨架搭完了。根据我个人经验学到这里最忌讳的就是“眼会手不会”。建议你跟着每一条示例在自己的MySQL里把student表建出来把三种INSERT写法都跑一遍把不带WHERE的UPDATE和DELETE各自试一次在测试表上亲身体验一下“全表被改”的震撼下次写代码才会本能地先摸WHERE。最后再分享一个小技巧以后你在命令行或客户端里执行重要DML语句之前如果心里有一丝丝不确定就先在原SQL前面加个SELECT把WHERE条件跑一遍。这个习惯陪我走过了很多线上环境价值比你想象的还要大。下一章我们自然就会聊到数据过滤与排序的更多玩法到时候你已经有了这批数据基础进阶起来会顺畅很多。