Where, group by, having, limit order

An SQL statement usually produces multiple temporary views, so the execution sequence of these keywords is very important, because you must understand whether this keyword is used to operate the fields before the corresponding view is formed or the temporary View is formed. This issue is especially important when the view with an alias is used.
 
The Group By and Having, Where, Order by keywords are executed in the following Order: Where, Group By, Having, Order.
 
Tip: When writing SQL statements, pay attention to the difference between <> and (). In the windows DOS command, they are about the same length and must be paid special attention.

RE: These clauses are used to filter rows, while column filtering is followed by select. I can also perform field operations on columns <2. You can also use aggregate functions. Sum count avg max min.

Tip: we can use count (*) and sum (score <60) to evaluate the data function of the table. Sometimes sum works better.

1. where

The where clause is followed by a condition to filter the required rows. The operators that can be followed by = and ,! =, <,>, <=,> =, In, and like (can be used together with the wildcard %, the effect will be better), .... and ........ AND, OR, NOT

For example, select id, name, score form student where score in (the highest score combination in each group );

2. The group by statement is used in combination with the aggregate function (also called the aggregate function sum count avg max min) to GROUP the result set based on one or more columns. Otherwise, it does not make much sense)

The principle of group by is that columns without aggregate functions in all columns after select must appear after group by. For example, we group the classification id of text, query the number of articles in each group. select articlecategory_id, count (*) from article group by articlecategory_id. If you use group, without an aggregate function, the result will be meaningless, if you think about it based on the actual situation, it becomes one-to-many (that is, a group corresponds to multiple objects (that is, multiple rows ), then the display of multi-row data for a group is changed to the display of the group and the first row of data. Therefore, we should use the aggregate function (the aggregate function is to change multiple to one ), that is, to filter the multi-row data to obtain the desired data. For example, you can use count to calculate the total number of forum group posts, or sum to calculate the total number of replies of Forum group posts, you can use max to calculate the posts most frequently replied by Forum groups. Using min to calculate the minimum number of replies of Forum groups (it doesn't make much sense, and the cheapest product in the mall makes sense ), avg can be used to calculate the weekly average access volume (average price of all commodities in the mall) of the Forum.
3. having is used for the query results and can only operate on the columns in the results. Unlike where, where is used for the original table (that is, the field of the table following from) play a role. The aggregate function can be used in having. Set conditions for the group by clause, similar to the where method for setting conditions for select statements. Having search conditions can include set function expressions. In addition, the search condition is the same as the where search condition.

Select articlecategory_id, count (*) from article group by articlecategory_id having count (*) <5;

4. order by sorting (you can sort multiple fields)

You can sort the Fields. The order by field 1 [asc] is in ascending order, and the order by field 1 is in descending order.

5. limit on items obtained by limit

Syntax: limit offset, N

Limit 5 limit;

Ps

In SQL statements, the following conditions are generally displayed in the order where-> group by-> having-order by-> limit