DQL语句之分组与过滤
说明：以下将使用mysql数据库管理系统中的mysql数据库中的emp表作为例子
本次内容接上次的学习笔记：
SQL中的DQL语句二(学习笔记三)
一、分组函数
分组函数又叫多行处理函数，多行处理函数只有5个;
avg 求平均
max 求最大值
min 求最小值
count 求一列有几个数
sum 求和
有以下几个特点：
1.所有的分组函数都是对"某一组"数据进行操作的且输入多行，最终输出的结果为一行
例：求出工资的最大值
select max(sal) from emp;
输出结果：
+----------+
| max(sal) |
+----------+
| 5000.00 |
+----------+
例：
select ename,max(sal) from emp;
输出结果：
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'mysql.emp.ENAME'; this is incompatible with sql_mode=only_full_group_by
2.多行处理函数自动忽略空(null)
注：所有数据库都是这样规定的，只要有NULL参与的运算结果一定是NULL
emp的表是这样的：
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
comm(补贴，福利)中的null是比较多的，我们以comm为例：
比如说，给每个员工涨10元补贴
select ename,(comm+10) as comm from emp;
输出结果：
+--------+---------+
| ename | comm |
+--------+---------+
| SMITH | NULL |
| ALLEN | 310.00 |
| WARD | 510.00 |
| JONES | NULL |
| MARTIN | 1410.00 |
| BLAKE | NULL |
| CLARK | NULL |
| SCOTT | NULL |
| KING | NULL |
| TURNER | 10.00 |
| ADAMS | NULL |
| JAMES | NULL |
| FORD | NULL |
| MILLER | NULL |
+--------+---------+
可以看到，所有带NULL的 加上10后都没有变化，这个非常直观的说明了只要有NULL参与的运算结果一定是NULL
求出comm的平均值；
select avg(comm) from emp;
输出结果
+------------+
| avg(comm) |
+------------+
| 550.000000 |
+------------+
这个例子也非常直观的说明了多行处理函数自动忽略空(NULL)
注：这里特别说明一下，因为多行处理函数是自动忽略NULL的，所以，一定不要这样写：
select sum(comm) from emp where comm is not null;
在使用多行处理行数时，不要在后面加where xxxx is not null
程序不会报错，但是画蛇添足，可以写但是完全没有必要。
3.分组函数一般都会和group by联合使用，这也是为什么它被称为分组函数的原因。
任何一个分组函数(count sum avg max min)都是在group by语句执行结束之后才会执行的。
没有写group by 相当于java的缺省一样，是会存在的，以整个表为一组。group by下面会讲。
说明：1.count (*)和count (具体的某个字段)，他们有什么区别?
count(*) :不是统计某个字段中数据的个数，而是统计总记录条数。(和某个字段无关)
count(具体的某个字段):表示统计具体的某个字段中不为NULL的数据总数量。
小tips：分组函数也能组合起来用
select count(*) , sum(sal) ,avg(sal) ,max (sal) ,min(sal) from emp;
单行处理函数
单行处理函数比较多，如果在这里一一赘述就会显得这篇博文太长了，这里只举个例子。
这里放一个其他人的博文链接：
MySQL学习笔记(2)–数据处理函数/单行处理函数
单行处理函数的特点与多行处理函数不同，它是输入一行，输出一行。
回顾一下这个：所有数据库都是这样规定的，只要有NULL参与的运算结果一定是NULL
那举个例子：求出所有员工的年薪：
因为上面这个原因，我们不能直接求出来,所以，这个时候引出我们的新函数 ifnull()空处理函数
ifnull是用法是：
ifnull(可能为NULL的数据,被当做什么处理)
那我们就可以这样写：
select ename,(sal+ifnull(comm,0))*12 from emp;
输出结果：
+--------+-------------------------+
| ename | (sal+ifnull(comm,0))*12 |
+--------+-------------------------+
| SMITH | 9600.00 |
| ALLEN | 22800.00 |
| WARD | 21000.00 |
| JONES | 35700.00 |
| MARTIN | 31800.00 |
| BLAKE | 34200.00 |
| CLARK | 29400.00 |
| SCOTT | 36000.00 |
| KING | 60000.00 |
| TURNER | 18000.00 |
| ADAMS | 13200.00 |
| JAMES | 11400.00 |
| FORD | 36000.00 |
| MILLER | 15600.00 |
+--------+-------------------------+
二、group by(分组查询)与having(过滤)
group by：按照某个字段或者某些字段进行分组。
having：having是对分组之后的数据进行再次过滤
重点：关于SQL语句的执行顺序问题
select 5
...
from 1
...
where 2
....
group by 3
....
having 4
....
order by 6
...
注：分组函数是在group by之后执行
没有写group by 时，实际上相当于默认存在，以整个表作为一个组
这个时候，就会出现一些问题，比如说这样：
选出大于平均工资的员工：
select ename,sal from emp where sal > avg(sal);
就会报错：
ERROR 1111 (HY000): Invalid use of group function
正确的写法应该是：
select ename,sal from emp where sal > (select avg(sal) from emp);
或者，求出平均工资后，再将平均工资代入where sal > xxx中。
注：注意这个书写顺序与执行顺序，很容易搞错。
还有除了select from 是必须写的外的，其他应该根据需求进行组合。
1.group by 的用法
group by：按照某个字段或者某些字段进行分组。
注意：分组函数一般都会和group by联合使用，这也是为什么它被称为分组函数的原因。
并且任何一个分组函数(count sum avg max min)都是在group by语句执行结束之后才会执行的。
案例:找出每个工作岗位的最高薪资。
select max(sal),job from emp group by job;
输出结果：
+----------+-----------+
| max(sal) | job |
+----------+-----------+
| 1300.00 | CLERK |
| 1600.00 | SALESMAN |
| 2975.00 | MANAGER |
| 3000.00 | ANALYST |
| 5000.00 | PRESIDENT |
+----------+-----------+
那能不能找出这些工资对应的人呢？
select ename，max(sal),job from emp group by job;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mysql.emp.ENAME' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
是不行的
注意：当一条语句中有group by的话，select后面只能跟分组函数和参与分组的字段。
多字段分组查询
这是一个比较重要的点，一般来说，把需求拆分，理清思路，并不是很难.
举例：
找出每个部门不同工作岗位的最高薪资。
在这里我们就要将需求拆解，理清思路。
首先，我们还是要参照一下，前面的执行顺序。在这里我在放过来。
select 5
...
from 1
...
where 2
....
group by 3
....
having 4
....
order by 6
注：分组函数是在group by之后执行
先where 进行筛选，然后是group by 进行分组，其次可以对过滤分组后的表再次进行筛选，最后进行排序。
那么，这个东西就好写了。
找出每个部门不同工作岗位的最高薪资。
首先，我们可以先分组，这里需要对两种数据进行分组，第一个是部分，第二个是工作岗位。
select job,deptno from emp group by deptno,job;
然后再找出每个部门不同工作岗位的最高薪资。
select job,deptno,max(sal) from emp group by deptno,job order by deptno;
+-----------+--------+----------+
| job | deptno | max(sal) |
+-----------+--------+----------+
| CLERK | 10 | 1300.00 |
| MANAGER | 10 | 2450.00 |
| PRESIDENT | 10 | 5000.00 |
| ANALYST | 20 | 3000.00 |
| CLERK | 20 | 1100.00 |
| MANAGER | 20 | 2975.00 |
| CLERK | 30 | 950.00 |
| MANAGER | 30 | 2850.00 |
| SALESMAN | 30 | 1600.00 |
+-----------+--------+----------+
having 与 where
having 和 where 两者的作用都是对表进行筛选，两者的区别在于 where 在group by 之前， having 在group by 之后。也就是说，
当需要在分组前进行筛选时，使用where；
当需要在分组后筛选时，选择having；
当无论分组前筛选，还是分组后筛选没有区别时，选择 having 与 选择 where 的最终结果是没有区别的。
这里建议此时选择使用 where ，因为当你筛选过后，group by 进行分组需要操作的量就小了。
这里来几个例子：
使用where 与使用 having 没有区别的。
找出每个部门的最高薪资，要求显示薪资大于 2900 的数据。
这里就可以有两种写法。
select max(sal),deptno from emp group by deptno having max(sal) > 2900;
select max(sal),deptno from emp where sal > 2900 group by deptno;
必须要使用 having的
例：
找出每个部门的平均薪资，要求显示薪资大于2000的数据。
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;