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 po
WHERE 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 Name
FROM 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