Oracle培訓(xùn)教程課件

Oracle教程

2009.5

Louis

佇/倡永國(guó)

/ShiwehtwnMnOracle教程

目錄

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

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

第二章Oracle入門...............................................................6

第三章查詢基礎(chǔ).................................................................11

第四章單行函數(shù).................................................................14

第五章分組函數(shù).................................................................19

第六章多表查詢................................................................21

第七章子查詢...................................................................24

第八章高級(jí)查詢................................................................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

第十九章游標(biāo)、函數(shù)............................................................79

第二十章存儲(chǔ)過(guò)程..............................................................86

第二十一章觸發(fā)器..............................................................90

第二十二章事務(wù)（數(shù)據(jù)庫(kù)系統(tǒng)概論）.............................................99

第二十三章用戶管理............................................................100

第二十四章備份恢復(fù)SQLLoader...............................................................................................104

第二十五章數(shù)據(jù)庫(kù)設(shè)計(jì)范式.....................................................106

第二十六章數(shù)據(jù)庫(kù)設(shè)計(jì)工具.....................................................107

第二十七章對(duì)象關(guān)系數(shù)據(jù)庫(kù)系統(tǒng).................................................112

第二十八章其他數(shù)據(jù)庫(kù)..........................................................113

-2-

佇/：信永國(guó)際

JCinunainlnnvattnn3l

?OlllyuvllHufiWilUllolOracle教程

引言

SUN2008初10億美元收購(gòu)MySQL

Oracle2009年4月74億美元收購(gòu)SUN

Sun與Oracle合并的未來(lái)

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

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

個(gè)真實(shí)的云計(jì)算環(huán)境。就那些將云計(jì)算停留在理論階段的對(duì)手來(lái)說(shuō)，收購(gòu)Sun對(duì)Oracle來(lái)說(shuō)將獲

得競(jìng)爭(zhēng)優(yōu)勢(shì)。

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

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

引力。

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

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

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

Oracle的OracleFusionMiddleware正是基于Java?

4,有意思的是，OracleFusionMiddleware擴(kuò)大了對(duì)服務(wù)器的需求，其中原因正是由于它是基

于Java。而現(xiàn)在，Oracle通過(guò)購(gòu)得Sun服務(wù)器資源，可以進(jìn)一步降低價(jià)格，從而進(jìn)一步提高Oracle

FusionMiddleware的競(jìng)爭(zhēng)力。

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

作為促銷的手段，這樣對(duì)那些硬件服務(wù)器競(jìng)爭(zhēng)對(duì)手來(lái)說(shuō)是不小的打擊。

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

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

7,Oracle收購(gòu)Sun,當(dāng)然也包括開(kāi)源數(shù)據(jù)庫(kù)MySQL。對(duì)于那些預(yù)算不多但又需要Oracle產(chǎn)

品的用戶，Oracle可以用MySQL來(lái)滿足這些用戶的需求。這可以使Oracle進(jìn)入中小企業(yè)業(yè)務(wù)，更

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

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

-3-

QZ信永國(guó)際

Oracle教程

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

能就會(huì)出現(xiàn)問(wèn)題。不過(guò)，不像舊事實(shí)上，每個(gè)硬件公司都繞不開(kāi)Oracle,因?yàn)樗菙?shù)據(jù)庫(kù)的主

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

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

必?fù)?dān)心合并會(huì)導(dǎo)致放棄自己在使用的服務(wù)器產(chǎn)品線。

總而言之，Oracle以相對(duì)較低的價(jià)格收購(gòu)Sun是明智之舉，將會(huì)使得合并后的Oracle成為各

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

本■計(jì)\I舁A-A-

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

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

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

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

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

用服務(wù)的中心。

GOOGLE

(-)原理:

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

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

云計(jì)算的基本原理是，通過(guò)使計(jì)算分布在大量的分布式計(jì)算機(jī)上，而非本地計(jì)算機(jī)或遠(yuǎn)程服務(wù)

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

需求訪問(wèn)計(jì)算機(jī)和存儲(chǔ)系統(tǒng)。

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

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

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

云計(jì)算的藍(lán)圖已經(jīng)呼之欲出：在未來(lái)，只需要一臺(tái)筆記本或者一個(gè)手機(jī)，就可以通過(guò)網(wǎng)絡(luò)服務(wù)

來(lái)實(shí)現(xiàn)我們需要的一切，甚至包括超級(jí)計(jì)算這樣的任務(wù)。從這個(gè)角度而言，最終用戶才是云計(jì)算的

