MySQL - 第十一章 | 使用資料處理函數

函數

與其他大多數計算機語言一樣，SQL支持利用函數來處理資料。函數一般是在資料上執行的，它給資料的轉換和處理提供了方便。
在前一章中用來去掉串尾空格的RTrim()就是一個函數的例子。

說明：函數沒有SQL的可移植性強能運行在多個系統上的程式碼稱為可移植的(portable)。相對來說多數SQL語句是可移植的，在SQL實現之間有差異時，這些差異通常不那麼難處理。而函數的可移植性卻不強。幾乎每種主要的DBMS的實現都支持其他實現不支持的函數，而且有時差異還很大。

為了程式碼的可移植，許多SQL工程師不贊成使用特殊實現的功能。雖然這樣做很有好處，但不總是利於應用程序的性能。如果不使用這些函數，編寫某些應用程序程式碼會很艱難。必須利用其他方法來實現DBMS非常有效地完成的工作。

如果你決定使用函數，應該保證做好代程式碼註解，以便以後你(或其他人)能確切地知道所編寫SQL程式碼的含義。

使用函數

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

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

準備

CREATE TABLE customers
(
  cust_id      int       NOT NULL AUTO_INCREMENT,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL ,
  PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@coyote.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10002, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '43333', 'USA', 'Jerry Mouse');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10003, 'Wascals', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'rabbit@wascally.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10004, 'Yosemite Place', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Y Sam', 'sam@yosemite.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10005, 'E Fudd', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'E Fudd');


CREATE TABLE orders
(
  order_num  int      NOT NULL AUTO_INCREMENT,
  order_date datetime NOT NULL ,
  cust_id    int      NOT NULL ,
  PRIMARY KEY (order_num)
) ENGINE=InnoDB;

INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20005, '2005-09-01', 10001);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20006, '2005-09-12', 10003);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20007, '2005-09-30', 10004);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20008, '2005-10-03', 10005);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20009, '2005-10-08', 10001);

文本處理函數

上一章中我們已經看過一個文本處理函數的例子，其中使用RTrim()函數來去除列值右邊的空格。下面是另一個例子，這次使用Upper()函數。

mysql> SELECT vend_name, Upper(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;
+----------------+------------------+
| vend_name      | vend_name_upcase |
+----------------+------------------+
| ACME           | ACME             |
| Anvils R Us    | ANVILS R US      |
| Furball Inc.   | FURBALL INC.     |
| Jet Set        | JET SET          |
| Jouets Et Ours | JOUETS ET OURS   |
| LT Supplies    | LT SUPPLIES      |
+----------------+------------------+
6 rows in set (0.01 sec)

分析：正如所見，Upper()將文本轉換為大寫，因此本例子中每個供應商都列出兩次，第一次為vendors表中存儲的值，第二次作為列vend_name_upcase轉換為大寫。

列出了某些常用的文本處理函數：

函數	說明
Left()	返回串左邊的字符
Length()	返回串的長度
Locate()	找出串的一個子串
Lower()	將串轉換為小寫
LTrim()	去掉串左邊的空格
Right()	返回串右邊的字符
RTrim()	去掉串右邊的空格
Soundex()	返回串的SOUNDEX值
SubString()	返回子串的字符
Upper()	將串轉換為大寫

其中的SOUNDEX需要做進一步的解釋：

SOUNDEX是一個將任何文本串轉換為描述其語音表示的字母數字模式的算法。
SOUNDEX考慮了類似的發音字符和音節，使得能對串進行發音比較而不是字母比較。
SOUNDEX不是SQL概念，但MySQL(就像多數DBMS一樣)都提供對SOUNDEX的支持。

使用Soundex()函數的例子:

customers表中有一個顧客Coyote Inc.，其聯繫名為Y.Lee。
但如果這是輸入錯誤，此聯繫名實際應該是Y.Lie，怎麼辦?顯然，按正確的聯繫名搜索不會返回資料。

1 2	mysql> SELECT cust_name, cust_contact FROM customers WHERE cust_contact = 'Y. Lie'; Empty set (0.00 sec)

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

mysql> 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.01 sec)

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

日期和時間處理函數

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

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

列出了某些常用的日期和時間處理函數：

