MySQL - 第十章 | 建立計算欄位

計算欄位

存儲在資料庫表中的資料一般不是應用程序所需要的格式。下面舉幾個例子：

如果想在一個欄位中既顯示公司名，又顯示公司的地址，但這兩個訊息一般包含在不同的表列中。
城市、州和郵政編碼存儲在不同的列中(應該這樣)，但郵件標籤打印程序卻需要把它們作為一個恰當格式的欄位檢索出來。
列資料是大小寫混合的，但報表程序需要把所有資料按大寫表示出來。
物品訂單表存儲物品的價格和數量，但不需要存儲每個物品的總價格(用價格乘以數量即可)。為打印發票，需要物品的總價格。
需要根據表資料進行總數、平均數計算或其他計算。
在上述每個例子中，存儲在表中的資料都不是應用程序所需要的。我們需要直接從資料庫中檢索出轉換、計算或格式化過的資料;而不是檢索出資料，然後再在客戶端應用程序或報告程序中重新格式化。

提示：客戶端與服務器的格式可在SQL語句內完成的許多轉換和格式化工作都可以直接在客戶端應用程序內完成。但一般來說，在資料庫服務器上完成這些操作比在客戶端中完成要快得多，因為DBMS是設計來快速有效地完成這種處理的。

準備

CREATE TABLE vendors
(
  vend_id      int      NOT NULL AUTO_INCREMENT,
  vend_name    char(50) NOT NULL ,
  vend_address char(50) NULL ,
  vend_city    char(50) NULL ,
  vend_state   char(5)  NULL ,
  vend_zip     char(10) NULL ,
  vend_country char(50) NULL ,
  PRIMARY KEY (vend_id)
) ENGINE=InnoDB;

INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1001,'Anvils R Us','123 Main Street','Southfield','MI','48075', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1002,'LT Supplies','500 Park Street','Anytown','OH','44333', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1003,'ACME','555 High Street','Los Angeles','CA','90046', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1004,'Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1005,'Jet Set','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1006,'Jouets Et Ours','1 Rue Amusement','Paris', NULL,'45678', 'France');

CREATE TABLE orderitems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL ,
  item_price decimal(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;

INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, 'ANV01', 10, 5.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, 'ANV02', 3, 9.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 3, 'TNT2', 5, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 4, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, 'JP2000', 1, 55);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, 'TNT2', 100, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, 'FC', 50, 2.50);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, 'OL1', 1, 8.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, 'SLING', 1, 4.49);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 4, 'ANV03', 1, 14.99);

拼接欄位

假如要生成一個供應商報表，需要在供應商的名字中按照name(location)這樣的格式列出供應商的位置。
此報表需要單個值，而表中資料存儲在兩個列vend_name和vend_ country中。
此外，需要用括號將vend_country括起來，這些東西都沒有明確存儲在資料庫表中。

術語：拼接(concatenate) 將值聯結到一起構成單個值。

把兩個列拼接起來。在MySQL的SELECT語句中，可使用Concat()函數來拼接兩個列。

提示：MySQL的不同之處多數DBMS使用+或||來實現拼接，MySQL則使用Concat()函數來實現。當把SQL語句轉換成MySQL語句時一定要把這個區別銘記在心。

mysql> SELECT concat(vend_name, ' (', vend_country, ')') FROM vendors ORDER BY vend_name;
+--------------------------------------------+
| concat(vend_name, ' (', vend_country, ')') |
+--------------------------------------------+
| ACME (USA)                                 |
| Anvils R Us (USA)                          |
| Furball Inc. (USA)                         |
| Jet Set (England)                          |
| Jouets Et Ours (France)                    |
| LT Supplies (USA)                          |
+--------------------------------------------+
6 rows in set (0.00 sec)

分析：Concat()拼接串，即把多個串連接起來形成一個較長的串。

Concat()需要一個或多個指定的串，各個串之間用逗號分隔。上面的SELECT語句連接以下4個元素:

存儲在vend_name列中的名字。
包含一個空格和一個左圓括號的串。
存儲在vend_country列中的國家。
包含一個右圓括號的串。

從上述輸出中可以看到，SELECT語句返回包含上述4個元素的單個列(計算欄位)。