真正擁有者。

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

-4-

佇/泰永國(guó)際

^SinyooimsmaitonaiOracle教程

（二）云計(jì)算有哪些好處？

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

等麻煩。

2、方便，它對(duì)用戶端的設(shè)備要求最低，使用起來(lái)很方便。

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

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

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

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

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

那種動(dòng)輒數(shù)百萬(wàn)美元的IT設(shè)備所帶來(lái)的生產(chǎn)力對(duì)它們而言真是如夢(mèng)一般遙遠(yuǎn)，而如今，“云”為它們

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

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

抹平了企業(yè)規(guī)模所導(dǎo)致的優(yōu)劣差距，而且極有可能讓優(yōu)劣之勢(shì)易主。簡(jiǎn)單地說(shuō)，當(dāng)今世上最強(qiáng)大最

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

技術(shù)。

（四）“云"時(shí)代

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

子郵件或U盤與他人分享信息。如果PC硬盤壞了，我們會(huì)因?yàn)橘Y料丟失而束手無(wú)策。

而在“云計(jì)算"時(shí)代，“云”會(huì)替我們做存儲(chǔ)和計(jì)算的工作?！霸啤本褪怯?jì)算機(jī)群，每一群包括了幾十

萬(wàn)臺(tái)、甚至上百萬(wàn)臺(tái)計(jì)算機(jī)。“云”的好處還在于，其中的計(jì)算機(jī)可以隨時(shí)更新，保證“云”長(zhǎng)生不老。

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

這樣的“云”。

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

我們可以在任何地點(diǎn)用任何設(shè)備，如電腦、手機(jī)等，快速地計(jì)算和找到這些資料。我們?cè)僖膊挥脫?dān)

心資料丟失。

-5-

佇/：信永國(guó)際

^SinyeeimemaitenalOracle教程

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

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

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

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

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

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

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

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

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

Access

Q:XML,TXT可以做為數(shù)據(jù)庫(kù)嗎？

1.3、E-R模型(Entry-Relation)

E-R模型三要素：實(shí)體、關(guān)系、屬性

實(shí)體間聯(lián)系(1:1)(1：n)(n:m)

Q:學(xué)生與課程什么關(guān)系？

第二章Oracle入門

2.1、Oracle概述

甲骨文，四大創(chuàng)始人

-6-

LarryEllison一生最大的目標(biāo)，"財(cái)富榜超過(guò)BillGate"

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

2.2、Oracle特點(diǎn)

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

支持多用戶、高性能的事務(wù)處理

強(qiáng)大的安全性控制和完整性控制

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

2.3、Oracle版本

Oracle8i:Iinternet表示Oracle公司要開(kāi)始正式進(jìn)入互聯(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)格計(jì)算(GridComputing)架構(gòu)來(lái)，采用Oracle數(shù)據(jù)庫(kù)和Oracle真正

應(yīng)用集群來(lái)重新打造并強(qiáng)化其基礎(chǔ)架構(gòu)和數(shù)據(jù)倉(cāng)庫(kù)環(huán)境

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

注意：

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

-7-

信永國(guó)新

Oracle教程

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

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

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

a）普通用戶：Scott/tiger（練習(xí)常用）

b）普通管理員：System/system

c）超級(jí)管理員：Sys/sys

5、,安裝完后的服務(wù)配置（運(yùn)行中輸入：services.msc）

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

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

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

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

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

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

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

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

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

開(kāi)啟該服務(wù)，會(huì)占用tomcat的8080端口。

查看端口號(hào)：tasklistlfindstr"8080"

如果裝完Oracle后，又改了機(jī)器名可能會(huì)導(dǎo)致Listener服務(wù)無(wú)法啟動(dòng)，解決方式：

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

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

■卸載步驟：

1.停止Oracle所有服務(wù)

2.運(yùn)行OracleUniversalInstaller卸載Oracle

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

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

HKEY__LOCAL__MACHINE\SOFTWAREOracle

,Oracle服務(wù)

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.刪除程序菜單項(xiàng)中的Oracle菜單

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

-8-

佇/偃永國(guó)際

^sinyeehrtemaikmalOracle教程

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

三一本地嫌猛?)

國(guó)a

DocumentsandSettings

?e

DRIVERS

田O

Intel

日Q

oracle

國(guó)liTadmin

國(guó)iQora92

oradata

Q:這三個(gè)文件夾各存放什么文件?

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以上版本，帶有自動(dòng)提示功能

