MySql中的数据查询语言（DQL）二：分组函数/聚合函数/多行处理函数和分组查询

一、分组函数/聚合函数/多行处理函数

1、概念

分组函数一共5个。分组函数自动忽略NULL
分组函数还有另一个名字：多行处理函数。
多行处理函数的特点： 输入多行，最终输出的结果是1行
记住：所有的分组函数都是对“某一组”数据进行操作的。

2、说明

注意：分组函数自动忽略空值，不需要手动的加where条件排除空值。

select count(*) from emp where xxx;符合条件的所记录总数。
select count(comm) from emp;	    comm这个字段中不为空的元素总数。

注意：分组函数不能直接使用在where关键字后面。

select ename,sal from emp where sal > avg(sal); //ERROR 1111 (HY000): Invalid use of group function

思考以上的错误信息：无效的使用了分组函数？
原因：SQL语句当中有一个语法规则，分组函数不可直接使用在where子句当中。why???
怎么解释？
因为group by是在where执行之后才会执行的。

3、count

count(*)和count(具体的某个字段)，他们有什么区别？
count(*): 不是统计某个字段中数据的个数，而是统计总记录条数。（和某个字段无关）
count(comm): 表示统计comm字段中不为NULL的数据总数量。
举例：
取得所有的员工数
Count(*)表示取得所有记录，忽略null，为null的值也会取得

mysql> select count(*) from emp;

±---------+
| count(*) |
±---------+
| 14 |
±---------+
1 row in set (0.05 sec)

取得津贴不为null员工数

mysql> select count(comm) from emp;

±------------+
| count(comm) |
±------------+
| 4 |
±------------+
1 row in set (0.00 sec)
采用count(字段名称)，不会取得为null的记录

4、sum：可以取得某一个列的和，null会被忽略

找出工资总和？

select sum(sal) from emp;

取得津贴的合计
select sum(comm) from emp where comm is not null;// 不需要额外添加这个过滤条件。sum函数自动忽略NULL。
取得薪水的合计（sal+comm)
select sum(sal+comm) from emp;//错误的，原因在于comm字段null值，所以无法计算，sum会忽略掉，正确的做法是将comm字段转换成0

select sum(sal+IFNULL(comm, 0)) from emp;

5、单行处理函数:

什么是单行处理函数？
输入一行，输出一行。
举例：
计算每个员工的年薪？
select ename,(sal+comm)*12 as yearsal from emp;
重点：所有数据库都是这样规定的，只要有NULL参与的运算结果一定是NULL。
使用ifnull函数：

select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;

ifnull() 空处理函数？
ifnull(可能为NULL的数据,被当做什么处理) ： 属于单行处理函数。

select ename,ifnull(comm,0) as comm from emp;

6、avg：取得某一列的平均值

举例：
取得平均薪水

select avg(sal) from emp;

找出工资高于平均工资的员工？

select ename,sal from emp where sal > avg(sal); //ERROR 1111 (HY000): Invalid use of group function

思考以上的错误信息：无效的使用了分组函数？
原因：SQL语句当中有一个语法规则，分组函数不可直接使用在where子句当中。why???
怎么解释？
因为group by是在where执行之后才会执行的。

select		5
..			
from			1
..
where			2
..
group by		3
..
having		4
..
order by		6
..

正确的：
第一步：找出平均工资

select avg(sal) from emp;

±------------+
| avg(sal) |
±------------+
| 2073.214286 |
±------------+
第二步：找出高于平均工资的员工

select ename,sal from emp where sal > 2073.214286;
select ename,sal from emp where sal > (select avg(sal) from emp);

7、max：取得某个一列的最大值

举例：
找出最高薪水的员工

mysql> select ename,sal from emp where sal = (select max(sal) from emp);

取得最高薪水
mysql> select max(sal) from emp;
取得最晚入职得员工
mysql> select max(hiredate) from emp;

8、min：取得某个一列的最小值

举例：
取得最低薪水

mysql> select min(sal) from emp;

取得最早入职得员工（可以不使用str_to_date转换）

mysql> select min(str_to_date(hiredate, '%Y-%m-%d')) from emp;

±---------------------------------------+
| min(str_to_date(hiredate, ‘%Y-%m-%d’)) |
±---------------------------------------+
| 1980-12-17 |
±---------------------------------------+
1 row in set (0.04 sec)

9、组合聚合函数

可以将这些聚合函数都放到select中一起使用

