Oracle培訓教程課件

Oracle教程

2009.5

Louis

佇/倡永國

/ShiwehtwnMnOracle教程

目錄

弓I言.............................................................................3

第一章數(shù)據(jù)庫基礎...............................................................6

第二章Oracle入門...............................................................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

第十六章索引...................................................................56

第十七章序列、同義詞..........................................................65

第十八章PLSQL................................................................................................................................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-

佇/：信永國際

JCinunainlnnvattnn3l

?OlllyuvllHufiWilUllolOracle教程

引言

SUN2008初10億美元收購MySQL

Oracle2009年4月74億美元收購SUN

Sun與Oracle合并的未來

1,如果云計算對企業(yè)來說變得越來越重要，那么數(shù)據(jù)將是云計算的核心。而講到數(shù)據(jù)，也就意味著

數(shù)據(jù)庫。就如塔克商學院數(shù)字策略中心主任M.EricJohnson所說的那樣，Sun已經(jīng)清楚展現(xiàn)了一

個真實的云計算環(huán)境。就那些將云計算停留在理論階段的對手來說，收購Sun對Oracle來說將獲

得競爭優(yōu)勢。

2,Johnson主任還指出，Sun在很多需求旺盛的方面“保持領(lǐng)先地位”。Sun的技術(shù)可以幫助數(shù)

據(jù)庫提高性能，尤其是對大型數(shù)據(jù)庫。Oracle和Sun的結(jié)合之后，對任何個信息官來說都具有吸

引力。

3,Oracle軟件和Java的緊密結(jié)合，使得Oracle的軟件能跨平臺使用。就如IDC研究副總裁

JeanBozman指出那樣，真正云計算的第一步是“將服務器和存儲單元垂直化”，可以適用于不同的

硬件平臺。憑借Java,Oracle可以拓展跨平臺的控制力，而這正是企業(yè)IT部門想要的。實際上，

Oracle的OracleFusionMiddleware正是基于Java?

4,有意思的是，OracleFusionMiddleware擴大了對服務器的需求，其中原因正是由于它是基

于Java。而現(xiàn)在，Oracle通過購得Sun服務器資源，可以進一步降低價格，從而進一步提高Oracle

FusionMiddleware的競爭力。

5,由于Oracle大部分收入來自數(shù)據(jù)庫軟件許可，因此有必要的話，可以用便宜的服務器硬件

作為促銷的手段，這樣對那些硬件服務器競爭對手來說是不小的打擊。

6,Sun已經(jīng)售出160萬到200萬臺服務器，還有更多的運行SunSolaris操作系統(tǒng)的其它公司

的服務器。這對Oracle捆綁銷售自家產(chǎn)品也很有幫助。

7,Oracle收購Sun,當然也包括開源數(shù)據(jù)庫MySQL。對于那些預算不多但又需要Oracle產(chǎn)

品的用戶，Oracle可以用MySQL來滿足這些用戶的需求。這可以使Oracle進入中小企業(yè)業(yè)務，更

有機會在這方面超過SAP。此外，還提供了和微軟SQLServer的競爭產(chǎn)品，而不需要降低Oracle

自有的產(chǎn)品的定位。

-3-

QZ信永國際

Oracle教程

Oracle和Sun的合作可能會影響Oracle和一些合作伙伴的關(guān)系，比如Oracle和惠普的關(guān)系可

能就會出現(xiàn)問題。不過，不像舊事實上，每個硬件公司都繞不開Oracle,因為它是數(shù)據(jù)庫的主

力軍。他們離不開Oracle,因此，他們也只能選擇忍氣吞聲。

而且，與Sun和舊M聯(lián)合不同，Oracle和Sun不存在硬件的沖突，那些Sun的服務器用戶不

必擔心合并會導致放棄自己在使用的服務器產(chǎn)品線。

總而言之，Oracle以相對較低的價格收購Sun是明智之舉，將會使得合并后的Oracle成為各

大公司數(shù)據(jù)中心的樞紐。

本■計\I舁A-A-

