Orcale常用函数用法总结

最新推荐文章于 2021-03-22 12:12:16 发布

BiuBiuBlingBling

最新推荐文章于 2021-03-22 12:12:16 发布

阅读量434

点赞数

分类专栏：工具

本文链接：https://blog.csdn.net/bigbingtang/article/details/101290303

版权

工具专栏收录该内容

17 篇文章 0 订阅

订阅专栏

1.DECODE

SELECT *
  FROM TABLE_NAME
 WHERE DECODE(V_CODE, '0', 1, INSTR(V_CODE, CODE)) > 0;

释义：V_CODE是传进来的字符串，CODE是要对比的那一列

如果='0',则使用条件1>0，即返回全部数据。

如果不等于‘0’，则使用条件INSTR(V_CODE, CODE) > 0

2.TRUNC

TRUNC（NUMBER）表示截断数字，TRUNC（date）表示截断日期。

格式：TRUNC（n1,n2），n1表示被截断的数字，n2表示要截断到那一位。n2可以是负数，表示截断小数点前。注意，TRUNC截断不是四舍五入。

/**************日期********************/
1.select trunc(sysdate) from dual --2019-9-18 今天的日期为2019-9-25
2.select trunc(sysdate, 'mm')   from   dual --2019-9-1    返回当月第一天.
3.select trunc(sysdate,'yy') from dual --2019-1-1       返回当年第一天
4.select trunc(sysdate,'dd') from dual --2019-9-25    返回当前年月日
5.select trunc(sysdate,'yyyy') from dual --2019-9-1   返回当年第一天
6.select trunc(sysdate,'d') from dual --2019-9-22 (星期天)返回当前星期的第一天
7.select trunc(sysdate, 'hh') from dual   --2019-9-25 11:00:00   当前时间为11:10
8.select trunc(sysdate, 'mi') from dual --2019-9-25 11:10:00   TRUNC()函数没有秒的精确
/***************数字********************/
/*
TRUNC（number,num_digits）
Number 需要截尾取整的数字。
Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。
TRUNC()函数截取时不进行四舍五入
*/
9.select trunc(123.458) from dual --123
10.select trunc(123.458,0) from dual --123
11.select trunc(123.458,1) from dual --123.4
12.select trunc(123.458,-1) from dual --120
13.select trunc(123.458,-4) from dual --0
14.select trunc(123.458,4) from dual --123.458
15.select trunc(123) from dual --123
16.select trunc(123,1) from dual --123
17.select trunc(123,-1) from dual --120

3.AVG

AVG(列名)：求该列的平均值

4.ROUND 四舍五入

5.TO_CHAR

TO_CHAR(SYSDATE, 'YYYY-MM-DD')

6.TO_DATE

TO_DATE('2019-05-03', 'YYYY-MM-DD')

7.SUBSTR 截取字符串

SUBSTR(目标字符串，开始位置，截取长度)

eg：SUBSTR('ABCDEF',4,3)

输出：DEF

8.KEEP() 分析函数

取同一个分组下以某个字段排序后，对指定字段取最小或最大的那个值。

语法：

-- min | max(column1) keep (dense_rank first | last order by column2) over (partion by column3);

-- 最前是聚合函数，可以是min、max、avg、sum。。。

-- column1为要计算的列；

-- dense_rank first，dense_rank last为keep 函数的保留属性，表示分组、排序结果集中第一个、最后一个；

-- 解释：返回按照column3分组后，按照column2排序的结果集中第一个或最后一个最小值或最大值column1。

select deptno, max(sal) keep(dense_rank first order by sal) first_max, max(sal) keep(dense_rank last order by sal) last_max from emp group by deptno;

-- 我们来分析一下，SQL里面的group by 是作用于聚会函数的，可以这么理解一下。还有，这里我们头脑里应该有这么一个概念，排序即分组

9.COUNT()

select count(*) ：查询所有列

select count(0)：忽略所有列，用数字效率要高一些，因为统计行数，与列无关。

10. MERGE INTO

orcal提供用来解决插入数据insert or update的问题，有相同数据

eg：

create table TEST (ID INTEGER,VALUE VARCHAR2(255) );   
insert into TEST values (1, 'test1');   
insert into TEST values (2, 'test2');

