where子句和having子句一样,都是用于条件判断的.这也是很多人区分不开的主要原因.不知道什么时候用什么的子句判断.既然定义了这两个判断子句,就肯定是有区别的,下面从四方面解释一下
1. where子句和having子句都可以使用的场景.
2. 只可以用where,不可以用having的情况.
3. 只可以用having,不可以用where情况.
4. 总结
1.where子句和having子句都可以使用的场景.
SELECT id,price from goodshaving id>1 LIMIT 0,5;
SELECTid,price from goods where id>1 LIMIT 0,5;
解释:在这种情况下和where的效果是等效的,但是如果没有筛选having所需字段(having id>1)就会报错! 因为having是从前筛选的字段再筛选,而where是从数据表中的字段直接进行的筛选的。
2. 只可以用where,不可以用having的场景
SELECT price from goods having id>1 LIMIT 0,5; (报错:1054 - Unknown column'id' in 'having clause')
由于having 是筛选组的.而查询语句没有将id这个字段筛选,所以这种场景下只能用where.如下:
SELECT price from goods where id>1 LIMIT 0,5;
3.只可以用having,不可以用where的场景
· GROUP BY 后只能 用having
SELECT id, AVG(price) avgprice,goodsName FROMgoods GROUP BY id having avgprice > 6000;
SELECT id, AVG(price)avgprice,goodsName FROM goods GROUP BY id where avgprice > 6000;
(错:1064 - You have an error in your SQL syntax; check the manual thatcorresponds to your MySQL server version for the right syntax to use near'where avgprice > 6000' at line 1)
但是WHERE可以这么使用 :SELECT id, AVG(price)avgprice,goodsName FROM goods WHERE id>100 GROUP BY id
· having能够使用统计函数,但是where不能使用
SELECT sum(price) fromgoods group by goodsName having sum(price)>210;
SELECT sum(price) from goods groupby goodsName where sum(price)>210;
(1064 - You have an error in your SQL syntax; check the manualthat corresponds to your MySQL server version for the right syntax to use near'where sum(price)>210' at line 1)
· having子句中可以使用字段别名,而where不能使用
SELECT id as asd, goodsName FROM goodshaving asd>5;
SELECTid as asd, goodsName FROM goods where asd>5; (1054 - Unknown column 'asd' in'where clause')