MySQL 核心语法与实战基础

4. 易错点：DELETE 中漏写 WHERE，导致全表数据删除。

DQL-基础查询

1. 一句话总结：查数据并做列表，只拿需要的列，有条件，有分页。
2. 关键词：SELECT / DISTINCT / 列投影 / AS
3. 基础命令：

SELECT id, username FROM user;
SELECT DISTINCT city FROM user;

4. 易错点：SELECT * 在查询大表/宽表时很慢，并且容易触发回表与网络 IO。

DQL-条件查询

1. 一句话总结：用 WHERE 筛选，筛选出'我需要的数据'。
2. 关键词：WHERE / LIKE / OR / BETWEEN
3. 基础命令：

-- 查今天已支付订单
SELECT * FROM `orders` WHERE status = 'PAID' AND pay_time >= CURDATE();

-- 查 username 以 a 开头 或 指定 id 集合
SELECT * FROM `user` WHERE username LIKE 'a%' OR id IN (1001, 1002, 1003);

4. 易错点：NULL 不能用 "=NULL"，而应该用 IS NULL / IS NOT NULL。

DQL-聚合函数

1. 一句话总结：统计报表：总数，平均数，最大最小值。
2. 关键词：SUM / AVG / MIN / MAX
3. 基础命令：

-- 总数
SELECT COUNT(*) FROM orders WHERE status = 'PAID';

-- 分组 + 平均数
SELECT dept_id, AVG(salary) AS avg_salary FROM emp GROUP BY dept_id;

4. 易错点：COUNT(col) 会忽略掉 NULL 值，所以用 COUNT(*)，是最保底的选择。

DQL-分组查询

1. 一句话总结：分组聚合，并按'维度查询'。
2. 关键词：GROUP BY / HAVING / 维度聚合
3. 基础命令：

SELECT city, COUNT(*) FROM user GROUP BY city;
SELECT city, COUNT(*) AS c FROM user GROUP BY city HAVING c > 2;

4. 易错点：WHERE vs HAVING：其中，WHERE 过滤的是行，而 HAVING 过滤的是聚合后的结果。

DQL-排序查询

1. 一句话总结：列表，按照时间、分数…排序。
2. 关键词：ORDER BY / DESC / ASC
3. 基础命令：

SELECT * FROM `post` ORDER BY created_at DESC;
SELECT * FROM `post` ORDER BY score DESC, created_at DESC;

4. 易错点：大 OFFSET 排序很慢。（可以通过索引进行优化）。

DQL-分页查询

1. 一句话总结：列表分页，解决'只取一页的问题'。
2. 关键词：LIMIT / OFFSET / 深分页
3. 基础命令：

SELECT * FROM `post` ORDER BY id DESC LIMIT 5 OFFSET 0;

-- 游标分页（直接确定 id，然后往前翻）
SELECT * FROM `post` WHERE id < 100000 ORDER BY id DESC LIMIT 3;

4. 易错点：深分页，如 OFFSET=10000 会抛弃大量行，所以推荐使用游标分页。

DQL-案例联系

1. 一句话总结：综合使用。
2. 关键词：组合查询 / 可读性 / 逐步验证
3. 基础命令：

-- 例：查北京用户的已支付订单列表
SELECT o.id, o.amount, o.created_at, u.username 
FROM `orders` o JOIN `user` u ON o.user_id = u.id 
WHERE u.city = 'Beijing' AND o.status = 'PAID' 
ORDER BY o.created_at DESC LIMIT 5;

EXPLAIN SELECT o.id, o.amount, o.created_at, u.username 
FROM `orders` o JOIN `user` u ON o.user_id = u.id 
WHERE u.city = 'Beijing' AND o.status = 'PAID' 
ORDER BY o.created_at DESC LIMIT 5;

4. 易错点：不 EXPLAIN 就上生产，就很容易写出慢查询。

DQL-执行顺序

1. 一句话总结：解释，为什么别名能够在 HAVING 中用，而不能在 WHERE 中用。
2. 关键词：FROM / WHERE / LIMIT / SELECT / GROUP BY / HAVING
3. 基础命令：

SELECT dept_id, COUNT(*) AS c FROM emp GROUP BY dept_id HAVING c > 1;

4. 易错点：执行顺序：FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY > LIMIT。原本按顺序，HAVING 应该也无法看到别名，但是 MySQL 做了拓展。