函數	說明
AddDate()	增加一個日期(天、周等)
AddTime()	增加一個時間(時、分等)
CurDate()	返回當前日期
CurTime()	返回當前時間
Date()	返回日期時間的日期部分
DateDiff()	計算兩個日期之差
Date_Add()	高度靈活的日期運算函數
Date_Format()	返回一個格式化的日期或時間串
Day()	返回一個日期的天數部分
DayOfWeek()	對於一個日期，返回對應的星期幾
Hour()	返回一個時間的小時部分
Minute()	返回一個時間的分鐘部分
Month()	返回一個日期的月份部分
Now()	返回當前日期和時間
Second()	返回一個時間的秒部分
Time()	返回一個日期時間的時間部分
Year()	返回一個日期的年份部分

重新復習用WHERE進行資料過濾的一個好時機。迄今為止，我們都是用比較數值和文本的WHERE子句過濾資料，但資料經常需要用日期進行過濾。用日期進行過濾需要注意一些別的問題和使用特殊的MySQL函數。

首先需要注意的是MySQL使用的日期格式。無論你什麼時候指定一個日期，不管是插入或更新表值還是用WHERE子句進行過濾，日期必須為格式yyyy-mm-dd。因此，2005年9月1日，給出為2005-09-01。雖然其他的日期格式可能也行，但這是首選的日期格式，因為它排除了多義性(如：04/05/06是2006年5月4日或2006年4月5日或2004年5月6日或……)。

提示：應該總是使用4位數字的年份支持2位數字的年份，MySQL處理00-69為2000-2069，處理70-99為1970-1999。雖然它們可能是打算要的年份，但使用完整的4位數字年份更可靠，因為MySQL不必做出任何假定。

mysql> SELECT cust_id, order_num FROM orders WHERE order_date = '2005-09-01';
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
+---------+-----------+
1 row in set (0.00 sec)

分析：此SELECT語句正常運行。它檢索出一個訂單記錄，該訂單記錄的order_date為2005-09-01。

但是，使用WHERE 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’失敗。
即使給出具有該日期的一行，也不會把它檢索出來，因為WHERE匹配失敗。
解決辦法是指示MySQL僅將給出的日期與列中的日期部分進行比較，而不是將給出的日期與整個列值進行比較。為此，必須使用Date()函數。

Date(order_date)指示MySQL僅提取列的日期部分，更可靠的 SELECT語句為:

mysql> SELECT cust_id, order_num FROM orders WHERE Date(order_date) = '2005-09-01';
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
+---------+-----------+
1 row in set (0.01 sec)

提示：如果要的是日期，請使用Date() 如果你想要的僅是日期，則使用Date()是一個良好的習慣，即使你知道相應的列只包含日期也是如此。這樣，如果由於某種原因表中以後有日期和時間值，你的SQL程式碼也不用改變。當然，也存在一個Time()函數，在你只想要時間時應該使用它。

在你知道瞭如何用日期進行相等測試後，其他操作符的使用也就很清楚了。

不過，還有一種日期比較需要說明。如果你想檢索出2005年9月下的所有訂單，怎麼辦?簡單的相等測試不行，因為它也要匹配月份中的天數。有幾種解決辦法，其中之一：

mysql> SELECT cust_id, order_num FROM orders WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10003 |     20006 |
|   10004 |     20007 |
+---------+-----------+
3 rows in set (0.01 sec)

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

另外一種辦法(一種不需要記住每個月中有多少天或不需要操心閏年2月的辦法)：

mysql> SELECT cust_id, order_num FROM orders WHERE Year(order_date) = 2005 AND Month(order_date) = 9;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10003 |     20006 |
|   10004 |     20007 |
+---------+-----------+
3 rows in set (0.00 sec)

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

數值處理函數

數值處理函數僅處理數值資料。這些函數一般主要用於代數、三角或幾何運算，因此沒有串或日期—時間處理函數的使用那麼頻繁。
具有諷刺意味的是，在主要DBMS的函數中，數值函數是最一致最統一的函數。

列出一些常用的數值處理函數：

函數	說明
Abs()	返回一個數的絕對值
Cos()	返回一個角度的餘弦
Exp()	返回一個數的指數值
Mod()	返回除操作的餘數
Pi()	返回圓周率
Rand()	返回一個隨機數
Sin()	返回一個角度的正弦
Sqrt()	返回一個數的平方根
Tan()	返回一個角度的正切