【MySQL — 数据库基础】深入解析 MySQL 的联合查询

---

1. 插入查询结果

---

语法

---

insert into table_name1 select* from table_name2 where restrictions ;

注意：查询的结果集合，列数 / 类型 / 顺序 要和 insert into 后面的表相匹配；列的名字不要求相同；

---

create table student1(id int , name varchar(20));create table student2(id int , name varchar(20));

---

要点讲解

---

1. 查询的结果集合，列数 / 类型 / 顺序要和 insert into 后面的表相匹配

---

insert into student1 values(1, '张三'), (2, '李四'), (100, '赵六');insert into student2 select* from student1 where id < 50;  -- 插入查询结果

---

2. 插入查询的表的列名，与插入的表列名不要求相同

---

drop table student2;create table student2(StudentId int , StudentName varchar(20));  -- 新创建的 student2 的列名和 student1 不同insert into student2 select* from student1 where id < 50;

---

3. 查询的结果集合，列数 / 类型 / 顺序和 insert into 后面的表不匹配，会报错

drop table student2;create table student2( StudentName varchar(20), StudentId int); -- 类型和 student1 不匹配insert into student2 select* from student1 where id < 50; 

---

4. 两个表的列类型不匹配，可以指定插入顺序，也可以指定查询顺序

insert into student2(StudentId, StudentName) select* from student1;  -- 指定插入顺序insert into student2 select name , id from student1;                 -- 指定查询顺序

---

2. 笛卡尔积

---

概念

---

笛卡尔积就像是把两个集合中的每一项都“配对”起来。

比如你有两个表，一个是人员名单，一个是产品清单：

• 人员名单：Alice 和 Bob
• 产品清单：Apple 和 Banana

如果你把每个人和每个产品都配对一次，就得到以下组合：

• Alice 和 Apple
• Alice 和 Banana
• Bob 和 Apple
• Bob 和 Banana

这就是笛卡尔积的结果。

---

简单来说，就是把一个表的每一行和另一个表的每一行都组合一下： 笛卡尔积的列数，就是刚才两个表的列数之和； 笛卡尔积的行数，就是两张表行数的乘积。

---

所谓的 “多表联合查询”，是基于笛卡尔积这样的运算展开的；但注意，笛卡尔积很容易产生大量不需要的数据，所以一般要避免在查询中直接用它，除非有特别的需要。

---

---

通过SQL计算笛卡尔积

---

create table class(classId int, className varchar(20)); insert into class values
(1, '一班'), 
(2, '2班');create table student(id int , name varchar(20) , classId int );insert into student values
(1, '张三', 1) , 
(2, '李四', 1) , 
(3, '王五', 2) , 
(4, '赵六', 2) ;select* from student, class;  -- 通过 SQL 计算笛卡尔积，将两张表综合在一起进行查询

---

---

要想进行一些更有实际意义的查询，就需要指定一些额外的条件：

---

在笛卡尔积查询 student 和 class 时，我们期望进行笛卡尔积的记录是 classId 相同的记录

select* from student , class  where classId = classId ;  

---

为了解决" classId 是哪张表的 classId " 这个歧义，我们需要显式指定 classId 是属于哪张表的

select* from student , class  where student.classId = class.classId ; -- 使用成员访问运算符. 来指定 classId 是属于哪张表的

---

3. 一次完整的联合查询过程

---

构造数据

drop table if exists classes ;
drop table if exists student ;
drop table if exists course ;
drop table if exists 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) , class_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, class_id) VALUES
('09982', '黑旋风李逵', 'xuanfengaqq.com', 1),
('00835', '菩提老祖', NULL, 1),
('00391', '白素贞', NULL, 1),
('00031', '许仙', '[e-mail]', 1),
('00054', '不想毕业', NULL, 1),
('51234', '好好说话', '[e-mail]', 2),
('83223', 'tellme', NULL, 2),
('09527', '老外学中文', '[e-mail]', 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);

---

测试数据主要包含三个实体：学生，班级，课程；