DQL-小结

1. 一句话总结：查询 = '筛选 (WHERE)+ 聚合 (GROUP)+ 排序 (ORDER)+ 分页 (LIMIT)+ 索引 (EXPLAIN)'。
2. 关键词：EXPLAIN / 回表 / 索引
3. 基础命令：

EXPLAIN SELECT ...;
-- 暂时，还没怎么用过 SHOW WARNINGS;（看优化器重写/警告）

4. 易错点：只会写代码，而不会检查，非常掉分。

DCL-用户管理

1. 一句话总结：管理账号与登录来源，用最小的权限，隔离环境。
2. 关键词：CREATE USER / ALTER USER / DROP USER
3. 基础命令：

CREATE USER 'user'@'localhost' IDENTIFIED BY '123456';
ALTER USER 'user'@'localhost' IDENTIFIED BY '1234';

4. 易错点：'user'@'localhost'与'user'@'%'是两条不同的账户，经常配错，出现能连/不能连的问题。(需注意优先级)。

DCL-权限控制

1. 一句话总结：控制谁，能对某些表，进行特定的权限操作。避免越权与误删。
2. 关键词：GRANT / REVOKE / SHOW GRANTS
3. 基础命令：

GRANT SELECT, ALTER ON interview_mysql.dept TO 'user'@'localhost';
-- 展示权限
SHOW GRANTS FOR 'user'@'localhost';

4. 易错点：直接给 GRANT ALL 是管理上的惰性，会给未来增加不可预测的风险。

DCL-小结

1. 一句话总结：账号='主题'，权限='动作 + 资源范围'，原则='最小权限'。
2. 关键词：最小权限 / 审计 / 账号隔离
3. 基础命令：

-- 删除
REVOKE SELECT ON interview_mysql.dept FROM 'user'@'localhost';
-- 刷新，并清空
FLUSH PRIVILEGES;

4. 易错点：误以为改了权限就会马上生效：ALTER/REVOKE 在 MySQL 中自动刷新，通常不需要 FLUSH。

函数 - 字符串函数

1. 一句话总结：清洗/格式化文本，拼接展示。
2. 关键词：CONCAT / REPLACE / SUBSTRING / LENGTH
3. 基础命令：

-- concat(..) 组合 -- coalesce(..) 选取第一个非空
SELECT CONCAT(username, '(', COALESCE(nickname, '-'), ')') AS showName FROM user;

-- 替换
SELECT REPLACE(email, '@example.com', '@demo.com') FROM user;

4. 易错点：LENGTH() 统计长度，中文可能会变长。用 CHAR_LENGTH() 更合适。

函数 - 数据函数

1. 一句话总结：做金额/评分/统计时，需要用到的数值处理。
2. 关键词：ROUND / CEIL / FLOOR / ABS / DECIMAL
3. 基础命令：

SELECT id, ROUND(amount, 2) AS money FROM orders;
SELECT id, FLOOR(amount), CEIL(amount) AS money FROM orders;

4. 易错点：金额计算时别用浮点数存储，用 DECIMAL(10,2)-定点数，否则会有精度的坑。

函数 - 日期函数

1. 一句话总结：解决了数据分析与时间报表中，对时间的分析与处理。
2. 关键词：INTERVAL / DATE / DATE_ADD / TIMESTAMPDIFF
3. 基础命令：

SELECT * FROM orders WHERE created_at > NOW() - INTERVAL 7 DAY;
SELECT DATE(created_at) AS d, COUNT(*) AS c FROM orders GROUP BY d ORDER BY c;

-- 计算时间差值 (时间单位，time1, time2)
SELECT TIMESTAMPDIFF(DAY, DATE(created_at), '2025-11-20') AS diff FROM orders;

4. 易错点：应用/DB 时区不一致会导致时间错位。可以统一时区，或者用 UTC。

函数 - 流程函数

1. 一句话总结：在 SQL 层，做简单的分支控制 / 空值处理 / 分类统计。
2. 关键词：CASE / COALESCE / IF / WHEN
3. 基础命令：

SELECT id, COALESCE(nickname, username) AS show_name FROM `user`;
SELECT SUM(CASE WHEN status = 'PAID' THEN 1 ELSE 0 END) AS paid_status,
       SUM(CASE WHEN status = 'CREATED' THEN 1 ELSE 0 END) AS created_status 
