函数(字符,日期,数值,及其他函数)"/>
oracle数据库,函数(字符,日期,数值,及其他函数)
- 字符型函数
针对字符、且返回值仍是字符的函数
- 连接 CONCAT(STR1,STR2)
CONCAT(STR1,STR2):括号内包括两个参数,字符串STR1和字符串STR2,函数效果为将括号内的两个字符串合并到一起,相比于||的连接,CONCAT函数若是想要同时连接多个数据,需要反复嵌套该函数
SELECT CONCAT(CONCAT('HE','LL'),'O') FROM DUAL; HELLO
- 大小写转换函数 LOWER(STR) UPPER(STR) INITCAP(STR)
- 1)LOWER() 将字符串转化为小写
SELECT LOWER('ASDFGF') FROM DUAL;
2)UPPER() 将字符串转化为大写
SELECT UPPER('jjkhkjj') FROM DUAL;
3)INITCAP() 将首字母转化为大写
SELECT ENAME,INITCAP(ENAME) FROM EMP;
SELECT INITCAP('AFSF HSG AHSG') FROM DUAL; //如果对多个字符做首字母大写操作,用空格隔开
3.替换函数 (整体) REPLACE(STR,S1,S2)
REPLACE(STR,S1,S2):该函数包括三个参数,STR-待处理的字符串,S1-将被替换掉的部分,S2-替换后的部分。换句话说:将STR中的S1替换成S2。这其中S1和S2都是视作一个整体,不能单独看其中的某个字符。
SELECT REPLACE('ASDFGH','A','K') FROM DUAL;
SELECT REPLACE('SDFGHTG','SDK','很好') FROM DUAL; //返回原字符串
4.去除函数 (个体) TRIM(LEADING/TRAILING/BOTH 'S' FROM STR) //LTRIM(STR,S1)//RTRIM(STR,S1)
- TRIM():去除指定字符串或字段两侧的空格LTRIM()
SELECT TRIM(' KKK ') FROM DUAL;
SELECT TRIM('A' FROM 'AGGGA') FROM DUAL;
--trim()
SELECT TRIM(' AAA ') FROM DUAL; --两侧有空格
SELECT TRIM(' AAA BBB ') FROM DUAL; --两侧及中间都有空格
SELECT TRIM('@ AAABBB @') FROM DUAL; --虽然两侧有空格但不是在最边上
- LTRIM():去除指定字符串或字段左侧的空格
SELECT LTRIM('ASD',',A') FROM DUAL; --去除字符串ASD中左侧的A,如果A参数省略,则默认去除空格。
SELECT LTRIM('ABCDE','ABC') FROM DUAL;
- RTRIM():去除指定字符串或字段右侧的空格
SELECT RTRIM('ABCDE','ED') FROM DUAL; //’ED’是两个个体,
SELECT RTRIM('ASD',',A') FROM DUAL; --去除字符串STR中右侧的S,如果S参数省略,则默认去除空格。
/*TRIM([LEADING/TRAILING/BOTH] [S FROM] STR) 去除字符串STR中的S,具体去除左侧/右侧/两侧,
根据参数[LEADING/TRAILING/BOTH] 决定,若省略该参数,默认去除两侧;
参数[S FROM] 决定了被去除的内容,若省略则默认去除空格。
*/
//--SELECT TRIM(LEADING/TRAILING/BOTH 'S' FROM 'SADSDS') FROM DUAL;
LEADING:去除首部 TRAILING:去除尾部 BOTH:去除两端
注意!虽然TRIM()/LTRIM()/RTRIM()都是用来去除字符串中的指定部分,但在完整用法上有很大区别,一定要注意区分!!!
1)LTRIM()与RTRIM()中的S可以是单个字符,也可以是多个字符,但TRIM()中的S只能是一个字符;
2)TRIM()函数没有TRIM(STR,S)这种用法,反过来LTRIM()与RTRIM()也不会写成LTRIM(S FROM STR)这种形式;
3)三个函数在去除时,都是从某侧开始去除直到第一个非S出现,特别需要注意LTRIM()与RTRIM()中的S若有多个字符,在去除时是挨个检索S中的每一个字符,而非将S视作一个整体(区分REPLACE函数)。
5.截取 SUBSTR(STR,IND,LEN)
SUBSTR(STR,IND,[LEN]):从字符串STR的IND位置,截取LEN长度的字符串并返回该截取内容
SELECT SUBSTR('ASDFG',0,3) FROM DUAL;
SELECT SUBSTR('ASDFG',1,3) FROM DUAL; //0和1都表示从第一位开始
SUBSTR函数可以说是除聚合函数外使用最频繁的函数,要牢记。
(1)不写截取长度时就截到末尾
SELECT SUBSTR('ASBGFDY',2) FROM DUAL; --从第二位开始截,截到末尾
(2)IND<0时 定位时从右到左定位,截取时还是从左到右截取
SELECT SUBSTR('ABCDEFG',-3,3) FROM DUAL; EFG --如果IND参数为负,表示从右向左定位,截取时是从左向右截取
SELECT ENAME,SUBSTR(ENAME,-4,1) FROM EMP;--不论IND参数是正还是负,截取时都是从左向右截取
(3)LEN<=0时,截取结果返回空值
SELECT ENAME,SUBSTR(ENAME,-2,-2) FROM EMP; --若LEN参数为0或负数,截取结果返回空值
SELECT ENAME,SUBSTR(ENAME,2,100) FROM EMP;--若LEN参数已超出STR实际长度,则截取到末尾;
--当IND为负数时,表示从右边算起第几位,SUBSTR('adgdshd',-2,4)表示从h开始,从左往右截取长度为4的字符串,
--如果长度不够,就把剩余的全部截取。
SELECT SUBSTRB('那还好多久啊',0,8) FROM DUAL; --SUBSTRB() 表示的是字节,在utf-8编码里一个汉字占三个字节,一个英文占一个字节
SELECT SUBSTRB('dsyfkhgkuhkgh',0,8) FROM DUAL;
SELECT SUBSTRB('5752523752',0,8) FROM DUAL; --一个数字占一个字节
总结:
1)截取函数中的参数IND和LEN分别表示起始位置和截取长度,而非起始位置和终止位置(因工作中常常从首位开始截取因而产生误解);
2)从0位开始截取,相当于从1位开始截取(需注意在某些数据库或程序语言中,0位与1位表示不同的位置);
3)如果IND参数为正,表示从左向右定位;如果IND参数为负,表示从右向左定位;
4)如果IND参数长度大于str实际长度,会返回空
5)无论IND参数是正还是负,截取时都是从左向右截取;
6)若LEN参数为0或负数,截取结果返回空值;
7)若LEN参数已超出STR实际长度,则截取到末尾;
8)LEN参数可以省略,表示截取到末尾,而IND参数必不可少。
9)当IND和LEN为小数时,按整数部分截取
6.填充 (针对字节,这里的长度是显示长度,汉字字符字节数固定为两个)(填充的字符为)
LPAD(STR,N,S):在字符串STR左侧,填充S字符,使长度达到N
SELECT LPAD('ABCDEF',14,'S') FROM DUAL;//
RPAD(STR,N,S):在字符串STR右侧,填充S字符,使长度达到N
--返回数值的函数
SELECT RPAD('ACSDF',10,'A') FROM DUAL;
- 如果填充长度小于字符长度,会根据填充长度从左至右截取字符
SELECT LPAD('AGHGDHSG',4,'1') FROM DUAL; AGHG
- 填充长度为0或负数时,返回空值(跟substr中len<=0时一样)
SELECT RPAD('KHDSHSHKD',-5,'1') FROM DUAL;
- 所填充的字符是一个个个体,而非一个整体
SELECT LPAD('ASSD',9,'**') FROM DUAL;
- 如果填充的长度大于指定的长度,那会根据指定长度将字符按顺序填充
SELECT RPAD('HJJJ',5,'%$^') FROM DUAL;
总结:
1)填充函数属于使用频率比较小但是十分有效的函数,该函数不求能完全记住用法,但一定要记住有这么一个函数提供填充功能;
2)S可以是某一个字符,也可以是一段字符串,若S是字符串,填充时会循环使用S中的每一个字符;
3)填充的步骤是先计算为达到长度N还欠缺多少位字符,然后使用S字符串拼接成相应长度的空缺,直接附在目标字符串左侧或右侧;
4)注意尽量避免N比STR总长还要小的情况
5)不指定填充物时会以空填充,填充物为空,返回结果为空
--返回数值的函数
1.获取字符长度 (针对字符) LENGTH(STR)
LENGTH(STR):返回字符串STR的字符长度
如何直观地判断字节长度?
LENGTHB(STR):返回字符串STR的字节长度
--所有函数中,若见到以字母B结尾的函数,多为针对字节的函数
2.获取字符位置(整体) INSTR(STR,S,IND,N)
INSTR(STR,S,IND,N):从第IND位开始,查找S在字符串STR中第N次出现的位置。
SELECT INSTR('ADSFGHAS','S',2,2) FROM DUAL; --8
一般用法:
INSTR(STR,S) :查找S在字符串STR中第一次出现的位置
SELECT INSTR('HGGHH','G') FROM DUAL; --2
深入研究:
1)IND=0 返回0(这里可以发现与SUBSTR(STR,IND,LEN)的区别,在SUBSTR函数中,IND=0与IND=1相同,而INSTR(STR,’S’,IND,N)中,IND=0返回0)
SELECT INSTR('ABCDE','B',0) FROM DUAL; --0
2) IND<0 (定位时从右往左,查找时也是从右往左,返回时从左往右)
SELECT INSTR('ABCDE','B',-2) FROM DUAL; --2
SELECT INSTR('ABCDEABCDE','B',-4,2) FROM DUAL; --2
SELECT INSTR('ABCDEABCDEABCDE','B',-4,3) FROM DUAL; --2
SELECT INSTR('CABDEABCDE','B',-5) FROM DUAL; --3
3) IND 超过字符长度返回0
SELECT INSTR('ABCDE','B',6) FROM DUAL; 0
4) N<=0 (ERROR)
SELECT INSTR('ABCDE','B',1,0) FROM DUAL;
SELECT INSTR('ABCDE','B',1,-2) FROM DUAL;
5) N 超过S在字符串中出现的次数 返回0
SELECT INSTR('AABBCCDD','B',2,3) FROM DUAL;
再次总结:
1)IND=0,返回0
2)IND<0,代表从右侧开始数起,第N次出现S的位置,返回位置时仍从左侧开始算起。
3)IND超出字符总长时,返回0
4)N<=0,会报错
5)N超过S在字符串中出现的次数,返回0
6)IND和N是小数,按整数部分运行
(截取函数)SUBSTR(STR,IND,LEN) 与 (获取位置)INSTR(STR,’S’,IND,N)两个函数的区别:
答:1.IND<0时,SUBSTR(STR,IND,LEN)定位时从右至左定位,截取时从左到右截取;而INSTR(STR,’S’,IND,N)定位时也是从右至左定位,查找时还是从右至左查找,返回时则是从左至右。
2.IND=0时,SUBSTR(STR,IND,LEN)定位从1开始,而INSTR(STR,’S’,IND,N)则返回0。
3.IND超过字符串长度时,SUBSTR()返回空,而INSTR()返回0。
4.SUBSTR(STR,IND,LEN)返回字符串 INSTR(STR,S,IND,N) 返回数值;
5.SUBSTR(STR,IND,LEN)中的LEN超过字符串长度时,定位后面的全截;
INSTR(STR,’S’,IND,N)中N超过字符在字符串出现的次数时,返回0。
6.在SUBSTR(STR,IND,LEN) 中LEN<=0时,返回空值。
而在INSTR(STR,’S’,IND,N) 中N<=0,ERROR
- 在SUBSTR(STR,IND,LEN)和INSTR(STR,’S’,IND,N)中,遇到小数一律按整数部分算。
- SUBSTR不满足要求时一般返回空,而INSTR不满足要求一般返回0或报错。
- 数值型函数
- 取绝对值 ABS(NUM)
ABS(NUM):获取数值NUM对应的绝对值。
SELECT ABS(-52.2) FROM DUAL; 52.2
该函数通常不直接对某数值进行取绝对值,而是对于一系列的计算结果获取绝对值。
SELECT ABS(-1.2*2.8) FROM DUAL;
2.向上取整与向下取整 CEIL(NUM) FLOOR(NUM)
2.1 CEIL(NUM) :向上取整,获取离NUM最近的两个整数中较大的那个数
SELECT CEIL(2.5) FROM DUAL; -- 3
SELECT CEIL(-3.9) FROM DUAL; -- -3
2.2 FLOOR(NUM):向下取整,获取离NUM最近的两个整数中较小的那个数
SELECT FLOOR(2.5) FROM DUAL; -- 2
SELECT FLOOR(-3.2) FROM DUAL; -- -4
注意:
- 若NUM本身即为整数,则无论向上取整还是向下取整,返回结果都为NUM本身
SELECT FLOOR(-5) FROM DUAL; -5
SELECT FLOOR(5) FROM DUAL; 5
2)无论NUM是正数还是负数,所谓上,即数轴指向的方向,所谓下,即数轴背向的方向
3.取余 MOD(NUM1,NUM2)
MOD(X,Y):X是被除数,Y是除数,计算Y整除X,获取计算结果中的余数。(不是商!不是商!不是商!)
SELECT MOD(5,2) FROM DUAL; 1
//MOD函数对于纯数学范畴的使用很少,但智慧的程序员们却因此提出了“分库分表”的概念:将数据按照一定规则均分到不同的数据库(表)中,变串行为并行,以此来提高代码和程序的运行效率。
4.四舍五入 ROUND(NUM,P)
ROUND(NUM[,P]):对数值NUM进行四舍五入,其中参数P决定了四舍五入的精度
- 基础用法
SELECT ROUND(2.254,2) FROM DUAL; 2.25// 2是精确到小数点第二位
- 精度为0或者精度省略 精确到个位
SELECT ROUND(215.2154,0) FROM DUAL; 215
SELECT ROUND(2.56521) FROM DUAL; 3//精度为0或精度不要取整。
- 负数的四舍五入
SELECT ROUND(-2.56,1) FROM DUAL; -2.6
SELECT ROUND(-1.2365,1) FROM DUAL; -1.2
SELECT ROUND(-5.564,0) FROM DUAL; -6
SELECT ROUND(-5.564) FROM DUAL; -6 //负数做四舍五入时,把负号去掉,四舍五入完后再把负数加上去
- 精度为负数
SELECT ROUND(2.253,-1) FROM DUAL; 0-- -1,-2,-3分别为保留到十位百位千位,返回值分别是10,100,1000
SELECT ROUND(55.253,-1) FROM DUAL; 60
SELECT ROUND(152.253,-2) FROM DUAL; 200
SELECT ROUND(1552.253,-3) FROM DUAL; 2000
注:p为-1时,保留到十位,对个位做四舍五入;P为-2时,保留到百位,对十位做四舍五入;
p为-3时,保留到千位,对百位做四舍五入。。。
- 精度不为整数
SELECT ROUND(2.325,1.21) FROM DUAL; 2.3//精度不为整数时,忽略小数部分。
5.截断 TRUNC(NUM,P)
TRUNC(NUM[,P]) :对数值NUM进行截断,其中参数P决定了截断的精度
- 基础用法
SELECT TRUNC(2245,-1) FROM DUAL; 2240//直接按照精度截断,不做四舍五入
- 精度为0或精度省略
SELECT TRUNC(33.25,0) FROM DUAL; 33
SELECT TRUNC(23.255) FROM DUAL; 23//直接截断
- 负数的截断
SELECT TRUNC(-11.254,1) FROM DUAL; -11.2
- 精度为负数
SELECT TRUNC(12.36,-1) FROM DUAL; 10
SELECT TRUNC(-12.2,-1) FROM DUAL; -10//直接截断
- 精度不为整数
SELECT TRUNC(12.255,-1.7) FROM DUAL; -10//将精度中的小数部分去掉,按整数精度截断
- 日期型函数
关于日期的计算:
日期直接加减一个整数,该数值以天为单位进行日期计算;
SELECT TO_DATE('1987/02/05','YYYY/MM/DD')+5 FROM DUAL;//1987/02/10
两日期相减,返回两日期相差的天数。
SELECT TO_DATE('1998/02/15','YYYY/MM/DD')-TO_DATE('1997/01/02','YYYY/MM/DD') FROM DUAL; 409
1.关于日期的四舍五入与截断
ROUND()函数与TRUNC()函数除了可以作用在数值上,也可以作用在日期上,对于日期也同样有四舍五入或截断的需求
对于四舍五入,在数值方面依靠数值5作为分界线,达不到5即被舍去,达到5即向前进1。在日期方面,四舍五入同样需要一个分界线,该分界线在不同的时间单位中并不相同,原则上是取居中的位置作为分界线。
对于截断,日期的截断与数值的截断类似,定位到截取精度,直接舍弃精度之外的部分,留下精度之内的部分。
- 省略精度,即默认的精确到天,天的界限
ROUND(): (12小时)
SELECT SYSDATE,ROUND(SYSDATE) FROM DUAL;
SELECT ROUND(TO_DATE('2022/01/02 12:00:00','YYYY/MM/DD HH24:MI:SS'))FROM DUAL; 2022/01/03 --天的界限是小时,大于或等于12小时进一天
TRUNC():
SELECT TRUNC(SYSDATE) FROM DUAL;
SELECT TRUNC(TO_DATE('2022/01/02 15:36:25','YYYY/MM/DD HH24:MI:SS'))FROM DUAL; 2022/01/02
- 年的界限 //7月份
ROUND():
SELECT ROUND(SYSDATE,'YYYY') FROM DUAL; //月份大于等于7时,年份进1,月日变成01/01
SELECT ROUND(TO_DATE('1998/07/02','YYYY/MM/DD'),'YYYY') FROM DUAL;
//1999/01/01
TRUNC():
SELECT TRUNC(SYSDATE,'YYYY') FROM DUAL;
SELECT TRUNC(TO_DATE('1998/05/09','YYYY/MM/DD'),'YYYY') FROM DUAL;
1998/01/01 截断是直接返回所在年份的一月一日。
- 月的界限 //16号
ROUND():
SELECT ROUND(SYSDATE,'MM') FROM DUAL; //对当天所处月份进行四舍五入
SELECT ROUND(TO_DATE('2022/05/16','YYYY/MM/DD'),'MM') FROM DUAL;
2022/06/01 当天数大于等于16时,月份进1
TRUNC():
SELECT TRUNC(SYSDATE,'MM') FROM DUAL;
SELECT TRUNC(TO_DATE('2022/05/07','YYYY/MM/DD'),'MM') FROM DUAL; 2022/05/01 返回所在月份的一号
- 季的界限 //每季度中间月份的16号
ROUND():
SELECT ROUND(SYSDATE,'Q') FROM DUAL;
SELECT ROUND(TO_DATE('2022/05/16','YYYY/MM/DD'),'Q') FROM DUAL;
2022/07/01
一季度 01/01(02/16) 二季度 04/01 (05/16) 三季度 07/01 (08/16) 四季度 10/01 (11/16)
TRUNC():
SELECT TRUNC(SYSDATE,'Q') FROM DUAL;
SELECT TRUNC(TO_DATE('2022/9/16','YYYY/MM/DD'),'Q') FROM DUAL;
2022/07/01 按所在月份所在季度的第一个月份的第一天来截
- 小时的界限 (30分钟)
ROUND():
SELECT ROUND(SYSDATE,'HH') FROM DUAL;
SELECT ROUND(TO_DATE('2022/02/12 15:30:00','YYYY/MM/DD HH24:MI:SS'),'HH') FROM DUAL; 2022/02/12/ 16:00:00
TRUNC():
SELECT TRUNC(SYSDATE,'HH') FROM DUAL;
SELECT TRUNC(TO_DATE('2022/02/02 15:30:14','YYYY/MM/DD HH24:MI:SS'),'HH') FROM DUAL; 2022/02/02 15:00:00 按小时截
- 星期的界限
星期:
WW:一年中的第几个星期,从第一天开始算起,一个七天算作一周,和星期几无关
IW:一年中的第几个星期,星期一为本周第一天,每年末最后一个周不足四天算至下年第一周,
足四天将下年本周的剩余时间一同算作本年最后一周
W:一个月中的第几个星期,从第一天开始算起,一个七天算作一周,和星期几无关
DAY:星期几全称
1.星期的界限WW(一个七天算作一周):--每7天内的第四天的12点
SELECT ROUND(TO_DATE('20220104 11:59:59' ,'YYYYMMDD HH24:MI:SS'),'WW') FROM DUAL;--2022/01/01
SELECT ROUND(TO_DATE('20220104 12:00:00' ,'YYYYMMDD HH24:MI:SS'),'WW') FROM DUAL;--2022/01/08
SELECT ROUND(TO_DATE('20220111 11:59:59' ,'YYYYMMDD HH24:MI:SS'),'WW') FROM DUAL;--2022/01/08
SELECT ROUND(TO_DATE('20220111 12:00:00' ,'YYYYMMDD HH24:MI:SS'),'WW') FROM DUAL;--2022/01/15
2.星期的界限IW(星期一为本周第一天):--周四的12点
SELECT ROUND(TO_DATE('20220728 11:59:59' ,'YYYYMMDD HH24:MI:SS'),'IW') FROM DUAL;
SELECT ROUND(TO_DATE('20220728 12:00:00' ,'YYYYMMDD HH24:MI:SS'),'IW') FROM DUAL;
SELECT TRUNC(TO_DATE('20220728 11:59:59' ,'YYYYMMDD HH24:MI:SS'),'IW') FROM DUAL;
SELECT TRUNC(TO_DATE('20220728 12:00:00' ,'YYYYMMDD HH24:MI:SS'),'IW') FROM DUAL;
3.月周界限W(7天一周):--每7天内的第四天的12点
SELECT ROUND(TO_DATE('20220704 11:59:59' ,'YYYYMMDD HH24:MI:SS'),'W') FROM DUAL;
SELECT ROUND(TO_DATE('20220704 12:00:00' ,'YYYYMMDD HH24:MI:SS'),'W') FROM DUAL;
SELECT ROUND(TO_DATE('20220711 11:59:59' ,'YYYYMMDD HH24:MI:SS'),'W') FROM DUAL;
SELECT ROUND(TO_DATE('20220711 12:00:00' ,'YYYYMMDD HH24:MI:SS'),'W') FROM DUAL;
4.星期几-DAY界限:--周三12:00
SELECT ROUND(TO_DATE('20220726 11:59:59' ,'YYYYMMDD HH24:MI:SS'),'DAY') FROM DUAL;--返回本周默认的第一天-星期天
SELECT ROUND(TO_DATE('20220726 12:00:00' ,'YYYYMMDD HH24:MI:SS'),'DAY') FROM DUAL;--返回本周默认的第一天-星期天
SELECT ROUND(TO_DATE('20220727 11:59:59' ,'YYYYMMDD HH24:MI:SS'),'DAY') FROM DUAL;--返回本周默认的第一天-星期天
SELECT ROUND(TO_DATE('20220727 12:00:00' ,'YYYYMMDD HH24:MI:SS'),'DAY') FROM DUAL;--下个星期天
日期函数
1.获取月份差值 (d1-d2)
MONTHS_BETWEEN(d1,d2) :求D1和D2之间相差几个月(d1>d2)
常规用法:给定两个日期求两日期之间的月份差
1.计算从元旦到今天经过了几个月(整数月)
SELECT CEIL(MONTHS_BETWEEN(SYSDATE,TO_DATE('2022/01/01','YYYY/MM/DD')))
FROM DUAL; --CEIL:向上取整
2.计算从元旦到今天经过了几个月(非整数月)
SELECT MONTHS_BETWEEN(SYSDATE,TO_DATE('2022/01/01','YYYY/MM/DD')) FROM DUAL;
3.计算从今天到明年元旦还要等待几个月(整数月)
SELECT CEIL(MONTHS_BETWEEN(TO_DATE('2023/01/01','YYYY/MM/DD'),SYSDATE)) FROM DUAL;
4.两个日期都是月末日期的时候,只计算整数部分
SELECT ABS(MONTHS_BETWEEN(TO_DATE('2022/01/31','YYYY/MM/DD'),TO_DATE('2022/06/30','YYYY/MM/DD'))) FROM DUAL;
特殊情况
1)一般工作中使用到的情况是,给定两个月初或月末日期、或者两个DD相同的日期。如果给定的两个日期DD不相同,月份差会得到非整数,小数部分为剩余天数/31,这个规则无论针对哪一个月都是如此;
2)如果给定的两个日期DD不同,但是想要获得整数月,可以配合TRUNC函数或后续提到的获取月末日期函数使用;
3)如果两个日期的DD不同,但是都是月末日期,则获取的月份差将是整数;
4)需要特别注意,函数中录入的两个日期是有大小之分的(区别BETWEEN AND),一般晚的日期在前,早的日期在后,如此得到的结果便是正数,反之获得负数。如果不确定两个日期的大小,可搭配ABS()函数使用。
2.获取月末日期
LAST_DAY(D):获取指定日期对应的当月最后一天,即月末日期
举例:
- 获取当月最后一天。
SELECT LAST_DAY(SYSDATE) FROM DUAL; //会显示时分秒
SELECT TRUNC(LAST_DAY(SYSDATE)) FROM DUAL; //加上TRUNC函数,会把时分秒隐去
- 查询某月份的最大天数
SELECT LAST_DAY(TO_DATE('07','MM')) FROM DUAL; //显示年月日
SELECT TO_CHAR(LAST_DAY(TO_DATE('02','MM')),'DD') FROM DUAL;
SELECT SUBSTR(TO_CHAR(LAST_DAY(TO_DATE('07','MM'))),1,2) FROM DUAL;31
//与电脑显示有关,先看一下天对应的位置,再按位置截断
SELECT TO_CHAR(LAST_DAY(SYSDATE),'DD') FROM DUAL; --31(精确到天)
- 查询某日期对应的年份是平年还是闰年
SELECT CASE
WHEN TO_NUMBER(TO_CHAR(LAST_DAY(TRUNC(SYSDATE, 'YYYY') + 31), 'DD')) = 28 THEN
'平年'
ELSE
'闰年'
END 年份
FROM DUAL;
- 获取给定两日期的月份差(整数月) --将两个日期都做LAST_DAY处理
SELECT ABS(MONTHS_BETWEEN(LAST_DAY(TO_DATE('2022/02/04','YYYY/MM/DD')),LAST_DAY(TO_DATE('2022/07/15','YYYY/MM/DD')))) FROM DUAL;
3.月份加减 ADD_MONTHS(D,N)
ADD_MONTHS(D,N) :在D日期的基础上加N个月。N可为正可为负
--基础用法:
SELECT ADD_MONTHS(TO_DATE('2022/01/15','YYYY/MM/DD'),2) FROM DUAL;
SELECT ADD_MONTHS(TO_DATE('2022/01/15','YYYY/MM/DD'),-2) FROM DUAL;
--求平年闰年
SELECT CASE
WHEN TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'),11)),'DDD')) = 365 THEN
'平年'
ELSE
'闰年'
END 年份
FROM DUAL;
--N为负数表示在D日期的基础上减去几个月
特殊情况:
1)如果N是小数
SELECT ADD_MONTHS(TO_DATE('2022/02/14','YYYY/MM/DD'),1.5) FROM DUAL; //小数部分没有意义
2)如果DD是月末(返回所在月份的最后一天)
SELECT TO_DATE('2012/02/29','YYYY/MM/DD'),ADD_MONTHS(TO_DATE('2012/02/29','YYYY/MM/DD'),2) FROM DUAL; --2022/04/30
SELECT TO_DATE('2012/03/31','YYYY/MM/DD'),ADD_MONTHS(TO_DATE('2012/03/31','YYYY/MM/DD'),3) FROM DUAL; --2012/06/30
- 如果DD不在计算后的月份中
(如果DD不在计算后的月份中,则返回计算后的最后一天)
SELECT TO_DATE('2012/01/31','YYYY/MM/DD'),ADD_MONTHS(TO_DATE('2012/01/31','YYYY/MM/DD'),3) FROM DUAL; --2012/04/30
SELECT TO_DATE('2013/01/29','YYYY/MM/DD'),ADD_MONTHS(TO_DATE('2013/01/29','YYYY/MM/DD'),1) FROM DUAL; --2013/02/28
思考:
返回日期对应的当月第一天
1.)SELECT LAST_DAY(ADD_MONTHS(SYSDATE,-1))+1 FROM DUAL;
--将当前月份减1,再做LAST_DAY,可以得到当前所在月份上个月的最后一天,在加上一天,即为这个月的一号
2.)SELECT ADD_MONTHS(LAST_DAY(SYSDATE),-1)+1 FROM DUAL;
--将当前日期经过LAST_DAY得到这个月的最后一天,再经过ADD_MONTHS减去一个月,得到上个月的最后一天,再加一天,即为当月的第一天
3.)SELECT ADD_MONTHS(LAST_DAY(SYSDATE)+1,-1) FROM DUAL;
--将当前月份经过LAST_DAY返回这个月的最后一天,再用ADD_DATE减去1,即为这个月的第一天
4.SELECT TO_DATE(TO_CHAR(SYSDATE,'YYYYMM')||'01','YYYY/MM/DD') FROM DUAL
4.获取下个周几
NEXT_DAY (D,W):给定日期D,和星期W,获取D日期之后的下一个星期W(不是下个周的周几)(下一次出现星期几是几号)
例如:
某业务只能在周五办理,错过只能等下一个周五。请问今天对应的下一个周五是几号。
SELECT NEXT_DAY(SYSDATE,'星期五') FROM DUAL;
--思考题:
给定任意日期,如何获取下个周的星期五
思考:
给定任意日期,如何获取下个周的星期五
SELECT TRUNC(SYSDATE,'IW')+6,NEXT_DAY(TRUNC(SYSDATE,'IW')+6,'星期五') FROM DUAL;
思考:
给定任意日期,如何获取下个周的星期五(或者下个周的星期N)
SELECT NEXT_DAY(TRUNC(TO_DATE('20220727','YYYYMMDD'),'IW')+6,'星期五') FROM DUAL;
例如:在本周日获取下个周的星期日
SELECT NEXT_DAY(TRUNC(TO_DATE('20220731','YYYYMMDD'),'IW')+6,'星期日') FROM DUAL;
--基于周日获取的NEXT_DAY,结果总是在下个周
- 其他函数
- 条件取值
DECODE(EXPR,VALUE1,RESULT1,VALUE2,RESULT2,…,DEF_RESULT)
举例:
使用decode函数,职位是分析员的,工资+1000;职位是经理的,工资+800;职位是其它的,工资+400
SELECT ENAME,SAL,JOB,
DECODE(JOB,'ANALYST',SAL+1000,'MANAGER',SAL+800,SAL+400) FROM EMP;
--IN() LIKE BETWEEN AND ANY() > < =...在DECODE函数中用不了
其实效果即等同于
CASE EXPR
WHEN VALUE1 THEN RESULT1
WHEN VALUE2 THEN RESULT2
…
ELSE DEF_RESULT
END
SELECT ENAME, --只能做相等判断
JOB,
SAL,
CASE JOB
WHEN 'ANALYST' THEN
SAL + 1000
WHEN 'MANAGER' THEN
SAL + 800
ELSE
SAL + 400
END
FROM EMP;
--CASE WHEN THEN WHEN THEN END (都可以用)
SELECT ENAME,JOB,SAL,CASE WHEN JOB='ANALYST' THEN SAL+1000 WHEN JOB='MANAGER' THEN SAL+800 ELSE SAL+400 END FROM EMP;
2.返回集合中第一个不为空的内容
--COALESCE(c1,c2,c3,c4,.....):返回括号中第一个非空表达式,如果都为空,则返回空
CREATE TABLE BOY(姓名 CHAR(50),
小名 CHAR(20),
绰号 CHAR(50)); --建表
插入数据
SELECT * FROM BOY FOR UPDATE; --在插入的时候留几个空不写数据
SELECT 姓名,小名,绰号,COALESCE(姓名,小名,绰号) FROM BOY;
3.空值的赋值
--NVL(EXPR,VALUE):如果EXPR是空,返回一个VALUE,如果EXPR不为空,返回EXPR本身
SELECT NVL(小名,'没有') FROM BOY;
SELECT NVL(COMM,10000) FROM EMP;
--NVL2(EXPR1,EXPR2,EXPR3):expr1不为NULL,返回expr2;expr1为NULL,返回expr3
--公司今年业务赚了很多钱,老板开心,决定给员工发奖金,有奖金的加5000,没奖金的给4000
SELECT COMM,NVL2(COMM,COMM+5000,4000) FROM EMP;
--坑爹老板//公司不景气,老板决定工资加奖金不为空的,只发工资部分,空的,返回工资加奖金(空)
SELECT NVL2(SAL+COMM,SAL,SAL+COMM)FROM EMP;
4.去重
--DISTINCT COL_LIST:
--SELECT DISTINCT COL_LIST FROM TB_NAME …对COL_LIST范围内的字段进行去重
--举例(以下查询结果全部要求去重):
--获取公司的所有岗位
SELECT DISTINCT JOB FROM EMP;
--获取公司的所有部门
SELECT DISTINCT DEPTNO FROM EMP;
--获取各部门的各岗位
SELECT DISTINCT DEPTNO,JOB FROM EMP GROUP BY DEPTNO,JOB;
--平年闰年
SELECT CASE
WHEN MOD(TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE('1900/01/01',
'YYYY/MM/DD')),
'YYYY')),400) = 0 OR
MOD(TO_NUMBER(TO_CHAR(TO_DATE('1900/01/01', 'YYYY/MM/DD'),
'YYYY')),4) = 0 AND
MOD(TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE('1900/01/01',
'YYYY/MM/DD')),
'YYYY')),100) <> 0 THEN
'闰年'
ELSE
'平年'
END 平年闰年
FROM EMP;
更多推荐
oracle数据库,函数(字符,日期,数值,及其他函数)
发布评论