select查询 having和where的区别

mysql> select avg(sal),deptno from emp where sal>1000;
+-------------+--------+
| avg(sal)    | deptno |
+-------------+--------+
| 2379.545455 |     30 |
+-------------+--------+
1 row in set (0.00 sec)

mysql>  select deptno from emp group by deptno having avg(sal)>1000;
+--------+
| deptno |
+--------+
|     10 |
|     20 |
|     30 |
+--------+
3 rows in set (0.00 sec)

mysql>  select sal,deptno from emp group by deptno having sal>1000;
+---------+--------+
| sal     | deptno |
+---------+--------+
| 2450.00 |     10 |
| 1600.00 |     30 |
+---------+--------+
2 rows in set (0.00 sec)

mysql>  select avg(sal),deptno from emp group by deptno having sal>1000;
ERROR 1054 (42S22): Unknown column 'sal' in 'having clause'
mysql> select sal,avg(sal),deptno from emp group by deptno having sal>1000;
+---------+-------------+--------+
| sal     | avg(sal)    | deptno |
+---------+-------------+--------+
| 2450.00 | 2916.666667 |     10 |
| 1600.00 | 1566.666667 |     30 |
+---------+-------------+--------+
2 rows in set (0.00 sec)

mysql> select avg(sal),deptno from emp group by deptno having sal>1000;
ERROR 1054 (42S22): Unknown column 'sal' in 'having clause'
mysql> select ename from emp where sal>3000;
+-------+
| ename |
+-------+
| king  |
+-------+
1 row in set (0.00 sec)

mysql> select deptno from emp group by deptno having sal>1000;
ERROR 1054 (42S22): Unknown column 'sal' in 'having clause'
mysql> select deptno from emp group by deptno having avg(sal)>1000;
+--------+
| deptno |
+--------+
|     10 |
|     20 |
|     30 |
+--------+
3 rows in set (0.00 sec)

mysql> select deptno from emp where sal>1000;
+--------+
| deptno |
+--------+
|     30 |
|     30 |
|     20 |
|     30 |
|     30 |
|     10 |
|     20 |
|     10 |
|     30 |
|     20 |
|     10 |
+--------+
11 rows in set (0.00 sec)

mysql> select deptno from emp where avg(sal)>1000;
ERROR 1111 (HY000): Invalid use of group function
mysql> select sal from map having sal>1000;
ERROR 1146 (42S02): Table 'test.map' doesn't exist
mysql>