2.7.6、ObjectBrowser

國(guó)外用的比較多(日本)

-9-

佇，信永國(guó)際

XStawMiminiMMOracle教程

2.8、Scott案例下表分析

281、雇員表：EMP

雇員表(EMP)

No.字段類型描述

1EMPNONUMBERS)表示雇員編號(hào)，是唯一編號(hào)

2ENAMEVARCHAR2(10)表示雇員姓名

JOBVARCHAR2(9)表示工作職位

4MGRNUMBERS)表示一個(gè)雇員的領(lǐng)導(dǎo)編號(hào)

5HIREDATEDATE表示雇傭日期

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

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

8DEPTNONLMBER(2)部門編號(hào)

2.8.2、部門表：D叩t

部門表(dept)

No.字段類型描述

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

2DNAMEVARCHAR2(14)部門名稱

3LOCVARCHAR2(13)部門位置

2.8.3、工資等級(jí)表:Salgrade

工資等級(jí)表(SALGRADE)

No.字段類型描述

1GRADENUMBER等級(jí)名稱

2LOSALNUMBER此等級(jí)的最低工費(fèi)

3HISALNUMBER此等級(jí)的最高工資

-10-

佇，信永國(guó)際

SX‘Sinyeei而贏rtiMBlOracle教程

2.8.4、獎(jiǎng)金表：Bonus

獎(jiǎng)金表（BONUS）

No.字段類型描述

1ENAMEVARCHAR2(10)雇員姓名

2JOBVARCHAR2(9)雇員工作

3SALNUMBER雇員工資

4COMMNUMBER雇員獎(jiǎng)金（仰金）

第三章查詢基礎(chǔ)

3.1、入門語(yǔ)句

普通用戶連接：Connscott/tiger

超級(jí)管理員連接：Conn“sys/sysassysdba”

Disconnect;斷開(kāi)連接

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

Edc:\l.txt編輯SQL語(yǔ)句

@c:\l.txt運(yùn)行SQL語(yǔ)句

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

Select*fromtab;查看該用戶下的所有對(duì)象

Showuser;顯示當(dāng)前用戶

如果在sys用戶下：查詢Select*fromemp;會(huì)報(bào)錯(cuò)，原因：emp是屬于scott,所以此時(shí)必

須使用：select*fromscott.emp;

/運(yùn)行上一條語(yǔ)句

3.2xSQL簡(jiǎn)介

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

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

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

得到國(guó)際標(biāo)準(zhǔn)組織的支持下成為國(guó)際標(biāo)準(zhǔn)。不過(guò)各種通行的數(shù)據(jù)庫(kù)系統(tǒng)在其實(shí)踐過(guò)程中都

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

全相互通用

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

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

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

事務(wù)控制語(yǔ)句Commit>Rollback>Sav叩oint

-11-

佇/倡永國(guó)

>SinyoeimamatOracle教程

3.3、簡(jiǎn)單的Select語(yǔ)句

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

3.5、連接運(yùn)算符||

3.6、使用字段別名as

3.7、空值isnull

3.8、去除重復(fù)行distinct

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

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

3.11xin操作notin

3.12、模糊查詢like

%表示零或多個(gè)字符

_表示一個(gè)字符

對(duì)于特殊符號(hào)可使用ESCAPE標(biāo)識(shí)符來(lái)查找

select*fromempwhereenamelike'%*_%'escape

上面的escape表示*后面的那個(gè)符號(hào)不當(dāng)成特殊字符處理，就是查找普通的_符號(hào)

3.13、邏輯運(yùn)算符orandnot

3.14、練習(xí)

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

Select*fromempwheredeptno=30;

-12-

佇/倡永國(guó)

>SinyoeimamatOracle教程

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

Selectempno,enamefromempwherejob='Manager';

找出獎(jiǎng)金高于工資的員工

Select*fromempwherecomm>sal;

找出每個(gè)員工獎(jiǎng)金和工資的總和

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;

找出有獎(jiǎng)金的員工的不同工作

Selectdistinctjobfromempwherecommisnotnullandcomm>0

找出沒(méi)有獎(jiǎng)金或者獎(jiǎng)金低于500的員工

Select*fromempwherecomm<500orcommisnull;

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

selectenamefromemporderbyhiredate;

-13-

”:信永國(guó)窿

"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-

佇/偃永國(guó)際

^SinyeeimamailsnalOracle教程

Instr

SelectinstrCHelloWorld；"')fromdual;8indexof

