📕摘要

前面我們學習了數據庫設計時要滿足三大范式，也就意味著數據會被拆分到許多張表中，當我們想查詢一個學生的基本信息與成績時，此時就會涉及到學生表，班級表，成績表等多張數據表，但我們給用戶展示信息時并不會把冗余的數據也展示給用戶，所以我們就需要用到聯合查詢從多張表中查詢出有用的數據。此時的‘聯合’，就是指多張數據表的組合。

📕1. 多表聯合查詢時MySQL內部原理

當我們進行多表聯合查詢時，MySQL內部會進行以下操作：

1. 參與查詢的所有表取笛卡爾積，結果集在臨時表中
   
2. 觀察哪些記錄是有效數據，根據兩個表的關聯關系過濾掉無效數據
   
   =======================================================================
   首先我們要構造一個練習數據

create database if not exists test; -- 創建庫use test;-- 課程表
create table if not exists course(id bigint primary key auto_increment,`name` varchar(20) not null
);insert into course (name) values ('Java'), ('C++'), ('MySQL'), ('操作系統'), ('計
算機網絡'), ('數據結構');-- 學生表
create table if not exists student(id bigint primary key auto_increment,`name` varchar(20),sno varchar(20),age bigint,gender bigint,enroll_date varchar(20),class_id bigint
);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);-- 班級表
create table if not exists class(id bigint primary key auto_increment,`name` varchar(20)
);insert into class(name) values ('Java001班'), ('C++001班'), ('前端001班');-- 分數表
create table if not exists score(id bigint primary key auto_increment,score bigint,student_id bigint,course_id bigint
);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);

Navicat可視化圖：

1. 班級表
   
2. 課程表
   
3. 分數表
   
4. 學生表
   

??1.1 實例：一個完整的聯合查詢過程

查詢學生姓名為孫悟空的詳細信息，包括學生個人信息和班級信息

1. 首先確定參與查詢的表，分別是student表與class表

select * from student,class;

2. 確定連接條件，條件為student表中的class_id要與class表中的id相等

select * from student,class where student.class_id = class.id;

3. 加入查詢條件

select * from student,class where student.class_id = class.id and student.`name` = '孫悟空';

4. 精減查詢結果字段

selectstudent.id,student.name,class.name
from student,class 
wherestudent.class_id = class.id 
and student.`name` = '孫悟空';

5. 可以為表名指定別名

selectstu.id,stu.name,c.name
from student as stu,class as c
wherestu.class_id =c.id 
and stu.`name` = '孫悟空';

📕2. 內連接

select * from 表名1 as 別名1 , 表名2 as 別名2 where 連接條件 and 其他條件;

1. 查詢"唐三藏"同學的成績

-- 查詢唐三藏同學的成績
selectstudent.`name`,score.score,course.`name` fromstudent,score,course wherestudent.id = score.student_id andscore.course_id = course.id andstudent.`name` = '唐三藏';

2. 查詢所有同學的總成績，及同學的個人信息

  select student.`name`,sum(score.score) as '總分'from student,scorewherestudent.id = score.student_idgroup by `name`;

3. 查詢所有同學每門課的成績，及同學的個人信息

selectstudent.`name`,score.score,course.`name`fromstudent,score,course where student.id = score.student_id and score.course_id = course.id;

📕3. 外連接

外連接分為左外連接、右外連接和全外連接三種類型，因為MySQL不支持全外連接，所以本文不再介紹外連接部分。
? 左外連接：返回左表的所有記錄和右表中匹配的記錄。如果右表中沒有匹配的記錄，則結果集中對
應字段會顯示為NULL。
? 右外連接：與左外連接相反，返回右表的所有記錄和左表中匹配的記錄。如果左表中沒有匹配的記
錄，則結果集中對應字段會顯示為NULL。

-- 左外連接，表1完全顯?
select 字段名 from 表名1 left join 表名2 on 連接條件;
-- 右外連接，表2完全顯?
select 字段 from 表名1 right join 表名2 on 連接條件;

