亚洲在线久爱草,狠狠天天香蕉网,天天搞日日干久草,伊人亚洲日本欧美

代碼一：

1

2

3

4

? ?

select?c.ci_id,?wm_concat(s.stu_name)?stu_ids

from?pm_ci?c,?pm_stu?s

where?instr(c.stu_ids,?s.stu_id)?>?0

group?by?c.ci_id;

? ?

代碼實現結果如下：

發現和視頻結果不一樣，沒有按序號順序輸入，可能是因為wm_concat（）沒有排序功能？這點不清楚，請大佬指教??！

利用listagg（）代替wm_concat（）就可以輸出正確結果了。

代碼二：

1

2

3

4

? ?

select?c.ci_id,?listagg(s.stu_name,?',')?within?group?(order?by?s.stu_id)?stu_ids

from?pm_ci?c,?pm_stu?s

where?instr(c.stu_ids,?s.stu_id)?>?0

group?by?c.ci_id;

? ?--------------------------------------------------------------------------

select cstu.ci_id, wm_concat(stu.stu_name) stu_name
?from (select c.ci_id, stu.stu_name
? ? ? ? ?from PM_CI c, PM_STU stu
? ? ? ? where instr(c.stu_ids, stu.stu_id) != 0) cstu
group by cstu.ci_id

結果如下：

練習題：

要求查詢一下的結果：

1、先創建表及初始化數據

?①創建及初始化學生表PM_STU

create table PM_STU(STU_ID? ? ? ? ? ?VARCHAR2(20) NOT NULL,

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? STU_NAME? ? ? VARCHAR2(20));? ? ? ? ? ? ? ??

INSERT INTO? PM_STU VALUES(1,'張三');

INSERT INTO? PM_STU VALUES(2,'李四');

INSERT INTO? PM_STU VALUES(3,'王五');

INSERT INTO? PM_STU VALUES(4,'趙六');?

②創建及初始化選課表PM_CI

? 先創建一個PM_CI1來轉換成PM_CI

CREATE TABLE PM_CI1(CI_ID? ? ? ? VARCHAR2(20) NOT NULL,

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? STU_IDS?? VARCHAR2(20));? ? ? ? ? ? ? ? ?

INSERT INTO PM_CI1 VALUES(1,1);

INSERT INTO PM_CI1 VALUES(1,2);

INSERT INTO PM_CI1 VALUES(1,3);

INSERT INTO PM_CI1 VALUES(1,4);

INSERT INTO PM_CI1 VALUES(2,1);

INSERT INTO PM_CI1 VALUES(2,4);

創建PM_CI，并插入數據

CREATE TABLE PM_CI(CI_ID? ? ? ?VARCHAR2(20) NOT NULL,

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?STU_IDS?? VARCHAR2(20));

?

INSERT INTO? PM_CI? SELECT CI_ID,WM_CONCAT(STU_IDS)? FROM PM_CI1 GROUP BY CI_ID;

---先做出兩張表的笛卡爾積，再根據有效的條件instr(a.STU_IDS,b.STU_ID) !=0，過濾得到每個科目對應的學生

? ? ? ?select? a.CI_ID ,b.STU_NAME

??????? from? PM_CI a,PM_STU b

??????? where? ?instr(a.STU_IDS,b.STU_ID) !=0;

? ---使用WM_CONCAT（）函數，列轉行

select? ?CI_ID,? ?WM_CONCAT(STU_NAME) SUT_NAME

from (

??????? select a.CI_ID ,b.STU_NAME

??????? from PM_CI a,PM_STU b

??????? where instr(a.STU_IDS,b.STU_ID) !=0

???????? )

group by CI_ID;

【筆記本scott賬戶創建pm_cil對應截圖上的pm_ci】

案列三：

所有員工的入職日期：

注意：列的別名是純數字的話必須將列明加上雙引號

解決方式一：分析過程，使用函數解決得到查詢結果

解決方式二：使用子查詢的方式解決，利用Oracle的一張偽表dual表

案例二：

使用explain plan for 得到Sql語句的執行計劃，查看其耗費的系統資源，查看執行計劃：select* from table (dbms_xplan.display)；得到使用相關子查詢的方式效果更好一些

案例一：分頁顯示員工信息

