day07-使用函数处理数据

day07-使用函数处理数据

函数

什么是函数，DBMS 支持何种函数，以及如何使用这些函数；还将讲解为什么 SQL 函数的使用可能会带来问题。




与大多数其他计算机语言一样，SQL 也可以用函数来处理数据。函数一般是在数据上执行的，为数据的转换和处理提供了方便。

前一课中用来去掉字符串尾的空格的 RTRIM()就是一个函数。

函数带来的问题
在学习这一课并进行实践之前，你应该了解使用 SQL 函数所存在的问题。与几乎所有 DBMS 都等同地支持 SQL 语句（如 SELECT）不同，每一个DBMS 都有特定的函数。事实上，只有少数几个函数被所有主要的 DBMS等同地支持。虽然所有类型的函数一般都可以在每个 DBMS 中使用，但各个函数的名称和语法可能极其不同。为了说明可能存在的问题，表 8-1列出了 3 个常用的函数及其在各个 DBMS 中的语法：

函数	语法
提取字符串的组成 部分	DB2、Oracle、PostgreSQL和SQLite使用SUBSTR()；MariaDB、 MySQL和SQL Server使用SUBSTRING()
数据类型转换	Oracle使用多个函数，每种类型的转换有一个函数；DB2和 PostgreSQL使用CAST()；MariaDB、MySQL和SQL Server使用
取当前日期	DB2和PostgreSQL使用CURRENT_DATE；MariaDB和MySQL使用 CURDATE()；Oracle使用SYSDATE；SQL Server使用GETDATE()；QLite使用DATE()

可以看到，与 SQL 语句不一样，SQL 函数不是可移植的。这意味着为特定 SQL 实现编写的代码在其他实现中可能不能用。

可移植（portable） 
所编写的代码可以在多个系统上运行。

为了代码的可移植，许多 SQL 程序员不赞成使用特定于实现的功能。虽然这样做很有好处，但有的时候并不利于应用程序的性能。如果不使用这些函数，编写某些应用程序代码会很艰难。必须利用其他方法来实现DBMS 可以非常有效完成的工作。

提示：是否应该使用函数？ 
现在，你面临是否应该使用函数的选择。决定权在你，使用或是不使用也没有对错之分。如果你决定使用函数，应该保证做好代码注释，以便以后你自己（或其他人）能确切地知道这些 SQL 代码的含义。

使用函数

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

 用于处理文本字符串（如删除或填充值，转换值为大写或小写）的文本函数。
 用于在数值数据上进行算术操作（如返回绝对值，进行代数运算）的数值函数。
 用于处理日期和时间值并从这些值中提取特定成分（如返回两个日期之差，检查日期有效性）的日期和时间函数。
 用于生成美观好懂的输出内容的格式化函数（如用语言形式表达出日期，用货币符号和千分位表示金额）。
 返回 DBMS 正使用的特殊信息（如返回用户登录信息）的系统函数。
我们在上一课看到函数用于 SELECT 后面的列名，但函数的作用不仅于此。它还可以作为 SELECT 语句的其他成分，如在 WHERE 子句中使用，在其他 SQL 语句中使用等，后面会做更多的介绍。

文本处理函数

SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
FROM Vendors
ORDER BY vend_name;


输出：
mysql> 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   |
+-----------------+------------------+
6 rows in set (0.00 sec)

mysql> 





可以看到，UPPER()将文本转换为大写，因此本例子中每个供应商都列出两次，第一次为 Vendors 表中存储的值，第二次作为列 vend_name_upcase 转换为大写。



注意：

大写，小写，大小写混合 
此时你应该已经知道SQL函数不区分大小写，因此upper(), UPPER(), Upper()都可以，substr(), SUBSTR(), SubStr()也都行。随你的喜好，不过注意保持风格一致，不要变来变去，否则你写的程序代码就不好读了。

函数	说明
LEFT()（或使用子字符串函数）	返回字符串左边的字符
LENGTH()（也使用DATALENGTH()或LEN()）	返回字符串的长度
LOWER()	将字符串转换为小写
LTRIM()	去掉字符串左边的空格
RIGHT()（或使用子字符串函数）	返回字符串右边的字符
RTRIM()	去掉字符串右边的空格
SUBSTR()或SUBSTRING()	提取字符串的组成部分
SOUNDEX()	返回字符串的SOUNDEX值
UPPER()	将字符串转换为大写

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



