MySQL多表关系与多表查询

学习前思路拆解：

1.理解数据为什么要拆表存储

2.明白多表有几种数据关系

3.掌握表关系限制的语法并实现

4.掌握多表查询语法和练习

数据库多表关系

1.为什么要拆表存储数据？

因为将数据全部存储在一个表中，容易出现数据冗余。

例子：学生表和学生成绩表

不拆表存储：

拆表存储：

                           

       学生表和学生成绩表不拆分，表内出现重复学号，人名等信息，显得臃肿。而将学生表和学生成绩表拆分存储。学生表可以包含学生的基本信息，如姓名、年龄和班级，而成绩表则专注于记录各科目的成绩。

仅是通过将数据分散到多个表中，就可以减少数据冗余，优化查询速度，并提升数据的维护性。

2.多表有几种数据关系

多（两及两以上）表数据关系总共可以分为三种：

1. 一对一（1:1）：一个表中的一条记录仅对应另一个表中的一条记录。例如，每个学生只对应一个学生档案。

2. 一对多（1:N）：一个表中的一条记录可以对应另一个表中的多条记录。例如，一个班级可以有多个学生。

3. 多对多（M:N）：两个表中的多条记录可以互相对应。这通常通过中间表实现，例如学生和课程之间的关系，一个学生可以选修多门课程，而一门课程也可以有多个学生。（双向查看）。

例子：

       

3.表关系限制的语法并实现

3.1 一对一（1:1）

特点：
每条记录在一个表中与另一表的唯一一条记录对应。
冗余解决：
一对一并不能解决数据冗余问题
存在意义：
有助于将不同类型的信息分开管理，例如将基本信息与详细信息分开，使数据更具可读性。
关系维护：
只需在两个表中维护相应的外键关系，但外键唯一。

两种方案：1.子表外键于主键融合，两表共用同个主键。2.给子表外键添加唯一约束。

 例子代码：

一对一：                                                    

3.2 一对多（1:N）

特点：
一条记录在一个表中可以在另一个表中对应多条记录。
即：主表对应多条子表数据，子表对应主表至多一条数据。
冗余解决：
一对多可以解决数据冗余问题。
存在意义：
适用于多个对象归属于同一类别的情况，如一个班级下有多个学生，便于组织和管理数据。
关系维护：
需要确保一个表的主键在另一个表中作为外键。（外键不唯一）

例子代码：

一对多

3.3 多对多（M:N）

特点：
两个表中的多条记录可以相互对应，通常通过中间表来实现。
冗余解决：
通过中间表，将多对多的关系转化为两次一对多的关系，减少数据冗余。
存在意义：
适用于复杂的关系场景，例如学生可以选修多门课程，而一门课程也可以有多个学生参与。
关系维护：
中间表包含两个外键，主表数据之间间接关联。

例子代码：

多对多：

4.多表查询语法和练习

数据查询语法分类：1.单表查询 2.多表查询

在本文章中主要介绍多表查询，单表查询不做过多讨论。

多表查询的关键是理解如何通过不同的连接和合并方式来获取和处理数据。

按照多表查询的结果可以分为垂直合并与水平合并。

4.1 垂直合并

垂直合并使用的是：联合语法，即 UNION 和 UNION ALL 

UNION :合并记录，同时去掉重复数据
UNION ALL :合并记录，但不去掉重复数据（都不要求主外键）

例子：通过搜索旅游，得到的数据有视频，图片，文章等，数据分别可视为来自视频数据库，图片数据库，以及网站数据库。

代码实战：

CREATE TABLE a (
    aname VARCHAR(100),
    score INT
);

CREATE TABLE b (
    bname VARCHAR(100),
    bcore INT
);
INSERT INTO a (aname, score) VALUES
('小明', 88),
('小红', 77);
INSERT INTO b (bname, bcore) VALUES
('小明', 88),
('小蓝', 55),
('小鹿', 66);

表数据结果：

a表：                                            b表：

 

使用 UNION
UNION 用于合并两个结果集，并去除重复记录。
SELECT aname, score FROM a
UNION
SELECT bname, bcore FROM b;

查询结果为：

结果分析：可以看到小明只出现了一次，因为UNION去除了重复记录。

使用 UNION ALL
UNION ALL 用于合并两个结果集，并保留所有记录，包括重复记录。
SELECT aname, score FROM a
UNION ALL
SELECT bname, bcore FROM b;

