一.常见的聚合函数介绍

1.1 什么是聚合函数

聚合函数作用于一组数据, 并对一组数据返回一个值, 也就是输入一组数据的集合,输出的是单个值

1.2 AVG (求平均函数) 和 SUM函数

注意: 这两个函数只适用于数值类型的变量

例题1: 求员工工资的平均数和总数

SELECT AVG(salary), SUM(salary)
FROM employees e 
/*
 AVG(salary)|SUM(salary)|
-----------+-----------+
6461.682243|   691400.0|
 */

1.3 MIN 和 MAX函数

注意: 使用于字符串类型(根据UTF-8比较规则), 日期类型, 数值类型

例题2: 求员工工资中的最大值和最小值

SELECT MAX(salary), MIN(salary) 
FROM employees e
/*
 MAX(salary)|MIN(salary)|
-----------+-----------+
    24000.0|     2100.0|
 */

#求姓名和入职时间

SELECT MAX(last_name), MIN(last_name), MAX(hire_date), MIN(hire_date)
FROM employees e 
/*
 MAX(last_name)|MIN(last_name)|MAX(hire_date)|MIN(hire_date)|
--------------+--------------+--------------+--------------+
Zlotkey       |Abel          |    2000-04-21|    1987-06-17|
 */

1.4 COUNT函数

作用: 计算指定字段在查询结构中出现的个数 (用来统计个数的)

例题3: 统计表中总人数
可用三种方式来实现
方式一: COUNT (*) 意思用 * 来代替一条数据
方式二: COUNT (1) 意思用 1 来代替一条数据
方式三: COUNT (具体字段) ----> 注意:这种方式是不统计包含NULL的

效率: 方式1 = 方式2 > 方式3

#已知:commission_pct(奖金)有72个员工的奖金为NULL

SELECT COUNT(last_name), COUNT(1), COUNT(*), COUNT(commission_pct)
FROM employees e 
/*
 COUNT(last_name)|COUNT(1)|COUNT(*)|COUNT(commission_pct)|
----------------+--------+--------+---------------------+
             107|     107|     107|                   35|
 */

公式: AVG = SUM / COUNT

SELECT AVG(salary), SUM(salary)/COUNT(salary)
FROM employees e 
/*
 AVG(salary)|SUM(salary)/COUNT(salary)|
-----------+-------------------------+
6461.682243|              6461.682243|
 */

例题4: 查询公司中平均奖金率

#错误的
SELECT AVG(commission_pct)
FROM employees e 
/*
 AVG(commission_pct)|
-------------------+
           0.222857|
 */


#正确的(没奖金的人头也要算)
SELECT AVG(IFNULL(commission_pct,0)), 
SUM(commission_pct)/COUNT(IFNULL(commission_pct,0)) AS '平均奖金'
FROM employees e 
/*
 AVG(IFNULL(commission_pct,0))|平均奖金    |
-----------------------------+--------+
                     0.072897|0.072897|
 */

二.GROUP BY (分组)

2.1 基本使用

例题5: 查询各个工种的平均工资

SELECT job_id, AVG(salary)
FROM employees e 
GROUP BY job_id

2.2 使用多个列分组

例题5: 查询各个部门号和工种的平均工资

SELECT department_id, job_id, AVG(salary)
FROM employees e 
GROUP BY department_id, job_id
#或者
SELECT job_id, department_id, AVG(salary)
FROM employees e 
GROUP BY job_id, department_id

上面两种方式,比如先按性别分再按班级分和先按班级分再按性别分是一样的

注意: SELECT中出现的非组函数的字段必须声明在GROUP BY 中
反之, GROUP BY中声明的字段不一定得出现在SELECT中

#错误示例
SELECT job_id, department_id, AVG(salary)
FROM employees e 
GROUP BY job_id

#其中, GROUP BY中缺少了SELECT中的department_id

2.3 GROUP 中使用 WITH ROLLUP

SELECT department_id, AVG(salary)
FROM employees e 
GROUP BY department_id WITH ROLLUP
/*
 department_id|AVG(salary) |
-------------+------------+
             |      7000.0|
           10|      4400.0|
           20|      9500.0|
           30|      4150.0|
           40|      6500.0|
           50| 3475.555556|
           60|      5760.0|
           70|     10000.0|
           80| 8955.882353|
           90|19333.333333|
          100|      8600.0|
          110|     10150.0|
             | 6461.682243|
 */

注意: 我们发现在GROUP 中使用 WITH ROLLUP结果会在最后多一行汇总

所以当使用WITH ROLLUP时,不能同时使用ORDER BY自居进行结果排序,因为最后的汇总结果会扰乱排序

三.HAVING

3.1 基本使用

例题6: 查询各个部门中最高工资比10000高的部门信息

#错误示例
SELECT department_id, MAX(salary)
FROM employees e 
WHERE MAX(salary) > 10000
GROUP BY department_id 

#正确示范
SELECT department_id, MAX(salary)
FROM employees e 
GROUP BY department_id
HAVING MAX(salary) > 10000

要求1: 如果过滤条件中使用了聚合函数, 则必须使用HAVING来替换WHERE, 否则报错
要求2: HAVING必须声明在 GROUP BY的后面
要求3: 我们使用HAVING的前提是SQL中使用了GROUP BY

3.2 WHERE和HAVING的对比

例题7: 查询部门id为10,20,30,40的部门中工资高于10000的部门信息

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

方式一执行效率高于方式二
根据情况结合使用

四.SELECT的执行过程

4.1 查询的结构

SQL99语法:
SELECT …,…,(存在聚合函数)
FROM …(LEFT / RIGHT) JOIN … ON 多表的连接条件
(LEFT / RIGHT) JOIN … ON…
WHERE 不包含聚合函数的过滤条件
GROUP BY …,…,…
HAVING 包含聚合函数的过滤条件
ORDER BY …,…,…(ASC / DESC)
LIMIT …,…