文章目錄
(1)Oracle正則匹配使用
(2)Oracle修改有數(shù)據(jù)的數(shù)據(jù)字段類型
(3)Oracle表數(shù)據(jù)回滾語句
(4)sql篩選出記錄數(shù)大于2的記錄
(5)oracle同義詞
(6)oracle內(nèi)外連接
(7)oracle去重關(guān)鍵字
(8)Oracle不等于符號
(9)1=1和1=0
(10)創(chuàng)建表空間
(11)Oracle in關(guān)鍵字和等于符號
(12)NVL函數(shù)
(13)union
(14)exist和in
(15)遞歸查詢
(16)條件sql
(17)oracle數(shù)據(jù)庫還原
(18)重導(dǎo)dmp文件
(19)vm_concat函數(shù)
(20)replace函數(shù)
(21) instr函數(shù)

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:]]+$');
1
(2)Oracle修改有數(shù)據(jù)的數(shù)據(jù)字段類型
有些時候，我們是要修改一些數(shù)據(jù)字段的類型的，mysql是可以支持的，不過Oracle對一些有數(shù)據(jù)的數(shù)據(jù)字段是不予許修改的，這時候，可以通過下面sql進行修改，大概思路就是創(chuàng)建一個臨時的，負責(zé)轉(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;
1
2
3
4
5
6
7
8
9
(3)Oracle表數(shù)據(jù)回滾語句
【表數(shù)據(jù)恢復(fù)回N分鐘之前】

alter table 表名 enable row movement;
flashback table 表名 to timestamp systimestamp - interval 'N' minute;
1
2
3
4
(4)sql篩選出記錄數(shù)大于2的記錄
select id,count(*) from A a
where a.test like '%'
group by id
having count(*)>=2
1
2
3
4
(5)oracle同義詞
創(chuàng)建同義詞：
create public synonym tablename for user.tablename;
synonyms
1
2
3
(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，是為了避免負責(zé)的條件查詢出現(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 www.xinghenyule.com“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ù)行,相當(dāng)于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 www.dfgjpt.com/ A a where exists(select b.id from B b where a.id = b.id)
1
2
3
(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 www.fengshen157.com/‘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=當(dāng)前用戶 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 www.tygj178.com? 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

(19)vm_concat函數(shù)
函數(shù)wm_concat(列名)，該函數(shù)可以把列值以","號分隔起來,并顯示成一行

(20)replace函數(shù)
REPLACE(char, search_string [, replacement_string ] )

char : 等待替換的字符串

search_string : 搜索需要替換的字符串

replacement_string : 替換字符串

如果replacement_string缺省或者為null,那么所有char中出現(xiàn)的search_string 都將被移除

如果search_string為null,那么結(jié)果就是char
例子：過濾某個字段的換行符

select replace(a, chr(10)) from tableA
1
(21) instr函數(shù)
格式一：instr( string1, string2 ) / instr(源字符串, 目標字符串)

格式二：instr( string1, string2 [, start_position [, nth_appearance ] ] ) / instr(源字符串, 目標字符串, 起始位置, 匹配序號)

解析：string2 的值要在string1中查找，是從start_position給出的數(shù)值（即：位置）開始在string1檢索，檢索第nth_appearance（幾）次出現(xiàn)string2。

例子：查詢某個數(shù)據(jù)字段是否包含換行符

select * from tableA where instr(a,chr(10))>0

轉(zhuǎn)載于:https://www.cnblogs.com/qwangxiao/p/9882526.html

總結(jié)

以上是生活随笔為你收集整理的Oracle学习笔记整理手册的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。