Oracle學習筆記(八)

標籤：varchar   排序   括弧   順序   學生   acl   order by   含義   執行計畫   

十一、子查詢
1、子查詢概述
學習子查詢的原因
案例：查詢工資比SCOTT高的員工資訊
思路：1、scott的工資
select sal from emp where ename=‘SCOTT‘;
2、比3000高的員工資訊
select * from emp where sal>3000;
子查詢方式:
select * from emp where sal> (select sal from emp where ename=‘SCOTT‘);

2、子查詢的使用
子查詢注意的10個問題
(a)子查詢文法中的小括弧

(b)子查詢的書寫風格
格式:
select *
from emp
where sal> (select sal
from emp
where ename=‘SCOTT‘);
(c)可以使用子查詢的位置:where,select,having,from
select案例
單行子查詢,只返回一條記錄
案例:
select empno,ename,sal,(select job from emp where empno=7830) 第四列 from emp;

多行子查詢,返回多條記錄

having案例:
10號部門的平均工資:
select deptno,avg(sal) from emp group by deptno having avg(sal)>(select max(sal) from emp where deptno=30);

from案例:
select * from(select empno,ename,sal from emp);

(d)不可以使用子查詢的位置:group by
不允許的情況:
select avg(sal) from emp group by (select deptno from emp);
(e)強調:from後面的子查詢
(1)查詢員工資訊:員工號,姓名,月薪
select * from (select empno as 員工號,ename as 姓名,sal as 月薪 from emp);
(2)查詢員工資訊:員工號,姓名,月薪,年薪
select * from (select empno as 員工號,ename as 姓名,sal as 月薪,sal*12 as 年薪 from emp);

(f)主查詢和子查詢可以不是同一張表
案例:
查詢部門名稱是SALES的員工資訊
select * from emp where deptno=(select deptno from dept where dname=‘SALES‘);

select e.* from emp e,dept d where e.deptno=d.deptno and d.dname=‘SALES‘;
(g)一般不在子查詢中,使用排序,但在Top-N分析問題中,必須對子查詢排序
案例:
找到員工表中工資最高的前三名,如下格式:
ROWNUM EMPNO ENAME SAL
1 7839 KING 5000
2 7788 SCOTT 3000
3 7902 FORD 3000
---------rownum 行號 偽劣
select rownum,empno,ename,sal from emp where rownum<=3 order by sal desc;

行號需要注意的兩個問題:
行號永遠按照預設的順序產生
行號只能使用<,<=;不能使用>,>=

(h)一般先執行子查詢,再執行主查詢,但相互關聯的子查詢例外
案例:
找到員工表中薪水大於本部門平均薪水的員工
select empno,ename,sal,(select avg(nvl(sal,0)) from emp where deptno=e.deptno) as 平均薪水
from emp e
where sal>(select avg(nvl(sal,0)) from emp where deptno=e.deptno);
3、子查詢的類型
(i)單行子查詢只能使用單行操作符,多行子查詢只能使用多行操作符
單行操作符
操作符 含義
= equal to
> greater than
>= greater than or equal to
< less than
<= less than or equal to
<> Not equal to

多行操作符
操作符 含義
IN 等於列表中的任何一個
ANY 和子查詢返回的任意一個值比較
ALL 和子查詢返回的所有值比較

單行子查詢:
案例:
查詢員工資訊,要求:
A、職位與7566員工一樣
B、薪水大於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);

多行子查詢:
多行操作符:in
案例:
查詢部門名稱是SALES和ACCOUNTING的員工資訊
select * from emp where deptno in (select deptno from dept where dname=‘SALES‘ or dname=‘ACCOUNTING‘);

select e.* from emp e, dept d where e.deptno=d.deptno and (d.dname=‘SALES‘ or 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);

