Python 3 MySQL SQL logical query statement execution order

Python 3 MySQL SQL logical query statement execution order one, the definition order of the SELECT statement keyword

SELECT DISTINCT <select_list><left_table><join_type> join <right_table>  <join_condition><where_condition><group_by_list><order_by_condition><limit_number>

Second, the execution order of the SELECT statement keyword

1 (7)       2 (8)     DISTINCT <select_list> 3 (1) from     <left_table > 4 (3)     <join_type> join <right_table> 5 (2) on     <join_condition> 6 (4)     WHERE <where_condition> 7 (5)     GROUP by <group_by_list> 8 (6)     Have  9 (9)     ORDER by <order_by_condition> (Ten)    LIMIT <limit_number>

III. preparation of tables and data

1. Create a new test database testdb;

Create Database TestDB;

2. Create test tables table1 and table2;

CREATE TABLE table1 (     customer_id varchar () not null, City     VARCHAR (ten) not NULL,     PRIMARY KEY (customer_id)) ENGINE=innodb DEFAULT charset=UTF8; CREATE TABLE table2 (     order_id INT not NULL auto_increment,     customer_id VARCHAR),     PRIMARY KEY (order_id)) ENGINE=innodb DEFAULT Charset=utf8;

View Code

3. Insert test data;

INSERT into table1 (customer_id,city) VALUES ('163','Hangzhou'); INSERT into table1 (customer_id,city) VALUES ('9you','Shanghai'); INSERT into table1 (customer_id,city) VALUES ('TX','Hangzhou'); INSERT into table1 (customer_id,city) VALUES ('Baidu','Hangzhou'); INSERT into table2 (customer_id) VALUES ('163'); INSERT into table2 (customer_id) VALUES ('163'); INSERT into table2 (customer_id) VALUES ('9you'); INSERT into table2 (customer_id) VALUES ('9you'); INSERT into table2 (customer_id) VALUES ('9you'); INSERT into table2 (customer_id) VALUES ('TX'); INSERT into table2 (customer_id) VALUES (NULL);

View Code

viewing table1 and table2 should look like this:

from +-------------+----------+ | customer_id | City     | +-------------+----------+ | 163         | hangzhou | | 9you        | shanghai | | Baidu       | hangzhou | | TX          
   
    in Set (0.00
     sec) MySQL
    
    from +----------+-------------+ | order_id | customer_id | +----------+-------------+ |        1 | 163         | |        2 | 163         | |        3 | 9you        | |        4 | 9you        | |        5 | 9you        | |        6 | TX          | |        7 | NULL        | +----------+-------------+
    

    In Set (0.00 sec)
   

Iv. SQL logical Query Test statement

# query customers from Hangzhou with fewer than 2 orders. = ' Hangzhou ' < 2 ORDER by total_orders DESC; 

V. Execution sequence Analysis

During the execution of these SQL statements, a virtual table is generated to hold the result of the SQL statement (which is the focus), and I am now going to track the changes in this virtual table and get the final query results to analyze the execution order and process of the entire SQL logical query.

Execute from statement

The first step is to execute the FROM statement. We first need to know which table to start with, and that's what the From tells us. Now that we have the <left_table> and <right_table> two tables, which table do we start with, or do we start with some sort of connection from the two tables? How do they relate to each other? --Descartes Product

For what is Descartes product, please Google brain by yourself. After the FROM statement performs a Cartesian product on two tables, a virtual table is called VT1 (vitual table 1), which reads as follows:

+-------------+----------+----------+-------------+| customer_id | City | order_id | customer_id |+-------------+----------+----------+-------------+| 163 |        Hangzhou | 1 | 163 | | 9you |        Shanghai | 1 | 163 | | Baidu |        Hangzhou | 1 | 163 | | TX |        Hangzhou | 1 | 163 | | 163 |        Hangzhou | 2 | 163 | | 9you |        Shanghai | 2 | 163 | | Baidu |        Hangzhou | 2 | 163 | | TX |        Hangzhou | 2 | 163 | | 163 |        Hangzhou | 3 | 9you | | 9you |        Shanghai | 3 | 9you | | Baidu |        Hangzhou | 3 | 9you | | TX |        Hangzhou | 3 | 9you | | 163 |        Hangzhou | 4 | 9you | | 9you |        Shanghai | 4 | 9you | | Baidu |        Hangzhou | 4 | 9you | | TX |        Hangzhou | 4 | 9you | | 163 |     Hangzhou |   5 | 9you | | 9you |        Shanghai | 5 | 9you | | Baidu |        Hangzhou | 5 | 9you | | TX |        Hangzhou | 5 | 9you | | 163 |        Hangzhou | 6 | TX | | 9you |        Shanghai | 6 | TX | | Baidu |        Hangzhou | 6 | TX | | TX |        Hangzhou | 6 | TX | | 163 |        Hangzhou | 7 | NULL | | 9you |        Shanghai | 7 | NULL | | Baidu |        Hangzhou | 7 | NULL | | TX |        Hangzhou | 7 | NULL |+-------------+----------+----------+-------------+

