超詳細Oracle教程(共182頁)

1、Oracle 教程(jiochng)2009.5LouisOracle 教程(jiochng)目錄(ml)引言. 3第一章 數(shù)據(jù)庫基礎. 6第二章 Oracle 入門(r mn) . 6第三章 查詢基礎. 11 第四章 單行函數(shù). 14 第五章 分組函數(shù). 19 第六章 多表查詢. 21 第七章 子查詢. 24 第八章 高級查詢. 40 第九章 數(shù)據(jù)字典. 43 第十章 Oracle 數(shù)據(jù)類型 . 44 第十一章 Oracle 體系結(jié)構(gòu)(DBA) . 45 第十二章 DDL(改變表結(jié)構(gòu)) . 46 第十三章 DML(改變數(shù)據(jù)結(jié)構(gòu)）. 48 第十四章 約束. 49 第十五章 視圖. 55 第十六

2、章 索引. 56 第十七章 序列、同義詞. 65 第十八章 PL SQL. 67 第十九章 游標、函數(shù). 79 第二十章 存儲過程. 86 第二十一章 觸發(fā)器. 90 第二十二章 事務（數(shù)據(jù)庫系統(tǒng)概論） . 99 第二十三章 用戶管理. 100 第二十四章 備份 恢復 SQLLoader . 104 第二十五章 數(shù)據(jù)庫設計范式. 106 第二十六章 數(shù)據(jù)庫設計工具. 107 第二十七章 對象關(guān)系數(shù)據(jù)庫系統(tǒng) . 112 第二十八章 其他數(shù)據(jù)庫. 113-2-Oracle 教程(jiochng)引言SUN 2008 初 10 億美元收購(shugu) MySQL Oracle 2009 年 4 月

3、 74 億美元收購 SUNSun 與 Oracle 合并(hbng)的未來1，如果云計算對企業(yè)來說變得越來越重要，那么數(shù)據(jù)將是云計算的核心。而講到數(shù)據(jù)，也就意味著 數(shù)據(jù)庫。就如塔克商學院數(shù)字策略中心主任 M. Eric Johnson 所說的那樣，Sun 已經(jīng)清楚展現(xiàn)了一 個真實的云計算環(huán)境。就那些將云計算停留在理論階段的對手來說，收購 Sun 對 Oracle 來說將獲 得競爭優(yōu)勢。2，Johnson 主任還指出，Sun 在很多需求旺盛的方面“保持領先地位”。Sun 的技術(shù)可以幫助數(shù) 據(jù)庫提高性能，尤其是對大型數(shù)據(jù)庫。Oracle 和 Sun 的結(jié)合之后，對任何一個信息官來說都具有吸 引力。

4、3，Oracle 軟件和 Java 的緊密結(jié)合，使得 Oracle 的軟件能跨平臺使用。就如 IDC 研究副總裁 Jean Bozman 指出那樣，真正云計算的第一步是“將服務器和存儲單元垂直化”，可以適用于不同的 硬件平臺。憑借 Java，Oracle 可以拓展跨平臺的控制力，而這正是企業(yè) IT 部門想要的。實際上， Oracle 的 Oracle Fusion Middleware 正是基于 Java。4，有意思的是，Oracle Fusion Middleware 擴大了對服務器的需求，其中原因正是由于它是基于 Java。而現(xiàn)在，Oracle 通過購得 Sun 服務器資源，可以進一步降低

5、價格，從而進一步提高 OracleFusion Middleware 的競爭力。5，由于 Oracle 大部分收入來自數(shù)據(jù)庫軟件許可，因此有必要的話，可以用便宜的服務器硬件 作為促銷的手段，這樣對那些硬件服務器競爭對手來說是不小的打擊。6，Sun 已經(jīng)售出 160 萬到 200 萬臺服務器，還有更多的運行 Sun Solaris 操作系統(tǒng)的其它公司 的服務器。這對 Oracle 捆綁銷售自家產(chǎn)品也很有幫助。7，Oracle 收購 Sun，當然也包括開源數(shù)據(jù)庫 MySQL。對于那些預算不多但又需要 Oracle 產(chǎn) 品的用戶，Oracle 可以用 MySQL 來滿足這些用戶的需求。這可以使 Or

6、acle 進入中小企業(yè)業(yè)務，更 有機會在這方面超過 SAP。此外，還提供了和微軟 SQLServer 的競爭產(chǎn)品，而不需要降低 Oracle 自有的產(chǎn)品的定位。-3-Oracle 教程(jiochng)Oracle 和 Sun 的合作(hzu)可能會影響(yngxing) Oracle 和一些合作伙伴的關(guān)系，比如 Oracle 和惠普的關(guān)系可 能就會出現(xiàn)問題。不過，不像 IBM，事實上，每個硬件公司都繞不開 Oracle，因為它是數(shù)據(jù)庫的主 力軍。他們離不開 Oracle，因此，他們也只能選擇忍氣吞聲。而且，與 Sun 和 IBM 聯(lián)合不同，Oracle 和 Sun 不存在硬件的沖突，那些 S

7、un 的服務器用戶不 必擔心合并會導致放棄自己在使用的服務器產(chǎn)品線?？偠灾?，Oracle 以相對較低的價格收購 Sun 是明智之舉，將會使得合并后的 Oracle 成為各 大公司數(shù)據(jù)中心的樞紐。云計算李開復（現(xiàn)任 Google 全球副總裁、中國區(qū)總裁）打了一個很形象的比喻：錢莊。最早人們只是把 錢放在枕頭底下，后來有了錢莊，很安全，不過兌現(xiàn)起來比較麻煩?，F(xiàn)在發(fā)展到銀行可以到任何一 個網(wǎng)點取錢，甚至通過 ATM，或者國外的渠道。就像用電不需要家家裝備發(fā)電機，直接從電力公司 購買一樣?！霸朴嬎恪睅淼木褪沁@樣一種變革由谷歌、IBM 這樣的專業(yè)網(wǎng)絡公司來搭建計算機 存儲、運算中心，用戶通過一根網(wǎng)線

8、借助瀏覽器就可以很方便的訪問，把“云”做為資料存儲以及應 用服務的中心。GOOGLE（一）原理:云計算(Cloud Computing)是分布式處理(Distributed Computing)、并行處理(Parallel Comput ing)和網(wǎng)格計算(Grid Computing)的發(fā)展，或者說是這些計算機科學概念的商業(yè)實現(xiàn)。云計算的基本原理是，通過使計算分布在大量的分布式計算機上，而非本地計算機或遠程服務 器中，企業(yè)數(shù)據(jù)中心的運行將更與互聯(lián)網(wǎng)相似。這使得企業(yè)能夠?qū)①Y源切換到需要的應用上，根據(jù) 需求訪問計算機和存儲系統(tǒng)。這可是一種革命性的舉措，打個比方，這就好比是從古老的單臺發(fā)電機模式轉(zhuǎn)

