這一課介紹什么是函數，DBMS支持何種函數，以及如何使用這些函數；還將講解為什么SQL函數的使用可能會帶來問題。

8.1 函數

與大多數其他計算機語言一樣，SQL也可以用函數來處理數據。函數一般是在數據上執行的，為數據的轉換和處理提供了方便。
前一課中用來去掉字符串尾的空格的RTRIM()就是一個函數。
函數帶來的問題
在學習這一課并進行實踐之前，你應該了解使用SQL函數所存在的問題。
與幾乎所有DBMS都等同地支持SQL語句（如SELECT）不同，每一個DBMS都有特定的函數。事實上，只有少數幾個函數被所有主要的DBMS等同地支持。雖然所有類型的函數一般都可以在每個DBMS中使用，但各個函數的名稱和語法可能極其不同。為了說明可能存在的問題，表8-1列出了3個常用的函數及其在各個DBMS中的語法：
表8-1 DBMS函數的差異

函　　數	語　　法
提取字符串的組成部分	Access使用MID()； DB2、Oracle、PostgreSQL和SQLite使用SUBSTR()； MySQL和SQL Server使用SUBSTRING()
數據類型轉換	Access和Oracle使用多個函數，每種類型的轉換有一個函數； DB2和PostgreSQL使用CAST()； MariaDB、MySQL和SQL Server使用CONVERT()
取當前日期	Access使用NOW()； DB2和PostgreSQL使用CURRENT_DATE； MariaDB和MySQL使用CURDATE()； Oracle使用SYSDATE； SQL Server使用GETDATE()； SQLite使用DATE()

可以看到，與SQL語句不一樣，SQL函數不是可移植的。這表示為特定SQL實現編寫的代碼在其他實現中可能不正常。

可移植（portable）
所編寫的代碼可以在多個系統上運行。

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

提示：是否應該使用函數？
現在，你面臨是否應該使用函數的選擇。決定權在你，使用或是不使用也沒有對錯之分。如果你決定使用函數，應該保證做好代碼注釋，以便以后你（或其他人）能確切地知道所編寫的SQL代碼的含義。

8.2 使用函數

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

• 用于處理文本字符串（如刪除或填充值，轉換值為大寫或小寫）的文本函數。
• 用于在數值數據上進行算術操作（如返回絕對值，進行代數運算）的數值函數。
• 用于處理日期和時間值并從這些值中提取特定成分（如返回兩個日期之差，檢查日期有效性）的日期和時間函數。
• 返回DBMS正使用的特殊信息（如返回用戶登錄信息）的系統函數。
  我們在上一課看到函數用作SELECT語句的列表成分，但函數的作用不僅于此。它還可以作為SELECT語句的其他成分，如在WHERE子句中使用，在其他SQL語句中使用等，后面會做更多的介紹。

8.2.1 文本處理函數

在上一課，我們已經看過一個文本處理函數的例子，其中使用RTRIM()函數來去除列值右邊的空格。下面是另一個例子，這次使用的是UPPER()函數：
輸入▼

SELECT vend_name, UPPER(vend_name) AS vend_name_upcase 
FROM Vendors 
ORDER BY vend_name; 

輸出▼

vend_name	vend_name_upcase
Bear Emporium	BEAR EMPORIUM
Bears R Us	BEARS R US
Doll House Inc.	DOLL HOUSE INC.
Fun and Games	FUN AND GAMES
Furball Inc.	FURBALL INC.
Jouets et ours	JOUETS ET OURS

分析▼
可以看到，UPPER()將文本轉換為大寫，因此本例子中每個供應商都列出兩次，第一次為Vendors表中存儲的值，第二次作為列vend_name_upcase轉換為大寫。
表8-2列出了一些常用的文本處理函數。
表8-2 常用的文本處理函數

函　　數	說　　明
LEFT()（或使用子字符串函數）	返回字符串左邊的字符
LENGTH()（也使用DATALENGTH()或LEN()）	返回字符串的長度
LOWER()（Access使用LCASE()）	將字符串轉換為小寫
LTRIM()	去掉字符串左邊的空格
RIGHT()（或使用子字符串函數）	返回字符串右邊的字符
RTRIM()	去掉字符串右邊的空格
SOUNDEX()	返回字符串的SOUNDEX值
UPPER()（Access使用UCASE()）	將字符串轉換為大寫

表8-2中的SOUNDEX需要做進一步的解釋。SOUNDEX是一個將任何文本串轉換為描述其語音表示的字母數字模式的算法。SOUNDEX考慮了類似的發音字符和音節，使得能對字符串進行發音比較而不是字母比較。雖然SOUNDEX不是SQL概念，但多數DBMS都提供對SOUNDEX的支持。

說明：SOUNDEX支持 Microsoft Access和PostgreSQL不支持SOUNDEX()，因此以下的例子不適用于這些DBMS。
另外，如果在創建SQLite時使用了SQLITE_SOUNDEX編譯時選項，那么SOUNDEX()在SQLite中就可用。因為SQLITE_SOUNDEX不是默認的編譯時選項，所以多數SQLite實現不支持SOUNDEX()。

