MySQL 全套基础知识清单｜DDL/DML/DQL/ 约束 / 多表查询 / 函数 / 窗口函数-平芜编程栈

📝前言：很多同学学 MySQL 知识点零散，写 SQL 踩各种坑：忘记where全表更新、分不清delete与truncate、多表连接搞不懂内外连接、窗口函数三种排名混淆。本文把 MySQL 核心基础整理成一份可查阅的知识清单，适合复习刷题、面试速查，所有 SQL 均可复制运行，零基础也能看懂。

环境准备

MySQL 版本：5.7 / 8.0（窗口函数仅MySQL8.0 及以上支持）
客户端：Navicat / MySQL 命令行 / DBeaver/ DBeaver

说明：本文全部 SQL 语句，在 MySQL5.7 下除窗口函数外均可执行；窗口函数必须 MySQL8.0+。

先建表：复制这一段，后面的例子全部能跑

全文所有示例都基于下面这 4 张表，你把它们一次性跑完，后面每个 SQL 都能直接复制执行、看到真实结果。

-- ============================================ -- MySQL 语法练习表（一次性执行即可） -- ============================================ drop database if exists practice; create database practice default charset utf8mb4; use practice; -- 1. 分类表 create table category ( cid int primary key auto_increment, cname varchar(20) not null ); -- 2. 商品表（多对一：多个商品属于一个分类） create table product ( pid int primary key auto_increment, pname varchar(50) not null, price decimal(10,2), category_id int, constraint fk_product_category foreign key (category_id) references category(cid) ); -- 3. 用户表 create table user ( id int primary key auto_increment, name varchar(20) not null, gender char(1) default '男', city varchar(20), age int ); -- 4. 员工表 create table employee ( id int primary key auto_increment, ename varchar(20) not null, salary decimal(10,2), dname varchar(20) ); -- 5. 学生成绩表 create table student ( id int primary key auto_increment, name varchar(20) not null, score int ); -- ============================================ -- 初始化数据 -- ============================================ insert into category(cname) values ('服装'),('数码'),('食品'); insert into product(pname, price, category_id) values ('羽绒服', 899.00, 1), ('牛仔裤', 299.00, 1), ('羊毛衫', 459.00, 1), ('笔记本电脑', 6999.00, 2), ('蓝牙耳机', 399.00, 2), ('机械键盘', 599.00, 2), ('牛肉干', 89.00, 3), ('坚果礼盒', 168.00, 3); insert into user(name, gender, city, age) values ('tom', '男', '北京', 20), ('jerry', '男', '上海', 22), ('lucy', '女', '广州', 19), ('lily', '女', null, 25); insert into employee(ename, salary, dname) values ('张三', 12000, '技术部'), ('李四', 9000, '技术部'), ('王五', 9000, '技术部'), ('赵六', 15000, '销售部'), ('钱七', 11000, '销售部'); insert into student(name, score) values ('小明', 92), ('小红', 85), ('小刚', 85), ('小美', 67), ('小李', 45); -- 验证一下 select count(*) from product; -- 应该返回 8

一、DDL 数据定义语言（操作库、表结构）

DDL：操作数据库、表、字段结构，不操作表里面的数据。核心关键字：create、alter、drop、rename

1.1 修改表字段（alter table）

表格

操作	语法说明	关键点
添加字段	`alter table 表名 add 字段类型(长度) [first / after 字段];`	`first`放到首位；`after col`放到指定列后面
修改字段类型 / 约束	`alter table 表名 modify 字段新类型(长度) 约束;`	只改类型约束，不改列名
修改列名 + 类型	`alter table 表名 change 旧列名新列名类型(长度) 约束;`	必须写新旧两个列名
删除列	`alter table 表名 drop 字段名;`	直接删除整列，谨慎操作
修改表名	`alter table 旧表 rename to 新表;`	推荐写法

查看表结构：

desc 表名; show create table 表名; -- 查看完整建表语句，含存储引擎

1.2 MySQL 存储引擎

InnoDB（默认）：支持事务、外键、行级锁；MySQL 5.6.4 起支持全文索引。业务项目首选。
MyISAM：查询性能高，支持全文索引；不支持事务、只支持表级锁、崩溃后恢复能力差。
Memory：数据存内存，速度快；重启/断电数据全部丢失，一般只做临时缓存。

选型的核心依据是锁粒度，不是全文索引： InnoDB 是行级锁（写操作只锁一行），MyISAM 是表级锁（任何写操作锁整张表）。这意味着 MyISAM 在"有并发写入"的场景下会严重阻塞——这才是它退出历史舞台的真正原因。

