《SQL必知必会》第八课 使用函数处理数据 文本处理函数、日期和时间处理函数、数值处理函数

第八课 使用函数处理数据

#使用SQL的数据处理函数
#数据处理函数在格式化、处理和过滤数据中非常有用，但它们在各种SQL实现中很不一致

函数：

SQL可使用函数来处理数据，为数据的转换和处理提供方便。
SQL函数不可移植，即特定SQL实现编写的代码在其他实现中可能不正常
可移植性：指所编码的代码可以在多个系统上运行

如：去掉字符串尾的空格的RTRIM()就是一个函数

SQL函数存在的问题：

每个DBMS都有特定的函数
只有少数几个函数被所有主要的DBMS等同地支持
虽然所有类型的函数一般都可以在每个DBMS中使用，但各个函数的名称和语法可能极为不同

若决定使用函数，应保证做好代码注释

下列3个常用函数及其在各个DBMS中的语法：

函数	语法
提取字符串的组成部分	Access：MID()；DB2、Oracle、PostgreSQL、SQLite：SUBSTR()；MySQL、SQL Server：SUBSTRING()
数据类型转换	Access和 Oracle使用多个函数；DB2、PostgreSQL：CAST()；MySQL、SQL Server：CONVERT()
获取当前日期	Access：NOW()；DB2、PostgreSQL：CURRENT_DATE； MySQL：CURDATE()；Oracle：SYSDATE；SQL Server：GETDATE()；SQLite：DATE()

使用SQL函数：

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

• 用于处理文本字符串（如删除、填充、转换大小写）的文本处理函数
• 用于在数值数据上进行算术操作（如返回绝对值、代数运算）的数值处理函数
• 用于处理日期和时间值并从中提取特定成分（如返回两个日期之差、检查日期有效性）的日期和时间处理函数
• 返回DBMS正使用的特殊信息（如返回用户登录信息）的系统函数

SQL函数可用于SELECT后面的列名，也可作为SELECT语句的其它成分如在WHERE语句中使用等

vendors表：

文本处理函数：

【1】SELECT vend_name,UPPER(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;
#使用UPPER()函数将文本转为大写
#每个供应商列出两次
#第一次为Vendors表中的值，第二次作为vend_name_upcase列转换为大写

常用文本处理函数：

函数	说明
LEFT() （或使用字符串函数）	返回字符串左边的字符
RIGHT() （或使用字符串函数）	返回字符串右边的字符
LENGTH() （也使用DATALENGTH()或LEN()）	返回字符串的长度
LOWER() （Access使用LCASE()）	将字符串转换为小写
UPPER() （Access使用UCASE()）	将字符串转换为大写
LTRIM()	去掉字符串左边的空格
RTRIM()	去掉字符串右边的空格
SOUNDEX()	返回字符串的SOUNDEX值

SOUNDEX()函数解释：
(1)SOUNDEX()函数是一个将任何文本字符串转换为描述其语音表示的字母数字模式的算法
(2)SOUNDEX()函数考虑类似的发音字符和音节，能对字符串进行发音比较，而不是字母比较
(3)多少DBMS提供对SOUNDEX()函数的支持，但Access和PostgreSQL不支持SOUNDEX()函数

Customers表：

【2】SELECT cust_name, cust_contact FROM customers WHERE cust_contact = 'Michael Green';
#Customers表中没有联系名cust_contact为Michael Green，实际为Michelle Green，故不会返回数据

【3】SELECT cust_name, cust_contact FROM customers WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');
#在WHERE子句中使用SOUNDEX()函数进行搜索
#把cust_contact联系名列值和搜索字符串’Michael Green’转换为它们的SOUNDEX值
#因为Michael Green和Michelle Green发音相似，故而SOUNDEX值匹配
#因此WHERE子句正确地过滤出所需数据
#匹配所有发音类似于Michael Green的cust_contact联系名

日期和时间处理函数：

日期和时间采用相应的数据类型存储在表中，每种DBMS都有自己的特殊形式。
日期和时间值以特殊的格式存储
日期和时间函数用来读取、统计、处理这些值，在SQL中具有重要作用，但可移植性差

Orders表：
Orders表中的订单都带有订单日期order_date

【4】SELECT order_num FROM orders WHERE YEAR(order_date) = 2012;
#MySQL中检索2012年的所有订单
#MySQL中使用YEAR()函数从日期中提取年份
#日期处理函数
#SQL Server版本中语句：SELECT order_num FROM orders WHERE DATEPART(yy,order_date) = 2012;
#Access版本中语句：SELECT order_num FROM orders WHERE DATEPART('yyyy',order_date) = 2012;
#PostgreSQL版本中语句：SELECT order_num FROM orders WHERE DATE_PART('year',order_date) = 2012;
#SQLite版本中语句：SELECT order_num FROM orders WHERE strftime('%Y',order_date) = '2012';
#Oracle版本中语句1：SELECT order_num FROM orders WHERE to_number(to_char(order_date,'YYYY')) = 2012;
#Oracle版本中语句2：SELECT order_num FROM orders WHERE order_date BETWEEN to_date('01-01-2012') AND to_date('12-31-2012');
#相同的代码在不同的DBMS中可能不起作用，MySQL中没有DATEPART()函数

数值处理函数：

(1)数值处理函数仅处理数值数据
(2)数值处理函数一般用于代数、三角或几何运算
(3)在主要DBMS的函数中，数值函数最统一、最一致

常用数值处理函数：

函数	说明
SIN()	返回一个角度的正弦
COS()	返回一个角度的余弦
TAN()	返回一个角度的正切
ABS()	返回一个数的绝对值
EXP()	返回一个数的指数值
PI()	返回圆周率
SQRT()	返回一个数的平方根

需要完整代码或书籍PDF版的小伙伴可关注微信公众号：菜田里守望者

打开微信扫一扫关注吧，你们的支持就是我的动力

参考文献：
【1】《SQL必知必会》第四版 人民邮电出版社 [美] Ben Forta 著 钟鸣 刘晓霞 译