FROM orders;

4. 易错点：CASE 太复杂，会导致难以维护，应放在应用层。

约束 - 概述

1. 一句话总结：用约束保证数据的正确性：唯一/非空/范围…等等。
2. 关键词：PRIMARY KEY / UNIQUE / NOT NULL / CHECK
3. 基础命令：

SHOW CREATE TABLE `user`;
SHOW INDEX FROM `user`;

4. 易错点：只靠代码约束不保底；并发下需要数据库进行保底。

约束 - 外键约束

1. 一句话总结：保证'子表引用父亲合理化'，避免孤儿数据。
2. 关键词：FOREIGN KEY / 父子表 / 引用完整性
3. 基础命令：

SHOW CREATE TABLE `order_item`;

-- 触发外键：order_id 不存在（会报 cannot add or update a child row）
INSERT INTO `order_item` (id, order_id, product_name, price, qty, created_at)
VALUES (79999, 99999, 'baditem', 1.00, 1, NOW());

4. 易错点：外键可能会带来写入约束与锁影响。因此在高并发场景下可能会替换掉约束。

约束 - 小结

1. 一句话总结：约束=数据库的最后一道防线，尤其主键/唯一/非空这些是高频应用，而外键则需要另外考虑。
2. 关键词：并发一致性 / 数据质量 / 兜底
3. 基础命令：

SHOW INDEX FROM `orders`;
SHOW CREATE TABLE `orders`;

4. 易错点：没有约束，仅靠业务查重，在并发场景下必翻车（典型：'先查后插'竞态）。

多表关系介绍

1. 一句话总结：关系：一对一/一对多/多对多，决定表结构与连接方式。
2. 关键词：1:1 / 1:n / n:m / 中间表
3. 基础命令：

-- 多对多 连表查询
SELECT u.username, r.name AS role_name 
FROM `user_role` ur JOIN `user` u ON ur.user_id = u.id 
JOIN `role` r ON ur.role_id = r.id 
WHERE u.id = 1001;

-- 查看中间表
SHOW CREATE TABLE `user_role`;

4. 易错点：多对多表格中，需要建立中间表。如果硬塞逗号隔开 = 灾难（无法索引，无法约束，难以维护）。

多表查询概述

1. 一句话总结：把多个表拼接成一个结果集。
2. 关键词：JOIN 连接表 / ON 筛选条件
3. 基础命令：

SELECT o.id, u.username, o.status, o.amount 
FROM `orders` o JOIN `user` u ON o.user_id = u.id;

4. 易错点：如果忘写 ON 或者条件写错，会导致笛卡尔积，结果爆炸且超慢。

内连接

1. 一句话总结：只要两边都匹配的记录：最常见的业务联表 (INNER JOIN = JOIN)。
2. 关键词：INNER JOIN / 等值连接
3. 基础命令：

SELECT o.id, u.username FROM `orders` o INNER JOIN `user` u ON o.user_id = u.id;
SELECT o.id, u.username FROM `orders` o JOIN `user` u ON o.user_id = u.id;

4. 易错点：列名冲突，一定要用 o.id，否则会造成不同表格之间的列名冲突。

外连接

1. 一句话总结：保留一侧：'用户列表 + 是否下过订单'。
2. 关键词：LEFT JOIN / RIGHT JOIN
3. 基础命令：

-- 主表为：user，主表
SELECT u.id, u.username, o.id AS order_id 
FROM `user` u LEFT JOIN `orders` o ON u.id = o.user_id;

-- 只保留那些「没有任何订单匹配」的用户
SELECT u.* FROM `user` u LEFT JOIN `orders` o ON u.id = o.user_id WHERE o.id IS NULL;

4. 易错点：把右表 ON 内的匹配条件，写到 WHERE 中，会把 LEFT JOIN 变成 INNER JOIN。因为 ON 决定是如何匹配，而 WHERE 决定的是哪些行能活下来。

自连接

1. 一句话总结：一张表自己连自己：一般用于组织关系/上下级/…
2. 关键词：层级关系 / 别名
3. 基础命令：

SELECT e.name AS emp_name, m.name AS manager_name 
FROM `emp` e LEFT JOIN `emp` m ON e.manager_id = m.id;
SELECT * FROM `emp` WHERE manager_id IS NULL;

4. 易错点：别名起的不好，就会错列；自连接必须全程通过别名区分。

