MySQL（4）

十三、子查询

定义：子查询（嵌套查询）指一个 SELECT 语句中包含另一个 SELECT 语句，内层查询的结果作为外层查询的条件 / 数据源使用。

案例：

1. 查询在‘武汉’工作过的员工信息

-- 分步查询select * from emp where deptno in (1,2)；（假设武汉部门编号是 1、2）select deptno from dept where loc=' 武汉 '；（先查武汉对应的部门编号）

-- 子查询写法select * from emp where deptno in (select deptno from dept where loc=' 武汉 ')；

-- 等价 JOIN 写法（对比参考）select e.* from emp e join dept d on e.deptno=d.deptno and d.loc=' 武汉 '；

2. 查询比‘阿朱’工资高的员工信息

-- 分步查询select * from emp where sal>3000；（假设阿朱工资是 3000）select sal from emp where ename=' 阿朱 '；（先查阿朱的工资）

-- 子查询写法select * from emp where sal>(select sal from emp where ename=' 阿朱 ')；

3. 查询工资高于平均工资的员工信息

-- 分步查询select * from emp where sal > 平均工资；（伪代码，仅示意）select avg (sal) from emp；（先查全公司平均工资）

-- 子查询写法select * from emp where sal>(select avg (sal) from emp)；

4. 查询工资高于本部门平均工资的员工信息

-- 步骤 1：查询每个部门的平均工资select deptno,avg (sal) avg_sal from emp group by deptno；

-- 步骤 2：关联员工表，查询员工 + 对应部门平均工资select * from emp t1,(select deptno,avg (sal) avg_sal from emp group by deptno) t2 where t1.deptno=t2.deptno；

-- 步骤 3：筛选工资高于本部门平均工资的员工select t1.*,t2.avg_sal from emp t1,(select deptno,avg (sal) avg_sal from emp group by deptno) t2 where t1.deptno=t2.deptno and t1.sal>t2.avg_sal；

5. 查询和张三丰同一个部门且工资比他高的员工信息

-- 子查询写法（多个子查询）select * from emp where deptno=(select deptno from emp where ename=' 张三丰 ') and sal>(select sal from emp where ename=' 张三丰 ')；

6. 查询工资最高的员工信息

select * from emp where sal=(select max(sal) from emp)；

7. 查询和张三丰同一个职位且入职时间比他晚的员工信息（补全未完成案例）

select * from emp where job=(select job from emp where ename=' 张三丰 ') and hiredate>(select hiredate from emp where ename=' 张三丰 ')；

8. 查询 1 号和 2 号部门的职位相同的员工信息

select * from emp where job in(select job from emp where deptno=1 or deptno=2)；

十四、事务【重点】

1、什么是事务？事务是一组不可分割的 SQL 操作集合，执行结果只有两种：

要么全部成功执行，要么全部回滚（恢复到操作前状态）（类比：“无间道”—— 去不了终点就回到原点）。

2、MySQL 控制事务

开启事务（开启后，undo_log 会记录操作前的老数据）

START TRANSACTION；（注：原笔记中 "STARTS" 为笔误，正确为 "START"）

执行多条 SQL（如转账：扣钱 + 加钱）

sql1；（示例：update account set money=money-100 where id=1；）sql2；（示例：update account set money=money+100 where id=2；）

提交事务（操作生效，删除 undo_log，数据永久修改）

COMMIT；

回滚事务（操作失败，根据 undo_log 恢复数据到事务开启前状态）

ROLLBACK；

3、事务的四个特性（ACID）

原子性（Atomicity）：事务是最小的执行单位，不可再分割；

一致性（Consistency）：事务执行前后，数据的逻辑完整性保持正确（如转账总金额不变）；

隔离性（Isolation）：多个并发事务之间相互隔离，互不干扰；

持久性（Durability）：事务一旦提交，修改永久生效，无法通过回滚恢复。

4、JDBC 控制事务//

1. 关闭自动提交（等价于 MySQL 的 START TRANSACTION）conn.setAutoCommit (false)；

try {// 执行多条 SQL 操作sql1；（示例：扣钱操作）//int a = 6/0; // 模拟异常sql2；（示例：加钱操作）

// 2. 全部成功则提交事务conn.commit ()；} catch (Exception e){// 3. 有异常则回滚事务conn.rollback ()；e.printStackTrace ()；}

5、并发下的事务问题脏读：

一个事务读到了另一个事务未提交的临时数据（数据可能被回滚，不可靠）；

不可重复读：同一事务内多次读取同一数据，结果不一致（被其他事务修改并提交）；

幻读：同一事务内多次执行同一查询，结果集行数不一致（被其他事务增 / 删数据）。

6、事务的隔离级别

（1）隔离级别对比（补全原笔记缺失内容）

隔离级别                                                          脏读 不可重复读 幻读读

未提交（READ-UNCOMMITTED）                  √             √           √

读已提交（READ-COMMITTED）                    ×             √           √

可重复读（REPEATABLE-READ）                   ×             ×          √

串行化（SERIALIZABLE）                                ×             ×          ×

（注：MySQL 默认隔离级别为 “可重复读”）

（2）修改隔离级别-- ① 查看当前隔离级别（MySQL 5.7 及以下）select @@tx_isolation；

-- ① 查看当前隔离级别（MySQL 8.0 及以上）SELECT @@transaction_isolation；

-- ② 永久修改 MySQL 隔离级别（需重启服务）

步骤 1：找到 MySQL 配置文件 my.ini（Windows）/my.cnf（Linux）

步骤 2：在 [mysqld] 部分添加 / 修改：

transaction-isolation = READ-UNCOMMITTED（可替换为其他级别）

步骤 3：重启 MySQL 服务（Windows）

net stop mysqlnet start mysql

总结

1. 子查询：核心是 “内层查询为外层服务”，可搭配 IN/=/＞等运算符，部分场景可替换为 JOIN，需根据可读性选择；
2. 事务：核心是保证多 SQL 操作的原子性，通过 START TRANSACTION/COMMIT/ROLLBACK 控制，ACID 特性是事务的核心保障；
3. 隔离级别：不同级别可解决不同并发问题，级别越高性能越低，需根据业务平衡，MySQL 默认 “可重复读”。