MySQL快速回顧：計算欄位與函數

9.1 計算欄位

存儲在資料庫表中的數據一般不是應用程式所需要的格式。比如：

• 如果想要在一個欄位中既顯示公司名，又顯示公式的地址，但這兩個信息一般包含在不同的表列中。
• 城市、州和郵政編碼存儲在不同的列中，但郵件標簽列印程式卻需要把它們作為一個恰當格式的欄位檢索出來。
• 列數據是大小寫混合的，但報表程式需要把所有數據按大寫表示出來。

在上面舉的例子中，存儲在表中的數據都不是應用程式所需要的。我們需要直接資料庫中檢索出轉換、計算或格式化過的數據；而不是檢索出數據，然後在客戶機應用程式或報告程式中重新格式化。

所以就需要計算欄位。計算欄位並不實際存儲於資料庫表中，而是運行時在SELECT語句內創建的。

這裡的欄位（field）基本上跟列（column）的意思相同，經常互換使用，不過資料庫列一般稱為列，而術語欄位通常用在計算欄位的連接上。

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

9.1.1 計算欄位的使用

舉一個創建由兩列組成的標題的簡單例子。

vendors表包含供應商名和位置信息。假如要生成一個供應商報表，需要在供應商的名字中按照name(location)這樣的格式列出供應商的位置。

此報表需要單個值，而表中數據存儲在兩個列vend_name和vend_country中。此外，需要用括弧將vend_country括起來，這些東西都沒有明確存儲在資料庫表中。來看看如何用SELECT來編寫這樣的格式。

拼接（concatenate）將值聯結到一起構成單個值。

解決的方法就是將兩個列拼接起來。在MySQL的SELECT語句中，可使用CONCAT()函數來拼接兩個列。

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

結果：

SELECT CONCAT(vend_name, '(', vend_country, ')')
 FROM vendors
 ORDER BY vend_name;

輸出：

+-------------------------------------------+
| CONCAT(vend_name, '(', vend_country, ')') |
+-------------------------------------------+
| ACME(USA)                                 |
| Anvils R Us(USA)                          |
| Jet Set(England)                          |
| LT Supplies(USA)                          |
+-------------------------------------------+
4 rows in set (0.06 sec)

解釋：

• CONCAT()拼接串），即把多個串連接起來形成一個較長的串。CONCAT()需要一個或多個指定的串，各個串之間用逗號分隔。(關於更多函數的使用後面會講)
• 上面的SELECT語句連接以下4個元素：
  • 存儲在vend_name列中的名字；
  • 包括一個空格和一個左圓括弧的串；
  • 存儲在vend_country列中的國家；
  • 包括一個右圓括弧的串。

9.1.2 使用別名

你拿上面那條語句去執行，會發現新計算出的列名，列名好長而且列名的意義不能明確看出。實際上它是沒有名稱，它只是一個值。如果僅在SQL查詢工具中查看一下結果，這樣沒什麼不好。但是，一個未命名的列不能用於客戶機應用中，因為客戶機沒有辦法引用它。這時候SQL就引出別名。

別名（alias）是一個欄位或值的替換名。別名用AS關鍵字賦予。

使用別名修改上面的SQL語句

SELECT CONCAT(vend_name, '(', vend_country, ')')
 AS vend_title
 FROM vendors
 ORDER BY vend_name;

AS也可省略，只要在需要重命名的後面空一格。

SELECT CONCAT(vend_name, '(', vend_country, ')')
  vend_title
 FROM vendors
 ORDER BY vend_name;

輸出：

+------------------+
| vend_title       |
+------------------+
| ACME(USA)        |
| Anvils R Us(USA) |
| Jet Set(England) |
| LT Supplies(USA) |
+------------------+
4 rows in set (0.06 sec)

這樣任何客戶機都可以按別名引用這個列，看起來是一個實際的表列一樣。

別名不止用於計算欄位中，還可以在實際的表列名包含不符合規定的字元（含空格）時重新命名它，在原來的名字含混或容易誤解時擴充它，等。但記住，並不是真正會去重命名錶中的實際列名。

別名有時候也稱為導出列（derived column），不管稱為什麼，它們所代表的都是相同的東西。

9.1.3 執行算術計算

計算欄位的另一常見用途是讀檢索出的數據進行算術計算。比如，一個訂單表order中含有物品價格price和物品數量quantity，需要求物品總價格。那麼只需要 物品價格乘以物品數據即可。

SELECT order_id, order_price*order_quantiry 
 AS expanded_price
 FROM order;

MySQL算術操作符：+、-、*、/。

SELECT可用通過使用計算欄位來測試。

SELECT 2*3;

小結：介紹了計算欄位以及如何創建計算欄位。此外還學瞭如何創建和使用別名，以便應用程式能引用計算欄位。

9.2 函數

SQL支持利用函數來處理數據。

可移植性（portable）: 能運行在多個系統上的代碼。
多數SQL語句是可移植的，在SQL實現之間有差異時，這些差異通常不那麼難處理。而函數的可移植性卻不強。幾乎每種主要的DBMS的實現都支持其他實現不支持的函數，而且有時差異還很大。
為了代碼的可移植性，很多SQL程式員不贊成使用特殊實現的功能。雖然這樣有好處，但是不使用這些函數，編寫某些應用程式代碼會很難。
如果決定使用函數，應該保證做好代碼註釋，以便以後你（或其他人）能確切地知道所編寫的SQL代碼的含義。

9.2.1 使用函數

大多數SQL實現支持以下類型的函數