联合查询 UNION

1. 一句话总结：把两条查询上下拼接成一张结果（字段数与类型要对齐）。
2. 关键词：UNION 去重 / UNION ALL 不去重
3. 基础命令：

-- 去重
SELECT id FROM `t_a` UNION SELECT id FROM `t_b`;

-- 不去重
SELECT id FROM `t_a` UNION ALL SELECT id FROM `t_b`;

4. 易错点：默认 UNION 会去重，稍慢。所以能用 UNION ALL 尽量就用这个。

子查询

1. 一句话总结：把一个查询的结果当成另一个查询的条件/表；常用于'先算出集合再过滤'。
2. 关键词：IN / EXISTS
3. 基础命令：

-- r&d 部门的员工
SELECT * FROM `emp` WHERE dept_id IN (SELECT id FROM `dept` WHERE name = 'r&d');

-- EXISTS 写法
SELECT * FROM `emp` e WHERE EXISTS (SELECT 1 FROM `dept` d WHERE d.id = e.dept_id AND d.name = 'r&d');

4. 易错点：子查询不一定慢，但相关子查询写不好会重复执行；能 JOIN 时也要会解释取舍。

标量子查询

1. 一句话总结：子查询返回单个值：用于'和某个统计值比较'。
2. 关键词：SCALAR / 标量 / 聚合比较
3. 基础命令：

SELECT * FROM `emp` WHERE salary > (SELECT AVG(salary) FROM `emp`);
SELECT * FROM `orders` WHERE amount = (SELECT MAX(amount) FROM `orders`);

4. 易错点：子查询返回多行会报错：Subquery returns more than 1 row，必须保证唯一或加聚合 / LIMIT。

列子查询

1. 一句话总结：子查询返回一列多行：用于 IN/ANY/ALL。
2. 关键词：IN / ANY / ALL
3. 基础命令：

SELECT * FROM `emp` WHERE dept_id IN (SELECT id FROM `dept` WHERE city = 'beijing');
SELECT * FROM `emp` WHERE salary > ALL (SELECT salary FROM `emp` WHERE dept_id = 10);

4. 易错点：IN 遇到 NULL 可能让逻辑变怪；必要时用 EXISTS 更稳。因为 SQL 不是'二值逻辑'，而是'三值逻辑'：TRUE / FALSE / UNKNOWN（未知）。

可以动手看看，这个简单的例子：

-- 这样没啥问题
SELECT * FROM `emp` WHERE id IN (NULL, 1, 2);

-- 真正会把你坑惨的，是 NOT IN+NULL
SELECT * FROM `emp` WHERE id NOT IN (NULL, 1, 2);
-- 明明有好几行符合条件的，却一行都不会返回！！

行子查询

1. 一句话总结：自查询返回一行多列。
2. 关键词：ROW CONSTRUCTOR / 多列比较
3. 基础命令：

SELECT * FROM `emp` WHERE (dept_id, job) = (SELECT dept_id, job FROM `emp` WHERE id = 2);

4. 易错点：复合列要有合适联合索引，不然容易全表扫。

表子查询

1. 一句话总结：把子查询当作临时表 (派生表)，在 JOIN / 过滤。
2. 关键词：临时表 / DERIVED TABLE
3. 基础命令：

SELECT x.dept_id, x.c 
FROM (SELECT dept_id, COUNT(*) AS c FROM emp GROUP BY dept_id) x 
WHERE x.c > 2;

SELECT y.user_id, y.total_amount 
FROM (SELECT user_id, SUM(amount) AS total_amount FROM orders GROUP BY user_id) y 
ORDER BY y.total_amount DESC LIMIT 5;

4. 易错点：派生表可能物化到临时表，数据量大很慢；尽量让派生表可被优化（字段少、条件早过滤）。

事务简介

1. 一句话总结：事务，用于把多步操作，变成'一同成功，或一同失败'。
2. 关键词：TRANSACTION / COMMIT / ROLLBACK
3. 基础命令：

START TRANSACTION;
COMMIT;
-- 或 ROLLBACK;

4. 易错点：MySQL 默认 AUTOCOMMIT=1，你以为在事务里其实不是；要么显式开启，要么在框架里确认。

事务演示

1. 一句话总结：必知步骤：开启事务 -> 更新 -> 异常回滚 -> 保证了一致性。
2. 关键词：锁 / 回滚 / 一致性
3. 基础命令：

