[mysql]聚合函数GROUP BY和HAVING的使用和sql查询语句的底层执行逻辑

#GROUP BY的使用

还是先从需求出发,我们现在想求员工表里各个部门的平均工资,最高工资

SELECT department_id,AVG(salary)

FROM employees

GROUP BY department_id

我们就会知道它会把一样的id分组,没有部门的就会分为一组,我们也可以用其他字段来分组,我们想查询不同jb_id的平均工资

SELECT job_id,AVG(salary)

FROM employees

GROUP BY job_id

那这个大家就了解了,还是比较容易理解的

现在我们要使用多个列分组比如我们要计算不同部门的不同工种的平均工资,也就是用job_id和department_id的平均工资

SELECT job_id,department_id,AVG(salary)

FROM employees

GROUP BY department_id, job_id

我们现在想先用job_id先分组,然后再对departmentid分组

这两个是一样的吗.答案是一样的,比如我们先把一个部门给分类,在把一个工种分类,和先把工种分类再把部门分类这个明显是相同的

刚才我们计算的时候,没有加部门和工种,字段,是为了我们的可读性来给它加上了字段,我们能不能在group,by里去掉工种只按照部门分工种

现在mysql最新版已经不支持这个写法了,因为我们用group by只对工种进行分组了,但是剩下的部门没有分,那不就没有位置放部门了

GROUP BY的结论1

我们除了聚合函数的字段,一定要出现在GROUP BY中,但是GROUPBY的字段不一定要出现在查询字段中.

GROUP BY的位置

GROUP BY必须在FROM 和WHERE的后面ORDER BY的前面和LIMIT的前面

GROUP BY的结论3新特性

Mysql中的GROUP BY的WITH ROLLUP

我们在GROUP 字段 WITH ROLLUP

现在我们是不是可以发现最后多了一行,这个数字实际上是公司的平均工资,

我们这里要小心的使用ORDER BY和 WITH ROLLUP 我们不加的话是可以进行运行,但是加入之后就会报错,这是错误的方式,但是mysql最新版已经支持这么做了.

 

我们要是出现分组了大家就应该注意到是我们要对字段进行相同值分组的时候进行.

HAVING TO的使用

我们现在多了一个HAVING TO

现在我们想查询各个部门中最高工资比10000高的部门信息.

相当于我们想查询各个部门中最高工资

要求1

SELECT后面出现的非聚合函数字段,一定要出现在GROUP BY后面

还有一件事,我们如果字段出现非聚合函数,一定要出现在GROUPBY中,这个大家一定要牢记,那么现在我们发现查询出来的结果有很多是没有超过10000的,和我们的要求不一致,比10000小的我们就不要,我们以前是只讲过WHERE 而且它必须跟在FROM后面,那么我们现在写WHERE MAX(salary)>10000,

要求2

一旦我们的过滤条件中使用的聚合函数,那么我们就必须要使用HAVING TO来替换WHERE 否则就会报错.

现在我们出现了聚合函数,或者说组函数了,我们来写一个正确的写法

不管我们学那个语言我们实际上是有很多规则的,+表示加和连接,这是规范和规则出现的,直接背就好了,就和叫爸爸叫妈妈,一样没有小孩会想为什么要这么叫,下面的东西就可以考虑深层次的原因,后面的东西我们都是可以解释的,也就是一会我们会说HAVING TO 为什么不能用WHERE,

我们把HAVING换进去,发现还是报错了,

要求2:我们要把HAVING TO 放在GROUP BY的后面

他的声明位置必须在GROUP BY的后面,写在前面了自然就错误了

这就是我们正确的情况

要求3

要是没有GROUP BY能不能使用HAVING TO 函数,发现我们是可以使用的,因为我们没有聚合函数了,我们使用的时候,最后聚合函数的结果是不是没有必要去过滤它了.我们用WHERE筛选之后,再用聚合函数之后再用HAVING就没有什么意义了

所以HAVING 要和GROUPBY一起合并使用.

现在我们要查询部门ID为10,20,30,40,4个部门最高工资比10000高的部门信息

这里前面也是一个过滤条件,我们能不能用WHERE来写,这是方式1

SELECT department_id,MAX(salary)

FROM employees

WHERE department_id IN (10,20,30,40)

GROUP BY department_id

HAVING MAX(salary)>10000