通過刪除資料右側多餘的空格來整理資料，這可以使用MySQL的RTrim()函數來完成。

mysql> SELECT concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') FROM vendors ORDER BY vend_name;
+----------------------------------------------------------+
| concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') |
+----------------------------------------------------------+
| ACME (USA)                                               |
| Anvils R Us (USA)                                        |
| Furball Inc. (USA)                                       |
| Jet Set (England)                                        |
| Jouets Et Ours (France)                                  |
| LT Supplies (USA)                                        |
+----------------------------------------------------------+
6 rows in set (0.00 sec)

說明：Trim函數 MySQL除了支持RTrim()(正如剛才所見，它去掉串右邊的空格)，還支持LTrim()(去掉串左邊的空格)以及Trim()(去掉串左右兩邊的空格)。

使用別名

從前面的輸出中可以看到，SELECT語句拼接地址欄位工作得很好。但此新計算列的名字是什麼呢?實際上它沒有名字，它只是一個值。如果僅在SQL查詢工具中查看一下結果，這樣沒有什麼不好。但是，一個未命名的列不能用於客戶端應用中，因為客戶端沒有辦法引用它。

為了解決這個問題，SQL支持列別名。別名(alias)是一個欄位或值的替換名。別名用AS關鍵字賦予。

mysql> SELECT concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name;
+-------------------------+
| vend_title              |
+-------------------------+
| ACME (USA)              |
| Anvils R Us (USA)       |
| Furball Inc. (USA)      |
| Jet Set (England)       |
| Jouets Et Ours (France) |
| LT Supplies (USA)       |
+-------------------------+
6 rows in set (0.00 sec)

分析：SELECT語句本身與以前使用的相同，只不過這裡的語句中計算欄位之後跟了文本AS vend_title。它指示SQL建立一個包含指定計算的名為vend_title的計算欄位。從輸出中可以看到，結果與以前的相同，但現在列名為vend_title，任何客戶端應用都可以按名引用這個列，就像它是一個實際的表列一樣。

提示：別名的其他用途別名還有其他用途。常見的用途包括在實際的表列名包含不符合規定的字符(如空格)時重新命名它，在原來的名字含混或容易誤解時擴充它，等等。

說明：導出列別名有時也稱為導出列(derivedcolumn)，不管稱為什麼，它們所代表的都是相同的東西。

執行算術計算

計算欄位的另一常見用途是對檢索出的資料進行算術計算。舉一個例子，orders表包含收到的所有訂單，orderitems表包含每個訂單中的各項物品。

mysql> SELECT prod_id, quantity, item_price FROM orderitems WHERE order_num = 20005;
+---------+----------+------------+
| prod_id | quantity | item_price |
+---------+----------+------------+
| ANV01   |       10 |       5.99 |
| ANV02   |        3 |       9.99 |
| TNT2    |        5 |      10.00 |
| FB      |        1 |      10.00 |
+---------+----------+------------+
4 rows in set (0.01 sec)

item_price列包含訂單中每項物品的單價。如下匯總物品的價格(單價乘以訂購數量):

mysql> SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM orderitems WHERE order_num = 20005;
+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| ANV01   |       10 |       5.99 |          59.90 |
| ANV02   |        3 |       9.99 |          29.97 |
| TNT2    |        5 |      10.00 |          50.00 |
| FB      |        1 |      10.00 |          10.00 |
+---------+----------+------------+----------------+
4 rows in set (0.01 sec)

分析：輸出中顯示的expanded_price列為一個計算欄位，此計算為quantity*item_price。客戶端應用現在可以使用這個新計算列，就像使用其他列一樣。

操作符	說明
+	加
-	減
*	乘
/	除

提示：如何測試計算SELECT提供了測試和試驗函數與計算的一個很好的辦法。雖然SELECT通常用來從表中檢索資料，但可以省略FROM子句以便簡單地訪問和處理表達式。例如，SELECT 3*2;將返回6，SELECT Trim(‘abc’);將返回abc，而SELECT Now()利用Now()函數返回當前日期和時間。通過這些例子，可以明白如何根據需要使用SELECT進行試驗。