MySQL Field alias


As with the table alias described earlier in this article, SQL (MySQL) also supports aliasing the table's fields (columns).

SELECT column AS column_alias FROM table

Example

One fo Field Alias ​​obvious effect is that you can customize the field names returned by the query data. Such as the following table data:

User table:

uid username password email regdate
1 admin b7e591c246d010bb2ccd77d52490c85e admin@gmail.com 1277992339
2 Jack a193686a53e4de85ee3f2ff0576adf01 jack@gmail.com 1278063917
3 Jack 0193686a35e4de85ee3f2ff0567adf490 jack@gmail.com 1278061380

In the query, the use of alias for the username field:

SELECT username AS name, email FROM user

The results are as follows:

name email
admin admin@gmail.com
Jack jack@gmail.com
Jack jack@gmail.com

Of course, such a simple definition of the alias field is not much practical significance, the more meaningful field alias name is to solve the duplication of the field, such as a table field is queried twice or more times:

SELECT username AS name, username, email FROM user

Or when two or more tables are queried, with the same return field:

Article table:

aid title content uid pubtime
1 Article 1 Article 1 Body Content ... 1 1278982219
2 Article 2 Article 2 Body Content ... 1 1278985319
3 Article 3 Article 3 Body Content ... 2 1279185319

User table:

uid username title password email regdate
1 admin Admin b7e591c246d010bb2ccd77d52490c85e admin@gmail.com 1277992339
2 Jack Observer a193686a53e4de85ee3f2ff0576adf01 jack@gmail.com 1278063917
3 Jack Call me a foreigner 0193686a35e4de85ee3f2ff0567adf490 jack@gmail.com 1278061380

The above two tables have defined the title field and need to return the field data, you need to define the field alias (at least one definition):

SELECT a.title AS atitle, u.username, u.title AS utitle FROM article AS a, user AS u where a.uid = u.uid

The results are as follows:

atitle username utitle
Article 1 admin Admin
Article 2 admin Admin
Article 3 Jack Observer

As you can see, when the query returns the same field name, you can define the alias to avoid conflicts, the above examples of the query also defines the field aliases and table aliases.


Note

In general, the AS keyword for defining a field alias can be omitted, that is, the following two SQL statements are consistent:

SELECT username AS_name FROM user
SELECT username name FROM user

However, we do not recommend omitting the AS keyword.

ALIAS ​​is the SQL standard syntax, almost all database systems are supported. In dealing with some complex queries, you can reasonably define the table and field aliases to make SQL statements look more streamlined and easy to read, but also to avoid the same field when the query returns data conflict.




Copyright 2014-2017 by DeveloperTut.com. All Rights Reserved.
TwitterGoogle+Facebook