我们还能把条件写在WHERE

SELECT department_id,MAX(salary)

FROM employees

GROUP BY department_id

HAVING MAX(salary)>10000 AND department_id IN (10,20,30,40)

那么大家就会纳闷了,那么大家应该就有一个疑惑了,WHERE和HAVING有什么区别呢.

大家可以逆向思维.存在就是合理的,WHERE 里面是不能加聚合函数,那我们直接用HAVING不就好了,我们这里就会推荐大家使用方式1,因为方式1的执行效率比方式2高,,

当过滤条件中有聚合函数时间,这个过滤条件必须声明在HAVING中,没有聚合函数时,此过滤条件声明在WHERE中和HAVING都可以,建议大家声明在WHERE中

这不是同情,因为WHERE的效率高,虽然是建议,大家还是直接当成规则用了.

WHERE和HAVING的对比1从适用范围来讲HAVING的适用范围更广,HAVING可以完成聚合函数的过滤

2如果过滤函数里没有聚合函数,这种情况WHERE效率高于HAVING,开发中选择

,子查询是一个查询中套了一个查询,基本的知识点我们已经讲到,子查询只是在结构里嵌套另一个结构,所以我们可以直接开始讲SELECT 语句的结构了

SELECT语句的完整结构:

sql92语法

SELECT            (含有聚合函数)

FROM 表1,表2,表3

WHERE 多表的连接条件 AND 过滤条件,不包含聚合函数

GROUP BY 分组操作,….

HAVING 包含聚合函数的过滤条件

ORDER BY

LIMIT

sql99语法

SELECT ……(含有聚合函数)

FROM 表1 (left/right)JOIN

ON 表2

(left/right)JOIN

ON表2

WHERE 不包含 聚合函数的过滤条件

GROUP BY

HAVING

ORDER BY

LIMIT

SQL语句的执行过程

第一部分

SELECT ……(含有聚合函数)

第二部分

FROM 表1 (left/right)JOIN

ON 表2

(left/right)JOIN

ON表2

WHERE 不包含 聚合函数的过滤条件

GROUP BY

HAVING

第三部分

ORDER BY

LIMIT

正常情况我们以为是按顺序运行的,实际上我们的是先执行的第二部分,然后第一部分最后第三部分.

上来我们先FROM 表1 (left/right)JOIN

ON 表2

(left/right)JOIN

ON表2

WHERE 不包含 聚合函数的过滤条件

GROUP BY

HAVING

如下

FROM ->ON->(left/right)JOIN->WHERE->GROUP->HAVING ->SELECT->DISTINCT->ORDER BY->LIMIT

我们先找到一张表连接另一张表,进行笛卡尔交叉的连接,然后用ON来把不关联的去掉了,限制了连接的条件,这是在后台的虚拟表,每执行一步就变化一步,过滤了之后,由于可能是左外和右外的连接,,虚拟表也会进行保留,然后进行WHERE条件的过滤,保留需要的内容,然后对其中内容进行分组,分组之后我们再进行HAVING聚合函数的条件筛选,然后看看SELECT想要的是那几个字段,因为字段是全部保留之前虚拟表里,用SELECT筛选出我们要的字段,,因为这里面还有可能有DISTINCT去重的操作,再过滤一小部分,确定了最后的数据,对数据进行排序,和分页的操作

现在我们就可以解释为什么where比HAVING的数据效率高了,因为我们分组之前可能10万条数据,我们对它进行筛选了只剩下10条,然后我们分组,那是不是很简单,如果我们用的是HAVING那么就吭哧吭哧对它进行10万条分组,,最后HAVING说只要最后2个条件,那前面8万条都浪费操作了,

为什么WHERE 不能放聚合条件呢,因为我们还没分组,聚合条件根本用不了,你只能前向引用,HAVING可以向分组的GROUPBY进行计算函数,但是 WHERE不能向未来运行GROUPBY进行前向引用,这里就解释完了,

我们在SELECT中查询一个字段我们可以在ORDERBY里使用别名,但是不能再WHERE里使用,也是因为这个原因

每个过程中都会形成一个虚拟表,这就是一个执行过程,是不是感觉挺高端的,之后我们还会讲更多SQL的底层逻辑我们才能将SQL的优化问题.得知道粮食里的结构才能养生,我们要能辨别知识的结构.我们就得有一个完善的体系架构