（以下內容全部來自上述課程）

聚合函數

1. 介紹

聚合函數作用于一組數據，并對一組數據返回一個值。

• 聚合函數類型

• AVG（）
• SUM（）
• MAX（）
• MIN（）
• COUNT（）

2. 常見的聚合函數

2.1 AVG / SUM

#1.1 AVG / SUM:只適用于數值類型的字段(或變量)
SELECT AVG(salary),SUM(salary),AVG(salary) * 107 
FROM employees;#如下的操作沒有意義
SELECT SUM(last_name),AVG(last_name),SUM(hire_date) 
FROM employees;

2.2 MAX / MIN

#1.2 MAX / MIN :適用于數值類型、字符串類型、日期時問類型的字段(或變量)
SELECT MAX(salary),MIN(salary)
FROM employees;	SELECT MAX(last_name),MIN(last_name),MAX(hire_date),MIN(hire_date) 
FROM employees;

2.3 COUNT

#1.3 COUNT:
# （1）作用:計算指定字段在查詢結構中出現的個數（不包含NULL）
#                 107             107            107             107        107        107
SELECT COUNT (employee_id),COUNT (salary) ,COUNT(2 * salary),COUNT (1) , COUNT (2), COUNT (*) 
FROM employees ;SELECT	*	
FROM employees;#如果計算表中有多少條記錄，如何實現?
#方式1:COUNT(*)
#方式2: COUNT(1)
#方式3:COUNT(具體字段):不一定對!#（2）注意:計算指定字段出現的個數時，是不計算NULL值的。 
SELECT COUNT(commission_pct) 
FROM employees;SELECT commission_pct 
FROM employees
WHERE commission_pct IS NOT NULL;# (3) 公式: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;	#需求:查詢公司中平均獎金率#錯誤的!
SELECT AVG(commission_pct) 
FROM employees;#正確的:
SELECT SUM(commission_pct) / COUNT(IFNULL(commission_pct,0)), 
AVG(IFNULL(commission_pct,0)) 
FROM employees;

其他：方差、標準差、中位數

3. GROUP BY 的使用

1. 結論1:SELECT中出現的非組函數的字段必須聲明在GROUP BY 中。
   反之，GROUP BY中聲明的字段可以不出現在SELECT中。
2. 結論2:GROUP BY聲明在FROM后面、WHERE后面，ORDER BY前面、LIMIT前面
3. 結論3:MySQL中GROUPBY中使用WITH ROLLUP（總體）
   說明:當使用ROLLUP時，不能同時使用ORDERBY子句進行結果排序，即ROLLUP和ORDERBY是互相排斥的。

#2. 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的平均工資 
SELECT department_id,job_id,AVG (salary) 
FROM employees
GROUP BYdepartment_id,job_id;
#或
SELECT job_id,department_id,AVG (salary) 
FROM employees
GROUP BY job_id,department_id;	

#錯誤的!
SELECT department_id, job_id,AVG (salary) 
FROM employees
GROUP BY department_id;#結論1:SELECT中出現的非組函數的字段必須聲明在GROUP BY 中。
#	反之，GROUP BY中聲明的字段可以不出現在SELECT中。	
#結論2:GROUP BY聲明在FROM后面、WHERE后面，ORDER BY前面、LIMIT前面#結論3:MySQL中GROUPBY中使用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時，不能同時使用ORDERBY子句進行結果排序，即ROLLUP和ORDERBY是互相排斥的。
#錯誤的:
SELECT department_id,AVG(salary) avg_sal 
FROM employees
GROUP BY department_id 
WITH ROLLUP 
ORDER BY avg_sal ASC;

4. HAVING的使用

1. 要求1:如果過濾條件中使用了聚合函數，則必須使用HAVING來替換WHERE。否則，報錯。

2. 要求2:HAVING必須聲明在GROUP BY 的后面。

3. 要求3:開發中，我們使用HAVING的前提是SQL中使用了GROUP BY。

4. 結論:當過濾條件中有聚合函數時，則此過濾條件必須聲明在HAVING中。

5. 當過濾條件中沒有聚合函數時，則此過濾條件聲明在WHERE中或HAVING中都可以。

6. 但是，建議大家聲明在WHERE

WHERE與AVING 的對比

1. 從適用范圍上來講，HAVING的適用范圍更廣。
2. 如果過濾條件中沒有聚合函數:這種情況下， WHERE的執行效率要高于HAVING

#3.HAVING的使用(作用:用來過濾數據的)
#練習:查詢各個部門中最高工資比10000高的部門信息#錯誤的寫法:
SELECT department_id,MAX(salary) 
FROM employees
WHERE MAX(salary)>10000 
GROUP BY department id;#要求1:如果過濾條件中使用了聚合函數，則必須使用HAVING來替換WHERE。否則，報錯。
#要求2:HAVING必須聲明在GROUP BY 的后面。
#正確的寫法:
SELECT department_id,MAX(salary) 
FROM employees
GROUP BY department id
HAVING MAX(salary)>10000;# 要求3:開發中，我們使用HAVING的前提是SQL中使用了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與AVING 的對比
1.從適用范圍上來講，HAVING的適用范圍更廣。
2.如果過濾條件中沒有聚合函數:這種情況下， WHERE的執行效率要高于HAVING
*/

5. SQL底層執行原理

5.1 SELECT 語句的完整結構

#sq192語法:
/*
SELECT .......··.(存在聚合函數) 
FROM .........
WHERE 多表的連接條件AND不包含聚合函數的過濾條件 
GROUP BY......
HAVING 包含聚合函數的過濾條件 
ORDER BY ....,...(ASC / DESC )
LIMIT	...	
*/#sq199語法:
/*
SELECT ....,....,....(存在聚合函數)
FROM ... (LEFT / RIGHT) JOIN ....ON 多表的連接條件
(LEFT / RIGHT) JOIN ... ON.... 
WHERE 不包含聚合函數的過濾條件 
GROUP BY.......
HAVING 包含聚合函數的過濾條件 
ORDER BY ....,...(ASC / DESC ) 
LIMIT ........
*/

5.2 SQL 語句的執行順序

#4.2 SQL語句的執行過程:
#FROM.......-> ON ->(LEFT/RIGNT JOIN) -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -># ORDER BY -> LIMIT