MySQL 多表查询

2022-11-23 139

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云数据库 RDS MySQL，集群系列 2核4GB

RDS MySQL Serverless 基础系列，0.5-2RCU 50GB

云数据库 RDS MySQL，高可用系列 2核4GB

简介： MySQL 多表查询

引言

多表查询与数据库的设计有密切的关系，在设计之前，我们需要先找实体，再找实体之间的关系。而找实体就相当于在 Java 中找对象一样，寻找的方法需要依赖各个表之间的关系，或者说，一张表的字段与另一张表的字段有什么关联。

一、三种关系

(1) 一对一关系

一名学生只能对应一个账户，一个账户也仅对应一名学生。

创建一张账户表，将账户名和学生ID 对应起来。

由于账户名和学生ID 是唯一的，所以我们可以设置约束【primary key / unique】

-- 表1 account
账户名   学生ID    学生Name
456     1     Jack
123     2     Rose
789     3     James

(2) 一对多关系

一名学生只能在一个班级中，一个班级可以包含多名学生。

创建两张表：班级表和学生表。

我们可以将 StudentID 和 RoomID 约束为【primary key】，而将表1 中学生所在的班级和表2 中的 RoomID 利用【foreign key】约束起来。

-- 表1 student 
StudentID   Name    所在班级
1           Jack    3(英才班)
2         Rose    1(普通班)
3         Ron     2(重点班)

-- 表2 class
RoomID      Name
1       普通班 
2       重点班
3       英才班
4       竞赛班

(3) 多对多关系

一名学生可以选择多门课，一门课程可以包含多名学生。

创建三张表：学生表，课程表，学生-课程关联表。

关联表中放着【学生ID 和课程编号】两者之间的对应关系。

-- 表1 student 
StudentID   Name    所在班级
1           Jack    3(英才班)
2         Rose    1(普通班)
3         Ron     2(重点班)

-- 表2 lesson
LessonID      Name
1         语文  
2         数学
3         英语
4         物理

-- 表3 association 
StudentID       LessonID
1         1 
1         2
1         3
2         4
...         ...

二、笛卡尔积

笛卡尔积的运算过程

笛卡尔积的运算过程：按顺序将第一张表A 的每条记录和第二张表B 的每条记录分别组合，得到了一张新的表C 。

笛卡尔积所产生新的表C，其列数就是表A 和表B 两张表的列数之和，其行数就是表A 和表B 两张表的行数之积。

如果表A 有五行三列，表 B 有六行五列，那么表C 就有三十行八列。

而在工作中，如果A表和B表都非常的大，如果贸然使用笛卡尔积运算，很大可能会导致数据库崩溃，会给公司造成很大的损失。所以在工作中，一般禁止使用多表查询。但我们还是需要学习多表查询，因为它之中有很多有用的查询功能。

演示简单的多表查询

我们创建两张表，一张表是 student，另一张表是 class，接着我们通过笛卡尔积来将两张表串联起来。

drop table if exists class;
create table class (
   RoomID int primary key auto_increment,
   Name varchar(20)
);
drop table if exists student;
create table student (
  studentID int primary key auto_increment,
  Name varchar(20),
  Class int
);
insert into class values(null,'普通班'), (null,'重点班');
insert into student values(null,'Jack',1), (null,'Rose',2), (null,'Ron',1);

表 student 
StudentID Name  Class
1         Jack  1(普通班)
2       Rose  2(重点班)
3       Ron   1(普通班)

表 class
RoomID    Name
1     普通班 
2     重点班

笛卡尔积
StudentID Name    Class   RoomID    Name
1         Jack    1     1     普通班 
1         Jack    1     2     重点班
2       Rose    2     1     普通班 
2       Rose    2     2     重点班
3       Ron     1     1     普通班 
3       Ron     1     2     重点班

在两张联合表中，有6组数据，而只有3组数据才是正确的，或者说，只有3组数据才是我们想要的，我通过红色框框标明出来了。因为笛卡尔积是一个单纯、无约束的排列组合，这里面的组合代表所有的情况，很大可能只是包含了我们某个需要的 " 结果 "，所以说我们需要在这所有的组合中去找到我们需要的。

如下，当班级号相同的时候，即是我们想要的。

select student.* , class.* from student, class; -- 写法一
select * from student, class; -- 写法二

所以我们可以通过下图程序筛选出我们想要的数据，使用 select 和 where 的时候，最好加上【表名.列名】，这样一来，我们可以避免 " 同名列 " 的情况出现。

而下面的【where student.Class = class.RoomID】也就是两张表的 " 连接条件 "，这很关键。也就是说，如果在笛卡尔积中找到我们想要的结果，一般来说，找到连接条件就是一个核心问题。

三、更复杂的多表查询

多表查询就是联合查询。

1. 创建四张表