9、向了電廠集中供 電的模式。它意味著計算能力也可以作為一種商品進行流通，就像煤氣、水電一樣，取用方便，費 用低廉。最大的不同在于，它是通過互聯(lián)網(wǎng)進行傳輸?shù)?。云計算的藍圖已經(jīng)呼之欲出：在未來，只需要一臺筆記本或者一個手機，就可以通過網(wǎng)絡服務 來實現(xiàn)我們需要的一切，甚至包括超級計算這樣的任務。從這個角度而言，最終用戶才是云計算的 真正擁有者。云計算的應用包含這樣的一種思想，把力量聯(lián)合起來，給其中的每一個成員使用。-4-（二）云計算有哪些好處？Oracle 教程(jiochng)1、安全，云計算提供了最可靠(kko)、最安全的數(shù)據(jù)存儲中心，用戶不用(byng)再擔心數(shù)據(jù)丟失、病毒入侵等麻煩。2、方便，

10、它對用戶端的設備要求最低，使用起來很方便。3、數(shù)據(jù)共享，它可以輕松實現(xiàn)不同設備間的數(shù)據(jù)與應用共享。4、無限可能，它為我們使用網(wǎng)絡提供了幾乎無限多的可能。（三）云計算最有利于中小企業(yè)？ 云計算技術(shù)將使得中小企業(yè)的成本大大降低。如果說“云”給大型企業(yè)的 IT 部門帶來了實惠，那么對于中小型企業(yè)而言，它可算得上是上天的恩賜了。過去，小公司人力資源不足，IT 預算吃緊，那種動輒數(shù)百萬美元的 IT 設備所帶來的生產(chǎn)力對它們而言真是如夢一般遙遠，而如今，“云”為它們 送來了大企業(yè)級的技術(shù)，并且先期成本極低，升級也很方便。這一新興趨勢的重要性毋庸置疑，不過，它還僅僅是一系列變革的起步階段而已。云計算不但 抹

