★ FEATURED ARTICLE 学MySQL有一段时间了今天想认认真真聊聊DML语句也就是数据操作语言。无论你是刚建好库和表的新手还是已经能写简单查询的初级开发者DML都是每天接触最多、出问题也最多的部分。INSERT、UPDATE、DELETE、SELECT这四条核心命令几乎覆盖了业务开发中90%的数据操作场景。这篇文章我打算用一套可以跟着敲的示例表从定义讲到用法再讲到实操中才遇得到的坑整体就是一篇可以当成“第三章”笔记来用的长文。如果你正好学到数据库操作这一块或者平时写SQL总感觉哪里不够踏实那这篇内容应该能帮上忙。提示本文所有示例都基于MySQL 8.0环境使用的字符集是utf8mb4建议你也用同样的配置避免后面演示时出现乱码问题。1. 先把概念理清楚DML到底是什么1.1 DML在SQL家族里的位置SQL语言按功能划分成几大类DDL数据定义语言负责CREATE、ALTER、DROP这些建表改表的操作DCL数据控制语言负责GRANT、REVOKE这类权限管理TCL事务控制语言管的是COMMIT、ROLLBACK而DML就是数据操作语言核心成员就四个INSERT、UPDATE、DELETE、SELECT。这里值得说一下有些老资料会把SELECT单独归为DQL数据查询语言但在MySQL官方手册和绝大多数面试场景里SELECT依然被归入DML范畴因为增删改查本身就是围绕“数据操作”这个整体概念展开的。你在写DML时SELECT作为查询操作反过来经常嵌套在INSERT、UPDATE、DELETE里充当数据来源分开理解是正确的但放在一起学更顺手。DML操作的对象是数据行不是表结构。换句话说DDL影响的是表的“骨架长什么样”DML影响的则是“表里到底装了什么内容”。我见过不少初学者把ALTER TABLE和UPDATE混在一起其实区别特别简单你想给表加一列那是DDL你想把某行数据的某个字段值改一下那是DML。1.2 为什么建议先掌握DML很多教程喜欢先花大篇幅讲数据类型、约束、索引这些内容当然重要但如果你是零基础一上来就背这些理论很容易劝退。更好的节奏是先会用DML把自己手头的数据“玩转”再回头补理论知识理解会深很多。比如你写一条INSERT语句时自然会接触到字段类型、默认值、非空约束写一条UPDATE时自然会碰到WHERE条件的威力写SELECT时自然会遇到索引为什么会让查询变快。从这个角度看DML是打通整个MySQL学习路径的关键节点。学完这一章你基本就能独立完成“建表、灌数据、改数据、删数据、查数据”的完整闭环这对后续学习事务、索引、优化都特别有帮助。2. 准备一套能跟着练的示例表没有数据支撑的SQL文章都是耍流氓所以先建两张表一张学生表一张班级表。这两张表之间有外键关联后续演示多表操作时也用得上。-- 班级表 CREATE TABLE class_info ( id INT AUTO_INCREMENT PRIMARY KEY COMMENT 班级ID, class_name VARCHAR(50) NOT NULL COMMENT 班级名称, create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT班级信息表; -- 学生表 CREATE TABLE student_info ( id INT AUTO_INCREMENT PRIMARY KEY COMMENT 学生ID, name VARCHAR(50) NOT NULL COMMENT 学生姓名, age INT COMMENT 年龄, gender TINYINT DEFAULT 0 COMMENT 性别: 0未知 1男 2女, class_id INT COMMENT 所属班级ID, score DECIMAL(5,2) COMMENT 总分, enroll_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 入学时间 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT学生信息表;这里用class_info和student_info是为了避免和MySQL的保留字冲突比如order这种词就不能直接当表名新手如果习惯用student、class其实也可以但加个后缀更保险。两张表建好之后先插入几条班级数据作为铺垫INSERT INTO class_info (class_name) VALUES (软件工程1班), (计算机科学2班), (大数据3班);从这一节开始下面的所有DML示例都会围绕这套表展开你可以直接复制到自己的测试库里跑跟着看效果。3. INSERT插入语句向表里喂数据的几种姿势3.1 基础单行插入INSERT最基础的语法是INSERT INTO 表名 (字段1, 字段2, ...) VALUES (值1, 值2, ...);举个例子向学生表插入一条记录INSERT INTO student_info (name, age, gender, class_id, score) VALUES (张三, 20, 1, 1, 588.50);这里有几个细节值得注意。字段列表可以不写全没写的字段会使用默认值比如enroll_time没写就自动取当前时间id没写就自动生成自增值。但如果你把某个NOT NULL字段漏掉了并且该字段没有默认值MySQL会直接报错。所以新手在一开始就要养成“写INSERT时明确字段列表”的习惯这比简写成INSERT INTO student_info VALUES (...)要稳健得多因为后者要求值的数量和顺序必须和表结构完全一致表结构一变你的SQL就崩了。字段的顺序可以按自己的意愿排列只要值和字段一一对应就行。比如我把年龄和班级ID换一下顺序也完全没问题只要VALUES里跟着换就行。3.2 多行插入与SET写法多行插入是MySQL一个很实用的特性一次插入多条记录时性能会比循环执行单条INSERT好很多。语法就是在VALUES后面用逗号分隔多组值INSERT INTO student_info (name, age, gender, class_id, score) VALUES (李四, 21, 1, 1, 552.00), (王五, 19, 2, 2, 621.50), (赵六, 22, 1, 3, 487.00), (钱七, 20, 2, 2, 599.00);执行完这条语句我们可以顺带用SELECT验证一下SELECT * FROM student_info;除了VALUES写法MySQL还支持SET形式的插入INSERT INTO student_info SET name 孙八, age 18, gender 2, class_id 3;这种写法更直观尤其适合一次只插入一条记录的场景写起来不像VALUES那样容易看花眼。但它的缺点是不太方便插入多行。所以我的建议是单条插入用SET批量插入用VALUES多行这个习惯可以一直保持下去。3.3 INSERT ... SELECT把查询结果灌进表里有些场景下你需要把另一张表或者同一张表的某些行的数据复制到目标表里。这时候INSERT ... SELECT就是效率最高的方式不需要先把数据查出来再手动拼INSERT。-- 把班级ID为2的学生复制到班级ID为3 INSERT INTO student_info (name, age, gender, class_id, score) SELECT name, age, gender, 3, score FROM student_info WHERE class_id 2;这里有一个很重要的点SELECT子句返回的列数和类型必须和INSERT后面的字段列表匹配否则MySQL会报“Column count doesnt match value count”的错误。另外需要注意如果原表里有自增主键你复制数据时可以不把主键复制过去让目标表自动生成新的主键。这种写法在实际业务里特别常用比如你要把“旧表数据迁移到新表”“把正式环境的数据抽一份到测试环境”用一条SQL就搞定了。我在实际项目里遇到过好几次因为人为复制数据导致的错误用INSERT ... SELECT配合WHERE条件反而更可控。3.4 主键冲突处理INSERT IGNORE和ON DUPLICATE KEY UPDATE给表里插入数据时最头疼的问题之一就是主键冲突。比如你想插入一个ID1的学生但表里已经有ID1的数据了直接执行INSERT会报错。解决办法有三种。第一种是INSERT IGNORE遇到主键或唯一键冲突就自动跳过这条数据不报错INSERT IGNORE INTO student_info (id, name, age) VALUES (1, 张三, 20);第二种是ON DUPLICATE KEY UPDATE它的逻辑是如果主键冲突了就执行后面的更新操作不冲突才执行插入。举个实际用法INSERT INTO student_info (id, name, age, score) VALUES (1, 张三, 20, 590.00) ON DUPLICATE KEY UPDATE age VALUES(age), score VALUES(score);这里VALUES(age)和VALUES(score)指的是你INSERT语句中试图插入的值。执行效果是ID1的记录存在则把age更新为20、score更新为590.00如果不存在就正常插入完整的记录。这在做数据同步、每日批处理任务时几乎是标配写法。第三种是REPLACE INTO这种方式的逻辑是先尝试插入如果遇到主键冲突就先删除原来的行再插入新行。它看起来和ON DUPLICATE KEY UPDATE有点像但执行代价更高因为删除和重新插入会导致自增主键发生变化。除非你能接受这一点否则我不推荐新手习惯性用它。我在多个项目里都见过因为用REPLACE导致数据被重建、外键关联出问题的案例尽量用INSERT ... ON DUPLICATE KEY UPDATE才是更稳的选择。3.5 INSERT相关的常见坑插入数据时最容易出问题的几个点我单独列出来字符串和日期必须加引号。数值类型可以裸写但字符串和日期不加引号时MySQL会尝试把它解析成数值或报错。比如插入abc不写引号直接就炸了。NULL和空字符串是两回事。NULL表示未知表示空字符串。如果字段设置了NOT NULL插入NULL会报错但插入不会。自增列不要手动赋值。除非你清楚自己在做什么否则让MySQL自己生成主键就好。手动插入一个很大的ID可能会把自增序列搞得混乱。超长字段值会被截断或报错。MySQL在严格模式下8.0默认开启会直接报错非严格模式下会静默截断这也是很多“数据看起来不对”的根源。判断当前模式可以用SELECT sql_mode;4. UPDATE更新语句改数据之前先想清楚4.1 基本UPDATE语法和条件过滤UPDATE的基本语法UPDATE 表名 SET 字段1 值1, 字段2 值2 WHERE 条件;比如把张三的年龄改成21UPDATE student_info SET age 21 WHERE name 张三;这里要单独讲讲WHERE子句的重要性。UPDATE不带WHERE就是对整张表的所有行执行更新。有一次我在测试环境执行UPDATE student_info SET score 600结果是全表学生的成绩全变成600分。在测试环境可能一笑了之如果在生产环境后果不堪设想。所以每次写UPDATE都可以在心里默念一遍我的WHERE真的能精准圈定目标数据吗建议在更新前先跑一遍同条件的SELECT确认影响范围SELECT * FROM student_info WHERE name 张三;然后再放心执行UPDATE。这个习惯一点都不麻烦但能省下无数后悔药。4.2 关联更新多表UPDATE有些场景下你要根据另一张表的数据来更新当前表。比如班级改名后你想把学生表里所有该班级学生的班级名称同步更新就可以用多表UPDATEUPDATE student_info s JOIN class_info c ON s.class_id c.id SET s.class_id 2 WHERE c.class_name 计算机科学2班;这里JOIN是用来关联两张表的它的核心逻辑是先找出班级名为“计算机科学2班”的班级ID然后更新学生表中对应的行。这种写法的好处是省去了子查询让SQL更清晰。更常见的一种写法是利用子查询UPDATE student_info SET score score 10 WHERE class_id IN (SELECT id FROM class_info WHERE class_name LIKE %计算机%);这句话会把计算机相关班级的所有学生成绩加10分。注意MySQL里更新同一张表时子查询直接引用同一张表可能会报“You cant specify target table for update in FROM clause”的错误这个后面在常见问题里我会详细说解法。4.3 UPDATE时容易被忽略的细节第一字段值的合法性。UPDATE不会自动检查你新赋的值是否合理比如把年龄改成-5MySQL不会拦你除非你建了CHECK约束。业务上的校验应该由应用层负责或者在建表时加合理的CHECK约束。第二数据类型的隐式转换。如果字段是字符串类型你给它赋一个数值MySQL会尝试自动转换虽然一般不报错但可能导致索引失效查询变慢。所以写UPDATE时尽量保持值的类型和字段类型一致。第三唯一键冲突。更新时如果新值导致唯一键重复MySQL会报错。比如你把某个学生的姓名改成和另一个学生相同而姓名列设置了UNIQUE约束这条UPDATE就会失败。5. DELETE删除语句删数据其实是门技术活5.1 DELETE基本语法与注意事项DELETE的基本语法DELETE FROM 表名 WHERE 条件;比如删除张三DELETE FROM student_info WHERE name 张三;同样DELETE不带WHERE就是清空整张表。而且DELETE和UPDATE还有一个区别有些数据库系统对于不带WHERE的DELETE会要求额外的权限或确认但MySQL默认不会拦你所以误删的可能性更高。我的建议是删除前先SELECT确认删除时尽量用主键或唯一键来圈定范围不要只凭姓名这种可能重复的字段来删。5.2 DELETE和TRUNCATE的区别很多新手问清空表的时候用DELETE还是TRUNCATE这两者的核心区别如下对比维度DELETETRUNCATE操作类型DML逐行删除DDL重建表结构是否可带WHERE可以按条件删不可以直接清空执行速度慢逐行操作并写日志快直接释放数据页自增ID是否重置不重置继续往下增长重置从1开始是否可以回滚在事务中可以默认不可回滚场景上如果只是删掉部分数据必然用DELETE如果要清空整张临时表并且不需要保留自增ID的连续性TRUNCATE更合适。需要注意TRUNCATE会触发MySQL的隐式提交即使你把它包在事务里也回滚不了这一点一定要记牢。5.3 多表删除同时删两张表的数据需要同时删除多个表的数据时多表DELETE就派上用场了。比如删除班级ID为3的班级同时删除该班级下的所有学生DELETE s, c FROM student_info s JOIN class_info c ON s.class_id c.id WHERE c.id 3;这里要注意DELETE后面跟的是表别名表示你要删的是哪几个表的数据而不是删JOIN的结果。如果只写DELETE FROM student_info那就只删学生表班级表还得再写一条SQL。实际开发中多表删除要特别谨慎尤其是存在外键关系时。如果表之间设置了ON DELETE CASCADE删父表记录时子表会自动删如果没有外键约束或者约束方式是RESTRICT删父表会报错得手动删完子表再删父表。5.4 误删除的补救思路虽然不想吓唬人但每个DBA和开发者都迟早会遇到误删数据的情况。简单说几个补救方向。如果MySQL开启了binlog可以通过binlog日志把删除操作找回来这是比较标准的恢复手段。测试环境下很多实例默认没开binlog生产环境一般都会开。如果你已经把DELETE包在事务里并且还没COMMIT直接ROLLBACK就能恢复。日常预防上可以给重要的表建定时备份任务甚至用软删除加一个is_deleted字段来代替物理删除。软删除在业务系统里非常常见避免了数据真正丢失带来的风险。另外MySQL 8.0里有UNDO日志保留时间的配置删除的数据在短时间内还是可恢复的但不要指望它最快最省事的还是“提前备份谨慎操作”。6. SELECT查询语句DML里真正的主角6.1 基础查询与过滤排序SELECT的语法比前面三个都复杂因为它承载了最丰富的查询需求。基本结构是SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 列 HAVING 条件 ORDER BY 列 LIMIT 数量;先看最基础的用法-- 查询所有学生 SELECT * FROM student_info; -- 只查姓名和成绩 SELECT name, score FROM student_info; -- 按成绩降序排列 SELECT name, score FROM student_info ORDER BY score DESC;ORDER BY默认为升序如果要降序要显式加DESC。多字段排序时写在前面的优先级高。比如先按班级排序再按成绩降序SELECT name, class_id, score FROM student_info ORDER BY class_id ASC, score DESC;6.2 条件过滤WHERE的进阶玩法WHERE子句里除了、、、这些比较运算符还有一些很常用的写法。-- 范围查询 SELECT * FROM student_info WHERE score BETWEEN 500 AND 600; -- 列表查询 SELECT * FROM student_info WHERE class_id IN (1, 2, 3); -- 模糊查询名字以张开头 SELECT * FROM student_info WHERE name LIKE 张%; -- 空值判断注意IS NULL不能写成 NULL SELECT * FROM student_info WHERE class_id IS NULL;模糊查询中%表示任意多个字符包括0个_表示任意单个字符。比如LIKE 张_只能匹配“张”加一个字的名字如“张三”匹配不了“张大壮”。这个细节在面试里也常考。还有一个新手容易踩的坑NULL的判断。NULL不是等于某个值也不是不等于某个值它是“未知”。所以任何 NULL的写法都查询不到数据只能用IS NULL或者IS NOT NULL。6.3 聚合函数与分组统计聚合函数是SELECT里非常核心的一块。它把多行的值汇总成一个值常用的有COUNT、SUM、AVG、MAX、MIN。比如统计学生人数和平均分SELECT COUNT(*) AS total_count, AVG(score) AS avg_score FROM student_info;COUNT(*)统计的是所有行数包括值为NULL的行COUNT(score)只统计score列非空的记录数。这个区别如果没搞清楚统计结果可能会有偏差。分组统计要配合GROUP BY比如按班级统计人数和平均分SELECT class_id, COUNT(*) AS num, AVG(score) AS avg_score FROM student_info GROUP BY class_id;这里很自然地引出一个问题如果你想在分组之后再加筛选条件不能用WHERE而要用HAVING。比如只想看平均分大于550的班级SELECT class_id, AVG(score) AS avg_score FROM student_info GROUP BY class_id HAVING avg_score 550;WHERE和HAVING的区别可以简单理解成WHERE在分组前过滤原始行HAVING在分组后过滤聚合结果。6.4 多表连接查询JOIN的用法数据很少只存在一张表里实际业务里绝大多数查询都要关联多张表。最常用的是INNER JOIN它返回两张表中满足关联条件的交集数据。SELECT s.name, c.class_name FROM student_info s INNER JOIN class_info c ON s.class_id c.id;这里INNER可以省略不写直接写JOIN效果一样。ON后面写的是关联条件它决定了两个表怎么拼在一起。LEFT JOIN则会把左表的所有数据都查出来即使右表没有匹配的记录右表字段会显示为NULL。SELECT s.name, c.class_name FROM student_info s LEFT JOIN class_info c ON s.class_id c.id;LEFT JOIN在开发中使用频率非常高比如你要查“每个学生的班级名即使这个学生没有分配班级也要列出来”就必须用LEFT JOININNER JOIN会直接把没分班的学生过滤掉。这两种JOIN的区别一定要弄透面试和实际开发里都是重中之重。6.5 分页查询LIMIT的实用套路分页查询是列表页功能的基本功。MySQL用LIMIT实现SELECT * FROM student_info ORDER BY id LIMIT 10 OFFSET 0;OFFSET 0表示从第1行开始取10行OFFSET 10就是从第11行开始。也可以简写成SELECT * FROM student_info ORDER BY id LIMIT 10, 10;这个写法中LIMIT 10, 10的第一个10是偏移量第二个10是返回的行数。很多新手会记反所以建议直接用LIMIT 页大小 OFFSET 偏移量这种写法语义更清晰。分页一定要配ORDER BY否则每次查出来的顺序可能不一样导致翻页时数据出现重复或漏掉。7. 实战小案例用DML完成一个完整需求把前面的知识串起来我们做一个模拟需求把计算机科学2班中成绩低于550分的学生全部加10分然后统计各班人数和平均分。第一步先查询一下目标数据看看影响范围SELECT s.id, s.name, s.score, c.class_name FROM student_info s JOIN class_info c ON s.class_id c.id WHERE c.class_name 计算机科学2班 AND s.score 550;第二步执行更新UPDATE student_info SET score score 10 WHERE class_id IN (SELECT id FROM class_info WHERE class_name 计算机科学2班) AND score 550;这里我想专门解释一下最容易出问题的地方如果你直接用JOIN的方式更新就把目标表也关联进去MySQL可能报错用子查询时如果子查询和更新目标表是同一张表也会出现限制。上面的写法是先把class_info查出来再更新student_info没有直接引用被更新的表所以能正常执行这是我实际开发中被MySQL坑过之后总结出来的稳妥写法。第三步验证结果SELECT c.class_name, COUNT(*) AS student_num, AVG(s.score) AS avg_score FROM student_info s JOIN class_info c ON s.class_id c.id GROUP BY c.class_name;这个流程看着简单但非常接近真实业务的处理逻辑先查再改再统计。每一步都在用前面讲过的知识这就是DML的日常形态。8. 常见问题速查表与避坑指南8.1 高频报错与解决方法问题现象报错信息原因解决方法插入数据时提示列数不匹配Column count doesnt match value countVALUES里的数量和字段列表数量不一致检查字段列表和值列表一一对应字符串忘记加引号Unknown column abc字符串被当成列名解析字符串和日期必须加单引号更新同一张表时子查询报错You cant specify target table for update in FROM clauseMySQL不允许同一张表作为更新目标和子查询来源子查询外面再套一层临时表或用JOIN使用保留字当表名/列名You have an error in your SQL syntaxorder、group等词是保留字加反引号或者换个名字中文乱码显示为问号字符集不一致建表用utf8mb4连接串加characterEncodingutf8查询结果NULL判断不出查不到数据用了 NULL改用IS NULL或IS NOT NULL删数据没有生效Rows matched: 0 Changed: 0WHERE条件没有匹配到数据先SELECT验证条件是否成立8.2 新手最容易踩的5个坑第一个坑是UPDATE和DELETE忘了写WHERE或者WHERE写得过于宽松。我见过因为一个WHERE id 0导致全表数据被改写的案例这种错误轻则数据错乱重则直接删库跑路。第二个坑是混淆了和IS对NULL的判断。NULL NULL的结果是NULL所以条件不成立。所有对NULL的判断都必须用IS NULL或IS NOT NULL。第三个坑是在多表JOIN时搞不清驱动表和匹配表的角色。LEFT JOIN左边的表是驱动表它会全量保留右边是匹配表匹配不到就补NULL。脑子里有这层概念写起多表查询才不会逻辑混乱。第四个坑是分页越翻越乱。没有ORDER BY的LIMIT分页MySQL返回的记录顺序是不确定的尤其在数据量大的时候翻页会出现重复数据。分页时一定要指定唯一性强的排序字段。第五个坑是执行完DML后忘了提交事务。如果你在代码里开启了事务但忘记COMMIT数据在别人眼里一直是旧状态这种现象在排查“为什么数据改了前端没变化”的问题时特别容易遇到。8.3 我的一点小建议既然看到这里了最后分享一个我用了很多年的习惯每次写DML尤其是UPDATE和DELETE都强制自己先写一条同名SELECT大概花两三秒的时间确认WHERE条件圈定的范围再回头执行。这个习惯在压力大、事情多的时候特别能救命。另外测试环境里练SQL时可以随便造一些垃圾数据反复试把报错都见一遍反而到了生产环境就不慌了。我自己刚学MySQL那会儿就是把一份数据翻来覆去地插入、更新、删除、再查回来折腾到最后四条命令的各种边缘情况基本都摸清了。后续如果你想深入可以再往事务、锁、索引优化这些方向走但DML这关打得越扎实后面这些东西学起来就越顺畅。
阅读完成 · 觉得有帮助？
