Some Understanding about SQL and mysql that alias cannot be called

When writing SQL statements, some statement aliases cannot be called. Baidu's reasons are due to different alias mechanisms. In order to avoid making the same mistake again next time, I summarized the information found on the Internet. 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
(3) join
(2) on
(4) where
(5) group
(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', all records of the student in class x will not be retrieved in the left outer join, so the student 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
Copy codeThe Code is as follows:
<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:

SQL code
Copy codeThe Code is as follows:
Select candidate name, max (total score) as max total score

From tb_Grade

Where examinee name is not null

Group by candidate 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.

(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.

The following example shows the root cause of Alias unavailability:

Example 1

SQL code
Copy codeThe Code is as follows:
Select m. mname as username,
Avg (s. score) as rscore,
(Case
When avg (s. score) <60 then
'Bad'
When avg (s. score)> = 60 and avg (s. score) <= 80 then
'Liang'
When avg (s. score)> 80 and avg (s. score) <= 100 then
'Out'
Else
'Invalid'
End)
From score s, student m
Where s. mid = m. mid
Group by m. mname
Order by rscore desc;

If you change the avg (s. score) in the case to the alias rscore and run it in SQL, an error is returned.

Example 2

SQL code
Copy codeThe Code is as follows:
Select count (*) as c from score group by mid having count (*)> 2;

If you replace the count (*) in having with the alias c, this statement becomes wrong in SQL. It is correct in mysql and cannot be understood, I gave a friend some advice and finally understood it. The following is a description:
The execution sequence of GroupBy, Having, Where, Orderby statements:
Finally, explain the execution sequence of the GroupBy, Having, Where, and Orderby statements. An SQL statement usually produces multiple temporary views, so the execution sequence of these keywords is very important, because you must understand whether this keyword is used to operate the fields before the corresponding view is formed or the temporary view is formed. This issue is especially important when the view with an alias is used. The keywords listed above are executed in the following order: Where, GroupBy, Having, Orderby. First, the where clause deletes records that do not meet the conditions in the most original records, and then groups the filtered views based on the grouping conditions specified after the GroupBy keyword, then, the system filters records that do not meet the conditions after the Having keyword Based on the specified filtering conditions in the grouping view, and sorts the views according to the OrderBy statement, so that the final result is generated. Among the four keywords, the column name of the final view can be used only in the OrderBy statement, for example:

SQL code
Copy codeThe Code is as follows:
SELECT FruitName, ProductPlace, Price, id as ide, Discount FROMT_TEST_FRUITINFO WHERE ORDER BY IDE

IDE is only available in the ORDERBY statement. In other condition statements, if you want to reference a column name, you can only use ID instead of IDE.