-- 开启事务
START TRANSACTION;
UPDATE `account` SET balance = balance - 100, updated_at = NOW() WHERE id = 9001;
UPDATE `account` SET balance = balance + 100, updated_at = NOW() WHERE id = 9002;
-- 提交
COMMIT;

4. 易错点：表引擎不是 InnoDB（如 MyISAM）不支持事务；面试一问就中招。

ACID 四大特性

1. 一句话总结：用 ACID 解释'为什么事务可靠'：原子/一致/隔离/持久。
2. 关键词：ATOMIC / CONSISTENT / ISOLATED / DURABLE
3. 基础命令：

-- 查看是否开启自动提交
SELECT @@AUTOCOMMIT;

-- 查看隔离程度
SHOW VARIABLES LIKE 'transaction_isolation';

4. 易错点：你说'有事务就绝对一致'不严谨：一致性还依赖约束、正确的业务逻辑与隔离级别。

并发事务问题

1. 一句话总结：并发下会出现：脏读、不可重复读、幻读。
2. 关键词：DIRTY READ / NON-REPEATABLE / PHANTOM
3. 基础命令：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

4. 易错点：MySQL InnoDB 在 RR 隔离级别下，普通查询通过 MVCC 保证可重复读并避免幻读；而在范围更新或锁定读中，会通过行锁和间隙锁（Next-Key Lock）防止幻读，但这也可能导致范围锁定，从而引发意料之外的锁等待。

隔离级别

1. 一句话总结：不同的隔离级别，解决了什么问题？代价是什么？
2. 关键词：RU / RC / RR / SERIALIZABLE / MVCC
3. 基础命令：

START TRANSACTION;
SELECT * FROM `account` WHERE id = 9001 FOR UPDATE;

-- 另一个会话可用共享锁读（mysql8 推荐 FOR SHARE）
SELECT * FROM `account` WHERE id = 9001 FOR SHARE;

-- 提交/回滚释放锁
COMMIT;

4. 易错点：FOR UPDATE 在无索引条件下可能锁全表/大量行；一定要让 WHERE 走索引。

实验数据，可直接插入 mysql

-- =========================================================
-- MySQL 8（InnoDB + utf8mb4）
-- =========================================================
DROP DATABASE IF EXISTS interview_mysql;
CREATE DATABASE interview_mysql DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE interview_mysql;

-- 关闭安全更新（练习 UPDATE/DELETE 时更方便；生产不建议）
SET SESSION sql_safe_updates = 0;

-- -----------------------
-- 清理旧表（按外键依赖顺序）
-- -----------------------
DROP TABLE IF EXISTS order_item;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS post;
DROP TABLE IF EXISTS user_role;
DROP TABLE IF EXISTS role;
DROP TABLE IF EXISTS account;
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
DROP TABLE IF EXISTS log;
DROP TABLE IF EXISTS t_a;
DROP TABLE IF EXISTS t_b;
DROP TABLE IF EXISTS user;

-- -----------------------
-- 1) 用户表（DQL / NULL / 字符串函数）
-- -----------------------
CREATE TABLE user (
    id BIGINT PRIMARY KEY,
    username VARCHAR(32) NOT NULL UNIQUE,
    nickname VARCHAR(32) NULL,
    email VARCHAR(64) NOT NULL UNIQUE,
    city VARCHAR(32) NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    INDEX idx_user_city (city),
    INDEX idx_user_created (created_at)
) ENGINE=InnoDB;

