超全 MySQL 学习笔记（3 万字）：操作、约束、范式、连接查询全解析

Ne0inhk

15 Mar 2026 — 51 min read

文章目录

一、数据库操作
二、 MySQL 常用字段类型速查笔记（数值 / 字符串 / 日期时间）
三、数据库约束
四、CRUD操作
五、数据库表设计流程
六、表设计规范 —— 三大范式
七、 MySQL 联合查询执行原理：多表笛卡尔积
八、MySQL 聚合函数与分组查询
- 1、插入查询结果
  - 推荐使用第三种方法
- 2、聚合函数
九、联合查询（表连接查询）

一、数据库操作

1.1 登录 MySQL 数据库

在命令行中输入以下命令，使用 root 用户登录 MySQL：

mysql -uroot -p

回车后输入自己的密码即可登录。

1.2 查看当前服务器中的所有数据库

showdatabases;

执行后会显示当前 MySQL 服务器中存在的所有数据库。

查询结果中显示的 11 rows in set 表示：一共查询到了 11 条记录set 表示结果集

1.3 创建数据库

在创建数据库时，建议先判断数据库是否已存在，避免重复创建导致报错。

1.3.1 创建数据库的标准语法

createdatabaseifnotexists mydb characterset utf8mb4 collate utf8mb4_0900_ai_ci;

说明：

if not exists：如果数据库不存在才创建
character set utf8mb4：指定字符集
collate utf8mb4_0900_ai_ci：排序规则（MySQL 8.0 及以上支持）

执行成功后，即可看到数据库 mydb 创建完成。

示例：

⚠️ 注意：数据库名是否可以使用关键字？

例如数据库名叫 create 或 database：

可以创建
但不推荐

