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)

          再看：

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> 

         再看：

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)

         再看：

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)

       小结一下：

       1.  按照这种顺序去写： where > group > having

       2.  用where的时，必须在原始表中有对应的字段名(不要求结果中有)， 用having的时候，必须是结果中有对应的字段名(不要求原始表中有)

      遇到了，所以记录一下。

      不多说。