> For the complete documentation index, see [llms.txt](https://hezhiqiang-book.gitbook.io/mysql/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://hezhiqiang-book.gitbook.io/mysql/di-yi-zhang/index-4/bie-ming.md).

# 5.1 别名

在本教程中，您将学习如何使用MySQL别名来提高查询的可读性。

MySQL支持两种别名，称为列别名和表别名。下面来详细看看和学习MySQL中的别名。

## MySQL列的别名

有时，列的名称是一些表达式，使查询的输出很难理解。要给列一个描述性名称，可以使用列别名。

以下语句说明了如何使用列别名：

```sql
SELECT 
 [column_1 | expression] AS descriptive_name
FROM table_name;
```

要给列添加别名，可以使用`AS`关键词后跟别名。 如果别名包含空格，则必须引用以下内容：

```sql
SELECT 
 [column_1 | expression] AS `descriptive name`
FROM table_name;
```

因为`AS`关键字是可选的，可以在语句中省略它。 请注意，还可以在表达式上使用别名。

我们来看看[示例数据库(yiibaidb)](http://www.yiibai.com/mysql/sample-database.html)中的`employees`表，其表结构如下所示 -

```sql
mysql> desc employees;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| employeeNumber | int(11)      | NO   | PRI | NULL    |       |
| lastName       | varchar(50)  | NO   |     | NULL    |       |
| firstName      | varchar(50)  | NO   |     | NULL    |       |
| extension      | varchar(10)  | NO   |     | NULL    |       |
| email          | varchar(100) | NO   |     | NULL    |       |
| officeCode     | varchar(10)  | NO   | MUL | NULL    |       |
| reportsTo      | int(11)      | YES  | MUL | NULL    |       |
| jobTitle       | varchar(50)  | NO   |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+
8 rows in set
```

以下查询选择员工的名字和姓氏，并将其组合起来生成全名。 `CONCAT_WS`函数用于连接名字和姓氏。

```sql
SELECT 
    CONCAT_WS(', ', lastName, firstname)
FROM
    employees;
```

执行上面代码，得到以下结果 -

```sql
mysql> SELECT 
    CONCAT_WS(', ', lastName, firstname)
FROM
    employees;
+--------------------------------------+
| CONCAT_WS(', ', lastName, firstname) |
+--------------------------------------+
| Murphy, Diane                        |
| Patterson, Mary                      |
| Firrelli, Jeff                       |
| Patterson, William                   |
| Bondur, Gerard                       |
| Bow, Anthony                         |
| Jennings, Leslie                     |
| Thompson, Leslie                     |
| Firrelli, Julie                      |
| Patterson, Steve                     |
| Tseng, Foon Yue                      |
| Vanauf, George                       |
| Bondur, Loui                         |
| Hernandez, Gerard                    |
| Castillo, Pamela                     |
| Bott, Larry                          |
| Jones, Barry                         |
| Fixter, Andy                         |
| Marsh, Peter                         |
| King, Tom                            |
| Nishi, Mami                          |
| Kato, Yoshimi                        |
| Gerard, Martin                       |
+--------------------------------------+
23 rows in set
```

在上面示例中，列标题很难阅读理解。可以为输出的标题分配一个有意义的列别名，以使其更可读，如以下查询：

```sql
SELECT
 CONCAT_WS(', ', lastName, firstname) AS `Full name`
FROM
 employees;
```

执行上面代码，得到以下结果 -

```sql
mysql> SELECT
 CONCAT_WS(', ', lastName, firstname) AS `Full name`
FROM
 employees;
+--------------------+
| Full name          |
+--------------------+
| Murphy, Diane      |
| Patterson, Mary    |
| Firrelli, Jeff     |
... ...
| King, Tom          |
| Nishi, Mami        |
| Kato, Yoshimi      |
| Gerard, Martin     |
+--------------------+
23 rows in set
```

在MySQL中，可以使用[ORDER BY](http://www.yiibai.com/mysql/order-by.html)，[GROUP BY](http://www.yiibai.com/mysql/group-by.html)和[HAVING](http://www.yiibai.com/mysql/having.html)子句中的列别名来引用该列。

以下查询使用`ORDER BY`子句中的列别名按字母顺序排列员工的全名：

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

执行上面代码，得到以下结果 -

```sql
mysql> SELECT
 CONCAT_WS(' ', lastName, firstname) `Full name`
FROM
 employees
ORDER BY
 `Full name`;
+-------------------+
| Full name         |
+-------------------+
| Bondur Gerard     |
| Bondur Loui       |
| Bott Larry        |
| Bow Anthony       |
| Castillo Pamela   |
| Firrelli Jeff     |
| Firrelli Julie    |
| Fixter Andy       |
| Gerard Martin     |
| Hernandez Gerard  |
| Jennings Leslie   |
| Jones Barry       |
| Kato Yoshimi      |
| King Tom          |
| Marsh Peter       |
| Murphy Diane      |
| Nishi Mami        |
| Patterson Mary    |
| Patterson Steve   |
| Patterson William |
| Thompson Leslie   |
| Tseng Foon Yue    |
| Vanauf George     |
+-------------------+
23 rows in set
```

以下语句查询总金额大于`60000`的订单。它在`GROUP BY`和`HAVING`子句中使用列别名。

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

执行上面查询语句，得到以下结果 -

```sql
mysql> SELECT
 orderNumber `Order no.`,
 SUM(priceEach * quantityOrdered) total
FROM
 orderdetails
GROUP BY
 `Order no.`
HAVING
 total > 60000;
+-----------+----------+
| Order no. | total    |
+-----------+----------+
|     10165 | 67392.85 |
|     10287 | 61402.00 |
|     10310 | 61234.67 |
+-----------+----------+
3 rows in set
```

> 请注意，不能在[WHERE](http://www.yiibai.com/mysql/where.html)子句中使用列别名。原因是当MySQL评估求值`WHERE`子句时，`SELECT`子句中指定的列的值可能尚未确定。

## MySQL表的别名

可以使用别名为表添加不同的名称。使用`AS`关键字在表名称分配别名，如下查询语句语法：

```
table_name AS table_alias
```

该表的别名称为表别名。像列别名一样，`AS`关键字是可选的，所以完全可以省略它。

一般在包含[INNER JOIN](http://www.yiibai.com/mysql/inner-join.html)，[LEFT JOIN](http://www.yiibai.com/mysql/left-join.html)，[self join](http://www.yiibai.com/mysql/self-join.html)子句和子查询的语句中使用表别名。

下面来看看客户(`customers`)和订单(`orders`)表，它们的ER图如下所示 -

![img](/files/-LyNuok2q6lb29W-bN6M)

两个表都具有相同的列名称：`customerNumber`。如果不使用表别名来指定是哪个表中的`customerNumber`列，则执行查询时将收到类似以下错误消息：

```sql
Error Code: 1052. Column 'customerNumber' in on clause is ambiguous
```

为避免此错误，应该使用表别名来限定`customerNumber`列：

```sql
SELECT
 customerName,
 COUNT(o.orderNumber) total
FROM
 customers c
INNER JOIN orders o ON c.customerNumber = o.customerNumber
GROUP BY
 customerName
HAVING total >=5
ORDER BY
 total DESC;
```

执行上面查询语句，得到以下结果 -

```sql
mysql> SELECT
 customerName,
 COUNT(o.orderNumber) total
FROM
 customers c
INNER JOIN orders o ON c.customerNumber = o.customerNumber
GROUP BY
 customerName
HAVING total >=5
ORDER BY
 total DESC;
+------------------------------+-------+
| customerName                 | total |
+------------------------------+-------+
| Euro+ Shopping Channel       |    26 |
| Mini Gifts Distributors Ltd. |    17 |
| Reims Collectables           |     5 |
| Down Under Souveniers, Inc   |     5 |
| Danish Wholesale Imports     |     5 |
| Australian Collectors, Co.   |     5 |
| Dragon Souveniers, Ltd.      |     5 |
+------------------------------+-------+
7 rows in set
```

上面的查询从客户(`customers`)和订单(`orders`)表中选择客户名称和订单数量。 它使用`c`作为`customers`表的表别名，`o`作为`orders`表的表别名。`customers`和`orders`表中的列通过表别名(`c`和`o`)引用。

如果您不在上述查询中使用别名，则必须使用表名称来引用其列，这样的会使得查询冗长且可读性较低，如下 -

```sql
SELECT
 customers.customerName,
 COUNT(orders.orderNumber) total
FROM
 customers
INNER JOIN orders ON customers.customerNumber = orders.customerNumber
GROUP BY
 customerName
ORDER BY
 total DESC
```

在本教程中，我们向演示了如何使用MySQL别名，使查询更易于阅读和更易于理解。


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://hezhiqiang-book.gitbook.io/mysql/di-yi-zhang/index-4/bie-ming.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.