1. 查詢沒有參加考試的同學信息

select * from student left join score on student.id = score.student_id where score.score is null;

2. 查詢沒有學生的班級

select * from student right join class on class.id = student.class_id where student.id is null;

📕4. 自連接

自連接是自己與自己取笛卡爾積，可以把行轉化成列，在查詢的時候可以使用where條件對結果進行過濾，以至于實現行與行之間的比較，在做自連接時要為表起別名（否則報錯）。

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

1. 顯示所有"MySQL"成績比"JAVA"成績高的成績信息

select s1.student_id as '學生',s1.score as 'MySQL',s2.score as 'JAVA' from (select * from score where  course_id = 3) as s1 ,(select * from score where course_id = 1 ) as s2 where s1.student_id = s2.student_id and s1.score > s2.score;

思路：先查出JAVA的成績，在查出MYSQL的成績，兩張表分別各自包含JAVA和MYSQL成績，然后進行連接，連接條件為表一與表二學生id相同，限制條件為MYSQL成績大于JAVA成績

📕5. 子查詢

子查詢是把?個SELECT語句的結果當做別一個SELECT語句的條件，也叫嵌套查詢。

select * from table1 where condition [= |in]（select * from where (......)）

??5.1 單行子查詢

示例： 查詢與"不想畢業"同學的同班同學

select student.`name`,student.class_id from student  where class_id   = (select class_id from student where `name` = '不想畢業' ) and `name` != '不想畢業';

??5.2 多行子查詢

示例：查詢"MySQL"或"Java"課程的成績信息

    select * from score where course_id in (select course.id from course where `name` = 'Java' or `name` = 'MySQL');

使用 not in 可以查詢除了"MySQL"或"Java"課程的成績

??5.3 多列子查詢

單行子查詢和多行子查詢都只返回一列數據，多列子查詢中可以返回多個列的數據，外層查詢與嵌套的內層查詢的列要匹配

示例：查詢重復錄入的分數

select *  from score where (score,student_id,course_id) in (select score,student_id,course_id from score group by score,student_id,course_id having count(*)>1);

??5.4 在from子句中使用子查詢

當?個查詢產生結果時，MySQL自動創建一個臨時表，然后把結果集放在這個臨時表中，最終返回
給用戶，在from子句中也可以使用臨時表進行子查詢或表連接操作

示例：查詢所有比"Java001班"平均分高的成績信息

select * from score as s ,(select avg(score) as avg_score from score where student_id in ( select student_id from student where class_id = 1))  as tmp where s.score > tmp.avg_score;

📕6. 合并查詢

為了合并多個select操作返回的結果，可以使?集合操作符 union，union all

-- 創建?個新表并初始化數據create table student1 like student;insert into student1 (name, sno, age, gender, enroll_date, class_id) values
('唐三藏', '100001', 18, 1, '1986-09-01', 1),
('劉備', '300001', 18, 1, '1993-09-01', 3),
('張?', '300002', 18, 1, '1993-09-01', 3),
('關?', '300003', 18, 1, '1993-09-01', 3);

??6.1 union

該操作符用于取得兩個結果集的并集。當使用該操作符時，會自動去掉結果集中的重復行。

示例：查詢student表中 id < 3 的同學和student1表中的所有同學

select * from student where id<3 union select * from student1;

??6.2 union all

該操作符?于取得兩個結果集的并集。當使用該操作符時，不會去掉結果集中的重復行。

示例：查詢student表中 id < 3 的同學和student1表中的所有同學

select * from student where id<3 union all select * from student1;

📕7. 插入查詢結果

insert into 表名(列名1，列名2) select .....

示例：將student表中C++001班的學生復制到student1表中

insert into student1 (name, sno, age, gender, enroll_date, class_id)
select s.name, s.sno, s.age, s.gender, s.enroll_date, s.class_id
from student s, class c where s.class_id = c.id and c.name = 'C++001班';