Oracle資料庫之SQL語句子查詢

/*
====================================================================================================
-- 非關聯子查詢:單行子查詢,多行子查詢
--  非關聯子查詢就是位于SELECT、UPDATE、或DELETE語句中内部的查詢語句
--子查詢(内部查詢)在執行主查詢之前執行一次 (隻執行1次)， 然後主查詢(外部查詢)會使用該子查詢的結果 周遊全部記錄
-- -- Notice:子查詢的結果有多個值，oracle會去掉重複的記錄，再将結果傳回給主查詢
=====================================================================================================
*/

-- ===========單行子查詢================================================================================
-- 子查詢隻傳回一條記錄.單行子查詢 ,用 <> , < , >, <=, >=  , = 操作

--在WHERE 子句中使用子查詢  
select ename,job from emp where empno = (select empno from emp  where mgr = 7902 );   
--分析:
	--1.先執行select empno from emp  where mgr = 7902(隻執行1次)得到查詢結果7369.
	--2. 再執行外部查詢:select ename,job from emp where empno =7369. 得到最終結果.
	
 --使用分組函數的子查詢
 select ename,job,sal from emp  where sal > (select avg(sal) from emp);
 -- from 後面的子查詢
 select ename, job from (select * from emp where empno = 7369); 
 --單行子查詢中的常見錯誤
        --子查詢的結果傳回多于一行
select empno,ename  from emp where sal =  (select sal from emp where deptno = 20); --error
		--子查詢中不能包含ORDER BY子句,任何的排序在外部查詢中完成
 select empno,ename from emp where sal > (select avg(sal) from emp order by empno); -- error
 select empno,ename from emp where sal > (select avg(sal) from emp)  order by empno; -- ok 
		--子查詢内部沒有傳回行，如下語句可以正确執行，但沒有資料傳回
 select ename,job  from emp where empno = (select empno  from emp where mgr = 8000); --error未標明行

 -- ===========多行子查詢================================================================================
 --子查詢 結果傳回多條記錄給外部查詢(同樣執行1次),外部查詢用in, any , all 處理
 -- 外部查詢用in , any , all 處理之前必須用一個  =, <>, <, >, <= , >= 操作符.
 
 --查詢每個部門中薪水最高的員工姓名
 select empno,ename,job from empwhere sal in  (select max(sal)  from emp group by deptno);
--分析:
-- 1.執行select max(sal)  from emp group by deptno ; 傳回多條記錄.
-- 2.執行select empno,ename,job from emp where sal in ( 從1中傳回的多條記錄進行一條一條的比對 ) ;
 
 --查詢每個部門中低于平均薪水的員工姓名 (any   任何一個)
 select empno,ename,job  from emp where sal < any	(select avg(sal) 	from emp	group by deptno);


 /*
====================================================================================================
-- 關聯子查詢:單行子查詢,多行子查詢
---- 子查詢中使用了主查詢中的某些字段，主查詢每掃描一行都要執行一次子查詢 

-- 關聯子查詢:(采用loop的方式進行)
-- 1.外部查詢得到一條記錄(從外部表中讀取資料) 并将其傳入到内部查詢
-- 2.内部查詢基于傳入的值執行
-- 3.内部查詢從其結果中把值傳回到外部查詢，
-- 外部查詢使用這些值來完成其處理，若符合條件，外部表的那條記錄就放入結果集中，
   -- 否則放棄(表示該記錄不符合條件)
   
-- 4. 重複1-3 .直到把outer表中的所有記錄判斷一遍
=====================================================================================================
*/
    --查詢工資高于同一部門的員工的部門号，姓名，工資
	select deptno,ename,sal from emp outer  where sal >(
				select avg(sal)from emp inner where inner.deptno = outer.deptno);
--分析:1. 取出外部outer的全部記錄一條一條的傳遞給子查詢(作為子查詢的條件)
--        2.子查詢根據外部查詢的條件執行1次子查詢
--  	3.直到外部記錄全部周遊完為止，才傳回最終的結果
-- 重點:主查詢每掃描一行都要執行一次子查詢 

 
 /*
====================================================================================================
 -- Exists:用來檢查子查詢傳回行的存在性
 -- 執行過程:
-- 1.外部查詢得到一條記錄(從外部表中讀取資料) 并将其傳入到内部查詢
-- 2.對inner表依次掃描，若根據條件存在一條記錄與 outer表中的記錄比對，立即停止掃描，
		傳回ture.立即停止掃描inner表.那麼該outer 表中的記錄放入結果集中，
		若掃描了全部記錄，沒有任何一條記錄符合比對條件，
		傳回false, outer表的記錄被過濾掉，不能出現在結果集中.
   
-- 3.重複步驟1-2.直至周遊完outer表中所有的記錄
 
=====================================================================================================
*/
          

    --查詢負責管理其它員工的員工記錄(使用exists),即是上司的員工
select empno,ename from emp outer where exists
				(select empno from emp inner where inner.mgr = outer.empno);
				
--分析:
	-- 1. 把outer表中的1行記錄的empno傳進子查詢select empno from emp inner where inner.mgr =xx中.
	-- 2.子查詢進行全部資料的周遊,檢查是否符合where條件的記錄
	-- 3.如果存在,那麼就把outer表中的該條記錄放入結果集.
	--4.不存在,outer表中的該條記錄 不放入結果集.
    -- 5.重複 1.- 4步,直到周遊完outer表中的全部記錄,生成最終的結果集
	
-- Notice:exists隻關心子查詢有沒有結果，并不需要傳回值，是以上述語句調整為(傳回常量):
 select empno,ename from emp outer where exists
				(select 1 from emp inner where inner.mgr = outer.empno);

 
 --========= not Exists 用Exists執行過程一樣,not Exists表示子查詢沒有傳回則把outer表放入結果集
  --查詢不管理其它員工的職員(not exists),查詢不是上司的員工
select empno,ename from emp outer where not exists
	(select empno from emp inner where inner.mgr = outer.empno)

 --分析:
	-- 1. 把outer表中的1行記錄的empno傳進子查詢select empno from emp inner where inner.mgr =xx中.
	-- 2.子查詢進行全部資料的周遊,檢查是否符合where條件的記錄
	-- 3.如果  不 存在,那麼就把outer表中的該條記錄放入結果集.
	--4.存在,outer表中的該條記錄 不放入結果集.
    -- 5.重複 1.- 4步,直到周遊完outer表中的全部記錄,生成最終的結果集
 
 
 --EXISTS 和NOT EXISTS 與IN 和NOT IN 的比較

 --       EXISTS與IN的不同：

  --          EXISTS隻檢查行的存在性,IN 要檢查實際值的存在性(一般情況下EXISTS的性能高于IN)

  --      NOT EXISTS 和NOT IN 

   --        當值清單中包含空值的情況下,NOT EXISTS 則傳回true,而NOT IN 則傳回false.

 
 -- update 的子查詢
 update emp set salary = (select avg(salary) from emp ) where deptno = 10;
 -- delete的子查詢
 delete from emp where salary > (select avg(salary) from emp);


筆記：group by ... having 子句的用法:

select sum(salary) from emp group by deptno having deptno > 10;
或者
select sum(salary) from emp group by deptno having sum(salary) > 5000 ;