mysql where和having的使用区别
- 先看表结构
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)
总结
- 按照这种顺序去写: where > group > having
- 用where的时,必须在原始表中有对应的字段名(不要求结果中有), 用having的时候,必须是结果中有对应的字段名(不要求原始表中有)
转自:stpeace
本作品采用 知识共享署名-相同方式共享 4.0 国际许可协议 进行许可。