读者打开 MySQL 表数据操作相关文章时最关心的往往不是某一两条命令而是增删改查怎么写才规范批量更新和删除如何避免锁表行转列怎么用 SQL 直接完成MySQL 8.0 和旧版本写法的差异在哪里。本文会围绕一张模拟“学生-课程-成绩”业务表展开覆盖插入、查询、排序、分组、聚合、更新、删除、事务和常见报错处理所有 SQL 都给出可执行示例适合初学者对照练习也适合开发者当作速查手册。1. MySQL 表数据操作核心能力速览能力项覆盖内容数据写入INSERT 单行插入、批量插入、INSERT IGNORE、ON DUPLICATE KEY UPDATE数据查询SELECT 条件过滤、模糊匹配、去重、逻辑运算查询排序ORDER BY 单字段排序、多字段排序、按中文或数字排序的注意点分组统计GROUP BY 配合聚合函数、HAVING 过滤、COUNT/SUM/AVG/MAX/MIN高级统计行转列、列转行、CASE WHEN 条件统计数据更新UPDATE 单表更新、多表关联更新、批量更新数据删除DELETE 单表删除、多表关联删除、TRUNCATE 与 DELETE 差异事务操作提交、回滚、事务隔离级别的初步观察安全边界锁等待、大批量操作拆分、权限控制本文使用的 MySQL 环境以 8.0 版本为参考多数 SQL 在 MySQL 5.7 同样可以运行但窗口函数、公用表表达式WITH 子句等特性需要 MySQL 8.0 支持。实际操作前建议先确认版本使用下面的命令查看SELECT VERSION();2. MySQL 表数据操作前置准备2.1 登录 MySQL 服务无论使用 Navicat、DataGrip 还是命令行工具第一步都是先连接 MySQL 服务。命令行连接方式mysql -u root -p如果提示ERROR 2002 (HY000): Cant connect to local MySQL server through socket /tmp/mysql.sock说明 MySQL 服务没有启动。在 Linux 系统上使用 systemd 启动服务后重试# 以 Ubuntu / CentOS 7 为例 systemctl start mysqld # 或 systemctl start mysql2.2 创建测试数据库表数据操作需要先有一个可以反复写入和删除的数据库。建议不要在正式业务库里试验本文创建一个专用测试库CREATE DATABASE IF NOT EXISTS student_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE student_db;2.3 创建学生表和成绩表这里设计一套简单但完整的测试模型学生表保存学生基本信息课程表保存课程信息成绩表保存学生选修课程后的分数。-- 学生表 CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 学生ID, stu_no VARCHAR(20) NOT NULL UNIQUE COMMENT 学号, name VARCHAR(50) NOT NULL COMMENT 姓名, gender CHAR(1) DEFAULT M COMMENT 性别 M男 F女, age INT DEFAULT 18 COMMENT 年龄, class_name VARCHAR(50) DEFAULT NULL COMMENT 班级, create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT学生信息表; -- 课程表 CREATE TABLE course ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 课程ID, course_no VARCHAR(20) NOT NULL UNIQUE COMMENT 课程编号, course_name VARCHAR(100) NOT NULL COMMENT 课程名称, credit DECIMAL(3,1) DEFAULT 2.0 COMMENT 学分 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT课程表; -- 成绩表 CREATE TABLE score ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 主键ID, student_id INT NOT NULL COMMENT 学生ID, course_id INT NOT NULL COMMENT 课程ID, score DECIMAL(5,1) DEFAULT NULL COMMENT 分数, exam_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 考试时间, UNIQUE KEY uk_stu_course (student_id, course_id), CONSTRAINT fk_score_student FOREIGN KEY (student_id) REFERENCES student(id), CONSTRAINT fk_score_course FOREIGN KEY (course_id) REFERENCES course(id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT成绩表;这里需要注意成绩表特意加上了UNIQUE KEY uk_stu_course (student_id, course_id)同一个学生同一门课只能保留一条成绩。很多初学者写成绩表时没有加唯一约束导致后续统计出现重复数据。2.4 插入基础测试数据后续章节的查询、排序、更新、删除操作都需要依赖数据先写入一批基础数据-- 插入课程数据 INSERT INTO course (course_no, course_name, credit) VALUES (C001, MySQL数据库, 3.0), (C002, Java程序设计, 4.0), (C003, Python数据分析, 2.5), (C004, 计算机网络, 3.5); -- 插入学生数据 INSERT INTO student (stu_no, name, gender, age, class_name) VALUES (2024001, 张伟, M, 20, 软件2401班), (2024002, 李娜, F, 19, 软件2401班), (2024003, 王强, M, 21, 软件2402班), (2024004, 赵敏, F, 20, 大数据2401班), (2024005, 刘洋, M, 22, 大数据2402班); -- 插入成绩数据 INSERT INTO score (student_id, course_id, score) VALUES (1, 1, 88.5), (1, 2, 92.0), (2, 1, 76.0), (2, 3, 85.5), (3, 1, 59.0), (3, 2, 66.5), (3, 4, 90.0), (4, 1, 95.0), (4, 3, 88.0), (4, 4, 72.5), (5, 2, 64.0), (5, 4, 58.5);3. MySQL 表数据操作之数据插入3.1 单行插入与批量插入INSERT 是最基础的表数据操作。写入单条记录时建议显式指定字段名这样后续增加字段时不会影响旧 SQLINSERT INTO student (stu_no, name, gender, age, class_name) VALUES (2024006, 陈静, F, 19, 软件2402班);批量插入比逐条插入效率更高尤其适合初始化数据和批量任务导入场景。JDBC 批量操作、命令行导入大量初始化数据时可以先把多条记录拼到一条 INSERT 语句中INSERT INTO course (course_no, course_name, credit) VALUES (C005, 操作系统, 4.0), (C006, 算法设计, 3.5), (C007, 软件工程, 2.0);有一条需要特别注意的经验一条 INSERT 语句插入的行数并非越多越好。当批量插入行数达到几千甚至上万行时单条 SQL 的执行时间会明显变长同时会占用较多内存和 binlog 写入资源。更稳妥的做法是把大批量数据拆成每条 500 到 1000 行的小批次分批执行。3.2 INSERT IGNORE 与冲突处理如果表中存在唯一索引插入重复数据时会直接报错ERROR 1062 (23000): Duplicate entry 2024001 for key student.uk_stu_no业务上常见的需求是数据存在就跳过不存在就插入。这时可以使用 INSERT IGNOREINSERT IGNORE INTO student (stu_no, name, gender, age, class_name) VALUES (2024001, 重复学号, M, 20, 测试班);执行后 MySQL 不会报错但受影响行数为 0。这种写法适合初始化脚本重复执行不会因为重复导入而中断任务。3.3 ON DUPLICATE KEY UPDATE 实现存在即更新比 INSERT IGNORE 更进一步的需求是记录不存在就插入记录存在就更新某些字段。成绩表录入分数时非常常用例如学生补考后要覆盖原成绩INSERT INTO score (student_id, course_id, score) VALUES (1, 1, 89.0) ON DUPLICATE KEY UPDATE score VALUES(score);MySQL 8.0.20 之后VALUES()函数在 ON DUPLICATE KEY UPDATE 子句中标记为即将弃用官方推荐使用别名语法。MySQL 8.0.20 及以上版本可以写成INSERT INTO score (student_id, course_id, score) VALUES (1, 1, 89.0) AS new_score ON DUPLICATE KEY UPDATE score new_score.score;这个细节在面试和代码审查中经常出现。如果当前项目使用的驱动版本或 MySQL 版本较老建议先用SELECT VERSION()确认版本再决定采用哪种写法。4. MySQL 表数据操作之条件查询4.1 WHERE 条件过滤取数据是表数据操作中占比最高的部分。通过 WHERE 限定范围避免 SELECT 全表扫描后到处翻数据-- 查询班级为软件2401班的学生 SELECT id, stu_no, name, gender, age, class_name FROM student WHERE class_name 软件2401班; -- 查询年龄大于等于20岁的学生 SELECT name, age, class_name FROM student WHERE age 20;多个条件组合时使用 AND、OR 和括号。要注意 AND 的优先级高于 OR混合使用时如果不加括号查询结果很容易和预期不一致-- 查询软件2401班的女生或者年龄大于21岁的学生 SELECT name, gender, age, class_name FROM student WHERE (class_name 软件2401班 AND gender F) OR age 21;4.2 LIKE 模糊匹配需要按姓名或班级模糊搜索使用 LIKE 配合通配符-- 查询姓张的学生 SELECT name, class_name FROM student WHERE name LIKE 张%; -- 查询名字包含“娜”的学生 SELECT name, class_name FROM student WHERE name LIKE %娜%;LIKE 以%开头时无法利用普通索引数据量大时会产生全表扫描。搜索“名字中间的字”这种需求在电商、办公系统里很常见一旦数据量大建议考虑全文索引或分词搜索方案而不是继续依赖 LIKE。4.3 IN 与 BETWEEN 区间查询使用 IN 可以匹配一组明确值SELECT course_no, course_name, credit FROM course WHERE course_no IN (C001, C004, C007);使用 BETWEEN AND 可以匹配连续范围SELECT id, score FROM score WHERE score BETWEEN 60 AND 90;BETWEEN 的边界是包含的也就是score 60 AND score 90。如果业务语言中“ 60 到 90”不包含端点就不要使用 BETWEEN。5. MySQL 表数据操作之排序与分页5.1 ORDER BY 单字段排序成绩排名是最典型的排序场景SELECT student_id, course_id, score FROM score WHERE course_id 1 ORDER BY score DESC;数字排序不是单纯看“大小关系”需要理解字段类型的影响。如果字段保存的是 VARCHAR 类型排序结果是字典序而不是自然大小序。例如字符串 58.5 会排在 6 前面。数字比较场景必须使用数值类型字段或者用 CAST 转换后再排序。5.2 多字段排序先按班级排序班级内部再按年龄从大到小排这是典型的多层级排序。SELECT name, class_name, age FROM student ORDER BY class_name ASC, age DESC;MySQL 会先按第一个字段排序第一个字段相同时才按第二个字段排序。5.3 分页查询 LIMIT分页查询在 Web 系统里非常常见。MySQL 使用 LIMIT 实现-- 查询第 1 页每页 5 条 SELECT id, stu_no, name, class_name FROM student ORDER BY id ASC LIMIT 0, 5; -- 查询第 2 页每页 5 条 SELECT id, stu_no, name, class_name FROM student ORDER BY id ASC LIMIT 5, 5;LIMIT 的偏移量计算公式是(页码 - 1) * 每页条数。这里需注意如果表数据量很大使用 LIMIT 100000, 5 会让 MySQL 扫描前面 10 万行后再返回 5 行越往后翻页越慢。更优的做法是用上一页最后一条记录的 id 作为条件SELECT id, stu_no, name, class_name FROM student WHERE id 100000 ORDER BY id ASC LIMIT 5;这种“键集分页”方式在百万元素级别的数据中性能优势非常明显。6. MySQL 表数据操作之聚合统计与行转列6.1 GROUP BY 配合聚合函数统计每个班级学生人数SELECT class_name, COUNT(*) AS student_count FROM student GROUP BY class_name;统计每门课程的平均分、最高分、最低分SELECT c.course_name, COUNT(s.id) AS exam_count, AVG(s.score) AS avg_score, MAX(s.score) AS max_score, MIN(s.score) AS min_score FROM course c LEFT JOIN score s ON c.id s.course_id GROUP BY c.course_name;6.2 HAVING 过滤分组结果WHERE 是在分组前过滤原始行HAVING 是在分组后过滤聚合结果。如果要查询平均分大于 80 的课程必须使用 HAVINGSELECT course_id, AVG(score) AS avg_score FROM score GROUP BY course_id HAVING AVG(score) 80;MySQL 有一个常见报错与分组统计直接相关。如果在 ONLY_FULL_GROUP_BY 开启时SELECT 里出现既不是聚合函数也不在 GROUP BY 子句中的字段MySQL 8.0 会把 SQL 直接拒绝。6.3 行转列实现行转列是数据报表开发中非常常见的需求。原表每一行是一个学生的一门成绩现在需要转成一行显示一个学生的多门成绩。使用 CASE WHEN 聚合函数SELECT st.name AS 学生姓名, MAX(CASE WHEN co.course_name MySQL数据库 THEN sc.score END) AS MySQL成绩, MAX(CASE WHEN co.course_name Java程序设计 THEN sc.score END) AS Java成绩, MAX(CASE WHEN co.course_name Python数据分析 THEN sc.score END) AS Python成绩 FROM student st LEFT JOIN score sc ON st.id sc.student_id LEFT JOIN course co ON sc.course_id co.id GROUP BY st.id, st.name ORDER BY st.id;执行后可以看到数据布局从纵向变成了横向。行转列的核心是先用 CASE WHEN 把原始值映射到对应列再用 MAX 或 SUM 对分组内多行做聚合消除 NULL适用于列数固定、取值范围可枚举的报表需求。如果你的 MySQL 版本是 8.0还能使用窗口函数做更灵活的转换。不过窗口函数更适合排名、累计值场景常规行转列依旧建议使用 CASE WHEN。6.4 列转行反向操作“列转行”在 MySQL 中通过 UNION ALL 实现SELECT id, 数学 AS course_name, math_score AS score FROM temp_score UNION ALL SELECT id, 语文, chinese_score FROM temp_score UNION ALL SELECT id, 英语, english_score FROM temp_score ORDER BY id;这种操作在数据清洗、导入 ETL 和自定义报表模块中比较常见。7. MySQL 表数据操作之更新与删除7.1 UPDATE 基础更新修改数据前最优先要做的是确认 WHERE 条件准确。例如将学号为 2024003 的学生的班级改为“软件2403班”UPDATE student SET class_name 软件2403班 WHERE stu_no 2024003;如果不写 WHEREMySQL 会更新整张表这是生产环境最危险的操作之一。执行 UPDATE 前可以先 SELECT 一下条件范围确认打算影响的行数。7.2 UPDATE 多表关联更新学生表和成绩表需要一起同步数据时可以考虑多表关联更新。例如将所有姓“王”的同学的 MySQL 成绩提高 5 分UPDATE score sc JOIN student st ON sc.student_id st.id JOIN course co ON sc.course_id co.id SET sc.score sc.score 5 WHERE st.name LIKE 王% AND co.course_name MySQL数据库;先 JOIN 查出目标集合再进行 SET 更新逻辑比子查询写法直观。7.3 DELETE 与 TRUNCATE 差异DELETE 是行级删除可以带条件删除后自增 ID 不会重置。TRUNCATE 是重建表并清空表数据不能带条件-- 删除指定学生 DELETE FROM student WHERE stu_no 2024006; -- 清空成绩表 TRUNCATE TABLE score;TRUNCATE 操作会重置 AUTO_INCREMENT而且不逐行记录 binlog执行速度通常远高于 DELETE 全表。对正式业务表要谨慎TRUNCATE 一旦执行很难通过常规的事务回滚恢复数据。7.4 大批量删除的安全操作方式删除表中大量数据例如超过总行数一半的记录时直接 DELETE 会产生大量行锁、占用 undo log 并导致主从延迟。更稳妥的方法是分批循环删除DELETE FROM score WHERE score 60 LIMIT 1000;在确保业务允许的前提下反复执行直到删除完毕。配合 SELECT COUNT(*) 观察剩余数量可以避免一次性锁定大量行。8. MySQL 表数据操作之事务与锁8.1 事务的基本使用多条 INSERT、UPDATE、DELETE 需要作为一个整体提交或回滚时要使用事务。例如录入成绩时同时更新成绩表和统计表如果第二步失败第一步不应保留START TRANSACTION; UPDATE score SET score 90.0 WHERE student_id 1 AND course_id 1; UPDATE student_stat SET total_exam_count total_exam_count 1 WHERE student_id 1; -- 确认无误后提交 COMMIT;事务执行过程中如果发现异常可以执行 ROLLBACK 回滚到事务开始状态ROLLBACK;8.2 锁表与锁等待当两个事务同时修改同一行数据时后提交的事务需要等待先提交的事务释放锁。初学者容易遇到的一个场景是一个事务里执行了 UPDATE但迟迟没有 COMMIT另一个事务更新同一行时卡住直到ERROR 1205: Lock wait timeout exceeded。观察当前锁等待状态SHOW PROCESSLIST;也可以查看事务和锁相关系统数据SELECT * FROM performance_schema.data_locks; SELECT * FROM information_schema.innodb_trx;如果开发环境出现锁等待超时最直接的手段是找到长时间未提交的事务并结束它-- 找到 trx_mysql_thread_id 后执行 KILL 12345;排查生产环境锁问题时不能只盯着 SQL 本身还需要看是否有事务遗漏了 COMMIT、是否有 SELECT ... FOR UPDATE 范围过大以及连接池中是否存在长期空闲但未关闭的事务。8.3 模拟锁等待可以在两个 MySQL 会话中快速验证锁等待会话 ASTART TRANSACTION; UPDATE score SET score score 1 WHERE student_id 1 AND course_id 1; -- 不提交会话 BSTART TRANSACTION; UPDATE score SET score score 1 WHERE student_id 1 AND course_id 1;会话 B 会一直阻塞到会话 A 提交或事务超时。这个场景适合用来练习理解 InnoDB 行锁机制实际开发时要同时提交会话 A 的修改COMMIT;9. MySQL 表数据操作常见报错与排查方法问题现象可能原因排查方式解决方案ERROR 1062: Duplicate entry插入数据与唯一索引冲突查看唯一索引字段改用 INSERT IGNORE 或 ON DUPLICATE KEY UPDATEERROR 2002: 无法通过 socket 连接 MySQL服务未启动或 socket 路径不一致检查服务状态启动 MySQL 服务查询结果排序不对字段类型是 VARCHAR查看字段类型使用 CAST 转数值排序分组查询报 ONLY_FULL_GROUP_BY 错误SQL 中出现了非聚合字段检查 SELECT 字段改为聚合字段或在 GROUP BY 中添加字段更新语句卡住或超时存在未提交事务持锁较长查询 information_schema.innodb_trx提交或回滚长时间事务DELETE 清空表后自增顺序变了使用 TRUNCATE 导致自增重置无需要保留编号连续时改用 DELETECSV 导入数据后中文乱码连接字符集和表字符集不一致检查 character_set_clientSET NAMES utf8mb4大量数据删除时主从延迟大单条 DELETE 删除行数过多查看从库延迟状态分批删除每次限制少量行数JDBC 批量插入速度很慢未配置 rewriteBatchedStatements查看连接参数JDBC URL 增加 rewriteBatchedStatementstrue查询用了索引还是很慢条件字段做了函数处理或隐式转换EXPLAIN 查看执行计划避免在索引列上使用函数保持字段类型一致10. MySQL 表数据操作性能观察与安全实践10.1 使用 EXPLAIN 观察 SQL 执行计划不管是查询慢还是更新慢第一步应该是分析执行计划EXPLAIN SELECT st.name, sc.score FROM score sc JOIN student st ON sc.student_id st.id WHERE sc.course_id 1 ORDER BY sc.score DESC;EXPLAIN 的结果中重点看type、possible_keys、key、rows这几列。rows数值越大说明 MySQL 预估扫描的行数越多。如果type为 ALL说明正在做全表扫描数据量较大时一定要检查索引设计和 WHERE 条件写法。10.2 慢查询日志定位问题 SQL生产环境定位慢 SQL最直接的手段是开启慢查询日志SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;通过观察日志可以知道哪些表、哪些语句耗时超过阈值再针对具体 SQL 分析索引或改写执行方式。10.3 表数据操作安全规范先列几条容易踩坑的规范**UPDATE 和 DELETE 必须有明确 WHERE要求先 SELECT 影响范围。**执行计划内的任何一次性全表更新都应反复确认。**涉及金额、分数、库存等数据的幂等操作要使用唯一约束。**例如成绩表防止同一学生同一课程重复录入使用联合唯一索引是最可靠的手段。**大批量更新、删除要分批执行。**避免一条 SQL 锁大量行减少主从复制延迟。**对现有数据做结构变更必须先备份。**例如新增字段前如果有默认值回填需求要先小范围验证。**线上不直接操作业务库。**测试表数据操作时使用本地或测试环境的专用库不要拿生产环境当练习场。**超大批量导入时先关闭非必要索引或延迟创建索引。**如果一张表已经有几百万行数据再插入几十万行建议考虑先禁用或删除多余二级索引导完再重建速度差距可能非常明显。11. MySQL 表数据操作常见场景速查11.1 学生课程成绩统计场景统计每个学生的总成绩、平均成绩、选课数量并按总成绩降序排列SELECT st.id, st.name, COUNT(sc.course_id) AS course_count, IFNULL(SUM(sc.score), 0) AS total_score, IFNULL(AVG(sc.score), 0) AS avg_score FROM student st LEFT JOIN score sc ON st.id sc.student_id GROUP BY st.id, st.name ORDER BY total_score DESC;使用 LEFT JOIN 的原因是有可能出现没有成绩的学生需要保留学生信息而不是只展示有成绩的人。统计时还要注意空值问题使用 IFNULL 将 NULL 转为 0否则聚合结果显示 NULL。11.2 成绩排名场景查询 MySQL 数据库这门课排名前三的学生SELECT st.name, sc.score FROM score sc JOIN student st ON sc.student_id st.id JOIN course co ON sc.course_id co.id WHERE co.course_name MySQL数据库 ORDER BY sc.score DESC LIMIT 3;如果需要保留并列排名MySQL 5.7 及以下版本实现比较繁琐MySQL 8.0 可以使用窗口函数 RANKSELECT st.name, sc.score, RANK() OVER (ORDER BY sc.score DESC) AS rank_no FROM score sc JOIN student st ON sc.student_id st.id JOIN course co ON sc.course_id co.id WHERE co.course_name MySQL数据库;11.3 统计各分数区间人数把一个班的成绩按 90-100、80-89、60-79、0-59 分段统计SELECT CASE WHEN score 90 THEN 90-100 WHEN score 80 THEN 80-89 WHEN score 60 THEN 60-79 ELSE 0-59 END AS score_range, COUNT(*) AS student_count FROM score JOIN course co ON score.course_id co.id WHERE co.course_name MySQL数据库 GROUP BY score_range ORDER BY score_range ASC;这种 CASE WHEN GROUP BY 的分段统计写法适合成绩分析、订单金额区间统计、时长分布统计等场景掌握后可以迁移到很多业务模块。12. 建议收藏的表数据操作自检清单结合前面所有内容整理一份可以直接用于日常开发的自检清单查询数据前确认表字段是否允许 NULL。排序时确认字段是数值类型还是字符串类型。分组统计时确认 SELECT 字段是否符合 ONLY_FULL_GROUP_BY 约束。行转列需求先确认取值集合是否固定不固定时考虑改用动态 SQL 或前端二次处理。更新和删除前先运行同条件 SELECT预估影响行数。大批量数据变更必须放到事务中分批执行并设置合理超时时间。锁等待发生时立即查询 innodb_trx确认是否存在长时间未提交事务。慢查询优先用 EXPLAIN 判断是否有全表扫描。导入数据如果出现中文乱码先执行SET NAMES utf8mb4再确认表的默认字符集。使用 JDBC 批处理时在连接 URL 中增加rewriteBatchedStatementstrue可以把多条 INSERT 合并提交执行效率提升非常明显。这些自检项覆盖了 MySQL 表数据操作的主要风险点可以作为平时环境测试和生产发布前的检查基线。MySQL 表数据操作的思路其实很简单先明确要操作的数据范围再选择合适的写入、更新、删除方式最后通过 EXPLAIN、慢查询日志和事务状态确认执行是否符合预期。前面提供的 SQL 示例都可以直接复制到本地测试库中运行。建议下一步练习时把学生、成绩两张表换成自己正在开发的业务表分别测试普通统计、行转列、批量更新和事务回滚遇到锁等待或分组报错时回到常见问题一节核对。这套 SQL 积累下来的经验和排查习惯在后续学习存储过程、触发器、JDBC 批量处理和分库分表时依然能复用可以先收藏这篇文章作为日常参考。
MySQL表数据操作实战：从增删改查到性能与安全
读者打开 MySQL 表数据操作相关文章时最关心的往往不是某一两条命令而是增删改查怎么写才规范批量更新和删除如何避免锁表行转列怎么用 SQL 直接完成MySQL 8.0 和旧版本写法的差异在哪里。本文会围绕一张模拟“学生-课程-成绩”业务表展开覆盖插入、查询、排序、分组、聚合、更新、删除、事务和常见报错处理所有 SQL 都给出可执行示例适合初学者对照练习也适合开发者当作速查手册。1. MySQL 表数据操作核心能力速览能力项覆盖内容数据写入INSERT 单行插入、批量插入、INSERT IGNORE、ON DUPLICATE KEY UPDATE数据查询SELECT 条件过滤、模糊匹配、去重、逻辑运算查询排序ORDER BY 单字段排序、多字段排序、按中文或数字排序的注意点分组统计GROUP BY 配合聚合函数、HAVING 过滤、COUNT/SUM/AVG/MAX/MIN高级统计行转列、列转行、CASE WHEN 条件统计数据更新UPDATE 单表更新、多表关联更新、批量更新数据删除DELETE 单表删除、多表关联删除、TRUNCATE 与 DELETE 差异事务操作提交、回滚、事务隔离级别的初步观察安全边界锁等待、大批量操作拆分、权限控制本文使用的 MySQL 环境以 8.0 版本为参考多数 SQL 在 MySQL 5.7 同样可以运行但窗口函数、公用表表达式WITH 子句等特性需要 MySQL 8.0 支持。实际操作前建议先确认版本使用下面的命令查看SELECT VERSION();2. MySQL 表数据操作前置准备2.1 登录 MySQL 服务无论使用 Navicat、DataGrip 还是命令行工具第一步都是先连接 MySQL 服务。命令行连接方式mysql -u root -p如果提示ERROR 2002 (HY000): Cant connect to local MySQL server through socket /tmp/mysql.sock说明 MySQL 服务没有启动。在 Linux 系统上使用 systemd 启动服务后重试# 以 Ubuntu / CentOS 7 为例 systemctl start mysqld # 或 systemctl start mysql2.2 创建测试数据库表数据操作需要先有一个可以反复写入和删除的数据库。建议不要在正式业务库里试验本文创建一个专用测试库CREATE DATABASE IF NOT EXISTS student_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE student_db;2.3 创建学生表和成绩表这里设计一套简单但完整的测试模型学生表保存学生基本信息课程表保存课程信息成绩表保存学生选修课程后的分数。-- 学生表 CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 学生ID, stu_no VARCHAR(20) NOT NULL UNIQUE COMMENT 学号, name VARCHAR(50) NOT NULL COMMENT 姓名, gender CHAR(1) DEFAULT M COMMENT 性别 M男 F女, age INT DEFAULT 18 COMMENT 年龄, class_name VARCHAR(50) DEFAULT NULL COMMENT 班级, create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT学生信息表; -- 课程表 CREATE TABLE course ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 课程ID, course_no VARCHAR(20) NOT NULL UNIQUE COMMENT 课程编号, course_name VARCHAR(100) NOT NULL COMMENT 课程名称, credit DECIMAL(3,1) DEFAULT 2.0 COMMENT 学分 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT课程表; -- 成绩表 CREATE TABLE score ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 主键ID, student_id INT NOT NULL COMMENT 学生ID, course_id INT NOT NULL COMMENT 课程ID, score DECIMAL(5,1) DEFAULT NULL COMMENT 分数, exam_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 考试时间, UNIQUE KEY uk_stu_course (student_id, course_id), CONSTRAINT fk_score_student FOREIGN KEY (student_id) REFERENCES student(id), CONSTRAINT fk_score_course FOREIGN KEY (course_id) REFERENCES course(id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT成绩表;这里需要注意成绩表特意加上了UNIQUE KEY uk_stu_course (student_id, course_id)同一个学生同一门课只能保留一条成绩。很多初学者写成绩表时没有加唯一约束导致后续统计出现重复数据。2.4 插入基础测试数据后续章节的查询、排序、更新、删除操作都需要依赖数据先写入一批基础数据-- 插入课程数据 INSERT INTO course (course_no, course_name, credit) VALUES (C001, MySQL数据库, 3.0), (C002, Java程序设计, 4.0), (C003, Python数据分析, 2.5), (C004, 计算机网络, 3.5); -- 插入学生数据 INSERT INTO student (stu_no, name, gender, age, class_name) VALUES (2024001, 张伟, M, 20, 软件2401班), (2024002, 李娜, F, 19, 软件2401班), (2024003, 王强, M, 21, 软件2402班), (2024004, 赵敏, F, 20, 大数据2401班), (2024005, 刘洋, M, 22, 大数据2402班); -- 插入成绩数据 INSERT INTO score (student_id, course_id, score) VALUES (1, 1, 88.5), (1, 2, 92.0), (2, 1, 76.0), (2, 3, 85.5), (3, 1, 59.0), (3, 2, 66.5), (3, 4, 90.0), (4, 1, 95.0), (4, 3, 88.0), (4, 4, 72.5), (5, 2, 64.0), (5, 4, 58.5);3. MySQL 表数据操作之数据插入3.1 单行插入与批量插入INSERT 是最基础的表数据操作。写入单条记录时建议显式指定字段名这样后续增加字段时不会影响旧 SQLINSERT INTO student (stu_no, name, gender, age, class_name) VALUES (2024006, 陈静, F, 19, 软件2402班);批量插入比逐条插入效率更高尤其适合初始化数据和批量任务导入场景。JDBC 批量操作、命令行导入大量初始化数据时可以先把多条记录拼到一条 INSERT 语句中INSERT INTO course (course_no, course_name, credit) VALUES (C005, 操作系统, 4.0), (C006, 算法设计, 3.5), (C007, 软件工程, 2.0);有一条需要特别注意的经验一条 INSERT 语句插入的行数并非越多越好。当批量插入行数达到几千甚至上万行时单条 SQL 的执行时间会明显变长同时会占用较多内存和 binlog 写入资源。更稳妥的做法是把大批量数据拆成每条 500 到 1000 行的小批次分批执行。3.2 INSERT IGNORE 与冲突处理如果表中存在唯一索引插入重复数据时会直接报错ERROR 1062 (23000): Duplicate entry 2024001 for key student.uk_stu_no业务上常见的需求是数据存在就跳过不存在就插入。这时可以使用 INSERT IGNOREINSERT IGNORE INTO student (stu_no, name, gender, age, class_name) VALUES (2024001, 重复学号, M, 20, 测试班);执行后 MySQL 不会报错但受影响行数为 0。这种写法适合初始化脚本重复执行不会因为重复导入而中断任务。3.3 ON DUPLICATE KEY UPDATE 实现存在即更新比 INSERT IGNORE 更进一步的需求是记录不存在就插入记录存在就更新某些字段。成绩表录入分数时非常常用例如学生补考后要覆盖原成绩INSERT INTO score (student_id, course_id, score) VALUES (1, 1, 89.0) ON DUPLICATE KEY UPDATE score VALUES(score);MySQL 8.0.20 之后VALUES()函数在 ON DUPLICATE KEY UPDATE 子句中标记为即将弃用官方推荐使用别名语法。MySQL 8.0.20 及以上版本可以写成INSERT INTO score (student_id, course_id, score) VALUES (1, 1, 89.0) AS new_score ON DUPLICATE KEY UPDATE score new_score.score;这个细节在面试和代码审查中经常出现。如果当前项目使用的驱动版本或 MySQL 版本较老建议先用SELECT VERSION()确认版本再决定采用哪种写法。4. MySQL 表数据操作之条件查询4.1 WHERE 条件过滤取数据是表数据操作中占比最高的部分。通过 WHERE 限定范围避免 SELECT 全表扫描后到处翻数据-- 查询班级为软件2401班的学生 SELECT id, stu_no, name, gender, age, class_name FROM student WHERE class_name 软件2401班; -- 查询年龄大于等于20岁的学生 SELECT name, age, class_name FROM student WHERE age 20;多个条件组合时使用 AND、OR 和括号。要注意 AND 的优先级高于 OR混合使用时如果不加括号查询结果很容易和预期不一致-- 查询软件2401班的女生或者年龄大于21岁的学生 SELECT name, gender, age, class_name FROM student WHERE (class_name 软件2401班 AND gender F) OR age 21;4.2 LIKE 模糊匹配需要按姓名或班级模糊搜索使用 LIKE 配合通配符-- 查询姓张的学生 SELECT name, class_name FROM student WHERE name LIKE 张%; -- 查询名字包含“娜”的学生 SELECT name, class_name FROM student WHERE name LIKE %娜%;LIKE 以%开头时无法利用普通索引数据量大时会产生全表扫描。搜索“名字中间的字”这种需求在电商、办公系统里很常见一旦数据量大建议考虑全文索引或分词搜索方案而不是继续依赖 LIKE。4.3 IN 与 BETWEEN 区间查询使用 IN 可以匹配一组明确值SELECT course_no, course_name, credit FROM course WHERE course_no IN (C001, C004, C007);使用 BETWEEN AND 可以匹配连续范围SELECT id, score FROM score WHERE score BETWEEN 60 AND 90;BETWEEN 的边界是包含的也就是score 60 AND score 90。如果业务语言中“ 60 到 90”不包含端点就不要使用 BETWEEN。5. MySQL 表数据操作之排序与分页5.1 ORDER BY 单字段排序成绩排名是最典型的排序场景SELECT student_id, course_id, score FROM score WHERE course_id 1 ORDER BY score DESC;数字排序不是单纯看“大小关系”需要理解字段类型的影响。如果字段保存的是 VARCHAR 类型排序结果是字典序而不是自然大小序。例如字符串 58.5 会排在 6 前面。数字比较场景必须使用数值类型字段或者用 CAST 转换后再排序。5.2 多字段排序先按班级排序班级内部再按年龄从大到小排这是典型的多层级排序。SELECT name, class_name, age FROM student ORDER BY class_name ASC, age DESC;MySQL 会先按第一个字段排序第一个字段相同时才按第二个字段排序。5.3 分页查询 LIMIT分页查询在 Web 系统里非常常见。MySQL 使用 LIMIT 实现-- 查询第 1 页每页 5 条 SELECT id, stu_no, name, class_name FROM student ORDER BY id ASC LIMIT 0, 5; -- 查询第 2 页每页 5 条 SELECT id, stu_no, name, class_name FROM student ORDER BY id ASC LIMIT 5, 5;LIMIT 的偏移量计算公式是(页码 - 1) * 每页条数。这里需注意如果表数据量很大使用 LIMIT 100000, 5 会让 MySQL 扫描前面 10 万行后再返回 5 行越往后翻页越慢。更优的做法是用上一页最后一条记录的 id 作为条件SELECT id, stu_no, name, class_name FROM student WHERE id 100000 ORDER BY id ASC LIMIT 5;这种“键集分页”方式在百万元素级别的数据中性能优势非常明显。6. MySQL 表数据操作之聚合统计与行转列6.1 GROUP BY 配合聚合函数统计每个班级学生人数SELECT class_name, COUNT(*) AS student_count FROM student GROUP BY class_name;统计每门课程的平均分、最高分、最低分SELECT c.course_name, COUNT(s.id) AS exam_count, AVG(s.score) AS avg_score, MAX(s.score) AS max_score, MIN(s.score) AS min_score FROM course c LEFT JOIN score s ON c.id s.course_id GROUP BY c.course_name;6.2 HAVING 过滤分组结果WHERE 是在分组前过滤原始行HAVING 是在分组后过滤聚合结果。如果要查询平均分大于 80 的课程必须使用 HAVINGSELECT course_id, AVG(score) AS avg_score FROM score GROUP BY course_id HAVING AVG(score) 80;MySQL 有一个常见报错与分组统计直接相关。如果在 ONLY_FULL_GROUP_BY 开启时SELECT 里出现既不是聚合函数也不在 GROUP BY 子句中的字段MySQL 8.0 会把 SQL 直接拒绝。6.3 行转列实现行转列是数据报表开发中非常常见的需求。原表每一行是一个学生的一门成绩现在需要转成一行显示一个学生的多门成绩。使用 CASE WHEN 聚合函数SELECT st.name AS 学生姓名, MAX(CASE WHEN co.course_name MySQL数据库 THEN sc.score END) AS MySQL成绩, MAX(CASE WHEN co.course_name Java程序设计 THEN sc.score END) AS Java成绩, MAX(CASE WHEN co.course_name Python数据分析 THEN sc.score END) AS Python成绩 FROM student st LEFT JOIN score sc ON st.id sc.student_id LEFT JOIN course co ON sc.course_id co.id GROUP BY st.id, st.name ORDER BY st.id;执行后可以看到数据布局从纵向变成了横向。行转列的核心是先用 CASE WHEN 把原始值映射到对应列再用 MAX 或 SUM 对分组内多行做聚合消除 NULL适用于列数固定、取值范围可枚举的报表需求。如果你的 MySQL 版本是 8.0还能使用窗口函数做更灵活的转换。不过窗口函数更适合排名、累计值场景常规行转列依旧建议使用 CASE WHEN。6.4 列转行反向操作“列转行”在 MySQL 中通过 UNION ALL 实现SELECT id, 数学 AS course_name, math_score AS score FROM temp_score UNION ALL SELECT id, 语文, chinese_score FROM temp_score UNION ALL SELECT id, 英语, english_score FROM temp_score ORDER BY id;这种操作在数据清洗、导入 ETL 和自定义报表模块中比较常见。7. MySQL 表数据操作之更新与删除7.1 UPDATE 基础更新修改数据前最优先要做的是确认 WHERE 条件准确。例如将学号为 2024003 的学生的班级改为“软件2403班”UPDATE student SET class_name 软件2403班 WHERE stu_no 2024003;如果不写 WHEREMySQL 会更新整张表这是生产环境最危险的操作之一。执行 UPDATE 前可以先 SELECT 一下条件范围确认打算影响的行数。7.2 UPDATE 多表关联更新学生表和成绩表需要一起同步数据时可以考虑多表关联更新。例如将所有姓“王”的同学的 MySQL 成绩提高 5 分UPDATE score sc JOIN student st ON sc.student_id st.id JOIN course co ON sc.course_id co.id SET sc.score sc.score 5 WHERE st.name LIKE 王% AND co.course_name MySQL数据库;先 JOIN 查出目标集合再进行 SET 更新逻辑比子查询写法直观。7.3 DELETE 与 TRUNCATE 差异DELETE 是行级删除可以带条件删除后自增 ID 不会重置。TRUNCATE 是重建表并清空表数据不能带条件-- 删除指定学生 DELETE FROM student WHERE stu_no 2024006; -- 清空成绩表 TRUNCATE TABLE score;TRUNCATE 操作会重置 AUTO_INCREMENT而且不逐行记录 binlog执行速度通常远高于 DELETE 全表。对正式业务表要谨慎TRUNCATE 一旦执行很难通过常规的事务回滚恢复数据。7.4 大批量删除的安全操作方式删除表中大量数据例如超过总行数一半的记录时直接 DELETE 会产生大量行锁、占用 undo log 并导致主从延迟。更稳妥的方法是分批循环删除DELETE FROM score WHERE score 60 LIMIT 1000;在确保业务允许的前提下反复执行直到删除完毕。配合 SELECT COUNT(*) 观察剩余数量可以避免一次性锁定大量行。8. MySQL 表数据操作之事务与锁8.1 事务的基本使用多条 INSERT、UPDATE、DELETE 需要作为一个整体提交或回滚时要使用事务。例如录入成绩时同时更新成绩表和统计表如果第二步失败第一步不应保留START TRANSACTION; UPDATE score SET score 90.0 WHERE student_id 1 AND course_id 1; UPDATE student_stat SET total_exam_count total_exam_count 1 WHERE student_id 1; -- 确认无误后提交 COMMIT;事务执行过程中如果发现异常可以执行 ROLLBACK 回滚到事务开始状态ROLLBACK;8.2 锁表与锁等待当两个事务同时修改同一行数据时后提交的事务需要等待先提交的事务释放锁。初学者容易遇到的一个场景是一个事务里执行了 UPDATE但迟迟没有 COMMIT另一个事务更新同一行时卡住直到ERROR 1205: Lock wait timeout exceeded。观察当前锁等待状态SHOW PROCESSLIST;也可以查看事务和锁相关系统数据SELECT * FROM performance_schema.data_locks; SELECT * FROM information_schema.innodb_trx;如果开发环境出现锁等待超时最直接的手段是找到长时间未提交的事务并结束它-- 找到 trx_mysql_thread_id 后执行 KILL 12345;排查生产环境锁问题时不能只盯着 SQL 本身还需要看是否有事务遗漏了 COMMIT、是否有 SELECT ... FOR UPDATE 范围过大以及连接池中是否存在长期空闲但未关闭的事务。8.3 模拟锁等待可以在两个 MySQL 会话中快速验证锁等待会话 ASTART TRANSACTION; UPDATE score SET score score 1 WHERE student_id 1 AND course_id 1; -- 不提交会话 BSTART TRANSACTION; UPDATE score SET score score 1 WHERE student_id 1 AND course_id 1;会话 B 会一直阻塞到会话 A 提交或事务超时。这个场景适合用来练习理解 InnoDB 行锁机制实际开发时要同时提交会话 A 的修改COMMIT;9. MySQL 表数据操作常见报错与排查方法问题现象可能原因排查方式解决方案ERROR 1062: Duplicate entry插入数据与唯一索引冲突查看唯一索引字段改用 INSERT IGNORE 或 ON DUPLICATE KEY UPDATEERROR 2002: 无法通过 socket 连接 MySQL服务未启动或 socket 路径不一致检查服务状态启动 MySQL 服务查询结果排序不对字段类型是 VARCHAR查看字段类型使用 CAST 转数值排序分组查询报 ONLY_FULL_GROUP_BY 错误SQL 中出现了非聚合字段检查 SELECT 字段改为聚合字段或在 GROUP BY 中添加字段更新语句卡住或超时存在未提交事务持锁较长查询 information_schema.innodb_trx提交或回滚长时间事务DELETE 清空表后自增顺序变了使用 TRUNCATE 导致自增重置无需要保留编号连续时改用 DELETECSV 导入数据后中文乱码连接字符集和表字符集不一致检查 character_set_clientSET NAMES utf8mb4大量数据删除时主从延迟大单条 DELETE 删除行数过多查看从库延迟状态分批删除每次限制少量行数JDBC 批量插入速度很慢未配置 rewriteBatchedStatements查看连接参数JDBC URL 增加 rewriteBatchedStatementstrue查询用了索引还是很慢条件字段做了函数处理或隐式转换EXPLAIN 查看执行计划避免在索引列上使用函数保持字段类型一致10. MySQL 表数据操作性能观察与安全实践10.1 使用 EXPLAIN 观察 SQL 执行计划不管是查询慢还是更新慢第一步应该是分析执行计划EXPLAIN SELECT st.name, sc.score FROM score sc JOIN student st ON sc.student_id st.id WHERE sc.course_id 1 ORDER BY sc.score DESC;EXPLAIN 的结果中重点看type、possible_keys、key、rows这几列。rows数值越大说明 MySQL 预估扫描的行数越多。如果type为 ALL说明正在做全表扫描数据量较大时一定要检查索引设计和 WHERE 条件写法。10.2 慢查询日志定位问题 SQL生产环境定位慢 SQL最直接的手段是开启慢查询日志SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;通过观察日志可以知道哪些表、哪些语句耗时超过阈值再针对具体 SQL 分析索引或改写执行方式。10.3 表数据操作安全规范先列几条容易踩坑的规范**UPDATE 和 DELETE 必须有明确 WHERE要求先 SELECT 影响范围。**执行计划内的任何一次性全表更新都应反复确认。**涉及金额、分数、库存等数据的幂等操作要使用唯一约束。**例如成绩表防止同一学生同一课程重复录入使用联合唯一索引是最可靠的手段。**大批量更新、删除要分批执行。**避免一条 SQL 锁大量行减少主从复制延迟。**对现有数据做结构变更必须先备份。**例如新增字段前如果有默认值回填需求要先小范围验证。**线上不直接操作业务库。**测试表数据操作时使用本地或测试环境的专用库不要拿生产环境当练习场。**超大批量导入时先关闭非必要索引或延迟创建索引。**如果一张表已经有几百万行数据再插入几十万行建议考虑先禁用或删除多余二级索引导完再重建速度差距可能非常明显。11. MySQL 表数据操作常见场景速查11.1 学生课程成绩统计场景统计每个学生的总成绩、平均成绩、选课数量并按总成绩降序排列SELECT st.id, st.name, COUNT(sc.course_id) AS course_count, IFNULL(SUM(sc.score), 0) AS total_score, IFNULL(AVG(sc.score), 0) AS avg_score FROM student st LEFT JOIN score sc ON st.id sc.student_id GROUP BY st.id, st.name ORDER BY total_score DESC;使用 LEFT JOIN 的原因是有可能出现没有成绩的学生需要保留学生信息而不是只展示有成绩的人。统计时还要注意空值问题使用 IFNULL 将 NULL 转为 0否则聚合结果显示 NULL。11.2 成绩排名场景查询 MySQL 数据库这门课排名前三的学生SELECT st.name, sc.score FROM score sc JOIN student st ON sc.student_id st.id JOIN course co ON sc.course_id co.id WHERE co.course_name MySQL数据库 ORDER BY sc.score DESC LIMIT 3;如果需要保留并列排名MySQL 5.7 及以下版本实现比较繁琐MySQL 8.0 可以使用窗口函数 RANKSELECT st.name, sc.score, RANK() OVER (ORDER BY sc.score DESC) AS rank_no FROM score sc JOIN student st ON sc.student_id st.id JOIN course co ON sc.course_id co.id WHERE co.course_name MySQL数据库;11.3 统计各分数区间人数把一个班的成绩按 90-100、80-89、60-79、0-59 分段统计SELECT CASE WHEN score 90 THEN 90-100 WHEN score 80 THEN 80-89 WHEN score 60 THEN 60-79 ELSE 0-59 END AS score_range, COUNT(*) AS student_count FROM score JOIN course co ON score.course_id co.id WHERE co.course_name MySQL数据库 GROUP BY score_range ORDER BY score_range ASC;这种 CASE WHEN GROUP BY 的分段统计写法适合成绩分析、订单金额区间统计、时长分布统计等场景掌握后可以迁移到很多业务模块。12. 建议收藏的表数据操作自检清单结合前面所有内容整理一份可以直接用于日常开发的自检清单查询数据前确认表字段是否允许 NULL。排序时确认字段是数值类型还是字符串类型。分组统计时确认 SELECT 字段是否符合 ONLY_FULL_GROUP_BY 约束。行转列需求先确认取值集合是否固定不固定时考虑改用动态 SQL 或前端二次处理。更新和删除前先运行同条件 SELECT预估影响行数。大批量数据变更必须放到事务中分批执行并设置合理超时时间。锁等待发生时立即查询 innodb_trx确认是否存在长时间未提交事务。慢查询优先用 EXPLAIN 判断是否有全表扫描。导入数据如果出现中文乱码先执行SET NAMES utf8mb4再确认表的默认字符集。使用 JDBC 批处理时在连接 URL 中增加rewriteBatchedStatementstrue可以把多条 INSERT 合并提交执行效率提升非常明显。这些自检项覆盖了 MySQL 表数据操作的主要风险点可以作为平时环境测试和生产发布前的检查基线。MySQL 表数据操作的思路其实很简单先明确要操作的数据范围再选择合适的写入、更新、删除方式最后通过 EXPLAIN、慢查询日志和事务状态确认执行是否符合预期。前面提供的 SQL 示例都可以直接复制到本地测试库中运行。建议下一步练习时把学生、成绩两张表换成自己正在开发的业务表分别测试普通统计、行转列、批量更新和事务回滚遇到锁等待或分组报错时回到常见问题一节核对。这套 SQL 积累下来的经验和排查习惯在后续学习存储过程、触发器、JDBC 批量处理和分库分表时依然能复用可以先收藏这篇文章作为日常参考。