【Oracle】第7天函数

Life lies in diligence.

已于 2022-05-07 10:46:47 修改

阅读量358

点赞数

分类专栏： sql 文章标签： sql 数据库 oracle

于 2021-01-20 15:14:10 首次发布

本文链接：https://blog.csdn.net/csdn_okcheng/article/details/112876931

版权

sql 专栏收录该内容

17 篇文章 4 订阅

订阅专栏

本文详述了Oracle数据库中各类函数的使用，包括字符型函数如LOWER()、UPPER()、CONCAT()，数值型函数如ABS()、ROUND()，日期函数如MONTHS_BETWEEN()、LAST_DAY()，以及窗口函数ROW_NUMBER()、RANK()等。通过实例展示了这些函数在实际操作中的应用，帮助理解并掌握Oracle数据库中的数据处理技巧。

摘要由CSDN通过智能技术生成

字符型函数

1.大小写控制函数

LOWER()--小写
UPPER()--大写
INITCAP()--只有首字母大写

该函数在查询字符串中可以提高查询效率

2.字符控制函数

CONCAT('Hello','World')--HelloWorld 
SUBSTR('HelloWorld',1,5)--Hello
LENGTH('HelloWorld')--10
LENGTHB(求字符串的字节长度)		--字节长度：保存字符串的时候，每一个字符都有一个自己的字节长度
								--字符集与字符字节的关系
								--UTF-8：一个汉字占用三个字节--常用
								--ZHS16GBK：一个汉宇占用两个字节--常用
INSTR('HelloWorld','W',1,1)--6
LPAD()/RPAD(sal,10,'*')--800*******
TRIM('H' from 'HelloHWorldH')--elloHWorld
REPLACE('HelloWorld','H','T')--TelloWorld

CONCAT()

CONCAT()拼接一切，但是只能拼接两坨东西，可嵌套

拼字符串和字符串

SELECT CONCAT('67班','马上放假') FROM DUAL;

拼字符串和字段

SELECT CONCAT('67班',ENAME) FROM EMP;

WM_CONCAT()

将一整个字段的每一行都用逗号拼接在一起

SELECT WM_CONCAT(ENAME) FROM EMP;

→
在这里插入图片描述

REPLACE函数

REPLACE函数会把指定字符串中想要替换的参数全部替换为指定的参数

REPLACE(原字符串，要去除的字符（默认为空格）)

TRIM函数

LTRIM(原字符串，要去除的字符（无论顺序）（默认为空值）)
RTRIM(原字符串，要去除的字符（无论顺序）（默认为空值）)

或者

TRIM([LEADING/TRAILING/BOTH] [S FROM] STR)

LTRIM()左去除
RTRIM()右去除

注意：

SELECT TRIM('ASDASDAS','A') FROM DUAL;--会报错，这样不可行
--应该为：
SELECT TRIM('A' FROM 'ASDASDAS') FROM DUAL;--注意，这个是从两侧开始去除，不能去除时立刻停止

SELECT LTRIM('ASD ASD','SAD') FROM DUAL;--可行，因为第二个参数不论顺序

→
在这里插入图片描述

LPAD()和RPAD()函数

LPAD(要填充的字符串，要填充到的长度为几个字符长度，用来填充的字符串（默认为空格）)

注意：
1.此函数可以用来从头截取！！！只需要让第二个参数小于LENGTH(要填充的字符串)就好了
2.会用第三个参数从左往右依次填充

SUBSTR()函数

SUBSTR()函数是从自定义位置截取的

SUBSTR(要截取的字符串，要截取的第几位（负数时，从右往左截取），截取多长的长度（默认截取到最后）)

INSTR()函数

获取字符位置的函数

INSTR(要处理的字符串,要查找的字符串（无论有几个都只找第一个）,开始查找的位置第几位（默认1）（负数时从右向左开始找，起始位置根据绝对值的大小不同而不同）,查找第几次出现的次数（默认1）（找不到则返回0）)

数值型函数

1.取绝对值

ABS(NUM)

应用：

SELECT ABS(-123) FROM DUAL;

2.向上取整/向下取整

CEIL(NUM)--向上取整

FLOOR(NUM)--向下取整

应用：

