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博客 ” ，链接：https://blog.itpub.net/219982/viewspace-617715/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
IT技术员职业生涯的红色警报
请登录后发表评论
登录
全部评论
