通过使用 SQL,可以为表名称或列名称指定别名(Alias)。
SQL 别名(Aliases)
SQL 别名用于为表或表中的列提供临时名称。
SQL 别名通常用于使列名更具可读性。
SQL 一个别名只存在于查询期间。
列的 SQL Alias 语法
SELECT column_name AS alias_name
FROM table_name;
表的 SQL Alias 语法
SELECT column_name(s)
FROM table_name AS alias_name;
演示数据库
以下是"Customers" 表中的数据:
下面是选自 "Orders" 表的数据:
列的 Alias 实例
以下SQL语句创建两个别名,一个用于ID列,另一个用于姓名列:
实例
SELECT ID as CustomerID,
姓名 AS Customer
FROM Customers;
以下SQL语句创建两个别名,一个用于姓名列,一个用于城市列。注:如果别名包含空格,则需要双引号或方括号:
实例
SELECT 姓名 AS Customer,
城市 AS [City Name]
FROM Customers;
以下SQL语句创建一个名为“地址”的别名,它包含四列(姓名,地址,邮编,城市 and 省份):
SELECT 姓名,
地址 + ', ' +
邮编 + ' ' +
城市 + ', ' +
省份 AS 地址
FROM Customers;
注意:要使上面的SQL语句在MySQL中工作,请使用以下命令:
SELECT 姓名,
CONCAT(地址,', ',
邮编,', ',
城市,', ',
省份) AS Address
FROM Customers;
在下列情况下使用别名是有用的:
- 查询涉及多个表
- 用于查询函数
- 需要把两个或更多的列放在一起
- 列名长或可读性差
表别名的基本语法如下:
SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];
列别名的基本语法如下:
SELECT column_name AS alias_name
FROM table_name WHERE [condition];
示例:
考虑下面两个数据表,(a)CUSTOMERS 表,如下:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
(b)另一个是 ORDERS 表,如下所示:
+-----+---------------------+-------------+--------+
|OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 | 1500 |
| 101 | 2009-11-20 00:00:00 | 2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 | 2060 |
+-----+---------------------+-------------+--------+
下面是表别名的用法:
SQL> SELECT C.ID, C.NAME, C.AGE, O.AMOUNT
FROM CUSTOMERS AS C, ORDERS AS O
WHERE C.ID = O.CUSTOMER_ID;
上面语句的运行结果如下所示:
+----+----------+-----+--------+
| ID | NAME | AGE | AMOUNT |
+----+----------+-----+--------+
| 3 | kaushik | 23 | 3000 |
| 3 | kaushik | 23 | 1500 |
| 2 | Khilan | 25 | 1560 |
| 4 | Chaitali | 25 | 2060 |
+----+----------+-----+--------+
下面是列别名的用法:
SQL> SELECT ID AS CUSTOMER_ID,
NAME AS CUSTOMER_NAME
FROM CUSTOMERS
WHERE SALARY IS NOT NULL;
其运行结果如下所示:
+-------------+---------------+
| CUSTOMER_ID | CUSTOMER_NAME |
+-------------+---------------+
| 1 | Ramesh |
| 2 | Khilan |
| 3 | kaushik |
| 4 | Chaitali |
| 5 | Hardik |
| 6 | Komal |
| 7 | Muffy |
+-------------+---------------+