顺带纠正一个流传很广的错误说法："InnoDB 不支持全文索引"是 MySQL 5.5 及以前的老黄历了，5.6.4 之后就支持。

show engines; -- 查询数据库支持的引擎 alter table 表名 engine=InnoDB; -- 修改表引擎

二、DML 数据操纵语言（操作表中数据：增删改）

DML 用来操作行数据：insert新增，update修改，delete删除。⚠️高危：不带where条件会操作全表！

2.1 insert 插入数据

-- user 表结构：(id, name, gender, city, age) -- 1. 指定字段插入（推荐：表结构变了也不容易出错） insert into user(name, gender, city) values ('tom', '男', '北京'); -- 2. 全字段插入：必须按表字段顺序，值的个数必须一致 insert into user values (null, 'jerry', '男', '上海', 22); -- ↑ 主键位传 null 触发自增 -- 3. 批量插入（开发推荐，减少网络 IO） insert into user(name, gender, city) values ('lucy', '女', '广州'), ('lily', '女', '深圳'); -- 4. 插入冲突时更新（MySQL 特有，很实用） insert into user(id, name) values (1, 'tom_new') on duplicate key update name = 'tom_new';

为什么推荐写字段名？因为全字段插入依赖字段顺序，一旦有人给表加了列，你的 SQL 就废了。写字段名多敲几个字，省掉一次线上事故。

2.2 update 更新数据

-- 修改多个字段逗号分隔，支持算术表达式 update user set gender='女',age=18 where id=2; update user set age = age + 1; -- 全部用户年龄+1，不加where，全表更新！

2.3 delete 删除数据 & truncate（面试高频）

delete from user where id=1; -- 根据条件删除行 delete from user; -- 删除表全部数据，DML语句，可以回滚，自增主键不会重置 truncate table user; -- DDL语句，销毁重建空表；自增主键归零；不可回滚

