全面解析MySQL(4)——三大范式与联合查询实例教程

1.三大范式

概念：数据库三大范式(Normal Forms)是关系型数据库设计中的基本原则，用于减少数据冗余和提高数据一致性

1.1 第一范式(1NF)

定义：确保每一列的原子性，即每一列都是不可再分的最小数据单元 要求：

• 每个字段只能包含单一值，不能是集合或数组
• 表中不能有重复的列。关系模型的创始人 E.F. Codd 最初提出范式理论时，要求关系表必须有一个主键/联合主键(唯一标识每一行的字段或字段组合)

示例：订单表

订单ID(order_id)	客户信息(customer_info)
1	张三，四川省XXX市，17392871691

• 无法单独查询四川省的客户信息(insert into table_name where address = '四川省')
• 更新客户地址时需要解析字符串(可能需要借助后端额外处理)

修正后示例：

订单ID(order_id)	客户姓名(user_name)	地址(address)	电话号码(tele)
1	张三	四川省XXX市	17392871691

1.2 第二范式(2NF)

定义：在满足第一范式的基础上，消除非主键字段对主键的部分函数依赖(即非主键字段必须完全依赖整个主键/联合主键，而非部分主键)
联合主键(Composite Primary Key)：是指由数据库表中两个或多个字段共同组成的主键，用于唯一标识表中的每一行数据。与单字段主键不同，联合主键通过多个字段的组合确保数据的唯一性

示例：学生-课程成绩表
使用学号+课程名唯一标识某一行记录，这两列就是该表的联合主键

学号(student_id)	姓名(student_name)	性别(sex)	课程名(course_name)	学分(credit)	分数(score)
1	张三	男	MySQL	4	80
2	李四	女	Java	4	75
1	张三	男	Java	4	70
3	王五	男	MySQL	4	85

姓名、性别两列由学号确定，和课程名没关系，即姓名和学号两列部分依赖学号；学分由课程名确定，和学号没关系，即学分这列部分依赖课程名

• 数据冗余：姓名、性别、学分三列在表中重复出现，造成大量冗余
• 更新异常：更新Java这门课的学分时，如果因为某些意外而中断更新，就会导致该表中Java的学分不一致

修正后示例：
学生表

学号(id)	姓名(name)	性别(sex)
1	张三	男
2	李四	女
1	张三	男
3	王五	男

课程表

课程ID(id)	课程名(course_name)	学分(credit)
1	MySQL	4
2	Java	4

分数表

学号	课程ID(id)	分数(score)
1	1	80
1	2	70
2	2	75
3	1	85

1.3 第三范式(3NF)

定义：在满足第二范式的基础上，消除非主键字段之间的传递依赖(即非主键字段不能依赖于其他非主键字段)

示例：员工部门表

员工ID	姓名	部门ID	部门名称
1	张三	研发部	北京总部
2	李四	市场部	上海分部
3	王五	研发部	北京总部

部门名称依赖于部门ID，而不是直接依赖于员工ID，存在传递依赖：员工ID → 部门ID → 部门信息

• 数据冗余：研发部名称重复存储
• 更新异常：修改部门所在地需要更新多条记录

2.联合查询

2.1 概述

使用场景：在数据设计时由于三大范式的要求，数据被拆分到多个表中，那么要查询⼀条数据的完整信息，就要从多个表中获取数据，这时就要使用联合查询

笛卡尔积(Cartesian Product)：联合查询的基础，指两个集合中所有可能的有序组合。在MySQL中，当联合查询未指定关联条件时，查询结果会返回所有表的行组合(列相加，行相乘)

学生表

学号	姓名	性别	班级编号
1	张三	男	1
2	李四	男	3
3	王五	女	2

班级表

编号	班级
1	Java班
2	C++班
3	Python班

两表进行笛卡尔积

学号	姓名	性别	班级编号	编号	班级
1	张三	男	1	1	Java班
1	张三	男	1	2	C++班
1	张三	男	1	3	Python班
2	李四	男	3	1	Java班
2	李四	男	3	2	C++班
2	李四	男	3	3	Python班
3	王五	女	2	1	Java班
3	王五	女	2	2	C++班
3	王五	女	2	3	Python班

笛卡尔积生成的结果集可能包含无效或冗余数据。无效数据通常指不符合业务逻辑、无实际意义或违反约束条件的组合。上述笛卡尔积的结果集中，只有学生表的班级编号与班级表的编号相同的记录才有实际意义

学号	姓名	性别	班级编号	编号	班级
1	张三	男	1	1	Java班
2	李四	男	3	3	Python班
3	王五	女	2	2	C++班

构造练习数据：

-- 创建学生表
create table classes (class_id int primary key auto_increment,class_name varchar(50) not null,teacher_name varchar(50),created_at timestamp default current_timestamp
);
-- 插入班级数据
insert into classes (class_name, teacher_name) values
('计算机科学1班', '张老师'),
('计算机科学2班', '李老师'),
('数学实验班', '王老师'),
('英语提高班', '赵老师'),
('物理竞赛班', NULL);  -- 这个班级暂时没有分配老师
--创建班级表
create table students (student_id int primary key auto_increment,student_name varchar(50) not null,gender char(1) check (gender in ('M', 'F')),age int,class_id int,enrollment_date date,foreign key (class_id) references classes(class_id) on delete set null 
);
-- 插入学生数据
insert into students (student_name, gender, age, class_id, enrollment_date) values
('张三', 'M', 20, 1, '2023-09-01'),
('李四', 'M', 21, 1, '2023-09-01'),
('王五', 'F', 19, 2, '2023-09-01'),
('赵六', 'F', 20, 2, '2023-09-02'),
('钱七', 'M', 22, 3, '2023-09-03'),
('孙八', 'F', 20, 3, '2023-09-03'),
('周九', 'M', 21, NULL, '2023-09-04'),  -- 这个学生尚未分班
('吴十', 'F', 19, NULL, '2023-09-05'); -- 这个学生尚未分班

