MySQL必备基础
MySQL必备基础
仅仅阅读，是远没有实践记得清楚。
我将建表语句放在了这里：实验数据，可直接插入mysql
而本文的sql操作，统一采用的是这里的数据。
（如果点击无效，可以直接翻到文章末尾，具体代码在文末）
DML-插入
1、一句话总结：在什么场景下，解决了什么问题：
在新增业务数据（如注册用户、创建订单、写日志）场景下，insert 用来把新记录持久化到数据库，解决“数据从业务产生到落库保存”的问题。
2、2~4个关键词：
insert into / values
3、2个基础，并直接能用的命令：
-- 更新状态-- 1) 单行插入：新增一个用户（注意：显式写列名，最稳）insertinto`user`(id, username, nickname, email, city, created_at, updated_at)values(2002,'mike',null,'[email protected]','Beijing',now(),now());-- 2) 多行插入：一次插入多条日志（演示批量 values）insertinto`log`(id,level, message, created_at)values(60010,'info','new log 1',now()),(60011,'warn','new log 2',now());4、1个坑，最容易翻车的点：
不写列名导致列顺序一变就插错；最好永远写上列名。
DML-更新和删除
1、一句话总结：在什么场景下，解决了什么问题：
改/删数据，用于修改业务状态。通过where进行精确定位，避免误伤。
2、2~4个关键词：
delete / update / where/ limit
3、2个基础，并直接能用的命令：
-- 更新状态update`orders`setstatus='PAID'where id =50002;-- 删除日志deletefrom`log`where created_at<NOW()-interval30day4、1个坑，最容易翻车的点：
delete中，漏写where，导致全表数据删除。
DQL-基础查询
1、一句话总结：在什么场景下，解决了什么问题：
查数据并做列表，只拿需要的列，有条件，有分页。
2、2~4个关键词：
select / distinct / 列投影 / as
3、2个基础，并直接能用的命令：
select id,username fromuser;selectdistinct city fromuser;4、1个坑，最容易翻车的点：
select * 在查询大表/宽表时很慢，并且容易触发回表与网络IO
DQL-条件查询
1、一句话总结：在什么场景下，解决了什么问题：
用where筛选，筛选出"我需要的数据"
2、2~4个关键词：
where / like / or / between
3、2个基础，并直接能用的命令：
-- 查今天已支付订单SELECT*FROM`orders`WHEREstatus='PAID'AND pay_time >= CURDATE();-- 查 username 以 a 开头 或 指定 id 集合SELECT*FROM`user`WHERE username LIKE'a%'OR id IN(1001,1002,1003);4、1个坑，最容易翻车的点：
null 不能用 " =null " ，而应该用 is null / is not null
DQL-聚合函数
1、一句话总结：在什么场景下，解决了什么问题：
统计报表：总数，平均数，最大最小值
2、2~4个关键词：
sum / avg / min / max
3、2个基础，并直接能用的命令：
-- 总数selectcount(*)from orders wherestatus='PAID';-- 分组+平均数select dept_id,avg(salary)as avg_salary from emp groupby dept_id;4、1个坑，最容易翻车的点：
count(col) ，会忽略掉NULL值，所以用count(*)，是最保底的选择。
DQL-分组查询
1、一句话总结：在什么场景下，解决了什么问题：
分组聚合，并按 “维度查询”
2、2~4个关键词：
group by / having / 维度聚合
3、2个基础，并直接能用的命令：
select city ,count(*)fromusergroupby city ;select city ,count(*)as c fromusergroupby city having c>2;4、1个坑，最容易翻车的点：
where vs having：其中，where过滤的是行，而having过滤的是聚合后的结果
DQL-排序查询
1、一句话总结：在什么场景下，解决了什么问题：
列表，按照时间、分数…排序
2、2~4个关键词：
order by / desc / asc
3、2个基础，并直接能用的命令：
select*from`post`orderby created_at desc;select*from`post`orderby score desc, created_at desc;4、1个坑，最容易翻车的点：
大 OFFSET 排序很慢。（可以通过索引进行优化）
DQL-分页查询
1、一句话总结：在什么场景下，解决了什么问题：
列表分页，解决 “只取一页的问题” 。
2、2~4个关键词：
limit / offset / 深分页
3、2个基础，并直接能用的命令：
select*from`post`orderby id desclimit5offset0;-- 游标分页（直接确定id，然后往前翻）select*from`post`where id<100000orderby id desclimit3;4、1个坑，最容易翻车的点：
深分页，如offset=10000会抛弃大量行，所以推荐使用游标分页。
DQL-案例联系
1、一句话总结：在什么场景下，解决了什么问题：
综合使用
2、2~4个关键词：
组合查询 / 可读性 / 逐步验证
3、2个基础，并直接能用的命令：
-- 例：查北京用户的已支付订单列表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'ORDERBY o.created_at DESCLIMIT5;EXPLAINSELECT 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'ORDERBY o.created_at DESCLIMIT5;4、1个坑，最容易翻车的点：
不 explain 就上生产，就很容易写出慢查询
DQL-执行顺序
1、一句话总结：在什么场景下，解决了什么问题：
解释，为什么别名能够在having中用，而不能在where中用。
2、2~4个关键词：
from / where / limit / select / group by / having
3、2个基础，并直接能用的命令：
select dept_id,count(*)as c from emp groupby dept_id having c>1;4、1个坑，最容易翻车的点：
执行顺序
from > where > group by > having > select > order by > limit
原本按顺序，having应该也无法看到别名，但是mysql做了拓展。
DQL-小结
1、一句话总结：在什么场景下，解决了什么问题：
查询 = “筛选(where)+聚合(group)+排序(order)+分页(limit)+索引(explain)”
2、2~4个关键词：
explain 回表 索引
3、2个基础，并直接能用的命令：
EXPLAINSELECT...;--暂时，还没怎么用过SHOWWARNINGS;（看优化器重写/警告） 4、1个坑，最容易翻车的点：
只会写代码，而不会检查，非常掉分。
DCL-用户管理
1、一句话总结：在什么场景下，解决了什么问题：
管理账号与登录来源，用最小的权限，隔离环境
2、2~4个关键词：
create user / alter user / drop user
3、2个基础，并直接能用的命令：
createuser'user'@'localhost' identified by'123456';alteruser'user'@'localhost' identified by'1234';4、1个坑，最容易翻车的点：
‘user’@‘localhost’ 与 ‘user’@'%'是两条不同的账户，经常配错，出现能连/不能连的问题。(需注意优先级)
DCL-权限控制
1、一句话总结：在什么场景下，解决了什么问题：
控制谁，能对某些表，进行特定的权限操作。避免越权与误删。
2、2~4个关键词：
grant / revoke / show grants
3、2个基础，并直接能用的命令：
grantselect,alteron interview_mysql.dept to'user'@'localhost';--展示权限show grants for'user'@'localhost'4、1个坑，最容易翻车的点：
直接给 grant all 是管理上的惰性，会给未来增加不可预测的风险。
DCL-小结
1、一句话总结：在什么场景下，解决了什么问题：
账号=“主题”， 权限=“动作+资源范围”，原则=“最小权限”
2、2~4个关键词：
最小权限 / 审计 / 账号隔离
3、2个基础，并直接能用的命令：
-- 删除revokeselecton interview_mysql.dept from'user'@'localhost';-- 刷新，并清空 flush privileges;4、1个坑，最容易翻车的点：
误以为改了权限就会马上生效：alter/revoke在mysql中自动刷新，通常不需要flush。
函数-字符串函数
1、一句话总结：在什么场景下，解决了什么问题：
清洗/格式化文本，拼接展示
2、2~4个关键词：
concat / replace / substring / length
3、2个基础，并直接能用的命令：
-- concat(..)组合-- coalesce(..)选取第一个非空select concat(username,'(',coalesce(nickname,'-'),')')as showName fromuser;--替换selectreplace(email,'@example.com','@demo.com')fromuser;4、1个坑，最容易翻车的点：
length()统计长度，中文可能会变长。用char_length()更合适。
函数-数据函数
1、一句话总结：在什么场景下，解决了什么问题：
做 金额/评分/统计 时，需要用到的数值处理
2、2~4个关键词：
round / ceil/floor / abs / decimal
3、2个基础，并直接能用的命令：
select id,round(amount,2)as money from orders;select id,floor(amount),ceil(amount)as money from orders;4、1个坑，最容易翻车的点：
金额计算时别用浮点数存储，用decimal(10,2)-定点数，否则会有精度的坑。
函数-日期函数
1、一句话总结：在什么场景下，解决了什么问题：
解决了数据分析与时间报表中，对时间的分析与处理。
2、2~4个关键词：
interval / date / date_add / timestampdiff
3、2个基础，并直接能用的命令：
select*from orders where created_at>now()-interval7day;selectdate(created_at)as d ,count(*)as c from orders groupby d orderby c --计算时间差值(时间单位,time1,time2)select timestampdiff(day,date(created_at),'2025-11-20')as diff from orders 4、1个坑，最容易翻车的点：
应用/DB 时区不一致会导致时间错位。可以统一时区，或者用UTC。
函数-流程函数
1、一句话总结：在什么场景下，解决了什么问题：
在sql层，做简单的分支控制 / 空值处理 / 分类统计
2、2~4个关键词：
case / coalesce / if / when
3、2个基础，并直接能用的命令：
SELECT id,COALESCE(nickname, username)AS show_name FROM`user`;selectsum(casewhenstatus='PAID'then1else0end)as paid_status,sum(casewhenstatus='CREATED'then1else0end)as created_status from orders;4、1个坑，最容易翻车的点：
case太复杂，会导致难以维护，应放在应用层。
约束-概述
1、一句话总结：在什么场景下，解决了什么问题：
用约束保证数据的正确性：唯一/非空/范围…等等
2、2~4个关键词：
primary key / unique / not null / check
3、2个基础，并直接能用的命令：
showcreatetable`user`;showindexfrom`user`;4、1个坑，最容易翻车的点：
只靠代码约束不保底；并发下需要数据库进行保底。
约束-外键约束
1、一句话总结：在什么场景下，解决了什么问题：
保证 “子表引用父亲合理化” ，避免孤儿数据。
2、2~4个关键词：
foreign key / 父子表 / 引用完整性
3、2个基础，并直接能用的命令：
showcreatetable`order_item`;-- 触发外键：order_id 不存在（会报 cannot add or update a child row）insertinto`order_item`(id, order_id, product_name, price, qty, created_at)values(79999,99999,'baditem',1.00,1,now());4、1个坑，最容易翻车的点：
外键可能会带来写入约束与锁影响。因此在高并发场景下可能会替换 掉约束。
约束-小结
1、一句话总结：在什么场景下，解决了什么问题：
约束=数据库的最后一道防线，尤其主键/唯一/非空这些是高频应用，而外键则需要另外考虑。
2、2~4个关键词：
并发一致性 / 数据质量 / 兜底
3、2个基础，并直接能用的命令：
showindexfrom`orders`;showcreatetable`orders`;4、1个坑，最容易翻车的点：
没有约束，仅靠业务查重，在并发场景下必翻车（典型："先查后插"竞态）
多表关系介绍
1、一句话总结：在什么场景下，解决了什么问题：
关系：一对一/一对多/多对多，决定表结构与连接方式。
2、2~4个关键词：
1:1 / 1:n / n:m / 中间表
3、2个基础，并直接能用的命令：
-- 多对多 连表查询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;-- 查看中间表showcreatetable`user_role`;4、1个坑，最容易翻车的点：
多对多表格中，需要建立中间表。如果硬塞逗号隔开 = 灾难（无法索引，无法约束，难以维护），
多表查询概述
1、一句话总结：在什么场景下，解决了什么问题：
把多个表拼接成一个结果集
2、2~4个关键词：
join连接表 / on筛选条件
3、2个基础，并直接能用的命令：
select o.id, u.username, o.status, o.amount from`orders` o join`user` u on o.user_id=u.id;4、1个坑，最容易翻车的点：
如果忘写on或者条件写错，会导致笛卡尔积，结果爆炸且超慢。
内连接
1、一句话总结：在什么场景下，解决了什么问题：
只要两边都匹配的记录：最常见的业务联表(inner join = join)
2、2~4个关键词：
inner join / 等值连接
3、2个基础，并直接能用的命令：
select o.id, u.username from`orders` o innerjoin`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、1个坑，最容易翻车的点：
列名冲突，一定要用o.id，否则会造成不同表格之间的列名冲突。
外连接
1、一句话总结：在什么场景下，解决了什么问题：
保留一侧：“用户列表+是否下过订单”
2、2~4个关键词：
left join / right join
3、2个基础，并直接能用的命令：
-- 主表为：user，主表select u.id, u.username, o.id as order_id from`user` u leftjoin`orders` o on u.id = o.user_id;-- 只保留那些「没有任何订单匹配」的用户select u.*from`user` u leftjoin`orders` o on u.id = o.user_id where o.id isnull;4、1个坑，最容易翻车的点：
把右表on内的匹配条件，写到where中，会把left join 变成 inner join。
因为on决定是如何匹配，而where决定的是那些行能活下来。
自连接
1、一句话总结：在什么场景下，解决了什么问题：
一张表自己连自己：一般用于 组织关系/上下级/…
2、2~4个关键词：
层级关系 / 别名
3、2个基础，并直接能用的命令：
select e.name as emp_name, m.name as manager_name from`emp` e leftjoin`emp` m on e.manager_id = m.id;select*from`emp`where manager_id isnull;4、1个坑，最容易翻车的点：
别名起的不好，就会错列；自连接必须全程通过别名区分。
联合查询 union
1、一句话总结：在什么场景下，解决了什么问题：
把两条查询上下拼接成一张结果（字段数与类型要对齐）
2、2~4个关键词：
union 去重 / union all 不去重
3、2个基础，并直接能用的命令：
-- 去重select id from`t_a`unionselect id from`t_b`;-- 不去重select id from`t_a`unionallselect id from`t_b`;4、1个坑，最容易翻车的点：
默认union会去重，稍慢。所以能用union all尽量就用这个。
子查询
1、一句话总结：在什么场景下，解决了什么问题：
把一个查询的结果当成另一个查询的条件/表；常用于“先算出集合再过滤”
2、2~4个关键词：
in / exists
3、2个基础，并直接能用的命令：
-- r&d 部门的员工select*from`emp`where dept_id in(select id from`dept`where name ='r&d');-- exists 写法select*from`emp` e whereexists(select1from`dept` d where d.id = e.dept_id and d.name ='r&d');4、1个坑，最容易翻车的点：
子查询不一定慢，但相关子查询写不好会重复执行；能 JOIN 时也要会解释取舍。
标量子查询
1、一句话总结：在什么场景下，解决了什么问题：
子查询返回单个值：用于“和某个统计值比较”。
2、2~4个关键词：
scalar / 标量 / 聚合比较
3、2个基础，并直接能用的命令：
SELECT*FROM`emp`WHERE salary >(SELECTAVG(salary)FROM`emp`);SELECT*FROM`orders`WHERE amount =(SELECTMAX(amount)FROM`orders`);4、1个坑，最容易翻车的点：
子查询返回多行会报错：Subquery returns more than 1 row，必须保证唯一或加聚合 / limit。
列子查询
1、一句话总结：在什么场景下，解决了什么问题：
子查询返回一列多行：用于 IN/ANY/ALL。
2、2~4个关键词：
in / any / all
3、2个基础，并直接能用的命令：
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、1个坑，最容易翻车的点：
in 遇到 null 可能让逻辑变怪；必要时用 exists 更稳。
因为 sql 不是“二值逻辑”，而是“三值逻辑”：true / false / unknown（未知）。
可以动手看看，这个简单的例子：
--这样没啥问题select*from`emp`where id in(null,1,2); 真正会把你坑惨的，是 notin+nullselect*from`emp`where id notin(null,1,2); 明明有好几行符合条件的，却一行都不会返回！！ 行子查询
1、一句话总结：在什么场景下，解决了什么问题：
自查询返回一行多列
2、2~4个关键词：
row constructor / 多列比较
3、2个基础，并直接能用的命令：
select*from`emp`where(dept_id, job)=(select dept_id, job from`emp`where id =2);4、1个坑，最容易翻车的点：
复合列要有合适联合索引，不然容易全表扫。
表子查询
1、一句话总结：在什么场景下，解决了什么问题：
把子查询当作临时表 (派生表)，在 join / 过滤
2、2~4个关键词：
临时表 / derived table
3、2个基础，并直接能用的命令：
select x.dept_id, x.c from(select dept_id,count(*)as c from emp groupby 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 groupby user_id) y orderby y.total_amount desclimit5;4、1个坑，最容易翻车的点：
派生表可能物化到临时表，数据量大很慢；尽量让派生表可被优化（字段少、条件早过滤）。
事务简介
1、一句话总结：在什么场景下，解决了什么问题：
事务，用于把多步操作，变成"一同成功，或一同失败"
2、2~4个关键词：
transaction / commit / rollback
3、2个基础，并直接能用的命令：
starttransaction;commit;-- 或rollback;4、1个坑，最容易翻车的点：
MySQL 默认 autocommit=1，你以为在事务里其实不是；要么显式开启，要么在框架里确认。
事务演示
1、一句话总结：在什么场景下，解决了什么问题：
必知步骤：开启事务 -> 更新 -> 异常回滚 -> 保证了一致性
2、2~4个关键词：
锁 / 回滚 / 一致性
3、2个基础，并直接能用的命令：
--开启事务starttransaction;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、1个坑，最容易翻车的点：
表引擎不是 InnoDB（如 MyISAM）不支持事务；面试一问就中招。
ACID四大特性
1、一句话总结：在什么场景下，解决了什么问题：
用 ACID 解释“为什么事务可靠”：原子/一致/隔离/持久。
2、2~4个关键词：
Atomic / Consistent / Isolated / Durable
3、2个基础，并直接能用的命令：
-- 查看是否开启自动提交select @@autocommit;-- 查看隔离程度show variables like'transaction_isolation';4、1个坑，最容易翻车的点：
你说“有事务就绝对一致”不严谨：一致性还依赖约束、正确的业务逻辑与隔离级别。
并发事务问题
1、一句话总结：在什么场景下，解决了什么问题：
并发下会出现：脏读、不可重复读、幻读。
2、2~4个关键词：
dirty read / non-repeatable / phantom
3、2个基础，并直接能用的命令：
setsessiontransactionisolationlevelreadcommitted;setsessiontransactionisolationlevelrepeatableread;4、1个坑，最容易翻车的点：
MySQL InnoDB 在 RR 隔离级别下，普通查询通过 MVCC 保证可重复读并避免幻读；而在范围更新或锁定读中，会通过行锁和间隙锁（Next-Key Lock）防止幻读，但这也可能导致范围锁定，从而引发意料之外的锁等待。
隔离级别
1、一句话总结：在什么场景下，解决了什么问题：
不同的隔离级别，解决了什么问题？代价是什么？
2、2~4个关键词：
RU / RC / RR / SERIALIZABLE / MVCC
3、2个基础，并直接能用的命令：
starttransaction;select*from`account`where id=9001forupdate;-- 另一个会话可用共享锁读（mysql8 推荐 for share）select*from`account`where id=9001forshare;-- 提交/回滚释放锁commit;4、1个坑，最容易翻车的点：
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; 本文为最小可复用笔记。
借鉴文章：
1、快速理解脏读、不可重复读、幻读和MVCC
2、六个案例搞懂间隙锁
3、什么是排他锁、共享锁、意向锁
4、chatgpt5.2
5、黑马MySQL
