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

This article mainly introduces the example analysis of the execution order of SQL query statements, which is very detailed and has a certain reference value. Friends who are interested must read it!

The order in which SQL query statements are executed is as follows:

(7) SELECT (8) DISTINCT (1) FROM (3) JOIN (2) ON (4) WHERE (5) GROUP BY (6) HAVING (9) ORDER BY (10) LIMIT

Preliminary preparatory work

1. Create a new test database

Create database testData

2. Create a test table and insert the data as follows:

User table

Order form

Prepare SQL logical query test statements

SELECT a. Userkeeper as total_ordersFROM user as aLEFT JOIN orders as bON a.user_id count (b.order_id) as total_ordersFROM user as aLEFT JOIN orders as bON a.user_id = b.user_idWHERE a.city = 'beijing'GROUP BY a.user_idHAVING COUNT (b.order_id)

< 2ORDER BY total_orders desc 使用上述SQL查询语句来获得来自北京，并且订单数少于2的客户; 在这些SQL语句的执行过程中，都会产生一个虚拟表，用来保存SQL语句的执行结果 一、执行FROM语句 第一步，执行FROM语句。我们首先需要知道最开始从哪个表开始的，这就是FROM告诉我们的。现在有了和两个表，我们到底从哪个表开始，还是从两个表进行某种联系以后再开始呢？它们之间如何产生联系呢？--笛卡尔积 经过FROM语句对两个表执行笛卡尔积，会得到一个虚拟表，VT1（vitual table 1），内容如下： 总共有28（user的记录条数 * orders的记录条数）条记录。这就是VT1的结果，接下来的操作就在VT1的基础上进行 二、执行ON过滤 执行完笛卡尔积以后，接着就进行ON a.user_id = b.user_id条件过滤，根据ON中指定的条件，去掉那些不符合条件的数据,得到VT2如下： select * from user as a inner JOIN orders as b ON a.user_id = b.user_id; 三、添加外部行 这一步只有在连接类型为OUTER JOIN时才发生，如LEFT OUTER JOIN、RIGHT OUTER JOIN和FULL OUTER JOIN。在大多数的时候，我们都是会省略掉OUTER关键字的，但OUTER表示的就是外部行的概念。 LEFT OUTER JOIN把左表记为保留表：即左表的数据会被全部查询出来，若右表中无对应数据，会用NULL来填充：

RIGHT OUTER JOIN records the right table as a reserved table: that is, all the data in the right table will be queried, and if there is no corresponding data in the left table, it will be supplemented by NULL.

FULL OUTER JOIN uses both left and right tables as reserved tables, but full join is not supported in Mysql. Full join can be achieved in the following ways:

Since I used LEFT JOIN in the test SQL query logic statement I prepared, the resulting VT3 table is as follows:

Perform where conditional filtering

Perform where conditional filtering on the data with external rows added, and only the records that meet the criteria will be filtered out. Execute WHERE a.city = 'beijing' to get the VT4 as follows:

But when using the WHERE clause, you need to be aware of the following two points:

1. Since the data has not been grouped yet, it is not possible to use where_condition=MIN (col) filtering on grouping statistics in where filtering conditions.

2. Since there is no progressive selection operation, the use of column aliases in select is not allowed, for example, select city as c from table1 wherec='beijing' is not allowed

Execute group by grouping statements

The GROU BY clause is mainly used to group the virtual tables obtained by using the WHERE clause. The VT5 obtained by executing GROUP BY a.user_id is as follows:

VI. Execute having

The HAVING clause is mainly used in conjunction with the GROUP BY clause to conditionally filter the data obtained by grouping the VT5, and execute HAVING COUNT (b.order_id) < 2 to get the following VT6:

7. Select list

Now that you get to the SELECT clause, don't assume that the SELECT clause is written on the first line and is the first to be executed.

We execute the SELECT a. User _ name _ as total_orders in the test statement, select the content we need from the VT6, and get the VT7 as follows:

Execute distinct to duplicate data

If the DISTINCT clause is specified in the query, a temporary memory table is created (if there is no room in it, it needs to be stored on the hard disk). The table structure of this temporary table is the same as the virtual table generated in the previous step, except that a unique index is added to the column for DISTINCT operation to remove duplicate data. There is no DISTINCT statement in the test SQL, so it will not be executed

IX. Execute the order by sentence

Sort the contents of the virtual table VT7 according to the specified columns, and then return a new virtual table. We execute the ORDER BY total_orders DESC in the test SQL statement and get the following results:

DESC reverse sort, ASC ascending sort

Execute limit words and sentences

The LIMIT clause selects the specified row data starting from the specified location from the virtual table obtained in the previous step and is often used for paging.

The LIMIT of the MySQL database supports the following forms of selection: limit NMagne m

Indicates that the m record is selected starting from the n record. For small data, there is no problem with using the LIMIT clause, and when the amount of data is very large, using LIMIT n, m is very inefficient. Because the mechanism of LIMIT is to scan from scratch every time, if you need to start from the 600,000 rows and read three pieces of data, you need to scan to 600000 rows and then read them, and the scanning process is a very inefficient process.

The above is all the contents of the article "sample Analysis of the execution order of SQL query statements". Thank you for reading! Hope to share the content to help you, more related 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.