2.2 内连接(inner join)

定义：在笛卡尔积的有效结果集基础上，仅返回满足条件的记录(如果不指定其他条件，内连接的结果集和笛卡尔积的有效结果集完全一样)

基础语法：

#语法1
select 字段 from 表1 别名1, 表2 别名2 where 连接条件 and 其他条件;
#语法2
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 where 其他条件;

示例：查询(student_id <= 3)的学生信息和班级信息
写法1：
写法2：

2.3 外连接

2.3.1 左外连接(left outer join)

定义：返回左表的所有/指定行，即使在右表中没有匹配的行(右表中未匹配的部分用null填充)。换言之，在有效笛卡尔积基础上增加未能与右表匹配的行，这些新增行的右表部分用null值填充

基础语法：

select 字段 from 表名1 left outer join 表名2 on 连接条件;

示例：

2.3.2 右外连接(right outer join)

定义：返回右表的所有行，即使在左表中没有匹配的行(左表中未匹配的部分用null填充)。换言之，在有效笛卡尔积基础上增加未能与左表匹配的行，这些新增行的左表部分用null值填充

基础语法：

select 字段 from 表名1 right outer join 表名2 on 连接条件;

示例：

2.3.3 全外连接(full outer join，MySQL不支持)

定义：返回左表和右表的所有行，未匹配的部分用null填充。若某行在另一表中有匹配，则显示匹配值

2.4 自连接

使用场景：实现行与行之间的比较

定义：自连接(Self Join)是SQL中的一种特殊连接操作，指同一张表通过别名(as)模拟成两张表，将行与行之间的关系转换为列与列之间的关系，并根据关联条件进行连接查询

基础语法：

select 字段 from table_name as 别名1,table_name as 别名2 where 连接条件 and 其他条件;

示例：查询数学成绩高于语文成绩的记录

2.5 子查询(Subquery)

定义：指一个SQL语句查询内部嵌套另一个完整的select查询语句

构造数据：

#学生表
create table students (student_id int primary key,name varchar(50) NOT NULL,class_id int,gender char(1),age int
);
insert into students values
(101, '张三', 1, '男', 18),
(102, '李四', 1, '女', 17),
(103, '王五', 2, '男', 19),
(104, '赵六', 2, '女', 18),
(105, '钱七', 3, '男', 17);
#成绩表
create table scores (score_id int primary key,student_id int,subject varchar(20),score int,foreign key (student_id) references students(student_id)
);
insert into scores values
(1, 101, '数学', 85),
(2, 101, '英语', 78),
(3, 102, '数学', 92),
(4, 102, '英语', 88),
(5, 103, '数学', 65),
(6, 103, '英语', 72),
(7, 104, '数学', 55),
(8, 104, '英语', 91),
(9, 105, '数学', 82),
(10, 105, '英语', 77);

2.5.1 单行子查询

定义：单行子查询指返回单个值(一行一列)的子查询，主查询将子查询结果作为标量值使用

基础语法：

select 列名 from table1 where 列名 = (select 列名 from table2...);

示例：查询张三的数学成绩

#先在students表中查询到张三的student_id
select student_id from students where name = '张三';
#再在scores表中利用查到的student_id来查询张三的数学成绩
select subject,score from scores where subject = '数学' and student_id = 101;#合并后的子查询
select subject,score from scores where subject = '数学' and student_id = 
(select student_id from students where name = '张三');

2.5.2 多行子查询

定义：主查询将子查询返回的多行单列结果集作为条件使用

基础语法：

select 列名 from table1 where 列名 in (select 列名 from table2...);

示例：查询数学成绩大于80的学生姓名

#先在scores表中查询数学成绩大于80的student_id
select student_id from scores where subject = '数学' and score > 80;
#再在students表中利用查到的student_id来姓名
select name from students where student_id in (101,102,105);#合并后的子查询
select name from students where student_id in 
(select student_id from scores where subject = '数学' and score > 80);

2.5.3 from子句中的子查询

定义：子查询作为临时表嵌入from子句(称为派生表)，需指定别名

示例：查询成绩大于平均成绩的记录

select * from scores as s,(select avg(score) as avg_score from scores) as a 
where s.score > a.avg_score;

2.6 合并查询

定义：合并查询(Set Operation Queries)是SQL中用于组合多个select结果集的操作
要求：需要查询的列数和数据类型匹配，列名不影响

基础语法1：

select col_1,col_2 ... from table_1
union
sekect col_1,col_2 ... from table_2;

• 自动去除重复行

基础语法2：

select col_1,col_2 ... from table_1
union all 
sekect col_1,col_2 ... from table_2;

• 保留重复行
• 性能优于union