MySQL 数据库 5（Day53）

主题：子查询进阶（IN/ANY/ALL/EXISTS、关联子查询）、视图、触发器（BEFORE/AFTER + NEW/OLD）、存储过程与 IN/OUT 参数、函数、流程控制（IF/CASE/WHILE/REPEAT/LOOP）、批量插入 300 万条数据实战、SQL 注入原理与防范

---

结构总览

MySQL 数据库 5
  ├─ 复习：单表查询 / 多表连接 / 基础子查询
  ├─ 子查询进阶
  │    ├─ IN / ANY / ALL / EXISTS
  │    ├─ 关联子查询（Correlated Subquery）
  │    └─ 经典题：每个部门最新入职的员工（子查询 / 连接 / 窗口函数三种解法）
  ├─ 视图（View）：虚拟表，封装查询语句
  ├─ 触发器（Trigger）：BEFORE|AFTER INSERT|UPDATE|DELETE + NEW/OLD
  ├─ 存储过程（Procedure）：CALL 调用、IN/OUT 参数
  │    └─ 实战：批量插入 300 万条数据 + 记录耗时与表文件大小
  ├─ 函数（Function）：必须 RETURN，用于 SQL 表达式
  ├─ 流程控制：IF / CASE / WHILE / REPEAT / LOOP + DECLARE 局部变量
  └─ SQL 注入：拼接成因 → 参数化查询 → 白名单 → 最小权限

关键要点

子查询进阶运算符：

运算符	含义	典型用法
IN	属于结果集合	WHERE age IN (SELECT age ...)
ANY	满足任意一个即可	= ANY ≈ IN；> ANY = 大于最小值
ALL	必须满足全部	> ALL = 大于最大值；< ALL = 小于最小值
EXISTS / NOT EXISTS	只判断是否存在记录（真/假），常配合关联子查询	选课/未选课学生

-- 薪资高于所有岗位平均薪资（不冒尖的反面：超过每一个平均值）
SELECT * FROM employee
WHERE salary > ALL (SELECT AVG(salary) FROM employee GROUP BY post);
 
-- 薪资不垫底：高于至少一个岗位平均薪资
SELECT * FROM employee
WHERE salary > ANY (SELECT AVG(salary) FROM employee GROUP BY post);
 
-- 至少选修一门课程的学生（EXISTS 关联子查询）
SELECT s.id, s.name FROM student AS s
WHERE EXISTS (SELECT 1 FROM student2course AS sc WHERE sc.sid = s.id);

排错：混淆 ANY/ALL；多行结果用 = 报错（改 IN/ANY/ALL）；NOT IN 遇到子查询返回 NULL 会全空（改用 NOT EXISTS）。

每个部门最新入职的员工（三种解法）：

-- 解法1：关联子查询
SELECT e.id, e.name, e.hire_date, e.depart_id
FROM employee AS e
WHERE e.hire_date = (
    SELECT MAX(e2.hire_date) FROM employee AS e2
    WHERE e2.depart_id = e.depart_id
)
ORDER BY e.depart_id, e.id;
 
-- 解法2：连接派生表
SELECT e.id, e.name, e.hire_date, e.depart_id
FROM employee AS e
INNER JOIN (
    SELECT depart_id, MAX(hire_date) AS max_hire_date
    FROM employee GROUP BY depart_id
) AS latest ON latest.depart_id = e.depart_id AND latest.max_hire_date = e.hire_date;
 
-- 解法3：窗口函数（MySQL 8.0+，每部门只取一名）
WITH ranked_employee AS (
    SELECT e.*, ROW_NUMBER() OVER (
        PARTITION BY e.depart_id ORDER BY e.hire_date DESC, e.id DESC
    ) AS row_num
    FROM employee AS e
)
SELECT id, name, hire_date, depart_id FROM ranked_employee WHERE row_num = 1;

注意：同一部门入职日期相同时解法 1/2 会返回多条。

视图（View）： 虚拟表，主要保存查询语句本身而非数据副本。作用：简化复杂查询、隐藏多表连接细节、限制用户可见字段、统一查询逻辑。