11、平了企業(yè)規(guī)模所導致的優(yōu)劣差距，而且極有可能讓優(yōu)劣之勢易主。簡單地說，當今世上最強大最 具革新意義的技術(shù)已不再為大型企業(yè)所獨有?！霸啤弊屆總€普通人都能以極低的成本接觸到頂尖的 IT 技術(shù)。（四）“云”時代目前，PC 依然是我們?nèi)粘９ぷ魃钪械暮诵墓ぞ呶覀冇?PC 處理文檔、存儲資料，通過電 子郵件或 U 盤與他人分享信息。如果 PC 硬盤壞了，我們會因為資料丟失而束手無策。而在“云計算”時代，“云”會替我們做存儲和計算的工作。“云”就是計算機群，每一群包括了幾十 萬臺、甚至上百萬臺計算機?！霸啤钡暮锰庍€在于，其中的計算機可以隨時更新，保證“云”長生不老。 Google 就有好幾個這樣的“云”，其

12、他 IT 巨頭，如微軟、雅虎、亞馬遜（Amazon）也有或正在建設 這樣的“云”。屆時，我們只需要一臺能上網(wǎng)的電腦，不需關(guān)心存儲或計算發(fā)生在哪朵“云”上，但一旦有需要， 我們可以在任何地點用任何設備，如電腦、手機等，快速地計算和找到這些資料。我們再也不用擔 心資料丟失。-5-第一章數(shù)據(jù)庫基礎(jch)1.1、數(shù)據(jù)庫基本概念(ginin)數(shù)據(jù)庫（Database, DB）數(shù)據(jù)庫管理系統(tǒng)(xtng)（Database Management System, DBMS） 數(shù)據(jù)庫管理員（ Database Administrator, DBA） 數(shù)據(jù)庫系統(tǒng)（ Database System, DBS ）

13、1.2、關(guān)系型數(shù)據(jù)庫Q:目前都有哪些主流的關(guān)系型數(shù)據(jù)庫Oracle 教程A:Oracle Oralce、 IBM DB2、MSSQL /Server、SyBase SyBase、IBM Informix、MySQL、AccessQ:XML,TXT 可以做為數(shù)據(jù)庫嗎？1.3、E-R 模型（Entry-Relation）E-R 模型三要素：實體、關(guān)系、屬性 實體間聯(lián)系（1:1）（1：n）(n:m)Q:學生與課程什么關(guān)系？第二章Oracle 入門2.1、Oracle 概述甲骨文，四大創(chuàng)始人-6-Oracle 教程(jiochng)Larry Ellison 一生(yshng)最大的目標，“財富(ci

14、f)榜超過 Bill Gate”Bruce Scott 已離開 Oralce,創(chuàng)建了一套新的數(shù)據(jù)庫 PointBase2.2、Oracle 特點全球化、跨平臺的數(shù)據(jù)庫 支持多用戶、高性能的事務處理 強大的安全性控制和完整性控制 支持分布式數(shù)據(jù)庫和分布處理2.3、Oracle 版本Oracle8i: I internet 表示 Oracle 公司要開始正式進入互聯(lián)網(wǎng) Oracle9i:與 Oracle8i 相關(guān)，性能方面更佳， 管理更人化 Oracle10g: g(grid)網(wǎng)格技術(shù)Oracle11g: g(grid)網(wǎng)格技術(shù)Q: 何為網(wǎng)格技術(shù)？2009 年 1 月淘寶網(wǎng)決定采用 Oracle

15、網(wǎng)格計算(Grid Computing)架構(gòu)來,采用 Oracle 數(shù)據(jù)庫和 Oracle 真正 應用集群來重新打造并強化其基礎架構(gòu)和數(shù)據(jù)倉庫環(huán)境2.4、安裝 Oracle 數(shù)據(jù)庫注意：1、 安裝的時候，一定要關(guān)掉防火墻。否則可能造成安裝不成功-7-2、 全局(qunj)數(shù)據(jù)庫名 SID,類似于 MYSQL 中常用(chn yn)的localhost.Oracle 教程(jiochng)3、 字符集一定要選擇正確。一旦選錯，除非更改成該字符集的父類。否則只能重裝4、 安裝完主要的用戶為：a)普通用戶： Scott/tiger(練習常用)b)普通管理員：System/system c)超級管理員