李開復(現(xiàn)任Google全球副總裁、中國區(qū)總裁)打了一個很形象的比喻：錢莊。最早人們只是把

錢放在枕頭底下，后來有了錢莊，很安全，不過兌現(xiàn)起來比較麻煩?，F(xiàn)在發(fā)展到銀行可以到任何一

個網(wǎng)點取錢，甚至通過ATM,或者國外的渠道。就像用電不需要家家裝備發(fā)電機，直接從電力公司

購買一樣。“云計算”帶來的就是這樣一種變革一由谷歌、IBM這樣的專業(yè)網(wǎng)絡公司來搭建計算機

存儲、運算中心，用戶通過?根網(wǎng)線借助瀏覽器就可以很方便的訪問，把“云”做為資料存儲以及應

用服務的中心。

GOOGLE

(-)原理:

云計算(CloudComputing)是分布式處理(DistributedComputing)>并行處理(ParallelComput

ing)和網(wǎng)格計算(GridComputing)的發(fā)展，或者說是這些計算機科學概念的商業(yè)實現(xiàn)。

云計算的基本原理是，通過使計算分布在大量的分布式計算機上，而非本地計算機或遠程服務

器中，企業(yè)數(shù)據(jù)中心的運行將更與互聯(lián)網(wǎng)相似。這使得企業(yè)能夠?qū)①Y源切換到需要的應用上，根據(jù)

需求訪問計算機和存儲系統(tǒng)。

這可是一種革命性的舉措，打個比方，這就好比是從古老的單臺發(fā)電機模式轉(zhuǎn)向了電廠集中供

電的模式。它意味著計算能力也可以作為一種商品進行流通，就像煤氣、水電一樣，取用方便，費

用低廉。最大的不同在于，它是通過互聯(lián)網(wǎng)進行傳輸?shù)摹?/p>

云計算的藍圖已經(jīng)呼之欲出：在未來，只需要一臺筆記本或者一個手機，就可以通過網(wǎng)絡服務

來實現(xiàn)我們需要的一切，甚至包括超級計算這樣的任務。從這個角度而言，最終用戶才是云計算的

真正擁有者。

云計算的應用包含這樣的一種思想，把力量聯(lián)合起來，給其中的每一個成員使用。

-4-

佇/泰永國際

^SinyooimsmaitonaiOracle教程

（二）云計算有哪些好處？

1、安全，云計算提供了最可靠、最安全的數(shù)據(jù)存儲中心，用戶不用再擔心數(shù)據(jù)丟失、病毒入侵

等麻煩。

2、方便，它對用戶端的設備要求最低，使用起來很方便。

3、數(shù)據(jù)共享，它可以輕松實現(xiàn)不同設備間的數(shù)據(jù)與應用共享。

4、無限可能，它為我們使用網(wǎng)絡提供了幾乎無限多的可能。

（三）云計算最有利于中小企業(yè)？

云計算技術(shù)將使得中小企業(yè)的成本大大降低。如果說“云”給大型企業(yè)的IT部門帶來了實惠，那

么對于中小型企業(yè)而言，它可算得上是上天的恩賜了。過去，小公司人力資源不足，n?預算吃緊，

那種動輒數(shù)百萬美元的IT設備所帶來的生產(chǎn)力對它們而言真是如夢一般遙遠，而如今，“云”為它們

送來了大企業(yè)級的技術(shù)，并且先期成本極低，升級也很方便。

這一新興趨勢的重要性毋庸置疑，不過，它還僅僅是一系列變革的起步階段而已。云計算不但

抹平了企業(yè)規(guī)模所導致的優(yōu)劣差距，而且極有可能讓優(yōu)劣之勢易主。簡單地說，當今世上最強大最

具革新意義的技術(shù)己不再為大型企業(yè)所獨有?！霸啤弊屆總€普通人都能以極低的成本接觸到頂尖的IT

技術(shù)。

（四）“云"時代

目前，PC依然是我們?nèi)粘９ぷ魃钪械暮诵墓ぞ咭晃覀冇肞C處理文檔、存儲資料，通過電

