Oracle!你必須要知道的Knowledge points

子查詢

什么是子查詢

當查詢中的限制條件需要另一個查詢提供時，我們可以把兩個查詢語句嵌套起來，提供條件的查詢語句作為子查詢。
子查詢，也叫內部查詢，先于主查詢執行，子查詢的結果被用于主查詢。
子查詢分為單行子查詢、多行子查詢和多列子查詢，單行子查詢作為判斷條件時用單行運算符，多行子查詢和多列子查詢作為判斷條件時用多行運算符。
單行運算符：>、=、>=、<、<>、<=
多行運算符：in、all、any

子查詢可以嵌套在哪里

Where子句：作為非分組函數篩選判斷的條件
From子句：將子查詢的查詢結果作為一張表來使用
Having子句：作為分組函數篩選判斷的條件

子查詢使用規范

子查詢需要用括號括起來
當子查詢放在from子句中時，是將查詢到的結果作為一張表來使用，需給別名
單行子查詢用單行運算符，多行子查詢用多行運算符
子查詢放在運算符右邊

單行子查詢

單行子查詢返回一行一列，使用單行運算符進行連接

--顯示和雇員7369從事相同工作并且工資大于雇員7876的雇員的姓名和工作。
select ename,job
from emp 
where job = (select job from emp where empno =7369)
and sal > (select sal from emp where empno = 7876);

--查詢工資最低的員工姓名，崗位及工資(子查詢中使用了分組函數)
select ename,job,sal
from emp
where sal = (select min(sal) from emp)

--查詢部門最低工資比20部門最低工資高的部門編號及最低工資(子查詢嵌套在having子句)
select  deptno,min(sal)
from  emp
group by deptno
having min(sal) > (select min(sal) from emp where deptno = 20 );

多行子查詢

多行子查詢返回一行或多行記錄，使用多行運算符進行連接

--查詢是經理的員工姓名、工資
select ename,sal
from emp
where empno in (select mgr from emp)

ANY的使用

表示和子查詢的任意一行結果進行比較，有一個滿足條件即可。

any：大于子查詢結果的任意一個，即大于最小值即可。
<any：小于子查詢結果的任意一個，即小于最大值即可。
=any：等于子查詢結果的任意一個，即等一任意一個即可，相當于in。

--查詢部門編號不為10，且工資比10部門任意一名員工工資高的員工編號，姓名，職位，工資。
select empno,ename,job,sal
from emp
where deptno <> 10 and sal > any(select sal from emp where deptno = 10);

ALL的使用

表示和子查詢的所有行結果進行比較，每一行必須都滿足條件。

<all:表示小于子查詢結果集中的所有行，即小于最小值
>all:表示大于子查詢結果集中的所有行，即大于最大值。
= all :表示等于子查詢結果集中的所有行,即等于所有值。

--查詢部門編號不為10，且工資比10部門所有員工工資低的員工編號，姓名，職位，工資。
select empno,ename,job,sal
from emp
where deptno <> 10 and sal > all(select sal from emp where deptno = 10);

多列子查詢

在一個表達式內同時和子查詢的多個列進行比較，即子查詢返回的記錄含多列

--查詢和1981年入職的任意一個員工的部門和職位完全相同員工姓名、部門、職位、入職日期,不包括1981年入職員工。
select ename,deptno,job,hiredate
from emp
where (deptno,job) in (select deptno,job from emp where to_char(hiredate,'YYYY') = '1981')
and to_char(hiredate,'YYYY') <> '1981';

--查詢和1981年入職的任意一個員工的部門或職位相同員工姓名、部門、職位、入職日期,不包括1981年入職員工
select ename,deptno,job,hiredate
from emp
where job in (select job from emp where to_char(hiredate,'YYYY') = '1981')
or deptno in (select deptno from emp where to_char(hiredate,'YYYY') = '1981')
and to_char(hiredate,'YYYY') <> '1981';

子查詢中的空值

子查詢結果中含有空值對主查詢會有怎樣的影響？

--查詢不是經理的員工姓名
select ename
from emp
where empno not in (select mgr from emp);