student 表
classes 表
course 表
score 表
-- 其中，classes 表和 student 表是一对多关系。
-- student 表和 course 表是多对多关系。
-- 而 score 表将 学生和课程联合起来了，所以 score 表即是一个联合表

create table classes(
    id int primary key auto_increment,
    name varchar(20),
    `desc` varchar(100)
);
create table student(
    id int primary key auto_increment,
    sn varchar(20),
    name varchar(20),
    qq_mail varchar(20),
    classes_id int
 );
create table course(
    id int primary key auto_increment,
    name varchar(20)
);
create table score(
    score decimal(3,1),
    student_id int,
    course_id int
);
insert into classes(name, `desc`) values
    ('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
    ('中文系2019级3班','学习了中国传统文学'),
    ('自动化2019级5班','学习了机械自动化');
insert into student(sn, name, qq_mail, classes_id) values
    ('09982','黑旋风李逵','xuanfeng@qq.com',1),
    ('00835','菩提老祖',null,1),
    ('00391','白素贞',null,1),
    ('00031','许仙','xuxian@qq.com',1),
    ('00054','不想毕业',null,1),
    ('51234','好好说话','say@qq.com',2),
    ('83223','tellme',null,2),
    ('09527','老外学中文','foreigner@qq.com',2);
insert into course(name) values
    ('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');
insert into score(score, student_id, course_id) values
    -- 黑旋风李逵
    (70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
    -- 菩提老祖
    (60, 2, 1),(59.5, 2, 5),
    -- 白素贞
    (33, 3, 1),(68, 3, 3),(99, 3, 5),
    -- 许仙
    (67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
    -- 不想毕业
    (81, 5, 1),(37, 5, 5),
    -- 好好说话
    (56, 6, 2),(43, 6, 4),(79, 6, 6),
    -- tellme
    (80, 7, 2),(92, 7, 6);

2. 查询许仙同学的所有成绩

分析问题：

" 许仙 " 这个名字来自于 student 表，" 成绩 " 来自于 score 表。

步骤(1)

我们利用笛卡尔积来联合查询 student 表和 score 表，然而，我们会发现很多数据并不是我们想要的结果。因为我们把两张表的所有列都整合在一起了，这是一个排列组合的结果，把所有情况都考虑进去了!

所以我们能看到下图数据有很多很多行，我都没有全部截图下来 !

这里需要注意一点，下面两种形式是等价的：

select * from student, score; -- 写法一
select student.*, score.* from student, score; -- 写法二

步骤(2)

由于 student 表中的 id 和 score 表中的 id 两者是相等关系，所以，我们需要设置此连接条件。这个时候，我们就会发现：查询出来的临时表已经缩小了很多。而下面的临时表就表示：【每个同学的每门课程的成绩】

步骤(3)

最后，由于我们只需要找到许仙同学的【名字和所有成绩】，所以我们可以再次设置条件。

总结：刚开始学习多表查询的时候，千万不要试图一步到位，最好是一点一点分析，一点一点写。

① 先分析数据来自哪些表，然后笛卡尔积，观察笛卡尔积的结果。

② 根据上述结果，筛选出合法的结果。

③ 再根据需求，一点一点接近预期。

3. 查询所有同学的名字和对应的总成绩

① 按 ID 相等，找到每个同学对应的每门课程

② 按【group by】为每个同学的姓名分组

③ 由于我们查询的是所有同学的【姓名 + 总成绩】，所以再次设定条件

4. 查询所有同学的名字、每门课程的名字、每门课程对应的分数

思想：将三张表进行笛卡尔积，再设置条件。

四、内连接与外连接

前几个都是通过内连接来查询数据，下面我们来看看外连接，并观察它们之间的区别。( 内连接在日常使用的过程中较为普遍，但外连接用的较少 )

-- 内连接
select 列 from 表1, 表2  where  连接条件 and 其他条件;
select 列 from 表1 [inner] join 表2  on 连接条件 and 其他条件;

-- 外连接
select 列 from 表1 left join 表2  on 连接条件 and 其他条件; --左外连接
select 列 from 表1 right join 表2  on 连接条件 and 其他条件; --右外连接

给出两张表：student 表和 score 表

我们发现 student 表中 ID=3，在 score 表中不存在。

score 表中 ID=4，在 student 表中不存在。

也就是说，这两张表并不是一一对应的。

表 student 
ID      Name  
1         Jack  
2       Rose  
3       Ron

表 score
StudentID score
1     90  
2     70
4     80

(1) 内连接的两种写法

我们只找两张表共同存在的部分

(2) 左外连接和右外连接

我们发现：左外连接不仅显示两张表的共同部分，也显示左表不存在于右表的部分。

而右外连接不仅显示两张表的共同部分，也显示右表不存在于左表的部分。

关系图：