Mysql Statement Execution Order

The end of the paper is shallow

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]fromjoin (such as Left join) Onwheregroup Byhavingunionorder Bylimit

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

Fromonjoinwheregroup Byhavingselectdistinctunionorder 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:

Select A.customerfrom orders awhere 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

Select A.customer,sum (a.orderprice) from orders awhere a.customer= ' Bush ' or A.customer = ' Adams ' 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:

Select A.customer,sum (a.orderprice) from orders awhere a.customer= ' Bush ' or A.customer = ' Adams ' GROUP by a.customerhaving SUM (a.orderprice) > 2000

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)

SELECT distinct sum (a.orderprice) from orders awhere a.customer= ' Bush ' or A.customer = ' Adams ' or A.customer = ' Carter ' Grou P by a.customerhaving 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):

SELECT distinct sum (a.orderprice) as Order1from orders Awhere a.customer= ' Bush ' or A.customer = ' Adams ' or A.customer = ' C Arter ' GROUP by a.customerhaving sum (a.orderprice) > 1500unionselect distinct sum (a.orderprice) as Order1from orders AW Here a.customer= ' Bush ' or A.customer = ' Adams ' or A.customer = ' Carter ' GROUP by a.customerhaving sum (a.orderprice) > 20 00

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:

SELECT distinct sum (a.orderprice) as Order1from orders Awhere a.customer= ' Bush ' or A.customer = ' Adams ' or A.customer = ' C Arter ' GROUP by a.customerhaving sum (a.orderprice) > 1500unionselect distinct sum (a.orderprice) as Order1from orders AW Here a.customer= ' Bush ' or A.customer = ' Adams ' or A.customer = ' Carter ' GROUP by a.customerhaving sum (a.orderprice) > 20 00order by Order1

Analysis: Sorted in ascending order with the following results:

statement seven limit:

SELECT distinct sum (a.orderprice) as Order1from orders Awhere a.customer= ' Bush ' or A.customer = ' Adams ' or A.customer = ' C Arter ' GROUP by a.customerhaving sum (a.orderprice) > 1500unionselect distinct sum (a.orderprice) as Order1from orders AW Here a.customer= ' Bush ' or A.customer = ' Adams ' or A.customer = ' Carter ' GROUP by a.customerhaving sum (a.orderprice) > 20 00order by Order1limit 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):

SELECT distinct sum (a.orderprice) as Order1,sum (D.orderprice) as Order2from orders Aleft join (select c.* from orders C) d On a.o_id = D.o_idwhere a.customer= ' Bush ' or A.customer = ' Adams ' or A.customer = ' Carter ' GROUP by a.customerhaving sum (a . Orderprice) > 1500unionselect distinct sum (a.orderprice) as Order1,sum (E.orderprice) as Order2from orders Aleft join (s  Elect c.* from Orders C) E on a.o_id = E.o_idwhere a.customer= ' Bush ' or A.customer = ' Adams ' or A.customer = ' Carter ' group by a.customerhaving sum (a.orderprice) > 2000order by order1limit 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;

How to make a multi-table query in the next chapter

Mysql Statement Execution Order