我们想插入一条数据 {ID=2,NAME='newtest2'} 那么可以这么写

--MERGE INTO  目标表
--USING 数据源表 on 关联条件
--WHEN MATCHED THEN UPDATE SET 赋值
--WHEN NOT MATCHED THEN  INSERT 赋值; 

MERGE INTO  TEST T1
USING (SELECT '2' as ID, 'newtest2' as NAME FROM dual) T2 on (T1.ID=T2.ID)
WHEN MATCHED THEN UPDATE SET T1.NAME=T2.NAME
WHEN NOT MATCHED THEN  INSERT (T1.ID, T1.NAME) VALUES (T2.ID, T2.NAME );

如果ID为2的数据存在那么 UPDATE，如果不存在INSERT

从using 搜出来的结果逐条与on条件匹配，然后决定是update还是Insert。当USING后面的sql没有查询到数据的时候，Merge Into语句是不会执行update和Insert操作的。

11.sign

sign(n) 取数字n的符号,大于0返回1, 小于0返回-1, 等于0返回0;

sign(a-b) 比较ab的大小，a>b 返回1 a<b返回-1 a=b返回0;

select sign(100),sign(-100),sign(0) from dual;
SIGN(100) SIGN(-100) SIGN(0)
--------------
1  -1  0
===============================================
a=10;b=20;
sign(a-b) = -1

12.ROW_NUMBER() OVER()函数

分组排序

over()里头的分组以及排序的执行晚于 where 、group by、 order by 的执行。

--测试数据
create table TEST_ROW_NUMBER_OVER(
       id varchar(10) not null,
       name varchar(10) null,
       age varchar(10) null,
       salary int null
);
select * from TEST_ROW_NUMBER_OVER t;
 
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(1,'a',10,8000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(1,'a2',11,6500);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(2,'b',12,13000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(2,'b2',13,4500);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(3,'c',14,3000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(3,'c2',15,20000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(4,'d',16,30000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(5,'d2',17,1800);


--一次排序：对查询结果进行排序（无分组）
select id,name,age,salary,row_number()over(order by salary desc) rn
from TEST_ROW_NUMBER_OVER t;
--进一步排序：根据id分组排序(id有重复，rank显示的是每一组的组内序号)
    select id,name,age,salary,row_number()over(partition by id order by salary desc) rank
    from TEST_ROW_NUMBER_OVER t;
--再一次排序：找出每一组中序号为一的数据
    select * from(select id,name,age,salary,row_number()over(partition by id order by salary desc) rank
    from TEST_ROW_NUMBER_OVER t)
    where rank <2
--排序找出年龄在13岁到16岁数据，按salary排序
    select id,name,age,salary,row_number()over(order by salary desc)  rank
    from TEST_ROW_NUMBER_OVER t where age between '13' and '16'

13.OVER(PARTITION BY)

分组以及排序

sum() over(partition by … order by …)：求分组后的总和。
first_value() over(partition by … order by …)：求分组后的第一个。
last_value() over(partition by … order by …)：求分组后的最后一个。
count() over(partition by … order by …)：求分组后的总数。
max() over(partition by … order by …)：求分组后的最大值。
min() over(partition by … order by …)：求分组后的最小值。
avg() over(partition by … order by …)：求分组后的平均值。
lag() over(partition by … order by …)：取出前n行数据。
lead() over(partition by … order by …)：取出后n行数据。
14.NVL
nvl()函数的格式如下：NVL(expr1,expr2);
判断某个值是否为空值，若不为空值则输出expr1，若为空值，返回指定值expr2。

15.Oracle in 查询数据

select * from 表名 where 字段 in ('AAA','BBB','CCC');

select * from 表名 where 字段 not in ('AAA','BBB','CCC');

以上不包括null的情况。

select * from 表名 where 字段 is null;

参考：

https://blog.csdn.net/haiross/article/details/12837033

https://blog.csdn.net/qq_39949109/article/details/80819308

https://www.cnblogs.com/relucent/p/4166544.html

https://blog.csdn.net/qq_25221835/article/details/82762416

https://www.cnblogs.com/520future/p/7463661.html

https://blog.csdn.net/sinat_27933301/article/details/80834802