Create Table Test6( id varchar2(30), name varchar2(30), age number(2), sex varchar2(2), ename varchar2(30) )
一、函数的分类
SQL函数一般分为两种
单行函数 基于单行的处理，一行产生一个结果
多行函数 基于多行的处理，对多行进行汇总，多行产生结果
以上是Oracle的一些常用的系统函数
二、常用的单行函数
1.1、字符函数
1、lower(字段名) 转小写
select id,lower(ENAME) from Test6
2、upper(字段名) 转大写
select id,upper(ENAME) from Test6
3、initcap(字段名) 首字母转大写
select id,initcap(ENAME) from Test6
4、concat(x,y) 功能与|| 符类似
select id, concat(ENAME,'aaa') as ENAME from Test6
5、substr(字段名,strat,end) 取字段名的子串
select id, substr(ENAME,1,2) as ENAME from Test6
6、length(字段名) 取字段长度
select id, length(ENAME) as ENAME from Test6
7、lpad (字符定长,不够长度时用指定的字符从左边填充)
select id, lpad(ENAME,10,'&') as ENAME from Test6
保证ENAME字段为10个字符长度,如果不够长,则从左边开始填充'&'符,如果字段超出长度,则截取。
8、rpad(字段订长,不够长度是用指定的字符从右边开始填充)和lpad相反
select id, rpad(ENAME,10,'&') as ENAME from Test6
9、trim(leading 字符 FROM 字段) 从字段头部删除指定字符
select id, trim(leading 'z' from ENAME) as ENAME from Test6
执行前：上
执行后:
字母z消失
10、trim(trailing 字符 FROM 字段) 从字段尾部删除指定字符
select id, trim(trailing 'u' from ENAME) as ENAME from Test6
尾部的u字符消失了
11、ltrim(字段名,字符) (等价于用 trim(leading 字符 FROM 字段) 从字段头部删除指定字符) 从字段的左侧开始删除字符
执行下面的sql前:
select id,ltrim(ENAME,'z') as ENAME from Test6
执行后,头部的字符'z'消失了
12、rtrim(字段名,字符) (等价于用 trim(trailing 字符 FROM 字段) 从字段尾部删除指定字符) 从字段的右侧开始删除字符,与ltrim相反
13、instr instr(字段名,要查找的子串,开始查找的位置,查找的子串第几次出现) 从字段中查找我们要查找的子串(按照一定的规则)
select id, instr(ENAME,'w',1,2) as POSITION from Test6
查找ENAME字段中'w'字符的位置,并且从ENAME字段的第一个位置开始查找,并取第二个出现w的位置返回
14、replace(字段名,待查找的子串,用于替换查找到的子串) 从字段名中检索需要替换的子串,并使用用于替换查找到的子串进行替换
select id, replace(ENAME,'s','66') as ENAME from Test6
将s替换成了66
注意:"&"很奇怪的一个问题！！！！！！
2、数值函数
1、round(字段名,要保留的小数位数) 四舍五入,并保留指定位数的小数
select id,name, nvl(age,30)/7 as result from Test6
对上面的结果进行四舍五入操作,结果保留0位小数
select id,name, round(nvl(age,30)/7,0) as result from Test6
2、trunc
(1)、截断数字
trunc(n1,n2) n1表示要截断的数字,n2表示要截断到哪一位,n2的位置根据小数点的位置来确定 注意:trunc的截取不进行四舍五入运算
i、不传n2,则n2默认为0
select trunc(66.6) from test6
ii、传入n2=1,表示截取到小数点的第一位.
select trunc(66.6,1) from test6
iii、传入n2为负数
select trunc(66.6,-1) from test6
表示从小数点左侧开始截取,把个位数全部去掉,保留十位数。
(2)trunc 用于截断日期
i、截取今天,去掉时分秒
select sysdate,trunc(sysdate,'dd') from dual
ii、截取本周第一天
select sysdate,trunc(sysdate,'d') from dual
iii、截取本月第一天
select sysdate,trunc(sysdate,'mm') from dual
vi、截取本年第一天
select sysdate,trunc(sysdate,'y') from dual
vii、截取到小时
select sysdate,trunc(sysdate,'hh') from dual
viii、截取到分钟
select sysdate,trunc(sysdate,'mi') from dual
vvi、获取上月第一天
select TRUNC(add_months(SYSDATE,1),'MM') from dual
注:通过trunc函数和下面将会讲解的日期函数我们可以获取任意我们想要的时间(事件可以是任意格式,只要你这些函数用的六),所以非常方便
(3)mod(被除数,除数) 取余
select mod(12,5) from dual
(4)ceil(x) 向上取整
select ceil(12/5) from dual
(5)floor(x) 向下取整
select floor(12/5) from dual
三、日期函数
下面的sysdate都是
1、sysdate 返回系统当前日期
实际上Oracle内部存储日期的格式是：世纪，年，月，日，小，分钟，秒。
不管如何输入都这样
9i开始，默认的日期格式是：DD-MON-RR,之前是DD-MON-YY
RR 和YY 都是世纪后的两位，但有区别
ORACLE的有效日期范围是：公元前年月日－年月日
select sysdate from dual
RR日期格式：
1、如果当前年份最后两位是：－，并且指定年份的最后两位也为－，
则返回本世纪
例：当前年：， 01－－，表示2008 年
2、如果当前年份最后两位是：－，指定年份最后两位为50－
则返回上世纪。
例：当前年：，01－－，表示1998
3、如果当前年最后两位为：－，指定年份最后两位为0－，
则返回下世纪。
例：当前年：，－－表示的是年
4、如果当前年最后两位是：－，指定年份最后两位为：－
则返回本世纪。
例：当前年：，－－表示的是年
2、months_between(x,y) 两个日期之间相差的月数
计算Test6表中的用户到目前位置,成为会员已经几个月了(注册即成为会员)
select months_between(sysdate,addtime)as diff_month from test6
3、add_months(日期,要加的月份) 返回在指定的日期后，加上n个月后的日期
select add_months(sysdate,3) from dual
4、last_day(日期) 返回指定日期所在月的最后一天
select last_day(sysdate) from dual
5、next_day(日期,下一天) 返回指定日期下一天，这个下一天可不是紧接着下一天的意思,而是下一个你指定的日子(比如星期一、星期二等等)。
星期日:0
星期一:1,
星期二:2,
.....
注意:这个函数有个需要注意的地方
NEXT_DAY()是和当前环境的LANGUAGE变量有关的。
So： 下一天最好用数字表示,否则你就会碰到莫名奇怪的问题。
6、 extract 日期提取函数
i、从日期中提取年份
SELECT EXTRACT(YEAR FROM sysdate) FROM dual;
ii、从日期中提取月份
SELECT EXTRACT(Month FROM sysdate) FROM dual;
iii、从日期中提取日
SELECT EXTRACT(DAY FROM sysdate) FROM dual;
vi、从日期中提取时
select extract(Hour FROM addtime) from test6
vii、从日期中提供分
select extract(MINUTE FROM addtime) from test6
viii、从日期中提取秒
select extract(second FROM addtime) from test6
四、转换函数
Year:
yy two digits 两位年 显示值:07
yyy three digits 三位年 显示值:007
yyyy four digits 四位年 显示值:2007
Month:
mm number 两位月 显示值:11
mon abbreviated 字符集表示 显示值:11月,若是英文版,显示nov
month spelled out 字符集表示 显示值:11月,若是英文版,显示november
Day:
dd number 当月第几天 显示值:02
ddd number 当年第几天 显示值:02
dy abbreviated 当周第几天简写 显示值:星期五,若是英文版,显示fri
day spelled out 当周第几天全写 显示值:星期五,若是英文版,显示friday
Hour:
hh two digits 12小时进制 显示值:01
hh24 two digits 24小时进制 显示值:13
Minute:
mi two digits 60进制 显示值:45
其它
Q digit 季度 显示值:4
WW digit 当年第几周 显示值:44
W digit 当月第几周 显示值:1
Second:
ss two digits 60进制 显示值:25
1、TO_CHAR 将日期转换成字符串
i、将日期转换成字符串
select TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual
ii、获取当前时间的年
select TO_CHAR(sysdate,'yyyy') from dual
iii、获取当前时间的月
select TO_CHAR(sysdate,'mm') from dual
vi、获取当前时间的日(当月的第几日)
select TO_CHAR(sysdate,'dd') from dual
vii、获取当前时间的日(当年的第几日)
select TO_CHAR(sysdate,'ddd') from dual
viii、获取当前时间的日(当周的第几日)
select TO_CHAR(sysdate,'dy') from dual
vvi、获取当前时间的时(24小时制)
select TO_CHAR(sysdate,'hh24') from dual
vvii、获取当前时间的分
select TO_CHAR(sysdate,'mi') from dual
vviii、获取当前时间的秒
select TO_CHAR(sysdate,'ss') from dual
2、TO_DATA 将字符串转换成为日期
select to_date('2017-03-14 13:23:44','yyyy-mm-dd hh24:mi:ss') from dual
3、TO_CHAR