下面給出一個使用SOUNDEX()函數的例子。Customers表中有一個顧客Kids Place，其聯系名為Michelle Green。但如果這是錯誤的輸入，此聯系名實際上應該是Michael Green，該怎么辦呢？顯然，按正確的聯系名搜索不會返回數據，如下所示：
輸入▼

SELECT cust_name, cust_contactFROM Customers WHERE cust_contact = 'Michael Green'; 

輸出▼

cust_name	cust_contact

現在試一下使用SOUNDEX()函數進行搜索，它匹配所有發音類似于Michael Green的聯系名：
輸入▼

SELECT cust_name, cust_contact 
FROM Customers 
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');  

輸出▼

cust_name	cust_contact
Kids Place	Michelle Green

分析▼
在這個例子中，WHERE子句使用SOUNDEX()函數把cust_contact列值和搜索字符串轉換為它們的SOUNDEX值。因為Michael Green和Michelle Green發音相似，所以它們的SOUNDEX值匹配，因此WHERE子句正確地過濾出了所需的數據。

8.2.2 日期和時間處理函數

日期和時間采用相應的數據類型存儲在表中，每種DBMS都有自己的特殊形式。日期和時間值以特殊的格式存儲，以便能快速和有效地排序或過濾，并且節省物理存儲空間。
應用程序一般不使用日期和時間的存儲格式，因此日期和時間函數總是用來讀取、統計和處理這些值。由于這個原因，日期和時間函數在SQL中具有重要的作用。遺憾的是，它們很不一致，可移植性最差。
我們舉個簡單的例子，來說明日期處理函數的用法。Orders表中包含的訂單都帶有訂單日期。為在SQL Server中檢索2012年的所有訂單，可如下進行：
輸入▼

SELECT order_num 
FROM Orders 
WHERE DATEPART(yy, order_date) = 2012; 

輸出▼

order_num
20005
20006
20007
20008
20009

在Access中使用如下版本：
輸入▼

SELECT order_num 
FROM Orders 
WHERE DATEPART('yyyy', order_date) = 2012; 

分析▼
這個例子（SQL Server和Sybase版本以及Access版本）使用了DATEPART()函數，顧名思義，此函數返回日期的某一部分。DATEPART()函數有兩個參數，它們分別是返回的成分和從中返回成分的日期。在此例子中，DATEPART()只從order_date列中返回年份。通過與2012比較，WHERE子句只過濾出此年份的訂單。
下面是使用名為DATE_PART()的類似函數的PostgreSQL版本：
輸入▼
SELECT order_num FROM Orders WHERE DATE_PART(‘year’, order_date) = 2012;
Oracle沒有DATEPART()函數，不過有幾個可用來完成相同檢索的日期處理函數。例如：
輸入▼

SELECT order_num 
FROM Orders 
WHERE to_number(to_char(order_date, 'YYYY')) = 2012;  

分析▼
在這個例子中，to_char()函數用來提取日期的成分，to_number()用來將提取出的成分轉換為數值，以便能與2012進行比較。
完成相同工作的另一方法是使用BETWEEN操作符：
輸入▼

SELECT order_num 
FROM Orders 
WHERE order_date BETWEEN to_date('01-01-2012') 
AND to_date('12-31-2012'); 

分析▼
在此例子中，Oracle的to_date()函數用來將兩個字符串轉換為日期。一個包含2012年1月1日，另一個包含2012年12月31日。BETWEEN操作符用來找出兩個日期之間的所有訂單。值得注意的是，相同的代碼在SQL Server中不起作用，因為它不支持to_date()函數。但是，如果用DATEPART()替換to_date()，當然可以使用這種類型的語句。
MySQL和MariaDB具有各種日期處理函數，但沒有DATEPART()。MySQL和MariaDB用戶可使用名為YEAR()的函數從日期中提取年份：
輸入▼

SELECT order_num 
FROM Orders 
WHERE YEAR(order_date) = 2012; 

在SQLite中有個小技巧：
輸入▼

SELECT order_num 
FROM Orders 
WHERE strftime('%Y', order_date) = 2012; 

這里給出的例子提取和使用日期的成分（年）。按月份過濾，可以進行相同的處理，指定AND操作符以及年和月份的比較。
DBMS提供的功能遠不止簡單的日期成分提取。大多數DBMS具有比較日期、執行基于日期的運算、選擇日期格式等的函數。但是，可以看到，不同DBMS的日期-時間處理函數可能不同。關于具體DBMS支持的日期-時間處理函數，請參閱相應的文檔。

8.2.3 數值處理函數

數值處理函數僅處理數值數據。這些函數一般主要用于代數、三角或幾何運算，因此不像字符串或日期-時間處理函數使用那么頻繁。
具有諷刺意味的是，在主要DBMS的函數中，數值函數是最一致、最統一的函數。表8-3列出一些常用的數值處理函數。

表8-3 常用數值處理函數

函　　數	說　　明
ABS()	返回一個數的絕對值
COS()	返回一個角度的余弦
EXP()	返回一個數的指數值
PI()	返回圓周率
SIN()	返回一個角度的正弦
SQRT()	返回一個數的平方根
TAN()	返回一個角度的正切

關于具體DBMS所支持的算術處理函數，請參閱相應的文檔。

---

上一篇：第7課 SQL入門之創建計算字段
下一篇：第9課 SQL入門之匯總數據