Shulou(Shulou.com)06/01 Report--

First, misunderstandings:

Don't mistakenly think that having and group by must be used together.

II. Analysis of the usage of where and having:

1. Scenarios in which both where and having can be used:

Select goods_price,goods_name from goods where goods_price > 100

Select goods_price,goods_name from goods having goods_price > 100

Explanation: the above having works on the premise that I have filtered out the goods_price field, in which case the effect is equivalent to where.

But if there is no select goods_price, it will be wrong! Because having is re-filtered from previously filtered fields, while where is filtered directly from fields in the data table.

So you can see that where works for database files, while having works for result sets.

In fact, the second sql statement is equivalent to the following statement that is easier to understand:

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

two。 Only where can be used, but not 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 reported an error because the goods_price was not filtered earlier, and it is more obvious that having is working on the result set.

3. You can only use having, not the where case:

Query the average price of each category_id commodity and obtain the commodity information with an average price of more than 1000 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

Explanation: the second sql statement reported an error because there is no ag field in the from goods data table.

III. Summary:

Where is followed by fields in the data table, and where plays a role in database files.

On the other hand, having only queries again based on the result set queried earlier, so having works against the result set.

Fourth, the case of the comprehensive application of where and having:

Inquire about the average scores of students who fail 2 or more.

SELECT st_name,sum (score=2

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.