✨面试考点delete VS truncate(区别）

delete：逐行删除，属于 DML，可以事务回滚；不会重置 auto_increment 自增；适合删除部分数据或者需要回滚场景。
truncate：直接删除重建表，DDL，不能回滚，重置自增主键；速度快，只能清空整张表。

三、MySQL 五大约束（保证数据合法性）

约束作用：防止脏数据入库，项目开发建议每张表设置主键。

先把两个常见误区说在前面：
1. `auto_increment` 不是约束，它是列属性。这是面试常考的区分点——约束是"限制你能存什么"，自增只是"帮你自动填值"，它不阻止任何数据入库。
2. 外键约束漏掉了。讲到多表关系却没提外键约束，是不完整的。

表格

约束	关键字	作用
主键约束	`primary key`	非空 + 唯一；一张表只能有一个，建议用无业务含义的 id
自增约束	`auto_increment`	整数主键自动生成 id；支持传 null/0 触发自增
非空约束	`not null`	该字段不能为 null
唯一约束	`unique`	字段不能重复；允许多个 null（这是和主键的关键区别）
默认约束	`default '值'`	插入不赋值，自动填充默认值
外键约束	foreign key	保证参照完整性：从表的值必须在主表存在
检查约束	check	自定义校验规则（MySQL 8.0.16+ 才真正生效，之前版本会静默忽略）

-- auto_increment 是列属性，跟在字段定义后面 create table game ( id int primary key auto_increment, -- primary key 是约束，auto_increment 是列属性 name varchar(20) not null ); -- 几个需要注意的点： -- 1. 一张表只能有一个 auto_increment 列，且该列必须有索引（通常就是主键） -- 2. delete 清空数据后，自增值不会回退（这是 delete vs truncate 的核心区别之一） -- 3. 插入时传 null 或 0 会触发自增

示例建表：

create table game( id int primary key auto_increment, -- 主键+自增 name varchar(20) not null, -- 非空 phone varchar(11) unique, -- 唯一 skill varchar(30) default '轻功' -- 默认值 );

注意：删除主键alter table game drop primary key;，删除主键后，原字段非空属性仍然保留。

补充：

-- 外键约束完整示例 create table category ( cid int primary key auto_increment, cname varchar(20) not null ); create table product ( pid int primary key auto_increment, pname varchar(50) not null, category_id int, constraint fk_product_category foreign key (category_id) references category(cid) on delete set null -- 分类被删除时，商品的 category_id 置为 null on update cascade -- 分类 id 更新时，商品的 category_id 同步更新 ); -- 外键的几种动作 -- on delete cascade 主表删除，从表一起删 -- on delete set null 主表删除，从表外键置 null（要求该列允许 null） -- on delete restrict 主表有引用时拒绝删除（默认行为） -- on delete no action 同 restrict

上面补充的建表脚本里我加了外键，是为了能直观看到约束效果。但实际项目里很多团队不用物理外键，原因是：
分布式/分库分表场景下外键无法跨库生效
外键会带来额外的锁与性能开销
数据迁移、批量导入时外键检查很麻烦
常见做法是在应用层用代码保证关联关系。面试问到就说清楚这两面，比只说"用"或"不用"都强。

四、DQL 查询语句（重点，开发使用最多）

DQL：select查询，不会修改原始数据。执行顺序：from → where → group by → having → select → order by → limit

4.1 基础查询

select * from product; -- 查询全部列，生产环境尽量不要用* select pid,pname from product; -- 查询指定列 select distinct category_id from product; -- distinct去重 select pname, price+20 as new_price from product; -- 运算 + 别名as

4.2 where 条件查询

比较运算符：> < >= <= = != <>（不等于）
逻辑运算符：and 并且、or 或者、not 取反
范围
- in(值1,值2)：离散多个值
- between A and B：连续闭区间 [A,B]
模糊查询 like：%匹配任意 0~ 多个字符；_匹配1 个任意字符
null 判断：必须用 is null /is not null，不能使用 = null

select * from product where price in (200,800); select * from product where price between 200 and 1000; select * from product where pname like '%香%'; -- 包含香字 select * from product where category_id is null; -- 判断空

4.3 order by 排序

-- asc升序（默认）；desc降序；多字段排序，前面字段相同才走后面 select * from product order by category_id asc, price desc;

4.4 聚合函数（统计列，忽略 null 值）

count()统计行数、sum()求和、max()最大值、min()最小值、avg()平均值

select count(*) from product; -- 统计全部行数 select count(1) from product; -- 同上 select count(category_id) from product; -- 统计该字段非 null 的行数 select count(distinct category_id) from product; -- 去重后计数

三种写法的区别:

写法	行为	注意
`count(*)`	统计所有行，包含 null	InnoDB 在 MySQL 8.0 后对此有专门优化，通常最快，推荐
`count(1)`	统计所有行，同样包含 null	与`count(*)`性能基本一致，早期有"count(1) 更快"的说法，现在已不成立
`count(字段)`	只统计该字段非 null 的行	字段有 null 时结果偏小，这是逻辑差异不是性能差异

count(*)和count(1)是性能问题（差别可忽略），count(*)和count(字段)是逻辑问题（结果可能不一样）。

注意：聚合函数直接 select 后面不能跟普通非聚合字段（MySQL 严格模式报错）。

4.5 group by 分组 + having 过滤分组结果

where：分组之前过滤原始行，不能写聚合函数
having：分组之后对聚合结果过滤，可以写聚合函数

-- 查询每个分类商品平均价格，只展示平均价格大于500的分类 select category_id,avg(price) avg_price from product where price>100 group by category_id having avg_price>500;

4.6 limit 分页

select * from product limit 0,5; -- 从索引 0 开始，取 5 条 select * from product limit 5 offset 0; -- 等价写法，推荐这种，语义更清晰

深分页陷阱（面试与实战都会遇到）

-- 这条语句看起来没问题，但 offset 很大时会非常慢 select * from product limit 1000000, 20;

为什么慢？因为 MySQL 必须先扫描并丢弃前面 1000000 行，才能返回后面的 20 行。

优化方案：用"上一页最大 id"代替 offset

-- 第一页 select * from product order by pid limit 20; -- 后续页：假设上一页最后一条 pid = 100 select * from product where pid > 100 order by pid limit 20;

这样每一页都走主键索引，性能不受页码影响。缺点是不能随意跳页（只能"上一页/下一页"），但对于信息流、订单列表这类场景完全够用。

五、表关系 & 多表查询

5.1 三种表关系

一对多（最常用）：在多的一方添加外键指向主表主键。例：分类表 (1) — 商品表 (多)。
一对一：方案 1，外键增加 unique；方案 2，两张表主键直接关联。例：用户表 — 用户详情表。
多对多：新建中间表，中间存放两张主表外键。例：学生 <-> 课程。

外键foreign key：保证参照完整性，实际项目开发很多业务不使用物理外键，在代码逻辑层面做关联。

5.2 多表连接

笛卡尔积：两张表直接关联没有条件，产生全部组合结果，要避免。

表格

连接类型	语法	说明
隐式内连接	`select * from A,B where A.id=B.aid`	只查询两边匹配到的数据
显式内连接	`A inner join B on 关联条件`	推荐，可读性高
左外连接	`A left join B on 条件`	左表全部保留，右表匹配不到显示 null
右外连接	`A right join B on 条件`	右表全部保留；可以改写为左连接
自连接	`A join A b on a.id=b.pid`	一张表当成两张表，例省市区层级数据

MySQL 没有full join全外连接，使用left join union right join实现全连接；union自动去重；union all直接拼接不去重，性能更高。

示例：显式左连接

select c.cname,p.pname from category c left join product p on c.cid = p.category_id;

5.3 子查询

子查询：select 里面嵌套 select，分三种场景：

标量子查询（返回 1 行 1 列）：where 字段 = (子查询)
列子查询（返回一列多行）：where 字段 in (子查询)
表子查询（返回多行多列）：from 后面当做临时表使用，必须给临时表别名。

-- 查询分类名称为服装的所有商品 select * from product where category_id = (select cid from category where cname='服装');

六、MySQL 常用函数

6.1 数值函数

round(3.1415,2); --四舍五入 ceil(3.01);--向上取整 floor(3.99);--向下取整 rand();--0~1随机数 mod(7,3); --求余数 pow(2,3);--幂运算

6.2 字符串函数

concat('a','b'); --拼接 concat_ws('_','a','b');--带分隔符拼接 replace('abc','b','M');--替换 substr('hello',2,2);--截取 upper() lower() --大小写转换 length() char_length() --字节长度 /字符个数

6.3 日期时间函数

now(); -- 当前时间 datetime curdate(); -- 当前日期 date_add(now(),interval 2 day); --时间加2天 date_sub(now(),interval 1 year); --时间减1年 datediff('2026‑01‑01','2025‑01‑01'); --天数差 date_format(now(),'%Y‑%m‑%d %H:%i:%s'); --格式化输出 str_to_date('2026‑05‑01','%Y‑%m‑%d'); --字符串转日期

6.4 流程控制函数

IF(条件,true返回,false返回)

select name,if(score>=60,'及格','不及格') from student;

IFNULL(字段,默认值)如果为 null 返回默认值

select name,ifnull(salary,0) from employee;

case when多条件分支（非常常用，行转列）

select *, case when score >=90 then '优秀' when score >=80 then '良好' else '不及格' end as level from student;

七、MySQL8.0 窗口函数（面试高频）

窗口函数：over()，不改变行数，新增计算列；MySQL8.0 才支持。

7.1 三大排名函数

row_number()：1,2,3,4，相同值排名不重复
rank()：1,2,2,4，相同值排名相同，跳过后续序号
dense_rank()：1,2,2,3，相同值排名相同，不跳号

select ename,salary, row_number() over(partition by dname order by salary desc) rn1, rank() over(partition by dname order by salary desc) rn2, dense_rank() over(partition by dname order by salary desc) rn3 from employee;

partition by：窗口分组；order by窗口内排序。

7.2 其他窗口函数

聚合窗口函数：sum / avg / max / min / count over()，组内累计统计。
lag(字段,n,默认值)获取当前行向上第 n 行数据
lead(字段,n,默认值)获取当前行向下第 n 行数据
first_value / last_value获取窗口内第一个 / 最后一个值
ntile(n)将窗口数据切分成 n 份，打分组标记。

自定义窗口行数范围：rows between ... and ...

sum(salary) over(partition by dname order by salary rows between unbounded preceding and current row)

7.3 CTE 公用表表达式（with）

简化复杂子查询，定义临时结果集

with t1 as (select * from employee where salary>8000) select * from t1;

八、高频踩坑 & 报错清单

❌update / delete忘记写where条件，全表修改删除：生产环境执行前先 select 校验条件！
❌判断 null 使用=null，必须用is null。
❌group by非分组字段直接 select（严格模式报错）。
❌子查询做临时表，不给临时表别名直接运行报错。
❌truncate不能回滚；delete 可以回滚。
❌窗口函数运行报语法错误：检查 MySQL 版本，5.7 不支持窗口函数。
❌distinct只写一个字段，后面多字段，去重是全部字段组合。

以上总结

DDL 操作表结构；DML 操作数据增删改；DQL 负责查询，是业务开发使用最多的部分。
五大约束保障数据质量；delete和truncate是必背面试题。
多表查询分清内连接、左连接，优先显式 join 写法，可读性高；MySQL 无 full join，用 union 实现。
熟练掌握常用内置函数：日期、字符串、case when、ifnull。
MySQL8.0 窗口函数：row_number/rank/dense_rank三者区分，掌握over(partition by order by)；适合分组排名、累计统计。

💡学习建议：不要死记语法，复制 SQL 本地执行看效果；写完 update/delete 先跑 select 验证 where 条件，养成习惯；重点掌握：连接查询、子查询、窗口函数排名。

补充：MySQL 另外三块核心内容：数据类型选型、索引与执行计划、事务与隔离级别。

九、数据类型选型（建表的第一步）

9.1 数值类型

类型	字节	范围/说明
tinyint	1	-128~127，适合状态字段（0/1/2）
int	4	常用主键
bigint	8	大表主键、雪花 ID
decimal(M,D)	变长	精确小数，金额最好用这个
float / double	4/8	近似值

为什么金额不能用 float？
`select 0.1 + 0.2;` 结果是 `0.30000000000000004`。浮点数在二进制下无法精确表示十进制小数，累计计算后误差会放大。金融场景一律用 `decimal(10,2)`。

9.2 字符串类型

类型	特点	适用场景
char(n)	定长，不足补空格	长度固定的值：手机号、身份证、MD5
varchar(n)	变长，额外 1-2 字节存长度	长度不固定：姓名、标题、地址
text	大文本，不能有默认值	文章内容、JSON 原文

varchar(50) 和 varchar(500) 有区别吗？
存储空间上没区别（都是按实际长度存），但内存占用上有区别——MySQL 排序、建临时表时按定义的最大长度分配内存。所以不要无脑写 varchar(500)。

9.3 时间类型

类型	字节	范围	说明
date	3	1000-01-01 ~ 9999-12-31	只要日期
datetime	8	1000-01-01 ~ 9999-12-31	不受时区影响
timestamp	4	1970-01-01 ~ 2038-01-19	随时区转换，有 2038 问题

推荐用 `datetime`。`timestamp` 的 2038 年溢出问题虽然还早，但它的自动更新特性经常带来意外行为。

十、索引（mysql面试第一考点）

10.1 为什么是 B+Tree

- B+Tree 的非叶子节点只存索引不存数据，所以一个节点能放更多索引项，树的高度更低（通常 3 层就能存千万级数据），磁盘 IO 次数更少
- 叶子节点之间用双向链表连接，范围查询非常快

10.2 最左前缀原则

-- 有联合索引 idx_name_age (name, age) where name = 'tom' -- ✅ 用到索引 where name = 'tom' and age = 20 -- ✅ 用到完整索引 where age = 20 -- ❌ 用不到（跳过了最左列） where name like '%om' -- ❌ 用不到（前缀是通配符） where name like 'to%' -- ✅ 可以用到

10.3覆盖索引

-- 如果索引是 idx_name_age (name, age) select age from user where name = 'tom'; -- ✅ 覆盖索引：要查的 age 已经在索引里，不需要回表查主键

10.4 索引失效的常见场景

对索引列做函数运算：where year(create_time) = 2026❌
隐式类型转换：where phone = 13800138000（phone 是 varchar）❌
or连接的条件中有一个没索引 ❌
违背最左前缀 ❌
MySQL 判断全表扫描更快时（比如小表）会放弃索引

十一、事务与隔离级别

ACID

A 原子性：要么全成功，要么全回滚（靠 undo log）
C 一致性：事务前后数据都满足约束
I 隔离性：并发事务之间互不干扰（靠锁 + MVCC）
D 持久性：提交后即使宕机也不丢（靠 redo log）

四种隔离级别与三个问题

隔离级别	脏读	不可重复读	幻读
读未提交 Read Uncommitted	❌ 会	❌ 会	❌ 会
读已提交 Read Committed	✅ 不会	❌ 会	❌ 会
可重复读 Repeatable Read（MySQL 默认）	✅	✅	✅ 基本解决（靠 MVCC + 间隙锁）
串行化 Serializable	✅	✅	✅

三个问题是什么

脏读：读到了别人未提交的数据，对方回滚了你就读到了不存在的东西
不可重复读：同一事务内两次读同一行，值不一样（别人改了并提交了）
幻读：同一事务内两次范围查询，行数不一样（别人插入了新行）

-- 查看与设置隔离级别 select @@transaction_isolation; -- MySQL 8.0（5.7 用 @@tx_isolation） set session transaction isolation level read committed; -- 事务基本操作 start transaction; update user set age = age - 1 where id = 1; savepoint sp1; -- 设置保存点 rollback to sp1; -- 回滚到保存点（不是回滚整个事务） commit;