mysql where和having的使用区别

最新推荐文章于 2024-02-01 20:19:43 发布

sreio

最新推荐文章于 2024-02-01 20:19:43 发布

阅读量163

点赞数

个人博客：https://blog.sreio.com

本文链接：https://blog.csdn.net/weialemon/article/details/100667667

版权

先看表结构

mysql> show create table tbxxx;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                       |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbxxx | CREATE TABLE `tbxxx` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL DEFAULT '',
  `type` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
mysql>

表中的数据：

mysql> select * from tbxxx;
+----+------+------+
| id | name | type |
+----+------+------+
|  1 | n1   |    1 |
|  2 | n1   |    2 |
|  3 | n1   |    3 |
|  4 | n2   |    2 |
|  5 | n3   |    3 |
|  6 | n4   |    4 |
+----+------+------+
6 rows in set (0.00 sec)

观察下面的语句

mysql> select name from tbxxx where name = 'n1';
+------+
| name |
+------+
| n1   |
| n1   |
| n1   |
+------+
3 rows in set (0.00 sec)
mysql> select name from tbxxx having name = 'n1';
+------+
| name |
+------+
| n1   |
| n1   |
| n1   |
+------+
3 rows in set (0.00 sec)

再比较下面的Sql语句：

mysql> select name from tbxxx where id = 1;
+------+
| name |
+------+
| n1   |
+------+
1 row in set (0.00 sec)
mysql> select name from tbxxx having id = 1;
ERROR 1054 (42S22): Unknown column 'id' in 'having clause'
mysql>

再比较下面的Sql语句：

mysql> select name as x from tbxxx where x = 'n1';
ERROR 1054 (42S22): Unknown column 'x' in 'where clause'
mysql> select name as x from tbxxx having x = 'n1';
+----+
| x  |
+----+
| n1 |
| n1 |
| n1 |
+----+
3 rows in set (0.00 sec)

再比较下面的Sql语句：

mysql> select name , count(name) as x from tbxxx group by name;
+------+---+
| name | x |
+------+---+
| n1   | 3 |
| n2   | 1 |
| n3   | 1 |
| n4   | 1 |
+------+---+
4 rows in set (0.00 sec)
 
mysql> 
mysql> select name , count(name) as x from tbxxx group by name where x = 3;
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 x = 3' at line 1
mysql> select name , count(name) as x from tbxxx group by name having x = 3;
+------+---+
| name | x |
+------+---+
| n1   | 3 |
+------+---+
1 row in set (0.00 sec)

再看：

mysql> select name , count(name) as x from tbxxx where name != 'n3' group by name having x > 0;
+------+---+
| name | x |
+------+---+
| n1   | 3 |
| n2   | 1 |
| n4   | 1 |
+------+---+
3 rows in set (0.00 sec)