CREATE VIEW department_salary_view AS
SELECT depart_id, COUNT(*) AS employee_count, AVG(salary) AS average_salary
FROM employee GROUP BY depart_id;
 
SHOW CREATE VIEW department_salary_view;   -- 查看定义
DROP VIEW IF EXISTS department_salary_view;

要点：数据仍来自基础表（基础表删字段后视图失效）；含聚合/分组/复杂连接的视图通常只读不适合直接 UPDATE；命名建议统一前缀 _view 或 v_。

触发器（Trigger）： 与表关联，INSERT/UPDATE/DELETE 发生时自动执行。时机 BEFORE/AFTER；行级对象 NEW（新数据）/OLD（旧数据）；INSERT 只有 NEW，DELETE 只有 OLD，UPDATE 两者都有。

-- 万能模板
DELIMITER //
CREATE TRIGGER 触发器名称
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
FOR EACH ROW
BEGIN
    -- 自动执行的 SQL
END//
DELIMITER ;
 
-- 实例1：薪资变更日志（AFTER UPDATE + NEW/OLD 比较）
CREATE TRIGGER employee_salary_update_trigger
AFTER UPDATE ON employee
FOR EACH ROW
BEGIN
    IF NOT (OLD.salary <=> NEW.salary) THEN
        INSERT INTO employee_salary_log(employee_id, employee_name, old_salary, new_salary)
        VALUES (NEW.id, NEW.name, OLD.salary, NEW.salary);
    END IF;
END
 
-- 实例2：插入前校验（BEFORE INSERT + SIGNAL 抛错）
CREATE TRIGGER employee_salary_insert_trigger
BEFORE INSERT ON employee
FOR EACH ROW
BEGIN
    IF NEW.salary IS NOT NULL AND NEW.salary < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '薪资不能小于0';
    END IF;
END

要点：多条语句必须临时改 DELIMITER；用 <=> 安全比较 NULL；触发器适合日志和校验，复杂业务应放应用层或存储过程。

存储过程（Procedure）： 保存在服务端的 SQL 语句集合，用 CALL 过程名() 调用（不是 SELECT）。开发人员调用、DBA 管理。

-- IN 参数传入 / OUT 参数返回
CREATE PROCEDURE count_employee(OUT p_total INT)
BEGIN
    SELECT COUNT(*) INTO p_total FROM employee;
END
CALL count_employee(@employee_total);
SELECT @employee_total;      -- OUT 结果通过用户变量取回

批量插入 300 万条数据实战：

CREATE TABLE big_data (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    code CHAR(32) NOT NULL,
    content VARCHAR(100) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) ENGINE = InnoDB;
 
-- 存储过程：每次插入 10000 条（10^4 CROSS JOIN 生成序号），共 300 批，整体一个事务
DELIMITER $$
CREATE PROCEDURE insert_3m_data()
BEGIN
    DECLARE v_batch INT DEFAULT 1;
    START TRANSACTION;
    WHILE v_batch <= 300 DO
        INSERT INTO big_data(code, content)
        SELECT MD5(CONCAT(v_batch, '_', nums.seq)), CONCAT('test_data_', v_batch, '_', nums.seq)
        FROM (SELECT a.n + b.n*10 + c.n*100 + d.n*1000 + 1 AS seq
              FROM (SELECT 0 AS n UNION ALL ... SELECT 9) AS a
              CROSS JOIN (...) AS b CROSS JOIN (...) AS c CROSS JOIN (...) AS d) AS nums;
        SET v_batch = v_batch + 1;
    END WHILE;
    COMMIT;
END$$
DELIMITER ;
 
TRUNCATE TABLE big_data;
CALL insert_3m_data();

记录执行时间（Linux 终端）：time mysql -uroot -p -D db13 -e "CALL insert_3m_data();"（real 总耗时 / user 用户态 CPU / sys 内核态 CPU）。

查询磁盘占用：

