MySQL表的增刪改查（進階）下

4.查詢

4.1聚合查詢

4.1.1聚合函式

在MySQL中常見的統計可以使用聚合函式來實現，常見的聚合函式有：

函式	說明
count[dintinct] expr	返回查詢到的數量（行數）
sum[dintinct] expr	返回查詢到的資料的總和
avg[dintinct] expr	返回查詢到的資料的平均值
max[dintinct] expr	返回查詢到的資料的最大值
min[dintinct] expr	返回查詢到的資料的最小值

案例：
在之前的exam_result表中返回 > 70 分以上的數學最低分。

SELECT MIN(math) FROM exam_result WHERE math > 70;

注意：
這裡的最小值是指在math>70的前提下的最小值，意思是在執行聚合函式取最小值之前先進行where條件篩選，在篩選結果中取最小值。

4.1.2 group by 子句

在select 查詢中使用group by子句可以對指定的列進行分組查詢。

準備測試案例：
準備測試表及資料：職員表，有id（主鍵）、name（姓名）、role（角色）、salary（薪水）

create table emp(
 id int primary key auto_increment,
 name varchar(20) not null,
 role varchar(20) not null,
 salary numeric(11,2)
);
-- 插入資料
insert into emp(name, role, salary) values
('馬雲','服務員', 1000.20),
('馬化騰','遊戲陪玩', 2000.99),
('孫悟空','遊戲角色', 999.11),
('豬無能','遊戲角色', 333.5),
('沙和尚','遊戲角色', 700.33),
('隔壁老王','董事長', 12000.66);

簡單演示一下什麼是分組，插入上面的資料之後執行下列語句：
查詢每個角色的最高工資、最低工資和平均工資

select     count(name),role,max(salary),min(salary),avg(salary) from emp group by role;

group by以role分為4組，在執行求和，平均值等一些聚合函式時是在求每個組裡面的總和，平均值等。

注意：

如上面的查詢語句，select指定的欄位（要顯示的列）必須是group by後面的分組依據欄位（這裡是role）若想要顯示其他欄位則必須包含在聚合函式中。

4.1.3 having語句

GROUP BY 子句進行分組以後，需要對分組結果再進行條件過濾時，不能使用 WHERE 語句，而需要用HAVING。
即where語句是在分組之前進行條件過濾（資料篩選），而要想對分組之後的資料再次進行條件過濾則需要使用having語句。
例如：
顯示平均工資低於1500的角色和它的平均工資。

select role,max(salary),min(salary),avg(salary) from emp group by role having avg(salary)<1500;

這裡將分組之後平均工資低於1500的資料過濾掉了。

4.2.聯合查詢(重點)

實際開發中往往資料來自不同的表，所以需要多表聯合查詢。多表查詢是對多張表的資料取笛卡爾積：

例如上圖對 R表和S表取笛卡爾積得到表T，即對兩張表進行排列組合得到的集合。
注意：
關聯查詢允許對錶取別名。
測試資料準備：

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, 9, 1),(98.5, 9, 3),(33, 9, 2),
-- 菩提老祖
(60, 10, 1),(59.5,10, 3),
-- 白素貞
(33, 11, 1),(68, 11, 3),(99, 11, 2),
-- 許仙
(67, 12, 1),(23, 12, 3),(56, 12, 2),
-- 不想畢業
(81, 13, 1),(37, 13, 3),
-- 好好說話
(56, 14, 2),(43, 14, 3),(79, 14, 1),
-- tellme
(80, 15, 2),(92, 15, 3);

4.2.1 內連線

語法：
select 欄位 from 表1 別名1 [inner] join 表2 別名2 on 連線條件 and 其他條件;
select 欄位 from 表1 別名1,表2 別名2 where 連線條件 and 其他條件;
案例：
查詢“許仙”同學的 成績

select
stu.name,   -- 學生表中的name欄位 
sco.score   -- 成績表的成績列
 from 
 student stu  -- 表1 學生表的別名stu
 inner join  score sco -- 表2成績表 別名為sco
  on stu.id=sco.student_id  and stu.name='許仙'; 

連線條件：inner join成績表中的id欄位=成績表中的student_id欄位，and學生的名字為許仙

查詢所有同學的成績，及同學的個人資訊：
學生表、成績表、課程表3張表關聯查詢

select
-- 這裡因為是不同的表中的欄位所以表示欄位時需要用表名.欄位
 stu.id,
 stu.sn,
 stu.name,
 stu.qq_mail,
 sco.score,
 sco.course_id,
cou.name
from
 student stu
 join score sco on stu.id = sco.student_id
 join course cou on sco.course_id = cou.id
order by
stu.id;

4.2.2 外連線

外連線又分為左連線和右連線。如果聯合查詢，左側的表完全顯示我們叫左連線，右側的表完全顯示我們叫右連線。

語法：
左外連線，表1完全顯示
select 欄位名 from 表名1 left join 表名2 on 連線條件;
右外連線，表2完全顯示
select 欄位 from 表名1 right join 表名2 on 連線條件;

案例：查詢所有同學的成績，及同學的個人資訊，如果該同學沒有成績，也需要顯示。
學生表、成績表、課程表3張表關聯查詢

SELECT
 stu.id,
 stu.sn,
 stu.NAME,
 stu.qq_mail,
 sco.score,
 sco.course_id,
 cou.NAME
FROM
 student stu
 LEFT JOIN score sco ON stu.id = sco.student_id
 LEFT JOIN course cou ON sco.course_id = cou.id
ORDER BY
 stu.id

4.2.3 自連線

自連線是指在同一張表連線自身 進行查詢。
案例：
顯示所有“計算機原理”成績比“Java”成績高的成績資訊

SELECT
 stu.*,
 s1.score Java,
 s2.score 計算機原理
FROM
 score s1
 JOIN score s2 ON s1.student_id = s2.student_id
 JOIN student stu ON s1.student_id = stu.id
 JOIN course c1 ON s1.course_id = c1.id
 JOIN course c2 ON s2.course_id = c2.id
 AND s1.score < s2.score
 AND c1.NAME = 'Java'
 AND c2.NAME = '計算機原理';

4.2.4 子查詢

子查詢是指嵌入在其他sql語句中的select語句，也叫巢狀查詢。
案例：
查詢與“不想畢業” 同學的同班同學：
select * from student where classes_id=(select classes_id from student where name=‘不想畢業’);

4.2.5 合併查詢

在實際應用中，為了合併多個select的執行結果，可以使用集合操作符 union，union all。使用UNION和UNION ALL時，前後查詢的結果集中，欄位需要一致。

• union

該操作符用於取得兩個結果集的並集。當使用該操作符時，會自動去掉結果集中的重複行。
案例：
查詢id小於3，或者名字為“英文”的課程：

 select * from course where id<3
union
select * from course where name='英文';

• union all

該操作符用於取得兩個結果集的並集。當使用該操作符時，不會去掉結果集中的重複行。
案例：
查詢id小於3，或者名字為“Java”的課程。

 select * from course where id<3
union all
select * from course where name='英文';