八、使用函数处理数据
加油吧！工程师.png
- 什么是函数?
- DBMS支持哪些函数?
- 如何使用这些函数?
- 为什么SQL函数的使用可能会带来问题?
1、函数
函数一般是在数据上执行的，位数据的转换和处理提供了方便。
函数带来的问题
几乎所有的DBMS都等同的支持SQL语句（例如：SELECT），但是 每一个DBMS都有特定的函数。
只有少数几个函数被所有主要的DBMS等同地支持。
虽然所有类型的函数一般都可以在每个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; SQLite使用DATE()
|
可以看到，与SQL语句不同，SQL函数不是可移植的。这意味着为特定SQL实现编写的代码在其他实现中可能不能用。
可移植（portable）
所编写的代码可以在多个系统上运行。
使用或者不使用函数没有对错之分。如果使用函数，应该保证做好代码注释。
2、使用函数
大多数SQL实现支持一下类型的函数。
- ①用于处理文本字符串（删除、填充、转换值为大写活小写）的文本函数。
- ②用于在数值数据上进行算术操作（返回绝对值、机型袋鼠运算等）的数值函数。
- ③用于处理日期的时间值并从这些值中提取特定成分（例如：返回两个日期之差，检查日期有效性）的日期和时间函数。
- ④用于生成美观好懂的输出内容的格式化函数（如语言形式表达出日期，用货币符号和千分位表示金额）。
- ⑤返回DBMS正确的特殊信息（如返回用户登录信息）的系统函数。
2.1 文本处理函数
RTRIM() 去掉列值右边的空格。
UPPER()将文本转换成大写
SELECT g_name, UPPER(g_name) AS g_name_upcase
FROM Goods
ORDER BY g_name;
- 常用的文本处理函数
| 函数 | 说明 |
|---|---|
| LEFT()（或使用子字符串函数） | 返回字符串左边的字符 |
| RIGHT()（或使用子字符串函数） | 返回字符串右边的字符 |
| LENGTH()（也是用DATALENGTH()或LEN()） | 返回字符串的长度 |
| LOWER() | 将字符串转换为小写
|
| UPPER() | 将字符串转换为大写
|
| LTRIM()（或使用子字符串函数） | 去掉字符串左边的空格
|
| RTRIM() | 去掉字符串右边的空格
|
| SUBSTR()或SUBSTRING() | 提取字符串的组成部分 |
| SOUNDEX() | 返回字符串的SOUNDEX值 |
①SOUNDEX是一个将任何文本串转换为描述其语音标识的字符数字模式的算法。SOUNDEX考虑了类似的发音字符和音节，使得能对字符串进行发音比较而不是字母比较。虽然SOUNDEX不是SQL概念，但多数DBMS凑提供对SOUNDEX的支持。
②PostgreSQL不支持SOUNDEX()函数，多数SQLite实现不支持SOUNDEX()，但是如果在创建SQLite时使用了SQLITE_SOUNDEX编译时选项，那么SOUNDEX()在SQLit中就可用。（SQLITE_SOUNDEX不是默认的编译时选项）
举例：
如果一个顾客表中一个人名字叫“Michael Green”,
但是搜索时候输入错误输入了“Michelle Green”,
如果直接搜是不会返回“Michael Green”的，
但是如果用SOUNDEX()就可以得到“Michael Green”。
//搜不到想要的Michael
SELECT name
FROM Customers
WHERE name = 'Michelle Green'
//可搜到想要的Michael
SELECT name
FROM Customers
WHERE SOUNDEX(name) = SOUNDEX('Michelle Green')
- 因为“Michelle Green”和“Michael Green”的发音相似，所以它们的SOUNDEX值匹配，因此WHERE子句正确的过滤除了所需的数据。
2.2、日期和时间处理函数
日期和时间的函数，在不同的DBMS中很不一致，可移植性很差。这里就不细说了。简单举个列子
例如：查询所有2020年的订单号
SELECT order_number
FROM Orders
WHERE DATEPART(yy, order_data) = 2020;
SELECT order_number
FROM Orders
WHERE DATE_PART('year', order_data) = 2020;
2.3 数值处理函数
数值处理函数仅处理数值数据。这些函数主要用于代数、三角或几何运算。虽然使用频率不高，但是在主要的DBMS函数中，数值函数是最一致的函数。
- 常用的数值处理函数：
| 函数 | 说明 |
|---|---|
| ABS() | 数的绝对值 |
| COS() | 角度的余弦 |
| SIN() | 角度的正弦 |
| PI() | 圆周率π的值 |
| EXP() | 数的指数值 |
| SQRT() | 数的平方根 |
| TAN() | 角度的正切 |
