MySQL【知识改变命运】10

0.前言

前言：为什么要联合查询？什么是联合查询呢?
1：在学过范式之后，我们会对一张表的数据，按照范式的标准拆分为多张表，这样便于降低数据的冗杂，但是我们如果查询表的数据，我们又如何一次性查询所有的数据呢？这时候就用到了联合查询
2：联合查询就是多个表的组合查询，一次查询涉及两张或则两张以上的表，成为联合查询

1.联合查询在MySQL里面的原理

• 参与查询的所有表笛卡尔积，结果集在临时表中
• 实例:创建两张表，一张student 表，一张class表
  
  插入数据
  
  表如下：

• 参与查询的所有表取笛卡尔积，结果集在临时表中：
  
  

• 观察哪些记录是有效数据，根据两个表的关联关系过滤掉⽆效数据

如果联合查询表的个数越多，表中的数据量越⼤，临时表就会越⼤，所以根据实际情况确定联合查询表的个数

2.练习一个完整的联合查询

2.1.构造练习案例数据

# 课程表
insert into course (name) values ('Java'), ('C++'), ('MySQL'), ('操作系统'), ('计
算机⽹络'), ('数据结构');
# 班级表
insert into class(name) values ('Java001班'), ('C++001班'), ('前端001班');
# 学⽣表
insert into student (name, sno, age, gender, enroll_date, class_id) values
('唐三藏', '100001', 18, 1, '1986-09-01', 1),
('孙悟空', '100002', 18, 1, '1986-09-01', 1),
('猪悟能', '100003', 18, 1, '1986-09-01', 1),
('沙悟净', '100004', 18, 1, '1986-09-01', 1),
('宋江', '200001', 18, 1, '2000-09-01', 2),
('武松', '200002', 18, 1, '2000-09-01', 2),
('李逹', '200003', 18, 1, '2000-09-01', 2),
('不想毕业', '200004', 18, 1, '2000-09-01', 2);
# 成绩表
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),
(80, 7, 2),(92, 7, 6);

2.2 案例：⼀个完整的联合查询的过程

• 查询学⽣姓名为宋江的详细信息，包括学⽣个⼈信息和班级信息

2.2.1. 确定参与查询的表，学⽣表和班级表

# 在from后同时写所有参与查询的表，并⽤逗号隔开(取笛卡尔积)
select *from student,class;

通过观察两张表联合后的结果集中，有些是无效的数据，如何过滤这些无用的数据呢？

2.2.2. 确定连接条件，student表中的class_id与class表中id列的值相等

连接条件，student表中的class_id与class表中id列的值相等
# 在where⼦句中加⼊连接条件
mysql> select * from student, class where student.class_id = class.id;

• 这里有个新的知识点，class_id在student表，但是两张表都有id列，不指定表名时MySQL无法分清要使用那张表的id列。这时候用表名.列名就可以区分。

2.2.3. 加⼊查询条件

• 上面查询产生了一个错误，因为我们无法知道这个name是指代的是那张表的name。
• 正确的写法如下：
  

2.2.4. 精减查询结果字段

select
 student.id, 
 student.name, 
 student.sno, 
 student.age, 
 student.gender, 
 student.enroll_date, 
 class.name 
from student, class 
where
 student.class_id = class.id 
and
 student.name = '宋江';

2.2.5. 可以为表名指定别名

2.3联合查询总结：

1. 确定几张表参与查询
2. 根据表与表之间的关系，确定过滤条件
3. 通过WHERE条件对整个结果集进行过滤
4. 精减字段，得到想要的结果
5. 通过为表起列名，精减SQL语句

3.内连接

3.1 语法

1 select 字段 from 表1 别名1, 表2 别名2 where 连接条件 and 其他条件;（简写）
2 select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 where 其他条件;（规范写法）

3.2 示例

3.2.1. 查询"唐三藏"同学的成绩

1. 确定几张表：两张学生表和成绩表（取两张表的笛卡尔积）

select * from student,score;

2. 根据表与表之间的关系，确定过滤条件

select * from student,score where student.id=score.student_id ;