SELECT CEIL(1.3) FROM DUAL;

→2

SELECT FLOOR(1.3) FROM DUAL;

→1

3.四舍五入/截取

ROUND(NUM,精度：精确到小数点后几位，默认为0)

TRUNC(NUM,精度：精确到小数点后几位，默认为0)

应用：

SELECT ROUND(123.145,-2) FROM DUAL;--100

SELECT TRUNC(123.145) FROM DUAL;--123

注意：ROUND(DATE,DATE精度)/TRUNC(DATE,DATE精度)
可以对日期做处理

sql的日期格式包含：

YYYY年/MM月/DD日/HH24时/MI分/SS秒/Q/WW/IW/DAY/DY

应用：

对年份应用——以6/7月为分界线

SELECT ROUND (TO_DATE('2020/08/24','YYYY/MM/DD'),'YYYY') FROM DUAL;--2021/1/1

SELECT ROUND (TO_DATE('2020/05/24','YYYY/MM/DD'),'YYYY') FROM DUAL;--2020/1/1

SELECT TRUNC (TO_DATE('2020/08/24','YYYY/MM/DD'),'YYYY') FROM DUAL;--2020/1/1

SELECT TRUNC (TO_DATE('2020/05/24','YYYY/MM/DD'),'YYYY') FROM DUAL;--2020/1/1

对月份应用——以15/16日为分界线

SELECT ROUND (TO_DATE('2020/08/24','YYYY/MM/DD'),'MM') FROM DUAL;--2020/9/1

SELECT ROUND (TO_DATE('2020/08/15','YYYY/MM/DD'),'MM') FROM DUAL;--2020/8/1

对日应用——以中午12点为分界线

SELECT ROUND (TO_DATE('2020/08/15 10:59:59','YYYY/MM/DD HH24:MI:SS'),'DD') FROM DUAL;--2020/8/16

SELECT ROUND (TO_DATE('2020/08/15 14:59:59','YYYY/MM/DD HH24:MI:SS'),'DD') FROM DUAL;--2020/8/17

对星期应用——以周三的中午12点为分界线

SELECT ROUND (TO_DATE('2020/08/19','YYYY/MM/DD'),'DAY') FROM DUAL;--2020/8/16

SELECT ROUND (TO_DATE('2020/08/20','YYYY/MM/DD'),'DAY') FROM DUAL;--2020/8/23

4.取余函数

MOD(VALUE1,VALUE2)

取VALUE1/VALUE2的余数

应用：
EMP表中工资能被3整除的提成加工资的10%，不能被3整除但被4整除的提成加工资的20%，不能被3整除且不能被4整除但被5整除的提成加工资的50%，其他不做修改
→

SELECT SAL,CASE WHEN MOD(SAL,3)=0 THEN NVL(COMM,0)+0.1*SAL
				WHEN MOD(SAL,4)=0 THEN NVL(COMM,0)+1.2*SAL
				WHEN MOD(SAL,5)=0 THEN NVL(COMM,0)+1.5*SAL
				END  COMM_PLUS
				FROM EMP;

注意：MOD()函数经常用于分库分表的操作中

5.平方根函数

SQRT(NUM)

求NUM 的平方根，NUM>=0

6.幂指函数

POWER(底数,指数)

7.ASCII码相关函数

ASCII(N)--将字符N转化为ASCII值

CHR(N)--将ASCII值N转化为字符

8.ROWID伪列

ROWID

自动生成一串18位字符串，一共62¹⁸种组合，故近似可以视为唯一的，已知ROWID，查询时效率比较高

ROWNUM

自动生成一组递增的字符串，只在当前查询语句生效

9.去重函数

DISTINCT

把查询结果中的完全重复的数据去掉

有时DISTINCT 函数会失灵，需要再用到 ROWID套子查询去重

10.判断函数

CASE WHEN THEN ELSE END

DECODE(COLUMN,VALUE1,VALUES1,VALUE2,VALUES2...)--往往用于等值比较

当COLUMN的值等于VALUE 1时，给COLUMN列赋值VALUES1
当COLUMN的值等于VALUE 2时，给COLUMN列赋值VALUES2。。。

11.去空函数

NVL(COLUMN1,VALUE1)