子郵件或U盤與他人分享信息。如果PC硬盤壞了，我們會因為資料丟失而束手無策。

而在“云計算"時代，“云”會替我們做存儲和計算的工作?！霸啤本褪怯嬎銠C群，每一群包括了幾十

萬臺、甚至上百萬臺計算機?！霸啤钡暮锰庍€在于，其中的計算機可以隨時更新，保證“云”長生不老。

Google就有好幾個這樣的"云"，其他IT巨頭，如微軟、雅虎、亞馬遜（Amazon）也有或正在建設

這樣的“云”。

屆時，我們只需要一臺能上網(wǎng)的電腦，不需關(guān)心存儲或計算發(fā)生在哪朵“云”上，但一旦有需要，

我們可以在任何地點用任何設備，如電腦、手機等，快速地計算和找到這些資料。我們再也不用擔

心資料丟失。

-5-

佇/：信永國際

^SinyeeimemaitenalOracle教程

第一章數(shù)據(jù)庫基礎

1.1、數(shù)據(jù)庫基本概念

數(shù)據(jù)庫(Database,DB)

數(shù)據(jù)庫管理系統(tǒng)(DatabaseManagementSystem,DBMS)

數(shù)據(jù)庫管理員(DatabaseAdministrator,DBA)

數(shù)據(jù)庫系統(tǒng)(DatabaseSystem,DBS)

1.2、關(guān)系型數(shù)據(jù)庫

Q:目前都有哪些主流的關(guān)系型數(shù)據(jù)庫

A:OracleOralce>IBMDB2、MSSQL/Server>SyBaseSyBase>IBMInformix>MySQL>

Access

Q: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-

LarryEllison一生最大的目標，"財富榜超過BillGate"

BruceScott已離開Oralce,創(chuàng)建了一套新的數(shù)據(jù)庫PointBase

2.2、Oracle特點

全球化、跨平臺的數(shù)據(jù)庫

支持多用戶、高性能的事務處理

強大的安全性控制和完整性控制

支持分布式數(shù)據(jù)庫和分布處理

2.3、Oracle版本

Oracle8i:Iinternet表示Oracle公司要開始正式進入互聯(lián)網(wǎng)

Oracle"與Oracle8i相關(guān)，性能方面更佳，管理更人性化

Oracle10g:g(grid)網(wǎng)格技術(shù)

Oracle11g:g(grid)網(wǎng)格技術(shù)

Q：何為網(wǎng)格技術(shù)？

2009年1月

淘寶網(wǎng)決定采用Oracle網(wǎng)格計算(GridComputing)架構(gòu)來，采用Oracle數(shù)據(jù)庫和Oracle真正

應用集群來重新打造并強化其基礎架構(gòu)和數(shù)據(jù)倉庫環(huán)境

2.4、安裝Oracle數(shù)據(jù)庫

注意：

1、安裝的時候，一定要關(guān)掉防火墻。否則可能造成安裝不成功

-7-

信永國新

Oracle教程

2、全局數(shù)據(jù)庫名SID,類似于MYSQL中常用的localhost.

3、字符集一定要選擇正確。一旦選錯，除非更改成該字符集的父類。否則只能重裝

4、安裝完主要的用戶為：

a）普通用戶：Scott/tiger（練習常用）

b）普通管理員：System/system

c）超級管理員：Sys/sys

5、,安裝完后的服務配置（運行中輸入：services.msc）

^^OraclefilTSRecoveryService巳禁用本地系統(tǒng)

OracleOraKome92Agent已禁用本地系統(tǒng)

OracleOraHome92ClientCache已禁用本地系統(tǒng)

OracleOraJfome92KTTPServer已禁用本地系統(tǒng)

OracleOraHome92PagingServer已禁用本地系統(tǒng)

爆OracleOraMome92SNMPPeerEncapsillator已禁用本地系統(tǒng)

用^OracleOraHome92SNMPPeerMasterAgent己禁用本地系統(tǒng)

