The understanding of the select+ five-sentence of MySQL

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.