7.DQL分组函数

啊条

于 2024-07-15 15:24:15 发布

阅读量506

点赞数 11

文章标签：数据库 sql mysql

本文链接：https://blog.csdn.net/weixin_64032491/article/details/140439955

版权

单元概述

本单元首先讲解MAX、MIN、AVG、SUM、COUNT5个分组函数的使用，其次讲解了具有分组功能的子句GROUP BY，最后针对于分组结果后过滤又讲解了HAVING子句。

1. 为什么使用分组函数

请思考如下需求？

查询所有员工的工资总和？
查询最高工资？
查询公司总人数？

以上这些需求，都有一个共性，返回值不再是某一个员工的信息，而是一组员工的统计信息。如求工资总和是所有员工的工资之和，用我们之前所学的SQL无法实现数据统计，所以本单元我们重点讲解在SQL中如何实现对数据进行分组，并对分组的结果进行统计。

2. 分组函数

2.1 分组函数语法

分组函数也叫聚合函数。是对表中一组记录进行操作，每组只返回一个结果。我们只讲如下5个常用的分组函数：

分组函数	含义
MAX	求最大值
MIN	求最小值
SUM	求和
AVG	求平均值
COUNT	求个数

分组函数的语法如下：

SELECT 列名, 分组函数

FROM 表名

WHERE 条件表达式

ORDER BY 列名;

说明：

1、分组函数写在SELECT子句上

2、WHERE、ORDER BY子句可以省略不写

2.2 MIN函数和MAX函数

MIN和MAX函数主要是返回每组的最小值和最大值，语法如下：

MIN( [ DISTINCT | ALL ] 列名 | 表达式 )

MAX( [ DISTINCT | ALL ] 列名 | 表达式 )

说明：

1、MIN和MAX可以用于任何数据类型

2、DISTINCT表示去掉组中的重复值，ALL表示不去掉重复值，省略不写默认为ALL

3、既可以写列名，也可以写表达式，通常写列名。

4、MIN和MAX函数会忽略掉NULL值后，再进行运算。

例：查询员工入职的最早日期和最晚日期

SELECT  MIN(hiredate), MAX(hiredate) 
FROM    emp;

例：查询最低工资和最高工资

SELECT  MIN(sal), MAX(sal) 
FROM    emp;

2.3 SUM函数和AVG函数

SUM和AVG函数分别返回每组的总和及平均值，语法如下：

SUM( [ DISTINCT | ALL ] 列名 | 表达式 )

AVG( [ DISTINCT | ALL ] 列名 | 表达式 )

说明：

1、SUM和AVG函数只能够对数值类型的列或表达式操作。

2、SUM和AVG函数会忽略掉NULL值后，再进行运算。

例：查询职位以SALES开头的所有员工工资和、平均工资。

SELECT    SUM(sal), AVG(sal) 
FROM    emp 
WHERE    job LIKE 'SALES%';

2.4 COUNT函数

COUNT函数用来返回满足条件的每组记录个数，语法如下：

1、COUNT(*)：返回满足条件的每组记录个数。

2、COUNT( [ DISTINCT | ALL ] 列名 | 表达式 )：返回满足条件的每组非空记录个数。

说明：

5个分组函数，除COUNT(*)不忽略掉空值外，其余函数都是忽略掉空值再进行运算。

例：查询部门30有多少个员工，可以有如下两种写法：

方法1：

SELECT    COUNT(*) 
FROM    emp 
WHERE    deptno = 30;

方法2：

