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

This article shows you the understanding of sql and mysql about aliases can not be called, the content is concise and easy to understand, can definitely brighten your eyes, through the detailed introduction of this article, I hope you can get something.

When writing sql, due to some statements aliases can not be called, Baidu about the reason, the original is due to different alias mechanisms. In order to avoid making the same mistake next time, I summed up the information found on the Internet today, sql and mysql execution order, and found that the internal mechanism is the same. The biggest difference is in the reference of the alias.

I. sql execution order

(1) from

(2) on

(3) join

(4) where

(5) group by

(6) avg,sum....

(7) having

(8) select (9) distinct

(10) order by

From this order, it is not difficult to find that all query statements are executed from from. In the process of execution, each step will generate a virtual table for the next step, which will be used as input for the next step.

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

Step 2: the next step is to apply the on filter. The logical expression in on will be applied to each row in vt1, filter out the rows that satisfy the on logical expression, and generate the virtual table vt2.

Step 3: if it is outer join, then this step will add external rows, left outer jion will add the left table filtered in the second step, and if it is right outer join, then add the rows filtered out by the right table in the second step, so as to generate the virtual table vt3

Step 4: if the number of tables in the from clause is more than two tables, then join the vt3 and the third table to calculate the Cartesian product to generate a virtual table. This process is a repetition of 1-3 steps, resulting in a new virtual table vt3.

Step 5: apply the where filter to reference the where filter on the virtual table produced in the previous step to generate the virtual table vt4. There is an important detail here. For queries that contain outer join clauses, there is a puzzling question. Should the on filter or the where filter specify the logical expression? The biggest difference between on and where is that if you apply logical expressions to on, you can add the removed lines back in the third step outer join, while the removal of where is final.

To take a simple example, there is a student table (class, name) and a transcript (name, grade). Now I need to return the scores of all the students in Class x, but there are several students in this class who are absent from the exam. that is to say, there is no record in the score sheet. In order to achieve our expected results, we need to specify the relationship between the student and the transcript in the on clause. Name = grade. (name) then do we find that when performing the second step, the record of the students who did not take the exam will not appear in vt2, because they are filtered out by the logical expression of on, but we can use left outer join to find the students who did not take the exam in the left table, because we want to return all the students in class x if we apply students in on. If class ='x', then all records of students in class x will not be retrieved in left outer join, so 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 into a group to get the virtual table vt5. If group by is applied, then all the steps that follow can only get columns of vt5 or aggregate functions (count, sum, avg, etc.). The reason is that the final result set contains only one row for each group. Please keep that in mind.

Step 7: apply the cube or rollup option to generate a supergroup for vt5 and generate vt6.

Step 8: apply the having filter to generate the vt7. The having filter is the first and only filter that applies to grouped data.

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

Step 10: apply the distinct clause and remove the same line from vt8 to generate vt9. In fact, if you apply the group by clause, then distinct is redundant, also because when grouping is to group the only values in the column and return only one row of records for each group, then all records will be different.

Step 11: apply the order by clause. Sorts vt9 by order_by_condition, which returns a cursor instead of a virtual table. Sql is based on the theory of set, the set does not sort its rows in advance, it is just a logical collection of members, and the order of members does not matter. A query that sorts a table can return an object that contains a logical organization of a specific physical order. This object is called a cursor. Because the return value is a cursor, queries using the order by clause cannot be applied to table expressions. Sorting is costly, and it's best not to specify order by unless you have to sort it.

Finally, it is the first and only step in this step that can use aliases in the select list. Step 12: apply the top option. At this point, the result is returned to the requestor, the user.

Second, the execution order of mysql SELECT statement defines that a completed SELECT statement contains optional clauses. The definition of the SELECT statement is as follows: SQL code

The code is as follows:

[]

[]

[]

[]

[]

[]

The SELECT clause is required, and other clauses such as WHERE clause, GROUP BY clause, and so on are optional. In a SELECT statement, the order of clauses is fixed. For example, the GROUP BY clause does not precede the WHERE clause. The execution order of the clauses in the SELECT statement is different from the input order of the subclauses of the SELECT statement, so it is not executed from the SELECT clause, but in the following order: start-> FROM clause-> WHERE clause-> GROUP BY clause-> HAVING clause-> ORDER BY clause-> SELECT clause-> LIMIT clause-> final result each clause produces an intermediate result after execution, which is used by the next clause If a clause does not exist, skip the comparison. The execution order of mysql and sql is basically the same. The standard order of SQL statements is: SQL code.

The code is as follows:

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) > 600order by max total score

In the above example, the order in which SQL statements are executed is as follows:

(1)。 First execute the FROM clause to assemble the data from the data source from the tb_Grade table

(2)。 Execute the WHERE clause to filter all data in the tb_Grade table that is not NULL

(3)。 Execute the GROUP BY clause and group the tb_Grade according to the "student name" column

(4)。 Calculate the max () aggregation function, and calculate some of the largest values in the total score according to the "total score".

(5)。 Execute the HAVING clause and screen those with a total score of more than 600.

(6)。 Execute the ORDER BY clause and sort the final results by Max score. Here is an example to illustrate the root cause of aliases not being used: example-SQL code

The code is as follows

: select m.mname as username, avg (s.score) as rscore, (case when avg (s.score)

= 60 and avg (s.score) 80 and avg (s.score) 2

If the count (*) in having is replaced by an alias c, this sentence becomes wrong in sql and is correct under mysql. I was puzzled. I gave advice to my friend and finally understood it. The following is an explanation: the execution order of GroupBy and Having,Where,Orderby statements: finally, I want to explain the execution order of several GroupBy,Having,Where,Orderby statements. A SQL statement often produces multiple temporary views, so the order in which these keywords are executed is important because you have to know whether the keyword operates on the field before the corresponding view is formed or on the resulting temporary view, which is especially important in views that use aliases. The keywords listed above are executed in the following order: Where,GroupBy,Having,Orderby. First of all, where deletes the records that do not meet the conditions in the original records, and then groups the filtered views by the grouping conditions specified after the GroupBy keyword, and then the system filters out the records that do not meet the conditions after grouping the views according to the filter conditions specified after the Having keywords, and then sorts the views according to the OrderBy statement, so that the final result is produced. Of these four keywords, the column name of the final view can be used only in the OrderBy statement, such as:

SQL code

The code is as follows: SELECT FruitName,ProductPlace,Price,ID AS IDE,Discount FROMT_TEST_FRUITINFO WHERE ORDER BY IDE

IDE can be used only in ORDERBY statements, and only ID can be used in other conditional statements if column names need to be referenced, not IDE.

The above is about sql and mysql's understanding that aliases cannot be called. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are 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.