若COLUMN1列中有空值，赋值为VALUE1

NVL2(COLUMN1,VALUE1,VALUE2)

若COLUMN1列中有空值，赋值为VALUE2，否则赋值为VALUE1

12.空值处理函数

COALESCE(c1,c2,c3,c4,......cn)

从左往右依次返回括号中字段的第一个非空表达式，如果都为空，则返回空

日期函数

Oracle中的日期型数据实际含有两个值：
日期和时间
注意：
1.日期可以和数值做加减法，但是不可以乘除
2.两个日期直接相减返回的是日期之间相差的天数

1.获取月份差值函数

MONTHS_BETWEEN(D1,D2)

求D1减去D2中隔着几个月份

应用：

SELECT MONTHS_BETWEEN(TO_DATE('2020/05/09','YYYY/MM/DD'),TO_DATE('2020/08/19','YYYY/MM/DD')) FROM DUAL;

→-3.32258064516129

注意：
1.相同日子加减，结果为整数，特别地，月末日子加减，结果为整数
2.天数不足整月，固定地除以31以小数显示

练习：
求高中时期经历了多少个月？
15/09/01入学 18/06/01毕业
去除寒暑假
寒假：01/23-02/18
暑假：07/13-09/01
→

SELECT (SELECT MONTHS_BETWEEN(TO_DATE('2018/06/01','YYYY/MM/DD'),TO_DATE('2015/09/01','YYYY/MM/DD')) FROM DUAL) 
- 
(SELECT MONTHS_BETWEEN(TO_DATE('2017/02/18','YYYY/MM/DD'),TO_DATE('2017/01/23','YYYY/MM/DD')) FROM DUAL)*3
-
(SELECT MONTHS_BETWEEN(TO_DATE('2017/09/01','YYYY/MM/DD'),TO_DATE('2017/07/13','YYYY/MM/DD')) FROM DUAL)*2
FROM DUAL;

习题:
1.计算从元旦到今天经过了几个月
→

SELECT MONTHS_BETWEEN(SYSDATE,TO_DATE('2020/01/01','YYYY/MM/DD')) FROM DUAL;

2.计算从今天到国庆还有几个月
→

SELECT MONTHS_BETWEEN (TO_DATE('2021/10/01','YYYY/MM/DD'),SYSDATE) FROM DUAL;

3.计算从今天到明年元旦还有几个月
→

SELECT MONTHS_BETWEEN(TO_DATE('2021/01/01','YYYY/MM/DD'),SYSDATE ) FROM DUAL;

2.获取月末日期函数

LAST_DAY(一个日期，没有月则默认为当前月)

应用：
求本月10号对应的最后一天
→

SELECT LAST_DAY(TO_DATE('10','DD')) FROM DUAL;

3.获取下星期几函数

NEXT_DAY(D,第几天或者星期几)

给定日期D，和W周几，返回下一次从D开始数的W

应用：

SELECT NEXT_DAY(SYSDATE,'星期四') FROM DUAL;

→2020/12/24 星期四 13:35:35

SELECT NEXT_DAY(SYSDATE,7) FROM DUAL;

→2020/12/26 星期六 13:35:18

↑这里是因为老外从周日开始算一个新的一周

SELECT NEXT_DAY(SYSDATE,'星期三') FROM DUAL;

→2020/12/30 星期三 13:36:00

4.SYSDATE函数

先来讲讲日期格式：
年 ‘YYYY’ 月 ‘MM’ 日 ‘DD’ 时 HH24/HH12 分 MI 秒 SS
日期格式的应用：0

SELECT TO_DATE('2020/08/19','YYYY/MM/DD') FROM DUAL;

SELECT TO_DATE('2020/08/19 10:16:29','YYYY/MM/DD HH12:MI:SS') FROM DUAL;

SELECT TO_DATE('2020','YYYY') FROM DUAL;

→2020/12/1 星期二

——↑默认本月第一天

应用：

SELECT SYSDATE,SYSDATE+1.3,SYSDATE+1/24/60 FROM DUAL;

→
在这里插入图片描述

5.月份加减函数

ADD_MONTHS(Date,N)

给Date(‘YYYY/MM/DD’)加N个月