Lpad

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

Rpad

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

Trim

trim(*MrSmith*)過(guò)濾首尾空格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-

佇/倡永國(guó)

>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ù)

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

返回expl的值

NVL2()函數(shù)

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

-16-

佇/倡永國(guó)

>SinyoeimamatOracle教程

COALESCE。函數(shù)

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

回該值。

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

CASE表達(dá)式

selectempno,ename,sal,

casedeptno

when10then'財(cái)務(wù)部，

when20then'研發(fā)部，

when30Ihen銷售部，

else沫知部門,

end部門

fromemp;

DECODE。函數(shù)

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

selectempno,ename,sal,

decode(deptno,10,‘財(cái)務(wù)部'，

20J研發(fā)部;

30,銷售部二

‘未知部門')

部門

fromemp;

decode與case哪個(gè)更好用呢？

單行函數(shù)嵌套

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

4.6、練習(xí)

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

select*fromempwherelast_day(hiredate)-2=hiredate;

-17-

Js而》imanMMOracle教程

找出25年前雇的員工

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

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

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

找出姓名為5個(gè)字母的員工

select*fromempwherelength(ename)=5;

找出姓名中不帶R這個(gè)字母的員工

select*fromempwhereenamenotlike1%R%1;

顯示所有員工的姓名的第一個(gè)字

selectsubstr(ename,0,1)fromemp;

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

假設(shè)一個(gè)月為30天，找出所有員工的日薪，不計(jì)小數(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-

佇，信永國(guó)際

^SinyeeimsmaiienalOracle教程

第五章分組函數(shù)

5.1、COUNT

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

5.2、Avg,max,min,sum

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

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

selectavg(comm)fromemp;

selectsum(comm)fromemp;

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

selectavg(nvl(comm,0))fromemp;

5.4、GROUPBY子句

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

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

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

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

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

如果沒(méi)有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-

佇/倡永國(guó)

>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練習(xí)

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

Selectavg(sal)fromempwheresal>500groupbydeptno;

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

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

算出部門30中得到最多獎(jiǎng)金的員工獎(jiǎng)金

Selectmax(comm)fromempwheredeptno=30;

算出部門30中得到最多獎(jiǎng)金的員工姓名

selectenamefromempwherecomm=(selectmax(comm)fromempwhere

deptno=30);

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

Selectjobrmin(sal),count(*)fromempgroupbyjob;

-20-

Js而》imanMMOracle教程

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

金不錯(cuò)”，如果平均獎(jiǎng)金100到300,顯示“獎(jiǎng)金一般”，如果平均獎(jiǎng)金小于100,顯示“基本沒(méi)有獎(jiǎng)金”,

按部門編號(hào)降序，平均工資降序排列

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

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

fromempgroupbyjoborderbyjobdesc,avg(sal)desc;

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

Selectcount(*),deptnofromempgroupbydeptno;

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

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

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

分組統(tǒng)計(jì)每個(gè)部門下，每種職位的平均獎(jiǎng)金(也要算沒(méi)獎(jiǎng)金的人)和總工資(包括獎(jiǎ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-

佇/倡永國(guó)

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

佇/倡永國(guó)

>SinyoeimamatOracle教程

滿連接

1

22

3

44

5

6

左連接

1

22

3

44

5

右連接

22

44

6

6.8、集合操作

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

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

?INTERSECT：交集：只顯示重復(fù)的

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

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

CREATETABLEemp20ASSELECT*FROMempWHEREdeptno=20;

1、驗(yàn)證UNION及UNIONALL

UNION：SELECT*FROMempUNIONSELECT*FROMemp20;

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

UNIONALL：SELECT*FROMempUNIONALLSELECT*FROMemp20;

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

2、驗(yàn)證INTERSECT

SELECT*FROMempINTERSECTSELECT*FROMemp20;

只顯示了兩個(gè)表中彼此重復(fù)的記錄。

3、MINUS：返回差異的記錄

SELECT*FROMempMINUSSELECT*FROMemp20;

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

-23-

Oracle教程

滿鏈接也可以用以下的方式來(lái)表示：

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、子查詢空值/多值問(wèn)題

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

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

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

詢中對(duì)其使用相應(yīng)的單行記錄比較運(yùn)算符

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

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

對(duì)其使用單行記錄比較運(yùn)算符

(多值)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-

佇/倡永國(guó)

>SinyoeimamatOracle教程

from(select*fromemporderbysaldesc)

whererownum<=5;

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