-- -----------------------
-- 2) 角色表 + 多对多中间表（多表/约束）
-- -----------------------
CREATE TABLE role (
    id INT PRIMARY KEY,
    name VARCHAR(32) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE user_role (
    user_id BIGINT NOT NULL,
    role_id INT NOT NULL,
    PRIMARY KEY (user_id, role_id),
    CONSTRAINT fk_ur_user FOREIGN KEY (user_id) REFERENCES user(id),
    CONSTRAINT fk_ur_role FOREIGN KEY (role_id) REFERENCES role(id)
) ENGINE=InnoDB;

-- -----------------------
-- 3) 部门/员工（JOIN + 自连接 + 聚合）
-- -----------------------
CREATE TABLE dept (
    id INT PRIMARY KEY,
    name VARCHAR(32) NOT NULL UNIQUE,
    city VARCHAR(32) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE emp (
    id INT PRIMARY KEY,
    name VARCHAR(32) NOT NULL,
    dept_id INT NOT NULL,
    manager_id INT NULL,
    job VARCHAR(32) NOT NULL,
    salary DECIMAL(10,2) NOT NULL,
    hired_at DATE NOT NULL,
    CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES dept(id),
    CONSTRAINT fk_emp_mgr FOREIGN KEY (manager_id) REFERENCES emp(id),
    INDEX idx_emp_dept (dept_id),
    INDEX idx_emp_mgr (manager_id),
    INDEX idx_emp_salary (salary)
) ENGINE=InnoDB;

-- -----------------------
-- 4) 账户表（事务转账练习）
-- -----------------------
CREATE TABLE account (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL UNIQUE,
    balance DECIMAL(12,2) NOT NULL,
    updated_at DATETIME NOT NULL,
    CONSTRAINT fk_acc_user FOREIGN KEY (user_id) REFERENCES user(id),
    INDEX idx_acc_balance (balance)
) ENGINE=InnoDB;

-- -----------------------
-- 5) 订单/明细（JOIN / 外键 / 分组统计 / NULL pay_time）
-- -----------------------
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    status VARCHAR(16) NOT NULL, -- CREATED/PAID/CANCELLED/REFUNDED
    amount DECIMAL(12,2) NOT NULL,
    created_at DATETIME NOT NULL,
    pay_time DATETIME NULL,
    updated_at DATETIME NOT NULL,
    CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES user(id),
    INDEX idx_orders_user (user_id),
    INDEX idx_orders_status_time (status, created_at),
    INDEX idx_orders_paytime (pay_time)
) ENGINE=InnoDB;

CREATE TABLE order_item (
    id BIGINT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    product_name VARCHAR(64) NOT NULL,
    price DECIMAL(12,2) NOT NULL,
    qty INT NOT NULL,
    created_at DATETIME NOT NULL,
    CONSTRAINT fk_item_order FOREIGN KEY (order_id) REFERENCES orders(id),
    INDEX idx_item_order (order_id)
) ENGINE=InnoDB;

-- -----------------------
-- 6) 帖子表（排序/分页/统计）
-- -----------------------
CREATE TABLE post (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    title VARCHAR(128) NOT NULL,
    score INT NOT NULL,
    created_at DATETIME NOT NULL,
    CONSTRAINT fk_post_user FOREIGN KEY (user_id) REFERENCES user(id),
    INDEX idx_post_score_time (score, created_at),
    INDEX idx_post_user (user_id)
) ENGINE=InnoDB;

-- -----------------------
-- 7) 日志表（DML 删除练习：清理 30 天前）
-- -----------------------
CREATE TABLE log (
    id BIGINT PRIMARY KEY,
    level VARCHAR(8) NOT NULL,
    message VARCHAR(255) NOT NULL,
    created_at DATETIME NOT NULL,
    INDEX idx_log_time (created_at),
    INDEX idx_log_level (level)
) ENGINE=InnoDB;

-- -----------------------
-- 8) UNION 练习用表
-- -----------------------
CREATE TABLE t_a (
    id INT PRIMARY KEY
) ENGINE=InnoDB;

CREATE TABLE t_b (
    id INT PRIMARY KEY
) ENGINE=InnoDB;

-- =========================================================
-- 插入数据
-- =========================================================
-- users（含 nickname NULL、不同城市、不同时间）
INSERT INTO user (id, username, nickname, email, city, created_at, updated_at) 
VALUES 
(1001, 'alice', '阿丽', '[email protected]', 'Beijing', '2025-11-01 10:00:00', '2025-12-10 09:10:00'),
(1002, 'bob', NULL, '[email protected]', 'Beijing', '2025-11-05 11:00:00', '2025-12-11 12:00:00'),
(1003, 'carol', '小 C', '[email protected]', 'Shanghai', '2025-10-20 09:30:00', '2025-12-09 08:00:00'),
(1004, 'dave', NULL, '[email protected]', 'Shanghai', '2025-11-12 16:20:00', '2025-12-12 19:00:00'),
(1005, 'erin', 'E 宝', '[email protected]', 'Shenzhen', '2025-11-18 14:00:00', '2025-12-13 10:01:00'),
(1006, 'frank', NULL, '[email protected]', 'Shenzhen', '2025-11-21 17:45:00', '2025-12-13 17:45:00'),
(1007, 'grace', '小 G', '[email protected]', 'Guangzhou', '2025-10-01 08:00:00', '2025-12-01 08:00:00'),
(1008, 'henry', NULL, '[email protected]', 'Guangzhou', '2025-09-15 09:00:00', '2025-11-30 10:00:00'),
(1009, 'ivy', 'Ivy', '[email protected]', 'Hangzhou', '2025-11-30 20:00:00', '2025-12-12 20:00:00'),
(1010, 'jack', NULL, '[email protected]', 'Hangzhou', '2025-12-01 09:00:00', '2025-12-13 09:00:00'),
(1011, 'kate', 'K', '[email protected]', 'Beijing', '2025-10-10 10:10:10', '2025-12-13 23:59:00'),
(1012, 'leo', NULL, '[email protected]', 'Shanghai', '2025-12-05 12:12:12', '2025-12-12 12:12:12');