SELECT    COUNT(empno)       --不建议写COUNT(*）
FROM    emp 
WHERE    deptno = 30;

例：查询部门30有多少个员工有津贴

SELECT    COUNT(comm) 
FROM    emp 
WHERE    deptno = 30;

通过这个例子可以看出，COUNT(comm) 是忽略掉空值的。

2.5 分组函数中的DISTINCT

DISTINCT会消除重复记录后再使用分组函数

例：查询有员工的部门数量。

SELECT  COUNT(DISTINCT deptno) 
FROM    emp;

2.6 分组函数中空值处理

刚才已经说过，除了COUNT（*）之外，其它所有分组函数都会忽略列中的空值，然后再进行运算。如果想让空值参与运算，那应该如何处理呢。在MySQL中提供了IFNULL函数，用法如下：

IFNULL(表达式1，表达式2)：表示如果表达式1的值是NULL则取表达式2的值，如果表达式1不为NULL则用表达式1本身值。

例：查询所有员工的平均津贴，没有津贴的按0处理。

SELECT AVG(comm) , COUNT(comm) 
FROM   emp;

以上方式，并没有把没有津贴的员工按0处理，参与求平均值的是4个员工。

SELECT AVG(IFNULL(comm,0)) , COUNT(IFNULL(comm,0))  
FROM   emp;

以上方式，通过使用IFNULL函数，把津贴是NULL的员工，按照0来处理，参与求平均值的是14个员工。

3. 分组查询

3.1 分组查询语法

上面的案例都是把一个表中的所有行做为一组来处理。如果想查询每个部门有多少人，每种岗位有多少人等等类似需求，就需要先把结果集按照某个列进行分组，然后再进行查询。

在SQL中，可以通过GROUP BY 子句，将表中满足WHERE条件的记录按照指定的列划分成若干个小组，划分的规则是：把满足条件的记录，在该列上相同的值做为一组。

语法如下：

SELECT 列名, 分组函数(列名)

FROM 表名

WHERE 条件表达式

GROUP BY 列名

ORDER BY 列名;

说明：

1、GROUP BY子句写在WHERE子句之后，其后的列名表示按照哪列进行分组

2、WHERE子句、ORDER BY 子句都可以省略不写

例：查询每个部门的编号，以及该部门所有员工的平均工资

SELECT   deptno, AVG(sal) 
FROM     emp 
GROUP BY deptno;

例：查询每种岗位上有多少个员工

SELECT  job , COUNT(empno) 
FROM emp 
GROUP BY job;

3.2 分组语句的错误写法

分析如下SQL的执行结果

SELECT  job , COUNT(empno) , sal 
FROM emp 
GROUP BY job;

本SQL的查询结果集前两列显示的每个职位下的员工个数，第三个列显示的哪个员工的工资呢？在MySLQ中默认显示的该组中第一个员工的工资，放在这里没有任何实际意义。在Oracle数据库中，这种写法会提示语法错误。因此，当有GROUP BY子句时，SELECT子句后面只能写：被分组的列、分组函数，这两类元素才有实际意义。

3.3 按多列分组查询

分组查询不但可以按照某一列进行分组，也可以按照多列进行分组。

例：查询每个部门每个岗位的工资总和。

SELECT   deptno, job, sum(sal) 
FROM     emp 
GROUP BY deptno, job;

3.4 多表查询分组查询

分组语句也可以和多表查询同时使用。

例：查询每个部门的部门编号，部门名称，部门人数，最高工资。

SELECT   dept.deptno, dname, count(empno), max(sal)  
FROM     emp ,dept 
WHERE emp.deptno = dept.deptno 
GROUP BY dept.deptno,dname;

注意：此处emp表和dept表都有deptno列，需要在列名前加上表名。

4. 过滤分组结果

4.1 HAVING子句

思考如下问题：查询部门人数大于3人的部门编号、部门人数。

"部门人数大于3"是一个条件，尝试一下是否可以写在WHERE子句中。

SELECT deptno,count(empno) 
FROM emp 
WHERE count(empno) >3 
GROUP BY deptno;

该SQL执行结束后，出现错误提示"Invalid use of group function"，表示组函数应用无效。原因在于WHERE子句在GROUP BY 子句之前执行，所以当WHERE子句执行的时候，尚未进行分组，也就无法在WHERE子句中使用分组函数。

在SQL中提供了HAVING子句，用来解决此问题，解决方式如下：

SELECT deptno,count(empno) 
FROM emp 
GROUP BY deptno 
HAVING count(empno) >3;

例：查询每个部门最高工资大于2900的部门编号，最高工资

SELECT   deptno, max(sal) 
FROM     emp 
GROUP BY deptno 
HAVING   max(sal)>2900;

例：查询职位以SALES开头，每种职位的工资和，并且要求工资和大于5000，按照工资和升序排列

SELECT    job, SUM(sal)  
FROM      emp 
WHERE      job NOT LIKE 'SALES%' 
GROUP BY  job 
HAVING    SUM(sal)>5000 
ORDER BY  SUM(sal);

总结:

1、WHERE子句用来过滤分组之前的记录，不能使用组函数

2、HAVING子句用来过滤分组之后的记录，可以使用组函数

5. SELECT语句6个子句的执行顺序

到现在为止，SELECT语句的6个子句都已经学习完毕，分别是：

SELECT子句、FROM子句、WHERE子句、GROUP BY子句、 HAVING子句、ORDER BY子句，书写直接按照此顺序就可以。那么这一条完整的SELECT子句发送到数据库服务器，执行顺序是如何的，可以通过案例来了解一下。

如下SQL语句：

SELECT    deptno,job,avg(sal) 
FROM      emp 
WHERE      job in ('SALESMAN','MANAGER','CLERK') 
GROUP BY  deptno,job 
HAVING avg(sal)>1000 
ORDER BY  3 DESC;

执行过程：

1、通过FROM子句中找到需要查询的表；

2、通过WHERE子句进行非分组函数筛选判断；

3、通过GROUP BY子句完成分组操作；

4、通过HAVING子句完成组函数筛选判断；

5、通过SELECT子句选择显示的列或表达式及组函数；

6、通过ORDER BY子句进行排序操作。

具体也可以通过下图了解：