Shulou(Shulou.com)05/31 Report--

Editor to share with you an example analysis of the writing and execution order of mysql sentences, I believe that most people do not know much about it, so share this article for your reference. I hope you will gain a lot after reading this article. Let's learn about it together.

The order in which mysql statements are written and executed is very different.

The general writing order of mysql is as follows:

Select

From

Join

On

Where

Group by

Having

Order by

Limit

However, the order of execution is:

From

# Cartesian product

On

# filter the virtual table of Cartesian product

Join

# specify join, which is used to add data to the virtual table after on, for example, left join will add the remaining data from the left table to the virtual table

Where

# filter the above virtual tables

Group by

# grouping

# used for having clause judgment. In writing, this kind of aggregate function is written in having judgment

Having

# aggregate and filter the grouped results

Select

# the returned single column must be in the group by clause, except for aggregate functions

Distinct

Order by

# sort

Limit

Partial explanation:

1. The results of from:select * from table_1, table_2; and select * from table_1 join table_2; are the same, and they all represent the Cartesian product.

It is used to directly calculate the Cartesian product of two tables to get the virtual table VT1, which is the first operation performed by all select statements, and other operations are performed on this table, that is, what is completed by the from operation.

2. On: filter qualified data from VT1 table to form VT2 table

3. Join: add the data of this join type to the VT2 table. For example, left join will add the remaining data of the left table to the virtual table VT2 to form the VT3 table. If the number of tables is greater than 2, the steps 1-3 will be repeated.

4. Where: perform filtering (aggregate function cannot be used) to get the VT4 table

5. Group by: group the VT4 table to get the VT5 table; the columns used in post-processing statements, such as select,having, must be included in the group by condition, and aggregate functions are needed for those that do not appear

6. Having: filter the grouped data to get the VT6 table

7. Select: return the column to get the VT7 table

8. Distinct: used to retrieve the VT8 table

9. Order by: used to sort to get the VT9 table

10. Limit: return the number of rows required to get VT10

Note:

In the group by condition, each column must be a valid column, not an aggregate function

The null value is also returned as a grouping

Except for aggregate functions, the columns in the select clause must be in the group by condition

The above is all the contents of the article "sample Analysis of the order of Writing and execution of mysql sentences". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.