注意：
1.N可为负
2.当N为小数时，此函数会先执行TRUNC再继续运算
3.如果DD为月末，则结果必定为月末
4.如果DD不在计算后的月份之中，则向前进一天，直至在该月份之中

转换函数

TO_CHAR()与TO_DATE()

TO_CHAR(其他类型的数据，希望转化的字符串格式)
TO_DATE(表示日期的字符串，与前者对应的日期格式)

TO_CHAR函数的详细笔记参考链接

注意：
1.TO_CHAR()函数常与TRIM()函数连用，因为容易出现空格
2.用 TO_CHAR()可以指定不同的日期格式，转出不同的内容
3.出于上述原因及不同工具日期显示格式不同的原因，很多项目以字符型格式存储日期
应用：与货币符号的连用/与千分符连用

L:当地货币符号 $:美元

SELECT TO_CHAR(123.456,L9999.99) FROM DUAL;--报错，缺失右括号

SELECT TO_CHAR('123.456','L9999.99') FROM DUAL;--         ￥123.46

习题：
将emp中工资大于2000的sal加上美元符号，其他加当地符号
→

SELECT CASE WHEN SAL>2000 THEN TRIM(TO_CHAR(SAL,'$9999.99')) ELSE TRIM(TO_CHAR(SAL,'L9999.99')) END FROM EMP;

应用：

年
Q 一年中的第几个季度
YYYY 年

SELECT TO_CHAR(TO_DATE('2020/08/20','YYYY/MM/DD'),'Q') FROM DUAL;

→3

SELECT TO_CHAR(TO_DATE('2020/08/20','YYYY/MM/DD'),'YYYY/Q') FROM DUAL;

→2020/3

月份
MONTH 月全称
MON 月简称
MM 月数（两位）

SELECT TO_CHAR(TO_DATE('2020/08/20','YYYY/MM/DD'),'MONTH') FROM DUAL;

→8月

SELECT TO_CHAR(TO_DATE('2020/08/20','YYYY/MM/DD'),'MON') FROM DUAL;

→8月

SELECT TO_CHAR(TO_DATE('2020/08/20','YYYY/MM/DD'),'MM') FROM DUAL;

→08

星期
WW 一年中的第几个周
IW 一年中的第几个周，星期一为本周的第一天，每年末最后一个周不足四天算至下年第一周，足四天将下年最后几天视为本年第一周，年初亦然
DAY 星期几
DY 星期几的简称‘

日
D 星期中的第几天
DD 月中的第几天
DDD 年中的第几天

时
HH24 一天中的第几个小时（24小时制）
HH/HH12 一天中的第几个小时（12小时制）
AM 上午
PM 下午

分
MI 一个小时中的第几分钟
秒
SS 一个分钟里的第几小时

TO_NUMBER()函数

将只含有数字的字符串转化为数值型
加上格式，还可以去除千分符和货币符号等字符

TO_NUMBER(字符串，格式)

应用：

SELECT TO_NUMBER(TO_CHAR(12345678,'99,999,999'),'99,999,999') FROM DUAL;

去除特定字符时，格式和 加上特定字符的格式 一致

开窗函数

开窗函数又叫分析函数、窗口函数

讲开窗函数之前先来看看聚合函数

聚合函数
将数据按照一定的规则分组，统一分析各组的某项情况，每个分组返回一行结果

那么开窗函数也有了定义：

开窗函数
将数据按照一定的规则分组，统一分析各组的某项情况，每行数据返回一行结果

开窗函数语法：

分析函数()OVER(分析子句)--OVER()是开窗函数的一个标志

分析子句（OVER子句）的语法常规如下：

OVER(PARTITION BY COLUMN1 ORDER BY COLUMN2)

其中

PARTITION BY COLUMN1:
指明以COLUMN1列分组
ORDER BY COLUMN2:
指明以COLUMN2列排序

开窗函数分为两类
1.聚合类

SUM()  AVG()  MAX()  MIN()  COUNT()

分组聚合

2.排序类

ROW_NUMBER()    RANK()    DENSE_RANK()

分组组内排序

聚合类应用：

聚合函数中必须有参数，OVER子句后面不一定需要有，如果没有，默认对整表分析

