整理一些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 只要你深入,你一定会发现他的强大与乐趣。