MySQL-based database practices (renaming operations)

Consider the following query query.

select name, course_idfrom instructor, teacheswhere instructor.ID = teaches.ID;

The result is a relationship that has attribute name,course_id, and the property name in the result comes from the from property name of the relationship in the clause. However, such a method will encounter problems in more complex situations, from there may be attributes with the same name in the two relationships of the ① clause, so there will be duplicate property names in the result ② if we select use arithmetic expressions in clauses, then the result property will have no Name ③ we want to replace the original property name with the new name.
For these reasons, SQL provides a way to rename (rename) the properties in a result relationship, that is, to use as clauses.

as new-name

asClauses can appear in select clauses, or they can appear in where clauses.
For example, if we want to use the name Instructor_name instead of the attribute name name, you can rewrite the query above as follows.

Mysql>SelectName asInstructor_name, course_id fromInstructor, teacheswhereInstructor.id = teaches.id;+-----------------+-----------+| Instructor_name | course_id |+-----------------+-----------+| Srinivasan | Cs-101|| Srinivasan | Cs-315|| Srinivasan | Cs-347|| Wu | FIN-201|| Mozart | MU-199|| Einstein | Phy-101|| El said | His-351|| Katz | Cs-101|| Katz | Cs-319|| Crick | BIO-101|| Crick | BIO-301|| Brandt | Cs-190|| Brandt | Cs-190|| Brandt | Cs-319|| Kim | Ee-181|+-----------------+-----------+ the rows inch Set(0.01Sec

asClauses work well when renaming relationships, because renaming a relationship is a matter of replacing a long relationship name with a short relationship name, which is handy for other places in the query.
Rewrite the query, listing the names of all the teachers in the university and all the course identifiers that are described.

Mysql>SelectT.name, s.course_id fromInstructor asT, teaches asS-whereT.id = s.id;+------------+-----------+| name | course_id |+------------+-----------+| Srinivasan | Cs-101|| Srinivasan | Cs-315|| Srinivasan | Cs-347|| Wu | FIN-201|| Mozart | MU-199|| Einstein | Phy-101|| El said | His-351|| Katz | Cs-101|| Katz | Cs-319|| Crick | BIO-101|| Crick | BIO-301|| Brandt | Cs-190|| Brandt | Cs-190|| Brandt | Cs-319|| Kim | Ee-181|+------------+-----------+ the rows inch Set(0.00Sec

Another important reason to rename a relationship is to apply to cases where a tuple in the same relationship needs to be compared. In this case, we need to have a relationship with itself to do the Cartesian product operation, if not renamed, it is not possible to distinguish a tuple from other tuples.
Consider the query to find out the names of all teachers who meet the following criteria, whose wages are at least higher than that of a teacher in the biology department.

mysql> select  distinct  T.name-from  instructor as  T, instructor as
      S--where  t.salary > S.salary and  s.dept_name =  ' biology ' ; +----------+  | Name |+----------+  | Wu | | Einstein | | Gold | | Katz | | Singh | | Brandt | | Kim |+----------+  7  rows   set  (0.01  sec)  

Note that instructor.salary cannot be used in the above query because the allegation is unknown. In the above query, T and S can be considered to be two copies of the instructor relationship, but more precisely the alias that is declared as a instructor relationship (alias). Identifiers such as T and S, which are used to rename a relationship, are referred to as the correlation name (correlation name) in the SQL standard, but are also commonly referred to as table aliases or related variables (correlation variable) or tuple variable (tuple variable).
The above query can be expressed in a better way, to find out the names of all teachers whose wages are higher than the minimum wage of the biology faculty, and we use the previous expression because it is more consistent with the SQL syntax we are learning now, but in the following practice we will see that this expression can also be visualized in SQL syntax.

MySQL-based database practices (renaming operations)