找出没个工作岗位的最高薪资：
当 存在group by的时候，select 后面允许跟参加分组的字段和分组函数，其他都不能跟，比如 几个ename是错的！（虽然在mysql能过，但毫无意义）
mysql> select max(sal),job from emp group by job;
+----------+-----------+
| max(sal) | job |
+----------+-----------+
| 3000.00 | ANALYST |
| 1300.00 | CLERK |
| 2975.00 | MANAGER |
| 5000.00 | PRESIDENT |
| 1600.00 | SALESMAN |
+----------+-----------+
5 rows in set (0.03 sec)
多个字段能不能联合起来分组：
案例：找出每个部门不同工作岗位的最高薪资
mysql> select deptno,job,max(sal) from emp group by deptno,job;
+--------+-----------+----------+
| deptno | job | max(sal) |
+--------+-----------+----------+
| 10 | CLERK | 1300.00 |
| 10 | MANAGER | 2450.00 |
| 10 | PRESIDENT | 5000.00 |
| 20 | ANALYST | 3000.00 |
| 20 | CLERK | 1100.00 |
| 20 | MANAGER | 2975.00 |
| 30 | CLERK | 950.00 |
| 30 | MANAGER | 2850.00 |
| 30 | SALESMAN | 1600.00 |
+--------+-----------+----------+
- 找出每个部门的最高薪资，要求显示薪资大于2900：
mysql> select max(sal) from emp group by deptno having max(sal)>2900 ;
+----------+
| max(sal) |
+----------+
| 5000.00 |
| 3000.00 |
+----------+
2 rows in set (0.01 sec)
效率低！！！！！！！！！！
mysql> select max(sal) from emp where sal>2900 group by deptno;
+----------+
| max(sal) |
+----------+
| 5000.00 |
| 3000.00 |
+----------+
2 rows in set (0.00 sec)
参加分组的数据变少 效率高，能用where尽量用where
找出每个部门的平均薪资，要求显示薪资大于2000
mysql> select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
+--------+-------------+
2 rows in set (0.00 sec)
总结一个完整的DQL语句 select .. from..where.. group bt .. having ..order by..
5 1 2 3 4 6