mysql> select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;

±---------±---------±------------±---------±---------+
| count(*) | sum(sal) | avg(sal) | max(sal) | min(sal) |
±---------±---------±------------±---------±---------+
| 14 | 29025.00 | 2073.214286 | 5000.00 | 800.00 |
±---------±---------±------------±---------±---------+
1 row in set (0.00 sec)

二、分组查询

分组查询主要涉及到两个子句，分别是：group by和having

1、group by：

按照某个字段或者某些字段进行分组。
举例：
取得每个工作岗位的工资合计，要求显示岗位名称和工资合计

mysql> select job,sum(sal) as sumSal from emp group by job;

案例：找出每个工作岗位的最高薪资。

mysql> select job,max(sal) from emp group by job;

每个工作岗位的平均薪资？

select job,avg(sal) from emp group by job;

多个字段能不能联合起来一块分组？
案例：找出每个部门不同工作岗位的最高薪资。

mysql> select max(sal),job,deptno from emp group by deptno,job;

注意：分组函数一般都会和group by联合使用，这也是为什么它被称为分组函数的原因。并且任何一个分组函数（count sum avg max min）都是在group by语句执行结束之后才会执行的。当一条sql语句没有group by的话，整张表的数据会自成一组。

select ename,max(sal),job from emp group by job;

以上在mysql当中，查询结果是有的，但是结果没有意义，在Oracle数据库当中会报错。语法错误。
Oracle的语法规则比MySQL语法规则严谨。
记住一个规则：当一条语句中有group by的话，select后面只能跟分组函数和参与分组的字段。

2、having :

having是对分组之后的数据进行再次过滤。

举例：
找出每个部门的最高薪资，要求显示薪资大于2900的数据。

第一步：找出每个部门的最高薪资

select max(sal),deptno from emp group by deptno;

±---------±-------+
| max(sal) | deptno |
±---------±-------+
| 5000.00 | 10 |
| 3000.00 | 20 |
| 2850.00 | 30 |
±---------±-------+

第二步：找出薪资大于2900

select max(sal),deptno from emp group by deptno having max(sal) > 2900; // 这种方式效率低。

±---------±-------+
| max(sal) | deptno |
±---------±-------+
| 5000.00 | 10 |
| 3000.00 | 20 |
±---------±-------+

select max(sal),deptno from emp where sal > 2900 group by deptno;  // 效率较高，建议能够使用where过滤的尽量使用where。

找出每个部门的平均薪资，要求显示薪资大于2000的数据。

第一步：找出每个部门的平均薪资

select deptno,avg(sal) from emp group by deptno;

第二步：要求显示薪资大于2000的数据

select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;

where后面不能使用分组函数：
select deptno,avg(sal) from emp where avg(sal) > 2000 group by deptno; // 错误了。
这种情况只能使用having过滤。

3、select语句总结

总结一个完整的DQL语句怎么写？

select		5
..
from			1	
..
where			2
..
group by		3
..
having		4
..
order by		6
..

一个完整的select语句格式如下
select 字段
from 表名
where …….
group by ………
having …….(就是为了过滤分组后的数据而存在的—不可以单独的出现)
order by ………

以上语句的执行顺序
首先执行where语句过滤原始数据
执行group by进行分组
执行having对分组数据进行操作
执行select出数据
执行order by排序

原则：能在where中过滤的数据，尽量在where中过滤，效率较高。having的过滤是专门对分组之后的数据进行过滤的。

4.distinct:关于查询结果集的去重

举例：

mysql> select distinct job from emp; // distinct关键字去除重复记录。

±----------+
| job |
±----------+
| CLERK |
| SALESMAN |
| MANAGER |
| ANALYST |
| PRESIDENT |
±----------+

mysql> select ename,distinct job from emp;
以上的sql语句是错误的。
记住：distinct只能出现在所有字段的最前面。

mysql> select distinct deptno,job from emp;//去重deptno与job两相连相同的

±-------±----------+
| deptno | job |
±-------±----------+
| 20 | CLERK |
| 30 | SALESMAN |
| 20 | MANAGER |
| 30 | MANAGER |
| 10 | MANAGER |
| 20 | ANALYST |
| 10 | PRESIDENT |
| 30 | CLERK |
| 10 | CLERK |
±-------±----------+

案例：统计岗位的数量？
select count(distinct job) from emp;

±--------------------+
| count(distinct job) |
±--------------------+
| 5 |
±--------------------+