#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的优化题目.得知道粮食里的结构才气养生,我们要能辨别知识的结构.我们就得有一个美满的体系架构
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。 |
