Oracle數(shù)據(jù)庫基礎(chǔ)應(yīng)用04

1、第四章 Oracle與高級SQL語句 目標 掌握SQL語句在Oracle數(shù)據(jù)庫的高級應(yīng)用 分組查詢 連接查詢 子查詢 合并查詢 概述、專業(yè)術(shù)語 1. 在數(shù)據(jù)庫應(yīng)用與設(shè)計方面經(jīng)常需要用到復(fù) 雜查詢來表達復(fù)雜的業(yè)務(wù)邏輯。 2. 本章將在基本的SQL語句使用的基礎(chǔ)上學(xué) 習高級SQL語句在Oracle數(shù)據(jù)庫中的使用。 1. 術(shù)語詞匯表 2. 定義在此章節(jié)中使用的術(shù)語 分組查詢 當執(zhí)行數(shù)據(jù)統(tǒng)計時，需要將表中的數(shù)據(jù)劃分 成幾個組，最終統(tǒng)計每個組的數(shù)據(jù)結(jié)果 數(shù)據(jù)分組統(tǒng)計是通過GROUP BY子句、分 組函數(shù)以及HAVING子句共同實現(xiàn) 分組查詢 分組函數(shù)：作用于多行，并返回單一的值 分組函數(shù)一般要與GROU

2、P BY結(jié)合使用。 如果沒有GROUP BY子句會匯總所有的行， 并產(chǎn)生一個結(jié)果 select avg ( sal ) as 平均工資 from emp ; 雇員表中所有員工的平均工資雇員表中所有員工的平均工資 select count ( * ) from emp where sal = 2000 ; 工資大于等于工資大于等于2000的員工人數(shù)的員工人數(shù) 分組查詢 GROUP BY：對查詢結(jié)果進行分組統(tǒng)計 select deptno, avg(sal), max(sal) from emp group by deptno ; 使用使用GROUP BY進行單列分組進行單列分組 顯示每個部門平均

3、工資和最高工資顯示每個部門平均工資和最高工資 select deptno, job, avg(sal), max(sal) from emp group by deptno, job ; 使用使用GROUP BY進行多列分組進行多列分組 顯示每個部門每種崗位的平均工資和最高工資顯示每個部門每種崗位的平均工資和最高工資 注意：注意：SELECT選擇列表中的列必須出現(xiàn)在選擇列表中的列必須出現(xiàn)在group by子句中子句中 分組查詢 HAVING：限制分組統(tǒng)計結(jié)果 HAVING子句必須跟在GROUP BY之后 select deptno, avg(sal), max(sal) from emp gr

4、oup by deptno having avg(sal) 2500 ; 顯示平均工資低于顯示平均工資低于2500的部門號、平均工資及最高工資的部門號、平均工資及最高工資 連接查詢 相等連接：檢索主從表之間的相關(guān)數(shù)據(jù) select e.ename, e.sal, d.dname from emp e, dept d where e.deptno = d.deptno ; 顯示所有雇員的姓名、和工資及其所在的部門名稱顯示所有雇員的姓名、和工資及其所在的部門名稱 enamesaldeptnodeptnodname select e.ename, e.sal, d.dname from emp e,

5、 dept d where e.deptno = d.deptno and d.deptno = 10 ; 使用使用AND指定其它條件指定其它條件 連接查詢 不等連接：在連接條件中使用除相等比較 符外的其它比較操作符的連接查詢 select e.ename, e.sal, d.dname from emp e, dept d where e.deptno between 10 and 20 ; 查詢部門號在查詢部門號在10和和20之間的部門名稱、雇員名稱、雇員薪水之間的部門名稱、雇員名稱、雇員薪水 連接查詢 自連接：在同一張表之間的連接查詢。用在 自參照表上顯示上下級關(guān)系或者層次關(guān)系 sele

6、ct a.ename from emp a, emp b where a.empno = b.mgr and b.ename = BLAKE ; 顯示雇員顯示雇員“BLAKE”的上級經(jīng)理的名字的上級經(jīng)理的名字 EMPNOENAMEMGR 7839KING 7566JONES7839 7698BLAKE7839 7782CLARK7839 連接查詢 內(nèi)連接和外連接 內(nèi)連接用于返回滿足條件的記錄 外連接不僅返回滿連接足條件的所有記 錄，而且還返回不滿足條件的記錄 SELECT table1.column, table2.column FROM table1 INNER | LEFT | RIGHT

7、 | FULL JOIN table2 ON table1.column = table2.column; INNER JOIN 表示內(nèi)連接 LEFT JOIN 表示左聯(lián)接 RIGHT JOIN 表示右連接 FULL JOIN 表示完全連接 ON 后跟連接條件 連接的語法：連接的語法： 連接查詢 內(nèi)連接 ( INNER JOIN ) select e.ename, d.dname from emp e inner join dept d on e.deptno = d.deptno and e.deptno = 20 ; 查詢部門編號為查詢部門編號為“20”的所有雇員姓名及雇員所在部門的名的所

8、有雇員姓名及雇員所在部門的名 稱稱 select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno and e.deptno = 20 ; 另一種寫法另一種寫法(相等連接相等連接) 連接查詢 左外連接 ( LEFT OUTER JOIN ) select d.dname, e.ename from dept d left join emp e on d.deptno = e.deptno and d.deptno = 20 ; 顯示所有部門的名稱，及部門編號為顯示所有部門的名稱，及部門編號為20的所有雇員名字的所有雇員名