(j)注意:子查詢中有null問題
單行:
子查詢不返回任何行(錯誤的例子)
select ename,job from emp where job (select job from emp where ename=‘Tom‘);
案例:
查詢不是老闆的員工(錯誤)
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);
4、綜合案例
目標:
(1)靈活運用select語句解決實際的問題
(2)通過四個具體的樣本,示範如何編寫高效的查詢語句
樣本一:
分頁查詢顯示員工資訊:顯示員工號,姓名,月薪
要求:(1)每頁顯示四條記錄
(2)顯示第二頁的員工
(3)按照月薪降序排列
注意:rownum只能使用<,<=,不能使用>,>=
Oracle 通過拼接子查詢方式實現分頁操作
select r,empno,ename,sal
from (select rownum r,empno,ename,sal
from (select rownum,empno,ename,sal from emp order by sal desc) e1
where rownum<=8) e2
where r>=5;
查看偽列的行號
select rownum,r,empno,ename,sal
from (select rownum r,empno,ename,sal
from (select rownum,empno,ename,sal from emp order by sal desc) e1
where rownum<=8) e2
where r>=5;

樣本二:
找到員工表中薪水大於本部門平均薪水的員工.
方法一:
select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) as 平均薪水
from emp e
where sal>(select avg(sal) from emp where deptno=e.deptno);
方法二:
select e.empno,e.ename,e.sal, d.avgsal as 平均薪水
from emp e,(select deptno ,avg(sal) avgsal from emp group by deptno) d
where e.deptno=d.deptno and e.sal>d.avgsal;

執行計畫設定:
explain plan for
select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) as 平均薪水
from emp e
where sal>(select avg(sal) from emp where deptno=e.deptno);
查看執行計畫:
select * from table(dbms_xplan.display);

執行計畫設定:
explain plan for
select e.empno,e.ename,e.sal, d.avgsal as 平均薪水
from emp e,(select deptno ,avg(sal) avgsal from emp group by deptno) d
where e.deptno=d.deptno and e.sal>d.avgsal;
查看執行計畫:
select * from table(dbms_xplan.display);

結論:相互關聯的子查詢更加的快捷

樣本三:
按部門統計員工人數,按照如下格式輸出:
Total 1980 1981 1982 1987
======== ======= ========== ======== =========
14 1 10 1 2

注意:員工的入職年份已知

方法一(函數方式):
select count(*) Total,
sum(decode(to_char(hiredate,‘YYYY‘),‘1980‘,1,0)) "1980",
sum(decode(to_char(hiredate,‘YYYY‘),‘1981‘,1,0)) "1981",
sum(decode(to_char(hiredate,‘YYYY‘),‘1982‘,1,0)) "1982",
sum(decode(to_char(hiredate,‘YYYY‘),‘1987‘,1,0)) "1987"
from emp;

方法二(子查詢方式):
select
(select count(*) from emp) Total,
(select count(*) from emp where to_char(hiredate,‘yyyy‘)=‘1980‘) "1980",
(select count(*) from emp where to_char(hiredate,‘yyyy‘)=‘1981‘) "1981",
(select count(*) from emp where to_char(hiredate,‘yyyy‘)=‘1982‘) "1982",
(select count(*) from emp where to_char(hiredate,‘yyyy‘)=‘1987‘) "1987"
from dual;

樣本四:
============選課表===============
create table pm_ci(
CI_ID varchar2(20) not null,
STU_IDS varchar2(100)
);
insert into pm_ci values(‘1‘,‘1,2,3,4‘);
insert into pm_ci values(‘2‘,‘1,4‘);

==========學生表========
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‘,‘趙六‘);

要求查詢以下結果:
CI_ID STU_NAME
==================== ===================
1 張三,李四,王五,趙六
2 張三,趙六

提示:
1、需要進行兩個表的串連查詢，為兩個表都取別名
2、使用instr(a,b)函數,該函數的含義為:如果字串b在字串a的裡面,則返回的是b在a中的位置,即傳回值大於0.
3、需要用到分組查詢
4、使用wm_concat(cols)函數對學生姓名用逗號進行拼接.

解法:
select ci_id,wm_concat(stu_name) stu_name from (select ci_id,stu_name from pm_ci c,pm_stu s where instr(c.stu_ids,s.stu_id)>0) t group by ci_id;

 

Oracle學習筆記(八)