• 用於處理文本串（如刪除或填充值，轉換值為大寫或小寫）的文本函數
• 用於在數值上進行算術操作（如返回絕對值，進行代數運算）的數值函數。
• 用於處理日期和時間值並從這些值中提取特定成分（例如，返回兩個日期之差，檢查日期有效性等）的日期和時間函數。
• 返回DBMS正使用的特殊信息（如返回用戶登錄信息，檢查版本細節）的系統函數

9.2.2 文本處理函數

之前已經介紹TRIM()：去空格的函數。類似的函數如下：
參考：菜鳥教程

上面的SOUNDEX需要進一步解釋：SOUNDEX是一個而將任何文本串轉換為描述其語音表示的字母數字模式的演算法。SOUNDEX考慮了類似的發音字元和音節，使得能對串進行發音比較而不是字母比較。雖然SOUNDEX不是SQL概念，但MySQL（就像很大DBMS一樣）都提供對SOUNDEX的支持。

比如，創建一張表名為customers，表中包含顧客（cust_name）和聯繫名（cust_contact）。

CREATE TABLE customers(
    cust_name varchar(20) PRIMARY KEY,
    cust_contact varchar(20)
);

現在假設有一個顧客Coyote Inc. , 其聯繫名為 Y.Lee。但如果這是輸入錯誤的結果，此聯繫名實際應該是Y.Lie，怎麼辦？顯然，按正確的聯繫名搜索不會返回數據，如下：

SELECT cust_name, cust_contact
FROM customers
WHERE cust_contact = 'Y.Lie';

輸出：

Empty set

現在試下使用SOUNDEX()函數進行搜索，它匹配所有發音類似於Y.Lie的聯繫名：

SELECT cust_name, cust_contact
FROM customers
WHERE SOUNDEX(cust_contact) = SOUNDEX('Y.Lie');

輸出：

+-------------+--------------+
| cust_name   | cust_contact |
+-------------+--------------+
| Coyote Inc. | Y.Lee        |
+-------------+--------------+
1 row in set (0.05 sec)

在這個例子中，WHERE子句使用SOUNDEX()函數來轉換cust_contact列值和搜索串為它們的SOUNDEX值。因為Y.Lee和Y.lie發音相似，所以它們的SOUNDEX值匹配，因此WHERE子句正確地過濾了所需的數據。

9.2.3 日期和時間處理函數

日期和時間採用相應的數據類型和特殊的格式存儲，以便快速和有效地排序或過濾，並節省物理存儲空間。

一般，應用程式不使用來存儲日期和時間的格式，因此日期和時間函數總是被用來讀取、統計和處理這些值。所以，日期和時間函數在MySQL語言中很重要。

表格轉載：菜鳥教程

更多參考：菜鳥教程

需要註意的是MySQL使用的日期格式。無論是什麼時候指定一個日期，或是插入或更新等，日期必須為格式yyyy-mm-dd。所以，2020年1月15號，給出的是2020-01-15.雖然其他的日期格式可能也行，但這是首選的日期格式，因為它排除了多義性（如，04/05/06是2006年5月4號還是2006年4月5號或...）。

應該總是使用4位數字的年份。MySQL雖然支持2位數字的年份，比如處理00-69位2000-2069。雖然它們可能是打算要的年份，但使用完整的4位數字年份更可靠。

在資料庫表中檢索時間日期，比如：

SELECT cust_id, order_num
FROM orders
WHERE order_date = '2005-09-01';

這樣寫的 order_date = '2005-09-01' 可靠嗎？如果order_date的數據類型是datetime，這種類型存儲日期及時間值，那麼在例表中的值全都具有時間值00:00:00，但實際上很可能並不總是這樣。如果用當前日期和時間存儲訂單日期（因此我們得知道訂單日期和下訂單當前的時間），怎麼辦？？比如，存儲的order_date值為2005-09-01 11:30:05，則WHERE order_date = '2005-09-01'就失敗。

解決的方式：讓MySQL僅將給出的日期與列中的日期部分進行比較，而不是將給出的日期與整個列值進行比較。所以得使用DATE()函數。DATE(order_date)表示MySQL僅提取列的日期部分，所以修改如下：

SELECT cust_id, order_num
FROM orders
WHERE DATE(order_date) = '2005-09-01';

所以，對於日期的數據要特別註意，如果要的是日期，使用DATE()，如果要的是天，使用DAY()，如果要的是月，使用MONTH()等。最好明確要的是什麼格式的日期，即使知道相應的列只包含日期也應該加上函數。

還有一種日期比較需要說明。如果要檢索2005年9月下的所有訂單，怎麼辦？？簡單的相等測試肯定不行，因為它也要匹配月份中的天數。提供以下的解決方法：

SELECT cust_id, order_num
FROM orders
WHERE DATE(order_date) BETWEEN '2005-09-01' AND '2005-09-30';

其中，BETWEEN操作符用來把2005-09-01和2005-09-30定義為一個要匹配的日期範圍。
還有另一種：

SELECT cust_id, order_num
FROM orders
WHERE YEAR(order_date) = 2005 AND MONTH(order_date) = 9;

解釋：YEAR()是一個從日期（或日期時間）中返回年份的函數。類似，MONTH從日期中返回月份。因此WHERE YEAR(order_date) = 2005 AND MONTH(order_date) = 9檢索出order_date為2005年9月的所有行。

9.2.3 數值處理函數

數值處理函數僅處理數值數據。一般主要用於代數、三角或幾何運算。

在主要DBMS的函數中，數值函數是最統一最一致的函數。

小結：介紹瞭如何使用SQL的數據處理函數，主要註意日期函數的使用。這些函數不需要死記硬背，忘了就拿出來看。當然，最好簡單的函數就記一記，比如：ABS()、SQRT()、YEAR()、HOUR()、DAY()等這些。