MySQL數(shù)據(jù)庫(kù)：第六章：分組函數(shù)/聚合函數(shù)

回退至Mysql數(shù)據(jù)庫(kù)理論與實(shí)戰(zhàn)
#進(jìn)階5：分組函數(shù)/聚合函數(shù)

本質(zhì)上就是一種函數(shù)，調(diào)用語(yǔ)法：select 函數(shù)名(實(shí)參列表);
函數(shù)：
單行函數(shù)（常見函數(shù)）：有幾行，最終有幾個(gè)結(jié)果
分組函數(shù)（聚合函數(shù)）：一組中有多行，但最終一個(gè)結(jié)果，一般用作統(tǒng)計(jì)
分組函數(shù)：
sum(參數(shù)）:求和
avg(參數(shù)):平均
max(參數(shù))：求最大值
min(參數(shù))：求最小值
count(參數(shù))：統(tǒng)計(jì)個(gè)數(shù)

#1.簡(jiǎn)單的使用

SELECT SUM(salary),AVG(salary),MAX(salary),MIN(salary),COUNT(salary) FROM employees;

#2.參數(shù)支持任意類型？

sum和avg一般用于處理數(shù)值型
count、max、min可以處理任意類型

SELECT SUM(last_name),AVG(last_name),MAX(last_name),MIN(last_name),COUNT(last_name) FROM employees;
SELECT SUM(hiredate),AVG(hiredate),MAX(hiredate),MIN(hiredate),COUNT(hiredate) FROM employees;

#3.是否都忽略null值？

五個(gè)分組函數(shù)都忽略null
SELECT SUM(commission_pct),AVG(commission_pct),MAX(commission_pct),MIN(commission_pct),COUNT(commission_pct) FROM employees;
SELECT AVG(commission_pct),SUM(commission_pct)/107,SUM(commission_pct)/35 FROM employees;

#4.去重的統(tǒng)計(jì)

 count(distinct 字段名)
    SELECT COUNT(department_id) FROM employees;
    #案例：?jiǎn)T工涉及到了幾個(gè)部門
    SELECT COUNT(DISTINCT department_id) FROM employees;
    #5.count的實(shí)參形式
    SELECT COUNT(*) FROM employees;
    SELECT COUNT(1) FROM employees;
    #6.添加篩選條件
    SELECT COUNT(*)  FROM employees WHERE department_id = 30;
    #7.不是所有的字段都能和分組函數(shù)一同查詢出來(lái)的
    SELECT COUNT(*) ,employee_id FROM employees;