子查詢的結果中有一條空值，這條空值導致主查詢沒有記錄返回，這是因為所有的條件和空值比較結果都是空值，因此無論什么時候只要空值
有可能成為子查詢結果集合中的一部分，就不能使用 not in 運算符。
?
如果子查詢的結果中包含空值，又想使用 not in 運算符該怎么辦？

--查詢不是經理的員工姓名
select ename
from emp
where empno not in (select nvl(mgr,0) from emp);     --使用nvl()函數去除空值影響

在from子句中使用子查詢

在from子句中使用子查詢，相當于把子查詢的查詢結果作為一張表來使用，在使用時建議加上相應的別名

--查詢比自己部門平均工資高的員工姓名，工資，部門編號，部門平均工資
select e.ename,e.sal,e.deptno,w.avgsal
from emp e,(select avg(sal) as avgsal deptno from emp group by deptno) w
where e.deptno = w.deptno and e.sal > w.avgsal;

ROWNUM

rownum是一個偽列，顧名思義，就是一個類似于表中的列，而實際并沒有存儲在表中的特殊列；
rownum的功能是在每次查詢時，返回結果集的順序號，這個順序號是在記錄輸出時才一步一步產生的，第一行顯示為1，第二行為2，以此類推。

select rownum.emp.* from emp;

關于rownum需要知道的幾點

1. rownum是在記錄輸出時才生成，且總是從1開始
2. rownum只能執行<、<=運算,不能執行>、>= 或區間運算between ... and ...
3. rownum和order by一起使用時，因為rownum在記錄輸出時生成，而order by子句在最后執行，所以當兩者一起使用時，需要注意rownum實際是已經被排了序的rownum。

TOP-N查詢

實現表中按照某個列排序，輸出最大或最小的N條記錄功能
Top-N語法

ASC：升序排序，查詢最小的N條記錄
DESC：降序排序，查詢最大的N條記錄

--查詢入職日期最早的前5名員工姓名，入職日期。
select rownum,ename,hiredate
from (select ename,hiredate from emp order by hiredate)
where rownum <= 5;

--查詢工作在CHICAGO并且入職日期最早的前2名員工姓名，入職日期
select rownum,ename,hiredate
from (select ename,hiredate from emp
                 where deptno = (select deptno from dept where loc = 'CHICAGO')
                 order by hiredate)
where rownum <= 2;

分頁查詢

利用ROWNUM的特性，可以實現數據庫端的分頁查詢
語法格式

--按照每頁顯示5條記錄，分別查詢第1頁，第2頁，第3頁信息，要求顯示員工姓名、入職日期、部門名稱。
select b.*
from (select rownum rn,ename,hiredate,dname
                 from emp,dept
                 where emp.deptno = dept.deptno and rownum <=  1*5) b
where rn > (1-1)*5;

select b.*
from (select rownum rn,ename,hiredate,dname
                 from emp,dept
                 where emp.deptno = dept.deptno and rownum <=  2*5) b
where rn > (2-1)*5;

select b.*
from (select rownum rn,ename,hiredate,dname
                 from emp,dept
                 where emp.deptno = dept.deptno and rownum <=  3*5) b
where rn > (3-1)*5;

排序后分頁

排序->rownum->select輸出最后結果

--按照每頁顯示5條記錄，分別查詢工資最高的第1頁，第2頁，第3頁信息，要求顯示員工姓名、入職日期、部門名稱、工資。
select *
from (select rownum rn,b.*
                 from (select ename,hiredate,dname,sal from emp,dept 
                           where emp.deptno = dept.deptno 
                           order by sal desc) b
                 where  rownum <= 1*5   )
where rn > (1-1) * 5;

select *
from (select rownum rn,b.*
                 from (select ename,hiredate,dname,sal from emp,dept 
                           where emp.deptno = dept.deptno 
                           order by sal desc) b
                 where  rownum <= 2*5   )
where rn > (2-1) * 5;

select *
from (select rownum rn,b.*
                 from (select ename,hiredate,dname,sal from emp,dept 
                           where emp.deptno = dept.deptno 
                           order by sal desc) b
                 where  rownum <= 3*5   )
where rn > (3-1) * 5;