学生 - 班级 属于一对多的关系，学生 - 课程 属于多对多的关系，所以我们需要通过一个关联表 score ，来体现课程和学生两个实体的联系；

---

内连接

---

语法

---

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

---

---

案例

---

查询“许仙”同学的成绩

---

---

初学多表查询阶段，不建议一次写出最终的SQL语句，可以一步步的优化查询，根据规律写出最终SQL;

---

---

查询所有同学的总成绩及个人信息

---

select student.id , student.name , sum(score.score) 
from student, score 
where student.id = score.student_id 
group by student.id ;

---

查询所有同学的总成绩, 列出同学姓名，课程名字，课程分数....

---

select student.name as studentName , course.name as courseName , score.score 
from student , course , score 
where student.id = score.student_id and course.id = score.course_id ;

---

使用 join on 的方式查询，可以更好的体现出表两两之间的联合查询过程

select* from student 	
join score 
on student.id = score.student_id 
join course 
on score.course_id = course.id ;

select student.name as studentName , course.name as courseName , score .score 
from student 
join score on student.id = score.student_id 
join course on course.id = score.course_id ;  -- 精简查询

---

外连接

---

语法

---

外连接也是 join on 这样的写法，但是不支持 from 多个表 ；

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

如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。

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

---

案例

---

create table student( id int , name varchar(20) ) ;create table score( id int , score int ) ;insert into student values( 1 , '张三' ), ( 2 , '李四' ), ( 3 , '王五' );insert into score values(1 , 90) , (2 , 80) , (3 , 70) ;

创建的这张表的数据是一一对应的，进行内连接和外连接，得到的结果完全相同；

但是如果上述的数据不再一一对应，内连接的结果和外连接就会出现差别；

update score set id = 4 where score = 70 ;-- 修改数据

---

内连接

select name , score from student  ,   score where student.id = score.id;-- 这个写法只能表示内连接，不能表示外连接select name , score from student join score  on   student.id = score.id;select name , score from student inner join score  on   student.id = score.id;-- inner join 表示内连接，inner 关键字可以省略-- 内连接，查询结果只会包含两个表中同时具备的数据

---

外连接

select name , score from student left join score on student.id = score.id ;-- 左外连接select name , score from student right join score on student.id = score.id ;-- 右外连接

---

自连接

---

自连接是指在同一张表连接自身进行查询，这并不是常规操作，而是针对特殊的情况的处理；

---

案例

---

查询计算机组成原理分数高于 Java 的同学

---

---

select s1.student_id , s1.score , s2.score 
from score as s1, score as s2 
where s1.student_id = s2.student_id 
and s1.course_id = 3 
and s2.course_id = 1 
and s1.score > s2.score;

---

如果发现要查询的条件是针对两行，而不是两列，就可以考虑使用自连接进行转换； 自连接前要先清楚表的量级，如何表非常大，连接开销也会非常庞大，容易就把数据库搞死了.

---

子查询

---

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

---

单行子查询：返回一行记录的子查询

---

查询与“不想毕业”同学的同班同学:

---

多行子查询：返回多行记录的子查询

---

使用多行子查询，就不能使用= > <这样的运算符直接比较了，但是可以使用 in

---

查询“语文”或”“英文”课程的成绩信息：

---

合并查询

---

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符union，union all。

使用 UNION 和 UNION ALL 时，前后查询的结果集中，字段需要一致（要求合并双方的类型，个数，顺序要相同，列名不要求相同）。

---

union

---

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

---

案例:查询id小于3，或者名字为“英文”的课程:select* from course where id < 3 union select* from course where name ='英文' ;-- 将两条SQL语句的查询结果一次性合在一张表中select* from course where id < 3 or name = '英文' ;-- 针对同一张表的查询， union 和 or 的效果相同，但是如果是不同的表，就只能用 union，不能用 or

---

union all

---

该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。

-- 查询id小于3，或者名字为“Java”的课程select* from course where id < 3 union all select* from course where name = 'Java' ;

---

SQL查询中各个关键字的执行先后顺序

---

from > on> join > where > group by > with > having > select > distinct > order by > limit

---

---