SELECT table_name, table_rows,
       ROUND(data_length/1024/1024, 2) AS data_mb,
       ROUND(index_length/1024/1024, 2) AS index_mb
FROM information_schema.tables
WHERE table_schema = 'db13' AND table_name = 'big_data';

sudo du -sh /var/lib/mysql/db13/big_data*          # 直接查看文件
docker exec -it mysql8 sh -c "du -sh ..."          # Docker 环境

函数（Function）： 接收参数并 RETURN 一个结果，只能用在 SQL 表达式中（SELECT/WHERE），不能 CALL。

CREATE FUNCTION get_salary_level(p_salary DECIMAL(15,2))
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    IF p_salary IS NULL THEN RETURN '未知';
    ELSEIF p_salary >= 100000 THEN RETURN '高级';
    ELSEIF p_salary >= 10000 THEN RETURN '中级';
    ELSE RETURN '初级';
    END IF;
END
 
SELECT id, name, get_salary_level(salary) AS salary_level FROM employee;

函数 vs 存储过程：函数必须有 RETURN、作为表达式使用；存储过程用 CALL、可返回结果集并执行多条数据操作。

流程控制： IF...ELSEIF...ELSE（注意是 ELSEIF 不是 ELSE IF）、CASE 多分支、WHILE（条件为真循环）、REPEAT…UNTIL（先执行一次）、LOOP + LEAVE 退出。局部变量 DECLARE 变量名 类型 DEFAULT 默认值; 必须声明在 BEGIN 之后、其他语句之前。

-- CASE 表达式（SQL 中直接用）
SELECT name,
    CASE
        WHEN salary >= 100000 THEN '高级'
        WHEN salary >= 10000 THEN '中级'
        ELSE '初级'
    END AS salary_level
FROM employee;

排错：变量未声明先用、WHILE 循环体忘写递增、IF 缺 END IF。

SQL 注入（SQL Injection）： 应用程序把用户输入直接拼接进 SQL 字符串，攻击者借特殊输入改变原始 SQL 执行逻辑。

防范原则：

• 参数化查询（预编译 PreparedStatement / pymysql 的 %s 占位符），不要自行替换单引号
• 动态表名/列名/排序字段用白名单映射成固定 SQL 片段
• 输入做长度、类型、格式校验
• 数据库账户遵循最小权限原则，应用不用 root 连接
• 错误信息不回显 SQL 细节给用户，密码用安全哈希保存

# 参数化查询示例（pymysql）
sql = "SELECT id, name FROM employee WHERE name = %s AND post = %s"
cursor.execute(sql, (username, password))

当日总结

• IN 判断属于集合，= ANY 等价 IN，> ANY 超过任意一个，> ALL 超过全部，EXISTS 只判断存在性
• 每部门最新员工三解法：关联子查询 / 连接派生表 / MySQL 8.0 窗口函数 ROW_NUMBER() OVER(PARTITION BY …)
• 视图是虚拟表，封装查询逻辑并控制可见范围
• 触发器自动响应增删改：BEFORE/AFTER × INSERT/UPDATE/DELETE，NEW/OLD 取新旧值
• 存储过程用 CALL 调用，支持 IN/OUT 参数，适合批量操作与复杂流程
• 函数必须 RETURN 一个值，作为 SQL 表达式使用
• 流程控制：IF/ELSEIF/CASE/WHILE/REPEAT/LOOP + DECLARE 局部变量
• 300 万条插入：CROSS JOIN 数字序列生成 + 分批事务 + time 计时 + information_schema/du 查大小
• SQL 注入根源是代码与输入混合，参数化查询 + 白名单 + 最小权限是核心防线

相关页面

• MySQL 概念页 — 本课核心概念页（子查询进阶/视图/触发器/存储过程/函数/SQL 注入）
• MySQL 数据库 4（Day52） — 上一实操课（分组/HAVING/排序分页/多表连接/基础子查询）
• MySQL 数据库 6（Day54） — 下一课（授权体系/InnoDB 架构）
• HTTP 协议 — Web 安全上下文（SQL 注入与 CSRF/XSS 同属应用安全主题）