OracleOraHome92TNSListener巳啟動手動本地系統(tǒng)

嚼OracleServiceCABE已啟動手動本地系統(tǒng)

開啟該服務，會占用tomcat的8080端口。

查看端口號：tasklistlfindstr"8080"

如果裝完Oracle后，又改了機器名可能會導致Listener服務無法啟動，解決方式：

修改C:\oracle\ora92\network\admin\listener.ora下的HOST

2.5、卸載Oracle數(shù)據(jù)庫

■卸載步驟：

1.停止Oracle所有服務

2.運行OracleUniversalInstaller卸載Oracle

3.修改注冊表，刪除Oracle相關(guān)信息

?Oracle軟件有關(guān)鍵-值

HKEY__LOCAL__MACHINE\SOFTWAREOracle

,Oracle服務

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Senices

,Oracle事件日志

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Evendog\App

lication

4.刪除Oracle系統(tǒng)目錄C：'programfiles\oralce

5.刪除Oracle環(huán)境變量

6.刪除程序菜單項中的Oracle菜單

7.（重啟系統(tǒng)后）刪除Oracle工作主FI錄DAoralce

-8-

佇/偃永國際

^sinyeehrtemaikmalOracle教程

2.6、Oracle目錄(Admin,oracle92,oradata)

三一本地嫌猛?)

國a

DocumentsandSettings

?e

DRIVERS

田O

Intel

日Q

oracle

國liTadmin

國iQora92

oradata

Q:這三個文件夾各存放什么文件?

2.7、Oracle客戶端工具

2.7.1、SQLPLUS

2.7.2、SQLPLUSW

2.7.3、SqlPlusWorksheet

2.7.4、iSqlplus(HTTP)

http://localhost:7778/isqlplus

2.7.5、PL/SQLDeveloper

7.14以上版本，帶有自動提示功能

2.7.6、ObjectBrowser

國外用的比較多(日本)

-9-

佇，信永國際

XStawMiminiMMOracle教程

2.8、Scott案例下表分析

281、雇員表：EMP

雇員表(EMP)

No.字段類型描述

1EMPNONUMBERS)表示雇員編號，是唯一編號

2ENAMEVARCHAR2(10)表示雇員姓名

JOBVARCHAR2(9)表示工作職位

4MGRNUMBERS)表示一個雇員的領(lǐng)導編號

5HIREDATEDATE表示雇傭日期

6SALNUMBER(7,2)表示月薪,工資

7COMMNUMBER(7.2)表示獎金,或者稱為何金

8DEPTNONLMBER(2)部門編號

2.8.2、部門表：D叩t

部門表(dept)

No.字段類型描述

1DEPTNO\LMBER(2)部門編號，是唯一編號

2DNAMEVARCHAR2(14)部門名稱

3LOCVARCHAR2(13)部門位置

2.8.3、工資等級表:Salgrade

工資等級表(SALGRADE)

No.字段類型描述

1GRADENUMBER等級名稱

2LOSALNUMBER此等級的最低工費

3HISALNUMBER此等級的最高工資

-10-

佇，信永國際

SX‘Sinyeei而贏rtiMBlOracle教程

2.8.4、獎金表：Bonus

獎金表（BONUS）

No.字段類型描述

1ENAMEVARCHAR2(10)雇員姓名

2JOBVARCHAR2(9)雇員工作

3SALNUMBER雇員工資

4COMMNUMBER雇員獎金（仰金）

第三章查詢基礎

3.1、入門語句

普通用戶連接：Connscott/tiger

超級管理員連接：Conn“sys/sysassysdba”

Disconnect;斷開連接

Savec:\l.txt把SQL存到文件

Edc:\l.txt編輯SQL語句

@c:\l.txt運行SQL語句

Descemp;描述Emp結(jié)構(gòu)

Select*fromtab;查看該用戶下的所有對象

Showuser;顯示當前用戶

如果在sys用戶下：查詢Select*fromemp;會報錯，原因：emp是屬于scott,所以此時必