输出：

mysql> SELECT cust_name, cust_contact
    -> FROM Customers
    -> WHERE cust_contact = 'Michael Green';
Empty set (0.00 sec)

mysql> 




现在试一下使用 SOUNDEX()函数进行搜索，它匹配所有发音类似于 Michael 
Green 的联系名：


SELECT cust_name, cust_contact
FROM Customers
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');


输出：
mysql> SELECT cust_name, cust_contact
    -> FROM Customers
    -> WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');
+------------+----------------+
| cust_name  | cust_contact   |
+------------+----------------+
| Kids Place | Michelle Green |
+------------+----------------+
1 row in set (0.00 sec)

mysql> 




在这个例子中，WHERE 子句使用 SOUNDEX()函数把 cust_contact 列值和搜索字符串转换为它们的 SOUNDEX 值。因为 Michael Green 和Michelle Green 发音相似，所以它们的 SOUNDEX 值匹配，因此 WHERE子句正确地过滤出了所需的数据。

日期和时间处理函数

日期和时间采用相应的数据类型存储在表中，每种 DBMS 都有自己的特殊形式。日期和时间值以特殊的格式存储，以便能快速和有效地排序或过滤，并且节省物理存储空间。


应用程序一般不使用日期和时间的存储格式，因此日期和时间函数总是用来读取、统计和处理这些值。由于这个原因，日期和时间函数在 SQL中具有重要的作用。遗憾的是，它们很不一致，可移植性最差。

我们举个简单的例子，来说明日期处理函数的用法。Orders 表中包含的订单都带有订单日期。要检索出某年的所有订单，需要按订单日期去找，但不需要完整日期，只要年份即可。

为在 SQL Server 中检索 2020 年的所有订单，可如下进行：



SELECT order_num
FROM Orders
WHERE DATEPART(yy, order_date) = 2020;


输出：



这个例子使用了 DATEPART()函数，顾名思义，此函数返回日期的某一部分。DATEPART()函数有两个参数，它们分别是返回的成分和从中返回成分的日期。在此例子中，DATEPART()只从 order_date 列中返回年份。通过与 2020 比较，WHERE 子句只过滤出此年份的订单。
下面是使用名为 DATE_PART()的类似函数的 PostgreSQL 版本：



SELECT order_num
FROM Orders
WHERE DATE_PART('year', order_date) = 2020;








输出：







SELECT order_num
FROM Orders
WHERE EXTRACT(year FROM order_date) = 2020;


输出：
mysql> SELECT order_num
    -> FROM Orders
    -> WHERE EXTRACT(year FROM order_date) = 2020;
+-----------+
| order_num |
+-----------+
|     20005 |
|     20006 |
|     20007 |
|     20008 |
|     20009 |
+-----------+
5 rows in set (0.00 sec)

mysql> 




在这个例子中，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;



输出:
mysql> SELECT order_num
    -> FROM Orders
    -> WHERE YEAR(order_date) = 2020;
+-----------+
| order_num |
+-----------+
|     20005 |
|     20006 |
|     20007 |
|     20008 |
|     20009 |
+-----------+
5 rows in set (0.00 sec)





在 SQLite 中有个小技巧：

SELECT order_num
FROM Orders
WHERE strftime('%Y', order_date) = '2020';

这里给出的例子提取和使用日期的成分（年）。按月份过滤，可以进行相同的处理，使用 AND 操作符可以进行年份和月份的比较。
DBMS 提供的功能远不止简单的日期成分提取。大多数 DBMS 具有比较日期、执行日期的运算、选择日期格式等的函数。但是，可以看到，不同 DBMS 的日期−时间处理函数可能不同。关于你的 DBMS 具体支持的日期−时间处理函数，请参阅相应的文档。

数值处理函数

数值处理函数仅处理数值数据。这些函数一般主要用于代数、三角或几何运算，因此不像字符串或日期−时间处理函数使用那么频繁。具有讽刺意味的是，在主要 DBMS 的函数中，数值函数是最一致、最统一的函数。下述表列出一些常用的数值处理函数。

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

