前言

上次說到了MySQL的查詢進階三和四，這次繼續討論查詢的相關的知識
鏈接: https://pan.baidu.com/s/1a_wstxPVhxeliZkt6-BTiw
提取碼: gaj8

進階五：分組查詢

語法

select 分組函式，列(要求出現在group by的后面)
from 表
【where 篩選條件】
group by 分組的串列
【oder by 子句】
注意：查詢串列必須特殊，要求是分組函式和group by后出現的欄位

特點

1.分組查詢中的篩選分為兩類
分組前篩選   位置：group by 子句的前面  關鍵字：where
分組后篩選   位置：group by 子句的后面  關鍵字：having
能用分組前篩選的優先考慮分組前篩選
group by 既支持多個欄位分組也支持單個欄位的分組（多個欄位的分組用逗號隔開且沒有順序的要求）
group by 也支持函式和運算式（用的相對上面的比較的少）
group by 也可以添加排序（放在整個的分組查詢的最后的）

1.簡單的分組查詢

#案例1、查詢每個工種的最高工資 
SELECT MAX(salary),job_id
FROM `employees`
GROUP BY job_id;
#案例2、查詢每個位置的部門個數
SELECT COUNT(1),`location_id`
FROM `departments`
GROUP BY `location_id`;
#案例3、查詢郵箱中包含a字符的，每個部門的平均工資
SELECT AVG(salary),`department_id`
FROM `employees`
WHERE `email` LIKE '%a%'
GROUP BY `department_id`;
#案例4、查詢有獎金的每個領導手下的最高工資
SELECT MAX(salary),`manager_id`
FROM `employees`
WHERE `commission_pct` IS NOT NULL
GROUP BY `manager_id`;

2.添加分組后的篩選

#案例5、查詢哪個部門的員工個數大于2
SELECT COUNT(*),`department_id`
FROM `employees`
GROUP BY `department_id`
HAVING COUNT(*)>2;
#案例6、查詢每個工種有獎金的的員工的最高工資大于12000的工種編號和最高工資
SELECT MAX(salary),job_id
FROM `employees`
WHERE `commission_pct` IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;
#案例7、查詢領導編號大于102的每個領導手下的最低工資大于5000的領導編號是哪個，以及其最低工資
SELECT MIN(salary),`manager_id`
FROM `employees`
WHERE `manager_id` > 102
GROUP BY `manager_id`
HAVING MIN(salary)>5000;

1. 按運算式或函式分組

#案例8、按姓名的長度分組，查詢每一組的員工個數，篩選員工個數大于5的有那些
SELECT COUNT(*),LENGTH(last_name)
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5;

2. 按多個欄位分組

#查詢每個部門每個工種的員工的平均工資
SELECT AVG(salary),`department_id`,`job_id`
FROM `employees`
GROUP BY `department_id`,`job_id`;

進階六 連接查詢

概念

又稱多表查詢，當查詢的欄位來自于多個表時，就會用到多表查詢
笛卡爾乘積現象：表一有m行，表二有n行，結果就是：m*n行
發生的原因：沒有有效的連接條件
如何避免：添加有限的連接條件

分類

按年代分類：
sql92標準：僅僅支持內連接
sq199標準：【推薦】支持內連接+外連接（左外和右外）+交叉連接
按功能分類：
	內連接：
		等值連接
		非等值連接
		自連接
	外連接：
		左外連接
		右外連接
		全連接
	交叉連接

SELECT * FROM `beauty`;

SELECT * FROM `boys`;

SELECT `name`,`boyName` FROM `beauty`,`boys`
WHERE `beauty`.`boyfriend_id`=`boys`.`id`;

一、sq192標準

1.等值連接

1.多表等值連接的結果為多表的交集部分
2.n表連接至少需要n-1個連接條件
3.多表的順序沒有要求
4.一般需要為表起別名
5.可以搭配前面的所有子句進行使用，；例如分組，排序，篩選等等

#案例、查詢對應的員工名和部門名
SELECT `last_name`,`department_name`
FROM `departments`,`employees`
WHERE `employees`.`department_id`=`departments`.`department_id`;

1.為表起別名

/*
好處：
	①提高陳述句的簡潔度
	②區分多個重名的欄位
注意：如果取了表名的別名就不能用原來的表名去限定了，只能用別名了
*/
#案例、查詢員工名，工種號，工種名
SELECT e.`last_name`,e.`job_id`,j.`job_title`
FROM `employees` e,`jobs` j
WHERE e.`job_id`=j.`job_id`;

2.表的順序是否可以調換（可以調換）

#案例、查詢員工名，工種號，工種名
SELECT e.`last_name`,e.`job_id`,j.`job_title`
FROM `jobs` j,`employees` e
WHERE e.`job_id`=j.`job_id`;

3.可以加篩選

#案例、查詢有獎金的員工名和部門名
SELECT e.`last_name`,d.`department_name`,e.`commission_pct`
FROM `employees` e,`departments` d
WHERE e.`department_id`=d.`department_id`
AND e.`commission_pct` IS NOT NULL;
#查詢城市中第二個字符為o的城市名和部門名
SELECT l.`city`,d.`department_name`
FROM `departments` d,`locations` l
WHERE d.`location_id`=l.`location_id`
AND l.`city` LIKE '_o%';

4.可以加分組