-- roles
INSERT INTO role (id, name) VALUES (1, 'admin'), (2, 'merchant'), (3, 'customer');

-- user_role（多对多）
INSERT INTO user_role (user_id, role_id) VALUES 
(1001, 1), (1001, 3), (1002, 3), (1003, 2), (1003, 3), (1004, 3), 
(1005, 2), (1006, 3), (1007, 3), (1008, 3), (1009, 3), (1010, 3), 
(1011, 1), (1012, 3);

-- dept
INSERT INTO dept (id, name, city) VALUES (10, 'R&D', 'Beijing'), (20, 'Sales', 'Shanghai'), (30, 'Ops', 'Shenzhen');

-- emp（自连接：先插 manager，再插下属）
INSERT INTO emp (id, name, dept_id, manager_id, job, salary, hired_at) VALUES 
(1, 'ZhangBoss', 10, NULL, 'Director', 35000.00, '2022-01-01'),
(2, 'LiDev', 10, 1, 'Backend', 22000.00, '2023-03-15'),
(3, 'WangDev', 10, 1, 'Backend', 21000.00, '2023-06-01'),
(4, 'ChenQA', 10, 1, 'QA', 18000.00, '2024-02-20'),
(5, 'ZhaoSales', 20, NULL, 'Manager', 26000.00, '2021-09-10'),
(6, 'SunSales', 20, 5, 'Sales', 16000.00, '2024-05-01'),
(7, 'WuOps', 30, NULL, 'Manager', 24000.00, '2022-11-11'),
(8, 'HeSRE', 30, 7, 'SRE', 20000.00, '2024-08-08');

-- account（事务练习：转账）
INSERT INTO account (id, user_id, balance, updated_at) VALUES 
(9001, 1001, 1000.00, '2025-12-13 10:00:00'),
(9002, 1002, 250.50, '2025-12-13 10:00:00'),
(9003, 1003, 88.80, '2025-12-13 10:00:00'),
(9004, 1004, 500.00, '2025-12-13 10:00:00'),
(9005, 1005, 999.99, '2025-12-13 10:00:00'),
(9006, 1006, 10.00, '2025-12-13 10:00:00'),
(9007, 1007, 66.66, '2025-12-13 10:00:00'),
(9008, 1008, 77.77, '2025-12-13 10:00:00'),
(9009, 1009, 300.00, '2025-12-13 10:00:00'),
(9010, 1010, 120.00, '2025-12-13 10:00:00'),
(9011, 1011, 8888.88, '2025-12-13 10:00:00'),
(9012, 1012, 15.00, '2025-12-13 10:00:00');

