SQL 使用数据处理函数
一、函数:
- 函数一般是在数据上执行的,为数据的转换和处理提供了方便。
1.1:函数带来的问题:
- 事实上,只有少数几个函数被所有主要的DBMS等同地支持。虽然所有类型的函数一般都可以在每个DBMS中使用,但各个函数的名称和语法可能及其不同。
- DBMS函数的差异:
函数 | 语法 |
提取字符串的组成部分 | Access使用MID();DB2、Oracle、Post-greSQL和SQLite使用SUBSTR();MySQL和SQL Server使用SUBSTRING() |
数据类型转换 | Access和Oracle使用多个函数,每种类型的转换有一个函数;DB2和PostgreSQL使用CAST();MariaDB、MySQL和SQL Server使用CONVERT() |
取当前日期 | Access使用NOW();DB2和PostgreSQL使用CURRENT_DATE;MariaDB和MySQL使用CURDATE();Oracle使用SYSDATE;SQLServer使用GETDATE();SQLite使用DATE() |
- 可以看到,SQL语句不一样,SQL函数不是可移植的。这表示为特定的SQL实现编写的代码在其他实现中可能不正常。
- 可移植:
-
所编写的代码可以在多个系统上运行
。 - 为了代码的可移植,许多SQL程序员不赞成使用特定于实现的功能。虽然这样做可以很有好处,
但有的时候并不利于应用程序的性能
。
- 是否应该使用函数?
- `如果你使用函数,应该保证做好代码的注释,以便维护。
二、使用函数:
- 大多数SQL实现支持以下类型的函数。
1.用于处理文本字符串
(如删除或填充值,转换值为大写或小写)的文本函数。
2.用于在数值数据上进行算术操作
(返回绝对值,进行代数运算)的数值函数。
3.用于处理日期和时间
值并从这些值中提取特定成分(如返回两个日期之差,检查日期有效性)的日期和时间函数。
4.返回DBMS正使用的特殊信息
(如返回用户登录信息)的系统函数。
2.1:文本处理函数:
- 使用
UPPER()函数将文本转换成大写
:
- 举个栗子:
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)
- 可以看到,UPPER()将文本转换为了大写,可以从输出结果看,第一为vendors表中存储的值,第二次作为vend_name_upcase转换为大写。
- 常用的文本处理函数
函数 | 说明 |
LEFT() ( 或使用子字符串函数 ) | 返回字符串左边的字符 |
LENGTH() ( 也使用DATALENGTH() 或 LEN() ) | 返回字符串的长度 |
LOWER() ( Access使用 LCASE() ) | 将字符转换为小写 |
LTRIM() | 去掉字符串左边的空格 |
RIGHT() (或使用子字符串函数) | 返回字符串后边的字符 |
RTRIM() | 去掉字符串后边的空格 |
SOUNDEX() | 返回字符串的SOUNDEX值 |
UPPER() | 将字符串转换为大写 |
2.2:日期和时间处理函数:
日期和时间采用相应的数据类型存储在表中,每种DBMS都有自己的特殊方式。
日期和时间值以特殊的格式存储,以便能快速和有效地排序或过滤,并且节省物理存储空间。
- 应用程序一般不使用日期和时间的存储格式,因此日期和时间函数总是用来读取、统计和处理这些值。
SELECT order_num FROM orders WHERE YEAR(order_date)=2012;
- 输出结果如下:
+-----------+
| order_num |
+-----------+
| 20005 |
| 20006 |
| 20007 |
| 20008 |
| 20009 |
+-----------+
- 注意:举得栗子提取和使用日期的成分(年)。按月份过滤,可以进行相同的处理,指定AND操作符以及年和月份的比较。
2.3:数值处理函数:
数值处理函数仅处理数值数据。
- 这些函数一般主要用于代数、三角或几何运算。
- 常用数值处理函数:
函数 | 说明 |
ABS() | 返回一个数的绝对值 |
COS() | 返回一个角度的余弦 |
EXP() | 返回一个数的指数值 |
PI() | 返回圆周率 |
SIN() | 返回一个角度的正弦 |
SQRT() | 返回一个数的平方根 |
TAN() | 返回一个角度的正切 |