#查詢每個城市的部門個數
SELECT COUNT(*) 個數,l.`city`
FROM `departments` d,`locations` l
WHERE d.`location_id`=l.`location_id`
GROUP BY l.city
#案例、查詢有獎金的每個部門的部門名和部門領導的編號和該部門的最低工資
SELECT `department_name`,d.`manager_id`,MIN(`salary`)
FROM `departments` AS d,`employees` e
WHERE d.`department_id`=e.`department_id`
AND e.`commission_pct` IS NOT NULL
GROUP BY d.`department_name`,d.`manager_id`;

5.可以加排序

#案例、查詢每個工種的工種名和員工的個數，并且按員工個數降序  
SELECT `job_title`,COUNT(*)
FROM `employees` e,`jobs` j
WHERE e.`job_id`=j.`job_id`
GROUP BY j.`job_id`
ORDER BY COUNT(*) DESC;

6.可以實作三表連接

#查詢員工名，部門名和所在的城市
SELECT `last_name`,`department_name`,`city`
FROM `employees` e,`departments` d,`locations` l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`;

2.非等值連接

SELECT * FROM `job_grades`;
#查詢員工的工資和工資級別
SELECT salary,grade_level
FROM `employees` e,`job_grades` j
WHERE salary BETWEEN j.`lowest_sal` AND j.`highest_sal`;

3.自連接

#查詢員工的名和上級的名稱
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees AS e,employees AS m
WHERE e.manager_id=m.employee_id;

二、sq199標準

語法：

select 查詢串列
from 表1 別名 【連接型別】
join 表2 別名
on 連接條件
【where 篩選條件】
【...】

分類：

內連接（?）：inner
外連接：
	左外（*）：left 【outer】
	右外（*）：right【outer】
	全外：full【outer】
交叉連接：cross

相關概念

#內連接
/*
語法：
select 查詢串列
from 表1 別名 【連接型別】
join 表2 別名
on 連接條件
【where 篩選條件】
【…】
分類：

內連接：
		等值連接
		非等值連接
		自連接

特點：

1.可以添加分組，排序，分組等
2.inner可以省略
3.篩選條件放在where的后面，連接條件放在on的后面，提高分離性，便于閱讀
4.inner join連接和sql92的等值連接的效果是一樣的，都是查詢多表的交集

*/

1.等值連接

#查詢員工名，部門名
SELECT `last_name`,`department_name`
FROM `employees` e
INNER JOIN `departments` d
ON e.`department_id`=d.`department_id`;
#查詢名字中包含e的員工名和工種名
SELECT `last_name`,`job_title`
FROM `employees` e
INNER JOIN `jobs` j
ON e.`job_id`=j.`job_id`
WHERE `last_name` LIKE '%e%';
#查詢部門個數大于3的城市名和部門個數
SELECT `city`,COUNT(*) 部門個數
FROM `departments` d
INNER JOIN `locations` l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING 部門個數>3;
#查詢哪個部門的員工的個數>3的部門名和員工個數并按個數降序
SELECT `department_name`,COUNT(*)
FROM `departments` d
INNER JOIN `employees` e
ON d.`department_id`=e.`department_id`
GROUP BY `department_name`
HAVING COUNT(*)>3
ORDER BY  COUNT(*) DESC;
#查詢員工名，部門名，工種名，并按部門名降序
SELECT `last_name`,`department_name`,`job_title`
FROM `employees` e
INNER JOIN `departments` d
ON e.`department_id`=d.`department_id`
INNER JOIN `jobs` j
ON e.`job_id`=j.`job_id`
ORDER BY `department_name` DESC;

2、非等值連接

#查詢員工的工資等級
SELECT `salary`,`grade_level`
FROM `employees` e
INNER JOIN `job_grades` j
ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;

3、自連接

#查詢員工的名字，上級的名字
SELECT n.`last_name`,m.`last_name`
FROM `employees` n
INNER JOIN `employees` m
ON n.`manager_id`=m.`employee_id`;
#查詢員工的名字（含k的），上級的名字
SELECT n.`last_name`,m.`last_name`
FROM `employees` n
INNER JOIN `employees` m
ON n.`manager_id`=m.`employee_id`
WHERE n.`last_name` LIKE '%k%';

4.外連接

/* 應用場景：用于查詢一個表中有，一個表中沒有的記錄 特點：
1.外連接的查詢結果為主表中的所有記錄（意思就是想要查詢的哪個表就是主表） 如果從表中有和他匹配的則顯示和他匹配的值 如果從表中沒有和他匹配的則顯示的是null 外連接查詢的結果=內連接結果+主表中有而從表中沒有的記錄
2.左外連接：left join 左邊是主表 右外連接：right join 右邊的是主表
3.左外和右外交換兩個表的順序可以實作同樣的效果
4.全外連接=內連接的結果+表1中有但表2中沒有的+表2中有但表1中沒有的
*/

1.左外

#查詢男朋友不在男神表的女神名(左外連接)
SELECT b.`name`
FROM `beauty` b
LEFT JOIN `boys` bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.id IS NULL;

2.右外

#查詢男朋友不在男神表的女神名(右外連接)
SELECT b.`name`
FROM `boys` bo
RIGHT JOIN `beauty` b
ON b.`boyfriend_id`=bo.`id`
WHERE bo.id IS NULL;
#案例、查詢哪個部門沒有員工
SELECT d.*
FROM `departments` d
LEFT OUTER JOIN `employees` e
ON d.`department_id`=e.`department_id`
WHERE e.`employee_id` IS NULL;

5.全外連接

SELECT bo.*,b.*
FROM `beauty` b
FULL OUTER JOIN `boys` bo
ON b.`boyfriend_id`=bo.id;
交叉連接（就是相當于是笛卡爾乘積）
SELECT b.*,bo.*
FROM `beauty` b
CROSS JOIN `boys` bo;