MySQL 之别名语法

旧城等待， 2022-07-16 09:39 196阅读 0赞

您可以重命名表或列，暂时给予另一名被称为别名。

使用表的别名，是指在一个特定的SQL语句重命名一个表。重命名是一个临时的变化，并在数据库中实际的表的名称不会改变。

列的别名是用来重命名表中的列一个特定的SQL查询的目的。

# **语法:** #

表别名的基本语法如下：

SELECT column1, column2....
    FROM table_name AS alias_name
    WHERE [condition];

列别名的基本语法如下：

SELECT column_name AS alias_name
    FROM table_name
    WHERE [condition];

# 注：AS 可省略！    #

# **实例:** #

考虑以下两个表，（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 | Ramesh  |  25 |   1560 |
    |  4 | kaushik |  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 | Ramesh        |
    |           3 | kaushik       |
    |           4 | kaushik       |
    |           5 | Hardik        |
    |           6 | Komal         |
    |           7 | Muffy         |