mysql基礎（七）：聚合函數

AVG和SUM

• 可以對數值型資料使用AVG 和 SUM 函數
• 代碼案例

SELECT AVG(salary), SUM(salary), AVG(salary) * 107 FROM employees;

# 錯誤案例：
# 如下的操作沒有意義，因為不是數值類型
SELECT SUM(last_name), AVG(last_name), SUM(hire_date) FROM employees;      

MIN和MAX函數

• 可以對任意資料類型的資料使用 MIN 和 MAX 函數
• 代碼案例

SELECT MAX(salary), MIN(salary) FROM employees;

SELECT MAX(last_name), MIN(last_name), MAX(hire_date), MIN(hire_date) FROM employees;      

COUNT函數

• COUNT(*)傳回表中記錄總數，适用于任意資料類型
• 代碼案例

# 計算指定字段在查詢結構中出現的個數（不包含NULL值的）
SELECT COUNT(employee_id), COUNT(salary), COUNT(2 * salary), COUNT(1), COUNT(2), COUNT(*) FROM employees;

# 計算表中有多少條記錄
# 方式1：COUNT(*)
# 方式2：COUNT(1)
# 方式3：COUNT(具體字段)，不一定對

# 注意：計算指定字段出現的個數時，是不計算NULL值的
SELECT COUNT(commission_pct) FROM employees;

# 公式：AVG = SUM / COUNT
SELECT AVG(salary), SUM(salary)/COUNT(salary),
AVG(commission_pct), SUM(commission_pct)/COUNT(commission_pct),
SUM(commission_pct) / 107
FROM employees;

# 查詢公司中平均獎金率
# 錯誤寫法：如果某些人的獎金為null，則不會計算
SELECT AVG(commission_pct) FROM employees;

# 正确寫法
SELECT SUM(commission_pct) / COUNT(IFNULL(commission_pct,0)),
AVG(IFNULL(commission_pct,0))
FROM employees;

# 如何需要統計表中的記錄數，使用COUNT(*)、COUNT(1)、COUNT(具體字段) 哪個效率更高
# 如果使用的是MyISAM 存儲引擎，則三者效率相同，都是O(1)
# 如果使用的是InnoDB 存儲引擎，則三者效率：COUNT(*) = COUNT(1)> COUNT(字段)      

GROUP BY

• 可以使用GROUP BY子句将表中的資料分成若幹組
• 代碼案例

# 查詢各個部門的平均工資，最高工資
SELECT department_id, AVG(salary), SUM(salary) FROM employees GROUP BY department_id

# 查詢各個job_id的平均工資
SELECT job_id, AVG(salary) FROM employees GROUP BY job_id;

# 查詢各個department_id,job_id的平均工資
# 方式1：
SELECT department_id, job_id, AVG(salary) FROM employees GROUP BY department_id, job_id;
# 方式2：
SELECT job_id, department_id, AVG(salary) FROM employees GROUP BY job_id,department_id;

# 錯誤寫法：job_id是非組函數，但沒有寫在group by中
SELECT department_id, job_id, AVG(salary)
FROM employees
GROUP BY department_id;

# 錯誤原因：SELECT中出現的非組函數的字段必須聲明在GROUP BY 中
# 反之，GROUP BY中聲明的字段可以不出現在SELECT中

# GROUP BY 聲明在FROM後面、WHERE後面，ORDER BY 前面、LIMIT前面

# MySQL中GROUP BY中使用WITH ROLLUP，用于統計
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id WITH ROLLUP;

# 查詢各個部門的平均工資，按照平均工資升序排列
SELECT department_id, AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal ASC;

# 當使用ROLLUP時，不能同時使用ORDER BY子句進行結果排序，即ROLLUP和ORDER BY是互相排斥的
# 錯誤寫法
SELECT department_id, AVG(salary) avg_sal
FROM employees
GROUP BY department_id WITH ROLLUP
ORDER BY avg_sal ASC;      

HAVING

• 簡介

1. 行已經被分組
2. 使用了聚合函數
3. 滿足HAVING 子句中條件的分組将被顯示
4. HAVING 不能單獨使用，必須要跟 GROUP BY 一起使用      

• 代碼案例

# 查詢各個部門中最高工資比10000高的部門資訊
# 錯誤寫法：不能在 WHERE 子句中使用聚合函數
SELECT department_id, MAX(salary)
FROM employees
WHERE MAX(salary) > 10000
GROUP BY department_id;

# 如果過濾條件中使用了聚合函數，則必須使用HAVING來替換WHERE。否則會報錯
# HAVING 必須聲明在 GROUP BY 的後面
#正确的寫法：
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;

# 開發中，我們使用HAVING的前提是SQL中使用了GROUP BY，也即是說使用了having就必須使用group by

# 查詢部門id為10,20,30,40這4個部門中最高工資比10000高的部門資訊
# 方式1：推薦，執行效率高于方式2
SELECT department_id, MAX(salary)
FROM employees
WHERE department_id IN (10,20,30,40)
GROUP BY department_id
HAVING MAX(salary) > 10000;

#方式2：
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000 AND department_id IN (10,20,30,40);

# 當過濾條件中有聚合函數時，則此過濾條件必須聲明在HAVING中
# 當過濾條件中沒有聚合函數時，則此過濾條件聲明在WHERE中或HAVING中都可以。但建議聲明在WHERE中

/*
 * WHERE 與 HAVING 的對比
 * 1. 從适用範圍上來講，HAVING的适用範圍更廣。
 * 2. 如果過濾條件中沒有聚合函數：這種情況下，WHERE的執行效率要高于HAVING
 */      

• ​

  ​sql語句書寫順序​

  ​

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...      

• ​

  ​sql語句執行順序​

  ​

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT      

# 1. where子句不能使用組函數進行過濾

# 2.查詢公司員工工資的最大值，最小值，平均值，總和
SELECT MAX(salary) max_sal, MIN(salary) mim_sal, AVG(salary) avg_sal, SUM(salary) sum_sal FROM employees;

# 3.查詢各job_id的員工工資的最大值，最小值，平均值，總和
SELECT job_id, MAX(salary), MIN(salary), AVG(salary), SUM(salary) FROM employees GROUP BY job_id;

# 4.選擇具有各個job_id的員勞工數
SELECT job_id, COUNT(*) FROM employees GROUP BY job_id;

# 5.查詢員工最高工資和最低工資的差距（DIFFERENCE）
SELECT MAX(salary) - MIN(salary) "DIFFERENCE" FROM employees;


# 6.查詢各個管理者手下員工的最低工資，其中最低工資不能低于6000，沒有管理者的員工不計算在内
SELECT manager_id, MIN(salary) FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) >= 6000;

# 7.查詢所有部門的名字，location_id，員工數量和平均工資，并按平均工資降序 
SELECT d.department_name, d.location_id, COUNT(employee_id), AVG(salary) 
FROM departments d LEFT JOIN employees e
ON d.`department_id` = e.`department_id`
GROUP BY department_name, location_id

# 8.查詢每個工種、每個部門的部門名、工種名和最低工資 
SELECT d.department_name, e.job_id, MIN(salary)
FROM departments d LEFT JOIN employees e
ON d.`department_id` = e.`department_id`
GROUP BY department_name, job_id