Key points for aliasing tables in SQL queries (summary sharing)

This article brings you relevant knowledge about SQL server. By using SQL, you can specify aliases for table names or column names. The following introduces the key points about aliasing tables with sql queries ( Related information involving nested queries), I hope it will be helpful to everyone.

Recommended study: "SQL Tutorial"

You can alias the table through spaces or as

But Note that if the operating database is Oracle, only spaces can be used, as does not comply with Oracle's syntax.

For example

Using aliases in simple queries

select *
from student s
where s.id = '10';

When using aliases in simple queries, there is generally nothing special to pay attention to and less operations to be done

Using aliases in complex queries

Question summary: There are three tables, student(sno,sname,ssex,birthday,class)

score(sno,cno,degree)

course(cno,cname,tno)

Query the records of all students who took the elective course "3-105" and whose grades are higher than those of the student numbered "109".

Answer:

select *
 from (select s.sno,s.sname,s.ssex,s.sbirthday,s.class,    sc.degree,c.cno,c.cname,c.tno from student s , course c ,score sc where s.sno = sc.sno and c.cno = sc.cno) ss
 where  ss.cno = '3-105' and ss.degree >( select degree from score where sno = '109' and cno = '3-105');

As you can see, in order to facilitate operation, we have redefined a table ss. This table is a large table that also contains the contents of the above three tables. But please pay attention to the following points, otherwise it is easy to make mistakes

When you want to display all the values ​​​​of the newly defined table, you cannot directly use *

. For example, if you change the declared answer to

select *
 from (select * from student s , course c ,score sc where s.sno = sc.sno and c.cno = sc.cno) ss
 where  ss.cno = '3-105' and ss.degree >( select degree from score where sno = '109' and cno = '3-105');

The command line will show that the column is not clearly defined, because we want to specify the column now as a new table, but the column names of some columns are repeated, and we need to specify one of them.

The newly created table cannot be used in the nested query statement, because the code in the nested query is a complete execution section and will be run from the beginning? Anyway, an error will be reported when calling it

select *
 from (select * from student s , course c ,score sc where s.sno = sc.sno and c.cno = sc.cno) ss
 where  ss.cno = '3-105' and ss.degree >( select degree from ss where sno = '109' and cno = '3-105');

The subquery in where in this SQL uses the ss new table, and compilation will show that there is no error in the table or view.

Recommended study: "SQL Tutorial"

The above is the detailed content of Key points for aliasing tables in SQL queries (summary sharing). For more information, please follow other related articles on the PHP Chinese website!