Oracle數據庫是行式數據庫，只能先取第一條數據才能取第二條數據

單行子查詢中的null值問題：若員工表中不存在Tom員工，主查詢的提交就永遠為假

多行子查詢中的null值問題：案列：查詢不是老板的員工。即查詢下圖的葉子節點即8條記錄【員工表中MGR列表示該員工的老板員工號 】

not in 的空值問題【a not in {10,20,null}等價于 a !=10 and a!=20 and a!=null;判斷一個值是否為空永遠為假]

員工表中MGR列的員工KING為空值

即查詢如下語句無返回結果：

select *?

from emp?

where? empno not in (select mgr from emp)

正確的使用多行子查詢中不包含空值

select *?

from emp?

where? empno not in (select mgr from emp where mgr is not null);

非法使用子查詢，使用單行操作符“=”，子查詢的返回結果就不允許是多行

多行操作符in?案例：查詢部門名稱是SALES和ACCOUNTING的員工信息

實現方式1：

select *

from emp?

where deptname in (select deptname where dname = 'SALES' or dname = 'ACCOUNTING');

實現方式2：

select? e.*

from emp e .dept d

where e.deptno = d.deptno and (d. dname = 'SALES' or? d.dname = 'ACCOUNTING');

多行操作符any 案例：查詢工資比30號部門任意一個員工高的員工信息

實現方式一：

select *

from emp

where sal? > any (select sal from emp where deptno = 30);

實現方式二：

select *

from emp

where sal? > (select? min(sal) from emp where deptno = 30);

多行操作符all 案列：查詢工資比30號部門所有員工高的員工信息

實現方式一：

select *

from emp

where sal? > all (select sal from emp where deptno = 30);

實現方式二：

select *

from emp

where sal? > (select? max(sal) from emp where deptno = 30);

單行子查詢只能使用單行操作符，多行子查詢只能使用多行操作符

子查詢:返回一條記錄屬于單行子查詢；多行子查詢：返回多條查詢記錄屬于多行子查詢。

查詢員工信息要求：職位與7566員工一樣，薪水大于7782員工新水：

select *?

from emp

where job = (select job from emp where empno=7566)? and?

? ? ? ? ? ?sal? > (select sal from emp where empno=7782)

查詢員工工資最低的信息

select *

from? emp

where sal =??(select? min(sal)? from emp );

查詢最低工資大于20號部門最低工資的部門號和部門的最頂工資

select? deptno ,min(sal)

from emp

group by deptno

having min(sal) >??(select min(sal)?

????????????????????????????????from emp?

????????????????????????????????where deptno = 20)

deptn部門號需要與主查詢的表中的員工的部門號保持一致，利用表別名的方式傳遞進來：

from 后面的子查詢

1：select *? from? (selcet empno,ename,sal from emp);

2：select *? from? (selcet empno,ename,sal,sal*12 年薪 from emp);

不可以使用子查詢的語句group by:

即下述語句是錯誤的

可以使用子查詢的位置：select，where,having,from

select 后面的子查詢語句必須是單行子查詢語句

select empno,ename,sal,(select job from emp where empno=7839) 第四列 from emp;

having子查詢語句

from子查詢語句

自查詢需要注意的問題

子查詢：解決不能一步求解的查詢

實例：查詢比SCOTT的工資高的員工

select *

from emp

where sal > (select sal

???????????????????? from emp

???????????????????? where ename ='scott');

自連接存在的問題：不適合操作大表，【自連接產生的是笛卡爾集】

解決辦法：層次查詢【本質上是單表查詢】

層次查詢的原理

from emp?

connect by 上一層的員工號=老板號? ?——》等價于如下所示的語句：

????層次查詢必須給出從哪一個節點開始遍歷：

select empno ,ename,sal,mgr

from emp?

connect by prior empno=mgr

start with empno=7566

????若從根節點開始遍歷，可以遍歷整個樹，只有根節點沒有老板號，可以這樣寫查詢語句

select empno ,ename,sal,mgr

from emp?

connect by prior empno=mgr

start with empno is null

? ? 層次查詢有偽列level，必須查詢該列才能顯示出來

select?level, empno ,ename,sal,mgr

from emp?

connect by prior empno=mgr

start with empno is null

order by 1