Oracle學習第三講

標籤：

關聯查詢

笛卡爾積
指做關聯操作的每個表的每一行都和其他表的每一行組合，假設兩個表的記錄條數分別為x和y，笛卡爾積將返回x*y條記錄
例如：select count(*) from emp;
select count(*) from dept;
select emp.ename,dept.dname from emp,dept;

等值串連
串連查詢中最常見的一種，通常是在有主外部索引鍵關聯關係的表之間建立，並將串連條件設定為有關係的列，使用“=”串連相關的表
例如：查詢職員的姓名、職位以及所在部門的名字和所在城市
select e.ename,e.job,d.dname,d.loc
from emp e,dept d
where e.deptno=d.deptno;

內串連
返回所有滿足條件的記錄
例如：查詢職員的姓名、職位以及所在部門的名字和所在城市
select e.ename,e.job,d.dname,d.loc
from emp e,dept d
where e.deptno=d.deptno;
或者（如果要寫內練接，就用下面這個）
select e.ename,e.job,d.dname,d.loc
from emp e join dept d
on (e.deptno=d.deptno);

外串連
不僅返回滿足條件的記錄，還將返回不滿足條件的記錄
select table1.column,table2.column
from table1 left|right|full[outer] join tanle2
on table1.column=table2.column;

例如：查詢職員的姓名、職位以及所在部門的名字和所在城市
左外串連
select e.ename,e.job,d.dname,d.loc
from emp e left outer join dept d
on e.deptno=d.deptno;
右外串連
select e.ename,e.job,d.dname,d.loc
from emp e right outer join dept d
on e.deptno=d.deptno;
全外串連
select e.ename,e.job,d.dname,d.loc
from emp e full outer join dept d
on e.deptno=d.deptno;

自串連
是一種特殊的串連查詢，資料的來源是同一個表，也就是說關聯關係來自於單表中的多個列
表中的列參照同一個表中的其它列的情況稱作自參照表
自串連是通過將表用別名虛擬成兩個表的方式實現，可以是等值或不等值串連
例如：查詢出每個職員的經理的名字以及他們的職員編號
select worker.empno w_empno, worker.ename w_ename,manager.ename m_ename,manager.empno m_empno
from emp worker join emp manager
on worker.mgr=manager.empno;

----------------------------------------------------------------------------------------

子查詢
子查詢在where子句中
在select查詢中，在where查詢條件中的限制條件不是一個確定的值，而是來自於另一個查詢的結果
為了給查詢提供資料而首先執行的查詢語句叫子查詢
子查詢是嵌入在其他SQL語句中的select語句，大部分出現在where子句中
子查詢嵌入的語句稱作主查詢或者父查詢
主查詢可以是select語句，也可以是其他sql語句中

根據返回結果的不同，子查詢可以分為單行子查詢、多行子查詢和多列子查詢
例如：查詢和Scott同職位的員工
select ename from emp where job=(select job from emp where ename=‘SCOTT‘);

rownum
被稱作為偽列，用於返回標識行資料順序的數字
select rownum,empno,ename,sal from emp;

例如：查詢出emp表第三條以後的資料
select * from (select rownum rm,e.* from emp e) where rm>3;
查詢出emp表第三條到第七條資料
select * from (select rownum rm,e.* from emp e) where rm between 3 and 7;

使用子查詢進行分頁
分頁策略：每次只取一頁的資料，每次換頁，取下一頁的資料
在oracle中利用rownum的功能可用來進行分頁，假設結果集共有105條資料，每頁20條資料，那麼
page1:1-20
page2:21-40
...
pageN:pagesize*(currentpage-1)+1pagesize*currentpage

例如：按照薪水倒序排列，去除結果集中第8-10條記錄
select * from (select rownum rm,sal from (select * from emp order by sal desc) ) where rm between 8 and 10;

查詢emp表，根據職員的職位計算獎金額，管理者1.2倍工資、辦事員1.1倍工資、銷售人員1.05倍工資，如果不是這三個職位的就取工資值。
decode函數解決此類問題
decode(column,search1,result1,search2,result2... ,result)

select ename,job,sal, decode(job,‘MANAGER‘,sal*1.2,‘ANALYST‘,sal*1.1,‘SALESMAN‘,sal*1.05,sal) bonus from emp;
等同於
select ename,job,sal,case job when ‘MANAGER‘ then sal*1.2
when ‘ANALYST‘ then sal*1.1
when ‘SALESMAN‘ then sal*1.05
else sal end bonus
from emp;

根據年薪算
select ename,job,sal,comm,decode(
job,
‘MANAGER‘,(sal+nvl(comm,0))*12*0.5,
‘ANALYST‘,(sal+nvl(comm,0))*12*0.4,
‘SALESMAN‘,(sal+nvl(comm,0))*12*0.3,
(sal+nvl(comm,0))*12*0.1
) bonus from emp;

多列子查詢

exists操作符
檢查子查詢中是否滿足條件的行
1--如果在子查詢中存在滿足條件的行，返回true，就不再子查詢繼續尋找
2--如果子查詢中不存在滿足條件的行，就返回false，繼續在子查詢中尋找

Oracle學習第三講