结果分析：小明在结果中出现了两次，因为 UNION ALL 保留所有记录，包括重复的。

使用细节：

1.重复数据：一行中所有列值都相同则为重复行。
2.合并的多个结果集之间的列数和类型要一一对应。

4.2 水平合并

水平合并可分为：内连接，外连接以及交叉连接   （要求主外键）

水平合并的例子：

          

水品合并后

4.2.1 内连接

基本语法:
SELECT 列 FROM 表1 as 别名 INNER JOIN 表2 别名 ON 表1别名.主键 = 表2别名.外键;

SELECT 列 FROM 表1 as 别名,表2 别名 WHERE 表1别名.主键 = 表2别名.外键;

注意：

       使用表来代替是因为不同的表存在相同的列名导致理解上出现错误；因为连接本质上是笛卡尔积，即将所有的行都拼接一边，所以为了避免出现错误的数据连接所以在连接查询必须添加主外键。

例子：

/*
   学生表（students）
   学生ID（student_id）
   姓名（name）*/

CREATE TABLE students (
    student_id INT,
    name VARCHAR(100)
);

/*
成绩表（grades）
学生ID（student_id）
成绩（score）*/

CREATE TABLE grades (
    student_id INT,
    score INT
);

--  插入数据到学生表
INSERT INTO students (student_id, name) VALUES
(1, '小明'),
(2, '小红'),
(3, '小蓝');

--  插入数据到成绩表
INSERT INTO grades (student_id, score) VALUES
(1, 88),
(2, 77),
(3, 90),
(4, 85);  -- 注意：这里的学生ID 4 没有对应学生

/*内连接查询
我们要查询每位学生及其对应的成绩：*/

SELECT s.name, g.score
FROM students AS s
INNER JOIN grades AS g ON s.student_id = g.student_id;

结果图：

注意：学生ID 为 4 的记录没有被返回，因为内连接只返回匹配的记录。

4.2.2 外连接

外连接分为左外连接和右外连接。

左外连接（LEFT JOIN）：返回左表的所有记录，以及右表中匹配的记录。

基本语法：
SELECT * FROM 表a AS a LEFT [OUTER] JOIN 表b AS b ON 表a.主键 = 表b.外键;

右外连接（RIGHT JOIN）：返回右表的所有记录，以及左表中匹配的记录。

基本语法：
SELECT * FROM 表a AS a RIGHT [OUTER] JOIN 表b AS b ON 表a.主键 = 表b.外键;

注意：

       外连接可以通过左和右指定一个逻辑主表，逻辑主表数据一定会查询到；但内连接只满足匹配条件的行数，要求两个表必须存在且主外键值相等才会被返回；[OUTER]可以被省略；如果有逻辑主表，就将逻辑主表放在第一位，后面全为左外连接。

例子：

/*
   学生表（students）
   学生ID（student_id）
   姓名（name）*/

CREATE TABLE students (
    student_id INT,
    name VARCHAR(100)
);

/*
成绩表（grades）
学生ID（student_id）
成绩（score）*/

CREATE TABLE grades (
    student_id INT,
    score INT
);

--  插入数据到学生表
INSERT INTO students (student_id, name) VALUES
(1, '小明'),
(2, '小红'),
(3, '小蓝');

--  插入数据到成绩表
INSERT INTO grades (student_id, score) VALUES
(1, 88),
(2, 77),
 -- 注意：这里的学生ID 3 没有对应成绩

--左外连接查询

SELECT s.name, g.score
FROM students AS s
LEFT JOIN grades AS g ON s.student_id = g.student_id;

结果：

结果分析：因为成绩表中小蓝没有成绩，所以左外连接查询小蓝结果为NULL

--右外连接查询

SELECT s.name, g.score 
FROM students AS s 
RIGHT JOIN grades AS g ON s.student_id = g.student_id;

结果：

结果分析：因为成绩表中没有学号为3的学生，所以右外连接不会显示任何匹配成绩。

4.2.3 自然连接

自然连接根据同名列自动匹配两个表的记录。
SELECT * FROM  emp NATURAL JOIN dept;（自然内连接）

SELECT * FROM  emp NATURAL LEFT JOIN dept; (自然左外连接)

SELECT * FROM  emp NATURAL RIGHT JOIN dept; (自然右外连接)

作用：和内外连接效果一致；自动查找主外键相等（找到列名相同判定相等，两个表只有主外键命名相等）；