SQL必知必会——使用函数处理数据

1、函数

与大多数其他计算机语言一样，SQL也可以用函数来处理数据。函数一般是在数据上执行的，为数据的转换和处理提供了方便。

与几乎所有DBMS都等同地支持SQL语句（如SELECT）不同，每一个DBMS都有特定的函数。事实上，只有少数几个函数被所有主要的DBMS等同地支持。虽然所有类型的函数一般都可以在每个DBMS中使用，但各个函数的名称和语法可能极其不同。为了说明可能存在的问题，下表列出了3个常用的函数及其在各个DBMS中的语法：

可以看到，与SQL语句不一样，SQL函数不是可移植的。这意味着为特定SQL实现编写的代码在其他实现中可能不能用。

2、使用函数

大多数SQL实现支持以下类型的函数。

• 用于处理文本字符串（如删除或填充值，转换值为大写或小写）的文本函数。
• 用于在数值数据上进行算术操作（如返回绝对值，进行代数运算）的数值函数。
• 用于处理日期和时间值并从这些值中提取特定成分（如返回两个日期之差，检查日期有效性）的日期和时间函数。
• 用于生成美观好懂的输出内容的格式化函数（如用语言形式表达出日期，用货币符号和千分位表示金额）​。
• 返回DBMS正使用的特殊信息（如返回用户登录信息）的系统函数。

2.1、文本处理函数

使用的是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   |
+-----------------+------------------+
6 rows in set (0.07 sec)

下表列出了一些常用的文本处理函数。

表中的SOUNDEX需要做进一步的解释。SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符和音节，使得能对字符串进行发音比较而不是字母比较。虽然SOUNDEX不是SQL概念，但多数DBMS都提供对SOUNDEX的支持。

PostgreSQL不支持SOUNDEX()，因此以下的例子不适用于这个DBMS另外，如果在创建SQLite时使用了SQLITE_SOUNDEX编译时选项，那么SOUNDEX()在SQLite中就可用。因为SQLITE_SOUNDEX不是默认的编译时选项，所以多数SQLite实现不支持SOUNDEX()。

下面给出一个使用SOUNDEX()函数的例子。Customers表中有一个顾客Kids Place，其联系名为MichelleGreen。但如果这是错误的输入，此联系名实际上应该是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 |
+------------+----------------+
1 row in set (0.06 sec)

在这个例子中，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进行比较。完成相同工作的另一方法是使用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;