第三天MySQL學習 ：分組函數、分組查詢、連接查詢（等值連接、非等值連接、自連接） ...
2020/5/6
分組函數：(分組函數用作統計使用，又稱聚合函數、統計函數或組函數)
#sum（求和）、avg（平均值）、max（最大值）、min（最小值）、count（計數）
特點：
1. 以上分組函數中都是可以忽略null值 （其中count本身就是計算非null值得個數）
2. sum和avg函數的參數一般只能處理數值型，而max、min以及count可針對任意類型的參數
SELECT SUM(salary) FROM employees;-> 691400.00
SELECT AVG(salary) FROM employees;-> 6461.682243
SELECT MAX(salary) FROM employees;-> 24000.00
SELECT MIN(salary) FROM employees;-> 2100.00
SELECT COUNT(salary) FROM employees;-> 107
#組合使用:
SELECT
SUM(salary) 和,
ROUND(AVG(salary),2) 平均, #嵌套使用round()函數，將值保留至小數點後面2位
MAX(salary) 最大值,
MIN(salary) 最小值,
COUNT(salary) 總數
FROM
employees;
關於分組函數忽略nul值，舉例：
SELECT
AVG(commission_pct),
SUM(commission_pct) / COUNT(commission_pct),
SUM(commission_pct) / COUNT(*)
FROM
employees;
這裡可以看出avg(commissom_pct)的值等於sum(commission_pct)/ count(commission_pct)(非空的總數)，而不是總體的個數(count(*))
#與DISTINCT（去重）關鍵字搭配使用
SELECT SUM(DISTINCT salary), SUM(salary) FROM employees;
去重之後，在統計工資之和
SELECT SUM(DISTINCT salary), SUM(salary) FROM employees;
統計工資的種類
#count函數詳細介紹
select count(*) from 表名; ->統計表的總行數
select count(1) from 表名; ->相當於在表中多了一列，這一列中根據表內的行數加了相應個數的1，統計1的個數，並返回
效率比較：
MYISAM存儲引擎下，count(*)的效率高
INNODB存儲引擎下，count(*)和count(1)的效率差不多，比count(欄位)(有個判斷欄位是否為null的過程)要高
註意：和分組函數一同查詢的欄位要求是group by 後的欄位
十六、分組查詢
語法：(group by 子句語法)
註意：查詢列表必須特殊，要求是分組函數或group by後出現的欄位
SELECT
分組函數，列（要求要出現在group by 之後）
FROM
表名
[WHERE
篩選條件]
GROUP BY
分組的列表
[ORDER BY
子句]
特點：
- 分組查詢中的篩選條件分為兩類
數據源 位置 關鍵字
分組前篩選 原始表 group by子句前 where
分組後篩選 分組後的結果 group by子句後 having
1.若分組函數做篩選條件則肯定放在having子句中
2.能用分組前篩選的，就優先考慮使用分組前篩選（考慮效率問題）
2. group by 子句中支持單個欄位分組，多個欄位分組（多個欄位用逗號隔開，沒有順序要求，還支持表達式和函數分組（用的較少））
3. 也可以添加排序（排序放在整個分組查詢語句的最後）
----------------------------------簡單分組查詢------------------------
#案例一：查詢每個部門的平均工資
SELECT
AVG(salary) 平均工資,
department_id
FROM
employees
GROUP BY
department_id;
#案例二：查詢每個工種的最高工資
SELECT
MAX(salary),
job_id
FROM
employees
GROUP BY
job_id;
#案例三：查詢每個位置上的部門個數
SELECT
COUNT(*),
location_id FROM
departments
GROUP BY
location_id;
-----------------------------添加篩選條件的分組查詢-------------------
1.分組前篩選
#案例1：查詢郵箱中包含a字元的，每個部門的平均工資
SELECT
AVG(salary) 平均工資,
department_id 部門編號
FROM
employees
WHERE
email LIKE '%a%'
GROUP BY
department_id;
#案例2：查詢有獎金的每個領導手下員工的最高工資
SELECT
MAX(salary) 最高工資,
manager_id 領導編號
FROM
employees
WHERE
commission_pct IS NOT NULL
GROUP BY
manager_id;
2.分組後篩選
#案例1：查詢哪個部門的員工個數>2
SELECT
count(*) 員工個數,
department_id 部門編號
FROM
employees
GROUP BY
department_id
HAVING #根據GROUP by 執行後的結果再篩選
count(*) > 2;
SELECT
count(*) 員工個數,
department_id 部門編號
FROM
employees
GROUP BY
department_id
HAVING
員工個數 > 2;#可使用別名
#案例2：查詢每個工種有獎金的員工的最高工資>12000
SELECT
MAX(salary) 最高工資,
job_id 工種編號
FROM
employees
WHERE
commission_pct IS NOT NULL
GROUP BY
job_id
HAVING
MAX(salary) > 12000;
-------------------------------------------------
SELECT
MAX(salary) 最高工資,
job_id 工種編號
FROM
employees
WHERE
commission_pct IS NOT NULL
GROUP BY
工種編號
HAVING
MAX(salary) > 12000;
註意：ORDER BY以及GROUP BY子句後都可以使用別名，註意！！！WHERE子句後不可以！！！
#案例3：查詢領導編號>102的每個領導手下的最低工資>5000的領導編號是哪個，以及其最低工資
SELECT
MIN(salary) 最低工資,
manager_id 領導編號
FROM
employees
WHERE
manager_id > 102
GROUP BY
manager_id
HAVING
MIN(salary) > 5000;
對比分組前篩選與分組後篩選：
數據源 位置 關鍵字
分組前篩選 原始表 group by子句前 where
分組後篩選 分組後的結果 group by子句後 having
註意：
- 若分組函數做篩選條件則肯定放在having子句中
- 能用分組前篩選的，就優先考慮使用分組前篩選（考慮效率問題）
---------------------按表達式或函數分組查詢(用的較少)--------------------
#案例：按員工姓名的長度分組，查詢每一組的員工個數，篩選員工個數>5的有哪些
SELECT
COUNT(*) 員工個數,
LENGTH(last_name) len_name
FROM
employees
GROUP BY
LENGTH(last_name)
HAVING
COUNT(*) > 5;
-----------------------------多個欄位的分組查詢----------------------------
#案例：每個部門每個工種的平均工資
SELECT
AVG(salary) 平均工資,
department_id,
job_id
FROM
employees
GROUP BY #department_id與job_id一致的分為一個小組（與順序無關）
department_id,
job_id;
----------------------------添加排序條件的分組查詢-------------------------
#案例：每個部門每個工種的獎金存在的並且平均工資大於1000的平均工資，並且按平均工資的高低顯示
SELECT
AVG(salary) 平均工資,
department_id,
job_id
FROM
employees
WHERE
department_id IS NOT NULL
GROUP BY #department_id與job_id一致的分為一個小組（與順序無關）
department_id,
job_id
HAVING
AVG(salary)>10000
ORDER BY
AVG(salary) DESC;
十七、連接查詢
含義：又稱多表查詢，當查詢的欄位來自於多個表時，就會用到
笛卡爾乘積現象：表1 有m行，表2 有n行，結果=m*n行
發生原因：沒有有效的連接條件
如何避免：添加上有效的連接條件
連接查詢分類：
按年代分類：
sq92標準：僅僅支持內連接（對MySQL而言）
sq99標準（推薦）：支持內連接+外連接（左外、右外）+交叉連接
按功能分類：
內連接：
等值連接
非等值連接
自連接
外連接：
左外連接
右外連接
全外連接
交叉連接
（sq92標準）
#等值連接
特點：
- 多表連接的結果為多表的交集部門
- n表連接，至少需要n-1個連接條件
- 多表的順序沒有要求
- 一般需要為表取別名
- 可以搭配前面介紹的所有子句
#案例1：查詢女神名和對應的男神名
SELECT
NAME,
boyname
FROM
beauty,
boys
WHERE
beauty.boyfriend_id = boys.id; #在兩個表之間添加了一個連接的條件
#案例2：查詢員工名和對應的部門名
SELECT
last_name,
department_name
FROM
employees,
departments
WHERE
employees.department_id = departments.department_id;
#案例3：查詢員工名、工種號、工種名
SELECT
last_name,
employees.job_id, #要用表名去限定，否則識別不出來是哪個表中的job_id
job_title
FROM
employees,
jobs #兩個表的順序可調換
WHERE
employees.job_id = jobs.job_id;
------------為表取別名----------------
- 提高語句的簡潔度
- 區分多個重名的欄位（限定欄位）
- 若為表取了別名，則查詢的欄位就不能使用原來的表名取限定
SELECT
e.last_name,
e.job_id,#用表名去限定
j.job_title
FROM
employees e,
jobs j
WHERE
e.job_id = j.job_id;
#案例4：查詢有獎金的員工名、部門名、獎金率【增加篩選條件】
SELECT
last_name,
department_name,
commission_pct
FROM
employees e,
departments d
WHERE
e.department_id = d.department_id
AND e.commission_pct IS NOT NULL;
#案例5：查詢城市名中第二個字元為'o'的部門名和城市名【增加篩選條件】
SELECT
department_name,
city
FROM
departments d,
locations l
WHERE
d.location_id = l.location_id
AND city LIKE '_o%';
#案例6：查詢每個城市的部門個數【與group by子句搭配使用】
SELECT
count(*) 個數,
city
FROM
departments d,
locations l
WHERE
d.location_id = l.location_id
GROUP BY
city;
#案例7：查詢有獎金的每個部門的部門名和部門的領導編號和該部門的最低工資
【與group by子句搭配使用】
SELECT
department_name,
e.manager_id,
MIN(salary)
FROM
departments d,
employees e
WHERE
e.department_id = d.department_id
AND e.commission_pct IS NOT NULL
GROUP BY
department_name,manager_id;
#案例8：查詢每個工種的工種名，和員工個數，並按員工個數降序【與order by 子句搭配使用】
SELECT
job_title,
COUNT(*)
FROM
jobs j,
employees e
WHERE
j.job_id = e.job_id
GROUP BY
job_title
ORDER BY
COUNT(*) DESC;
#案例9：查詢員工名、部門名和所在的城市【多表聯合查詢】
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;
#非等值連接
#案例1：查詢員工的工資和工資級別
SELECT
salary,
grade_level
FROM
employees e,
job_grades j
WHERE
salary BETWEEN lowest_sal #salary在這個範圍內就顯示出來（不是等值的形式，而是一個範圍的判斷）
AND highest_sal;
#自連接（當前表要要連接當前表，為了不模糊，則需各取別名進行限定！）
#案例：查詢員工名和上級的名稱
SELECT
e.last_name 員工名,
m.last_name 上級名稱
FROM
employees e,
employees m
WHERE
e.manager_id = m.employee_id;
