什么是聚合函数
聚合函数作用于一组数据,并对一组数据返回一个值.
聚合函数的类型
AVG() 平均值
SUM() 求和
MAX() 最大值
MIN() 最小值
COUNT() 计数 不计算NULL值
计算表中有多少条记录
COUNT(*) COUNT(1)
如果需要统计表中的记录数,使用COUNT(*),COUNT(1),COUNT(具体字段)哪个效率更高呢
对于MyISAM引擎的表是没有区别的,这种引擎内部有一计数器在维护着行数,时间复杂度都是O(1)
Innodb引擎的表用conut(*)=count(1)直接读行数,复杂度是O(n).因为Innodb真的要去数一遍,但好于count(列名)
GROUP BY的使用
查询各个部门的平均工资,最高工资
SELECT department_id,AVG(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 job_id,AVG(salary) FROM employees GROUP BY job_id;注意:
1.SELECT中出现的非组函数的字段必须声明在GROUP BY中.反之,GROUP BY中声明的字段可以不出现在SELECT中.
2.GROUP BY 声明在FROM后,WHERE后,BRDER BY前面,LIMIT前面
3.GROUP BY中使用WITH ROLLUP(不能和ORDER BY一起使用)
当使用ROLLUP时,不能同时使用ORDER BY字句进行结果排序,即ROLLUP和ORDER BY是互相排斥的
SELECT department_id,AVG(salary) FROM employees GROUP BY department_id WITH ROLLUP;HAVING的使用
作用:用来过滤数据
要求:
1.如果过滤条件中使用了聚合函数,则必须使用HAVING来替换WHERE,否则报错
2.HAVING必须声明在GROUP BY的后面
3.开发中,我们使用HAVING的前提是SQL中使用了GROUP BY.
查询各个部门中最高工资比10000高的部门信息
SELECT department_id, MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary) > 10000;查询部门ID为10,20,30,40这四个部门中最高工资比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与HAVING的对比：
区别1：WHERE可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件，HAVING必须要与GROUP BY配合使用，可以把分组计算的函数和分组字段作为筛选条件。这决定了需要对数据进行分组统计的时候，HAVING可以完成WHERE不能完成的任务，这是因为，在查询语句中，WHERE在GROUP BY之前，所以无法对分组结果进行筛选，HAVING在GROUP BY之后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选，这个功能是WHERE无法完成的。另外，WHERE排除的记录不再包括在分组中。
区别2：如果需要连接从关联表中获取需要的数据，WHERE是先筛选后连接，而HAVING是先连接后筛选
这一点就决定了在关联查询中，WHERE必HAVING更高效。因为WHERE可以先筛选，用一个筛选后较小的数据集合关联表进行连接，这样占用的资源比较少，执行效率也比较高。HAVING则需要先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用的资源就比较多，执行效率也比较低。
SQL底层执行原理
SELECT语句完整结构
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 ...,...
SQL语句的执行过程
FROM ->ON->(LEFT/RIGHT) JOIN ->WHERE->GROUP BY->HAVING->SELECT->DISTINCT->ORDER BY->LIMIT