-- orders（包含：已支付/未支付/取消/退款；也包含'无明细订单'用于 LEFT JOIN）
INSERT INTO orders (id, user_id, status, amount, created_at, pay_time, updated_at) VALUES 
(50001, 1001, 'PAID', 128.00, '2025-12-01 10:00:00', '2025-12-01 10:02:00', '2025-12-01 10:02:00'),
(50002, 1001, 'CREATED', 66.60, '2025-12-10 12:00:00', NULL, '2025-12-10 12:00:00'),
(50003, 1002, 'PAID', 19.90, '2025-12-02 09:00:00', '2025-12-02 09:01:00', '2025-12-02 09:01:00'),
(50004, 1003, 'CANCELLED', 58.00, '2025-11-20 18:30:00', NULL, '2025-11-20 18:45:00'),
(50005, 1003, 'PAID', 199.00, '2025-12-05 20:00:00', '2025-12-05 20:10:00', '2025-12-05 20:10:00'),
(50006, 1004, 'REFUNDED', 88.00, '2025-11-28 13:00:00', '2025-11-28 13:05:00', '2025-11-30 09:00:00'),
(50007, 1005, 'PAID', 520.00, '2025-12-11 11:11:11', '2025-12-11 11:12:00', '2025-12-11 11:12:00'),
(50008, 1006, 'CREATED', 39.90, '2025-12-12 15:00:00', NULL, '2025-12-12 15:00:00'),
(50009, 1009, 'PAID', 15.00, '2025-12-03 08:00:00', '2025-12-03 08:00:30', '2025-12-03 08:00:30'),
(50010, 1010, 'PAID', 88.88, '2025-12-13 09:09:09', '2025-12-13 09:10:00', '2025-12-13 09:10:00');

-- order_item（50002/50008 故意不给明细；练习'找无明细订单'）
INSERT INTO order_item (id, order_id, product_name, price, qty, created_at) VALUES 
(70001, 50001, 'Keyboard', 128.00, 1, '2025-12-01 10:00:10'),
(70002, 50003, 'Notebook', 19.90, 1, '2025-12-02 09:00:10'),
(70003, 50005, 'Monitor', 199.00, 1, '2025-12-05 20:00:10'),
(70004, 50006, 'Mouse', 88.00, 1, '2025-11-28 13:00:10'),
(70005, 50007, 'Phone', 520.00, 1, '2025-12-11 11:11:20'),
(70006, 50009, 'Cable', 15.00, 1, '2025-12-03 08:00:10'),
(70007, 50010, 'Headset', 88.88, 1, '2025-12-13 09:09:20');

-- posts（用于 ORDER BY / LIMIT / GROUP BY / HAVING）
INSERT INTO post (id, user_id, title, score, created_at) VALUES 
(80001, 1001, 'MySQL 基础复盘', 98, '2025-12-01 09:00:00'),
(80002, 1001, 'JOIN 面试题整理', 88, '2025-12-06 09:00:00'),
(80003, 1002, '分页优化：深分页到游标', 95, '2025-12-10 21:00:00'),
(80004, 1003, '事务与锁：RR 是否幻读', 93, '2025-12-08 20:00:00'),
(80005, 1003, 'EXPLAIN 怎么看', 80, '2025-11-22 10:00:00'),
(80006, 1004, '子查询与 EXISTS 对比', 70, '2025-12-02 19:00:00'),
(80007, 1005, '外键要不要上生产', 85, '2025-12-03 13:00:00'),
(80008, 1006, '字符串函数清洗数据', 60, '2025-12-04 14:00:00'),
(80009, 1007, 'GROUP BY + HAVING 实战', 92, '2025-12-05 16:00:00'),
(80010, 1008, '慢查询排查流程', 99, '2025-12-07 18:00:00'),
(80011, 1009, 'NULL 处理：COALESCE', 75, '2025-12-09 08:00:00'),
(80012, 1010, 'UNION vs UNION ALL', 65, '2025-12-12 08:30:00');

-- log（包含 30 天前的记录，练习 DELETE）
INSERT INTO log (id, level, message, created_at) VALUES 
(60001, 'INFO', 'service started', '2025-12-13 10:00:00'),
(60002, 'WARN', 'slow query detected', '2025-12-12 11:00:00'),
(60003, 'ERROR', 'db connection timeout', '2025-12-11 12:00:00'),
(60004, 'INFO', 'daily job finished', '2025-11-10 09:00:00'),
(60005, 'INFO', 'old log for cleanup (40d ago)', '2025-11-01 00:00:00'),
(60006, 'INFO', 'old log for cleanup (70d ago)', '2025-10-05 00:00:00');

-- UNION 练习数据
INSERT INTO t_a (id) VALUES (1), (2), (3), (10);
INSERT INTO t_b (id) VALUES (3), (4), (5), (10);

-- =========================================================
-- （可选）快速自检：确认数据量
-- =========================================================
-- SELECT COUNT(*) AS users FROM user;
-- SELECT status, COUNT(*) FROM orders GROUP BY status;
-- SELECT city, COUNT(*) FROM user GROUP BY city;