Mysql SQL Alias usage and Alias (Alias) syntax

Source: Internet
Author: User

SQL Alias syntax

SELECT column_name (s) FROM table_name AS alias_name

 
Column SQL Alias syntax

SELECT column_name AS alias_name FROM table_name

 
Alias instance: use the table name Alias

Suppose we have two tables: "Persons" and "Product_Orders ". We specify aliases "p" and "po" for them respectively ". Now we want to list all orders for John Adams. We can use the following SELECT statement: SELECT po. OrderID, p. LastName, p. FirstName FROM PersonsAS p, Product_OrdersAS poWHERE p. LastName = 'adams' WHERE p. FirstName = 'john'

 

SELECT statement without alias: SELECT Product_Orders.OrderID, Persons. LastName, Persons. FirstName FROM Persons, Product_Orders WHERE Persons. LastName = 'hansen' WHERE Persons. FirstName = 'ola'

 

You can see from the preceding two SELECT statements that the alias makes the query program easier to read and write.

Alias instance: Use a column name Alias table Persons:
Id LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Th Avenue New York
3 Carter Thomas Changan Street Beijing
SQL:

SELECT LastNameAS Family, FirstNameAS NameFROM Persons

 
Result:

Family Name
Adams John
Bush George
Carter Thomas

Next, we will discuss the use of alias in SQL. The most common aliases are column aliases and table aliases.

To put it simply, the column alias aims to make the SQL results readable. In the previous example, whenever we have a total turnover, the column name is SUM (sales ). Although there is no problem in this case, if this column is not a simple sum, but a complicated calculation, then the column name is not so easy to understand. If we use a column alias, we can confirm that the column name in the result is easy to understand.

The Second alias is the table alias. To get an alias for a table, you only need to leave a space after the table name in the FROM clause, and then list the table alias to use. This is convenient when we use SQL to obtain data from several different tables. We will see this later when talking about join.

Let's take a look at the syntax of the column alias and table alias:

SELECT "table alias". "column 1" "column alias" FROM "table name" "table alias"

Basically, these two aliases are placed behind the objects to be replaced, and they are separated by a blank space. We will continue to use the Store_Information table as an example:

Store_Information table

Store_namesalesdate
Los Angeles $1500jan-05-1999
San Francisco $300jan-08-1999
Boston $700jan-08-1999

We use an example on the same page as SQL GROUP. The difference here is that we have added the column alias and table alias:

SELECT A1.store _ name Store, SUM (A1.Sales) "Total Sales" FROM Store_Information A1 group by A1.store _ name

Result:

Store Total Sales
Los Angeles $1800
San Diego $250
Boston $700

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.