MySQL別名

2019-10-16 22:58:02

在本教學中,您將學習如何使用MySQL別名來提高查詢的可讀性。

MySQL支援兩種別名,稱為列別名和表別名。下面來詳細看看和學習MySQL中的別名。

MySQL列的別名

有時,列的名稱是一些表示式,使查詢的輸出很難理解。要給列一個描述性名稱,可以使用列別名。

以下語句說明了如何使用列別名:

SELECT 
 [column_1 | expression] AS descriptive_name
FROM table_name;

要給列新增別名,可以使用AS關鍵詞後跟別名。 如果別名包含空格,則必須參照以下內容:

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

因為AS關鍵字是可選的,可以在語句中省略它。 請注意,還可以在表示式上使用別名。

我們來看看範例資料庫(yiibaidb)中的employees表,其表結構如下所示 -

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函式用於連線名字和姓氏。

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

執行上面程式碼,得到以下結果 -

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

在上面範例中,列標題很難閱讀理解。可以為輸出的標題分配一個有意義的列別名,以使其更可讀,如以下查詢:

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

執行上面程式碼,得到以下結果 -

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 BYGROUP BYHAVING子句中的列別名來參照該列。

以下查詢使用ORDER BY子句中的列別名按字母順序排列員工的全名:

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

執行上面程式碼,得到以下結果 -

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 BYHAVING子句中使用列別名。

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

執行上面查詢語句,得到以下結果 -

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子句中使用列別名。原因是當MySQL評估求值WHERE子句時,SELECT子句中指定的列的值可能尚未確定。

MySQL表的別名

可以使用別名為表新增不同的名稱。使用AS關鍵字在表名稱分配別名,如下查詢語句語法:

table_name AS table_alias

該表的別名稱為表別名。像列別名一樣,AS關鍵字是可選的,所以完全可以省略它。

一般在包含INNER JOINLEFT JOINself join子句和子查詢的語句中使用表別名。

下面來看看客戶(customers)和訂單(orders)表,它們的ER圖如下所示 -

兩個表都具有相同的列名稱:customerNumber。如果不使用表別名來指定是哪個表中的customerNumber列,則執行查詢時將收到類似以下錯誤訊息:

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

為避免此錯誤,應該使用表別名來限定customerNumber列:

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;

執行上面查詢語句,得到以下結果 -

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表的表別名。customersorders表中的列通過表別名(co)參照。

如果您不在上述查詢中使用別名,則必須使用表名稱來參照其列,這樣的會使得查詢冗長且可讀性較低,如下 -

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別名,使查詢更易於閱讀和更易於理解。