MySQL(4)：三大范式与联合查询深入解读

文章标题：

MySQL(4)：深度剖析三大范式与联合查询

1.三大范式

1.1 第一范式(1NF)

概念：数据库的三大范式是关系型数据库设计里的基本准则，其作用是降低数据冗余度并提升数据的一致性

定义：要保证每一列都具有原子性，也就是每一列都是不可再分的最小数据单元。
要求：
* 每一个字段只能包含单一的值，不能是集合或者数组的形式
* 表中不能有重复的列。关系模型的开创者E.F. Codd最初提出范式理论时，要求关系表得有一个主键或者联合主键，用来唯一标识每一行数据

示例：订单表

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

修正后示例：

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的学分不一致

修正后示例：
学生表

课程表

成绩表

1.3 第三范式(3NF)

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

示例：员工部门表

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

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

2.联合查询

2.1 概述

使用场景：因为三大范式的要求，数据会被拆分到多个表中，要是想要查询一条数据的完整信息，就得从多个表中获取数据，这时候就要用到联合查询

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

学生表

班级表

两表进行笛卡尔积

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

构造练习数据：

-- 创建班级表
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 自连接

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

定义：自连接是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);

![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/572b4c62eb734f5dbb81ebe1