The understanding of the select+ five-sentence of MySQL

Select can contain very complex, very rich logic, the most can test a person's logical thinking ability and the mastery of SQL statement, I think so, many times before the interview almost all died in its hands, so it is today's log, determined to learn it well.

where expression

We have to understand that which row of the expression is placed in the table and which row is taken out

=,>,<,>=,<=,!=/<>,and,or,between And,in,not

GROUP BY

Grouping, general and statistical functions with the use of only meaningful

Max,min,avg,count,sum

Having an expression

Data in tables, tables on hard disk or memory in file form

And where difference: where is useful for table files.

The results of the query can also be seen as a table whose files are generally temporarily present in the buffer

Having is a function of the results of the query.

So there should be an existing where, have had

Order by sort

You can sort on a field, order by field 1 [ASC] ascending, [desc] Descending

It is possible to sort a field without a result, you can use another field to continue sorting,

Order BY field 1 [Asc/desc], field 2 [Asc/desc]

Limit [offset,] N

Offset: Offsets

N: The item taken out

For example, to fetch a record of 3-5 rows of a table,

SELECT * from TableName limit 2, 3;

Note: Where->group By->having-order by->limit, must appear sequentially.

Sub-query (emphasis)

Where type subquery: The criteria for comparing the results of an inner query to the outer query

Almost this form:

SELECT * from tableName where tid = (select Tid from ...);

From sub-query: The results of the inner query as a temporary table for the outer layer to continue the query

Form: SELECT * FROM (SELECT * from ...) As temp group by ...;

Exists sub-query: The outer query results to the inner layer, to see if the inner layer of the query is set up

Form: Select oid from Outtable o where exists (select * from intable I where i.oid=o.oid);

The understanding of the select+ five-sentence of MySQL