第四天 MySQL 核心知识点（结合 Java + 面试考点）

一、MySQL 多表查询（面试核心 + Java 实操高频）

1. 知识点解析（结合 Java）

多表查询是 MySQL 进阶的核心，Java 开发中几乎所有业务（如订单 - 用户 - 商品关联查询）都会用到，面试中常考「联表查询效率」「笛卡尔积避免」「关联查询的 Java 实现」。

2. 面试考点

-- 准备测试表（Java项目中对应User、Order实体类）
CREATE TABLE `user` (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  age INT
);

CREATE TABLE `order` (
  id INT PRIMARY KEY AUTO_INCREMENT,
  order_no VARCHAR(30) NOT NULL,
  user_id INT,
  amount DECIMAL(10,2),
  FOREIGN KEY (user_id) REFERENCES `user`(id)
);

-- 插入测试数据
INSERT INTO `user` (username, age) VALUES ('张三', 20), ('李四', 25), ('王五', 30);
INSERT INTO `order` (order_no, user_id, amount) VALUES ('ORD001', 1, 100.00), ('ORD002', 1, 200.00), ('ORD003', 2, 150.00);

-- 1. 内连接：查询有订单的用户及其订单（Java中返回UserOrderVO）
SELECT u.id, u.username, o.order_no, o.amount 
FROM `user` u 
INNER JOIN `order` o ON u.id = o.user_id;

-- 2. 左连接：查询所有用户及其订单（无订单的用户显示NULL）
SELECT u.id, u.username, o.order_no, o.amount 
FROM `user` u 
LEFT JOIN `order` o ON u.id = o.user_id;

-- 3. 右连接：查询所有订单及其所属用户（极少用，可转为左连接）
SELECT u.id, u.username, o.order_no, o.amount 
FROM `user` u 
RIGHT JOIN `order` o ON u.id = o.user_id;

-- 4. 面试高频：统计每个用户的订单总数和总金额（Java中用Map接收结果）
SELECT u.id, u.username, COUNT(o.id) AS order_count, SUM(o.amount) AS total_amount
FROM `user` u 
LEFT JOIN `order` o ON u.id = o.user_id
GROUP BY u.id, u.username;

二、MySQL 子查询（面试常问 + Java 避坑点）

1. 知识点解析（结合 Java）

子查询是嵌套在 SELECT/INSERT/UPDATE/DELETE 中的查询，Java 开发中要注意「子查询效率」（避免多层嵌套），面试常考「相关子查询 vs 非相关子查询」。

• 非相关子查询：子查询可独立执行（先查子查询，再查主查询），效率较高。

• 相关子查询：子查询依赖主查询的字段（逐行执行），效率低，Java 中尽量用联表查询替代。

2.面试考点

-- 1. 非相关子查询：查询订单金额大于150的用户（先查符合条件的user_id，再查用户）
SELECT * FROM `user` 
WHERE id IN (SELECT user_id FROM `order` WHERE amount > 150);

-- 2. 相关子查询：查询每个用户的最大金额订单（效率低，面试常问优化）
SELECT u.id, u.username, 
  (SELECT MAX(amount) FROM `order` o WHERE o.user_id = u.id) AS max_amount
FROM `user` u;

-- 优化方案（联表+分组，Java中优先用这个）
SELECT u.id, u.username, MAX(o.amount) AS max_amount
FROM `user` u 
LEFT JOIN `order` o ON u.id = o.user_id
GROUP BY u.id, u.username;

-- 3. 面试高频：子查询作为字段（Java中接收为VO的一个属性）
SELECT o.order_no, o.amount,
  (SELECT username FROM `user` u WHERE u.id = o.user_id) AS username
FROM `order` o;

三、MySQL 事务（Java 面试必问 + 核心考点）

1. 知识点解析（结合 Java)

事务是保证数据一致性的核心，Java 中通过Connection.setAutoCommit(false)手动控制事务，面试必考「ACID」「事务隔离级别」「脏读 / 幻读 / 不可重复读」。

• ACID：原子性（Atomic）、一致性（Consistent）、隔离性（Isolated）、持久性（Durable）。

• Java 中事务实现：① JDBC 手动控制；② Spring 声明式事务（@Transactional）；③ Spring 编程式事务（TransactionTemplate）。

-- MySQL事务实操（模拟Java中转账场景）
START TRANSACTION; -- 开启事务（Java中conn.setAutoCommit(false)）
UPDATE `user_account` SET balance = balance - 100 WHERE user_id = 1; -- 扣钱
UPDATE `user_account` SET balance = balance + 100 WHERE user_id = 2; -- 加钱
COMMIT; -- 提交事务（Java中conn.commit()）
-- 出错时回滚：ROLLBACK;（Java中conn.rollback()）

-- 测试脏读（设置隔离级别为读未提交）
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
UPDATE `user` SET age = 21 WHERE id = 1;
-- 未提交时，其他会话能读到age=21（脏读）
ROLLBACK;

-- Java中JDBC事务代码示例（核心面试考点）
/*
Connection conn = JDBCUtil.getConnection();
try {
    conn.setAutoCommit(false); // 关闭自动提交
    // 执行SQL操作
    PreparedStatement ps1 = conn.prepareStatement("UPDATE user_account SET balance = balance - 100 WHERE user_id = ?");
    ps1.setInt(1, 1);
    ps1.executeUpdate();
    
    PreparedStatement ps2 = conn.prepareStatement("UPDATE user_account SET balance = balance + 100 WHERE user_id = ?");
    ps2.setInt(1, 2);
    ps2.executeUpdate();
    
    conn.commit(); // 提交事务
} catch (SQLException e) {
    conn.rollback(); // 回滚事务
    e.printStackTrace();
} finally {
    conn.setAutoCommit(true);
    JDBCUtil.close(conn);
}
*/