day012--mysql中的聚合函数

聚合函数是一种可以传入多个数据，并返回一条数据的函数。聚合函数与单行函数相比，不同的地方就是传入的数据数量不同，单行函数只能传入一个数据，而聚合函数能够传入多条。但它们的相同点就是无论输入的数据有多少，返回的数据都只有一条。

目录

一，常见的聚合函数 

二，group by的使用

二，having的使用

---

一，常见的聚合函数 

聚合函数有许多，如：求平均值的avg函数，求和函数sum函数，求最小值函数min函数，求最大值函数max函数等，值得我们关注的是，聚合函数本身是具有去空值的功能的，当我们的数据表中含有空值时，聚合函数会将其去掉，因此空值并不会影响到我们聚合函数的计算。如下我们先使用avg，sum，min，max函数来查看员工表中的平均工资，所有员工的工资总和，最大工资数及最小工资数：

mysql> select avg(emp_salary),sum(emp_salary),max(emp_salary),min(emp_salary) from dep;

 可能会有人想到，既然我们找出来了最大工资和最小工资数，那么我们可以再去将所属的员工也显示出来吗？大家想到的是不是下面这样的：

 就是在查询的时候加上员工的id，但是它只会显示出来我们数据表中的第一条数据的id数，这样的结果无疑是错误的，并不能够实现我们的需求，那么我们该怎么做呢？很遗憾的是我们现在并不能显示对应的信息内容，我们需要使用到group  by分组和子查询对其处理，这里我们先留着这个问题。现在我们来认识group by关键字的使用。

二，group by的使用

group by关键字，我们可以直接见名知义，就是用来分组的。举个例子，现在我们有一张如下内容数据的表，里面包含有员工id，部门id，部门名，员工工资，员工的岗位： 

 当我们想要查询各部门的平均工资是多少，这个时候我们就需要确定下我们的分组依据，例如我们这里就以部门名为分组依据（相同部门为一组，然后再进行求平均数）：

mysql> select  avg(emp_salary) as avg from dep group by  dep_name;

 如上我们可以看到，求出来的平均数小数位很多，现在我们使用round函数进行四舍五入，保留2位后的结果：

如上我们可以看到有两条数据显示出来，但是可读性不怎么好，因为我们不知道求出来的平均值是什么部门的，因此我们可以在select查询的时候指定一下部门名的字段：

 group by后面并不是只能接一个字段，而是可以接两个字段，实现二次分组：

mysql> select  dep_name,emp_job,avg(emp_salary) as avg from dep group by  dep_name,emp_job;

如上我们先使用部门名进行分组，接着再在相同的部门里面将不同的岗位分组，最后才是求平均值

因为存在一条数据的字段值为无限循环小数，这个时候我们可以像之前那样使用round四舍五入函数保留两位小数：

mysql> select  dep_name,emp_job,round(avg(emp_salary),2) as avg from dep group by  dep_name,emp_job;

观察上面的分组，其实我们可以直接拿员工的工资岗位来进行分组，再计算平均值：

mysql> select  dep_name,emp_job,truncate(avg(emp_salary),2) as avg from dep group by  emp_job;

注：truncate函数为截断函数，不会像四舍五入那样可以逢五进一，而是直接进行了截断。

group by关键字需要写在where关键字之后，但是可以写在order  by及limit关键字之前。

二，having的使用

having关键字的作用和where关键字类似，都是用于过滤条件，但是having关键字的里面可以包含聚合函数，而我们的where函数里面不能包含聚合函数，不然就会报错。

例如，当我们想要查询在部门表中平均工资大于18000的部门名是哪些，这个时候我们的过滤条件就不可避免的要包含有聚合函数avg，如果我们不使用having关键字，而是像之前那样依旧使用where来作为过滤的关键字，结果将会如下报错：

mysql> select  dep_name  from dep where  avg(emp_salary)>10000;

 如上我们可以看到该语句就会报错，且给我们提示：非法使用group函数，这个时候我们就会纳闷了，哎，我也没有group关键字的使用，为什么会出现这个提示呢？究其原因是因为我们的聚合函数要想和我们表格中的字段名dep_name联系起来，就需要使用到group by关键字。因为在MySQL中如果查询语句中含有聚合函数，那么非聚合函数的部分即字段名要想显示出来，就必须使用group by关键字来进行分组。那我们加上group by关键字之后就大功告成了吗？

mysql> select  dep_name  from dep where  avg(emp_salary)>10000 group by dep_name;

 很显然，依旧不行，那么这个又是因为什么？主要还是因为在MySQL中的聚合函数不能接在where关键字后面，而是另外一个关键字‘having’后面，并且需要注意的是having的使用必须是我们的语句已经经过了分组后才能进行having关键字过滤，即having写在group by之后。如下：

mysql> select  dep_name ,avg(emp_salary)  as avg1 from  dep group by dep_name  having avg1>10000;

 这样我们就查询出来了平均工资大于10000的部门名。

现在我们再来总结一下这些关键字的书写位置：

select ....from...where/join  on  

group  by ...

having ...

order by...

limit....

还有一点需要注意的就是：当我们的过滤条件里面没有聚合函数的话，建议写在where关键字之后，对于MySQL来说，它是先去执行找表的操作【并使用where关键字进行过滤】，找到表之后才是去查询字段，最后再将我们查询出来的数据进行排序或分页，

因为where关键字的使用在having之前，所以where关键字的执行效率比having高，能够将我们的非聚合函数的过滤条件接在where关键字之后。

举个例子，我们的需求如下：

查询‘算法岗’员工工资大于在‘算法岗’这个职位工作的员工平均工资的员工是谁。

mysql> select emp_id,dep_name,emp_salary,avg(emp_salary) as avg1 from dep where emp_job='算法岗'group by dep_name having emp_salary > avg1;

 如上我们可以看到返回了算法岗上工资最高的员工id，所在的部门名，工资等消息。那么有一个疑问就是：我们先使用where关键字过滤条件来求平均工资的结果，和之前直接没有where关键字过滤的结果会一样吗？我们可以先思考一下

 毫无疑问，两者是不同的，我们加了where  emp_job='算法岗'这个过滤条件，因此计算的平均值也是在算法岗里面，而不是之前的的算法岗＋架构＋后端＋前端再求平均值，如下：

关于聚合函数，group  by，having有问题的请在评论区留言。