SQL单行函数

SQL单行函数

2017年10月29日

17:19

格式：返回值 函数名称（参数）

单行函数按类型分为：

• 字符串函数
• 数值函数
• 日期函数
• 转换函数
• 通用函数

字符串函数

	函数名称	描述
1	字符串 UPPER(列 | 字符串)	将传入的字符串变为大写字母形式
2	字符串 LOWER(列 | 字符串)	将传入的字符串变为小写字母形式
3	字符串 INITCAP(列 | 字符串)	开头首字母大写，其他的字母全部变为小写
4	数字 LENGTH(列 | 字符串)	取得指定字符串的长度
5	字符串 SUBSTR(列 | 字符串 , 开始索引 , [长度])	进行字符串的截取，如果没有设置长度，表示从开始索引一直截取到结尾
6	字符串 REPLACE(列 | 字符串 , 旧内容 , 新内容)	将指定字符串的数据以新数据替换旧数据

统一将内容转换成需要的格式

• SELECT UPPER('hello') FROM dual ;
• SELECT LOWER('hello') FROM dual ;

首字母大写其他字母小写

• SELECT ename , INITCAP(ename) FROM emp ;

获取字符串的长度

• SELECT LENGTH('helloworld!!!') FROM dual ;
• SELECT * FROM emp WHERE LENGTH(ename) = 5 ;

字符串截取

从指定位置截取到结尾：SYBSTR(列 | 字符串 , 开始索引 )

截取部分内容：SYBSTR(列 | 字符串 , 开始索引 , [长度)

在Oracle里面，所有字符串首字母的索引都是1，如果设置的是0，也会按照1的方式来处理。

• SELECT SYBSTR('HELLOWORLD') FROM dual；

截取3个字符：

前3个

• SELECT ename , SUBSTR(ename,1,3) FROM emp ;

后3个

• SELECT ename , SUBSTR(ename,LENGTH(ename)-2) FROM emp ;
• SELECT ename , SUBSTR(ename,-3) FROM emp ;

 

 

数值函数

No	函数名称	描述
1	数字 ROUND(列 | 数字 [, 小数位])	实现数据的四舍五入，可以设置保留小数位
2	数字 TRUNC(列 | 数字 [, 小数位])	实现数据的截取，即：不进位
3	数字 MOD(列 | 数字 , 列 | 数字])	求模

 

ROUND函数：如果没有设置小数点，那么会直接不保留小数点进行进位；

如果设置了小数点就会在指定的小数位上实现四舍五入；

如果为负，表示进行整数位的进位。

• SELECT ROUND(129.23233) FROM dual ;
• SELECT ROUND(129.23233,2) FROM dual ;
• SELECT ROUND(129.23233,-2) FROM dual ;

 

• SELECT TRUNC(132.3124132) FROM dual ;
• SELECT TRUNC(132.3124132,2) FROM dual ;
• SELECT TRUNC(132.3124132,-2) FROM dual ;

 

• SELECT MOD(10,3) FROM dual ;

 

日期函数

伪列：SYSDATE,SYSTIMESTAMP

• SELECT SYSDATE FROM dual ;
• SELECT SYSTIMESTAMP FROM dual ;

SYSDATE伪列里面包含了有日期时间内容；（11-1月 -17）

三个日期操作公式：

• 日期 + 数字 =日期 （表示若干天之后的日期）；
• 日期 - 数字 =日期 （表示若干天之前的日期）；
• 日期 - 日期 = 数字 （天数）；

• SELECT SYSDATE-7,SYSDATE+120 FROM dual ;

由于每个月的天数不一样，直接用天数不准；

• SELECT ename ， SYSDATE-hiredate FROM emp ;

为了准确地进行日期操作，Oracle提供有四个日期处理函数：

No	函数
1	日期 ADD_MONTHS(列 | 日期 , 月数)	在指定的日期上增加若干个月之后的日期
2	数字 MONTHS_BETWEEN(列 |日期 , 列 |日期)	返回两个日期之间所经历的月数
3	日期 LAST_DAY(列 | 日期)	取得指定日期所在月的最后一天
4	日期 NEXT_DAY(列 | 日期 , 星期x)	返回下一个指定的一周时间数对应的日期

转换函数

No	函数名称	描述
1	字符串 TO_CHAR(列 | 数字 , 转换格式)	将日期或数字格式化为指定结构的字符串
2	日期 TO_DATE(列 | 字符串 , 转换格式)	按照指定的转换格式编写字符串后将其变为日期型数据
3	数字 TO_NUMBER(列 | 字符串)	将字符串变为数字

1、转字符串函数：TO_CHAR()

• 日期：年（yyyy）、月（mm）、日（dd）；
• 时间：时（hh、hh24）、分（mi）、秒（ss）；
• 数字：任意数字（9）、本地货币符号（L）。

2、转日期函数：TO_DATE()

• SELECT TO_DATE('1889-10-19' , 'yyyy-mm-dd') FROM dual ;

3、转数字函数TO_NUMBER()

• SELECT TO_NUMBER('1') + TO_NUMBER('2) FROM dual ；

Oracle里面已经默认提供了许多的自动转换机制。

通用函数：Oracle特色函数（2个）

No	函数名称	描述
1	数字 NVL(列 | NULL , 默认值)	如果传入的内容是null，则使用默认数值处理，如果不是空则使用原始数值处理。
2	数据类型 DECOME(列 | 字符串 | 数值，比较内容1，显示内容1，比较内容2，显示内容2，… ，[，默认显示内容])	设置的内容会与每一个比较内容进行比较，如果内容相同，则会使用显示内容进行输出，如果都不相同，则使用最后的默认显示内容输出。

1、处理null函数

• SELECT empno,ename,sal,comm,(sal+comm)*12 FROM emp ;

• 如果comm为null，则sal+comm也为null；需将null替换为0

• SELECT empno,ename,sal,comm,(sal+NVL(comm,0))*12 FROM emp ;

2、DECODE()函数

SELECT ename , job , DECODE(job , 'CLERK' , '办事员' , 'SALESMAN' , '销售员' ，'---')