己回憶當初開始學oracle時用到的
oracle初學者常見SQL問題
1.產生數字序列
使用connect by子句產生數字序列
10g
SQL> select level rn from dual connect by level <=10;
LEVEL
----------
1
2
3
4
5
6
7
8
9
10
9i：select * from (select level rn from dual connect by level <=10);
level是表示連線次數的1個偽列，也可以用行號偽列rownum代替
select * from (select rownum rn from dual connect by rownum <=10);
因為dual表只有1行，還有特殊性，即使你插入了資料，它的行數也不變
2.分等級查詢
列出一個排的各個人的等級關係
with army as
(select '排長'id ,'' mgrid from dual union all
select '1班長'id ,'排長' mgrid from dual union all
select '2班長'id ,'排長' mgrid from dual union all
select '1兵'id ,'1班長' mgrid from dual union all
select '2兵'id ,'2班長' mgrid from dual)
select id,level,SYS_CONNECT_BY_PATH(id,'/')path
from army
connect by prior id=mgrid; --id的上級id是mgrid，“--”表示註釋從它開始到本行結束
ID LEVEL PATH
----- ---------- --------------------
1兵 1 /1兵
2兵 1 /2兵
1班長 1 /1班長
1兵 2 /1班長/1兵
2班長 1 /2班長
2兵 2 /2班長/2兵
排長 1 /排長
1班長 2 /排長/1班長
1兵 3 /排長/1班長/1兵
2班長 2 /排長/2班長
2兵 3 /排長/2班長/2兵
SQL> with army as
2 (select '排長'id ,'' mgrid from dual union all
3 select '1班長'id ,'排長' mgrid from dual union all
4 select '2班長'id ,'排長' mgrid from dual union all
5 select '1兵'id ,'1班長' mgrid from dual union all
6 select '2兵'id ,'2班長' mgrid from dual)
7 select id,level,SYS_CONNECT_BY_PATH(id,'/')path
8 from army where level=3 --where 子句中的level是對連線的結果進行過濾
9 connect by NOCYCLE prior id=mgrid;
ID LEVEL PATH
----- ---------- ----------------------------------------
1兵 3 /排長/1班長/1兵
2兵 3 /排長/2班長/2兵
SQL> 8
8* from army where level=3
SQL> c/3/2
8* from army where level=2
SQL> /
ID LEVEL PATH
----- ---------- ----------------------------------------
1兵 2 /1班長/1兵
2兵 2 /2班長/2兵
1班長 2 /排長/1班長
2班長 2 /排長/2班長
SQL> with army as
2 (select '排長'id ,'' mgrid from dual union all
3 select '1班長'id ,'排長' mgrid from dual union all
4 select '2班長'id ,'排長' mgrid from dual union all
5 select '1兵'id ,'1班長' mgrid from dual union all
6 select '2兵'id ,'2班長' mgrid from dual)
7 select id,level,SYS_CONNECT_BY_PATH(id,'/')path
8 from army where instr(id,'長')>0 --where 限制輸出連線到的行，必須是"長"
9 start with id='1兵'
10 connect by level=2; --connect by 子句中的level是對連線的次數進行限制
ID LEVEL PATH
----- ---------- ----------------------------------------
排長 2 /1兵/排長
1班長 2 /1兵/1班長
2班長 2 /1兵/2班長
Elapsed: 00:00:00.06
SQL> 10
10* connect by level=2
SQL> c/2/3
10* connect by level=3 --因為沒有第2層，就沒有辦法連線3層
SQL> /
no rows selected
Elapsed: 00:00:00.04
SQL> with army as
2 (select '排長'id ,'' mgrid from dual union all
3 select '1班長'id ,'排長' mgrid from dual union all
4 select '2班長'id ,'排長' mgrid from dual union all
5 select '1兵'id ,'1班長' mgrid from dual union all
6 select '2兵'id ,'2班長' mgrid from dual)
7 select id,level,SYS_CONNECT_BY_PATH(id,'/')path
8 from army
9 start with id='1兵'
10 connect by level=3; --沒有進行連線，也沒有限制輸出，輸出start with
ID LEVEL PATH
----- ---------- ----------------------------------------
1兵 1 /1兵
Elapsed: 00:00:00.03
SQL> 10
10* connect by level=3
SQL> c/3/1
10* connect by level=1
SQL> /
ID LEVEL PATH
----- ---------- ----------------------------------------
1兵 1 /1兵
Elapsed: 00:00:00.06
SQL> 10
10* connect by level=1
SQL> c/1/2
10* connect by level=2 --只要執行了連線，就有了2層
SQL> /
ID LEVEL PATH
----- ---------- ----------------------------------------
1兵 1 /1兵 --沒有限定where子句，原始行總是被輸出
排長 2 /1兵/排長
1班長 2 /1兵/1班長
2班長 2 /1兵/2班長
1兵 2 /1兵/1兵
2兵 2 /1兵/2兵
其中NOCYCLE表示一行不與本身迴圈連線，是10g新增的關鍵字，必須和PRIOR連用才有效
SQL> with army as
2 (select '排長'id ,'' mgrid from dual union all
3 select '1班長'id ,'排長' mgrid from dual union all
4 select '2班長'id ,'排長' mgrid from dual union all
5 select '1兵'id ,'1班長' mgrid from dual union all
6 select '2兵'id ,'2班長' mgrid from dual)
7 select id,level,SYS_CONNECT_BY_PATH(id,'/')path
8 from army
9 start with id='1兵'
10 connect by prior substr(id,2)=substr(id,2);
ERROR:
ORA-01436: CONNECT BY loop in user data
no rows selected
Elapsed: 00:00:00.07
SQL> c/p/nocycle p
10* connect by nocycle prior substr(id,2)=substr(id,2) --沒有寫level<=?,預設level=原始記錄總數，但沒有連線成功，
SQL> /
ID LEVEL PATH
----- ---------- ----------------------------------------
1兵 1 /1兵
Elapsed: 00:00:00.04
SQL> 9
9* start with id='1兵'
SQL> del --不加start with限制，也沒有寫level<=?,預設level=原始記錄總數，但沒有連線成功，輸出原始記錄
SQL> /
ID LEVEL PATH
----- ---------- ----------------------------------------
1班長 1 /1班長
2班長 1 /2班長
1兵 1 /1兵
2兵 1 /2兵
排長 1 /排長
Elapsed: 00:00:00.04
SQL> with army as
2 (select '排長'id ,'' mgrid from dual union all
3 select '1班長'id ,'排長' mgrid from dual union all
4 select '2班長'id ,'排長' mgrid from dual union all
5 select '1兵'id ,'1班長' mgrid from dual union all
6 select '2兵'id ,'2班長' mgrid from dual)
7 select id,level,SYS_CONNECT_BY_PATH(id,'/')path
8 from army
9 connect by nocycle prior id like '_'||substr(id,2) and level<=2; --兵之間，班長之間連線，前提是數字字首為1位
ID LEVEL PATH
----- ---------- ----------------------------------------
排長 1 /排長
1班長 1 /1班長
2班長 2 /1班長/2班長
2班長 1 /2班長
1班長 2 /2班長/1班長
1兵 1 /1兵
2兵 2 /1兵/2兵
2兵 1 /2兵
1兵 2 /2兵/1兵
connect by 子句中的level,一般用<=,如下例所示
3.求記錄間的排列組合
SQL> with a as(select level l from dual connect by level<=3)
2 select l,level,SYS_CONNECT_BY_PATH(l,' ')path
3 from a where level=2
4 connect by prior l<>l and level<=2; --兩兩連線可以用prior l<>l 保證不迴圈
L LEVEL PATH
---------- ---------- ----------------------------------------
2 2 1 2
3 2 1 3
1 2 2 1
3 2 2 3
1 2 3 1
2 2 3 2
6 rows selected.
Elapsed: 00:00:00.04
SQL> with a as(select level l from dual connect by level<=3)
2 select l,level,SYS_CONNECT_BY_PATH(l,' ')path
3 from a where level=3
4 connect by prior l<>l and level<=3;
ERROR:
ORA-01436: CONNECT BY loop in user data
no rows selected
Elapsed: 00:00:00.04
SQL> with a as(select level l from dual connect by level<=3)
2 select l,level,SYS_CONNECT_BY_PATH(l,' ')path
3 from a where level=3
4 connect by nocycle prior l<>l and level<=3; --超過2個連線可以用nocycle保證不迴圈
L LEVEL PATH
---------- ---------- ----------------------------------------
3 3 1 2 3
2 3 1 3 2
3 3 2 1 3
1 3 2 3 1
2 3 3 1 2
1 3 3 2 1
SQL> with a as(select level l from dual connect by level<=3)
2 select l,level,SYS_CONNECT_BY_PATH(l,' ')path
3 from a where level=3
4 connect by nocycle level<=3 --沒有prior的nocycle沒有作用
SQL> /
L LEVEL PATH
---------- ---------- ----------------------------------------
1 3 1 1 1
2 3 1 1 2
3 3 1 1 3
1 3 1 2 1
2 3 1 2 2
3 3 1 2 3
1 3 1 3 1
2 3 1 3 2
3 3 1 3 3
1 3 2 1 1
2 3 2 1 2
3 3 2 1 3
1 3 2 2 1
2 3 2 2 2
3 3 2 2 3
1 3 2 3 1
2 3 2 3 2
3 3 2 3 3
1 3 3 1 1
2 3 3 1 2
3 3 3 1 3
1 3 3 2 1
2 3 3 2 2
3 3 3 2 3
1 3 3 3 1
2 3 3 3 2
3 3 3 3 3
使用connect by子句產生數字序列
10g
SQL> select level rn from dual connect by level <=10;
LEVEL
----------
1
2
3
4
5
6
7
8
9
10
9i：select * from (select level rn from dual connect by level <=10);
level是表示連線次數的1個偽列，也可以用行號偽列rownum代替
select * from (select rownum rn from dual connect by rownum <=10);
因為dual表只有1行，還有特殊性，即使你插入了資料，它的行數也不變
2.分等級查詢
列出一個排的各個人的等級關係
with army as
(select '排長'id ,'' mgrid from dual union all
select '1班長'id ,'排長' mgrid from dual union all
select '2班長'id ,'排長' mgrid from dual union all
select '1兵'id ,'1班長' mgrid from dual union all
select '2兵'id ,'2班長' mgrid from dual)
select id,level,SYS_CONNECT_BY_PATH(id,'/')path
from army
connect by prior id=mgrid; --id的上級id是mgrid，“--”表示註釋從它開始到本行結束
ID LEVEL PATH
----- ---------- --------------------
1兵 1 /1兵
2兵 1 /2兵
1班長 1 /1班長
1兵 2 /1班長/1兵
2班長 1 /2班長
2兵 2 /2班長/2兵
排長 1 /排長
1班長 2 /排長/1班長
1兵 3 /排長/1班長/1兵
2班長 2 /排長/2班長
2兵 3 /排長/2班長/2兵
SQL> with army as
2 (select '排長'id ,'' mgrid from dual union all
3 select '1班長'id ,'排長' mgrid from dual union all
4 select '2班長'id ,'排長' mgrid from dual union all
5 select '1兵'id ,'1班長' mgrid from dual union all
6 select '2兵'id ,'2班長' mgrid from dual)
7 select id,level,SYS_CONNECT_BY_PATH(id,'/')path
8 from army where level=3 --where 子句中的level是對連線的結果進行過濾
9 connect by NOCYCLE prior id=mgrid;
ID LEVEL PATH
----- ---------- ----------------------------------------
1兵 3 /排長/1班長/1兵
2兵 3 /排長/2班長/2兵
SQL> 8
8* from army where level=3
SQL> c/3/2
8* from army where level=2
SQL> /
ID LEVEL PATH
----- ---------- ----------------------------------------
1兵 2 /1班長/1兵
2兵 2 /2班長/2兵
1班長 2 /排長/1班長
2班長 2 /排長/2班長
SQL> with army as
2 (select '排長'id ,'' mgrid from dual union all
3 select '1班長'id ,'排長' mgrid from dual union all
4 select '2班長'id ,'排長' mgrid from dual union all
5 select '1兵'id ,'1班長' mgrid from dual union all
6 select '2兵'id ,'2班長' mgrid from dual)
7 select id,level,SYS_CONNECT_BY_PATH(id,'/')path
8 from army where instr(id,'長')>0 --where 限制輸出連線到的行，必須是"長"
9 start with id='1兵'
10 connect by level=2; --connect by 子句中的level是對連線的次數進行限制
ID LEVEL PATH
----- ---------- ----------------------------------------
排長 2 /1兵/排長
1班長 2 /1兵/1班長
2班長 2 /1兵/2班長
Elapsed: 00:00:00.06
SQL> 10
10* connect by level=2
SQL> c/2/3
10* connect by level=3 --因為沒有第2層，就沒有辦法連線3層
SQL> /
no rows selected
Elapsed: 00:00:00.04
SQL> with army as
2 (select '排長'id ,'' mgrid from dual union all
3 select '1班長'id ,'排長' mgrid from dual union all
4 select '2班長'id ,'排長' mgrid from dual union all
5 select '1兵'id ,'1班長' mgrid from dual union all
6 select '2兵'id ,'2班長' mgrid from dual)
7 select id,level,SYS_CONNECT_BY_PATH(id,'/')path
8 from army
9 start with id='1兵'
10 connect by level=3; --沒有進行連線，也沒有限制輸出，輸出start with
ID LEVEL PATH
----- ---------- ----------------------------------------
1兵 1 /1兵
Elapsed: 00:00:00.03
SQL> 10
10* connect by level=3
SQL> c/3/1
10* connect by level=1
SQL> /
ID LEVEL PATH
----- ---------- ----------------------------------------
1兵 1 /1兵
Elapsed: 00:00:00.06
SQL> 10
10* connect by level=1
SQL> c/1/2
10* connect by level=2 --只要執行了連線，就有了2層
SQL> /
ID LEVEL PATH
----- ---------- ----------------------------------------
1兵 1 /1兵 --沒有限定where子句，原始行總是被輸出
排長 2 /1兵/排長
1班長 2 /1兵/1班長
2班長 2 /1兵/2班長
1兵 2 /1兵/1兵
2兵 2 /1兵/2兵
其中NOCYCLE表示一行不與本身迴圈連線，是10g新增的關鍵字，必須和PRIOR連用才有效
SQL> with army as
2 (select '排長'id ,'' mgrid from dual union all
3 select '1班長'id ,'排長' mgrid from dual union all
4 select '2班長'id ,'排長' mgrid from dual union all
5 select '1兵'id ,'1班長' mgrid from dual union all
6 select '2兵'id ,'2班長' mgrid from dual)
7 select id,level,SYS_CONNECT_BY_PATH(id,'/')path
8 from army
9 start with id='1兵'
10 connect by prior substr(id,2)=substr(id,2);
ERROR:
ORA-01436: CONNECT BY loop in user data
no rows selected
Elapsed: 00:00:00.07
SQL> c/p/nocycle p
10* connect by nocycle prior substr(id,2)=substr(id,2) --沒有寫level<=?,預設level=原始記錄總數，但沒有連線成功，
SQL> /
ID LEVEL PATH
----- ---------- ----------------------------------------
1兵 1 /1兵
Elapsed: 00:00:00.04
SQL> 9
9* start with id='1兵'
SQL> del --不加start with限制，也沒有寫level<=?,預設level=原始記錄總數，但沒有連線成功，輸出原始記錄
SQL> /
ID LEVEL PATH
----- ---------- ----------------------------------------
1班長 1 /1班長
2班長 1 /2班長
1兵 1 /1兵
2兵 1 /2兵
排長 1 /排長
Elapsed: 00:00:00.04
SQL> with army as
2 (select '排長'id ,'' mgrid from dual union all
3 select '1班長'id ,'排長' mgrid from dual union all
4 select '2班長'id ,'排長' mgrid from dual union all
5 select '1兵'id ,'1班長' mgrid from dual union all
6 select '2兵'id ,'2班長' mgrid from dual)
7 select id,level,SYS_CONNECT_BY_PATH(id,'/')path
8 from army
9 connect by nocycle prior id like '_'||substr(id,2) and level<=2; --兵之間，班長之間連線，前提是數字字首為1位
ID LEVEL PATH
----- ---------- ----------------------------------------
排長 1 /排長
1班長 1 /1班長
2班長 2 /1班長/2班長
2班長 1 /2班長
1班長 2 /2班長/1班長
1兵 1 /1兵
2兵 2 /1兵/2兵
2兵 1 /2兵
1兵 2 /2兵/1兵
connect by 子句中的level,一般用<=,如下例所示
3.求記錄間的排列組合
SQL> with a as(select level l from dual connect by level<=3)
2 select l,level,SYS_CONNECT_BY_PATH(l,' ')path
3 from a where level=2
4 connect by prior l<>l and level<=2; --兩兩連線可以用prior l<>l 保證不迴圈
L LEVEL PATH
---------- ---------- ----------------------------------------
2 2 1 2
3 2 1 3
1 2 2 1
3 2 2 3
1 2 3 1
2 2 3 2
6 rows selected.
Elapsed: 00:00:00.04
SQL> with a as(select level l from dual connect by level<=3)
2 select l,level,SYS_CONNECT_BY_PATH(l,' ')path
3 from a where level=3
4 connect by prior l<>l and level<=3;
ERROR:
ORA-01436: CONNECT BY loop in user data
no rows selected
Elapsed: 00:00:00.04
SQL> with a as(select level l from dual connect by level<=3)
2 select l,level,SYS_CONNECT_BY_PATH(l,' ')path
3 from a where level=3
4 connect by nocycle prior l<>l and level<=3; --超過2個連線可以用nocycle保證不迴圈
L LEVEL PATH
---------- ---------- ----------------------------------------
3 3 1 2 3
2 3 1 3 2
3 3 2 1 3
1 3 2 3 1
2 3 3 1 2
1 3 3 2 1
SQL> with a as(select level l from dual connect by level<=3)
2 select l,level,SYS_CONNECT_BY_PATH(l,' ')path
3 from a where level=3
4 connect by nocycle level<=3 --沒有prior的nocycle沒有作用
SQL> /
L LEVEL PATH
---------- ---------- ----------------------------------------
1 3 1 1 1
2 3 1 1 2
3 3 1 1 3
1 3 1 2 1
2 3 1 2 2
3 3 1 2 3
1 3 1 3 1
2 3 1 3 2
3 3 1 3 3
1 3 2 1 1
2 3 2 1 2
3 3 2 1 3
1 3 2 2 1
2 3 2 2 2
3 3 2 2 3
1 3 2 3 1
2 3 2 3 2
3 3 2 3 3
1 3 3 1 1
2 3 3 1 2
3 3 3 1 3
1 3 3 2 1
2 3 3 2 2
3 3 3 2 3
1 3 3 3 1
2 3 3 3 2
3 3 3 3 3
己回憶當初開始學oracle時用到的
sqlplus常用命令
格式化
每行長度132字元 每頁最多50000行
set lines 132 pages 50000
對path列名/列別名 設定為40字元寬
col path format a40
反饋執行時間
set timi on
列出當前緩衝區最後1個SQL語句
li
列出第8行
8
把第8行的a替換成b,注意只替換第1個a,如果後面還有a要替換成b,要多次執行
c/a/b
sqlplus常用命令
格式化
每行長度132字元 每頁最多50000行
set lines 132 pages 50000
對path列名/列別名 設定為40字元寬
col path format a40
反饋執行時間
set timi on
列出當前緩衝區最後1個SQL語句
li
列出第8行
8
把第8行的a替換成b,注意只替換第1個a,如果後面還有a要替換成b,要多次執行
c/a/b
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/219982/viewspace-617715/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 《從0開始學Elasticsearch》—初識ElasticsearchElasticsearch
- PHP獲取當前季度的開始時間和結束時間PHP
- 當我們開始用Node重寫以前的專案時
- 初學Web前端會用到開發工具（附官網下載地址）Web前端
- [譯文] 當你開始學習程式設計時，你最希望知道什麼？程式設計
- oracle開發常用到的函式Oracle函式
- 明天開始系統學習oracleOracle
- 剛開始學習nlp時遇到的問題
- 當初自學C++時的筆記記錄C++筆記
- 自述：javaWeb剛學時所需要的用到的jsJavaWebJS
- 當AI開始保護她AI
- 從零開始再學 JavaScript 定時器JavaScript定時器
- 當出現記憶體洩漏的時候記憶體
- 初學Oracle的一點心得Oracle
- 說說我當初是如何學Linux的Linux
- 求教：各位高人關於初學jive,應從哪裡開始？
- 查詢時若時間為空，開始時間取今天的零點，結束時間取當前時間
- 看到說面試阿里的問題，回憶回憶呀面試阿里
- oracle 安裝要用到的記憶體相關引數詳解Oracle記憶體
- 當獲得版號是一款遊戲成功的關鍵時，便是悲哀的開始遊戲
- 當使用 position 屬性時，請始終設定 !DOCTYPE 宣告：當使用 float 屬性時，請始終設定 !DOCTYPE 宣告：
- 排序sort area 記憶體不足會用到臨時表空間排序記憶體
- 當AI開始擁有“潛意識”AI
- 開啟oracle的flashback閃回功能Oracle
- 開始我的學習之旅。。。。
- 如果當初學習程式設計時能有人給我這些忠告該多好程式設計
- 回憶Emacs 和Vim 的學習之路 – v0.0.9Mac
- oracle 閃回基於時間的恢復Oracle
- 如何讓手遊記憶體佔用更小？從記憶體消耗iOS實時統計開始記憶體iOS
- 當騰訊開始推出自己的女性戀愛遊戲遊戲
- 當AI開始“踢髒球”，你還敢信任強化學習嗎？AI強化學習
- 程式設計零基礎應當如何開始學習 Python？程式設計Python
- 《學習Oracle從這裡開始》之常用SQL篇OracleSQL
- 從0開始學遊戲開發-蔡能-極客時間遊戲開發
- 寫給大資料初學者，從零開始學習大資料開發的完整路線大資料
- 與京東分手後 優衣庫要憑一己之力搞當日達？
- c++ 獲取當前時間周初凌晨時間戳（獲取當前時間週一凌晨時間戳）C++時間戳
- 開始學習啦