本文介紹什麼是函數，DBMS 支持何種函數，以及如何使用這些函數；還將講解為什麼 SQL 函數的使用可能會帶來問題。 一、函數 與大多數其他電腦語言一樣，SQL 也可以用函數來處理數據。函數一般是在數據上執行的，為數據的轉換和處理提供了方便。 SQL 如何創建計算欄位 中用來去掉字元串尾的空格的 ...
目錄
本文介紹什麼是函數，DBMS 支持何種函數，以及如何使用這些函數；還將講解為什麼 SQL 函數的使用可能會帶來問題。
一、函數
與大多數其他電腦語言一樣，SQL 也可以用函數來處理數據。函數一般是在數據上執行的，為數據的轉換和處理提供了方便。
SQL 如何創建計算欄位 中用來去掉字元串尾的空格的 RTRIM() 就是一個函數。
1.1 函數帶來的問題
在繼續閱讀之前，你應該瞭解使用 SQL 函數所存在的問題。
與幾乎所有 DBMS 都等同地支持 SQL 語句（如 SELECT）不同，每一個 DBMS 都有特定的函數。
事實上，只有少數幾個函數被所有主要的 DBMS 等同地支持。雖然所有類型的函數一般都可以在每個 DBMS 中使用，但各個函數的名稱和語法可能極其不同。
為了說明可能存在的問題，表 1 列出了 3 個常用的函數及其在各個 DBMS 中的語法：
表 1 DBMS 函數的差異
| 函數 | 語法 |
|---|---|
| 提取字元串的組成部分 | DB2、Oracle、PostgreSQL 和 SQLite 使用 SUBSTR()；MariaDB、MySQL 和 SQL Server 使用 SUBSTRING() |
| 數據類型轉換 | Oracle 使用多個函數，每種類型的轉換有一個函數；DB2 和 PostgreSQL 使用 CAST()；MariaDB、MySQL 和 SQL Server 使用 CONVERT() |
| 取當前日期 | DB2 和 PostgreSQL 使用 CURRENT_DATE；MariaDB 和 MySQL 使用 CURDATE()；Oracle 使用 SYSDATE；SQL Server 使用 GETDATE()；SQLite 使用 DATE() |
可以看到，與 SQL 語句不一樣，SQL 函數不是可移植的。這意味著為特定 SQL 實現編寫的代碼在其他實現中可能不能用。
可移植（portable）
所編寫的代碼可以在多個系統上運行。
為了代碼的可移植，許多 SQL 程式員不贊成使用特定於實現的功能。雖然這樣做很有好處，但有的時候並不利於應用程式的性能。
如果不使用這些函數，編寫某些應用程式代碼會很艱難。必須利用其他方法來實現 DBMS 可以非常有效完成的工作。
提示：是否應該使用函數？
現在，你面臨是否應該使用函數的選擇。決定權在你，使用或是不使用也沒有對錯之分。
如果你決定使用函數，應該保證做好代碼註釋，以便以後你自己（或其他人）能確切地知道這些 SQL 代碼的含義。
二、使用函數
大多數 SQL 實現支持以下類型的函數。
- 用於處理文本字元串（如刪除或填充值，轉換值為大寫或小寫）的文本函數。
- 用於在數值數據上進行算術操作（如返回絕對值，進行代數運算）的數值函數。
- 用於處理日期和時間值並從這些值中提取特定成分（如返回兩個日期之差，檢查日期有效性）的日期和時間函數。
- 用於生成美觀好懂的輸出內容的格式化函數（如用語言形式表達出日期，用貨幣符號和千分位表示金額）。
- 返回 DBMS 正使用的特殊信息（如返回用戶登錄信息）的系統函數。
我們在 SQL 如何創建計算欄位 中看到函數用於 SELECT 後面的列名，但函數的作用不僅於此。
它還可以作為 SELECT 語句的其他成分，如在 WHERE 子句中使用，在其他 SQL 語句中使用等，後面會做更多的介紹。
2.1 文本處理函數
在 SQL 如何創建計算欄位 中，我們已經看過一個文本處理函數的例子，其中使用 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 轉換為大寫。
提示：大寫，小寫，大小寫混合
此時你應該已經知道 SQL 函數不區分大小寫，因此
upper(),UPPER(),Upper()都可以，substr(),SUBSTR(),SubStr()也都行。隨你的喜好，不過註意保持風格一致，不要變來變去，否則你寫的程式代碼就不好讀了。
表 2 列出了一些常用的文本處理函數。
表 2 常用的文本處理函數
| 函數 | 說明 |
|---|---|
LEFT()（或使用子字元串函數） |
返回字元串左邊的字元 |
LENGTH()（也使用 DATALENGTH() 或 LEN()） |
返回字元串的長度 |
LOWER() |
將字元串轉換為小寫 |
LTRIM() |
去掉字元串左邊的空格 |
RIGHT()（或使用子字元串函數） |
返回字元串右邊的字元 |
RTRIM() |
去掉字元串右邊的空格 |
SUBSTR() 或 SUBSTRING() |
提取字元串的組成部分（見表 1） |
SOUNDEX() |
返回字元串的 SOUNDEX 值 |
UPPER() |
將字元串轉換為大寫 |
表 2 中的 SOUNDEX 需要做進一步的解釋。
SOUNDEX 是一個將任何文本串轉換為描述其語音表示的字母數字模式的演算法。
SOUNDEX 考慮了類似的發音字元和音節，使得能對字元串進行發音比較而不是字母比較。
雖然 SOUNDEX 不是 SQL 概念，但多數 DBMS 都提供對 SOUNDEX 的支持。
說明：
SOUNDEX支持PostgreSQL 不支持
SOUNDEX()，因此以下的例子不適用於這個 DBMS。另外，如果在創建 SQLite 時使用了
SQLITE_SOUNDEX編譯時選項，那麼SOUNDEX()在 SQLite 中就可用。因為
SQLITE_SOUNDEX不是預設的編譯時選項，所以多數 SQLite 實現不支持SOUNDEX()。
下麵給出一個使用 SOUNDEX() 函數的例子。Customers 表中有一個顧客 Kids Place，其聯繫名為 Michelle Green。但如果這是錯誤的輸入，此聯繫名實際上應該是 Michael Green，該怎麼辦呢？顯然，按正確的聯繫名搜索不會返回數據，如下所示：
SELECT cust_name, cust_contact
FROM 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 子句正確地過濾出了所需的數據。
2.2 日期和時間處理函數
日期和時間採用相應的數據類型存儲在表中，每種 DBMS 都有自己的特殊形式。日期和時間值以特殊的格式存儲，以便能快速和有效地排序或過濾，並且節省物理存儲空間。
應用程式一般不使用日期和時間的存儲格式，因此日期和時間函數總是用來讀取、統計和處理這些值。由於這個原因，日期和時間函數在 SQL 中具有重要的作用。遺憾的是，它們很不一致，可移植性最差。
我們舉個簡單的例子，來說明日期處理函數的用法。Orders 表中包含的訂單都帶有訂單日期。要檢索出某年的所有訂單，需要按訂單日期去找，但不需要完整日期，只要年份即可。
為在 SQL Server 中檢索 2020 年的所有訂單，可如下進行：
SELECT order_num
FROM Orders
WHERE DATEPART(yy, order_date) = 2020;
輸出：
order_num
-----------
20005
20006
20007
20008
20009
這個例子使用了 DATEPART() 函數，顧名思義，此函數返回日期的某一部分。DATEPART()函數有兩個參數，它們分別是返回的成分和從中返回成分的日期。
在此例子中，DATEPART() 只從 order_date 列中返回年份。通過與 2020 比較，WHERE 子句只過濾出此年份的訂單。
下麵是使用名為 DATE_PART() 的類似函數的 PostgreSQL 版本：
SELECT order_num
FROM Orders
WHERE DATE_PART('year', order_date) = 2020;
Oracle 沒有 DATEPART() 函數，不過有幾個可用來完成相同檢索的日期處理函數。例如：
SELECT order_num
FROM Orders
WHERE EXTRACT(year FROM order_date) = 2020;
在這個例子中，EXTRACT() 函數用來提取日期的成分，year 表示提取哪個部分，返回值再與 2020 進行比較。
提示：PostgreSQL 支持
Extract()除了前面用到的
DatePart()，PostgreSQL 也支持Extract()函數，因而也能這麼用。
完成相同工作的另一方法是使用 BETWEEN 操作符：
SELECT order_num
FROM Orders
WHERE order_date BETWEEN to_date('2020-01-01', 'yyyy-mm-dd')
AND to_date('2020-12-31', 'yyyy-mm-dd');
在此例子中，Oracle 的 to_date() 函數用來將兩個字元串轉換為日期。一個包含 2020 年 1 月 1 日，另一個包含 2020 年 12 月 31 日。
BETWEEN 操作符用來找出兩個日期之間的所有訂單。值得註意的是，相同的代碼在 SQL Server 中不起作用，因為它不支持 to_date() 函數。但是，如果用 DATEPART() 替換 to_date()，當然可以使用這種類型的語句。
DB2，MySQL 和 MariaDB 具有各種日期處理函數，但沒有 DATEPART()。DB2，MySQL 和 MariaDB 用戶可使用名為 YEAR() 的函數從日期中提取年份：
SELECT order_num
FROM Orders
WHERE YEAR(order_date) = 2020;
在 SQLite 中有個小技巧：
SELECT order_num
FROM Orders
WHERE strftime('%Y', order_date) = '2020';
這裡給出的例子提取和使用日期的成分（年）。按月份過濾，可以進行相同的處理，使用 AND 操作符可以進行年份和月份的比較。
DBMS 提供的功能遠不止簡單的日期成分提取。大多數 DBMS 具有比較日期、執行日期的運算、選擇日期格式等的函數。
但是，可以看到，不同 DBMS 的日期/時間處理函數可能不同。關於你的 DBMS 具體支持的日期/時間處理函數，請參閱相應的文檔。
2.3 數值處理函數
數值處理函數僅處理數值數據。這些函數一般主要用於代數、三角或幾何運算，因此不像字元串或日期/時間處理函數使用那麼頻繁。
具有諷刺意味的是，在主要 DBMS 的函數中，數值函數是最一致、最統一的函數。表 3 列出一些常用的數值處理函數。
表 3 常用數值處理函數
| 函數 | 說明 |
|---|---|
ABS() |
返回一個數的絕對值 |
COS() |
返回一個角度的餘弦 |
EXP() |
返回一個數的指數值 |
PI() |
返回圓周率 π 的值 |
SIN() |
返回一個角度的正弦 |
SQRT() |
返回一個數的平方根 |
TAN() |
返回一個角度的正切 |
關於具體 DBMS 所支持的算術處理函數，請參閱相應的文檔。
三、小結
本文介紹瞭如何使用 SQL 的數據處理函數。雖然這些函數在格式化、處理和過濾數據中非常有用，但它們在各種 SQL 實現中很不一致。
原文鏈接：https://www.developerastrid.com/sql/sql-functions/
（完）
