MySQL 别名使用

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 difficult to understand. To give a column a descriptive name, you use a column alias. The following illustrates how to use the column alias:

SELECT [col1 | 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 the it as shown in the syntax. 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 combine them to produce the full names. The CONCAT_WS function is used to concatenate first name and last name.

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:

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:

SELECT CONCAT_WS(', ',lastName, firstname) `Full name` FROM employees ORDER BY `Full name`;

The following statement selects the order whose total amount is greater than 60000. It uses column aliases in GROUP BY and HAVING clauses.

SELECT orderNumber `Order no.`, SUM(priceEach * quantityOrdered) Total FROM orderDetails GROUP BY `Order no.` HAVING total > 60000;

Notice that you cannot use column alias in the WHERE clause. The reason is that when MySQL evaluates the WHEREclause, the values of columns specified in the SELECT clause may not be determined yet.

MySQL alias for tables

An alias also gives a table a different name. You assign a table an alias by using the AS keyword as the following syntax:

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:

See the following query:

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 orders tables. 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 customersand orders tables is referred 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.

In this tutorial, we have shown you how to use MySQL alias to make your query easy to read and simple to understand.

原文链接：http://outofmemory.cn/mysql/mysql-alias