【MySQL】(8) 聯合查詢

一、聯合查詢的作用

? ? ? ? 由于范式的規則，數據分到多個表中，想要查詢完整的信息，就需要聯合查詢多張表。比如查詢學生的學生信息和所在班級的信息，就需要聯合查詢學生表和班級表。

二、聯合查詢過程

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

 # 課程表
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);

1、確定聯合查詢的表

? ? ? ? 學生表 student 和班級表 class，進行笛卡爾積：

2、過濾掉班級號不匹配的信息

? ? ? ? 學生表和班級表都有 id，需要指定是哪個表的 id。

3、篩選出指定學生

4、篩選出想要的字段，并精簡表名

三、內連接

? ? ? ? 上面的示例就是內連接。?

1、語法

select 字段 from 表1 別名1, 表2 別名2 where 連接條件 and 其他條件;select 字段 from 表1 別名1 [inner] join 表2 別名2 on 連接條件 where 其他條件;

2、示例1：查詢“唐三藏”同學的成績。

? ? ? ? 查詢的表：student、score。

? ? ? ? 確定連接條件需要查看表結構：s.id = sc.student_id。

? ? ? ?其他條件：s.name = '唐三藏'。

? ? ? ?字段：s.name、sc.score。

3、示例2：查詢所有同學的總成績、個人信息。

? ? ? ? 查詢的表：student、score。

? ? ? ? 連接條件：s.id = sc.student_id。

? ? ? ? 分組查詢：sc.student_id，① 唯一。② 對哪個表的列求和，就對那個表的列進行分組。

? ? ? ? 查詢字段：s.id、s.sno、s.name、s.age、s.gender、s.enroll_date、sc.s（分組后，這些字段的值對于某一組都是確定的，可以在分組后查詢）、avg(sc.score)（聚合函數的值也是確定的）。

? ? ? ? 查詢結果：

4、示例3：查詢所有同學的個人信息、每門課的成績

? ? ? ? 查詢表：student、score、course。

? ? ? ? 連接條件：

? ? ? ? 查詢結果：

? ? ? ? 也可以使用：

????????工作中盡量少對大表關聯查詢，最多關聯 3 個表，因為很耗資源。

四、外連接

左外連接：以左表為基準，顯示左表所有值，顯示右表匹配的值，沒有匹配的就顯示 NULL。
右外連接：跟左外連接反著來。
全連接：左、右都全顯示，沒有匹配的就顯示 NULL。（MySQL 不支持）

????????內連接是有匹配的才顯示，外連接沒有匹配的也會顯示 NULL。

1、語法

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

2、示例1：查詢沒有參加考試的同學信息

? ? ? ? 該查詢就無法使用內連接完成。

? ? ? ? 查詢的表：student、score。

? ? ? ? 連接條件：s.id = sc.student_id。

? ? ? ? 沒有參加考試的同學：同學信息要全顯示，沒有匹配的成績顯示 NULL。

????????其它條件：sc.score is?null。（注意不要用＝，null 與任意值做計算都是 null）

3、示例2：查詢沒有學生的班級

五、自連接

? ? ? ? 自連接是自己跟自己做笛卡爾積。上面的連接都是列與列的比較，有時候我們想要同一列中行與行的比較，但MySQL 又不支持直接比較行與行。就需要用自連接，把行轉為列。

? ? ? ? 比如我們想要比較同一個學生的 score 字段的不同課程成績：

1、示例1：顯示所有 'Java' 比 'MySQL' 分數低的學生和成績信息

? ? ? ? 查詢的表：score sc1、score sc2、student s。

? ? ? ? 連接條件：sc1.student_id = sc2.student_id。同一個學生的成績進行比較。（自連接）

? ? ? ? ? ? ? ? ? ? ? ? ? sc1.student_id = student.id。

? ? ? ? 其它條件：sc1.class_id = Java 的編號，sc2.class_id = MySQL 的編號。（選出對應的課程）。sc1.score <?sc2.score。

2、示例2：通過課程名實現示例1

? ? ? ? 有時候希望實現用戶輸入 2 個課程名稱，就能直接查詢的功能。所以先把 score 表與 class 表關聯后，在對課程名進行篩選。

????????查詢的表：score sc1、class c1、score sc2、class c2。

? ? ? ? 連接條件：sc1.class_id = c1.id（代表課程1）

? ? ? ? ? ? ? ? ? ? ? ? ? sc2.class_id = c2.id（代表課程2）

? ? ? ? ? ? ? ? ? ? ? ? ??sc1.student_id = sc2.student_id（代表同一個學生）

? ? ? ? 其他條件：c1.name = 'Java', c2.name = 'MySQL'

? ? ? ? ? ? ? ? ? ? ? ? ??sc1.score <?sc2.score

六、子查詢

? ? ? ? 子查詢就是一個 select 語句的結果是另一個 select 語句的條件，可以嵌套多層，也叫嵌套查詢。子查詢只是把多條的查詢語句合并成一條查詢語句，在工作中不能嵌套太多，影響查詢效率。

1、語法

????????in 表示內層的 select 返回的是一個結果集。

2、單行子查詢。

? ? ? ? 內層只返回一行數據。

????????示例：查詢 '不想畢業' 的同班同學

? ? ? ? 先要查詢 '不想畢業' 是哪個班的（內層返回1行），然后根據班級 id 查詢同班同學（外層）。最后排除 '不想畢業' 的信息。

3、多行子查詢

? ? ? ? 內層返回多行數據。

? ? ? ? 示例：查詢 'Java' 或 'MySQL' 的成績信息。

? ? ? ? 先查詢 'Java' 或 'MySQL' 的課程 id（返回2行），然后根據課程 id 查詢成績信息。

4、多列子查詢

? ? ? ? 內層返回多列數據，外層條件與內層查詢的列要匹配。

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

? ? ? ? 首先查詢重復的成績信息（分數、學生 id、課程 id，相同的分為一組，統計每組數據行數，計數大于1的則重復），再根據內層的查詢結果在外層查詢完整的成績信息（成績 id，分數、學生 id、課程 id）。

5、在 from 子句中使用子查詢

? ? ? ? from 后面接查詢的表，這個表可以是真實表或者臨時表，而子查詢的結果就是存儲在臨時表中。

? ? ? ? 示例：查詢大于 'Java001' 班平均分的成績信息。

? ? ? ? 先查找班級為 'Java001班' 的學生成績，即將 class、score、student 關聯，選出 class.name = 'Java001班' 的數據行，再求平均值，平均值存儲在臨時表中。

? ? ? ? 再將平均值臨時表與 score 表進行內連接，最后使用其它條件 score > avg。

七、合并查詢

? ? ? ? 想讓多個 select 查詢結果集一起返回，就用合并查詢。

1、union（結果集去重）

? ? ? ? 準備一個與 student 結構一致的 student2?表：

? ? ? ? 插入數據：

insert into student2 (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);

? ? ? ? student 表數據：

? ? ? ??