小结

介绍了如何使用 SQL 的数据处理函数。虽然这些函数在格式化、处理和过滤数据中非常有用，但它们在各种 SQL 实现中很不一致。

小练习

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

-- DB2, PostgreSQL
SELECT cust_id, cust_name,
        UPPER(LEFT(cust_contact, 2)) || UPPER(LEFT(cust_city, 3)) AS user_login
FROM customers;
-- Oracle, SQLite
SELECT cust_id, cust_name,
        UPPER(SUBSTR(cust_contact, 1, 2)) || UPPER(SUBSTR(cust_city, 1, 3)) AS user_login
 FROM customers;
-- MySQL
SELECT cust_id, cust_name,
        CONCAT(UPPER(LEFT(cust_contact, 2)), UPPER(LEFT(cust_city, 3))) AS user_login
FROM Customers;
-- SQL Server
SELECT cust_id, cust_name,
        UPPER(LEFT(cust_contact, 2)) + UPPER(LEFT(cust_city, 3)) AS user_login
FROM customers;





mysql> SELECT cust_id, cust_name,
    ->         CONCAT(UPPER(LEFT(cust_contact, 2)), UPPER(LEFT(cust_city, 3))) AS user_login
    -> FROM Customers;
+------------+---------------+------------+
| cust_id    | cust_name     | user_login |
+------------+---------------+------------+
| 1000000001 | Village Toys  | JODET      |
| 1000000002 | Kids Place    | MICOL      |
| 1000000003 | Fun4All       | JIMUN      |
| 1000000004 | Fun4All       | DEPHO      |
| 1000000005 | The Toy Store | KICHI      |
+------------+---------------+------------+
5 rows in set (0.00 sec)

mysql> 


SELECT 
    cust_id,                  -- 选择客户ID
    cust_name,                -- 选择客户姓名
    CONCAT(                   -- 连接以下两部分字符串
        UPPER(LEFT(cust_contact, 2)),   -- 1. 取联系人姓名的前2字符并转为大写
        UPPER(LEFT(cust_city, 3))       -- 2. 取城市名称的前3字符并转为大写
    ) AS user_login           -- 将连接结果命名为 user_login
FROM 
    Customers;                -- 从客户表中查询
    
    



LEFT(str, N)
作用：截取字符串 str 左侧的 N 个字符
示例：LEFT('北京', 2) → '北京' 
UPPER(str)
作用：将字符串 str 全部转为大写字母
示例：UPPER('ab') → 'AB' 
CONCAT(str1, str2)
作用：拼接多个字符串为一个新字符串
特性：若任一输入为 NULL，则返回 NULL
示例：CONCAT('HE', 'BEI') → 'HEBEI' 

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

-- DB2, MariaDB, MySQL
SELECT order_num, order_date
FROM Orders
WHERE YEAR(order_date) = 2020 AND MONTH(order_date) = 1
ORDER BY order_date;

-- Oracle, PostgreSQL
SELECT order_num, order_date
FROM Orders
WHERE EXTRACT(year FROM order_date) = 2020 AND EXTRACT(month FROM order_date) = 1
ORDER BY order_date;

-- PostgreSQL
SELECT order_num, order_date
FROM Orders
WHERE DATE_PART('year', order_date) = 2020
AND DATE_PART('month', order_date) = 1
ORDER BY order_date;

-- SQL Server
SELECT order_num, order_date
FROM Orders
WHERE DATEPART(yy, order_date) = 2020 AND DATEPART(mm, order_date) = 1
ORDER BY order_date;

-- SQLite
SELECT order_num, order_date
FROM Orders
WHERE strftime('%Y', order_date) = '2020'
 AND strftime('%m', order_date) = '01'
ORDER BY order_date;





mysql> SELECT order_num, order_date
    -> FROM Orders
    -> WHERE YEAR(order_date) = 2020 AND MONTH(order_date) = 1
    -> ORDER BY order_date;
+-----------+---------------------+
| order_num | order_date          |
+-----------+---------------------+
|     20006 | 2020-01-12 00:00:00 |
|     20007 | 2020-01-30 00:00:00 |
+-----------+---------------------+
2 rows in set (0.00 sec)

mysql>