MySQL入门(3)- 分组函数(count、sum、avg、max、min)&分组查询(groupby、orderby)

分组函数&分组查询

此系列记录PN视频学习笔记


* 本博客涉及到的数据为bjpowernode.sql,数据见文末,SQL文见MySQL入门(9)文末。

* 该数据有三张表,分别是部门表dept(deptno部门编号、dname部门名称、loc位置)、

                                       员工表emp(empno工号、ename员工姓名、job职位、mgr直属领导工号、

                                                            hiredate入职日期、sal月薪、comm补贴、deptno部门编号)、

                                        薪水等级表salgrade(grade等级、losal区间下限、hisal区间上限)


一、分组函数/聚合函数/多行处理函数(自动忽略空值)

对所有行求和之后得到一个结果,如:

分组函数:count、sum、avg、max、min

分组函数不能直接使用在where语句中:select sal from emp where sal > avg(sal)

select sum(sal) from emp;

select avg(sal) from emp;

select max(sal) from emp;

select min(sal) from emp;

select count(ename) from emp;

select count(comm) from emp;【4个】(cont(字段名),当前该字段不为null的计数)

select count(*) from emp;【14个】

select count(*) from emp where comm is null;【10个】(count(*)是满足某个条件的计数)

 

二、去除重复distinct

只作用于查询结果,不更改原数据,只能出现所有字段的最前方。

select job from emp;

select distinct job from emp;

select ename,distinctjob from emp;(会报错,ename有14条数据,distingct job只有五条)

select distinct dept,job fromemp;(取出两个记录都重复的记录)

select count(distinctjob) from emp;(统计有多少个工种)

 

三、分组查询

    1、分组查询涉及到两个子句:

       -group by

       -having(分组前完成数据过滤,而where在分组后过滤)

 

    2、 group by

       2.1

              orderby 【表示通过哪个或那些字段进行排序】

              groupby 【表示通过哪个或那些字段进行分组】

 

       2.2案例:找出每个工作岗位的最高薪水【先按照工作岗位分组,使用max函数求每一组的最高薪水】

       selectmax(sal) from emp group by job;

       以上SQL语句表示,先按照job分组然后对每一组使用max(sal)求最高薪水。

       以上查询结果是5条结果

       selectjob,max(sal) from emp groupby job;

       工作岗位可以写在select后面,因为工作岗位job也是5个不同的值。

       selectename,job,max(sal) from emp group by job;

       ename  可以写在select后面吗?

                    - mysql中可以,语法在这方面比较松散,不严格,但是查询结果没有意义,因为名字和工资对不上。

                    - oracle中不可以,语法严格,执行的时候会直接报错。

       重点:若一条DQL语句中有group by子句,那么select关键字后面只能跟参与分组的字段和分组函数。

 

       2.3案例:计算每个部门的平均薪水【按照部门编号分组,对每一组求平均薪水】

           selectdeptno,avg(sal) as avgsal fromemp group by deptno;

 

       2.4案例:计算不同部门中不同工作岗位的最高薪水

           selectdeptno,job,max(sal) as maxsal from emp group by deptno,job;

 

       2.5案例:找出每一个工作岗位(除manager)的最高薪水

           selectjob,max(sal) from emp wherejob <> ‘manager’group by job;

 

       2.6案例:找出每一个工作岗位的平均薪水,要求显示平均薪水大于1500。

           selectjob,avg from emp where avg(sal)> 1500 group by job;

           where关键字后不可以直接使用分组函数。

分组函数必须在分组完成后执行,而分组需要group by,group by 要在where后执行

          

    3、having

        having和where功能都是为了完成数据的过滤,where和having后面都是添加条件。

        where在group by之前完成过滤,having在group by之后完成过滤。

 

 

        案例:找出每一个工作岗位的平均薪水,要求显示平均薪水大于1500。

select job,avg(sal) fromemp group by job having avg(sal)> 1500;

 

       原则:尽量在where中过滤,无法过滤的数据,通常都是需要先分组之后再过滤,这个时候可以选择having。即能在where过滤就不要在having过滤,能在分组前过滤掉就要用where过滤掉,以提高效率。

 

    4、一个完整的DQL语句的总结:

                    select

                                           …

                    from

                                           …

                    where

                                           …

                    group by

                                           …

                    having

                                           …

                    order by

                                            …

 

                    第一:以上关键字顺序不能变,严格遵守

                    第二:执行顺序:

                             1、from              从某张表中检索数据

                             2、where            经过条件进行过滤

                             3、group by       然后分组

                             4、having           分组之后不满意再过滤

                             5、select             查询出来

                             6、order by        排序输出


DEPTNODNAMELOC
10ACCOUNTINGNEW YORK
20RESEARCHINGDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7369SMITHCLERK79021980/12/17800NULL20
7499ALLENSALESMAN76981981/2/20160030030
7521WARDSALESMAN76981981/2/22125050030
7566JONESMANAGER78391981/4/22975NULL20
7654MARTINSALESMAN76981981/9/281250140030
7698BLAKEMANAGER78391981/5/12850NULL30
7782CLARKMANAGER78391981/6/92450NULL10
7788SCOTTANALYST75661987/4/193000NULL20
7839KINGPERSIDENTNULL1981/11/175000NULL10
7844TURNERSALESMAN76981981/9/81500NULL30
7876ADAMSCLERK77881987/5/231100NULL20
7900JAMESCLERK76981981/12/3950NULL30
7902FORDANALYST75661981/12/33000NULL20
7934MILLERCLERK77821982/1/231300NULL10
GRADELOSALHISAL
17001200
212011400
314012000
420013000
530019999

展开阅读全文

没有更多推荐了,返回首页