8-使用函数处理数据

8.1-函数

SQL可以用函数来处理数据。函数一般是在数据上执行的，为数据的转换和处理提供了方便。

8.1.1 函数带来的问题

每种DBMS都有特定的函数，只有很少一部分函数，是被所有主要的DBMS等同的支持。

虽然所有的类型的函数一般都可以在每个DBMS中使用，但每个函数的名称和语法可能及其不同。

下面是三个常用的函数以及其在各个数据库中的语法：

可以看到，与SQL不一样，SQL函数是不可移植的。许多SQL程序员不赞成使用特定的函数实现特定的功能。虽然这么做有好处，但是有时候不利于应用的性能。

8.2-使用函数

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

• 用于处理文本字符串（如删除、填充值，转换大小写）的函数。

• 用于在数值数据上进行算术操作（如返回绝对值，进行代数运算）的数值函数。

• 用于处理日期和时间，并从这些值中提取某些我们想要的成分（如返回两个日期之差，检查日期的有效性）的日期和时间函数。

• 用于生成可读性比较好的格式化函数（如用语言形式表达出日期，用货币符号和千分位表示金额）。

• 返回DBMS正使用的特殊信息（如返回用户登录信息）的系统函数。

8.2.1 文本处理函数

上述内容中有一个文本处理函数，RTRIM()用来去除某列值右边的空格。

这次我们使用UPPER() 将英文字符串全部转换为大写。

select vend_name,UPPER(vend_name) as vend_name_upcase
from vendors
order by vend_name;

 

由上述输出结果我们可以看到，左边列是原始数据，右边列是我们使用文本处理函数后得到的数据。

大写、小写、大小写混合

SQL函数不区分大小写，因此，upper()、UPPER()、Upper()三个函数是同一个作用。

substr()、SUBSTR()、Substr()也是同样的道理。

虽然不区分大小写，但是还是要有自己的风格，不要变来变去，使得编写的代码可读性较差。

常用的文本处理函数：

函数	说明
left() (或使用子字符串函数)	返回字符串左边的字符
lenfth() (也可以使用datalength() 或者 len())	返回字符串的长度
lower()	将字符串转换为小写
ltrim()	去掉字符串左边的空格
rtrim()	去掉字符串右边的空格
right() （或使用子字符串函数）	返回字符串右边的字符
substr()或者substring()	提取字符串的组成部分
soundex()	返回字符串的soundex值
upper()	将字符串转换为大写

soundex是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。soundex考虑了类似的发音字符和音节，使得能对字符串进行发音比较而不是字母比较。

虽然soundex不是SQL概念，但大多数DBMS都提供了对soundex的支持。

soundex支持 PostgreSQL不支持soundex()，因此以下的例子不适用于这个DBMS。另外，如果在创建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";

显然，输出的行为0。

如果使用soundex()函数进行搜索，他匹配所有发音类似于Michael Green的联系名。

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子句正确地过滤出了所需的数据。

8.2.2 日期和时间处理函数

日期和时间值以特殊格式存储，以便能快速有效的排序或者过滤，并且节省物理存储空间。

应用程序一般不使用日期和时间的存储格式，因此日期和时间函数总是用来读取、统计和处理这些值。由于这个原因，日期和时间函数在SQL中具有重要的作用。但是每个DBMS几乎都不一样。

例子：Orders表中包含的订单都带有订单日期。要检索出某年的所有订单，需要按订单日期去找，但不需要完整日期，只要年份即可。

在SQL Server中检索2020年所有订单

select order_num
from orders
where datepart(yy,order_date) = 2020;

在本机的MySQL环境下执行上述语句，会出现：ERROR 1305 (42000): FUNCTION databases.datepart does not exist

提示我们该函数不存在。

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';

上述SQL语句用来提取和使用日期的年。按月份过滤，可以进行相同的处理。

DBMS提供的功能远不止简单的日期成分提取。大多数DBMS具有比较日期、执行日期的运算、选择日期格式等的函数。但是，可以看到，不同DBMS的日期−时间处理函数可能不同。

8.2.3 数值处理函数

数值处理函数，主要用来处理数值数据，这些函数主要用来处理代数、三角和几何，所以，一般情况下，数值处理函数不像日期和时间处理函数使用那么频繁。

但是，数值处理函数是各个DBMS最统一的函数。

练习

1. 我们的商店已经上线了，正在创建顾客账户。所有用户都需要登录名，默认登录名是其名称和所在城市的组合。编写SQL语句，返回顾客ID（cust_id）、顾客名称（cust_name）和登录名（user_login），其中登录名全部为大写字母，并由顾客联系人的前两个字符（cust_contact）和其所在城市的前三个字符（cust_city）组成。例如，我的登录名是BEOAK（Ben Forta，居住在Oak Park）。提示：需要使用函数、拼接和别名。

   select cust_id,cust_name,
           upper(Concat(substring(cust_contact,1,2),substring(cust_city,1,3))) as user_login
   from customers;

   

    

2. 编写SQL语句，返回2020年1月的所有订单的订单号（order_num）和订单日期（order_date），并按订单日期排序。你应该能够根据目前已学的知识来解决此问题，但也可以开卷查阅DBMS文档。

   select order_num,order_date
   from orders
   order by order_date;

   

   我见青山多妩媚，料青山间我应如是。