如果必须使用关键字作为数据库名，需要使用 反引号（`） 包裹：

createdatabase`database`;

示例：

实际开发中，数据库名应尽量做到：语义清晰全小写不使用关键字

1.3.2 utf8 与 utf8mb4 的区别（高频面试题）

字符集	最大字节	是否支持 emoji
utf8	3 字节	❌ 不支持
utf8mb4	4 字节	✅ 支持

结论：

MySQL 官方推荐使用 utf8mb4，新项目应统一使用该字符集。

1.4 查看 MySQL 服务的默认字符集

show variables like'%character%';

该命令用于查看 MySQL 当前服务的 全局字符编码配置。
示例：

1.5 查看当前正在使用的数据库

selectdatabase();

使用 use 数据库名; 后，再次查看即可看到当前数据库

如果返回 NULL，说明当前没有选中任何数据库

当前没有正在操作的数据库。

use mydb;

1.6 删除数据库（慎用）

dropdatabaseifexists 数据库名;

⚠️ 重要提醒：

删除数据库是 不可逆操作
实际工作中，严禁随意删除数据库

1.7 查看 MySQL 帮助

\?

该命令可查看 MySQL 内置帮助信息，适合新手快速了解可用命令。

1.8 创建数据表

1.8.1 创建数据表的基本语法

createtableifnotexists table_name ( field1 datatype,-- 字段名 数据类型 field2 datatype, field3 datatype -- 最后一个字段后不加逗号);

字段定义在小括号中，语句以分号结尾。

1.8.2 示例：创建 student 表

createtable student ( id int, name varchar(50));

说明：

id：学生编号
name：学生姓名，长度为 50

⚠️ 注意：

使用 varchar 时，必须指定长度

1.9 查看表结构

desc 表名;

字段说明：

Field：字段名
Type：数据类型
Null：是否允许为空
Key：键类型（主键、索引等）
Default：默认值
Extra：扩展信息

1.10 删除表（慎用）

droptableifexists 表名;

1.10.1 查看当前数据库中的所有表

showtables;

2、数据库操作注意事项（必看）

只操作 自己创建的数据库，其他数据库不要随意修改
SQL 写错时，可使用 Ctrl + C 取消当前输入
删除数据库是高危操作，生产环境严禁执行
删除表后通常难以及时发现问题，务必谨慎

二、 MySQL 常用字段类型速查笔记（数值 / 字符串 / 日期时间）

2.1 MySQL 常用数值类型

数值类型主要分为 整数类型 和 小数类型。

1️⃣ 数值类型总结表

类型	字节数	有符号范围（SIGNED）	无符号范围（UNSIGNED）	说明 / 使用场景
TINYINT	1	-128 ~ 127	0 ~ 255	状态值、布尔值（0/1）
SMALLINT	2	-32768 ~ 32767	0 ~ 65535	小范围计数
MEDIUMINT	3	-8388608 ~ 8388607	0 ~ 16777215	不太常用
INT / INTEGER	4	-2³¹ ~ 2³¹-1	0 ~ 2³²-1	最常用整数类型
BIGINT	8	-2⁶³ ~ 2⁶³-1	0 ~ 2⁶⁴-1	ID、订单号、大数值
FLOAT	4	约 ±1.175e-38 ~ ±3.402e38	—	单精度浮点，有精度误差
DOUBLE	8	约 ±2.225e-308 ~ ±1.797e308	—	双精度浮点
DECIMAL(M,D)	可变	精确数值	精确数值	金额、精确计算（推荐）

2️⃣ 使用建议（记住这几条就够了）

整数首选 INT，大 ID 用 BIGINT
金额、精确计算必须用 DECIMAL
不要用 FLOAT / DOUBLE 存钱（精度不可靠）
能用 UNSIGNED 就用，范围更大

2.2 MySQL 常用字符串类型

字符串类型是建表中 最容易纠结、也最容易踩坑 的部分。

1️⃣ 字符串类型总结表

类型	是否定长	最大长度	存储方式	典型使用场景
CHAR(n)	是	0–255	固定长度	身份证号、手机号、状态码
VARCHAR(n)	否	0–65535*	变长	用户名、标题、描述
TINYTEXT	否	255	变长	短文本
TEXT	否	65,535	变长	正文、备注
MEDIUMTEXT	否	16,777,215	变长	较大文本
LONGTEXT	否	4,294,967,295	变长	超大文本
ENUM	否	65535 个元素	枚举存储	状态、类型
SET	否	64 个元素	位存储	多选标签

VARCHAR 实际可存长度受 字符集 和 行大小（65KB）限制。

2️⃣ CHAR vs VARCHAR（面试必考）

CHAR：
- 固定长度
- 查询略快
- 适合长度固定的字段
VARCHAR：
- 变长
- 更省空间
- 实际开发中使用最多

👉 口诀：长度固定用 CHAR，不固定用 VARCHAR

3️⃣ 实战经验

手机号、身份证 用字符串，不用数字
ENUM / SET 约束强但扩展性差
实际项目中更推荐：TINYINT + 字典表

2.3 MySQL 常用日期时间类型

时间字段几乎每张表都会用到，是高频面试点。

1️⃣ 日期时间类型总结表

类型	字节数	格式	范围	是否含时区	常见使用场景
DATE	3	YYYY-MM-DD	1000-01-01 ~ 9999-12-31	否	生日、日期
TIME	3	HH:MM:SS	-838:59:59 ~ 838:59:59	否	时长、用时
DATETIME	8	YYYY-MM-DD HH:MM:SS	1000-01-01 ~ 9999-12-31	否	最常用时间
TIMESTAMP	4	YYYY-MM-DD HH:MM:SS	1970-01-01 ~ 2038-01-19	✅	创建时间、更新时间
YEAR	1	YYYY	1901 ~ 2155	否	年份

2️⃣ DATETIME vs TIMESTAMP

DATETIME
- 不受时区影响
- 时间范围大
- 更稳定，推荐使用
TIMESTAMP
- 自动进行时区转换
- 占用空间小
- 有 2038 年问题

👉 实际建议：优先 DATETIME，明确需要时区再用 TIMESTAMP

2.4快速选型口诀（复习专用）

整数：INT / BIGINT
金额：DECIMAL
字符串：VARCHAR
大文本：TEXT
时间：DATETIME
状态值：TINYINT

三、数据库约束

是关系型数据库的一个重要功能，约束一般是指定在列上的。

3.1 常见约束类型

约束类型	作用
NOT NULL	字段不能为空
UNIQUE	字段值不能重复
PRIMARY KEY	唯一 + 非空
FOREIGN KEY	建立表与表之间的关联
DEFAULT	设置默认值

约束的作用：

保证数据的准确性和完整性。

3.2 NOT NULL

NOT NULL 表示某列不能存储NULL值
NOT NULL的作用：

如果要把一个列定义为必填项，就可以使用NOT NULL非空约束

示例：

在创建表时，可以在相应字段加入约束类型
示例：

NO 表示当前列不能为空
YES 表示当前列可以为空

写入数据时会报错，提示不能写入NULL值，这是因为数据库做了校验

3.3 UNIQUE

保证某列的每行必须有唯一值，也就是说某列的值在整个表中不能重复

示例：

所以需要加入unique约束去创建表
示例：

示例：

⚠️ 注意：NULL值可以重复插入

3.4 DEFAULT

规定没有给列赋值时的默认值
在插入时，只指定了id，没有指定name，此时name用默认值填充

示例：

⚠️ 注意：
虽然指定了默认约束，但是当我们手动将这一列的值设置为NULL时，插入的值依然是NULL，因为此时的这个NULL值是我们手动指定的，也可以理解为我们想要的值。用户指定的优先级高于默认约束

示例：

3.5 主键（PRIMARY KEY）

NOT NULL 和 UNIQUE的结合，有助于更容易快速的找到表中特定的记录。
特点：

唯一
不能为空
一张表只能有一个主键

示例：

createtable student ( id int, name varchar(50),primarykey(id));

一个表中不允许有两个主键

这里id和name复合主键中的列值都相同，所以被判定为相同，第二条数据插入失败

示例：

⚠️ 注意：一个主键可以同时包含多个列（复合主键）
示例：

主键值自增1
在最大值的基础上加1，所以主键值在数据表中有可能是不连续的

可以指定主键
可以指定主键，只要主键不重复即可
示例：

指定列插入
当指定列插入，也会自动生成id，作为数据行的主键
示例：

全列插入
在写入数据时，不具体指定主键列的值，而是用NULL代替
示例：

让数据库帮我们去维护主键的增长，在插入的时候，先找到最大值，然后在这个基础上加1，生成一个新的值，作为新一个数据行主键（id列）的值。
示例：

主键两个约束同时生效

⚠️ 注意：
设置主键的核心作用：
唯一标识一条记录 + 提高查询效率 + 维护数据完整性 + 支持表关联
在实际开发中，每张表都应该设计主键（一般使用自增 id）。

id列指定的非空和唯一约束，列被表示为了PRI，表示他是一个主键
示例：

3.6 外键（FOREIGN KEY）

外键用于建立两张表之间的联系。

foreignkey(字段名)references 主表(列)

说明：

foreign key：创建外键的关键字
字段名：表示当前表中哪个字段与主表建立主外键关系
references：关键字，表示后面要引用哪个表中的哪个列
主表(列)：指定主表和相应的列

⚠️ 注意：

外键字段必须与被关联字段类型一致
用于维护数据之间的关联关系

示例：
创建班级表和学生表（但不创建外键）
学生表中有一个字段是班级编号，写入数据时，这个编号必须是有效的

在class表中插入几个班级

在student表中插入了编号5这个班级，但这个班级根本不存在，此时，数据依然能写入成功，是不合理的。

示例：
创建班级表和学生表（但创建外键）

这里不存在编号为5的班级，所以插入数据失败。
⚠️ 注意：
当子表中存在对主表的依赖的时候，能不能删除主表中相应的记录？

依然会报主外键关系的错误。
所以如果想要删除主表中的记录，子表中不能有对该记录的依赖。也就意味着，需要先删除子表中的记录，再去删主表中的记录。

四、CRUD操作

在操作之前，我们需要明确自己操作的是哪部分的数据。

1、新增（create）

1.单行数据

insertinto[(字段1，字段2)]values(值,值);

insertinto student(id,name)values(1,'张三');

执行成功，有一条数据被写入数据表。

2.简写方式：不用在表名后面指定列名，在values列表中按定义字段的顺序设置相应的值。

1.如果列与值的个数不匹配就会报一个错误，这条记录无法写入数据表

insertinto 表名 values(值,值...);

2.指定列插入

insertinto 表名(列名)values(值);

3.多行插入

insertinto 表名[(列名...)]values(值),(值)...

2、查询（Retrieve）

1.全列查询

select*from 表名；

⚠️ 注意：这个一个非常危险的操作，因为在生产环境中，一个表中的数据量可能会很多很多，当一个查询开始的时候，磁盘会开销，网络会开销，这些都是非常紧缺的资源。这一条语句开始执行的时候，可能会吃光服务器所有资源，其他程序或数据库操作就要等待当前SQL语句执行完以后才能继续执行。

2.指定列查询

select 列名[,列名]...from 表名;

指定查询id，姓名，和语文成绩。

列与列之间也可以参与运算，计算总分

还可以利用as关键字给表达式取别名

把所有学生的语文成绩在原来的基础上加上十分

查询的结果是一个表达式

3.去重：DISTINCT

selectdistinct 列名 from 表名；

这里没有加distinct关键字查询出来的成绩有两个98分

加上distinct关键字查询出来的记录如下

重复的记录只保留了一条。
⚠️ 注意：在查询结果中，每一列都相同MYSQL才认为他们是重复的数据，也就是数据行与数据行之间，两条记录完全一致才是重复的数据。
例如：
在查询数学成绩时，加上id这一列，第一列和第三列记录，虽然数学成绩一致，但是他们的id不一致，所以distinct关键字也就不把他们当做重复的数据行。

4.排序：ORDER BY

排序规则

1.升序 ASC(不指定时，默认是升序)
2.降序 DESC

select 列名 from 表名 orderby 列名[ASC|DESC];

4.使用表达式及别名排序

⚠️ 注意：MYSQL中NULL的特殊性

3.NULL数据排序，视为比任何值都笑，升序出现在最上面，降序出现在最下面。

2.对数学成绩进行升序排序

1.按语文成绩从高到低排序（DESC）

不论什么值和NULL进行运算，返回值都是NULL
NULL始终被判定为FALSE
NULL的值不是我们以前学过的其他编程语言中的0，在MYSQL中它就是NULL

5.可以对多个字段进行排序，排序的优先级与书写有关
例如：先按数学降序排序，再按语文升序排序，再按英语进行升序排序

5.条件查询:WHERE

语法：

select*from 表名 where 列名/表达式 运算符 条件;

5.1 运算符说明表

运算符	说明
`>`	大于
`>=`	大于等于
`<`	小于
`<=`	小于等于
`=`	等于，NULL 不安全，例如 `NULL = NULL` 的结果是 NULL
`<=>`	等于，NULL 安全，例如 `NULL <=> NULL` 的结果是 TRUE(1)
`!=`, `<>`	不等于
`BETWEEN a0 AND a1`	范围匹配，`[a0, a1]`，如果 `a0 <= value <= a1`，返回 TRUE(1)
`IN (option, ...)`	如果是 `option` 中的任意一个，返回 TRUE(1)
`IS NULL`	是 NULL
`IS NOT NULL`	不是 NULL
`LIKE`	模糊匹配，`%` 表示任意多个（包括 0 个）任意字符，`_` 表示任意一个字符

5.5逻辑运算符说明表

运算符	说明
`AND`	多个条件必须都为 TRUE(1)，结果才是 TRUE(1)
`OR`	任意一个条件为 TRUE(1)，结果为 TRUE(1)
`NOT`	条件为 TRUE(1)，结果为 FALSE(0)

⚠️ 注意：

WHERE条件可以使用表达式，但不能使用别名。
AND的优先级高于OR，在同时使用时，需要使用小括号()包裹优先执行的部分

例如

9.查询数学成绩是58 或者 59 或者 98 或者 99 分的同学

8.查询语文成绩在[80,90]分的同学的语文成绩

7.观察and和or的优先级
and的优先级高于or

6.查询语文成绩大于80或英语成绩大于80的同学

5.查询语文成绩大于80且英语成绩大于80的同学

4.查询总分在200分一下的同学

.⚠️ 注意：在where子句中不能用别名作为过滤条件，在where条件中使用了表达式，那么就要把表达式完整的写在where子句中，不能使用别名。
出现这种现象和MYSQL执行SQL语句的顺序有关
1.如果要在数据中查某些数据，首先要确定表，先执行from;
2.在查询过程中要根据指定的查询条件把符合条件的数据过滤出来，这时执行的就是where子句
3.执行select后面的指定的列，这些列最终需要加入到结果集
4.排序操作，根据order by子句中指定的列名和排序规则进行排序。

3.查询语文成绩好于英语成绩的同学

.⚠️ 注意：在一行数据中的两个列是可以进行比较的，但不能跨行比较
。

2.查询英语成绩不及格的同学（<60）

1.like模糊匹配
%表示多个任意字符，_表示任意一个字符

查询方法 1.用OR把所有的条件关联起来
用IN（集合）

11.分页查询：LIMIT
作用：限制查询结果集中的条数
分页查询在项目开发中运用的非常多，只要查询的是一个记录的集合（多条记录）都在使用分页查询。

10.NULL的查询：IS[NOT] NULL

--起始下标为0--从0开始，筛选n条结果select....from 表名 [where...][orderby...]limit n;

--从s开始，筛选n条结果select....from 表名 [where...][orderby...]limit s, n;

⚠️ 注意：如果起始位置超过了整个结果集的范围，也是可以执行的，只不过是一个空集

--从s开始，筛选n条结果,比第二种用法更明确，推荐使用--offset偏移量，也就是从哪开始的意思select....from 表名 [where...][orderby...]limit n offset s;

⚠️ 注意：s = (当前页号-1)*每页显示的记录数

3、修改（Update）

update 表名 set 列名1= 值1, 列名2= 值2where 条件;

3.将总成绩倒数前三的同学的数学成绩加上5分

2.将孙权的数学成绩变更为60分，语文成绩变更为73分

1.将孙悟空的数学成绩变更为80分

4、删除（Delete）

deletefrom 表名 [where...][orderby...][limit...]

2.删除英语成绩倒数前三的同学的考试成绩

先对英语成绩进行升序排序，再限制删除的条数。
⚠️ 注意：delete不加where条件是个非常危险的操作，会把表中的所有记录都删除。

1.删除孙悟空的考试成绩

5、知识CRUD总结

1、Create（新增）

使用 INSERT INTO 向数据表中插入数据
支持：
- 单行插入
- 指定列插入
- 多行插入（推荐，效率更高）
⚠️ 插入时必须保证 列数与值的个数一致

2、Retrieve（查询）

查询是数据库中最常用的操作，重点掌握以下内容：

1️⃣ 基础查询

SELECT *（生产环境慎用）
指定列查询（推荐）

2️⃣ 表达式查询

列可以参与运算
可以使用 AS 起别名

3️⃣ DISTINCT 去重

只有整行数据完全一致才会被认为重复

4️⃣ ORDER BY 排序

默认升序 ASC
降序 DESC
支持多字段排序（前面的优先级更高）
NULL 在升序中最小，在降序中最大

5️⃣ WHERE 条件过滤（重点）

支持比较运算符：> >= < <= = !=
支持逻辑运算符：AND、OR、NOT
支持 BETWEEN、IN、LIKE、IS NULL
⚠️ AND 优先级高于 OR
⚠️ WHERE 不能使用别名

🔥 MySQL 执行顺序（高频考点）

FROM → WHERE → SELECT → ORDER BY → LIMIT

理解执行顺序可以帮助我们：

明白为什么 WHERE 不能使用别名
理解排序为什么最后执行
理解分页为什么写在最后

3、 Update（修改）

使用 UPDATE ... SET ... WHERE ...
⚠️ 不加 WHERE 会更新整张表（极度危险）

4、Delete（删除）

使用 DELETE FROM ... WHERE ...
可以结合 ORDER BY + LIMIT 精准删除
⚠️ 不加 WHERE 会删除整张表数据

🚨 高频易错点总结

NULL 不能使用 = 判断，要使用 IS NULL
WHERE 中不能使用 SELECT 定义的别名
AND 优先级高于 OR
分页公式：

s = (当前页号 - 1) * 每页条数

生产环境慎用：
- SELECT *
- 不带 WHERE 的 UPDATE
- 不带 WHERE 的 DELETE

🎯 一句话总结

先确定数据来源（FROM）
再过滤数据（WHERE）
再选择字段（SELECT）
再排序（ORDER BY）
最后限制条数（LIMIT）

掌握这条主线，MySQL 的 CRUD 操作基本就能熟练掌握。

五、数据库表设计流程

OOA（面向对象分析） → OOD（面向对象设计） → OOP（面向对象编程）

5.1 从需求中获取类

从业务需求中分析出类
类对应数据库中的实体
实体在数据库中表现为一张张表
类中的属性对应表中的 字段（列）

5.1.1 对应关系

类 → 实体 → 表
属性 → 字段（列）

5.2 确定类与类之间的关系

在数据库中体现为表与表之间的关系：

一对一（1:1）
一对多（1:N）
多对多（M:N）

5.3 使用 SQL 创建具体的表

通过 SQL 语句（如 CREATE TABLE）实现表结构设计。

六、表设计规范 —— 三大范式

设计表时需要遵守一定规则，这些规则称为：

数据库三大范式（Normal Form）

6.1 什么是范式？

范式是描述 数据关系模型规范程度 的标准。

6.2数据库关系类型

一对一关系
一对多关系
多对多关系

6.3 范式分类

6.3.1 第一范式（1NF）

要求：

字段必须具有原子性（不可再分）

示例：

✅ 正确：

id	name
1	张三

❌ 错误：一个字段内包含了两门成绩

学号	姓名	课程成绩
001	张三	数学80，英语90

6.3.2 第二范式（2NF）

满足第二范式必须：

先满足第一范式（1NF）
所有非主属性必须完全依赖主键
不能存在部分函数依赖

⚠️ 重点：

只有在复合主键情况下才可能违反第二范式
如果主键只有一列（非复合主键），天然满足第二范式

6.3.2.1第二范式（2NF）正反例说明

正例（满足第二范式）：

1️⃣学生表

学号（主键）	姓名	年龄

说明：

主键：学号
姓名、年龄完全依赖学号
不存在部分依赖

2️⃣ 课程表

课程编号（主键）	课程名	学分

说明：

主键：课程编号
课程名、学分完全依赖课程编号

3️⃣ 学生选修成绩表

学号	课程编号	成绩

说明：

复合主键：(学号, 课程编号)
成绩依赖于“学生 + 课程”
必须通过两个字段才能确定成绩

✅ 正例总结

每张表都有主键
所有非主属性都完全依赖主键
不存在只依赖主键一部分的字段

反例（不满足第二范式）：

1️⃣学生选修课成绩表（错误设计）

学号	学生姓名	年龄	课程名	学分	成绩

说明：

假设复合主键：(学号, 课程名)
学生的姓名，年龄和和课程名没有关系，即学生的姓名只依赖于学号，不依赖于课程
学分于学生没有关系，即学分只依赖于课程，不依赖于学号
对于两个或多个关键字共同决定一条记录的情况，如果一行中的有些字段只与关键字段中的一个有关系，这种情况就称为部分依赖，不满足第二范式。

6.3.2.2不满足第二范式（2NF）可能出现的问题

6.3.2.2.1 数据冗余

表现（针对上面不满足2NF设计的表）

学生姓名、年龄重复出现
课程学分重复出现

例如：

如果有 100 个学生选修 MYSQL
那么 MYSQL 的学分会重复存 100 次。

后果

占用大量存储空间
数据维护成本高
容易出现不一致

6.3.2.2…2 更新异常（Update Anomaly）

场景
MYSQL 课程学分从 50 调整为 60
问题
必须更新所有关于 MYSQL 的记录。

如果：有些记录更新成功，有些记录更新失败
就会出现：同一门课程出现不同学分。

导致

数据不一致。

6.3.2.2.3插入异常（Insert Anomaly）

场景
学校新开一门课程：但还没有学生选修。

课程名：Python
学分：40

问题

由于成绩表是围绕“学生 + 课程”建立的：

没有学生，就无法插入课程信息
必须虚构一个学生成绩才能插入课程数据

导致：

课程信息无法独立存在

6.3.2.2.4 删除异常（Delete Anomaly）

场景
某门课程最后一个学生退选，删除该学生成绩记录时：

同时把课程学分信息删除了

导致：

课程信息丢失

6.3.2.2.5核心问题总结

第二范式解决的是“部分依赖问题”，
如果存在部分依赖，就会产生数据冗余、更新异常、插入异常和删除异常。

6.3 第三范式（3NF）

第三范式（3NF）解决的是：

非主属性对主键的 传递依赖问题

要达到第三范式，必须：

满足第一范式（字段不可再分）
满足第二范式（无部分依赖）
不存在传递依赖

6.3.1什么是传递依赖？

如果存在：

主键 → A
A → B

那么就会形成：

主键 → B（通过 A 传递）

这就叫 传递依赖

6.3.2第三范式（3NF）正反例说明

反例（不满足第三范式）：
错误设计：学生表

学号	姓名	年龄	所在学院	学院地址	学院电话

说明：

主键：学号
学号 → 姓名、年龄（正常)
学号 → 所在学院（正常）
所在学院 → 学院地址、学院电话（问题在这里）,这里形成了依赖链，学号 → 所在学院 → 学院地址、学院电话，也就是说：学院地址、学院电话不是直接依赖学号，而是依赖“所在学院”，这就是传递依赖，不满足第三范式。
一个表中混合了两个实体的信息：学生，学院，这会导致：学院信息重复存储，修改学院电话要修改很多行，删除最后一个学生会把学院信息删掉。

正例（满足第三范式）：
正确设计：学生表

第一步：拆分实体
学院表

学院编号（主键）	学院名	学院电话	学院地址

学生表

学号（主键）	姓名	年龄	学院编号（外键）

主键：学号
外键：学院编号

现在依赖关系变成：

学生表：
学号 → 姓名、年龄、学院编号

学院表：
学院编号 → 学院名、学院电话、学院地址

不存在：

主键 → A → B

因此满足 3NF

6.3.2.1判断是否违反 3NF：

表中是否包含两个实体？
是否存在：主键 → A → B？

只要有“中间传递”，就是不满足第三范式。

6.4 一对一关系设计

场景：登录系统

登录时使用的是 账号（用户名 + 密码）
登录成功后展示的是 用户信息（姓名，班级等）

从业务上分析，可以抽象出两个实体：

用户（User）
账号（Account）

6.4.1 实体分析

1️⃣ 用户（User）
记录个人信息：

姓名
年龄
手机号
QQ
邮箱
班级等

2️⃣ 账号（Account）
记录登录信息：

用户名
密码

6.4.2 两个实体之间的关系

一个用户只能有一个账号
一个账号只能属于一个用户

这是典型的：
一对一关系（1 : 1）

6.4.3一对一关系如何设计表？

一对一关系在数据库中通常有两种实现方式。

6.4.3.1 方式一：合并为一张表

把用户信息和账号信息放在同一张表中。

user( user_id, name, age, phone_num, mail, username, password );

特点：

所有信息集中在一张表
适用于用户和账号强绑定、不会分离的场景
查询方便

缺点：

表字段变多
账号信息与用户信息耦合严重
后期扩展不灵活

6.4.3.2 方式二：拆成两张表（推荐 ⭐）

分别建立：

用户表

user( user_id, name, age, phone_num, mail );

账号表

account( account_id, username, password, user_id );

通过 user_id 建立关联。

6.5 一对多关系设计（1 : N）

6.5.1 什么是一对多？

举例：

学生和班级

一个班级可以有多个学生
一个学生只能属于一个班级

站在班级角度：1
站在学生角度：N

所以是：一对多关系（1 : N）

6.5.2 一对多如何设计表？

设计原则：

外键放在“多”的一方

6.5.2.1 错误设计 ❌

class( class_id, class_name, student_ids -- 不要这样设计,关系型数据库里面没有集合类型);

1.关系型数据库没有“集合类型”
2.student_ids 会变成一个可分字段，违反第一范式（1NF）

6.5.2.2 正确设计 ✅

拆成两张表：

1️⃣ 班级表（1的一方）

class( class_id PRIMARYKEY, class_name );

2️⃣学生表（多的一方）

student( student_id PRIMARYKEY, name, age, class_id -- 外键);

可以通过class_id表示学生在哪个班级，上面student表中二班有2个学生，三班1个，四班1个学生。

6.6 多对多关系设计（M : N）

6.6.1 什么是多对多？

举例：学生和课程

一个学生可以选修多门课程
一门课程可以被多个学生选修

所以是：

学生 ⇄ 课程
多对多关系（M : N）

6.6.2 多对多不能直接建外键

错误理解 ❌：

在 student 表里加多个 course_id
在 course 表里加多个 student_id

原因：

关系型数据库没有“集合类型”
会违反第一范式（字段不可再分）

6.6.3 正确做法：建立“中间表”

设计步骤：

第一步：分别创建两个实体表

1️⃣ 学生表

student( student_id PRIMARYKEY, name, age );

student_id	name
1	张三
2	李四

2️⃣ 课程表

course( course_id PRIMARYKEY, course_name );

course_id	course_name
1	MYSQL
2	JAVA

第二步：创建关系表（中间表）

student_course( id PRIMARYKEY, student_id, course_id );

3️⃣ 选课关系表

student_id	course_id
1	1
1	2
2	1

表示：

张三选了 MYSQL 和 JAVA
李四选了 MYSQL

6.6.4设计实例

1️⃣班级表（班级编号，班级名）

createtable class( class_id bigintprimarykeyauto_increment, name varchar(50)notnull);

2️⃣学生表（学生编号，学号，姓名，年龄，邮件，班级编号）

createtable student( student_id bigintprimarykeyauto_increment, sn varchar(6)unique, name varchar(50)notnull, age int, mail varchar(50), class_id bigint,foreignkey(class_id)references class(class_id));

3️⃣课程表（课程编号，课程名）

createtable course( course_id bigintprimarykeyauto_increment, name varchar(50)notnull);

4️⃣成绩表（编号，学生编号，课程编号，成绩）

createtable score( score_id bigintprimarykeyauto_increment, student_id bigint, course_id bigint, score decimal(5,2),foreignkey(student_id)references student(student_id),foreignkey(course_id)references course(course_id));

班级表与学生表之间是一对多的关系，一个班级对应多个学生
学生表与课程表之间是多对多的关系(一个学生可以选择多门课程，一门课程可以被多门学生选择)，通过成绩表进行关联。

6.6.4.1 多对多的核心总结

设计口诀：

多对多
必须加中间表

6.6.5三种关系对比总结

关系类型	设计方式
1 : 1	外键 + 唯一约束
1 : N	外键放在多的一方
M : N	建立中间表

七、 MySQL 联合查询执行原理：多表笛卡尔积

在 MySQL 执行多表联合查询时，如果没有指定连接条件（如 JOIN...ON 或 WHERE），系统会执行最基础的笛卡尔积运算。

7.1基础数据准备

学生表 (Table A)

id (编号)	name (姓名)	gender (性别)
1	张三	男
2	李四	女
3	王五	男
4	赵六	男

班级表 (Table B)

id (编号)	name (班级)
1	1班
2	2班

7.2 笛卡尔积的执行过程

对多张表进行笛卡尔积时，MySQL 会遵循以下逻辑进行组合：

取第一条记录：先从第一张表（学生表）中取出第一条记录（张三）。
全量组合：将这条记录分别与第二张表（班级表）中的每一条记录进行组合，生成新的记录。

张三 + 1班 生成第1条结果
张三 + 2班 生成第2条结果

循环往复：接着取出学生表的第二条记录（李四），再次与班级表的所有记录组合。
最终结果：直到遍历完第一张表的所有行，得到一个全排列结果集。

7.3 联合查询结果集 (全排列)

最终生成的查询结果如下表所示：

id	姓名	性别	班级.id	班级名
1	张三	男	1	1班
1	张三	男	2	2班
2	李四	女	1	1班
2	李四	女	2	2班
3	王五	男	1	1班
3	王五	男	2	2班
4	赵六	男	1	1班
4	赵六	男	2	2班

八、MySQL 聚合函数与分组查询

1、插入查询结果

现在有两张表，想把stu中的数据复制到stu1里面

有以下三种方法

1、一条一条数据重新插入一遍
2、把原来的数据导出来，再把表名改一下，再改入到目标表中
3、可以使用insert into select 语句

2、聚合函数

MYSQL中内置的函数

2.1常用聚合函数（含示例）

聚合函数这些操作都是针对某一列进行运算的，表达式查询是对一行记录中的列与列之间进行运算的

函数	作用说明	示例
COUNT([DISTINCT] expr)	返回查询到的数据数量（统计行数）	`SELECT COUNT(*) FROM student;`
SUM([DISTINCT] expr)	返回某列数据的总和（仅适用于数值类型）	`SELECT SUM(score) FROM student;`
AVG([DISTINCT] expr)	返回某列数据的平均值（仅适用于数值类型）	`SELECT AVG(score) FROM student;`
MAX([DISTINCT] expr)	返回某列数据的最大值	`SELECT MAX(score) FROM student;`
MIN([DISTINCT] expr)	返回某列数据的最小值	`SELECT MIN(score) FROM student;`

2.1.1 count()统计所有的行

--count()selectcount()from 表名;

示例：

使用count()统计指定列，null值不参与统计

*统计表中的行数

2.1.2 SUM(列名) 求和

把查询结果中的所有行中的指定列进行相加
示例：

在sum()求和时，null值不参与运算

计算所有学生的语文成绩的总分

2.1.3 AVG() 求平均值

示例：

求语文，数学，英语三门课的总分的平均值（参数可以是表达式），也可以使用别名

对所有同学的数学成绩求平均值

2.1.4 MAX(),MIN()

求所有行中指定列的最大值，最小值
示例：

找出语文成绩的最高分和英语成绩的最低分（多个聚合函数可以同时使用），可以使用别名

2.1.5 GROUP BY子句

在 SELECT 查询中使用 GROUP BY 可以把数据按指定列分组。分组后，SELECT 里直接列出的字段必须是用来分组的列；如果想显示其他列的值，就需要用聚合函数（比如 SUM、COUNT、AVG 等）来处理。

SELECT column1,SUM(column2),...FROM table_name GROUPBY column1, column3;

说明：

column1, column3：要分组的列（可以分多列）
SUM(column2)：没有被分组的列，如果想在结果里显示，就必须用聚合函数处理（如 SUM、COUNT、AVG 等）
GROUP BY：分组查询的关键字

示例：

group by 后面可以放order by,对分组结果进行排序

round(数值,小数点位数)

计算不同角色的工资平均值
mysql内部先分组再计算

2.2 HAVING子句

group by子句进行分组以后，需要对分组之后的结果进行过滤,不能使用where语句，要使用having子句
⚠️ 注意：where 和having的区别总结

特点	WHERE	HAVING
作用对象	原始数据行	分组后的聚合结果
使用位置	GROUP BY 前	GROUP BY 后
函数支持	不支持聚合函数	支持聚合函数（如 COUNT, SUM, AVG）

示例：

显示平均工资低于1500的角色和它的平均工资

显示每个角色的最高工资，最低工资，平均工资

对角色分组后的工资进行筛选

九、联合查询（表连接查询）

设计数据时把表进行拆分，为了消除表中字段的依赖关系，比如部分函数依赖，传递依赖。这时会导致一条SQL语句查询出来的数据，对业务来说是不完整的，我们就可以使用联合查询把关系中的数据全部查出来，在一个数据行中显示详细信息。

9.1内连接

内连接查询步骤1.首先确定哪几张表要参与查询2.对目标表取笛卡尔积3.根据表与表之间的主外键关系，4.确定对整个结果集的过滤条件5.精减查询字段，得到想要的结果

笛卡尔积语法（基础形式）

select*from 表名,表名；

1、取笛卡尔积

数据准备

示例：

⚠️ 注意：
笛卡尔积会产生无效组合数据，需要后续通过连接条件过滤

2、通过连接条件过滤无效数据

两个表中有主外键关系，只需要判断两张表中的主外键字段是否相等即可。

3、能通过指定列查询，来精减结果集

可以通过表名. 列名的方式指定要查询的字段

通过取别名的方式来简化SQL语句

给student表取别名为s,给class表取别名为c。

联合查询例子

示例1：

1、查询许仙同学的成绩

1、首先要确定哪几张表参与查询

成绩表
学生

5、精减查询字段，得到想要的结果
学生名，分数

4、确定结果集的过滤结果条件
在where条件中增加student.name = '许仙’的过滤条件

3.根据表与表之间的主外键关系，确定过滤条件
两张表中通过student_id作为主外键关联字段

2、取两张表的笛卡尔积

示例2：

2. 查询所有同学的总成绩及同学的个人信息

1、首先要确定哪几张表参与查询

成绩表
学生表

5、精减查询字段，得到想要的结果

9.2外连接

4、确定结果集的过滤结果条件
按学生的id进行分组，并在查询列表中，使用聚合函数sum(分数),计算总分

3.根据表与表之间的主外键关系，确定过滤条件
两张表中通过student_id作为主外键关联字段

2、取两张表的笛卡尔积

外连接分为左外连接和右外连接。如果联合查询，左侧表完全显示就称为左外连接，右侧表完全显示就称为右外连接。

9.2.1右外连接

SELECT 列名1, 列名2,...FROM 表1RIGHTJOIN 表2ON 表1.共同列 = 表2.共同列;

示例：

在学生表中，没有学生的班级id是3，但是想要显示这个班级，就需要用到右外连接。

9.2.2左外连接

SELECT 列名1, 列名2,...FROM 表1LEFTJOIN 表2ON 表1.共同列 = 表2.共同列;

示例：

5.加入最后条件计算机组成原理”成绩大于“Java”

4.观察结果集，确定过滤条件
要么是s1表中的course_id = 1 并且s2表中course_id = 3；
要么是s1表中的course_id = 3 并且s2表中course_id = 1；
任意选取其中一个

3.确定连接条件
连接条件中的student_id必须要相等

圈出来的是符合条件的记录

2.取笛卡尔积

⚠️ 注意：
表名重复了，需要通过取别名的方式来取笛卡尔积。

1.确定涉及的表：课程表，成绩表

在同一个学生中必须同时修了这两门课才能做比较

查询哪位同学没有考试成绩
1.在同学表中有记录
2.在分数表中没有对应的记录

⚠️ 注意：MYSQL中不支持全外连接 FULL JOIN

9.3自连接

自己与自己表连接，可以把行转换成列，在查询的时候使用where条件进行过滤，也就是可以实现行与行之间的比较功能
示例：显示所有“计算机组成原理”成绩比“Java”高的成绩信息

9.4子查询

也叫嵌套查询，子查询是把一条SQL的查询结果，当作另外一条SQL的查询条件，可以嵌套很多很多层。
⚠️ 注意：由于嵌套层级没有固定限制，多层嵌套查询的效率是不可控的，工作谨慎使用。

9.4.1单行子查询

返回的是一个对象
示例：
查询“不想毕业”同学的同班同学

1.参与查询的表
学生表

对整体加过滤条件也是可以的

3.在学生表中查出与“不想毕业”同学的班级编号相同的学生

2.先查出“不想毕业”同学的班级编号

9.4.2多行子查询

返回的是一个集合，集合包含多个对象

SELECT*FROM 表1WHERE 列名 IN(SELECT 列名 FROM 表2WHERE 条件);

示例：
查询“语文”或“英文”课程的成绩信息

4.把以上分布查询SQL拼装起来，变成子查询

3.根据获取到的课程编号，在成绩表中查询对应的课程分数

在课程表中获取“语文”或“英文”课程的编号

1.涉及哪些表
课程表，成绩表

9.4.3 [NOT] EXISTS关键字

select*from 表名 whereexists(select*from 表名1)； -- exists后面括号中的查询语句，如果有结果返回，则执行外层的查询--如果返回的是空结果集，则不执行外层的查询

示例：

返回的是空结果集，则不执行外层的查询

⚠️ 注意：
列名为null，值也为null时，返回的集合是一个非空的

有结果返回，则执行外层的查询

9.5合并查询

作用：合并多个查询结果到一个结果集中，关键字为union ,union all。

1.5.1根据一张表的结构，创建新表

createtable 新表 like 旧表;

9.5.2 union 和union all

示例1：
想要合并这两张表

合并之后：

⚠️ 注意
在单表中还是更推荐使用or去连接不同的查询条件
在多表中，就没有办法用or,如果最终结果是从多个表中获取的，必须要用union来进行合并。

示例2：
合并这两张表

通过union all关键字合并这两张表，发现union all关键字不会自动去掉重复的行

通过union关键字合并这两张表，发现union会自动去掉重复的行

union 和union all总结

对比项	UNION	UNION ALL
是否保留重复数据	否	是

学习路上一起进步，如果觉得内容不错，记得点赞支持一下，也可以关注我，后续持续分享高质量技术文章！