/*
多表查詢 多個數(shù)據(jù)庫表做連接查詢
使用場景： 查詢的數(shù)據(jù)來源為多個表
*/
--查詢員工信息和員工的部門信息
select * from emp;
select * from dept;
--使用關聯(lián)條件 過濾無效數(shù)據(jù)
select * from emp,dept where emp.deptno=dept.deptno
/*
內(nèi)連接 隱式內(nèi)連接 select * from A,B where A.列=B.列
顯式內(nèi)連接 select * from A inner join B on A.列=B.列
特點 做關聯(lián)查詢的兩個表 必須雙方條件數(shù)據(jù)完全匹配 才會提取
*/
--使用顯式內(nèi)連接實現(xiàn)
select * from emp inner join dept on emp.deptno = dept.deptno
/*
外連接
左外連接 select * from A left join B on A.列=B.列
以左表為基準 左表數(shù)據(jù)全部顯示 右表數(shù)據(jù)作為補充顯示
如果沒有數(shù)據(jù) 顯示空
右外連接 select * from B right join A on A.列=B.列
以右表為基準 右表數(shù)據(jù)全部顯示 左表數(shù)據(jù)作為補充顯示
如果沒有數(shù)據(jù) 顯示空
**/
--查詢部門信息和部門下的員工信息 沒有員工的部門也要顯示
--左外連接實現(xiàn)
select * from dept left join emp on dept.deptno = emp.deptno
--右外連接實現(xiàn)
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數(shù)據(jù)庫特有的外連接
語法：使用符號（+） 實現(xiàn)外連接
使用方法：根據(jù)需求 將符號放在 作為補充顯示的表的列后面
select * from A,B where A.列=B.列(+)
*/
--使用oracle數(shù)據(jù)庫特有外連接 跟等號左右無關
select * from emp,dept where emp.deptno(+)=dept.deptno
select * from emp,dept where dept.deptno=emp.deptno(+)
/*
自連接 自己跟自己做關聯(lián)查詢
自連接查詢 別名必須加
select * from A A1,A A2 where A1.列=A2.列
使用場景：
關聯(lián)的記錄在同一個表內(nèi)
*/
--查詢員工的信息和員工的領導信息
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
--根據(jù)查詢的數(shù)據(jù) 一張表一張表加的時候 分析表之間的關聯(lián)關系
--oracle的decode函數(shù)
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.查詢數(shù)據(jù) 員工信息
--2.數(shù)據(jù)來源 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.查詢數(shù)據(jù) 員工信息 最低工資 部門名稱
select deptno,min(sal) d_min from emp group by deptno
--2.數(shù)據(jù)來源 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的方式實現(xiàn)
select * from dept where deptno in (select deptno from emp)
/*
普通子查詢 執(zhí)行順序是 先執(zhí)行子查詢得到結果用于主查詢
exists表達式執(zhí)行順序更改
*/
select * from dept where
exists(select * from emp where emp.deptno = dept.deptno)
/*
mySql 使用limit 提取特定記錄條數(shù)
oracle 使用 rownum 實現(xiàn)提取記錄 用于分頁使用
rownum 是oracle數(shù)據(jù)庫查詢到記錄 生成的一系列的數(shù)值 (1,2,3,4)
rownum用于做大于判斷 沒有結果 必須使用子查詢先生成rownum
rowun用于小于判斷可以直接查詢出結果
rowunm的執(zhí)行原理 :
1: 執(zhí)行sql語句;
2: 取到第一條記錄,賦值rownum為1;
3: 判斷rownum是否滿足條件,如果不滿足放棄該行,滿足返回該行.(不滿足條件,rownum還是從1開始進行判斷)
4: 繼續(xù)提取記錄,繼續(xù)生成rownum;
5: 循環(huán)步驟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.工資>部門平均工資 必須本部門
行列轉(zhuǎn)換
Total 1980 1981 1982 1987
14 1 10 1 2
1: 嘗試豎起一列
上面是用年的數(shù)值做的別名,下面是年對應的入職員工數(shù)
if年1987顯示值是2
decode(hire_year,'1987',hire_count)
2: 使用聚合函數(shù)處理空值
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
--統(tǒng)計每年入職的員工個數(shù)
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
--把空值的記錄過濾掉 聚合函數(shù)忽略空值的記錄
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:表示行號，實際上只是一個列,但是這個列是一個偽列,此列可以在每張表中出
現(xiàn)。
ROWID:表中每行數(shù)據(jù)指向磁盤上的物理地址。
/*
集合的運算
交集 取兩個集合共同的部分 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部門下的員工
--不使用集合實現(xiàn)
select * from emp where sal>1500 or deptno=20
--使用集合實現(xiàn) --不包含重復記錄
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
--使用集合實現(xiàn)
select * from emp where sal>1500
intersect
select * from emp where deptno=20
--1981年入職的普通員工（不包括經(jīng)理，總裁）
--使用以前知識實現(xiàn)
select * from emp where '1981' = to_char(hiredate,'yyyy')
and job not in ('MANAGER','PRESIDENT')
--使用集合實現(xiàn)
select * from emp where '1981' = to_char(hiredate,'yyyy')
minus
select * from emp where job in ('MANAGER','PRESIDENT')
/*
集合的使用場景
用于做跨表合并數(shù)據(jù)使用
合并數(shù)據(jù)規(guī)則
必須合并的列的數(shù)量一致 列的數(shù)值類型相同
*/
--查詢公司下所有的員工信息
select empno buisiness_no,ename buisiness_name from emp
union
select mid,mname from manager;
--數(shù)據(jù)類型不一致不能合并
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;
Oracle數(shù)據(jù)庫之第二篇
?著作權歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。
相關閱讀更多精彩內(nèi)容
- 眾所周知，group by 子句是用來分組的，by后面跟上字段，表示以哪個字段之后，那么oracle和mysql的...
- 數(shù)據(jù)泵技術 數(shù)據(jù)泵技術是OracleDatabase 10g之后的新技術，它比原來導入/導出(imp,exp)技術...
- 導入和導出 EXP命令實現(xiàn)導出 步驟： 方法一： 第一步：按下windows+R調(diào)出運行窗口。 第二步：輸入EXP...
- 數(shù)據(jù)庫實例名 一個instance_name（數(shù)據(jù)庫實例名）是一個參數(shù) 查詢instance_name 方法一：s...
