版權聲明：本文為部落客原創文章，遵循 CC 4.0 BY-SA 版權協定，轉載請附上原文出處連結和本聲明。
本文連結：https://blog.csdn.net/zhao1299002788/article/details/101757909
/*
多表查詢 多個資料庫表做連接配接查詢
使用場景： 查詢的資料來源為多個表
*/
--查詢員工資訊和員工的部門資訊
select * from emp;
select * from dept;
--使用關聯條件 過濾無效資料
select * from emp,dept where emp.deptno=dept.deptno
/*
内連接配接 隐式内連接配接 select * from A,B where A.列=B.列
顯式内連接配接 select * from A inner join B on A.列=B.列
特點 做關聯查詢的兩個表 必須雙方條件資料完全比對 才會提取
*/
--使用顯式内連接配接實作
select * from emp inner join dept on emp.deptno = dept.deptno
/*
外連接配接
左外連接配接 select * from A left join B on A.列=B.列
以左表為基準 左表資料全部顯示 右表資料作為補充顯示
如果沒有資料 顯示空
右外連接配接 select * from B right join A on A.列=B.列
以右表為基準 右表資料全部顯示 左表資料作為補充顯示
如果沒有資料 顯示空
**/
--查詢部門資訊和部門下的員工資訊 沒有員工的部門也要顯示
--左外連接配接實作
select * from dept left join emp on dept.deptno = emp.deptno
--右外連接配接實作
select * from emp right join dept on dept.deptno = emp.deptno
--特定要求部門顯示左邊
select dept.*,emp.* from emp right join dept on dept.deptno = emp.deptno
/*
oracle資料庫特有的外連接配接
文法：使用符号（+） 實作外連接配接
使用方法：根據需求 将符号放在 作為補充顯示的表的列後面
select * from A,B where A.列=B.列(+)
*/
--使用oracle資料庫特有外連接配接 跟等号左右無關
select * from emp,dept where emp.deptno(+)=dept.deptno
select * from emp,dept where dept.deptno=emp.deptno(+)
/*
自連接配接 自己跟自己做關聯查詢
自連接配接查詢 别名必須加
select * from A A1,A A2 where A1.列=A2.列
使用場景：
關聯的記錄在同一個表内
*/
--查詢員工的資訊和員工的上司資訊
select e.empno,e.ename,
m.empno mgr_no,m.ename mgr_name
from emp e,emp m where e.mgr = m.empno
--在上面基礎上 再查詢員工的部門資訊 dept
select * from dept
select e.empno,e.ename,d.dname,
m.empno mgr_no,m.ename mgr_name
from emp e,emp m,dept d
where e.mgr = m.empno and d.deptno = e.deptno
--在上面基礎之上 再查詢員工的工資等級 salgrade
select * from salgrade
select e.empno,
e.ename,
d.dname,
s1.grade,
m.empno mgr_no,
m.ename mgr_name
from emp e, emp m, dept d, salgrade s1
where e.mgr = m.empno
and d.deptno = e.deptno
and e.sal between s1.losal and s1.hisal
--在基礎之上查詢上司的工資等級
select e.empno,
e.ename,
d.dname,
s1.grade,
m.empno mgr_no,
m.ename mgr_name,
s2.grade mgr_grade
from emp e, emp m, dept d, salgrade s1,salgrade s2
where e.mgr = m.empno
and d.deptno = e.deptno
and e.sal between s1.losal and s1.hisal
and m.sal between s2.losal and s2.hisal
--根據查詢的資料 一張表一張表加的時候 分析表之間的關聯關系
--oracle的decode函數
select e.empno,
e.ename,
d.dname,
decode(s1.grade,1,'一級',2,'二級',3,'三級',4,'四級','五級') emp_grade,
m.empno mgr_no,
m.ename mgr_name,
s2.grade mgr_grade
from emp e, emp m, dept d, salgrade s1,salgrade s2
where e.mgr = m.empno
and d.deptno = e.deptno
and e.sal between s1.losal and s1.hisal
and m.sal between s2.losal and s2.hisal
---錯誤示例
select e.empno,
e.ename,
d.dname,
s1.grade,
m.empno mgr_no,
m.ename mgr_name,
s1.grade mgr_grade
from emp e, emp m, dept d, salgrade s1
where e.mgr = m.empno
and d.deptno = e.deptno
and e.sal between s1.losal and s1.hisal
and m.sal between s1.losal and s1.hisal
/*
子查詢 在查詢語句中嵌套查詢語句
文法：
單行子查詢 select * from A where A.列= sql傳回的唯一值
多行子查詢 select * from A where A.列 in sql傳回單列多個值
select * from A,(sql語句傳回多行多列臨時表) t
where A.列 = t.列
*/
--查詢比雇員7654工資高，同時從事和7788相同工作的員工資訊？
--1.查詢資料 員工資訊
--2.資料來源 emp表
--3.查詢條件 工資>7654的工資 工作=7788的工作
select sal from emp where empno=7654 --1250
select job from emp where empno=7788 --ANALYST
--使用結果查詢員工
select * from emp where sal > 1250 and job = 'ANALYST'
--使用sql語句替換查詢條件
select * from emp where
sal > (select sal from emp where empno=7654)
and job = (select job from emp where empno=7788)
--查詢每個部門的最低工資，和最低工資的雇員 及他的部門名稱
--1.查詢資料 員工資訊 最低工資 部門名稱
select deptno,min(sal) d_min from emp group by deptno
--2.資料來源 emp sql語句得到的臨時表 dept
--3.查詢條件 員工工資=部門最低工資 本部門
select e.empno,e.ename,e.sal,d_m.d_min ,dept.dname
from emp e,
(select deptno,min(sal) d_min from emp group by deptno) d_m,
dept
where e.deptno = d_m.deptno and e.sal = d_m.d_min
and e.deptno = dept.deptno
--查詢每個部門最低工資的員工資訊
select * from emp where sal =
(select min(sal) d_min from emp group by deptno)
select * from emp where sal = (800,950,1300)
select * from emp where sal in
(select min(sal) d_min from emp group by deptno)
--查詢不是上司的員工資訊
--1.員工資訊
--2.emp
--3.不是上司
--子查詢空值問題 空值判斷 用is null is not null 其餘判斷結果為UNKNOW
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 )
select * from emp where empno not in( select nvl(mgr,0) from emp )
select * from emp where empno not in( select mgr from emp where mgr >0 )
/*
子查詢特殊使用
exists 存在 表達式 (sql語句)
判斷結果集是否存在 如果存在 exists表達式傳回true
不存在 傳回false
*/
--簡單示例
select * from emp where exists(select * from dept)--所有員工資訊
select * from emp where exists(select * from dept where deptno=123)-- 沒有記錄
--查詢有員工的部門資訊
--1.部門資訊
--2.dept
--3.部門有員工
select deptno from emp; ---得到了有員工的部門編号
--使用in的方式實作
select * from dept where deptno in (select deptno from emp)
/*
普通子查詢 執行順序是 先執行子查詢得到結果用于主查詢
exists表達式執行順序更改
*/
select * from dept where
exists(select * from emp where emp.deptno = dept.deptno)
/*
mySql 使用limit 提取特定記錄條數
oracle 使用 rownum 實作提取記錄 用于分頁使用
rownum 是oracle資料庫查詢到記錄 生成的一系列的數值 (1,2,3,4)
rownum用于做大于判斷 沒有結果 必須使用子查詢先生成rownum
rowun用于小于判斷可以直接查詢出結果
rowunm的執行原理 :
1: 執行sql語句;
2: 取到第一條記錄,指派rownum為1;
3: 判斷rownum是否滿足條件,如果不滿足放棄該行,滿足傳回該行.(不滿足條件,rownum還是從1開始進行判斷)
4: 繼續提取記錄,繼續生成rownum;
5: 循環步驟3;
*/
--rownum的示例
select rownum,emp.* from emp where rownum >5 --沒有任何記錄
select rownum,emp.* from emp where rownum <5 --前四條記錄
select rownum,emp.* from emp where rownum =1 --隻有一條
select rownum,emp.* from emp where rownum >1 --沒有
select rownum,emp.* from emp where rownum >=1 --所有記錄
--先生成rownum 再使用rownum過濾5條記錄以後
select * from (select rownum r,emp.* from emp) where r>5
--查詢員工表中 工資最高的前三名
--工資按照倒序排序
select * from emp order by sal desc
--加入rownum
select rownum,emp.* from emp order by sal desc
--先按照工資排序 再排序基礎之上生成rownum
select rownum,t.* from (select * from emp order by sal desc)t
--判斷rownum提取前三條
select rownum,t.* from (select * from emp order by sal desc)t where rownum<4
--提取6--10條記錄
select * from (
select rownum r,t.* from (select * from emp order by sal desc)t) tt
where r> 5 and r <11
--查詢員工表中工資大于本部門平均工資的員工資訊
--1.員工資訊
select deptno,avg(sal) d_a from emp group by deptno
--2.emp
--3.工資>部門平均工資 必須本部門
行列轉換
Total 1980 1981 1982 1987
14 1 10 1 2
1: 嘗試豎起一列
上面是用年的數值做的别名,下面是年對應的入職員工數
if年1987顯示值是2
decode(hire_year,'1987',hire_count)
2: 使用聚合函數處理空值
select *
from emp e, (select deptno, avg(sal) d_a from emp group by deptno) d_avg
where e.sal > d_avg.d_a
and e.deptno = d_avg.deptno
--統計每年入職的員工個數
select to_char(hiredate,'yyyy') hire_year,count(*) hire_count
from emp group by to_char(hiredate,'yyyy')
--對結果集格式處理 先豎起來一列
select decode(t.hire_year,'1987',t.hire_count) "1987" from
(select to_char(hiredate,'yyyy') hire_year,count(*) hire_count
from emp group by to_char(hiredate,'yyyy') ) t
--把空值的記錄過濾掉 聚合函數忽略空值的記錄
select sum(decode(t.hire_year,'1987',t.hire_count)) "1987" from
(select to_char(hiredate,'yyyy') hire_year,count(*) hire_count
from emp group by to_char(hiredate,'yyyy') ) t
--補全其餘的列
select sum(decode(t.hire_year,'1980',t.hire_count)) "1980",
max(decode(t.hire_year,'1981',t.hire_count)) "1981",
min(decode(t.hire_year,'1982',t.hire_count)) "1982",
avg(decode(t.hire_year,'1987',t.hire_count)) "1987"
from
(select to_char(hiredate,'yyyy') hire_year,count(*) hire_count
from emp group by to_char(hiredate,'yyyy') ) t
--使用sum對hire_count做求和運算補上total
select sum(t.hire_count) total,
sum(decode(t.hire_year,'1980',t.hire_count)) "1980",
max(decode(t.hire_year,'1981',t.hire_count)) "1981",
min(decode(t.hire_year,'1982',t.hire_count)) "1982",
avg(decode(t.hire_year,'1987',t.hire_count)) "1987"
from
(select to_char(hiredate,'yyyy') hire_year,count(*) hire_count
from emp group by to_char(hiredate,'yyyy') ) t
補充知識點：Oracle 中的分頁查詢
ROWNUM:表示行号，實際上隻是一個列,但是這個列是一個僞列,此列可以在每張表中出
現。
ROWID:表中每行資料指向磁盤上的實體位址。
/*
集合的運算
交集 取兩個集合共同的部分 intersect A(1,2,3) B(2,3,4) A交B (2,3)
并集 取兩個集合最大的部分 union A(1,2,3) B(2,3,4) A并B (1,2,3,4)
union all A并B (1,2,3,2,3,4)
差集 從一個集合去掉另外一個集合剩餘的部分 minus A差B (1)
*/
--範例：工資大于1500,或者是20部門下的員工
--不使用集合實作
select * from emp where sal>1500 or deptno=20
--使用集合實作 --不包含重複記錄
select * from emp where sal>1500
union
select * from emp where deptno=20
--union all
select * from emp where sal>1500
union all
select * from emp where deptno=20
--範例：工資大于1500,并且是20部門下的員工
select * from emp where sal>1500 and deptno=20
--使用集合實作
select * from emp where sal>1500
intersect
select * from emp where deptno=20
--1981年入職的普通員工（不包括經理，總裁）
--使用以前知識實作
select * from emp where '1981' = to_char(hiredate,'yyyy')
and job not in ('MANAGER','PRESIDENT')
--使用集合實作
select * from emp where '1981' = to_char(hiredate,'yyyy')
minus
select * from emp where job in ('MANAGER','PRESIDENT')
/*
集合的使用場景
用于做跨表合并資料使用
合并資料規則
必須合并的列的數量一緻 列的數值類型相同
*/
--查詢公司下所有的員工資訊
select empno buisiness_no,ename buisiness_name from emp
union
select mid,mname from manager;
--資料類型不一緻不能合并
select empno buisiness_no,ename buisiness_name from emp
union
select mname,mid from manager;
--上司表
create table manager(
mid number(9),
mname varchar(10)
)
insert into manager values(1,'zs');
insert into manager values(2,'lisi');
commit; 複制