MySQL 入门课程与练习 · 事务、提交与回滚至进一步阅读

返回分篇目录

从建表开始，用同一组数据完成查询，再学习表关系、事务和索引。

事务、提交与回滚

事务把多条操作组织为一个工作单元。先创建账户表并准备数据，再开启事务。

CREATE TABLE account (
  id INT PRIMARY KEY,
  balance DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB;
INSERT INTO account (id, balance) VALUES (1, 500), (2, 50000);

START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 2;
UPDATE account SET balance = balance + 100 WHERE id = 1;
COMMIT;

失败后由应用或操作者决定执行 ROLLBACK;，不要假设任意单条 SQL 报错都会自动撤销整个事务。

保存点与自动提交

SELECT @@autocommit;
START TRANSACTION;
UPDATE account SET balance = balance + 50 WHERE id = 1;
SAVEPOINT s1;
UPDATE account SET balance = balance + 20 WHERE id = 1;
ROLLBACK TO SAVEPOINT s1;
ROLLBACK;

保存点回滚只撤销保存点之后的部分，ROLLBACK 撤销整个未提交事务。SET autocommit = 0 会影响当前会话后续操作，练习时优先显式使用 START TRANSACTION；切换自动提交模式前先确认未提交事务。

双会话验证与 ACID

在会话 A 开启事务并修改账户，在会话 B 查询，记录提交前后的观察结果。另一会话何时看到变化，与隔离级别和它自己的事务快照有关，不能简单解释成“内存数据和磁盘数据的区别”。

ACID 分别描述原子性、一致性、隔离性、持久性。建表、改表、TRUNCATE TABLE 等 DDL 涉及隐式提交，不应夹在回滚实验中。更深入的锁实验见 MySQL 锁与死锁。

视图与数据访问边界

视图保存查询定义，可以复用查询或限制暴露的列，不会自动复制出一份独立数据。

CREATE VIEW v_emp_20 AS
SELECT empno, ename, sal, deptno FROM emp WHERE deptno = 20;
SELECT * FROM v_emp_20;

CREATE OR REPLACE VIEW v_emp_20 AS
SELECT empno, ename, sal, deptno FROM emp
WHERE deptno = 20 AND sal >= 3000
WITH CHECK OPTION;

DROP VIEW v_emp_20;

是否能通过视图写入，取决于查询是否满足可更新条件；不要将所有视图当作可写表。WITH CHECK OPTION 对可更新视图的写入条件进行检查；隐藏敏感列还需要配合实际权限配置。

索引、执行计划与数据导入

创建索引前先看查询

EXPLAIN SELECT empno, ename FROM emp WHERE ename = 'LIN';
CREATE INDEX idx_emp_ename ON emp (ename);
SHOW INDEX FROM emp;
EXPLAIN SELECT empno, ename FROM emp WHERE ename = 'LIN';

CREATE INDEX idx_emp_dept_sal ON emp (deptno, sal);
EXPLAIN SELECT empno, sal FROM emp
WHERE deptno = 10 ORDER BY sal;

DROP INDEX idx_emp_ename ON emp;

索引占空间，也增加写入维护成本。表很小或条件命中大量行时，优化器未必选择索引，不要把一次耗时对比当作固定性能结论。大样本实验需要自行准备测试数据。

导入测试数据

在 MySQL 客户端中用 SOURCE 导入自己的 SQL 文件；先检查脚本内容及目标数据库，路径按实际系统填写。

USE mysql_practice;
SOURCE /path/to/your/practice.sql;

综合练习与验收

使用前面建立的部门、员工和账户表完成练习。先独立写 SQL，再展开核对。

条件、排序与统计：筛选员工，再计算部门指标

1. 查询没有奖金的员工，分别区分 NULL 与零。
2. 查询名字以 L 开头的员工，按工资降序及员工编号升序排列。
3. 查询各部门人数和平均工资，只保留人数大于 1 的部门。
4. 按入职年份统计人数。

SELECT ename, comm FROM emp WHERE comm IS NULL;
SELECT ename, comm FROM emp WHERE comm = 0;
SELECT empno, ename, sal FROM emp
WHERE ename LIKE 'L%' ORDER BY sal DESC, empno ASC;
SELECT deptno, COUNT(*) AS employee_count, AVG(sal) AS average_salary
FROM emp GROUP BY deptno HAVING COUNT(*) > 1;
SELECT EXTRACT(YEAR FROM hiredate) AS hire_year, COUNT(*) AS employee_count
FROM emp GROUP BY EXTRACT(YEAR FROM hiredate);

关联与子查询：空部门、主管及并列最高平均工资

1. 列出所有部门及人数，检查“支持”部门是否出现。
2. 列出所有员工及主管姓名，检查没有主管的记录是否保留。
3. 查出平均工资最高的所有部门，并列时不能只返回一条。

SELECT d.deptno, d.dname, COUNT(e.empno) AS employee_count
FROM dept AS d LEFT JOIN emp AS e ON d.deptno = e.deptno
GROUP BY d.deptno, d.dname;

SELECT e.ename AS employee, m.ename AS manager
FROM emp AS e LEFT JOIN emp AS m ON e.mgr = m.empno;

SELECT deptno, AVG(sal) AS average_salary
FROM emp GROUP BY deptno
HAVING AVG(sal) = (
  SELECT MAX(averages.average_salary)
  FROM (SELECT AVG(sal) AS average_salary FROM emp GROUP BY deptno) AS averages
);

建模与事务：多对多关系、成绩汇总和转账回滚

1. 用教师、学生和关系表建立多对多关系，限制同一个师生组合重复出现。
2. 用 student_score(name, subject, score) 保存成绩，查询每人的平均分、最高分、最低分，用 GROUP_CONCAT 汇总不及格科目。
3. 只修改一侧账户余额后回滚，确认没有留下半次转账。
4. 演示保存点回滚，说明它与整个事务回滚的区别。

验收时检查结果行数、空值、未匹配记录、并列值和回滚后的余额，不只检查语句能否执行。

完成练习后清理

确认不再需要练习数据后，清理专用练习库：

DROP DATABASE mysql_practice;

进一步阅读

• MySQL 查询语法与函数
• MySQL 8.4：事务与隐式提交
• MySQL 8.4：字符集配置