【MySQL】select,having和where的区别（详解）

含义上来说，其实having和where的意思是相同的。
但是，having处理的是聚合后的表。
而where处理的是聚合前的表。
下面我们来举个例子。
首先我们创建了一张表：

mysql> select * from salary;
+----+------+-------------------+------------+
| id | name | pay_for_per_month | position   |
+----+------+-------------------+------------+
|  1 | z1   |             10000 | JAVA研发   |
|  2 | z2   |             11000 | JAVA研发   |
|  3 | z3   |             17000 | JAVA研发   |
|  4 | z4   |             18000 | c++研发    |
|  5 | z5   |              8000 | c++研发    |
|  6 | z6   |              6000 | 嵌入式开发 |
|  7 | z7   |              8000 | 嵌入式开发 |
|  8 | z8   |              9000 | 嵌入式开发 |
|  9 | z9   |             12000 | 算法工程师 |
| 10 | z10  |             15000 | 算法工程师 |
| 11 | z11  |             23000 | 算法工程师 |
| 12 | z12  |             33000 | 架构师     |
| 13 | z13  |             53000 | 架构师     |
| 14 | z14  |            153000 | 架构师     |
+----+------+-------------------+------------+
14 rows in set (0.00 sec)

然后我们来看一下having的用法：

mysql> select position,max(pay_for_per_month) from salary group by position;
+------------+------------------------+
| position   | max(pay_for_per_month) |
+------------+------------------------+
| JAVA研发   |                  17000 |
| c++研发    |                  18000 |
| 嵌入式开发 |                   9000 |
| 算法工程师 |                  23000 |
| 架构师     |                 153000 |
+------------+------------------------+
5 rows in set (0.00 sec)

mysql> select position,count(pay_for_per_month) from salary group by position;
+------------+--------------------------+
| position   | count(pay_for_per_month) |
+------------+--------------------------+
| JAVA研发   |                        3 |
| c++研发    |                        2 |
| 嵌入式开发 |                        3 |
| 算法工程师 |                        3 |
| 架构师     |                        3 |
+------------+--------------------------+
5 rows in set (0.00 sec)

mysql> select position,max(pay_for_per_month) from salary group by position having pay_for_per_month>10000;
ERROR 1054 (42S22): Unknown column 'pay_for_per_month' in 'having clause'
mysql> select position,max(pay_for_per_month) from salary group by position having pay_for_per_month>10000;
ERROR 1054 (42S22): Unknown column 'pay_for_per_month' in 'having clause'
mysql> select position,max(pay_for_per_month) from salary group by position having max(pay_for_per_month)>10000;
+------------+------------------------+
| position   | max(pay_for_per_month) |
+------------+------------------------+
| JAVA研发   |                  17000 |
| c++研发    |                  18000 |
| 算法工程师 |                  23000 |
| 架构师     |                 153000 |
+------------+------------------------+
4 rows in set (0.00 sec)

mysql> select position,count(pay_for_per_month) from salary group by position having count(id)>3;
Empty set (0.00 sec)

mysql> select position,count(pay_for_per_month) from salary group by position having count(id)>2;
+------------+--------------------------+
| position   | count(pay_for_per_month) |
+------------+--------------------------+
| JAVA研发   |                        3 |
| 嵌入式开发 |                        3 |
| 算法工程师 |                        3 |
| 架构师     |                        3 |
+------------+--------------------------+
4 rows in set (0.00 sec)

select position,pay_for_per_month from salary group by position having count(id)>2;
+------------+-------------------+
| position   | pay_for_per_month |
+------------+-------------------+
| JAVA研发   |             10000 |
| 嵌入式开发 |              6000 |
| 算法工程师 |             12000 |
| 架构师     |             33000 |
+------------+-------------------+
4 rows in set (0.00 sec)

我们可以看到having后面必须跟聚合函数的内容。

---

下面我们来看一下where的用法

mysql> select position,pay_for_per_month from salary where position like '架构师';
+----------+-------------------+
| position | pay_for_per_month |
+----------+-------------------+
| 架构师   |             33000 |
| 架构师   |             53000 |
| 架构师   |            153000 |
+----------+-------------------+
3 rows in set (0.00 sec)

mysql> select position,pay_for_per_month from salary where position !='架构师';
+------------+-------------------+
| position   | pay_for_per_month |
+------------+-------------------+
| JAVA研发   |             10000 |
| JAVA研发   |             11000 |
| JAVA研发   |             17000 |
| c++研发    |             18000 |
| c++研发    |              8000 |
| 嵌入式开发 |              6000 |
| 嵌入式开发 |              8000 |
| 嵌入式开发 |              9000 |
| 算法工程师 |             12000 |
| 算法工程师 |             15000 |
| 算法工程师 |             23000 |
+------------+-------------------+
11 rows in set (0.00 sec)

mysql> select position,pay_for_per_month from salary group by position having count(id)>2;
+------------+-------------------+
| position   | pay_for_per_month |
+------------+-------------------+
| JAVA研发   |             10000 |
| 嵌入式开发 |              6000 |
| 算法工程师 |             12000 |
| 架构师     |             33000 |
+------------+-------------------+
4 rows in set (0.00 sec)

mysql> select position,max(pay_for_per_month) from salary where position !='架构师';
+----------+------------------------+
| position | max(pay_for_per_month) |
+----------+------------------------+
| JAVA研发 |                  23000 |
+----------+------------------------+
1 row in set (0.00 sec)

mysql> select position,max(pay_for_per_month) from salary group by position where position !='架构 师';
ERROR 1064 (42000): 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 'where position !='架构师'' at line 1

mysql> select position,pay_for_per_month from salary group by position where position like '架构师';
ERROR 1064 (42000): 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 'where position like '架构师'' at line 1