16、：Sys/sys5、安裝完后的服務配置 (運行中輸入：services.msc)開啟該服務，會占用 tomcat 的 8080 端口。 查看端口號：tasklist|findstr 8080如果裝完 Oracle 后，又改了機器名可能會導致 Listener 服務無法啟動，解決方式： 修改 C:oracleora92networkadmin listener.ora 下的 HOST2.5、卸載 Oracle 數(shù)據(jù)庫2.6、Oracle 目錄(ml)（Admin,oracle92,oradata） Q:這三個文件夾各存放(cnfng)什么文件？2.7、Oracle 客戶(k h)端工具2.7.1

17、、SQLPLUS2.7.2、SQLPLUSW2.7.3、Sql Plus WorkSheet2.7.4、iSqlplus（HTTP）http:/localhost:7778/isqlplus2.7.5、PL /SQL Developer7.14 以上版本，帶有自動提示功能2.7.6、Object Browser國外用的比較多（日本）2.8、Scott 案例下表分析(fnx)2.8.1、雇員表：EMP 2.8.2、部門表：Dept2.8.3、工資等級表：Salgrade-10-Oracle 教程(jiochng)2.8.4、獎金表：Bonus 第三章查詢(chxn)基礎3.1、入門(r mn)語

18、句普通用戶連接： Conn scott/tiger 超級管理員連接： Conn “sys/sys as sysdba” Disconnect; 斷開連接Save c:1.txt 把 SQL 存到文件Ed c:1.txt 編輯 SQL 語句 c:1.txt 運行 SQL 語句Desc emp; 描述 Emp 結(jié)構(gòu)Select * from tab; 查看該用戶下的所有對象Show user; 顯示當前用戶如果在 sys 用戶下：查詢 Select * from emp; 會報錯，原因：emp 是屬于 scott，所以此時必 須使用：select * from scott.emp;/運行上一條語句

19、3.2、SQL 簡介SQL 全名是結(jié)構(gòu)化查詢語言（Structured Query Language），是用于數(shù)據(jù)庫中的標準數(shù)據(jù)查 詢語言，IBM 公司最早使用在其開發(fā)的數(shù)據(jù)庫系統(tǒng)中。1986 年 10 月，美國 ANSI 對 SQL 進行規(guī)范后，以此作為關(guān)系式數(shù)據(jù)庫管理系統(tǒng)的標準語言 (ANSI X3. 135-1986)，1987 年 得到國際標準組織的支持下成為國際標準。不過各種通行的數(shù)據(jù)庫系統(tǒng)在其實踐過程中都對SQL 規(guī)范作了某些編改和擴充。所以，實際上不同數(shù)據(jù)庫系統(tǒng)之間的 SQL 語言不能完 全相互通用DML 語句（數(shù)據(jù)操作語言）Insert、Update、 Delete selec

20、t、DDL 語句（數(shù)據(jù)定義語言）Create、Alter、 Drop、Truncate DCL 語句（數(shù)據(jù)控制語言）Grant、Revoke事務控制語句 Commit 、Rollback、Savepoint-11-3.3、簡單(jindn)的 Select 語句(yj)3.4、使用(shyng)算術(shù)表達式+ - / *3.5、連接運算符 |3.6、使用字段別名 as3.7、空值 is null3.8、去除重復行 distinct3.9、查詢結(jié)果排序 order by asc(desc)3.10、比較運算符 (!= or ) between and3.11、in 操作 not in3.12、模糊

21、查詢 like% 表示零或多個字符_表示一個字符對于特殊符號可使用 ESCAPE 標識符來查找select * from emp where ename like %*_% escape *Oracle 教程上面的 escape 表示*后面的那個符號不當成特殊字符處理，就是查找普通的_符號3.13、邏輯運算符 or and not3.14、練習選擇在部門 30 中員工的所有信息Select * from emp where deptno=30;-12-Oracle 教程(jiochng)列出職位為（MANAGER）的員工的編號，姓名Select empno,ename from emp whe

