mysql聚合函數(shù)有哪些及怎么使用

這篇“mysql聚合函數(shù)有哪些及怎么使用”文章的知識點(diǎn)大部分人都不太理解，所以小編給大家總結(jié)了以下內(nèi)容，內(nèi)容詳細(xì)，步驟清晰，具有一定的借鑒價值，希望大家閱讀完這篇文章能有所收獲，下面我們一起來看看這篇“mysql聚合函數(shù)有哪些及怎么使用”文章吧。

AVG和SUM

• 可以對數(shù)值型數(shù)據(jù)使用AVG 和 SUM 函數(shù)

• 代碼案例

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

# 錯誤案例：
# 如下的操作沒有意義，因?yàn)椴皇菙?shù)值類型
SELECT SUM(last_name), AVG(last_name), SUM(hire_date) FROM employees;

MIN和MAX函數(shù)

• 可以對任意數(shù)據(jù)類型的數(shù)據(jù)使用 MIN 和 MAX 函數(shù)

• 代碼案例

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

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

COUNT函數(shù)

• COUNT(*)返回表中記錄總數(shù)，適用于任意數(shù)據(jù)類型

• 代碼案例

# 計(jì)算指定字段在查詢結(jié)構(gòu)中出現(xiàn)的個數(shù)（不包含NULL值的）
SELECT COUNT(employee_id), COUNT(salary), COUNT(2 * salary), COUNT(1), COUNT(2), COUNT(*) FROM employees;

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

# 注意：計(jì)算指定字段出現(xiàn)的個數(shù)時，是不計(jì)算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，則不會計(jì)算
SELECT AVG(commission_pct) FROM employees;

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

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

GROUP BY

• 可以使用GROUP BY子句將表中的數(shù)據(jù)分成若干組

• 代碼案例

# 查詢各個部門的平均工資，最高工資
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是非組函數(shù)，但沒有寫在group by中
SELECT department_id, job_id, AVG(salary)
FROM employees
GROUP BY department_id;

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

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

# MySQL中GROUP BY中使用WITH ROLLUP，用于統(tǒng)計(jì)
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;

# 當(dāng)使用ROLLUP時，不能同時使用ORDER BY子句進(jìn)行結(jié)果排序，即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. 行已經(jīng)被分組
2. 使用了聚合函數(shù)
3. 滿足HAVING 子句中條件的分組將被顯示
4. HAVING 不能單獨(dú)使用，必須要跟 GROUP BY 一起使用

• 代碼案例

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

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

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

# 查詢部門id為10,20,30,40這4個部門中最高工資比10000高的部門信息
# 方式1：推薦，執(zhí)行效率高于方式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);

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

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

• sql語句書寫順序

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

• sql語句執(zhí)行順序

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

課后練習(xí)

# 1. where子句不能使用組函數(shù)進(jìn)行過濾

# 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的員工人數(shù)
SELECT job_id, COUNT(*) FROM employees GROUP BY job_id;

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


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

# 7.查詢所有部門的名字，location_id，員工數(shù)量和平均工資，并按平均工資降序 
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

以上就是關(guān)于“mysql聚合函數(shù)有哪些及怎么使用”這篇文章的內(nèi)容，相信大家都有了一定的了解，希望小編分享的內(nèi)容對大家有幫助，若想了解更多相關(guān)的知識內(nèi)容，請關(guān)注億速云行業(yè)資訊頻道。