9、字 連接查詢 右外連接 ( RIGHT OUTER JOIN ) select d.dname, e.ename from dept d right join emp e on d.deptno = e.deptno and e.deptno = 20 ; 顯示所有雇員名字，及部門編號為顯示所有雇員名字，及部門編號為20的雇員的雇員 所在的部門的名稱所在的部門的名稱 連接查詢 完全外連接( FULL OUTER JOIN ) select d.dname, e.ename from dept d full join emp e on d.deptno = e.deptno and e.dept

10、no = 20 ; 顯示部門編號為顯示部門編號為20的所有雇員名字及對應(yīng)的部的所有雇員名字及對應(yīng)的部 門名稱，同時列出其他的雇員名字和部門名稱門名稱，同時列出其他的雇員名字和部門名稱 子查詢 子查詢：嵌入在其它SQL語句中的SELECT 語句，也叫做嵌套查詢 子查詢又被分為 單行子查詢 多行子查詢 多列子查詢 子查詢 單行子查詢：內(nèi)層子查詢只返回單行數(shù)據(jù) 外層查詢的WHERE子句中可以使用單行比 較運算符( 、= 、=、 、 all (select sal from emp wheredeptno=20) ; 使用使用 ALL 顯示比部門編號為顯示比部門編號為20的所有雇員的工資都高的雇員信息

11、的所有雇員的工資都高的雇員信息 select ename, deptno from emp where sal any (select sal from emp wheredeptno=10) ; 使用使用 ANY 顯示比部門編號為顯示比部門編號為10的任一雇員的工資高的雇員信息的任一雇員的工資高的雇員信息 子查詢 多列子查詢：內(nèi)層子查詢返回多列數(shù)據(jù) select ename, job, sal, deptno from emp where ( deptno, job ) = ( select deptno, job from emp where ename = SMITH ) ; 顯示與顯示

12、與“SMITH”同一部門，同一職務(wù)的雇員信息同一部門，同一職務(wù)的雇員信息 子查詢 其它常用的子查詢 select ename, job, sal, deptno from emp where exists ( select * from dept where dept.deptno = emp.deptno and loc = NEW YORK ) ; 相關(guān)子查詢：內(nèi)部子查詢需要引用外部主查詢列表相關(guān)子查詢：內(nèi)部子查詢需要引用外部主查詢列表 顯示工作在顯示工作在NEW YORK的所有雇員信息的所有雇員信息 相關(guān)子查詢常用相關(guān)子查詢常用EXISTS謂詞來實現(xiàn)謂詞來實現(xiàn) 如果子查詢存在返回結(jié)果如果

13、子查詢存在返回結(jié)果,則條件為則條件為TRUE ,否則為否則為FALSE 子查詢 insert into employee ( id, name ) select empno, ename from emp ; INSERT語句中使用子查詢語句中使用子查詢 update emp set sal = (select sal from emp where ename = SMITH) ; UPDATE語句中使用子查詢語句中使用子查詢 delete from emp where sal = (select sal from emp where ename = SMITH) ; DELETE語句中使用子

14、查詢語句中使用子查詢 合并查詢 合并查詢：將兩個或多個分結(jié)果集進行集合 操作得到新的結(jié)果集。 可采用的集合操作符有： UNION(并集，結(jié)果總集刪除重復(fù)記錄) UNIION ALL(并集，結(jié)果不刪除重復(fù)記錄) INTERSECT(交集) MINUS(差集) 合并查詢 UNION：獲取兩個結(jié)果集的并集，自動過 濾掉重復(fù)數(shù)據(jù)行，按輸出結(jié)果的第一列排序 select ename, job, sal from emp where sal = 2000 union select ename, job, sal from emp where job = MANAGER; 查詢工資在查詢工資在2000以上，

15、或者職務(wù)是以上，或者職務(wù)是“MANAGER”的員工的名字，職務(wù)和工資的員工的名字，職務(wù)和工資 UNION ALL：與UNIN的區(qū)別是它不會過濾 掉重復(fù)數(shù)據(jù)行 合并查詢 INTERSECT：獲取兩個結(jié)果集的交集，即 顯示同時存在于兩個結(jié)果集中的數(shù)據(jù)。 select ename, job, sal from emp where sal = 2000 intersect select ename, job, sal from emp where job = MANAGER; 查詢工資在查詢工資在2000以上，并且職務(wù)是以上，并且職務(wù)是“MANAGER”的員工的名字，職務(wù)和工資的員工的名字，職務(wù)和工資

16、 合并查詢 MINUS：獲取兩個結(jié)果集的差集。即顯示 在第一個結(jié)果集中存在，第二個結(jié)果集中不 存在的數(shù)據(jù)集。 select ename, job, sal from emp where sal = 2000 minus select ename, job, sal from emp where job = MANAGER; 查詢工資在查詢工資在2000以上，但職務(wù)不是以上，但職務(wù)不是“MANAGER”的員工的名字，職務(wù)和工資的員工的名字，職務(wù)和工資 其它復(fù)雜的查詢 使用CASE表達式 select ename, sal, case when sal 3000 then 3 when sal 200 then 2 else 1 end grade from emp ; 對雇員表雇員工資進行查詢分級對雇員表雇員工資進行查詢分級 對于工資在對于工資在3000以上的定為最高級（第三級）工資，以上的定為最高級（第三級）工資， 對于工資大于對于工資大于200且小于等于且小