須使用：select*fromscott.emp;

/運行上一條語句

3.2xSQL簡介

SQL全名是結(jié)構(gòu)化查詢語言（StructuredQueryLanguage）,是用于數(shù)據(jù)庫中的標準數(shù)據(jù)查

詢語言，IBM公司最早使用在其開發(fā)的數(shù)據(jù)庫系統(tǒng)中。1986年10月，美國ANSI對SQL

進行規(guī)范后，以此作為關(guān)系式數(shù)據(jù)庫管理系統(tǒng)的標準語言（ANSIX3.135-1986）,1987年

得到國際標準組織的支持下成為國際標準。不過各種通行的數(shù)據(jù)庫系統(tǒng)在其實踐過程中都

對SQL規(guī)范作了某些編改和擴充。所以，實際上不同數(shù)據(jù)庫系統(tǒng)之間的SQL語言不能完

全相互通用

DML語句（數(shù)據(jù)操作語言）Insert、Update、Delete>Merge

DDL語句（數(shù)據(jù)定義語言）Create>Alter、Drop>Truncate

DCL語句（數(shù)據(jù)控制語言）Grant、Revoke

事務控制語句Commit>Rollback>Sav叩oint

-11-

佇/倡永國

>SinyoeimamatOracle教程

3.3、簡單的Select語句

