别名

通过使用 SQL，可以为表名称或列名称指定别名。

基本上，创建别名是为了让列名称的可读性更强。

列的SQL别名语法

SELECT column_name AS alias_name
FROM table_name;

表的SQL别名语法

SELECT column_name(s)
FROM table_name AS alias_name;

AS 关键词可以忽略不写，用空格隔开即可。

演示数据库

在本教程中，我们将使用 LintCode 样本数据库。

下面是选自 "teachers" 教师表的数据：

下面是 "courses" 课程表的数据：

1 列的别名实例Ⅰ

下面的 SQL 语句指定了两个别名，一个是 name 列的别名为 ‘ teacher_name’，一个是 country 列的别名为 ‘ teacher_country’。

提示：

如果别名包含空格，要求使用双引号或方括号，建议不使用空格，而使用 '_'。

SELECT name AS teacher_name, country teacher_name
FROM teachers;

执行结果输出

mysql> SELECT name AS teacher_name, country teacher_name
    -> FROM teachers;
+------------------+--------------+
| teacher_name     | teacher_name |
+------------------+--------------+
| Eastern Heretic  | CN           |
| Northern Beggar  | CN           |
| Western Venom    | USA          |
| Southern Emperor | CN           |
| Linghu Chong     | CN           |
+------------------+--------------+
5 rows in set (0.00 sec)

练习题：列的别名Ⅰ

查询所有教师的 id 和姓名（name），并为 name 起别名为 teacher_name。

SELECT id, name AS teacher_name
FROM teachers;

执行结果输出

+----+------------------+
| id | teacher_name     |
+----+------------------+
|  1 | Eastern Heretic  |
|  2 | Northern Beggar  |
|  3 | Western Venom    |
|  4 | Southern Emperor |
|  5 | Linghu Chong     |
+----+------------------+

2 列的别名实例Ⅱ

在下面的 SQL 语句中，我们把两个列（age 和 country）结合在一起，并创建一个名为 "teacher_info" 的别名。

SELECT name, CONCAT(age, ', ', country) AS teacher_info
FROM teachers;

其中

CONCAT 方法可以将所有参数进行拼接成一个字符串。

执行结果输出

mysql> SELECT name, CONCAT(age, ', ', country) AS teacher_info
    -> FROM teachers;
+------------------+--------------+
| name             | teacher_info |
+------------------+--------------+
| Eastern Heretic  | 20, CN       |
| Northern Beggar  | 21, CN       |
| Western Venom    | 28, USA      |
| Southern Emperor | 21, CN       |
| Linghu Chong     | 18, CN       |
+------------------+--------------+
5 rows in set (0.00 sec)

练习题：列的别名Ⅱ

查询所有教师的 id 和姓名（name）和 age，将 age 后面拼接一个字符 ‘ 岁’ ，并为 age 起别名为 teacher_age

SELECT id, name, CONCAT(age, '岁') AS teacher_age
FROM teachers;

执行结果输出

+----+------------------+-------------+
| id | name             | teacher_age |
+----+------------------+-------------+
|  1 | Eastern Heretic  | 20岁        |
|  2 | Northern Beggar  | 21岁        |
|  3 | Western Venom    | 28岁        |
|  4 | Southern Emperor | 21岁        |
|  5 | Linghu Chong     | 18岁        |
+----+------------------+-------------+

3 表的别名实例

下面的 SQL 语句查询所有课程的 id ，课程名和对应教师的姓名。我们使用 "teachers" 和 "courses" 表，并分别为它们指定表别名 "t" 和 "c"（通过使用别名让 SQL 更简短）：

-- 不使用别名写法 --
SELECT courses.id, courses.name, teachers.name
FROM teachers, courses where teachers.id = courses.teacher_id;

-- 使用别名的写法 --
SELECT c.id, c.name, t.name
FROM teachers t, courses c where t.id = c.teacher_id;

执行输出结果

mysql> SELECT c.id, c.name, t.name
    -> FROM teachers t, courses c where t.id = c.teacher_id;
+----+-------------------------+------------------+
| id | name                    | name             |
+----+-------------------------+------------------+
|  1 | Senior Algorithm        | Southern Emperor |
|  2 | System Design           | Western Venom    |
|  3 | Django                  | Western Venom    |
|  4 | Web                     | Southern Emperor |
|  5 | Big Data                | Eastern Heretic  |
|  6 | Artificial Intelligence | Western Venom    |
|  7 | Java P6+                | Western Venom    |
|  8 | Data Analysis           | Eastern Heretic  |
| 10 | Object Oriented Design  | Southern Emperor |
| 12 | Dynamic Programming     | Eastern Heretic  |
+----+-------------------------+------------------+
10 rows in set (0.00 sec)

练习题：表的别名

查询所有课程的 id ，课程名和对应教师的姓名。我们使用 "teachers" 和 "courses" 表，并分别为它们指定表别名 "t" 和 "c"，因为课程名和教师姓名的列名都是 'name' ，要求为课程名起别名为 'course_name'，为教师姓名起别名为 'teacher_name' ：

SELECT c.id, c.name course_name, t.name teacher_name
FROM teachers t, courses c where t.id = c.teacher_id;

执行输出结果

+----+-------------------------+------------------+
| id | course_name             | teacher_name     |
+----+-------------------------+------------------+
|  1 | Senior Algorithm        | Southern Emperor |
|  2 | System Design           | Western Venom    |
|  3 | Django                  | Western Venom    |
|  4 | Web                     | Southern Emperor |
|  5 | Big Data                | Eastern Heretic  |
|  6 | Artificial Intelligence | Western Venom    |
|  7 | Java P6+                | Western Venom    |
|  8 | Data Analysis           | Eastern Heretic  |
| 10 | Object Oriented Design  | Southern Emperor |
| 12 | Dynamic Programming     | Eastern Heretic  |
+----+-------------------------+------------------+

4 AS 总结

在下面的情况下，使用别名很有用：

• 在查询中涉及超过一个表

• 在查询中使用了函数

• 列名称很长或者可读性差

• 需要把两个列或者多个列结合在一起