Like Share Discussion Bookmark Smile

J.J. Huang   2020-06-18   MySQL   瀏覽次數:

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

函數

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

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

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

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

使用函數

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

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

準備

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
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()函數。

1
2
3
4
5
6
7
8
9
10
11
12
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的聯繫名:

1
2
3
4
5
6
7
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不必做出任何假定。

1
2
3
4
5
6
7
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語句為:

1
2
3
4
5
6
7
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月下的 所有訂單,怎麼辦?簡單的相等測試不行,因為它也要匹配月份中的天 數。有幾種解決辦法,其中之一:

1
2
3
4
5
6
7
8
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月的辦法):

1
2
3
4
5
6
7
8
9
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() 返回一個角度的正切

結語

這篇請時常翻出來觀看,把「文本處理函數」、「日期和時間處理函數」、「數值處理函數」好好多看幾遍,在開發過程中,必然可以使用函數處理掉很多麻煩棘手的問題,或是讓你的業務邏輯可以更簡單的處理。

個人覺得在處理「日期」這塊,是最常見的,如果夠熟悉函數,其實可以解決很多開發上或是業務上的困難;當然跟金融或是統計之類的比較相關得也有「數值」的處理,所以好好記住這些函數,絕對是有幫助的。

註:以上參考了
MySQL必知必会 MySQL Crash Course