基础+进阶的oracle精华

整理一些oracle精华、项目中常用的oracle语法。

---- ---- ----

表相关（常用语句）：

快速创建表

create table table1 as
select * from table2 where 1=0;

快速查询表结构

desc table1;

自增长序列

CREATE SEQUENCE SEQ_table1
INCREMENT BY 1
START WITH 100000
NOMAXVALUE
NOCYCLE
CACHE 10;

触发器

CREATE OR REPLACE TRIGGER TIB_table1
BEFORE INSERT ON table1
FOR EACH ROW
  BEGIN
    SELECT SEQ_table1.nextval
    INTO :new.ID
    FROM dual;
  END;

flash 闪回表

alter table table1 enable row movement;
flashback table table1 to timestamp to_timestamp('2018-01-05 15:10:00','yyyy-mm-dd hh24:mi:ss');
alter table table1 disable row movement;
select * from table1 ; 

数据库实施的时候你也许会用到：

创建表空间

create tablespace JWBJ 
datafile 'E:\app\Administrator\oradata\JWBPBJ\JWBJ.dbf' size 2048M --存储地址 初始大小2G
autoextend on next 200M maxsize unlimited   --每次扩展200M，无限制扩展
EXTENT MANAGEMENT local  autoallocate
segment space management auto;

创建用户并指定表空间及赋予权限

create user jwbpbj  identified by jwbpbj default tablespace JWBJ;
grant dba to jwbpbj;

--重新指定用户表空间
alter user jwbpbj  default tablespace JWWL;

创建directory 

select * from dba_directories;
create directory DIR_BPBJ as 'E:\app\Administrator\dir_bpbj';

oracle 密码快过期 

-- 修改永不过期后 需要重新修改密码才生效
SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

指定用户QUEMAT查看xx表的权限

grant select on table1 to QUEMAT;

数据链路

create public database link test_JW(数据库链路的名字)   
  connect to eysonline(要连接数据库的用户名) identified by eysonline(要连接数据库的密码)  
  using   '(DESCRIPTION =
    (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.xx.xx  )(PORT = 1521)))
    (CONNECT_DATA =(SID = test1))
  )';(单引号内的内容是要连接数据库的连接字符串) 
 
INSERT INTO tt.table1@test_JW
SELECT * FROM table2 ;

数据库备份/迁移

--原数据库导出 
expdp user/psd@tt directory=DIR_tt dumpfile=2018-8-07bak.dmp logfile=2018-8-07bak.log 
--新数据库导入 
impdp user/psd@tt directory=DIR_tt dumpfile=2018-8-07bak.dmp  logfile=2018-8-07bak.log

查看当前用户

show user;

job启用/停用

--启动job

exec dbms_job.run(44);

--停用job

EXEC DBMS_JOB.BROKEN(44,TRUE);

查询相关：

排序时空值放最后

select * from test_table ORDER BY in_date desc nulls last;

minus  （union的相反用法）

例：table1 字段check_no的值集有 1,2,3,4 table2 字段stock_in_code的值集有 2,3,4 。经过minus 比较查询后 最终查询结果集是1。相反，如果把table2 放在上方 table1放在下方 则查询结果集为空。这就是一个包含的关系 切记不要用反了顺序。minus反之用法 union 合并结果集 这个就不多说了。

--table1结果集排除table2的结果集 把剩下的结果集查询出来
SELECT check_no FROM table1 where  is_deal='1' 
minus
select stock_in_code from table2 where account_id = '1234';

高级查询over partition by（分析函数）

例：统计各单位商品金额最多的商品信息

-- 以account_id及dept_code 做分组条件 以qm_money 倒排序 取前6条商品信息
select 
    material_code as materialCode,material_name as materialName,qm_money as intyMoney
from(
  select 
    material_code,material_name,qm_money,
    row_number() over(partition by account_id,dept_code order by a.qm_money desc) rn
  from TJ_HOME_CHART a 
  where a.account_id = 1234 and a.dept_code like '0101' || '%' and material_diff != '03'
)
where rn <= 6;

一些常用函数

-- str1为null时 转成0
select nvl(str1,'0') from table1;

-- case when 当str1为0时 截取str1前4位（substr函数） 当str1位1时 截取前2位 否则等于本身
select 
(case nvl(str1,'1')  when '0' then substr(str1,0,4)
 when '1' then substr(str1,0,2)
else str1 end) as str1
from table1;

--DECODE函数 当str1为0时 值为0 否则 汇总str2求和（sum聚合函数）
SELECT DECODE (str1,'0', 0,sum(str2))  FROM table2;

--一些聚合函数+group by+having (avg平均 max最大)
select str1,sum(str2),avg(str3),max(str4) from table1
group by str1 having sum(str2) > 1;

--日期函数+to_char函数
select * from table1 where
str1 = TO_CHAR ( ADD_MONTHS ( TO_DATE (str2, 'yyyy-mm-dd'), -1),'yyyy-mm');

--REGEXP_LIKE正则表达式函数（与LIKE功能相似）
select * from table1 where 
REGEXP_LIKE(str1,'^(01|02|03)');

行转列 

-- 行转列 默认 逗号隔开
select wm_concat(str1) name from table1;

--列转行 可以看一篇案例https://www.cnblogs.com/mellowsmile/p/4642306.html

操作数据相关：

update  -- exists

exists表示()内子查询语句返回结果不为空说明where条件成立就会执行主sql语句，如果为空就表示where条件不成立，sql语句就不会执行。not exists和exists相反，子查询语句结果为空，则表示where条件成立，执行sql语句。负责不执行。

update table1 set (str1,str2) = (select ss1,ss2 from table2 where table1.id = table2.id)
where exists(select 1 from table2 where table1.id = table2.id);

alter操作

--添加字段及注释
alter table table1 add (str1 varchar2(10));
COMMENT ON COLUMN table1.str1 IS '这是备注';
--修改列名
ALTER TABLE table1 RENAME COLUMN str1 to str2;
--删除字段
alter table table1 drop column str1;

insert into select 快速插入

--将table1结果集插入到table2
Insert into Table2 
select  *  from Table1;

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 今天先整理这么多，空了再更新， oracle 只要你深入，你一定会发现他的强大与乐趣。