Mysql statement-execution sequence _ MySQL

1. what do developers need to know about databases? When databases are involved in development, only SQL statements are used. it is important to write and optimize SQL statements. the thick books in mysql target DBAs, we only need to learn 1. what do developers need to know about databases?When databases are involved in development, only SQL statements are used. it is important to write and optimize SQL statements. the thick books in mysql target DBAs, we only need to learn the SQL.

2. since writing SQL statements is the goal, how can I write SQL statements.Learn the following:

1) the execution sequence of Mysql. this is the core of SQL writing. some of the errors I encountered earlier were due to lack of understanding of them;

2) it is important to perform multi-table queries and optimization;

3) functions of SQL statements and functions provided by SQL facilitate many operations;

3. This article summarizes the execution sequence of Mysql statements:

1) Mysql syntax order, that is, when the following keywords exist in SQL, they must be in this order:

Select [distinct] fromjoin (such as left join) onwheregroup byhavingunionorder bylimit

2) the execution sequence of Mysql is as follows:

fromonjoinwheregroup byhavingselectdistinctunionorder by

3) Learn the preceding Mysql syntax sequence and execution sequence step by step:

Create the following table orders:

Note: All the following statements comply with the syntax sequence (and it is not possible that they do not comply with the syntax sequence, because the error pai_^ will be reported) and only analyze the execution sequence: (join and on are multi-table queries, which are placed at the end of the presentation)

Statement 1:

select a.Customerfrom orders awhere a.Customer='Bush' or a.Customer = 'Adams'

Analysis 1: First, find the table using the from statement, then obtain the qualified records based on the where statement, and finally select the required fields. The results are as follows:

Statement 2Groupby: the groupby must be used with the aggregate function.

select a.Customer,sum(a.OrderPrice)from orders awhere a.Customer='Bush' or a.Customer = 'Adams'group by a.Customer

Analysis 2: After the from and where statements are executed, execute group by and execute the sum aggregate function based on the group by field. In this case, the record is not repeated for the group by field, and the result is as follows:

Statement 3Having:

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 3: having is used to filter the results of group by because where is executed before group. The results are as follows:

Statement 4Distinct: (for testing, first change the OrderPrice of the Adams record in the database to 3000)

select distinct sum(a.OrderPrice)from orders awhere a.Customer='Bush' or a.Customer = 'Adams' or a.Customer = 'Carter'group by a.Customerhaving sum(a.OrderPrice) > 1700

Analysis 4: A record will be obtained (without distinct, there will be two identical records ):

Statement 5Union: merge all select results (remove duplicate records by default ):

select distinct sum(a.OrderPrice) As Order1from orders awhere a.Customer='Bush' or a.Customer = 'Adams' or a.Customer = 'Carter'group by a.Customerhaving sum(a.OrderPrice) > 1500unionselect distinct sum(a.OrderPrice) As Order1from orders awhere a.Customer='Bush' or a.Customer = 'Adams' or a.Customer = 'Carter'group by a.Customerhaving sum(a.OrderPrice) > 2000

Analysis 5: by default, duplicate records are removed (union all is used to retain duplicate records). The result is as follows:

Statement 6Order:

select distinct sum(a.OrderPrice) As order1from orders awhere a.Customer='Bush' or a.Customer = 'Adams' or a.Customer = 'Carter'group by a.Customerhaving sum(a.OrderPrice) > 1500unionselect distinct sum(a.OrderPrice) As order1from orders awhere a.Customer='Bush' or a.Customer = 'Adams' or a.Customer = 'Carter'group by a.Customerhaving sum(a.OrderPrice) > 2000order by order1

Analysis: sorting in ascending order. The results are as follows:

Statement 7Limit:

select distinct sum(a.OrderPrice) As order1from orders awhere a.Customer='Bush' or a.Customer = 'Adams' or a.Customer = 'Carter'group by a.Customerhaving sum(a.OrderPrice) > 1500unionselect distinct sum(a.OrderPrice) As order1from orders awhere a.Customer='Bush' or a.Customer = 'Adams' or a.Customer = 'Carter'group by a.Customerhaving sum(a.OrderPrice) > 2000order by order1limit 1

Analysis 7: obtain the first record in the result. The result is as follows:

Statement 8(Join and on are described below ):

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 (select 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 8: In fact, the join on statement connects one more table and two identical tables, both of which are Orders. The execution process is to add the table data specified by left join to the table specified by from according to the conditions specified by on after the from keyword is executed, and then execute the where clause.

Note:

1) using distinct should be written before all the fields to be queried, followed by several fields, which represent modifying several fields rather than following the distinct field;

2) after the execution of group by (with aggregate functions), the field after group by must be unique in the result, so you do not need to use distinct for this field;

The above is the Mysql statement-execution sequence _ MySQL content. For more information, please follow the PHP Chinese network (www.php1.cn )!