3. 添加where条件
   
4. 精减查询字段

select student.name,score.score from student,score where student.id=score.student_id and student.name='唐三藏';

5. 通过起别名，精减整个sql语句

select s.name,sc.score from student s,score sc where s.id=sc.student_id and s.name='唐三藏';

6. 用规范的写法写：

select s.name, sc.score from student s join score sc on sc.student_id = 
s.id where s.name = '唐三藏';

总结一下：

3.2.2.查询所有同学的总成绩，及同学的个⼈信息

select s.name,sum(sc.score) from student s join score sc on s.id=sc.student_id group by s.id;

• 这里注意一下这个分组
• 

分组之后，列的值在组内是相同的，即使该列不是分组列（没有在group by指定），依然可以写在查询列表中。

3.2.3.查询所有同学的总成绩，及同学的个⼈信息

1.select * from student s join score sc on s.id=sc.student_id join course c on c.id=sc.course_id;
2.select * from student s,score sc,course c where c.id=sc.course_id and s.id=sc.student_id;

• 切记关联查询表不要超过三张，在以后真正工作的时候不要做大表的表关联查询。

4. 外连接

• 外连接分为左外连接、右外连接和全外连接三种类型，MySQL不⽀持全外连接。
• 左外连接：返回左表的所有记录和右表中匹配的记录。如果右表中没有匹配的记录，则结果集中对应字段会显⽰为NULL。
• 右外连接：与左外连接相反，返回右表的所有记录和左表中匹配的记录。如果左表中没有匹配的记录，则结果集中对应字段会显⽰为NULL。
• 全外连接：结合了左外连接和右外连接的特点，返回左右表中的所有记录。如果某⼀边表中没有匹配的记录，则结果集中对应字段会显⽰为NULL。

4.1.语法

-- 左外连接，表1完全显⽰
select 字段名 from 表名1 left join 表名2 on 连接条件;
-- 右外连接，表2完全显⽰
select 字段 from 表名1 right join 表名2 on 连接条件;

4.2.查询没有参加考试的同学信息

# 左连接以JOIN左边的表为基准，左表显⽰全部记录，右表中没有匹配的记录⽤NULL填充
select * from student s left join score sc on s.id=sc.student_id; 

• 左表中显示所有记录，右边中没有与左表匹配的记录用NULL填充。
  添加where 条件
  

4.3.查询没有学⽣的班级

# 右连接以JOIN右边的表为基准，右表显⽰全部记录，左表中没有匹配的记录⽤NULL填充
select * from student s right join class c on c.id=s.class_id; 

添加where条件过滤

5. ⾃连接

5.1.应⽤场景

⾃连接是⾃⼰与⾃⼰取笛卡尔积，可以把⾏转化成列，在查询的时候可以使⽤where条件对结果进⾏过滤，或者说实现⾏与⾏之间的⽐较。在做表连接时为表起不同的别名。

# 不为表指定别名
mysql> select * from score, score;
ERROR 1066 (42000): Not unique table/alias: 'score'
# 指定别名
mysql> select * from score s1, score s2;

5.2.显⽰所有"MySQL"成绩⽐"JAVA"成绩⾼的成绩信息

• 首先分为两步进行，先要找到课程的id，java是1，MySQL是3

• 确定连接条件
  两个表中的student_id 必须相等

• 确定符合题目的要求
  要么sc1表中的course_id =1 且 sc2表中的course_id = 3
  要么sc1表中的course_id =3 且 sc2表中的course_id = 1

select  * from score sc1,score sc2 where sc1.student_id=sc2.student_id and sc1.course_id=1 and  sc2.course_id=3 ;

• 加入结果集的过滤条件，分数比较

select  * from score sc1,score sc2 where sc1.student_id=sc2.student_id and sc1.course_id=1 and  sc2.course_id=3 and  sc1.score<sc2.score;

精减查询结果，用别名表示表头

5.3 表连接练习

5.3.1.显⽰所有"MySQL"成绩⽐"JAVA"成绩⾼的学⽣信息和班级以及成绩信息