一. 简介
where
对查询数据进行过滤
having
用于对已分组的数据进行过滤
having和group by 必须配合使用(有having的时候必须出现group by)
二. 用法
where
select * from table where sum(字段)>100
having
select * from table group by 字段 having 字段>10
三.区别
1. 被执行的数据来源不同
where是数据从磁盘读入内存的时候进行判断,
而having是磁盘读入内存后再判断。
2. 执行顺序不一样
WHERE>HAVING
MySQL解释sql语言时的执行顺序:
SELECT DISTINCT <select_list> FROM <left_table> <join_type> JOIN <right_table> ON <join_condition> WHERE <where_condition> GROUP BY <group_by_list> HAVING <having_condition> ORDER BY <order_by_condition> LIMIT <limit_number>
3. where不可以使用字段的别名,但是having可以
eg:
select name as aa from student where aa > 100 (错误) select name as aa from student group name having aa > 100 (正确)
4. having能够使用聚合函数当做条件,但是where不能使用,where只能使用存在的列当做条件。
eg:
select * as aa from student where count(*) > 1 (错误) select * from student group name having count(name) > 1 (正确)