There are a total of (table1) records (number of record strips * table2 records). This is the result of VT1, and the next operation is based on the VT1.

Performing on filtering

After the completion of the Cartesian product, then the ON a.customer_id = b.customer_id conditional filtering, according to the ON conditions specified in the removal of those non-conforming data, to obtain the VT2 table, the contents are as follows:

+-------------+----------+----------+-------------+| customer_id | City     | order_id | customer_id |+-------------+----------+----------+-------------+| 163         | hangzhou |        1 | 163 |         | 163         | hangzhou |        2 | 163 |         | 9you        | shanghai |        3 | 9you        | | 9you        | shanghai |        4 | 9you        | | 9you        | shanghai |        5 | 9you        | | tx          | hangzhou |        6 | Tx          |+-------------+----------+----------+-------------+

VT2 is the ON useful data obtained after conditional filtering, and the next operation will continue on the basis of VT2.

Add an external row

This step occurs only when the connection type is, OUTER JOIN such as LEFT OUTER JOIN , RIGHT OUTER JOIN and FULL OUTER JOIN . Most of the time, we omit the OUTER keyword, but that's the concept of the OUTER outer line.

LEFT OUTER JOINThe left memento is the reserved table and the result is:

+-------------+----------+----------+-------------+| customer_id | City     | order_id | customer_id |+-------------+----------+----------+-------------+| 163         | hangzhou |        1 | 163 |         | 163         | hangzhou |        2 | 163 |         | 9you        | shanghai |        3 | 9you        | | 9you        | shanghai |        4 | 9you        | | 9you        | shanghai |        5 | 9you        | | tx          | hangzhou |        6 | TX          | | Baidu       | hangzhou |     NULL | NULL        |+-------------+----------+----------+-------------+

Right OUTER join to the left memento as a reserved table, the results are:

+-------------+----------+----------+-------------+| customer_id | City     | order_id | customer_id |+-------------+----------+----------+-------------+| 163         | hangzhou |        1 | 163 |         | 163         | hangzhou |        2 | 163 |         | 9you        | shanghai |        3 | 9you        | | 9you        | shanghai |        4 | 9you        | | 9you        | shanghai |        5 | 9you        | | tx          | hangzhou |        6 | TX          | | NULL        | NULL     |        7 | NULL        |+-------------+----------+----------+-------------+

The full OUTER join takes the left and right tables as a reserved table, resulting in:

+-------------+----------+----------+-------------+| customer_id | City     | order_id | customer_id |+-------------+----------+----------+-------------+| 163         | hangzhou |        1 | 163 |         | 163         | hangzhou |        2 | 163 |         | 9you        | shanghai |        3 | 9you        | | 9you        | shanghai |        4 | 9you        | | 9you        | shanghai |        5 | 9you        | | tx          | hangzhou |        6 | TX          | | Baidu       | hangzhou |     NULL | NULL        | | NULL        | NULL     |        7 | NULL        |+-------------+----------+----------+-------------+

The job of adding an outer row is to add the data that is filtered by the filter in the reserved table based on the VT2 table, the data in the non-reserved table is given a null value, and the virtual table VT3 is generated.

Because I am using a left JOIN in the prepared test SQL query logic statement, the following data is filtered out:

| Baidu       | hangzhou |     NULL | NULL        |

Now add this data to the VT2 table and get the VT3 table as follows:

+-------------+----------+----------+-------------+| customer_id | City     | order_id | customer_id |+-------------+----------+----------+-------------+| 163         | hangzhou |        1 | 163 |         | 163         | hangzhou |        2 | 163 |         | 9you        | shanghai |        3 | 9you        | | 9you        | shanghai |        4 | 9you        | | 9you        | shanghai |        5 | 9you        | | tx          | hangzhou |        6 | TX          | | Baidu       | hangzhou |     NULL | NULL        |+-------------+----------+----------+-------------+