SELECT  DEPTNO,SUM(SAL)OVER() FROM EMP ;--分析子句中为空时，默认从第一行统计到最后一行

→
在这里插入图片描述

如果参数齐全，则按照正常的语法执行此语句

SELECT DEPTNO,SUM(SAL)OVER(PARTITION BY DEPTNO) FROM EMP;--分析子句中有分组参数时，默认从组内第一行统计到最后一行

→
在这里插入图片描述

SELECT DEPTNO,SAL,SUM(SAL)OVER(ORDER BY SAL) FROM EMP ;--如果分析子句中只有排序子句呢？这里有一个默认的窗口范围

→
在这里插入图片描述

注意：
注意上图的第4、5行，这两行的计算规则需要注意一下

排序类应用：

1.ROW_NUMBER()函数

ROW_NUMBER()

该函数可以将表格中的数据先分组排序，再标记第1、2、3…
应用：

SELECT EMP.*,ROW_NUMBER()OVER(ORDER BY SAL) FROM EMP ;

→
在这里插入图片描述应用：

SELECT EMP.*,ROW_NUMBER()OVER(PARTITION BY DEPTNO ORDER BY SAL) FROM EMP ;

→
在这里插入图片描述

2.RANK()函数

RANK()

和ROW_NUMBER函数的区别在于
RANK()的排序中，只有一个排序条件，不会有次排序条件，是第几，就是第几，有并列就并列取前位

3.DENSE_RANK()函数

DENSE_RANK()

是第几不一定是第几，排序过程中不会有跳名次的情况，有并列不会并列取后位

4.会并列取后函数

SELECT E.*,COUNT(*)OVER(ORDER BY SAL) FROM EMP E;

以上4函数总结

SELECT E.*,ROW_NUMBER()OVER(ORDER BY SAL) FROM EMP E;
SELECT E.*,RANK()OVER(ORDER BY SAL) FROM EMP E;
SELECT E.*,DENSE_RANK()OVER(ORDER BY SAL) FROM EMP E;
SELECT E.*,COUNT(*)OVER(ORDER BY SAL) FROM EMP E;

→
在这里插入图片描述

分析函数的注意：

1.分析函数不包含需要分析的内容
2.对于分析子句，窗口子句最少得有ORDER BY 语句
3.不能直接写在 WHERE后，起别名也无效，应该套用子查询

窗口范围

窗口范围是分析子句中的一个也可有也可无的参数，其经常跟随着聚合函数（比如SUM()/AVG()等）

OVER(PARTITION BY  ORDER BY RANGE ROWS BETWEEN [UNBOUNDED PRECEDING]/[N PRECEDING] AND [N FOLLOWING]/[UNBOUNDED FOLLOWING])

应用：

SELECT DEPTNO,COUNT(*) OVER(ORDER BY SAL ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) FROM EMP;

→
在这里插入图片描述

这里，ROWS BETWEEN 语句的作用是为了限制COUNT(*)语句的作用范围

这里我们将COUNT(*)替换为SUM()也一样

SELECT DEPTNO,SAL,SUM(SAL) OVER(ORDER BY SAL ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) FROM EMP;

→
在这里插入图片描述

Life lies in diligence.

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录

【Oracle】第7天 函数

函数

字符型函数

1.大小写控制函数

2.字符控制函数

CONCAT()

WM_CONCAT()

REPLACE函数

TRIM函数

LPAD()和RPAD()函数

SUBSTR()函数

INSTR()函数

数值型函数

1.取绝对值

2.向上取整/向下取整

3.四舍五入/截取

4.取余函数

5.平方根函数

6.幂指函数

7.ASCII码相关函数

8.ROWID伪列

9.去重函数

10.判断函数

11.去空函数

12.空值处理函数

日期函数

1.获取月份差值函数

2.获取月末日期函数

3.获取下星期几函数

4.SYSDATE函数

5.月份加减函数

转换函数

TO_CHAR()与TO_DATE()

TO_NUMBER()函数

开窗函数

聚合类应用：

排序类应用：

1.ROW_NUMBER()函数

2.RANK()函数

3.DENSE_RANK()函数

4.会并列取后函数

以上4函数总结

分析函数的注意：

窗口范围

【Oracle】第7天函数