Mysql Statement Execution Order

Reprinted from: http://blog.csdn.net/jintao_ma/article/details/51253356

1. What do you need to know about the database as a developer? In the development of the database, basically only the SQL statements, how to write SQL and optimize it is more important, those MySQL thick book for the DBA, we just need to learn the SQL in it.

2. Since writing SQL is a goal, how can you write sql well. Learn the following points:

1) The execution order of MySQL, this is the core of writing SQL, some of the errors encountered before is because they do not understand;

2) How to make multi-table query, optimization, this is a very important part;

3) SQL statement functions, SQL provides functions to facilitate a lot of operations;

3. This article summarizes the sequence of MySQL statement execution:

1) MySQL syntax order, that is, when the following keywords exist in SQL, they want to maintain the order:

SELECT[DISTINCT]  from  join (such as Left join) on where the group by has  Union  ORDER BY  limit  

　　

2) MySQL execution order, that is, at execution time, SQL is executed in the following order:

From  in  join  where  GROUP by  have  select  distinct  Union  ORDER BY  

3) for the above MySQL syntax sequence and execution sequence, the gradual study:

Create the following form orders:

Note: All of the following statements conform to the syntax order (and are not likely to be inconsistent because they will error ^_^) and only analyze their order of execution: (Join and on are multi-table queries, put in the last show)

Statement one:

[HTML]View PlainCopy  

1. Select A.customer
2. From Orders a
3. where a.customer=' Bush ' or A.customer = ' Adams '

Analysis One: The first is the from statement to find the table, and then according to where to get the matching records, and finally select the required fields, the result is as follows:

Statement two groupby:groupby to be used with aggregate functions

[HTML]View PlainCopy 

1. Select A.customer,sum (A.orderprice)
2. From Orders a
3. where a.customer=' Bush ' or A.customer = ' Adams '
4. GROUP BY A.customer

Analysis Two: After the execution of the From,where, the group by is executed, and the aggregate function of sum is executed according to the group by field. The resulting record is not duplicated for the field of group by, and the result is as follows:

statement three have:

[HTML]View PlainCopy 

1. Select A.customer,sum (A.orderprice)
2. From Orders a
3. where a.customer=' Bush ' or A.customer = ' Adams '
4. GROUP BY A.customer
5. Having sum (a.orderprice) >

Analysis three: Because where is executed before the group, how to filter the results of group by is used to have the following results:

statement four distinct: (for testing, the orderprice of the Adams record in the database is changed to 3000)

[HTML]View PlainCopy 

1. SELECT distinct sum (a.orderprice)
2. From Orders a
3. where a.customer=' Bush ' or A.customer = ' Adams ' or A.customer = ' Carter '
4. GROUP BY A.customer
5. Having sum (a.orderprice) > 1700

Analysis Four: Will get a record (no distinct, will be two the same record):

Statement Five Union: Merge The results of a select entirely (by default, duplicate records are removed):

[HTML]View PlainCopy 

2. SELECT distinct sum (a.orderprice) as Order1
4. From Orders a
6. where a.customer=' Bush ' or A.customer = ' Adams ' or A.customer = ' Carter '
8. GROUP BY A.customer
10. Having sum (a.orderprice) >
12. Union
14. SELECT distinct sum (a.orderprice) as Order1
16. From Orders a
18. where a.customer=' Bush ' or A.customer = ' Adams ' or A.customer = ' Carter '
20. GROUP BY A.customer
22. Having sum (a.orderprice) >

Analysis Five: The default is to remove duplicate records (want to keep duplicate records using union ALL), the results are as follows:

Statement six ORDER by:

[HTML]View PlainCopy  

2. SELECT distinct sum (a.orderprice) as Order1
4. From Orders a
6. where a.customer=' Bush ' or A.customer = ' Adams ' or A.customer = ' Carter '
8. GROUP BY A.customer
10. Having sum (a.orderprice) >
12. Union
14. SELECT distinct sum (a.orderprice) as Order1
16. From Orders a
18. where a.customer=' Bush ' or A.customer = ' Adams ' or A.customer = ' Carter '
20. GROUP BY A.customer
22. Having sum (a.orderprice) >
24. ORDER BY Order1

Analysis: Sorted in ascending order with the following results:

statement seven limit:

[HTML]View PlainCopy 

2. SELECT distinct sum (a.orderprice) as Order1
4. From Orders a
6. where a.customer=' Bush ' or A.customer = ' Adams ' or A.customer = ' Carter '
8. GROUP BY A.customer
10. Having sum (a.orderprice) >
12. Union
14. SELECT distinct sum (a.orderprice) as Order1
16. From Orders a
18. where a.customer=' Bush ' or A.customer = ' Adams ' or A.customer = ' Carter '
20. GROUP BY A.customer
22. Having sum (a.orderprice) >
24. ORDER BY Order1
26. Limit 1

Analysis VII: Take out the first 1 records in the results, the results are as follows:

Statement Eight (the above is basically finished, the following is join and on):

[HTML]View PlainCopy 

2. SELECT distinct sum (a.orderprice) as Order1,sum (D.orderprice) as Order2
4. From Orders a
6. Left join (select c.* from Orders c) d
8. On a.o_id = d.o_id
10. where a.customer=' Bush ' or A.customer = ' Adams ' or A.customer = ' Carter '
12. GROUP BY A.customer
14. Having sum (a.orderprice) >
16. Union
18. SELECT distinct sum (a.orderprice) as Order1,sum (E.orderprice) as Order2
20. From Orders a
22. Left join (select c.* from Orders c) E
24. On a.o_id = e.o_id
26. where a.customer=' Bush ' or A.customer = ' Adams ' or A.customer = ' Carter '
28. GROUP BY A.customer
30. Having sum (a.orderprice) >
32. ORDER BY Order1
34. Limit 1

Analysis Eight: The above statement actually joins on is a more connected table, and is two identical tables, are orders. The execution procedure is to append the table data specified by the left JOIN to the table specified by the From, and then execute the WHERE clause, after executing the FROM keyword, according to the conditions specified on.

Note:

1) Use distinct to write in front of all the fields you want to query, followed by several fields, which represent a few fields to be decorated, rather than the fields immediately following distinct;

2) After the group by IS executed (there is an aggregation function), the field after group by must be unique in the result, and there is no need to use distinct for this field;

Mysql Statement Execution Order