The next operation will be done on the VT3 table.

Execute where filter

Where filters are made for VT3 that add external rows, only records that match <where_condition> are exported to the virtual table VT4. When we execute where a.city = ' Hangzhou ', we get the following, and there is a virtual table VT4:

+-------------+----------+----------+-------------+| customer_id | City     | order_id | customer_id |+-------------+----------+----------+-------------+| 163         | hangzhou |        1 | 163 |         | 163         | hangzhou |        2 | 163 |         | tx          | hangzhou |        6 | TX          | | Baidu       | hangzhou |     NULL | NULL        |+-------------+----------+----------+-------------+

However, when using the WHERE clause, the following two points need to be noted:

1, because the data has not been grouped, so now can not be used in the Where filter where_condition=min (COL) Such a group of statistics filtering;

2, because there is no selection operation of the column, so the use of the column alias in select is not allowed, such as: Select City as C from T WHERE c= ' Shanghai '; is not allowed to occur.

Performing a GROUP by group

The Grou by clause is primarily a grouping of virtual tables that are obtained by using the WHERE clause. When we execute the group by A.CUSTOMER_ID in the test statement, we get the following (the first in the group is displayed by default):

+-------------+----------+----------+-------------+| customer_id | City     | order_id | customer_id |+-------------+----------+----------+-------------+| 163         | hangzhou |        1 | 163 |         | Baidu       | hangzhou |     NULL | NULL        | | tx          | hangzhou |        6 | Tx          |+-------------+----------+----------+-------------+

The resulting content is stored in the virtual table VT5, at which point we get a VT5 virtual table, and the next operation is done on that table.

Perform having filter

The HAVING clause is used primarily in conjunction with the GROUP BY clause to conditionally filter the VT5 virtual tables that are grouped. When I execute the having count (b.order_id) < 2 o'clock in the test statement, I get the following:

+-------------+----------+----------+-------------+| customer_id | City     | order_id | customer_id |+-------------+----------+----------+-------------+| Baidu       | hangzhou |     NULL | NULL        | | tx          | hangzhou |        6 | Tx          |+-------------+----------+----------+-------------+

This is the virtual table VT6

SELECT list

Only now executes to the SELECT clause, not to assume that the SELECT clause is written on the first line, which is the first one to be executed.

We execute the Select a.customer_id in the test statement, COUNT (b.order_id) as Total_orders, and choose what we need from the virtual table VT6. We will get the following content:

+-------------+--------------+| customer_id | Total_orders |+-------------+--------------+| Baidu       |            0 | | TX          |            1 |+-------------+--------------+

Not yet, this is just a virtual table VT7

Execute DISTINCT clause

If the DISTINCT clause is specified in the query, a temporary memory table is created (if the memory does not fit, it needs to be stored on the hard disk). The table structure of this temporary table is the same as the virtual table VT7 generated in the previous step, except for the addition of duplicate data by adding a unique index to the column that performed the distinct operation.

Because distinct is not used in my test SQL statement, this step does not generate a virtual table in this query.

Execute ORDER BY clause

Sort the contents of the virtual table by the specified column and return a new virtual table, we execute the order by Total_orders DESC in the test SQL statement, and we get the following:

+-------------+--------------+| customer_id | Total_orders |+-------------+--------------+| TX          |            1 | | Baidu       |            0 |+-------------+--------------+

You can see that this is the Total_orders column in descending order. The above results are stored in the VT8.

Execute the LIMIT clause

The LIMIT clause selects the specified row data starting at the specified location from the VT8 virtual table obtained in the previous step. For a limit clause that does not have an order by applied, the result is also unordered, so many times we will see that the limit clause is used with the ORDER BY clause.

The MySQL database limit supports the following forms of selection:

LIMIT N, M

Indicates that the M record is selected starting with the nth record. And many developers like to use this statement to solve paging problems. For small data, there is no problem with 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 need to start from the No. 600000 line, read 3 data, you need to scan to locate 600,000 rows before reading, and the scanning process is a very inefficient process. Therefore, for large data processing, it is very necessary to establish a certain caching mechanism in the application layer (now big data processing, mostly using cache)

Python 3 MySQL SQL logical query statement execution order