Execution order of Mysql statement

1. Such a question, as a developer, what should he master about the database? In the development process, the database is involved, basically only sql statements are used. How to write sql and optimize it is very important. Those thick books of mysql are aimed at DBA s, and we only need to learn the sql in them.

2. Since it is the goal to write sql, how to write sql well? Learn the following:

1) The execution order of MySQL, which is the core of writing sql, some errors encountered before are due to the ignorance of it;

2) How to query and optimize multiple tables is an important part;

3) The function of sql statement, the function provided by sql is convenient for many operations;

3. This article summarizes the learning of Mysql statement execution order:

1)Mysql syntax order, that is, when the following keywords exist in sql, they should maintain the order:

select[distinct]
from
join(as left join)
on
where
group by
having
union
order by
limit


2) The execution order of MySQL is as follows:

from
on
join
where
group by
having
select
distinct
union
order by


3) Learn from the above Mysql syntax order and execution order step by step:
Create the following table orders:

Note: all the following statements conform to the syntax order (it is also impossible to fail, because an error will be reported). Only the execution order is analyzed: (join and on belong to multi table query, which will be shown at the end)

Statement 1:

select a.Customer
from orders a
where a.Customer='Bush' or a.Customer = 'Adams'


Analysis 1: first, find the table in the from statement, then get the qualified records according to where, and finally select the required fields. The results are as follows:

Statement 2 group by: group by should be used with aggregate function

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


Analysis 2: after executing from, where, execute group by. At the same time, execute sum as the aggregate function according to the field of group by. In this way, the records obtained are not duplicate for the group by field. The results are as follows:

Statement 3 having:

select a.Customer,sum(a.OrderPrice)
from orders a
where a.Customer='Bush' or a.Customer = 'Adams'
group by a.Customer
having sum(a.OrderPrice) > 2000


Analysis 3: since where is executed before group, how to filter the results of group by uses having. The results are as follows:


Statement 4 distinct: (to test, first change the OrderPrice of the Adams record in the database to 3000)

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


Analysis 4: one record will be obtained (without distinct, it will be two same records):


Statement 5 union: completely merge the results of select (remove duplicate records by default):

select distinct sum(a.OrderPrice) As Order1
from orders a
where a.Customer='Bush' or a.Customer = 'Adams' or a.Customer = 'Carter'
group by a.Customer
having sum(a.OrderPrice) > 1500
union
select distinct sum(a.OrderPrice) As Order1
from orders a
where a.Customer='Bush' or a.Customer = 'Adams' or a.Customer = 'Carter'
group by a.Customer
having sum(a.OrderPrice) > 2000


Analysis 5: remove duplicate records by default (use union all if you want to keep duplicate records). The results are as follows:


Statement 6 order by:

select distinct sum(a.OrderPrice) As order1
from orders a
where a.Customer='Bush' or a.Customer = 'Adams' or a.Customer = 'Carter'
group by a.Customer
having sum(a.OrderPrice) > 1500
union
select distinct sum(a.OrderPrice) As order1
from orders a
where a.Customer='Bush' or a.Customer = 'Adams' or a.Customer = 'Carter'
group by a.Customer
having sum(a.OrderPrice) > 2000
order by order1


Analysis: sort in ascending order, and the results are as follows:


Statement 7 limit:

select distinct sum(a.OrderPrice) As order1
from orders a
where a.Customer='Bush' or a.Customer = 'Adams' or a.Customer = 'Carter'
group by a.Customer
having sum(a.OrderPrice) > 1500
union
select distinct sum(a.OrderPrice) As order1
from orders a
where a.Customer='Bush' or a.Customer = 'Adams' or a.Customer = 'Carter'
group by a.Customer
having sum(a.OrderPrice) > 2000
order by order1
limit 1


Analysis 7: take out the first record in the result, and the result is as follows:

Statement 8 (basically finished above, join and on are as follows):

select distinct sum(a.OrderPrice) As order1,sum(d.OrderPrice) As order2
from orders a
left join (select c.* from Orders c) d 
on a.O_Id = d.O_Id
where a.Customer='Bush' or a.Customer = 'Adams' or a.Customer = 'Carter'
group by a.Customer
having sum(a.OrderPrice) > 1500
union
select distinct sum(a.OrderPrice) As order1,sum(e.OrderPrice) As order2
from orders a
left join (select c.* from Orders c) e 
on a.O_Id = e.O_Id
where a.Customer='Bush' or a.Customer = 'Adams' or a.Customer = 'Carter'
group by a.Customer
having sum(a.OrderPrice) > 2000
order by order1
limit 1


Analysis 8: in fact, the above statement join on is to connect more than one table, and it is the same two tables, both of which are Orders. The execution process is to attach the table data specified by left join to the table specified by from according to the conditions specified by on after executing the from keyword, and then execute the where clause.
Note:

1) Using distinct should be written in front of all fields to be queried, followed by several fields, which means modifying several fields, rather than following the distinct fields;

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

 

Published 10 original articles, won praise 3, visited 1338
Private letter follow

Tags: SQL MySQL Database

Posted on Thu, 12 Mar 2020 06:11:58 -0400 by hoogeebear