3.4、使用算術(shù)表達式+-/*

3.5、連接運算符||

3.6、使用字段別名as

3.7、空值isnull

3.8、去除重復行distinct

3.9、查詢結(jié)果排序orderbyasc(desc)

3.10、比較運算符>v(!=or<>)betweenand

3.11xin操作notin

3.12、模糊查詢like

%表示零或多個字符

_表示一個字符

對于特殊符號可使用ESCAPE標識符來查找

select*fromempwhereenamelike'%*_%'escape

上面的escape表示*后面的那個符號不當成特殊字符處理，就是查找普通的_符號

3.13、邏輯運算符orandnot

3.14、練習

選擇在部門30中員工的所有信息

Select*fromempwheredeptno=30;

-12-

佇/倡永國

>SinyoeimamatOracle教程

列出職位為(MANAGER)的員工的編號，姓名

Selectempno,enamefromempwherejob='Manager';

找出獎金高于工資的員工

Select*fromempwherecomm>sal;

找出每個員工獎金和工資的總和

Selectsal+comm,enamefromemp;

找出部門10中的經(jīng)理(MANAGER)和部門20中的普通員工(CLERK)

Select*fromempwhere(deptno=10andjob=,MANAGER,)or(deptno=20andjob='CLERK');

找出部門10中既不是經(jīng)理也不是普通員工，而且工資大于等于2000的員工

Select*fromempwheredeptno=10andjobnotin(€MANAGER,,,CLERK)'andsal>=2000;

找出有獎金的員工的不同工作

Selectdistinctjobfromempwherecommisnotnullandcomm>0

找出沒有獎金或者獎金低于500的員工

Select*fromempwherecomm<500orcommisnull;

顯示雇員姓名，根據(jù)其服務年限，將最老的雇員排在最前面

selectenamefromemporderbyhiredate;

-13-

”:信永國窿

"sinyeeIntenurtio而Oracle教程

第四章單行函數(shù)

4.1、字符函數(shù)

Upper

SELECTUpper('abcde')FROMdual;

SELECT*FROMempWHEREename=UPPER('smith');

Lower

SELECTlower('ABCDE')FROMdual;

Initcap

Selectinitcap(ename)fromemp;

Concat

Selectconcat(4a,,,b,)fromdual;

Select4a5II'b'fromdual;

Substr

Selectsubstr(€abcde5,length(4abcde?)-2)fromdual;

Selectsubstr(4abcde3,3)fromdual;

Length

Selectlength(ename)fromemp;

Replace

Selectreplace(ename,'a','A')fromemp;

-14-

佇/偃永國際

^SinyeeimamailsnalOracle教程

Instr

SelectinstrCHelloWorld；"')fromdual;8indexof

Lpad

lpad('Smith',10,'*')左側(cè)填充lpad()*****Smith

Rpad

rpad('Smith*,10/*1)右側(cè)填充rpad()Smith*****

Trim

trim(*MrSmith*)過濾首尾空格trim。MrSmith

4.2、數(shù)值函數(shù)

Round

selectround(412z-2)fromdual;

selectround(412.313,2)fromdual;

Mod

Trunc

selecttrunc(412.13,-2)fromdual;

4.3、日期函數(shù)

Months_between()

selectmonths_between(sysdate,hiredate)fromemp;

Add_months()

selectadd_months(sysdate,1)fromdual;

-15-

佇/倡永國

>SinyoeimamatOracle教程

Next_day()

selectnexjday(sysdate,'星期')fromdual;

Last_day

selectlast_day(sysdate)fromdual;

4.4、轉(zhuǎn)換函數(shù)

lb_char

selectto_char(sysdate,'yyyy1)fromdual;

selectto_char(sysdate,1fmyyyy-mm-ddf)fromdual;

selectto_char(salJL999,999,999')fromemp;

selectto_char(sysdate,'Dz)fromdual;//返回星期

lb_number

selectto_number(*131)+to_number(1141)fromdual;

To_date

Selectto_date(4200902yyyyMMdd?)fromdual;

4.5、通用函數(shù)

NVL()函數(shù)

selectnvl(commf0)fromemp;

NULLIFO函數(shù)

如果表達式expl與exp2的值相等則返回null,否則

返回expl的值

NVL2()函數(shù)

selectempno,ename,sal,comm,nv!2(comm,sal+comm,sal)totalfromemp;

-16-

佇/倡永國

>SinyoeimamatOracle教程

COALESCE。函數(shù)

依次考察各參數(shù)表達式，遇到非null值即停止并返

回該值。

selectempno,ename,sal,comm,coalesce(sal+comm,sal,0)總收入fromemp;

CASE表達式

selectempno,ename,sal,

casedeptno

when10then'財務部，

when20then'研發(fā)部，

when30Ihen銷售部，

else沫知部門,

end部門

fromemp;

DECODE。函數(shù)

和case表達式類似，decode。函數(shù)也用于實現(xiàn)多路分支結(jié)構(gòu)

selectempno,ename,sal,

decode(deptno,10,‘財務部'，

20J研發(fā)部;

30,銷售部二

‘未知部門')

部門

fromemp;

decode與case哪個更好用呢？

單行函數(shù)嵌套

selectempno,lpad(initcap(trim(ename)),10/')namejob,salfromemp;

4.6、練習

找出每個月倒數(shù)第三天受雇的員工(如：2009-5-29)

select*fromempwherelast_day(hiredate)-2=hiredate;

-17-

Js而》imanMMOracle教程

找出25年前雇的員工

select*fromempwherehiredate<=add_months(sysdatez-25*12);

所有員工名字前加上Dear,并且名字首字母大寫

select*Dear*||initcap(ename)fromemp;

找出姓名為5個字母的員工

select*fromempwherelength(ename)=5;

找出姓名中不帶R這個字母的員工

select*fromempwhereenamenotlike1%R%1;

顯示所有員工的姓名的第一個字

selectsubstr(ename,0,1)fromemp;

顯示所有員工，按名字降序排列，若相同，則按工資升序排序

假設一個月為30天，找出所有員工的日薪，不計小數(shù)

找到2月份受雇的員工

select*fromempwhereto_char(hiredate,1fmnun1)=,2*;

列出員工加入公司的天數(shù)(四舍五入)

分別用case和decode函數(shù)列出員工所在的部門，deplno=10顯示部門10',

deptno=20顯示，部門20,

deptno=30顯示'部門301

deptno=40顯示，部門401

否則為‘其他部門’

-18-

佇，信永國際

^SinyeeimsmaiienalOracle教程

第五章分組函數(shù)

5.1、COUNT

如果數(shù)據(jù)庫表的沒有數(shù)據(jù)，count(*)返回的不是null,而是0

5.2、Avg,max,min,sum

5.3、分組函數(shù)與空值

分組函數(shù)省略列中的空值

selectavg(comm)fromemp;

selectsum(comm)fromemp;

可使用NVL()函數(shù)強制分組函數(shù)處理空值

selectavg(nvl(comm,0))fromemp;

5.4、GROUPBY子句

出現(xiàn)在SELECT列表中的字段或者出現(xiàn)在orderby后面的字段，如果不是包含在分組函數(shù)

中，那么該字段必須同時在GROUPBY子句中出現(xiàn)。

包含在GROUPBY子句中的字段則不必須出現(xiàn)在SELECT列表中。

可使用where字句限定查詢條件

可使用Orderby子句指定排序方式

如果沒有GROUPBY子句，SELECT列表中不允許出現(xiàn)字

段(單行函數(shù))與分組函數(shù)混用的情況。

selectempno,salfromemp;〃合法

selectavg(sal)fromemp;〃合法

selectempno,initcap(ename),avg(sal)fromemp;〃非法

不允許在WHERE子句中使用分組函數(shù)。

selectdeptno,avg(sal)

fromemp

whereavg(sal)>2000;

groupbydeptno;

5.5、HAVING子句

selectdeptno,job,avg(sal)

fromemp

-19-

佇/倡永國

>SinyoeimamatOracle教程

wherehiredate>=to_date(11981-05-01'/yvyy-mm-dd1)

groupbydeptno,job

havingavg(sal)>1200

orderbydeptnojob;

5.6、分組函數(shù)嵌套

selectmax(avg(sal))

fromemp

groupbydeptno;

5.7v練習

分組統(tǒng)計各部門下工資>500的員工的平均工資、

Selectavg(sal)fromempwheresal>500groupbydeptno;

統(tǒng)計各部門下平均工資大于500的部門

selectdeptno,avg(sal)fromempgroupbydeptnohavingavg(sal)>500;

算出部門30中得到最多獎金的員工獎金

Selectmax(comm)fromempwheredeptno=30;

算出部門30中得到最多獎金的員工姓名

selectenamefromempwherecomm=(selectmax(comm)fromempwhere

deptno=30);

算出每個職位的員工數(shù)和最低工資

Selectjobrmin(sal),count(*)fromempgroupbyjob;

-20-

Js而》imanMMOracle教程

算出每個部門,，每個職位的平均工資和平均獎金(平均值包括沒有獎金)，如果平均獎金大于300,顯示“獎

金不錯”，如果平均獎金100到300,顯示“獎金一般”，如果平均獎金小于100,顯示“基本沒有獎金”,

按部門編號降序，平均工資降序排列

Selectavg(sal),avg(nvl(comm.,0))casewhenavg(nvl(comm.,0))>300then，獎金不錯'

whenavg(nvl(comm.,0))<100andavg(nvl(comm,0))>300then，獎金不錯’end獎金狀況

fromempgroupbyjoborderbyjobdesc,avg(sal)desc;

列出員工表中每個部門的員工數(shù)，和部門no

Selectcount(*),deptnofromempgroupbydeptno;

得到工資大于自己部門平均工資的員工信息

select*fromempel,(selectdeptno,avg(sal)asavgsalfromempgroupbydeptno)e2

whereel.deptno=e2.deptnoandel.sal>e2.avgsal;

分組統(tǒng)計每個部門下，每種職位的平均獎金(也要算沒獎金的人)和總工資(包括獎金)

selectdeptnojob,avg(nvl(comm,0)),sum(sal+nvl(comm,0))fromempgroupbydeptno,job;

第六章多表查詢

6.1x笛卡爾集(CrossJoin)

Select*fromemp,dept;

6.2、等值連接(Equijoin)(Naturaljoin..on)

selectempno,ename,sal,emp.deptno,dnamefromemp,dept

whereemp.deptno=dept.deptno;

6.3、非等值連接(Non-Equijoin)

selectename,empno,gradefromemp,salgradewheresalbetweenlosaland

hisal;

-21-

佇/倡永國

>SinyoeimamatOracle教程

6.4、自連接(Selfjoin)

selecte.empnoze.enamerm.empnorm.enamefromempe,empmwheree.mgr=

m.empno;

selecte.empnoze.ename,m.empnorm.enamefromemperempmwherem.mgr=

e.empno;

6.5、左外聯(lián)接(LeftOuterJoin)

selects.sidrs.sname,si.sid,si.snamefromstudents,studentlsiwhere

s.sid=sl.sid(+);

selectempno,ename,dnamefromempleftouterjoindeptonemp.deptno=

dept.deptno;

6.6、右外聯(lián)接(RightOuterJoin)

selects?sid,s?sname,si?sid,si?snamefromstudents,studentlsiwhere

s?sid(+)=sl.sid;

selectempno,ename,dnamefromemprightouterjoindeptonemp.deptno

=dept.deptno;

6.7、滿外聯(lián)接(FullOuterJoin)

selectempno,enamezdnamefromempfullouterjoindeptonemp.deptno=

dept.deptno;

12

24

36

4

5

內(nèi)連接

22

44

-22-

佇/倡永國

>SinyoeimamatOracle教程

滿連接

1

22

3

44

5

6

左連接

1

22

3

44

5

右連接

22

44

6

6.8、集合操作

?UNION：并集，所有的內(nèi)容都查詢，重復的顯示一次

?UNIONALL：并集，所有的內(nèi)容都顯示，包括重復的

?INTERSECT：交集：只顯示重復的

■MINUS：差集：只顯示對方?jīng)]有的（跟順序是有關(guān)系的）

首先建立一張只包含20部門員工信息的表：

CREATETABLEemp20ASSELECT*FROMempWHEREdeptno=20;

1、驗證UNION及UNIONALL

UNION：SELECT*FROMempUNIONSELECT*FROMemp20;

使用此語句重復的內(nèi)容不再顯示了

UNIONALL：SELECT*FROMempUNIONALLSELECT*FROMemp20;

重復的內(nèi)容依然顯示

2、驗證INTERSECT

SELECT*FROMempINTERSECTSELECT*FROMemp20;

只顯示了兩個表中彼此重復的記錄。

3、MINUS：返回差異的記錄

SELECT*FROMempMINUSSELECT*FROMemp20;

只顯示了兩張表中的不同記錄

-23-

Oracle教程

滿鏈接也可以用以下的方式來表示：

selecttl.id,t2.idfromtable1tl,tablet2wheretl.id=t2.id(+)

union

selecttl.id,t2.idfromtable1tl,tablet2wheretl.id(+)=t2.id

第七章子查詢

7.1、單行子查詢

select*fromemp

wheresal>(selectsalfromempwhereempno=7566);

7.2、子查詢空值/多值問題

如果子查詢未返回任何行，則主查詢也不會返回任何結(jié)果

(空值)select*fromempwheresal>(selectsalfromempwhereempno=8888);

如果子查詢返回單行結(jié)果，則為單行子查詢，可以在主查

詢中對其使用相應的單行記錄比較運算符

(正常)select*fromempwheresal>(selectsalfromempwhereempno=7566);

如果子查詢返回多行結(jié)果，則為多行子查詢，此時不允許

對其使用單行記錄比較運算符

(多值)select*fromempwheresal>(selectavg(sal)fromempgroupbydeptno)，非法

7.3、多行子查詢

select*fromempwheresal>any(selectavg(sal)fromempgroupbydeptno);

select*fromempwheresal>all(selectavg(sal)fromempgroupbydeptno);

select*fromempwherejobin(selectjobfromempwhereename='MARTIN'orename='SMITH);

7.4、TopN查詢

select*fromempwhererownum=lorrownum=2;

select*

-24-

佇/倡永國

>SinyoeimamatOracle教程

from(select*fromemporderbysaldesc)

whererownum<=5;

Q:如何理解(select*fromempwhererownum<=5orderb