Oracle技术整理

一、sql的常用方法

1.（+）表关联一般不要用

2.order by 可以分组降序

3.row_number
row_number() over (partition by col1 order by col2) 表示根据col1分组，在分组内部根据 col2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)

4.rank
rank() over (partition by col1 order by col2) 相同的话返回相同的序号 下一名 跳跃

5.dense_rank
dense_rank() over (partition by col1 order by col2) 相同的话返回相同的序号 下一名 不跳跃

6.where 、on 后面字段尽量不要做运算

7.尽量用 exists ，not exists

8.截取函数

   substr(hs.STUDENT_NAME, 0, 1) --截取函数 --

9.递归查询

CONNECT BY PRIOR MANAGER_NO = TEACHER_NO --递归查询 查询所有上级（oracle）--

10.日期格式化函数

 select trunc(sysdate) from dual --2021-01-06 今天的日期为2021-01-06
 select trunc(sysdate, 'mm') from dual --2021-01-01 返回当月第一天.
 select trunc(sysdate,'yy') from dual --2021-01-01 返回当年第一天
 select trunc(sysdate,'dd') from dual --2021-01-06 返回当前年月日
 select trunc(sysdate,'yyyy') from dual --2021-01-01 返回当年第一天
 select trunc(sysdate,'d') from dual --2021-01-06 (星期天)返回当前星期的第一天
 select trunc(sysdate, 'hh') from dual --2021-01-06 17:00:00 当前时间为17:35 
 select trunc(sysdate, 'mi') from dual --2021-01-06 17:35:00 TRUNC()函数没有秒的精确

11.数字格式化

 /*
 TRUNC（number,num_digits） 
 Number 需要截尾取整的数字。 
 Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。
 TRUNC()函数截取时不进行四舍五入
 */
 select trunc(123.458) from dual --123
 select trunc(123.458,0) from dual --123
 select trunc(123.458,1) from dual --123.4
 select trunc(123.458,-1) from dual --120
 select trunc(123.458,-4) from dual --0
 select trunc(123.458,4) from dual --123.458
 select trunc(123) from dual --123
 select trunc(123,1) from dual --123
 select trunc(123,-1) from dual --120

12.case when用法

    CASE
    WHEN b.CORE >= 90 THEN
       '优秀'
       WHEN b.CORE >= 80 THEN
       '良好'
       WHEN b.CORE >= 60 THEN
       '及格'
    ELSE
       '不及格'  
    END lel ---(9)

13.ADD_MONTHS (加一个整月)

select add_months(date'2021-11-19',1) month
  from dual;

14.like

   <!--like -->
   <where>
       <if test="className != null and className != ''">
           and class_name like '%'||#{className, jdbcType=VARCHAR}||'%' x
       </if>
   </where>

15.over partition by与group by的区别：

group by是对检索结果的保留行进行单纯分组，一般和聚合函数一起使用例如max、min、sum、avg、count等一块用。partition 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行数据。
ratio_to_report() over(partition by ... order by ...)：Ratio_to_report() 括号中就是分子，over() 括号中就是分母。
percent_rank() over(partition by ... order by ...)：

16.SQL文中取两个字段的最小值
方法1：

select min(c) from 
(select  min(b) as c from tab_name
union
select min(a) as c from tab_name);

方法2：

select least(min(A),min(B)) from table;least(...),greatest(...)中如果含有null的话，无法得到结果
select greatest(max(A),max(B)) from table;

17.oracle 11g开始支持的行转列 列转行函数

原文连接：https://www.oracle.com/cn/database/articles/technology/pivot-and-unpivot.html

select *
from (select * from hand_student_core)
pivot (sum(CORE) for STUDENT_NO in ('s001','s002','s003','s004'));

二、序列

1.序列的创建

create sequence 序列名称
[increment by 步长]
[start with 开始值]
[maxvalue 最大值 | nomaxvalue]
[minvalue 最小值 | nominvalue]
[cycle|nocycle][cache 缓存大小|nocach]

CYCLE表示在递增至最大值或递减至最小值之后是否继续生成序列号，若是递减并有最大值，从最大值开始。 若是递增有最小值，从最小值开始。若没有，从START WITH 指定的值开始。

默认是NOCYCLE CACHE用来指定先预取p个数据在缓存中，以提高序列值的生成效率，默认是20

2.序列的使用

insert into hand_student values(hand_student_s.nextval,'同学1',20,'男');
 
SELECT hand_student_s.CURRVAL
FROM dual;

3.更改序列

ALTER SEQUENCE hand_student_s
 INCREMENT BY 20
 MAXVALUE 999999
 NOCACHE NOCYCLE;

4.删除序列

DROP SEQUENCE hand_student_s;

三、数据库分区

1.表分区的优缺点

表分区有以下优点：
1、改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
2、增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
3、维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；
4、均衡I/O：可以把不同的分区映射到不同磁盘以平衡I/O，改善整个系统性能。
缺点：
分区表相关：已经存在的表没有方法可以直接转化为分区表。不过 Oracle 提供了在线重定义

2.分区操作

--查看分区
select *
  from user_tab_partitions t
  where t.table_name = 'HALM_MD5_VALUE_MAP';
--创建分区
  partition by list (app)
(
    partition P1900010100010001 values ('1900010100010001')
);
--添加分区app
select 'alter table halm_md5_value_map add partition "P' || rp_app || '" VALUES(''' || rp_app || ''') UPDATE INDEXES;'
  from halm_platform;

3.子分区操作

--添加子分区
alter table HALM_PROCESS_CASH_FLOWS_ST
    modify partition "P2018123102810321"
        add subpartition "P2018123102810321_0"
        values ('0') update indexes; 
--删除
alter table HALM_PROCESS_CASH_FLOWS_ST  drop partition "P2018123102810321";
--清除子分区数据
alter table HALM_PROCESS_CFL_DIM truncate subpartition "P15086771cf66df79" update indexes;
--清除子分区数据,结构
alter table HALM_PROCESS_CFL_DIM drop subpartition "P15086771cf66df79" update indexes;

--查看子分区
SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='HALM_PROCESS_CFL_DIM';
select * from DBA_TAB_SUBPARTITIONS where TABLE_NAME = 'HALM_PROCESS_CFL_DIM';