在 MySQL 的学习路径中“表数据操作”是很容易被低估的一个环节。很多新手花了不少时间理解建表语句、字段类型、主键外键但到了真正写业务代码时每天接触最多的反而是对表里数据进行增删改查。更麻烦的是这类操作看起来简单写错之后的代价却可能非常大一条没有 WHERE 的 UPDATE 会改掉整张表一条没有事务保护的 DELETE 可能让线上数据无法恢复。本文围绕 MySQL 表数据操作展开系统梳理 INSERT、SELECT、UPDATE、DELETE 的使用方法、常见写法、事务与锁的影响并给出一个可以直接运行的完整案例。无论你是刚开始学数据库的初学者还是已经写过一段时间 SQL、想补齐安全细节的开发者这篇文章都值得收藏对照。1. 表数据操作到底是什么1.1 DML 与 DDL、DCL 的分工要理解 MySQL 表数据操作先要分清 SQL 语句的几个大类。数据库日常执行的语句通常可以划分为 DDL、DML、DCL、DQL类别英文全称主要语句作用对象DDLData Definition LanguageCREATE、ALTER、DROP、TRUNCATE表结构、数据库结构DMLData Manipulation LanguageINSERT、UPDATE、DELETE表中的数据DQLData Query LanguageSELECT查询表中的数据DCLData Control LanguageGRANT、REVOKE用户权限与访问控制本文关注的重点是 DML也就是对表数据的增、删、改操作。不过在日常开发中SELECT 查询和 DML 的关系非常紧密因为我们需要先查出哪些数据满足条件才敢放心执行 UPDATE 或 DELETE。所以下文会把 SELECT 也纳入“表数据操作”的完整讲解范围。1.2 为什么表数据操作如此重要建表只是搭建骨架真正驱动业务运转的是表里的数据。用户注册后要插入一条用户记录下单后要更新订单状态商品下架后要删除或标记失效记录。这些都是典型的表数据操作。从另一个角度看后端开发中大量性能问题、数据一致性问题、线上事故也都集中在数据操作环节。比如 UPDATE 没有走索引导致锁范围扩大DELETE 删掉了重要业务数据INSERT 大批量逐条提交导致性能很差。掌握表数据操作不等于只会写四条基本语句而是要理解条件过滤、事务边界、索引影响、备份策略这样在真实项目中才能安全落地。1.3 先理解行、列与记录在 MySQL 中一张表由行和列组成。列定义了字段名称与数据类型行则是一条具体的数据记录。例如学生表里每一行代表一个学生每一列代表学号、姓名、班级等信息。学习 DML 时要始终带着“操作对象是行”的意识。INSERT 是新增一行或多行UPDATE 是修改符合条件的行DELETE 是删除符合条件的行SELECT 是筛选出符合条件的行。写 SQL 时最重要的就是明确“哪些行会被影响”。2. 环境准备与测试表设计2.1 连接 MySQL开始操作前先确认已经安装并启动了 MySQL 服务。MySQL 的安装方式很多可以用官方安装包、操作系统软件源也可以用 Docker 快速启动一个实例。本文的重点是表数据操作SQL 语法在 MySQL 5.7 和 MySQL 8.0 中基本通用如果你的版本较新个别细节以自己环境为准即可。连接本机 MySQL 最常用的命令是mysql -uroot -p输入密码后会出现mysql提示符。看到提示符说明已经进入 MySQL 客户端可以执行 SQL 语句了。如果 MySQL 不在当前机器的默认 socket 路径也可以指定主机和端口mysql -h127.0.0.1 -P3306 -uroot -p这里-h指定主机-P指定端口-u指定用户-p表示需要输入密码。生产环境不建议直接用 root 操作业务库最好单独创建业务账号并授予最小权限。2.2 创建数据库与成绩表为了方便后续演示我们创建一个school_db数据库并设计一张学生表和一张成绩表。先创建数据库CREATE DATABASE IF NOT EXISTS school_db DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;使用数据库USE school_db;创建学生表CREATE TABLE student ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键, student_no VARCHAR(20) NOT NULL COMMENT 学号, student_name VARCHAR(50) NOT NULL COMMENT 姓名, gender CHAR(1) DEFAULT NULL COMMENT 性别, class_no VARCHAR(20) DEFAULT NULL COMMENT 班级, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id), UNIQUE KEY uk_student_no (student_no) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT学生表;创建成绩表CREATE TABLE score ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键, student_no VARCHAR(20) NOT NULL COMMENT 学号, course_name VARCHAR(50) NOT NULL COMMENT 课程名称, score DECIMAL(5,2) NOT NULL COMMENT 成绩, exam_date DATE NOT NULL COMMENT 考试日期, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id), KEY idx_student_course (student_no, course_name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT成绩表;这里没有创建物理外键而是通过student_no字段保持逻辑关联。实际项目中是否使用外键需要根据业务设计权衡外键能保证完整性但也可能带来额外的锁与性能开销。表数据操作示例中先使用逻辑外键更灵活。关于字段类型这里单独强调一个常见误区MySQL 中的INT(5)并不表示“只能存 5 位数字”。INT的存储范围是固定的括号里的数字通常只是配合ZEROFILL使用时的显示宽度。比如INT(5)仍然可以存储超过 5 位的整数只是某些场景下显示效果不同。计算时也仍然按照整数类型处理并不会因为写成了INT(5)就限制数值大小。2.3 准备初始测试数据插入几条学生数据INSERT INTO student (student_no, student_name, gender, class_no) VALUES (1001, 张三, M, 2024-01班), (1002, 李四, F, 2024-01班), (1003, 王五, M, 2024-02班), (1004, 赵六, F, 2024-02班), (1005, 孙七, M, 2024-03班);插入成绩数据INSERT INTO score (student_no, course_name, score, exam_date) VALUES (1001, 数据库原理, 82.50, 2025-01-10), (1001, Java程序设计, 67.00, 2025-01-12), (1002, 数据库原理, 58.00, 2025-01-10), (1002, Java程序设计, 74.00, 2025-01-12), (1003, 数据库原理, 45.50, 2025-01-10), (1004, 网络基础, 90.00, 2025-01-15), (1005, Java程序设计, 63.50, 2025-01-12);这样我们就有了一个可以反复练习的表环境。后面的讲解都会围绕这两张表展开。3. 插入数据INSERT 的多种写法3.1 基础的单行插入INSERT 的作用是向表中新增记录。最基本的语法是INSERT INTO student (student_no, student_name, gender, class_no) VALUES (1006, 周八, M, 2024-03班);执行后MySQL 会返回类似下面的信息Query OK, 1 row affected (0.01 sec)这说明成功插入了 1 行记录。如果省略字段列表就需要按照建表时的字段顺序依次提供所有值INSERT INTO student VALUES (NULL, 1007, 吴九, F, 2024-01班, NOW());但这种写法要求调用方对表结构非常清楚一旦表结构调整这段 SQL 很容易出错。实际开发中更推荐显式写出字段列表可读性和稳定性都更好。3.2 插入部分字段当某些字段有默认值或者允许为 NULL 时INSERT 语句可以不提供完整字段列表。例如gender允许为 NULLcreate_time有默认值那么可以这样写INSERT INTO student (student_no, student_name, class_no) VALUES (1008, 郑十, 2024-01班);执行后gender会是 NULLcreate_time会自动使用当前时间。灵活使用默认值可以减少业务代码里不必要的赋值。3.3 一次插入多行当需要批量插入多条记录时不要一条一条执行 INSERT而是使用多行 VALUES 的写法INSERT INTO score (student_no, course_name, score, exam_date) VALUES (1009, 数据库原理, 88.00, 2025-03-01), (1010, Java程序设计, 72.50, 2025-03-05), (1011, 网络基础, 91.00, 2025-03-08);一次插入多行可以减少客户端与 MySQL 服务端之间的网络交互次数也能让导入效率明显提升。在数据量较大时可以按每批 500 到 2000 行的量级分批执行避免单条 SQL 过大。3.4 通过 SELECT 复制数据INSERT 的数据来源不一定只能手写 VALUES也可以来自另一张表或同一张表的查询结果。例如要把学生表中 2024-01 班的学生复制到一张临时表中可以先建一张临时表CREATE TABLE student_temp LIKE student;然后使用 INSERT INTO ... SELECT 把数据复制进去INSERT INTO student_temp (student_no, student_name, gender, class_no) SELECT student_no, student_name, gender, class_no FROM student WHERE class_no 2024-01班;这种写法常用于数据归档、临时表加工、表结构升级等场景。需要注意SELECT 出来的字段顺序必须和 INSERT 后面的字段列表一致。3.5 处理唯一键冲突如果表中已经存在唯一约束插入重复记录时会报错。比如student_no有唯一索引再次插入学号 1001 就会提示ERROR 1062 (23000): Duplicate entry 1001 for key student.uk_student_no有两种常见处理方式。第一种是使用INSERT IGNORE忽略冲突并保留原记录INSERT IGNORE INTO student (student_no, student_name, gender, class_no) VALUES (1001, 张三, M, 2024-01班);执行后返回Query OK, 0 rows affected说明重复数据被忽略。第二种是使用ON DUPLICATE KEY UPDATE在冲突时执行更新操作INSERT INTO student (student_no, student_name, gender, class_no) VALUES (1001, 张三, M, 2024-05班) ON DUPLICATE KEY UPDATE class_no VALUES(class_no);这条语句的意思是如果学号 1001 不存在就正常插入如果已经存在则把该学生的班级更新为2024-05班。高版本 MySQL 对VALUES()函数可能出现弃用提示但当前示例思路仍可运行生产环境请根据版本选择更合适的别名写法。4. 查询数据SELECT 的核心用法4.1 基础查询与字段别名SELECT 是使用频率最高的 SQL 语句。最简单的查询可以列出全部学生SELECT * FROM student;*表示返回所有字段适合快速查看数据但生产环境不建议在业务代码里经常使用SELECT *。因为表结构可能增加字段返回过多无用数据会增加网络传输和内存开销。更推荐按需列出字段并为字段设置可读的别名SELECT student_no AS sno, student_name AS name, class_no FROM student;这里AS用于给字段或表起别名。在 Java、Python 等语言中接收查询结果时清晰的别名能让字段映射更直观。4.2 WHERE 条件过滤与 NULL 判断WHERE 子句用于筛选符合条件的行。例如查询班级为2024-01班的学生SELECT student_no, student_name, class_no FROM student WHERE class_no 2024-01班;多个条件可以用 AND、OR 组合。查询成绩大于 80 分且考试日期在 2025 年之后的记录SELECT student_no, course_name, score, exam_date FROM score WHERE score 80 AND exam_date 2025-01-01;新手经常在 NULL 判断上出错。NULL 表示“未知值”不能用或!判断。比如学生表中有学生的gender为空正确的写法是SELECT student_no, student_name FROM student WHERE gender IS NULL;如果写成gender NULLMySQL 不会报错但查询结果永远是空因为 NULL 不等于任何值包括它自己。这一点在面试和日常开发中都经常被考察。4.3 排序 ORDER BY使用 ORDER BY 可以让查询结果按照指定字段排序。默认是升序 ASC也可以指定降序 DESC。查询成绩从高到低排列SELECT student_no, course_name, score FROM score ORDER BY score DESC;需要按多个字段排序时可以写多个排序键。比如先按成绩降序成绩相同则按考试日期升序SELECT student_no, course_name, score, exam_date FROM score ORDER BY score DESC, exam_date ASC;在 MySQL 默认字符集和排序规则下字符串比较通常不区分大小写。如果你希望区分大小写可以使用BINARY关键字或指定大小写敏感的排序规则。这个问题经常被忽视但在用户名、编码类字段查询时可能造成不符合预期的结果。4.4 聚合统计与分组 GROUP BY聚合函数可以将多行数据汇总成一个或多个统计值。常用的聚合函数有 COUNT、SUM、AVG、MAX、MIN。例如统计成绩表中共有多少条记录SELECT COUNT(*) AS total_cnt FROM score;统计每个学生参加了几门考试、平均分是多少SELECT student_no, COUNT(*) AS exam_cnt, ROUND(AVG(score), 2) AS avg_score FROM score GROUP BY student_no ORDER BY avg_score DESC;这里的ROUND(AVG(score), 2)表示对平均分四舍五入并保留两位小数。执行结果会按学生学号分组分别统计每个人的考试次数和平均成绩。WHERE 和 GROUP BY 的执行顺序需要特别注意WHERE 是在分组之前过滤原始行GROUP BY 之后如果想对分组结果再做过滤必须使用 HAVING而不能使用 WHERE。例如只保留平均分大于 70 的学生SELECT student_no, COUNT(*) AS exam_cnt, ROUND(AVG(score), 2) AS avg_score FROM score GROUP BY student_no HAVING AVG(score) 70 ORDER BY avg_score DESC;把HAVING AVG(score) 70换成WHERE AVG(score) 70会直接报错因为 WHERE 无法作用于聚合结果。4.5 分页 LIMIT当查询结果很多时可以用 LIMIT 限制返回条数也可以配合 OFFSET 实现分页。例如查询成绩最高的前 3 条记录SELECT student_no, course_name, score FROM score ORDER BY score DESC LIMIT 3;分页查询第 2 页每页 3 条SELECT student_no, course_name, score FROM score ORDER BY score DESC LIMIT 3 OFFSET 3;这里的OFFSET 3表示跳过前 3 条记录。如果只写LIMIT 3表示返回 3 条如果写LIMIT 3, 3则第一个 3 表示偏移量第二个 3 表示返回条数容易混淆实际项目中建议写清OFFSET可读性更好。4.6 多表关联查询 JOIN业务数据往往分散在多个表中查询时需要把表关联起来。比如想查看学生的姓名和对应成绩可以使用 JOINSELECT s.student_no, s.student_name, sc.course_name, sc.score FROM student s JOIN score sc ON s.student_no sc.student_no WHERE sc.course_name 数据库原理 ORDER BY sc.score DESC;这里s和sc分别是 student、score 表的别名。JOIN 的作用是把两个表中student_no相同的行连接在一起。如果希望查询所有学生以及他们的成绩情况即使某些学生没有成绩也要显示可以使用 LEFT JOINSELECT s.student_no, s.student_name, sc.course_name, sc.score FROM student s LEFT JOIN score sc ON s.student_no sc.student_no;LEFT JOIN 会保留左表中的所有行右表中没有匹配到数据时对应字段会显示为 NULL。这种写法在统计“哪些学生没有考试记录”时非常有用。5. 修改数据UPDATE 的正确姿势5.1 UPDATE 基础语法UPDATE 用于修改表中已有记录基础语法如下UPDATE score SET score 90.00 WHERE student_no 1001 AND course_name 数据库原理 AND exam_date 2025-01-10;执行后MySQL 会返回受影响的行数。如果该条件匹配到了 1 条记录就会返回Query OK, 1 row affected (0.01 sec)这里最关键的是 WHERE 条件。UPDATE 如果没有 WHERE会更新表中所有行。在开发环境和测试环境可能无所谓但在生产环境执行全表 UPDATE几乎等同于事故。5.2 更新字段自身进行计算修改数据时常需要基于原字段值进行计算。比如给某位学生的一门课程成绩加 5 分UPDATE score SET score score 5 WHERE student_no 1002 AND course_name 数据库原理;这里的score score 5表示读取当前成绩加上 5 后再写回字段。MySQL 的字段计算不需要额外使用 SELECT 先把值取出来直接写表达式即可。还有一种常见需求是根据条件给不同行设置不同的值可以用 CASE WHEN 实现。例如对 2025 年考试中的数据库原理成绩加 2 分对 Java 程序设计成绩加 1 分UPDATE score SET score score CASE course_name WHEN 数据库原理 THEN 2 WHEN Java程序设计 THEN 1 ELSE 0 END WHERE course_name IN (数据库原理, Java程序设计);CASE WHEN 让一条 UPDATE 可以处理更丰富的分支逻辑降低了在业务代码中逐条 update 的需求。5.3 UPDATE 的安全风险UPDATE 是数据操作中最需要谨慎对待的语句。建议在更新之前先用 SELECT 确认 WHERE 条件命中了哪些数据。例如原本要执行UPDATE score SET score 80 WHERE student_no 1002;可以先执行SELECT student_no, course_name, score FROM score WHERE student_no 1002;确认这些记录确实都是需要修改的目标。如果 SELECT 查出了意想不到的数据说明 WHERE 条件可能写错了需要立即停下来检查。在生产环境修改重要数据时更好做法是放到事务中执行更新后先查询验证确认无误再提交事务。这部分内容会在第 7 章详细展开。6. 删除数据DELETE 与 TRUNCATE 的区别6.1 按条件删除记录DELETE 用于删除表中符合条件的记录。例如删除学号为 1005 的学生成绩记录DELETE FROM score WHERE student_no 1005;如果 DELETE 不写 WHERE会清空整张表的数据DELETE FROM score;这种操作非常危险。虽然 InnoDB 引擎下不提交还能通过 ROLLBACK 回滚但在自动提交模式下相当于一瞬间清空整张表且恢复成本很高。平时练习和开发中一定要形成“DELET 前先 SELECT”的习惯。6.2 DELETE 与 TRUNCATE 的区别TRUNCATE TABLE 也可以清空表数据但它和 DELETE 有本质区别DELETE 是 DML可以带 WHERE可以配合事务回滚TRUNCATE 是 DDL不能带 WHERE执行后通常无法按事务回滚TRUNCATE 会重置 AUTO_INCREMENT 自增计数DELETE 默认不会TRUNCATE 清空大表的速度通常比 DELETE