MySQL学习-group by和having

操作的表
mysql> select * from 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

group by

group by ： 按照某个字段或者某些字段进行分组。
having : having是对分组之后的数据进行再次过滤。
案例：找出每个工作岗位的最高薪资。
(找的是每一组的最高薪资，所以需要先进行分组)
    select job,max(sal) from emp group by job;

+-----------+----------+
| job       | max(sal) |
+-----------+----------+
| ANALYST   |  3000.00 |
| CLERK     |  1300.00 |
| MANAGER   |  2975.00 |
| PRESIDENT |  5000.00 |
| SALESMAN  |  1600.00 |
+-----------+----------+

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

多字段分组查询

找出每个工作岗位的最高薪资
    select ename,job,max(sal) from emp group by job;
这条语句能不能执行，Oracle不能，MySQL可以，但没意义

+-------+-----------+----------+
| ename | job       | max(sal) |
+-------+-----------+----------+
| SCOTT | ANALYST   |  3000.00 |
| SMITH | CLERK     |  1300.00 |
| JONES | MANAGER   |  2975.00 |
| KING  | PRESIDENT |  5000.00 |
| ALLEN | SALESMAN  |  1600.00 |
+-------+-----------+----------+

以上在mysql当中，查询结果是有的，但是结果没有意义，在Oracle数据库当中会报错。语法错误。
Oracle的语法规则比MySQL语法规则严谨。
记住一个规则：当一条语句中有group by的话，select后面只能跟分组函数和参与分组的字段。
每个工作岗位的平均薪资？
    select job,avg(sal) from emp group by job;

+-----------+-------------+
| job       | avg(sal)    |
+-----------+-------------+
| ANALYST   | 3000.000000 |
| CLERK     | 1037.500000 |
| MANAGER   | 2758.333333 |
| PRESIDENT | 5000.000000 |
| SALESMAN  | 1400.000000 |
+-----------+-------------+

多个字段能不能联合起来一块分组？
案例：找出每个部门不同工作岗位的最高薪资。
先查看数据：select deptno,job,sal from emp order by deptno;

+--------+-----------+---------+
| deptno | job       | sal     |
+--------+-----------+---------+
|     10 | MANAGER   | 2450.00 |
|     10 | PRESIDENT | 5000.00 |
|     10 | CLERK     | 1300.00 |
|     20 | CLERK     |  800.00 |
|     20 | MANAGER   | 2975.00 |
|     20 | ANALYST   | 3000.00 |
|     20 | CLERK     | 1100.00 |
|     20 | ANALYST   | 3000.00 |
|     30 | SALESMAN  | 1600.00 |
|     30 | SALESMAN  | 1250.00 |
|     30 | SALESMAN  | 1250.00 |
|     30 | MANAGER   | 2850.00 |
|     30 | SALESMAN  | 1500.00 |
|     30 | CLERK     |  950.00 |
+--------+-----------+---------+

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

where和having的选择
找出每个部门的最高薪资，要求显示薪资大于2900的数据。
写SQL语句的时候，有时别幻想着一步就能写出来，有时候读一句，写一句。要分步骤来写。
第一步：找出每个部门的最高薪资
    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。
where已经提前过滤了。
where搞不定的我们就用having

找出每个部门的平均薪资，要求显示薪资大于2000的数据。
第一步：找出每个部门的平均薪资
    select deptno,avg(sal) from emp group by deptno;
第二步：要求显示薪资大于2000的数据
    select avg(sal),deptno from emp group by deptno having avg(sal)>2000;

+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
+--------+-------------+

where后面不能使用分组函数：
    select deptno,avg(sal) from emp where avg(sal) > 2000 group by deptno; // 错误了。
这种情况只能使用having过滤。
having是groupby的搭档，只有groupby出现了，having才有意义

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

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

排序是最后的，排序最后输出。

去除重复记录

1、关于查询结果集的去重？

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

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

mysql> select ename,distinct job from emp;
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘distinct job from emp’ at line 1
以上的sql语句是错误的。
记住：distinct只能出现在所有字段的最前面。
    select distinct deptno,job from emp;

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

distinct放到最前面表示后面所有字段去除重复记录。这里表名去除deptno和job同时重复的去掉
案例：统计岗位的数量？
   select count(distinct job) from emp;
去重之后统计数量

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