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()等這些，

標籤：MySQL

上一篇：MySQL快速回顧：高級查詢操作

下一篇：mysql查詢時不區分大小寫