The difference between where and having in MySQL

Misunderstanding:

Don't be wrong, think HAVING and Group BY must be used with it.

Analysis of where and having usage:

1. WHERe and having can use scenes:

Select goods_price, goods_name from goods where goods_price> 100

Select goods_price, goods_name from goods having goods_price> 100

Explanation: The premise of having above is that I have screened the goods_price field, and the effect of where is in this case is equivalent.

But if there is no select goods_price, an error will be reported! Intersection Because Haveing is screened from the previous screening field, and where is directly screened from the field in the data table.

Therefore, you can see that where is aimed at the database file, and HAVING played for the results set.

In fact, the second SQL statement is equivalent to the following statements to better understand:

  Select goods_price, goods_name from goods where 1 having goods_price> 100

2. You can only use where, and you cannot use the situation of having:

Select goods_name, goods_number from goods where goods_price> 100

Select goods_name, goods_number from goods having goods_price> 100

Explanation: The second SQL statement reports an error. This is caused by the screening of goods_price. It can be seen that HAVING plays a role in the result set.

3. You can only use having, and you do not use where:

Query the average price of each Category_id product, and obtain product information with an average price greater than 1,000 yuan.

  Select Category_id, AVG (GOODS_PRICE) As Ag from Goods Group by Category_id Having AG> 1000

  Select Category_id, AVG (GOODS_PRICE) As Ag from Goods WHERE AG> 1000 Group by Category_id_id_id

Explanation: The second SQL statement reports an error, because there is no AG field in the data table of FROM Goods.

Review:

WHERE must follow the fields in the data table, where is the role of database files.

Haveing only query based on the results set queries before, so Haveing plays a role in the results set.

The comprehensive application case of where and having:

SELECT st_name,sum(score<60)  as k,avg(score) FROM tbl_score group by st_name having k>=2；