SQL statement order BY, group by, having, where

Baidu knows:
1.order by is sorted by field ... Fields can be followed by DESC in descending order. ASC Ascending: Default to Ascending
2.group by is a group query

The difference is that the common having is with GROUP by ... group By...having ... Indicates grouping and re-condition filtering first
And if there is a where in front of groupby, it represents
This particular query in practice affects the results of the query.
PS: These keywords are sequential, Where...group By...having...order by  are optional, but if all is written out, it must be in this order.
================================

Anyway

First, Where

SELECT * from TableName where id= "2012";

is to look for records in TableName, id=2012.

The Where in this case filters the results of the query. Only records that satisfy the conditions in the WHERE clause are queried.

Second, Group by

According to the above table, demand: Find out the average price of apple in China,japan,usa, what do you do?

You can do this:select AVG (price) from TableName where fruitname= "Apple";

Group by is generally used with some aggregation functions, such as the average function AVG, which we used above, and sum sum, the number count, Max Max, Min.

For the table above, ask for the maximum price per fruit:Select Fruitname,productplace,max from tablename GROUP by Fruitname

Group by also has an important object of cooperation, that is having.

Three, having

With GROUP BY, how do you filter the results after grouping? Having can solve this problem.

1. First look at an example: to find the average price of more than 3.0 fruit

If you use:

Select Fruitname,avg from tablename where avg. >=3.0 GROUP BY Fruitname;

Is it possible to meet our requirements?

The answer is no, because the WHERE clause cannot use an aggregate function. In order to solve this problem, we have to use our killer, he is having;

Rewrite as follows:select Fruitname,avg from tablename GROUP by Fruitname have avg (price) >=3.0;

2. We continue to see that there is another bizarre

Select Fruitname,avg TableName GROUP by Fruitname have price<2.0;

What do you think the results of this query would be?

Yes, orange 0.8; This is the only record.

Why is it? Why hasn't Apple been found out because Apple has a price beyond 2.0.

In addition, the operator in can also be used in the HAVING clause.

Select Fruitname,avg TableName GROUP by Fruitname have fruitname in ("Orange", "Apple");

Iv. Order by

Order BY is the process of reordering the results of a query, usually at the end of a query statement, can be a single column, or it can be sorted by multiple columns.

Divided into ascending ASC and descending desc, the default is ascending.

Order by single column is easier to sort, and not cumbersome for many columns.

SELECT * FROM tablename GROUP by Friutname ORDER by Fruitname Asc,price Desc.

SQL statement order BY, group by, having, where