如何使用SQL函数处理数据

database

目录

  • 一、函数
    • 1.1 函数带来的问题
  • 二、使用函数
    • 2.1 文本处理函数
    • 2.2 日期和时间处理函数
    • 2.3 数值处理函数
  • 三、小结

本文介绍什么是函数,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 GreenMichelle 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://developerastrid.com/sql/sql-functions/

(完)

以上是 如何使用SQL函数处理数据 的全部内容, 来源链接: utcz.com/z/536504.html

回到顶部