Basic tutorial on using table alias and field alias in MySQL,
MySQL table Alias (Alias)
SQL table alias
In an SQL statement, you can specify an Alias (Alias) for the table name and field (column) name. The Alias is a standard SQL syntax and is supported by almost all database systems. Use the keyword AS to specify.
Table alias Syntax:
SELECT column FROM table AS table_alias
After the preceding SQL statement is executed, You can query the table_alias table. However, it is meaningless to simply query aliases for a single table. A table alias is usually set only when a table needs to be operated as multiple tables or when multiple tables are operated.
Table alias example
The following is an example of a simple multi-table operation:
Articles:
User table:
When querying an article, the author of the article is generally queried at the same time. The common SQL statement is:
SELECT article.title,article.content,user.username FROM article, user WHERE article.aid=1 AND article.uid=user.uid
After the table alias is set:
SELECT a.title,a.content,u.username FROM article AS a, user AS u where a.aid=1 and a.uid=u.uid
The query results of the preceding two SQL statements are the same:
It can be seen that using table alias query can make SQL concise and easier to write and read, especially when SQL is complex. In addition to using aliases to simplify SQL statements, for example, when a table is associated with itself, aliases must be used as two tables for association.
MySQL field alias (column alias)
SQL field alias
Like the table alias described earlier in this article, SQL (MySQL) also supports setting aliases for table fields (columns.
Field alias Syntax:
SELECT column AS column_alias FROM table
Example of field alias
An obvious effect of field alias is that you can customize the field names returned for data queries. The following table data:
User table:
When querying, use the alias for the username field:
SELECT username AS name,email FROM user
The returned query result is as follows:
Of course, defining the alias of a field in such a simple way does not have much practical significance. A field alias can be used to solve the problem of repeated field names, if a table field is queried twice or more times:
SELECT username AS name,username,email FROM user
If two or more tables have the same returned fields:
Articles:
User table:
When the title field is defined in both tables and data of this field needs to be returned, the field alias must be defined (at least one ):
SELECT a.title AS atitle,u.username,u.title AS utitle FROM article AS a, user AS u where a.uid=u.uid
The returned query result is as follows:
We can see that when the field names returned by the query are the same, you can define aliases to avoid conflicts. The preceding query example also defines the field aliases and table aliases.
Prompt
Generally, the AS keyword defining the field alias can be omitted, that is, the following two SQL statements have the same effect:
SELECT username AS name FROM userSELECT username name FROM user
However, we recommend that you do not omit the AS keyword.
Alias (alias) is the standard syntax of SQL, which is supported by almost all database systems. When processing some complex queries, You can reasonably define tables and field aliases to make the SQL statements look simpler and easier to read, and avoid conflicts when the query returns data of the same field.
Articles you may be interested in:
- Some Understanding about SQL and mysql that alias cannot be called
- Alias for MySQL notes