22、re job = Manager;找出獎金高于工資的員工Select * from emp where commsal;找出每個員工獎金和工資的總和(zngh)Select sal+comm,ename from emp;找出部門(bmn) 10 中的經(jīng)理(MANAGER)和部門 20 中的普通員工(CLERK)Select * from emp where (deptno=10 and job=MANAGER) or (deptno=20 and job=CLERK);找出部門 10 中既不是經(jīng)理也不是普通員工，而且工資大于等于 2000 的員工Select * from emp where

23、 deptno=10 and job not in(MANAGER,CLERK) and sal=2000;找出有獎金的員工的不同工作Select distinct job from emp where comm is not null and comm0找出沒有獎金或者獎金低于 500 的員工Select * from emp where comm500 or comm is null;顯示雇員姓名，根據(jù)其服務年限，將最老的雇員排在最前面select ename from emp order by hiredate ;-13-第四章單行(dn xn)函數(shù)4.1、字符(z f)函數(shù)UpperS

24、ELECT Upper (abcde) FROM dual ;SELECT * FROM emp WHERE ename=UPPER(smith) ;Oracle 教程(jiochng)LowerSELECT lower(ABCDE) FROM dual ;InitcapSelect initcap(ename) from emp;ConcatSelect concat(a,b)fromdual; Select a | b from dual;SubstrSelect substr(abcde,length(abcde)-2) from dual; Select substr(abcde,-3

25、,3) from dual;LengthSelect length(ename) from emp;ReplaceSelect replace(ename,a,A) from emp;-14-Oracle 教程(jiochng)InstrSelect instr(Hello World,or) from dual; 8 indexofLpadlpad(Smith,10,*) 左側(cè)(zu c)填充 lpad() *SmithRpadrpad(Smith,10,*) 右側(cè)(yu c)填充 rpad()Smith*Trimtrim(Mr Smith) 過濾首尾空格 trim() Mr Smith4.

26、2、數(shù)值函數(shù)Roundselect round(412,-2) from dual;select round(412.313,2) from dual;Mod select mod(5,3) from dual;Truncselect trunc(412.13,-2) from dual;4.3、日期函數(shù)Months_between()select months_between(sysdate,hiredate) from emp;Add_months()select add_months(sysdate,1) from dual;-15-Oracle 教程(jiochng)Next_day(

27、)select next_day(sysdate,星期一) from dual;Last_dayselect last_day(sysdate) from dual;4.4、轉(zhuǎn)換(zhunhun)函數(shù)To_charselect to_char(sysdate,yyyy) from dual;select to_char(sysdate,fmyyyy-mm-dd) from dual;select to_char(sal,L999,999,999) from emp;select to_char(sysdate,D) from dual;/返回(fnhu)星期To_numberselect to

28、_number(13)+to_number(14) from dual;To_dateSelect to_date(20090210,yyyyMMdd) from dual;4.5、通用函數(shù)NVL()函數(shù)select nvl(comm,0) from emp;NULLIF()函數(shù)如果表達式 exp1 與 exp2 的值相等則返回 null，否則 返回 exp1 的值NVL2()函數(shù)select empno, ename, sal, comm, nvl2(comm, sal+comm, sal) total from emp;-16-Oracle 教程(jiochng)COALESCE()函數(shù)(

29、hnsh)依次(yc)考察各參數(shù)表達式，遇到非 null 值即停止并返 回該值。select empno, ename, sal, comm, coalesce(sal+comm, sal, 0)總收入 from emp;CASE 表達式select empno, ename, sal, case deptnowhen 10 then 財務部when 20 then 研發(fā)部 when 30 then 銷售部 else 未知部門end 部門from emp;DECODE()函數(shù)和 case 表達式類似，decode()函數(shù)也用于實現(xiàn)多路分支結(jié)構(gòu)select empno, ename, sal,

