如何使用 SQL 函数处理数据

vaivxuanzi

已于 2022-05-12 17:03:25 修改

阅读量554

点赞数

分类专栏： SQL 文章标签： sql

于 2022-05-12 11:58:52 首次发布

原文链接：https://www.developerastrid.com/sql/sql-functions/

版权

SQL 专栏收录该内容

22 篇文章 6 订阅

订阅专栏

文章目录

本文介绍什么是函数，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()`	返回一个角度的正切