【开发日记】Oracle 常用操作及解决方案

--查询表说明和对应的表名
select t.table_name,f.comments from user_tables t inner join user_tab_comments f on t.table_name=f.table_name where f.comments like '%十周年%'

--修改语句
update 表名 set 列名=‘值’where 条件


--插入数据
insert into 表名 （字段1,字段2）values （'xxxxx','xxxxx'）;

--时间日期格式插入
    Sysdate --生成当前时间
    to_char(sysdate,'yyyy-mm-dd') --生成当前时间并规定样式 （时间格式可自定义）
    to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') --date类型转字符串类型
    to_date('2018/1/3 00:00:01','yyyy/mm/dd,hh24:mi:ss') --字符串类型转date类型
    --样例：
    insert into 表名 （字段1,字段2,字段3）values （sysdate,to_char(sysdate,'yyyy-mm-dd'),to_date('2018/1/3 00:00:01','yyyy/mm/dd,hh24:mi:ss')）;

--创建索引 
    create index 索引名称 on 表名 (字段名称);

--删除索引 
    drop index 索引名;


--创建组合索引 
    create index 索引名 on 表名(列名1,列名2);


--在数据库中查找表名 
    select * from user_tables where table_name like 'tablename%';


--查看该表的所有索引 
    select * from all_indexes where table_name = 'tablename';


--查看该表的所有索引列 
    select* from all_ind_columns where table_name = 'tablename';


--修改表名
    alter table table1 rename to table2

--添加列
    alter table 表名 add (列名 列类型,列名 列类型);

--删除列
    alter table 表名 drop column 列名

--表分区
    --创建表分区  
     --注意：创建分区表之后可以添加索引，但是如果某列即使分区条件列又要添加主键，那么表中不能有数据，需在空表中添加主键
     --表分区：
    --Createtable-创建表
        create table表名(
        id varchar(36),
        name varchar(36),
        value varchar(36),
        creat_date date
        )
    
    --创建分区，单表中不能有多种类型的分区
        --该分区为范围分区
            partition by range(id)(
            partition 分区名 values less than('200') tablespace 表空间, --该分区为以id分区0-200的区间
            partition 分区名 values less than('400') tablespace 表空间 --该分区为以id分区200-400的区间，如需添加以此类推
            )
            tablespace表空间;
        --该分区为时间分区，自动以时间分区
            partition by range (creat_date)  ---分区的依据字段
            INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))  --自动增加分区的间隔，MONTH可替换为day，yesr等
            (
            partition  p1  valueslessthan(to_date('2020-08-01','yyyy-mm-dd'))
            )
            tablespace 表空间 ;
    
    --查询单个分区的数据
    select * from 表名 partition (分区名);
    
    ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2018-09-01','YYYY-MM-DD'));

--创建视图

    create or replace view 试图名称 as （select  -------）
--创建双表查询合并
     create or replace view 试图名称 as  (
        select    a,b,c from 表1  
        union  all
            select  
            a1 as a ,
            (casse states 
                when 'i1'  then 'O1'
                when 'i2'  then 'O2'
                else  b1 end
            )as b,
            ' ' as c
            from 表2
            )
         
         
--说明：
--(casse states 
-- when 'i1'  then 'O1'
-- when 'i2'  then 'O2'
-- else  b1 end
--)as b,
--其中该部分为 第二个表匹配第一个表的字段，如果第二个表的值为
--i1 则对应显示O1
--同理i2对应显示O2
--如果没有其中条件则按原数值显示


--删除视图
drop view 视图名称;
--查看表实际占用空间
select * from (select segment_name, sum(bytes)/1024/1024/1024 "共占用(G)" from user_segments group by segment_name) where segment_name  in ('table_name');

--oracle数据表恢复

--第一步 查询对应表的提交记录
    select * from v$sql where sql_text like '%表名%' ORDER BY FIRST_LOAD_TIME DESC;

--查询到之后有该表的提交时间与提交记录以及提交条件

--第二步 查询到提交时的记录，按照当时提交的条件，查询到以提交的数据。

select  * from 表名 as of timestamp to_timestamp('时间','yyyy-mm-dd hh24:mi:ss') where 条件（此条件为第一步查询到的执行条件）;

--查询，对应数值 显示对应的列与值
SELECT
    id,
  ( CASE Sex
  WHEN'1'THEN'男'
  WHEN'0'THEN'女'
  ELSE' 未知'
  END)asSex,  
from 表名

--该功能为当数据库存储的性别为  1-男  0-女  时可以匹配查询到的数据为对应值

--截取函数 （功能为截取某位前或后的字符）
SUBSTR（）

--配合规则函数使用（功能为返回该字符串中对应字符或字符串的位置信息）
INSTR（）

--substr函数：截取某字符串
    substr(string,start,[length])
    
    --参数以及说明
    --string ：表示要截取的字段或者字符串
    --start：表示开始位置，如果start是负数，则从string的末尾算起
    --length：【可选项】 表示要截取的长度


--instr函数：返回字符串中某个值的位置
    instr(string,ch_string,[start],show_time)

    --参数以及说明
    --string ：表示字符串 
    --ch_string：要查找的字符串例如 ‘/’或者‘-’
    --start：【可选项】开始查找位置，默认1 ，如果为-1 则从右向左找
    --show_time：表示要查找的字符串第几次出现在原字符串中 

--案例
//该sql为查找最后一个‘/’之后的所有字符
select  SUBSTR('123/456/789',INSTR('123/456/789','/',-1,1)+1) from bual