mysql优化教程having和where的区别
mysql优化教程having和where的区别where是对原始数据进行筛选,having是对记录集进行筛选。
代码运行:
-- 查询女生
mysql> select * from stu where stusex='女';
+--------+----------+--------+--------+---------+------------+------+------+
| stuNo| stuName| stuSex | stuAge | stuSeat | stuAddress | ch | math |
+--------+----------+--------+--------+---------+------------+------+------+
| s25303 | 李斯文 | 女 | 22 | 2 | 北京 | 55 | 82 |
| s25305 | 诸葛丽丽 | 女 | 23 | 7 | 河南 | 72 | 56 |
| s25319 | 梅超风 | 女 | 23 | 5 | 河北 | 74 | 67 |
| s25321 | Tabm | 女 | 23 | 9 | 河北 | 88 | 77 |
+--------+----------+--------+--------+---------+------------+------+------+
4 rows in set (0.00 sec)
-- 查询女生
mysql> select * from stu having stusex='女';
+--------+----------+--------+--------+---------+------------+------+------+
| stuNo| stuName| stuSex | stuAge | stuSeat | stuAddress | ch | math |
+--------+----------+--------+--------+---------+------------+------+------+
| s25303 | 李斯文 | 女 | 22 | 2 | 北京 | 55 | 82 |
| s25305 | 诸葛丽丽 | 女 | 23 | 7 | 河南 | 72 | 56 |
| s25319 | 梅超风 | 女 | 23 | 5 | 河北 | 74 | 67 |
| s25321 | Tabm | 女 | 23 | 9 | 河北 | 88 | 77 |
+--------+----------+--------+--------+---------+------------+------+------+
4 rows in set (0.00 sec)
-- 查询女生姓名
mysql> select stuname from stu where stusex='女';
+----------+
| stuname|
+----------+
| 李斯文 |
| 诸葛丽丽 |
| 梅超风 |
| Tabm |
+----------+
4 rows in set (0.00 sec)
-- 使用having报错,因为结果集中没有stusex字段
mysql> select stuname from stu having stusex='女';
ERROR 1054 (42S22): Unknown column 'stusex' in 'having clause'
页:
[1]