[TOC]
PS：本博客收錄自己工作中遇到學(xué)到的一些Oracle技能，有時間就更新整理一下
(1)Oracle正則匹配使用
PS:這條SQL可以通過正則匹對查詢一下，表A的字段a是否有非數(shù)字的數(shù)據(jù)油讯，有時候數(shù)據(jù)表的一些字段是varchar類型的塔拳，如果sql里用to_number的話欠橘，就肯定會報無效數(shù)字的錯誤
select * from 表A where not regexp_like(字段a,'^[[:digit:]]+$');
(2)Oracle修改有數(shù)據(jù)的數(shù)據(jù)字段類型
有些時候摄悯，我們是要修改一些數(shù)據(jù)字段的類型的赞季，mysql是可以支持的，不過Oracle對一些有數(shù)據(jù)的數(shù)據(jù)字段是不予許修改的奢驯，這時候申钩，可以通過下面sql進行修改，大概思路就是創(chuàng)建一個臨時的瘪阁，負責轉(zhuǎn)運數(shù)據(jù)撒遣，然后再切換
alter table 表A add new_temp varchar2(50);
update 表A set new_temp = a;
commit;
update 表A set a = '';
commit;
alter table 表A modify a varchar2(50);
update 表A set a = new_temp;
commit;
alter table 表A drop column new_temp;
(3)Oracle表數(shù)據(jù)回滾語句
【表數(shù)據(jù)恢復(fù)回N分鐘之前】
alter table 表名 enable row movement;
flashback table 表名 to timestamp systimestamp - interval 'N' minute;
(4)sql篩選出記錄數(shù)大于2的記錄
select id,count(*) from A a
where a.test like '%'
group by id
having count(*)>=2
(5)oracle同義詞
創(chuàng)建同義詞：
create public synonym tablename for user.tablename;
synonyms
(6)oracle內(nèi)外連接
Oracle左右連接可以使用(+)，+號在左表示右外連接管跺，在右表示左外連接
(7)oracle去重關(guān)鍵字
Oracle關(guān)鍵字：
distinct：過濾掉多余的重復(fù)記錄只保留一條
(8)Oracle不等于符號
oracle不等于有下面三種表示：
!=义黎、^=、<>
<>可移植性好
(9)1=1和1=0
select * from t where 1=1
1=1條件始終為true豁跑，是為了避免負責的條件查詢出現(xiàn)錯誤
1=0條件始終未false廉涕，結(jié)果不會返回任何數(shù)據(jù)，所以可以用于建表艇拍，create table newtable as select * from oldtable where 1=0;
(10)創(chuàng)建表空間
--創(chuàng)建表空間
CREATE SMALLFILE
TABLESPACE "WORKFLOW01"
LOGGING
DATAFILE
'D:\app\Administrator\oradata\oanet\WORKFLOW01.ora' SIZE
245M EXTENT MANAGEMENT LOCAL SEGMENT SPACE Management AUTO
(11)Oracle in關(guān)鍵字和等于符號
oracle in 和 =的區(qū)別
in可以有多條記錄狐蜕，=只有一條記錄
=性能更好，因為使用=符合能更好地使用索引
(12)NVL函數(shù)
NVL函數(shù)的格式如下：NVL(expr1,expr2)
含義是：如果oracle第一個參數(shù)為空那么顯示第二個參數(shù)的值淑倾，如果第一個參數(shù)的值不為空馏鹤，則顯示第一個參數(shù)本來的值。
NVL2函數(shù)的格式如下：NVL2(expr1,expr2, expr3)
含義是：如果該函數(shù)的第一個參數(shù)為空那么顯示第二個參數(shù)的值娇哆，如果第一個參數(shù)的值不為空湃累，則顯示第三個參數(shù)的值。SQL> select ename,NVL2(comm,-1,1) from emp;
NULLIF(exp1,expr2)函數(shù)的作用是如果exp1和exp2相等則返回空(NULL)碍讨，否則返回第一個值治力。
Coalese函數(shù)的作用是的NVL的函數(shù)有點相似，其優(yōu)勢是有更多的選項勃黍。
格式如下：
Coalesce(expr1, expr2, expr3….. exprn)
表示可以指定多個表達式的占位符宵统。所有表達式必須是相同類型，或者可以隱性轉(zhuǎn)換為相同的類型覆获。
(13)union
union: 對兩個結(jié)果集進行并集操作, 不包括重復(fù)行,相當于distinct, 同時進行默認規(guī)則的排序;
union all: 對兩個結(jié)果集進行并集操作, 包括重復(fù)行, 即所有的結(jié)果全部顯示, 不管是不是重復(fù);
(14)exist和in
select a.id from A a where a.id in(select b.id from B b)
select a.id from A a where exists(select b.id from B b where a.id = b.id)
(15)遞歸查詢
start with 子句：遍歷起始條件马澈，有個小技巧，如果要查父結(jié)點弄息，這里可以用子結(jié)點的列痊班，反之亦然。
connect by 子句：連接條件摹量。關(guān)鍵詞prior涤伐，prior跟父節(jié)點列parentid放在一起馒胆，就是往父結(jié)點方向遍歷；prior跟子結(jié)點列subid放在一起凝果，則往葉子結(jié)點方向遍歷
https://www.cnblogs.com/benbenduo/p/4588612.html
(16)條件sql
case when then else end 可以理解為流程控制語句或條件控制語句祝迂。可以實現(xiàn)資料獲取的時候器净，可以更多的條件和自定義邏輯
select 1 from mytable;與select anycol(目的表集合中的任意一行） from mytable;與select * from mytable 作用上來說是沒有差別的型雳，都是查看是否有記錄，一般是作條件用的掌动。select 1 from 中的1是一常量四啰，查到的所有行的值都是它，但從效率上來說粗恢，1>anycol>*，因為不用查字典表欧瘪。
(17)oracle數(shù)據(jù)庫還原
1眷射、將TESTDB.bmp放置在dpdump目錄，如下路徑：
E:\app\Administrator\admin\orcl\dpdump
2佛掖、創(chuàng)建用戶
create user testdb65 identified by "testdb65"
default tablespace users temporary tablespace temp;
用戶授權(quán)
grant connect,dba to testdb65;
3妖碉、創(chuàng)建表空間
CREATE TABLESPACE HR_DATA01 DATAFILE 'D:\nhsj\hr_data01.dbf' SIZE 500M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K ; 【創(chuàng)建表空間】
4、導(dǎo)入 bgyhr2是來源庫
impdp testdb65/testdb65@orcl schema=testdb65 dumpfile=TESTDB65.dmp
導(dǎo)出 scott 是要導(dǎo)出的庫
expdp lfwtrain/a@orcl schema=當前用戶 dumpfile=expdptest.dmp
(18)重導(dǎo)dmp文件
Oracle保存數(shù)據(jù)時一般以DMP文件的形式進行保存
本博客記錄芥被，DMP文件的導(dǎo)入 先打開CMD
sqlplus /nolog
以sysdba連接
conn /as sysdba
這里創(chuàng)建表空間欧宜，因為表空間進行存儲的話，可以提供系統(tǒng)性能
CREATE TABLESPACE DATA01 DATAFILE 'D:\tablespace\data01.dbf' SIZE 100M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K ;
創(chuàng)建用戶
CREATE USER job IDENTIFIED BY job DEFAULT TABLESPACE DATA01 TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON DATA01
授權(quán)
grant connect,dba to job;
復(fù)制dmp文件到oracle安裝目錄里的app\admin\orce\dpdump里拴魄，然后用以下代碼導(dǎo)入dmp文件冗茸，要順便導(dǎo)入logfile的話，加上logfile=文件名
impdp job/job@orcl schemas=job dumpfile=EXPDP160810GZ.dmp
