什么是函数,DBMS支持何种函数,以及如何使用这些函数;为什么SQL函数的使用可能会带来问题。
一、函数
与大多数其他计算机语言一样,SQL也可以用函数来处理数据。函数一般是在数据上执行的,为数据的转换和处理提供了方便。
例如SQL必知必会笔记七(创建计算字段)用来去掉字符串尾的空格的RTRIM()就是一个函数。
函数带来的问题:
与几乎所有DBMS都等同地支持SQL语句(如SELECT)不同,每一个DBMS都有特定的函数。事实上,只有少数几个函数被所有主要的DBMS等同地支持。虽然所有类型的函数一般都可以在每个DBMS中使用,但各个函数的名称和语法可能极其不同。
表8-1 DBMS函数的差异
可以看到,与SQL语句不一样,SQL函数不是可移植的。这意味着为特定SQL实现编写的代码在其他实现中可能不正常。
可移植(portable)
所编写的代码可以在多个系统上运行。
提示:是否应该使用函数?
如果你决定使用函数,应该保证做好代码注释,以便以后你(或其他人)能确切地知道所编写的SQL代码的含义。
二、使用函数
大多数的SQL支持以下类型的函数:
• 用于处理文本字符串(如删除或填充值,转换值为大写或小写)的文本函数。
• 用于在数值数据上进行算术操作(如返回绝对值,进行代数运算)的数值函数。
• 用于处理日期和时间值并从这些值中提取特定成分(如返回两个日期之差,检查日期有效性)的日期和时间函数。
• 返回DBMS正使用的特殊信息(如返回用户登录信息)的系统函数。
文本处理函数
表8-2 常见的文本处理函数
表8-2中的SOUNDEX需要做进一步的解释。SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符和音节,使得能对字符串进行发音比较而不是字母比较。虽然SOUNDEX不是SQL概念,但多数DBMS都提供对SOUNDEX的支持。
SOUNDEX转换规则:
a e h i o u w y -> 0
b f p v -> 1
c g j k q s x z -> 2
d t -> 3
l -> 4
m n -> 5
r -> 6
1、提取字符串的首字母作为soundex的第一个值。
2、按照上面的字母对应规则,将后面的字母逐个替换为数字。如果有连续的相等的数字,只保留一个,其余的都删除掉。并去除所有的0。
3、如果结果超过4位,取前四位;如果结果不足4位向后补0。
说明:SOUNDEX支持
Microsoft Access和PostgreSQL不支持SOUNDEX()
另外,如果在创建SQLite时使用了SQLITE_SOUNDEX编译时选项,那么SOUNDEX()在SQLite中就可用。因为SQLITE_SOUNDEX不是默认的编译时选项,所以多数SQLite实现不支持SOUNDEX()。
例如:
Customers表中有一个顾客Kids Place,其联系名为Michelle Green。但如果这是错误的输入,此联系名实际上应该是Michael Green
SELECT cust_name, cust_contact FROM Customers WHERE cust_contact = 'Michael Green';
SOUNDEX搜索:
SELECT cust_name, cust_contact FROM Customers WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');
WHERE子句使用SOUNDEX()函数把cust_contact列值和搜索字符串转换为它们的SOUNDEX值。因为Michael Green和Michelle Green发音相似,所以它们的SOUNDEX值匹配,因此WHERE子句正确地过滤出了所需的数据。
二、日期和时间处理函数
日期和时间采用相应的数据类型存储在表中,每种DBMS都有自己的特殊形式。日期和时间值以特殊的格式存储,以便能快速和有效地排序或过滤,并且节省物理存储空间。
应用程序一般不使用日期和时间的存储格式,因此日期和时间函数总是用来读取、统计和处理这些值。由于这个原因,日期和时间函数在SQL中具有重要的作用。遗憾的是,它们很不一致,可移植性最差。
例如:
Orders表中包含的订单都带有订单日期。
为在SQL Server中检索2012年的所有订单:
SELECT order_num FROM Orders WHERE DATEPART(yy, order_date) = 2012;
在Access中使用如下版本:
SELECT order_num FROM Orders WHERE DATEPART('yyyy', order_date) = 2012;
这个例子(SQL Server和Sybase版本以及Access版本)使用了DATEPART()函数,顾名思义,此函数返回日期的某一部分。DATEPART()函数有两个参数,它们分别是返回的成分和从中返回成分的日期。在此例子中,DATEPART()只从order_date列中返回年份。通过与2012比较,WHERE子句只过滤出此年份的订单。
PostgreSQL版本:
SELECT order_num FROM Orders WHERE DATE_PART('year', order_date) = 2012;
Oracle没有DATEPART()函数,不过有几个可用来完成相同检索的日期处理函数:
SELECT order_num FROM Orders WHERE to_number(to_char(order_date, 'YYYY')) = 2012;
to_char()函数用来提取日期的成分,to_number()用来将提取出的成分转换为数值,以便能与2012进行比较。
完成相同工作的另一方法是使用BETWEEN操作符:
SELECT order_num FROM Orders WHERE order_date BETWEEN to_date('01-01-2012') AND to_date('12-31-2012');
在此例子中,Oracle的to_date()函数用来将两个字符串转换为日期。一个包含2012年1月1日,另一个包含2012年12月31日。BETWEEN操作符用来找出两个日期之间的所有订单。值得注意的是,相同的代码在SQL Server中不起作用,因为它不支持to_date()函数。但是,如果用CONVERT()替换to_date(),当然可以使用这种类型的语句。
MySQL和MariaDB具有各种日期处理函数,但没有DATEPART()。MySQL和MariaDB用户可使用名为YEAR()的函数从日期中提取年份:
SELECT order_num FROM Orders WHERE YEAR(order_date) = 2012;
在SQLite中有个小技巧:
SELECT order_num FROM Orders WHERE strftime('%Y', order_date) = '2012';
这里给出的例子提取和使用日期的成分(年)。按月份过滤,可以进行相同的处理,使用AND操作符可以进行年和月份的比较。
DBMS提供的功能远不只简单的日期成分提取。大多数DBMS具有比较日期、执行基于日期的运算、选择日期格式等的函数。但是,可以看到,不同DBMS的日期-时间处理函数可能不同。关于具体DBMS支持的日期-时间处理函数,请参阅相应的文档。
三、数值处理函数
数值处理函数仅处理数值数据。这些函数一般主要用于代数、三角或几何运算,因此不像字符串或日期-时间处理函数使用那么频繁。
具有讽刺意味的是,在主要DBMS的函数中,数值函数是最一致、最统一的函数。
表8-3 常用数值处理函数