A2-02-11.DML-Using MySQL Alias To Make The Queries More Readable
转载自：http://www.mysqltutorial.org/mysql-alias/
Using MySQL Alias To Make The Queries More Readable
Summary: in this tutorial, you will learn how to use MySQL alias to improve the readability of the queries.
MySQL supports two kinds of aliases which are known as column alias and table alias. Let’s examine each kind of alias in detail.
MySQL alias for columns
Sometimes the names of columns are so technical that make the query’s output very difficult to understand. To give a column a descriptive name, you use a column alias.
The following statement illustrates how to use the column alias:
|
1
2
3
|
SELECT
[column_1 | expression] AS descriptive_name
FROM table_name;
|
To give a column an alias, you use the AS keyword followed by the alias. If the alias contains space, you must quote it as the following:
|
1
2
3
|
SELECT
[column_1 | expression] AS `descriptive name`
FROM table_name;
|
Because the AS keyword is optional, you can omit it in the statement. Note that you can also give an expression an alias.
Let’s look at the employees table in the sample database.
The following query selects first names and last names of employees and combines them to produce the full names. The CONCAT_WS function is used to concatenate first name and last name.
|
1
2
3
4
|
SELECT
CONCAT_WS(', ', lastName, firstname)
FROM
employees;
|
The column heading is quite difficult to read. You can assign the heading of the output a column alias to make it more readable as the following query:
|
1
2
3
4
|
SELECT
CONCAT_WS(', ', lastName, firstname) AS `Full name`
FROM
employees;
|
In MySQL, you can use the column alias in the ORDER BY, GROUP BY and HAVING clauses to refer to the column.
The following query uses the column alias in the ORDER BY clause to sort the employee’s full names alphabetically:
|
1
2
3
4
5
6
|
SELECT
CONCAT_WS(', ', lastName, firstname) `Full name`
FROM
employees
ORDER BY
`Full name`;
|
The following statement selects the orders whose total amount are greater than 60000. It uses column aliases in GROUP BY and HAVING clauses.
|
1
2
3
4
5
6
7
8
9
|
SELECT
orderNumber `Order no.`,
SUM(priceEach * quantityOrdered) total
FROM
orderdetails
GROUP BY
`Order no.`
HAVING
total > 60000;
|
Notice that you cannot use a column alias in the WHERE clause. The reason is that when MySQL evaluates the WHERE clause, the values of columns specified in the SELECT clause may not be determined yet.
MySQL alias for tables
You can use an alias to give a table a different name. You assign a table an alias by using the ASkeyword as the following syntax:
|
1
|
table_name AS table_alias
|
The alias for the table is called table alias. Like the column alias, the AS keyword is optional so you can omit it.
You often use the table alias in the statement that contains INNER JOIN, LEFT JOIN, self join clauses, and in subqueries.
Let’s look at the customers and orders tables.
Both tables have the same column name:customerNumber.Without using the table alias to qualify the customerNumber column, you will get an error message like:
|
1
|
Error Code: 1052. Column 'customerNumber' in on clause is ambiguous
|
To avoid this error, you use table alias to qualify the customerNumber column:
|
1
2
3
4
5
6
7
8
9
10
|
SELECT
customerName,
COUNT(o.orderNumber) total
FROM
customers c
INNER JOIN orders o ON c.customerNumber = o.customerNumber
GROUP BY
customerName
ORDER BY
total DESC;
|
The query above selects customer name and the number of orders from the customers and orderstables. It uses c as a table alias for the customers table and o as a table alias for the orders table. The columns in the customers and orders tables are referred to via the table aliases.
If you do not use alias in the query above, you have to use the table name to refer to its columns, which makes the query lengthy and less readable as the following:
|
1
2
3
4
5
6
7
8
9
10
|
SELECT
customers.customerName,
COUNT(orders.orderNumber) total
FROM
customers
INNER JOIN orders ON customers.customerNumber = orders.customerNumber
GROUP BY
customerName
ORDER BY
total DESC
|
In this tutorial, we have shown you how to use MySQL alias to make your queries easier to read and simpler to understand.
