SQL and MySQL statement execution sequence analysis, sqlmysql statement Sequence

SQL and MySQL statement execution sequence analysis, sqlmysql statement Sequence

Today, I encountered a problem: Can I use the as alias in the insert into, update, and delete statements of mysql? I am still looking at it, but I found some useful knowledge when I checked the materials. I would like to share with you about the SQL and MySQL statement execution sequence:
The execution sequence of SQL and mysql is the same as that of internal mechanism. The biggest difference is the alias reference.

I. SQL Execution Sequence

(1) from

(2) on

(3) join

(4) where

(5) group by (start to use the alias in select, which can be used in subsequent statements)

(6) avg, sum ....

(7) having

(8) select

(9) distinct

(10) order

In this order, it is not difficult to find that all query statements start from. During the execution process, each step generates a virtual table for the next step, this virtual table will be used as the input for the next execution step.

Step 1: first execute a Cartesian product for the first two tables in the from clause, and then generate the virtual table vt1 (select a relatively small table as the basic table)

Step 2: Apply the on filter. The logical expression in on applies to each row in vt1, filters out rows that meet the on logical expression, and generates a virtual table vt2.

Step 3: For outer join, the external row is added in this step, and left outer jion adds the left table to the filter in step 2, if right outer join is used, add the rows filtered out by the right table in step 2 to generate the virtual table vt3.

Step 4: if the number of tables in the from clause is more than two tables, connect vt3 to the third table to calculate the Cartesian Product and generate a virtual table. This process is a repeated step of 1-3, finally, a new virtual table vt3 is obtained.

Step 5: Apply the where filter, reference the where filter to the virtual table produced in the previous step, and generate the virtual table vt4. In this case, I have to explain the important details, there is a confusing question about queries that contain the outer join clause. Do you still use the where filter to specify a logical expression in the on filter? The biggest difference between on and where is that if a logical expression is applied to on, you can add the removed row again in step 3 outer join, and the final result of where is removed. For example, there is a student table (class, name) and a student table (name, score). Now I need to return the score of all the students in Class x, however, there are several students in this class who are absent from the exam, that is, there is no record in the student list. In order to get our expected results, we need to specify the relationship between the student and the student table in the on clause (student. name = score. name) So do we find that when we perform step 2, the records of students who did not take the test will not appear in vt2 because they are filtered out by the on logical expression, however, we can use left outer join to retrieve the students not taking the test in the left table (student), because we want to return all the students in Class x, if the student is applied to the on application. if the class is 'x', left outer join will retrieve all the student records of Class x (thanks to Kang qinmou _ Kang qinmiao, a netizen ), therefore, students can only be applied in the where filter. class = 'X' because its filtering is final.

Step 6: The group by clause combines the unique values in a group to obtain the virtual table vt5. If group by is applied, all subsequent steps can only obtain vt5 columns or aggregate functions (count, sum, avg, etc ). The reason is that the final result set contains only one row for each group. Keep this in mind.

Step 7: Apply the cube or rollup option to generate a supergroup and vt6.

Step 8: Apply the having filter to generate vt7. Having is the first and only Filter Applied to grouped data.

Step 9: process the select clause. Filter the columns that appear in select in vt7. Generate vt8.

Step 10: Apply the distinct clause, remove the same row from vt8, and generate vt9. In fact, if the group by clause is applied, distinct is redundant because the unique values in the column are grouped into a group, at the same time, only one row of records is returned for each group, so the records will be different.

Step 1: Apply the order by clause. Sort vt9 by order_by_condition. A cursor is returned instead of a virtual table. SQL is based on the set theory, and the set does not sort its rows in advance. It is only a logical set of members, and the order of members is irrelevant. You can return an object for sorting a table. This object contains the logical organization of a specific physical order. This object is called a cursor. Because the return value is a cursor, the query using the order by clause cannot be applied to table expressions. Sorting requires a high cost. Unless you have to sort, you 'd better not specify order by. Finally, this step is the first and only one step that can use aliases in the select list.

Step 2: Apply the top option. In this case, the result is returned to the requester, that is, the user.

Ii. execution sequence of mysql

SELECT statement Definition

A complete SELECT statement contains several optional clauses. The SELECT statement is defined as follows:

SQL code

<SELECT clause> [<FROM clause>] [<WHERE clause>] [<GROUP BY clause>] [<HAVING clause>] [<ORDER BY clause>] [<LIMIT clause>] 

The SELECT clause is required. Other clauses, such as the WHERE clause and group by clause, are optional.

In a SELECT statement, the order of clauses is fixed. For example, the group by clause is not located before the WHERE clause.

SELECT statement execution sequence

The execution sequence of the SELECT statement substatements is different from that of the SELECT statement substatements. Therefore, the substatements are executed in the following order instead of starting from the SELECT clause:

Start> FROM clause> WHERE clause> group by clause> HAVING clause> order by clause> SELECT clause> LIMIT clause> final result
After each clause is executed, an intermediate result is generated for the subsequent clause. If no clause exists, skip

After comparison, the execution sequence of mysql and SQL is basically the same, and the standard order of SQL statements is:

Select examinee name, max (total score) as max total score from tb_Grade where examinee name is not null group by examinee name having max (total score)> 600 order by max total score

In the preceding example, the SQL statement execution sequence is as follows:

(1) first, execute the FROM clause to assemble data FROM the tb_Grade table.

(2) execute the WHERE clause to filter data that is not NULL in the tb_Grade table.

(3 ). execute the group by clause to GROUP the tb_Grade table BY the "Student name" Column (Note: aliases in select can be used at the beginning of this step, and a cursor instead of a table is returned, therefore, the alias in select cannot be used in the where clause, but having can be used. Thanks to the netizen zyt1369 for asking this question)

(4) Calculate the max () Aggregation Function and calculate the maximum values in the total score by "total score ".

(5). Execute the HAVING clause to filter course scores greater than 600.

(7) Execute the order by clause to sort the final result BY "Max score.

I will continue to look for the problems I encountered. Of course, I also hope that you can give me some advice.

Summary

The above is all the content about SQL and MySQL statement execution sequence analysis in this article. If you have any shortcomings, please leave a message to correct them and I will reply to you in a timely manner.

If you are interested, refer to: Examples of optimization techniques for slow sub-query efficiency of mysql in statements, analysis of MYSQL sub-query and nested query optimization instances, and several important MySQL variables, hope to help you.