Basic tutorial on using table alias and field alias in MySQL,

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.