30、decode(deptno, 10, 財務部,20, 研發(fā)部,30, 銷售部,未知部門)部門from emp;decode 與 case 哪個更好用呢？單行函數(shù)嵌套select empno, lpad(initcap(trim(ename),10, )name, job, sal from emp;4.6、練習找出每個月倒數(shù)第三天受雇的員工（如：2009-5-29）select * from emp where last_day(hiredate)-2=hiredate;-17-Oracle 教程(jiochng)找出 25 年前雇的員工select * from emp where hire

31、date 2000;group by deptno;5.5、HAVING 子句select deptno, job, avg(sal)from emp-19-where hiredate = to_date(1981-05-01,yyyy-mm-dd)group by deptno,job having avg(sal) 1200 order by deptno,job;5.6、分組函數(shù)嵌套select max(avg(sal)from empgroup by deptno;5.7、練習(linx)分組統(tǒng)計各部門下工資500 的員工的平均工資、Oracle 教程(jiochng)Select

32、avg(sal) from emp where sal500 group by deptno ;統(tǒng)計各部門下平均工資大于 500 的部門select deptno,avg(sal) from emp group by deptno having avg(sal)500 ;算出部門(bmn) 30 中得到最多獎金的員工獎金Select max(comm) from emp where deptno = 30 ;算出部門 30 中得到最多獎金的員工姓名select ename from emp where comm = (select max(comm) from emp where deptno

33、=30);算出每個職位的員工數(shù)和最低工資Selectjob,min(sal),count(*) from emp group by job;-20-Oracle 教程(jiochng)算出每個部門,，每個職位的平均工資和平均獎金(平均值包括沒有獎金)，如果平均獎金大于 300，顯示“獎金不錯”，如果平均獎金(jingjn) 100 到 300，顯示“獎金一般”，如果平均獎金小于 100，顯示“基本沒有獎金”， 按部門編號降序，平均工資降序排列(pili)Select avg(sal),avg(nvl(comm.,0) case when avg(nvl(comm,0)300 then 獎 金

34、不錯 when avg(nvl(comm,0)300 then 獎金不錯 end 獎金狀況 from emp group by job order by job desc,avg(sal) desc;列出員工表中每個部門的員工數(shù)，和部門 noSelect count(*),deptno from emp group by deptno;得到工資大于自己部門平均工資的員工信息select * from emp e1,(select deptno,avg(sal) as avgsal from emp group by deptno) e2where e1.deptno=e2.deptno and

35、 e1.sal e2.avgsal;分組統(tǒng)計每個部門下，每種職位的平均獎金（也要算沒獎金的人）和總工資(包括獎金)select deptno,job,avg(nvl(comm,0),sum(sal+nvl(comm,0) from emp group by deptno,job;第六章多表查詢6.1、笛卡爾集(Cross Join)Select * from emp,dept;6.2、等值連接(Equijoin)(Natural join.on)select empno, ename, sal, emp.deptno, dname from emp, dept where emp.deptno

36、 = dept.deptno;6.3、非等值連接(Non-Equijoin)select ename,empno,grade from emp,salgrade where sal between losal and hisal;-21-6.4、自連接(Self join)Oracle 教程(jiochng)select e.empno,e.ename,m.empno,m.ename from emp e,emp m where e.mgr =m.empno;select e.empno,e.ename,m.empno,m.ename from emp e,emp m where m.mgr

37、=e.empno;6.5、左外聯(lián)接（Left Outer Join ）select s.sid,s.sname,s1.sid,s1.sname from student s,student1 s1 where s.sid=s1.sid(+);select empno,ename,dname from emp left outer join dept on emp.deptno =dept.deptno;6.6、右外聯(lián)接（Right Outer Join）select s.sid,s.sname,s1.sid,s1.sname from student s,student1 s1 where s

38、.sid(+)=s1.sid;select empno,ename,dname from emp right outer join dept on emp.deptno= dept.deptno;6.7、滿外聯(lián)接（Full Outer Join）select empno,ename,dname from emp full outer join dept on emp.deptno =dept.deptno;12243645內(nèi)連接(linji)2244-22-Oracle 教程(jiochng)滿連接(linji)12234456左連接(linji)12 234 45右連接2 24 466.8、

39、集合操作UNION：并集，所有的內(nèi)容都查詢，重復的顯示一次UNION ALL：并集，所有的內(nèi)容都顯示，包括重復的INTERSECT：交集：只顯示重復的MINUS：差集：只顯示對方?jīng)]有的（跟順序是有關(guān)系的） 首先建立一張只包含 20 部門員工信息的表：CREATE TABLE emp20 AS SELECT * FROM emp WHERE deptno=20 ;1、 驗證 UNION 及 UNION ALLUNION：SELECT * FROM emp UNION SELECT * FROM emp20 ;使用此語句重復的內(nèi)容不再顯示了UNION ALL：SELECT * FROM emp U

40、NION ALL SELECT * FROM emp20 ;重復的內(nèi)容依然顯示2、 驗證 INTERSECTSELECT * FROM emp INTERSECT SELECT * FROM emp20 ;只顯示了兩個表中彼此重復的記錄。3、 MINUS：返回差異的記錄SELECT * FROM emp MINUS SELECT * FROM emp20 ;只顯示了兩張表中的不同記錄-23-滿鏈接(lin ji)也可以用以下的方式(fngsh)來表示(biosh)：Oracle 教程select t1.id,t2.idfrom table1t1,tablet2 wheret1.id=t2.id

41、(+)unionselect t1.id,t2.idfrom table1t1,tablet2 wheret1.id(+)=t2.id第七章子查詢7.1、單行子查詢select * from empwhere sal (select sal from emp where empno = 7566);7.2、子查詢空值/多值問題如果子查詢未返回任何行，則主查詢也不會返回任何結(jié)果(空值)select * from emp where sal (select sal from emp where empno = 8888);如果子查詢返回單行結(jié)果，則為單行子查詢，可以在主查 詢中對其使用相應的單行記

42、錄比較運算符(正常)select * from emp where sal (select sal from emp where empno = 7566);如果子查詢返回多行結(jié)果，則為多行子查詢，此時不允許 對其使用單行記錄比較運算符(多值)select * from emp where sal (select avg(sal) from emp group by deptno);/非法7.3、多行子查詢select * from emp where sal any(select avg(sal) from emp group by deptno);select * from emp whe

43、re sal all(select avg(sal) from emp group by deptno);select * from emp where job in (select job from emp where ename = MARTIN or ename = SMITH);7.4、TopN 查詢select * from emp where rownum=1 or rownum=2;select *-24-from (select *from emp order by sal desc)where rownum = 5;Q:如何(rh)理解（select *from emp wh

44、ere rownum=5 order by sal desc）7.5、分頁查詢(chxn)Oracle 教程(jiochng)select * from (select rownum no,e.* from(select * from emp order by sal desc) e where rownum=3;select *from(select rownum no,e.* from (select * from emp order by sal desc) e)whereno=3 and no3) ed where d.deptno=ed.deptno;找出工資比 jones 多的員工

45、select*fromempwheresal=(selectsalfromempwhere lower(ename)=jones);列出所有員工的姓名和其上級的姓名select e1.ename as lower ,e2.ename as upper from emp e1,emp e2 where e1.mgr= e2.empno;select e1.ename as lower ,e2.ename as upper from emp e1,emp e2 where e1.mgr = e2.empno(+);以職位分組，找出平均工資最高的兩種職位Select * from ( select

46、avg(sal) from emp order by job desc ) where rownum (select max(sal) from emp where deptno=20) and e.deptno=d.deptnoselect ename,deptno,sal from emp where salall(select sal from emp where deptno=20) and deptno not in 20得到平均工資大于 2000 的工作職種select job from emp group by job having avg(sal) 2000;-26-Oracl

47、e 教程(jiochng)分部門得到工資大于 2000 的所有員工的平均工資，并且平均工資還要大于 2500select deptno,avg(sal) from emp where sal2000 group by deptno having avg(sal)2500;得到每個月工資總數(shù)最少的那個部門的部門編號，部門名稱，部門位置(wi zhi)select * from dept wheredeptno = (select e.deptno from(select deptno,sum(sal) from emp group by deptno order by sum(sal)ewher

48、e rownum=1);分部門得到平均工資等級為 2 級（等級表）的部門編號(bin ho)select new.dno from salgrade sa,(select deptno as dno,avg(sal) as avgsal from emp group by deptno) new where sa.grade=4 and new.avgsal between sa.losal and sa.hisal;、查找出部門 10 和部門 20 中，工資最高第 3 名到工資第 5 名的員工的員工名字，部門名字，部門位置select emp.ename,dept.dname,dept.lo

49、c fromemp, dept,(select rownum no,new.* from(select * from empwhere emp.deptno = 10 or deptno = 20 order by emp.sal desc) new)ewhere emp.deptno = dept.deptno and e.no =3 and e.no (m.sal+nvl(m,0);-27-Oracle 教程(jiochng)查找出工資等級不為 4 級的員工的員工名字，部門名字，部門(bmn)位置(wi zhi)select ename,dname,loc,salfrom dept d,(

50、select emp.deptno,emp.ename,emp.sal from emp,salgradewhere sal between losal and hisal and grade !=4) new where d.deptno = new.deptno;select e.ename,d.dname,d.loc from emp e,dept d,(select * from salgrade where grade=4) s where e.deptno=d.deptno and(e.sals.hisal);查找出職位和MARTIN 或者SMITH一樣的員工的平均工資select

51、 avg(sal)from empwhere job in (select jobfrom empwhere ename=MARTIN or ename=SMITH);查找出不屬于任何部門的員工select * from emp where deptno is null or deptno not in(select deptno from dept);按部門統(tǒng)計員工數(shù)，查處員工數(shù)最多的部門的第二名到第五名（列出部門名字，部門位置）select dept.dname, dept.locfrom (select rownum no, deptno from(select count(*) emp

52、loyeeSum, deptno from emp group by deptno order byemployeeSum desc) e,dept where e.no between 2 and 5 and e.deptno = dept.deptno;查詢出 king 所在部門的部門號部門名稱部門人數(shù)方法一：select t.countno,d.dname,d.loc fromdept d,-28-(select count(*) countno,deptno fromemp wheredeptno in (select deptno from emp where ename = KIN

53、G) group by deptno) tOracle 教程(jiochng)whered.deptno = t.deptno;方法(fngf)二：select t2.countnum,d.dname,d.loc fromdept d,(select t.countnum,t.deptno from(select count(empno) countnum,deptno from emp group by deptno) t wheret.deptno in (select deptno from emp where lower(ename) = king) t2wheret2.deptno

54、= d.deptno;方法(fngf)三：select e.deptno as 部門號,d.dname as 部門名稱,(select count(*) from (select deptno from emp where deptno in (select deptno from emp where ename=KING) ) as 部門人數(shù)from emp e, dept dwhere e.deptno = d.deptno and e.ename = KING;查詢出 king 所在部門的工作年限最大的員工名字select ename, hiredate from empwhere hi

55、redate in (select min(hiredate) from emp where deptno in (select deptno from emp where ename=KING);查詢出工資成本最高的部門的部門號和部門名稱select d.deptno,d.dname,t.sum_sal fromdept d,(select deptno,sum(sal) sum_sal from emp group by deptno having sum(sal) =(select max(sum(sal) from emp group by deptno) t-29-whered.de

56、ptno = t.deptno;- select d.deptno,d.dname,t.sum_sal fromdept d,Oracle 教程(jiochng)(select * from (select deptno,sum(sal) sum_sal from emp group by deptno order bysum(sal)desc) where rownum=1) t whered.deptno = t.deptno;7.8、 面試題面試題一（廈門）Table: （員工(yungng) emp1）id name1 a2 b3 c4 dTable:( 性別(xngbi) sext)

57、id sex1 男4 女5 男找出忘記填寫性別的員工（用 Oracle 的兩種方式）select id ,name from emp1 e where e.id not in(select id from sext);select id from emp1 minus select id from sext;select * from emp1 e where e.id all(select id from sext);select e.* from emp1 e,(select id from emp1 minus select id from sext) s where e.id = s.

58、id;select e.id, from emp1 e,sext s where e.id=s.id(+) and s.sex is null;select * from emp1 left outer join sext on emp1.id = sext.id where sext.sex is null;select * from emp1 e where not exists(select * from sext s where e.id= s.id);select * from emp1 where id not in (select emp1.id from emp1, sext

59、where emp1.id = sext.id); select name from emp1 where id not in (select id from emp1 intersect select id from sext); SELECT*-30-FROM emp1 eWHERE(SELECT COUNT(*) FROM(SELECT id FROM emp1 UNION ALL SELECT id FROM sext) tWHERE t.id = e.id)=3)ewhereid=e.student_id;面試題五（福州）四張表 學生(xu sheng)表 student（sid,s

60、name)， 教師(jiosh)表 teacher(tid,tname)，課程表 course(cid,cname，ctype)， 選課表 choose_course(ccid,sid,tid,cid)insert into student values(1,小明);insert into student values(2,小花);insert into teacher values(1,陳紅);insert into teacher values(2,陳白);insert into course values(1,語文,文科);insert into course values(2,數(shù)學,理