一、聚合函数介绍
- 什么是聚合函数 聚合函数就是用于一组数据，并对一组数据返回一个值
- 类型 AVG()SUM()MAX()MIN()COUNT()
注意：与单行函数不同的是，聚合函数不能嵌套调用。比如不能出现类似“AVG(SUM(字段名称))”形式的调用。
1.1AVG&SUM
求平均值和求和
==AVG和SUM再计算时候会过滤空值==
举例用表salary；salary是工资。
mysql> desc salary;+--------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+--------------+------+-----+---------+-------+| userid | int(11) | YES | | NULL | || salary | decimal(9,2) | YES | | NULL | |+--------+--------------+------+-----+---------+-------+2 rows in set (0.01 sec)mysql> select * from salary;+--------+---------+| userid | salary |+--------+---------+| 1 | 1000.00 || 2 | 2000.00 || 3 | 3000.00 || 4 | 4000.00 || 5 | 5000.00 || 1 | NULL |+--------+---------+6 rows in set (0.00 sec)#AVG和SUM使用mysql> select AVG(salary),SUM(salary)from salary;+-------------+-------------+| AVG(salary) | SUM(salary) |+-------------+-------------+| 3000.000000 | 15000.00 |+-------------+-------------+1 row in set (0.00 sec)
1.2MAX&MIN
最大值和最小值
==适用于数值类型，字符串类型，时间日期类型的字段或变量==
mysql> select MAX(salary),MIN(salary) from salary;+-------------+-------------+| MAX(salary) | MIN(salary) |+-------------+-------------+| 5000.00 | 1000.00 |+-------------+-------------+1 row in set (0.00 sec)
1.3COUNT
计数作用
mysql> select COUNT(userid) from salary;+---------------+| COUNT(userid) |+---------------+| 6 |+---------------+1 row in set (0.00 sec)
- 计算表中有多少条数据，如何实现？
- COUNT(1)
- COUNT(*)
- COUNT(具体字段)不一定对，因为如果有字段时NULL的时候会出错
- 计算指定字段出现个数时，是不计算NULL的 问题：
- 如果需要统计表中的记录数，使用以上哪种方法会效率高
- 如果是使用MyISAM 存储引擎，则三者效率相同都是O(1)
- 如果使用的是InnoDB存储引擎，则COUNT(*)=COUNT(1)>COUNT(字段)
- 用count()，count(1)，count(列名)谁好呢? 其实，对于MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。 InnoDB引擎的表用count(),count(1)直接读行数，复杂度是O(n)，因为innodb真的要去数一遍。但好于具体的count(列名)。
- 能不能使用count(列名)替换count()? 不要使用 count(列名)来替代 count() ， count(*) 是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。
count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。
二、GROUP BY 的使用
查询各个部门的平均工资
SELECT department_id,AVG(salary)FROM employessGROUP BY department_id;
SELECT 中出现的非组函数的字段必须声明在GROUP BY中，反之GROUP BY中声明的字段可以不出现在SELECT中
例如：
正确SELECT department_id,job_id,AVG(salary)FROM employeesGROUP BY department_id,job_id;错误-SELECT中出现的非组函数的字段没有全部声明在GROUP BY 中-SELECT department_id,job_id,AVG(salary)FROM employeesGROUP BY department_id;
GROUP BY声明在FROM后面 WHERE后面 ORDER BY 前面 LIMIT前面
- MySQL中GROUP BY 使用WITH ROLLUP
- 使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。
mysql> SELECT userid,AVG(salary) FROM salary GROUP BY userid WITH ROLLUP;+--------+-------------+| userid | AVG(salary) |+--------+-------------+| 1 | 1000.000000 || 2 | 2000.000000 || 3 | 3000.000000 || 4 | 4000.000000 || 5 | 5000.000000 || NULL | 3000.000000 |+--------+-------------+6 rows in set (0.00 sec)
注意：当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。
三、HAVING的使用
是用来过滤数据的
错误的，因为WHERE中使用了MAX聚合函数，所以只能使用HAVINGSELECT userid,MAX(salary)FROM salaryWHERE MAX(salary) > 1000;GROUP BY userid;
那我们就把WHERE换成HAVING试试，结果发现还是出错
错误的，因为HAVING要放在GROUP BY后面SELECT userid,MAX(salary)FROM salaryHAVING MAX(salary) > 1000;GROUP BY userid;
最后修改
mysql> SELECT userid,MAX(salary) -> FROM salary -> GROUP BY userid -> HAVING MAX(salary) > 1000;+--------+-------------+| userid | MAX(salary) |+--------+-------------+| 2 | 2000.00 || 3 | 3000.00 || 4 | 4000.00 || 5 | 5000.00 |+--------+-------------+4 rows in set (0.00 sec)
开发中，我们使用 HAVING 的前提是SQL中使用了 GROUP BY
再举一个例子
查询部门id为1，2，3这3个部门中最高工资比2500高的部门信息:
mysql> select userid,MAX(salary) FROM salary -> WHERE userid IN (1,2,3) -> GROUP BY userid -> HAVING MAX(salary)>2500;+--------+-------------+| userid | MAX(salary) |+--------+-------------+| 3 | 3000.00 |+--------+-------------+1 row in set (0.00 sec)方式2：mysql> select userid,MAX(salary) FROM salary -> GROUP BY userid -> HAVING MAX(salary)>2500 AND userid IN (1,2,3);+--------+-------------+| userid | MAX(salary) |+--------+-------------+| 3 | 3000.00 |+--------+-------------+1 row in set (0.00 sec)
方式1的执行效率高于方式2
结论：
- 当过滤条件中有聚合函数时，则此过滤条件必须声明在 HAVING中。
- 当过滤条件中没有聚合函数时，则此过滤条件声明在 WHERE 中或 HAVING中都可以，但是，应该声明在 WHERE 中，因为执行效率高。
WHERE与HAVING对比
- 从适用范围上来讲，HAVING的适用范围更广
- 如果过滤条件没有聚合函数，这种情况下，WHERE的执行效率要高于HAVING
- 总结
- 有聚合函数用HAVING 没有 聚合函数用 WHERE
四、SQL底层执行原理
4.1SQL完整结构
SQL92语法:SELECT ...,...,... (存在聚合函数)FROM ...,...,...WHERE 多表的连接条件 AND 不包含聚合函数的过滤条件GROUP BY ...,...,...（分组）HAVING 包含聚合函数的过滤条件ORDER BY ...,... (ASC升序 / DESC降序)LIMIT ...,...（分页）SQL99语法SELECT ...,...,... (存在聚合函数)FROM ...(LEFT / RIGHT)JOIN...ON 多表的连接条件(LEFT / RIGHT)JOIN....ON...（左连接/右连接）WHERE 不包含聚合函数的过滤条件GROUP BY ...,...,...（分组）HAVING 包含聚合函数的过滤条件ORDER BY ...,... (ASC升序 / DESC降序)LIMIT ...,...（分页）
4.2SQL语句执行过程
FROM 表,表—>ON 限制连接条件—> 是否是LEET / RINGHT JOIN —> WHERE —> GROUP BY —> HAVING —>SELECT —>DISTINCT —> ORDER BY —> LIMIT
先找表，再根据连接条件进行多表连接，(左或右外连接)，WHERE筛选，分组，HAVING筛选，SELECT查询，去重，排序，分页
==过滤条件越靠前，执行效率越高，所以WHERE比HAVING效率高==
==在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个虚拟表，然后将这个虚拟表传入下一个步骤中作为输入。需要注意的是，这些步骤隐含在 SQL 的执行过程中，对于我们来说是不可见的。我们最终看到的是经过筛选得到的结果集。==
4.3SQL语句执行原理
SELECT 是先执行 FROM 这一步的。在这个阶段，如果是多张表联查，还会经历下面的几个步骤：
1.首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table）1-1；
2.通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；
3.添加外部行。如果我们使用的是左连接、右连接或者全连接，就会涉及到外部行，也就是在虚拟表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。
当然如果我们操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得到是我们的原始数据。
当我们拿到了查询数据表的原始数据，也就是最终的虚拟表vt1 ，就可以在此基础上再进行WHERE 阶段 。在这个阶段中，会根据 vt1 表的结果进行筛选过滤，得到虚拟表vt2。
然后进入第三步和第四步，也就是 GROUP和 HAVING 阶段 。在这个阶段中，实际上是在虚拟表 vt2 的基础上进行分组和分组过滤，得到中间的虚拟表vt3和vt4 。
当我们完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到 SELECT和 DISTINCT阶段 。
首先在 SELECT 阶段会提取想要的字段，然后在 DISTINCT 阶段过滤掉重复的行，分别得到中间的虚拟表vt5-1 和vt5-2 。
当我们提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是 ORDER BY阶段 ，得到虚拟表vt6 。
最后在 vt6 的基础上，取出指定行的记录，也就是LIMIT 阶段 ，得到最终的结果，对应的是虚拟表vt7。
当然我们在写 SELECT 语句的时候，不一定存在所有的关键字，相应的阶段就会省略。
同时因为 SQL 是一门类似英语的结构化查询语言，所以我们在写 SELECT 语句的时候，还要注意相应的关键字顺序，所谓底层运行的原理，就是我们刚才讲到的执行顺序。
图解：
