SQL必需掌握的100个重要知识点：使用函数处理数据

8.1 函数
与大多数其他计算机语言一样，SQL 也可以用函数来处理数据。函数一
般是在数据上执行的，为数据的转换和处理提供了方便。
前一课中用来去掉字符串尾的空格的 RTRIM() 就是一个函数。
函数带来的问题
在学习这一课并进行实践之前，你应该了解使用 SQL 函数所存在的
问题。
与几乎所有 DBMS都等同地支持 SQL语句（如 SELECT ）不同，每一个
DBMS都有特定的函数。事实上，只有少数几个函数被所有主要的 DBMS
等同地支持。虽然所有类型的函数一般都可以在每个 DBMS中使用，但
各个函数的名称和语法可能极其不同。为了说明可能存在的问题，表 8-1
列出了 3个常用的函数及其在各个 DBMS中的语法：

可以看到，与 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 转换为大写。
提示：大写，小写，大小写混合
此时你应该已经知道SQL函数不区分大小写，因此 upper(), UPPER(),
Upper() 都可以， substr(), SUBSTR(), SubStr() 也都行。随你的
喜好，不过注意保持风格一致，不要变来变去，否则你写的程序代码
就不好读了。
表 8-2列出了一些常用的文本处理函数。

 

表 8-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
子句正确地过滤出了所需的数据。
8.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具体支持的
日期 − 时间处理函数，请参阅相应的文档。
8.2.3 数值处理函数
数值处理函数仅处理数值数据。这些函数一般主要用于代数、三角或几
何运算，因此不像字符串或日期 − 时间处理函数使用那么频繁。
具有讽刺意味的是，在主要 DBMS的函数中，数值函数是最一致、最统
一的函数。表 8-3列出一些常用的数值处理函数。

 

关于具体 DBMS所支持的算术处理函数，请参阅相应的文档。
8.3 小结
这一课介绍了如何使用 SQL的数据处理函数。虽然这些函数在格式化、
处理和过滤数据中非常有用，但它们在各种 SQL实现中很不一致。