己回憶當初開始學oracle時用到的

fengzj發表於2009-10-29

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

己回憶當初開始學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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/219982/viewspace-617715/，如需轉載，請註明出處，否則將追究法律責任。

《從0開始學Elasticsearch》—初識Elasticsearch
2019-02-14
Elasticsearch
PHP獲取當前季度的開始時間和結束時間
2023-04-03
PHP
當我們開始用Node重寫以前的專案時
2013-12-22
初學Web前端會用到開發工具（附官網下載地址）
2020-04-15
Web前端
[譯文] 當你開始學習程式設計時，你最希望知道什麼？
2019-04-22
程式設計
oracle開發常用到的函式
2014-08-28
Oracle函式
明天開始系統學習oracle
2010-04-11
Oracle
剛開始學習nlp時遇到的問題
2018-07-04
當初自學C++時的筆記記錄
2021-04-04
C++筆記
自述：javaWeb剛學時所需要的用到的js
2020-10-07
JavaWebJS
當AI開始保護她
2018-12-21
AI
從零開始再學 JavaScript 定時器
2019-10-14
JavaScript定時器
當出現記憶體洩漏的時候
2017-03-27
記憶體
說說我當初是如何學Linux的
2019-07-18
Linux
求教：各位高人關於初學jive,應從哪裡開始？
2003-12-28
查詢時若時間為空，開始時間取今天的零點，結束時間取當前時間
2018-09-29
初學Oracle的一點心得
2016-01-14
Oracle
看到說面試阿里的問題，回憶回憶呀
2011-11-17
面試阿里
oracle 安裝要用到的記憶體相關引數詳解
2012-04-01
Oracle記憶體
當獲得版號是一款遊戲成功的關鍵時，便是悲哀的開始
2020-01-16
遊戲
當使用 position 屬性時，請始終設定 !DOCTYPE 宣告：當使用 float 屬性時，請始終設定 !DOCTYPE 宣告：
2016-07-08
排序sort area 記憶體不足會用到臨時表空間
2008-02-28
排序記憶體
當AI開始擁有“潛意識”
2019-10-18
AI
如果當初學習程式設計時能有人給我這些忠告該多好
2013-12-06
程式設計
回憶Emacs 和Vim 的學習之路 – v0.0.9
2015-11-13
Mac
如何讓手遊記憶體佔用更小？從記憶體消耗iOS實時統計開始
2019-12-19
記憶體iOS
開始我的學習之旅。。。。
2013-04-23
開啟oracle的flashback閃回功能
2013-06-19
Oracle
oracle 閃回基於時間的恢復
2010-08-01
Oracle
當AI開始“踢髒球”，你還敢信任強化學習嗎？
2020-03-30
AI強化學習
程式設計零基礎應當如何開始學習 Python？
2021-09-09
程式設計Python
當騰訊開始推出自己的女性戀愛遊戲
2020-04-15
遊戲
與京東分手後優衣庫要憑一己之力搞當日達？
2016-07-25
從0開始學遊戲開發-蔡能-極客時間
2019-04-29
遊戲開發
寫給大資料初學者，從零開始學習大資料開發的完整路線
2019-02-23
大資料
《學習Oracle從這裡開始》之常用SQL篇
2014-12-09
OracleSQL
c++ 獲取當前時間周初凌晨時間戳（獲取當前時間週一凌晨時間戳）
2020-10-24
C++時間戳
初學 PHP 對於回撥函式的一些理解
2018-10-22
PHP函式

己回憶當初開始學oracle時用到的

相關文章