oracle學習課件 計算機技術第05章

第5章使用SQL語言操作管理Oracle數(shù)據(jù)庫

課程描述

SQL語言

SQL*Plus

數(shù)據(jù)庫查詢

數(shù)據(jù)類型

常用函數(shù)

DML命令

5.1SQL語言概述

□SQL是一種面向數(shù)據(jù)庫的通用數(shù)據(jù)處理語言

規(guī)范，能完成以下幾類功能：提取查詢數(shù)據(jù),

插入修改刪除數(shù)據(jù)，生成、修改和刪除數(shù)據(jù)

庫對象，數(shù)據(jù)庫安全控制，數(shù)據(jù)庫完整性及

數(shù)據(jù)保護控制。

口數(shù)據(jù)庫對象包括表、視圖、索引、同義詞、

簇、觸發(fā)器、函數(shù)、過程、包、數(shù)據(jù)庫鏈、

快照等（表空間、回滾段、角色、用戶）。

數(shù)據(jù)庫通過對表的操作來管理存儲在其中的

數(shù)據(jù)。

SQL體系結(jié)構(gòu)

□SQL數(shù)據(jù)庫的體系結(jié)構(gòu)也是三級結(jié)構(gòu)，但術語與傳統(tǒng)關

系模型術語不同，在SQL中，關系攜式稱為“基本表”，

存儲模式稱為“存儲文件”，子模式稱為“視圖”，

元組稱“行”，屬性稱“列”。

□SQL數(shù)據(jù)庫體系的結(jié)構(gòu)要點如下：

(1)一個SQL數(shù)據(jù)庫是表的匯集。

(2)一個SQL表由行集構(gòu)成，行是列的序列，每列對應

一個數(shù)據(jù)項。

(3)表或者是基本表，或者是視圖。基本表是實際存

儲在數(shù)據(jù)庫中的表，視圖由是由若干基本表或其他視

圖構(gòu)成的表的定義。

(4)一個基本表可以跨一個或多個存儲文件,一個存

儲文件也可存放一個或多個基本表。存儲文件與物理

文件對應。

(5)用戶可以用SQL語句對表進行操作，包括視圖和基

術表。

(6)SQL的用戶可以是應用程序，也可以是終端用戶。

SQL組成

1、數(shù)據(jù)定義(SQLDDL)用于定義SQL模式、基

本表、視圖和索引的創(chuàng)建和撤消操作；

2、數(shù)據(jù)操縱(SQLDML)數(shù)據(jù)操縱分成數(shù)據(jù)查詢

和數(shù)據(jù)更新兩類。數(shù)據(jù)更新又分成插入、刪除、

和修改三種操作；

3、數(shù)據(jù)控制(SQLDCL)包括對基本表和視圖的

授權(quán)，完整性規(guī)則的描述，事務控制等內(nèi)容；

4、嵌入式SQL的使用規(guī)定，涉及到SQL語句嵌入

在宿主語言程序中使用的規(guī)則。

SQL*Plus編輯器的編輯命令

在SQL*Plus中鍵入SQL命令，將在緩沖區(qū)中保留最后一次

執(zhí)行的命令（一行或多行）。

SQL*PLUS關鍵字（以下命令以“CR”結(jié)束）

@、#、/、AcceptsAppendsBreak、

Btitle.Change、Clear、Column>

Compute、ConnectsCODV>Define、

Del.Describe,DisconnectsDocuments

Edit、Exit、Get、Help、

Host、Input、List、Newpacie、

Pause>Quit、Remark、Run、

SaveySet,Show、Spool、

Start、Timina,Ttitle.Undefine、

SQL*Plus編輯器的編輯命令

命令縮寫解釋

/執(zhí)行語句

RUNR運行SQL緩沖區(qū)命令

ListL列出緩沖區(qū)內(nèi)容

APPENDtextAtext行尾增力口text

CHANGEC/old/new在當前行中將old換為/new

CHANGEC/text/在當前行中刪除text

CLEARBufferCLBuff清除緩沖區(qū)

SAVE將SQL命令存入文件

DELDEL(DELn)刪除當前行（第n行）

INPUT1增加一行

INPUTtext/text增加有text組成的行

GET將文件裝入緩沖區(qū)

S77U?7或@執(zhí)行命令文件

SQL*Plus編輯器的編輯命令

通過SQLPLUS發(fā)送的最后一次SQL語句都會保存在SQL

PLUS的一個數(shù)據(jù)緩沖區(qū)內(nèi)，而SQLPLUS命令不包括在內(nèi),

可以通過編輯命令進行編輯。

(1)/(R)

重新執(zhí)行一下sql語句。

SQL<selectcount(*)fromstudents

SQL</

SQL*Plus編輯器的編輯命令

(2)list

列出緩沖區(qū)中的內(nèi)容。

SQL>1

1select*

2fromstudents

3*whereSex=f

1,2,3代表行號，帶*號代表當前行

SQL>12/列出第二行緩沖區(qū)內(nèi)容，同時第二行變成當前行

SQL>112/列出第一到第二行緩沖區(qū)里的內(nèi)容

SQL>1*/列出當前行，*表示當前行

SQL>12*/列出從第二行到當前行緩沖區(qū)的內(nèi)容

SQL>12last/列出從第二行到最后一行

SQL*Plus編輯器的編輯命令

(3)input(4)APPEND

在當前行后插入新行在當前行行尾增加內(nèi)容

SQL>iandage<20

SQL>12

SQL>1

SQL>atableb

1select*2*fromstudentstableb

2fromstudents

3whereSex=f

4*andage<20

(5)deln

n命令用于刪除第n行指令，不指定為當前行

SQL>del

SQL>del2

SQL*Plus編輯器的編輯命令

(6)change

用于修改替換當前語句中的字符

SQL>1SQL>C/<20/<19

1select*SQL>1

2fromstudents

3whereSex二f1select*

4*andage<202fromstudents

3whereSex='f'

4*andage<19

SQL*Plus編輯器的編輯命令

(7)save

□SQL命令存盤

SQL>save文件名

□若文件存在，可以替換

SQL>save文件名replace

□追加在文件后面，保留原有文件名

SQL>save文件名append

□不指定文件名，為命令文件，文件名為sql

SQL*Plus編輯器的編輯命令

(8)start

執(zhí)行命令文件，默認文件名為sql

SQL>start文件名

SQL>@文件名

(9)clear

清空當前屏幕

SQL>clearscreen

SQL*Plus環(huán)境參數(shù)

(1)set

環(huán)境項設置命令

SQL>set環(huán)境項設置內(nèi)容

SQL>setlinesize1000/設置屏幕的現(xiàn)實寬度為1000,

默認為100

(2)show

查看當前系統(tǒng)變量

SQL>showuser

SQL>showall

SQL>showerror

SQL*Plus環(huán)境參數(shù)

對環(huán)境設置后，退出后再一次進入時，這些環(huán)境設置又回到了默認值，想要

進次入先進前設入置好后的環(huán)，再境，進就入必此須文使牖用文彝件翳將置設好置的好環(huán)的環(huán)境境保保存存起起來來,，下

(3)store

保存環(huán)境設置

SQL>storesetenvironment,cmd

注意：environment是可以改變的，可以是其它名字只要后綴名不變即可。

當再次登錄進入時，只要運行這個文件即可

environment,cmd或者startenvironment,cmd

例如：

SQL>showpause

PAUSE為OFF

SQL>setpauseon

SQL>showpause

PAUSE為ON并設為〃“

SQL>storeset2.cmd

已創(chuàng)建文件2.cmd

SQL>exit

假脫機輸出

所謂假脫機輸出，就是將sql*plus的輸出信息保存到一個數(shù)據(jù)文件

中?？梢圆捎貌藛雾?，也可以采用命令spool來實現(xiàn)

(1)假脫機輸出

□使用sql*plus中的“文件”■?“假脫機”

□使用spool命令

SQL>spool文件名

SQL>spoole:/LmdOra.1is或

SQL>spoole:/LmdOra

(2)停止假脫機

停止假脫機可以采用命令spooloff或者spoolout

□spooloff:關閉輸出文件

□spoolout:關閉輸出文件并打印輸出。

聯(lián)機幫助

(1)help

獲取命令幫助

□SQL>help/顯示所有命令的幫助信息

□SQL>help命令名/顯示某個具體命令的幫助信息

SQL>helplist

(2)describe(desc)

快速查詢表及所有表列的概要，功能如下：

□列出表結(jié)構(gòu)

SQL>desc表名

□獲取有關函數(shù)、過程及包的描述

SQL>desc函數(shù)名(過程名&包名)

注：使用describe獲取信息，所查找的對象必須存在

數(shù)據(jù)庫查詢

查詢語句的基本結(jié)構(gòu)是由SELECT子句、FROM子句、

WHERE子句德組成的查詢塊。

數(shù)據(jù)查詢語句的格式

SELECT{*|table.*|[table.]field[Jtable.]field2[9...]]}[AS

aliasl[,alias2[,.??]]]

FROMtableexpression

[WHERE...]

[GROUPBY...]

[HAVING...]

[ORDERBY...

數(shù)據(jù)庫查詢

基本SELECT語句

SELECT'{[[DISTINCT]column\expression{alias\^

在語法中：

*選擇所有的列

DISTINCT禁止重復

這擇指定的字段或表達式

column\expression

alias給所選擇的列不同的標題

table指定包含列的表

簡單查詢

□SELECT子句

查詢語句的主關鍵字，在該子句中，通過指定列名來確定

表的哪些列值出現(xiàn)在查詢結(jié)果中。

SELECTsno9sname9dno

FROMstudent

WHEREsex=，女';

select子句后的查詢輸出列表項可以出現(xiàn)列名，常量，函數(shù)

以及四則運算。

簡單查詢

□WHERE子句

從表中找出滿足條件的記錄。

例：SELECTsname,birthdayFROMstudent

WHEREsex=6男'andbirthday

BETWEEN，01-1月-19805AND'3L12月-1983，；

WHERE子句后的條件表達式，參與運算的值可以包括常

量、系統(tǒng)函數(shù)及FROM子句中所聲明表中的列名。

在查詢條件中可以使用集合運算符。

伊J:SELECTsname,telFROMDEP

WHEREdnameINC計算機系'J外語系'J法律系'）；

簡單查詢

□ORDERBY條件子句

排序輸出。將查詢結(jié)果以排序的方式輸出。

SELECTfieldlistFROMtable

WHEREselectcriteria

ORDERBYfield[ASC|DESC][,field![ASC|DESC][,

默認順序是升序ASC,如果降序，指定DESC。

排序項可以是列名，表達式，別名，或輸出項的序號。

可以指定多個排序項，用逗號隔開。

ORDERBY的排序項可以不出現(xiàn)在SELECT后，但如果包

含DISTINCT時，ORDERBY后的排序項必須是SELECT

后的列名。

簡單查詢

例，

在職工表中emp,查詢職工的姓名、年齡、工資和部門,

按部門依次輸電，同一部門的職工按年齡由大到小輸出，

年齡相同按工資由低到高輸出。

SELECTdeptno,ename,age,sal

FROMemp

ORDERBYdeptno,ageDESC,sal;

簡單查詢

□GROUPBY條件子句

該子句指定用于分組的表達式，該表達式的值相同的記錄

被分為一組。用于在執(zhí)行查詢語句的統(tǒng)計工作。分組后，

組南藪再對每一小組進行統(tǒng)并。

SELECTfieldlist

FROMtable

WHEREcriteria

GROUPBYgroupfieldlist

例：查詢每個系的學生人數(shù)，輸出系號，人數(shù)

SELECTdno,count(*)

FROMstudent

GROUPBYdno;

*只有出現(xiàn)在group㈢子句中的列名才能與組函數(shù)并列出現(xiàn)

在select子句輸出項中。

簡單查詢

□GROUPBY子句中可以指定多個用于分組的表達式項，

按第一項分組后，每組再按第二項分，…，組函數(shù)以最小

的組為單位進行統(tǒng)計。

例：查詢201課程最高成績和最低成績，但系中男，女分開

統(tǒng)計

SELECTdno,sex,MAX(grade),MIN(grade)

FROMstudent,sc

WHEREcno=201andsc.sno=student.sno

GROUPBYdno,sex;

簡單查詢

□HAVING條件子句

與GROUPBY子句配合使用。當選擇是基于統(tǒng)計的結(jié)果

時，使用HAVING.

SELECTfieldlist

FROMtable例：查詢學分不到10的同學

WHEREselectcriteriaselectsno,sum(credit)

fromsc,course

GROUPBYgroupfieldlist

wheregrade>60and

HAVINGgroupcriteriao=o

groupbysno

havingsum(credit)<10;

簡單查詢

□WHERE和HAVING的區(qū)別

WHERE是查詢開始時從表中選擇記錄的條件

HAVING是依據(jù)分組統(tǒng)計的結(jié)果所進行的組的選擇

□出現(xiàn)在查詢語句中的6個基本子句（順序）:

必須：SELECT

FROM在一個查詢語句

可選：WHERE中，子句出現(xiàn)的

順序

GROUPBY

HAVING

ORDERBY

部分函數(shù)

□AVG(n)返回n的平均值。

例，查詢部門20職工的平均工資

SelectAVG(sal)fromemp

Wheredeptno=6205;

□COUNTC|expr})返回查詢到的行數(shù)。

參數(shù)為*,返回所有的行，包括空行。

象數(shù)為expr,返回expr為非空的行數(shù)。

例，查詢計算機系的學生總數(shù)

selectCOUNT(*)fromstudent,dep

wheredname=，并算機系'AND

dep.dno=student.dno;

部分函數(shù)

□MAX(expr)返回expr最大值。

表達式是字符型，則返回VARCHAR2,數(shù)據(jù)庫字

符集中排序最高的最大；

表達式是日期型，則返回日期，最近的日期最大;

表達式是數(shù)字型，則返回數(shù)字。

□MIN(expr)返回expr最小值，與MAX函數(shù)相反。

運算符與表達式

口SQL語句中的表達式由一個或多個值、運算符和SQL函數(shù)組

成。表達式值的類型取決于參與運算的值的類型。

□在SQL語句中可以使用表達式的地方有：

?SELECT語句的查詢列表中。

?WHERE和HAVING子句的條件中。

?ORDERBY子句中。

?INSERT語句的VALUES子句中。

?UPDATE語句的SET子句中。

運算符與表達式

ALL,ANY

用一個值與集合中的所有元素進行比較，比較條件都

滿足時為真。

(ALL,ANY的前面必須帶有一個比較運算符作為比較

條件)

例：select*fromempwheresal>ALL(

selectsalfromempwheredeptno=30);

注意：！=ALL相當于NOTIN,=ANY相當于IN

運算符與表達式

BETWEENaANDb

判斷一個值是否在a和b之間

例：SELECT*FROMemp

WHEREsalBETWEEN1000AND2000;

NOTBETWEENaANDb義詞。

EXISTS

用來判斷子查詢的返回結(jié)果，如果至少有一行返回，就為

真。

例:select*fromdepwhereexists(

select*fromstudentwheredno=dep.dno);

運算符與表達式

ISNULL

用于測試列值是否為空，列值為空時為真

例:SELECT*FROMempWHEREMGRISNULL;

ISNOTNULL測試空值，列值不為空時為真。

LIKE

用于包含匹配字符的字符串比較，實現(xiàn)模糊匹配。

Oracle提供了兩個字符匹配符號：%,_

%:代表任意多個字符，不能匹配一個空值。

代表任意一'個字符。

例：SELECT*FROMempWHEREenameLIKE'SM%';

匹配符只能出現(xiàn)在LIKE之后的字符串中，如果在LIKE之

前，則被看作普通字符

運算符與表達式

□邏輯運算符

AND（與）、OR（或）、NOT（非）

例，

SELECT*FROMemp

WHEREjob—CLERK'ANDdeptno=20;

SELECT*FROMemp

WHEREjob=6CLERK5ORjob=6MANAGER5;

SELECT*FROMemp

WHERENOT（jobISNULL）;

運算符與表達式

□集合運算符

SQL語句中可以出現(xiàn)集合運算符，是對兩個查詢結(jié)果關系進行

集合操作。在兩個查詢中取的列數(shù)與數(shù)據(jù)類型必須相同。

UNION并

INTERSECT交

MINUS差

運算符與表達式

□UNION并

將兩個或兩個以上SELECT語句的查詢結(jié)果集合合并成一個

結(jié)果集合顯示。（不包括重復值）

UNION的語法格式為：

Selectstatement

UNION[ALL]

selectstatement

[UNION[ALL]selectstatement][...n]

例，查詢至少選修102和105其中一門課的學生，輸出學號

selectsnofromscwherecno=f102f

union

selectsnofromscwherecno=f1051;

UNIONALL包括重復值

運算符與表達式

□INTERSECT交

將既屬于一個查詢結(jié)果又屬于另一個查詢結(jié)果的記錄作為結(jié)

臬。

例，查詢同時選修了102和105兩門課的學生

selectsnofromscwherecno='102'

intersect

selectsnofromscwherecno='105';

運算符與表達式

MINUS差

從一個查詢結(jié)果的記錄中去掉屬于另一個查詢結(jié)果的記錄。

例，查詢選修了302但沒有選修301的學生

selectsnofromscwherecno='302'

minus

selectsnofromscwherecno=l301l;

優(yōu)先規(guī)則

求值順序

1算木運算

2連字操作

3比較操作

4IS[NOT]NULL,LIKE,[NOT]IN

5[NOT]BETWEEN

6NOT邏輯條件

7AND邏輯條件

8OR邏輯條件

使用圓括號改變優(yōu)先規(guī)則

高級查詢

口在數(shù)據(jù)庫中會有許多表存儲不同的數(shù)據(jù)，并且這些表

之間有一定的關聯(lián)，同時從這多張表中抽取數(shù)據(jù)，就

是高級查詢。涉及到如何將多個查詢結(jié)果進行集合運

算，從多個表中取數(shù)據(jù)時如何進行連接，以及如何用

子查詢解決一些實際問題。

多表連接

□在多表連接查詢中，一般帶有WHERE子句，指定連接條件,

否則得到的查詢結(jié)果將是各表的笛卡爾積（即一表中的每條

記錄其另一個表中的每條記親做連顯）O

口笛卡爾連接是一種無條件連接，產(chǎn)生大量的行。如果第一張

表中有M條記錄，第二張表中有N條記錄，查詢結(jié)果是M*

N條記錄。

口避免產(chǎn)生笛卡爾連接，如果有N張表連接，至少有N?1個連

接條件。

多表連接

□在一條查詢語句中涉及多張表，需要將這些表連接起來進

行查詢。連接用于從不同的表中得到列的組合。它們之間

通常存在一定的聯(lián)系，必須識別連接多張表的公共列。

□在多表連接查詢中，在連接條件中使用等號“=”將被連接

的多張表中的列作相等比較，為等值連接，也叫做內(nèi)連接。

內(nèi)連接查詢結(jié)果只顯示完全滿足連接條件的記錄。

□在多表連接查詢中，在連接條件中使用等號“=”之外的其

它運算符連接多張表，為非等值連接。

注意：查詢涉及多表時，先分析查詢所涉及到幾張表，在

FROM后列出相應的袤名;然后在WHERE子句后寫上相應

的連接條件。

多表連接

例1,查詢計算機系的學生信息

SELECTsno,sname,sexFROMstudent,dep

WHEREstudent.dno=dep.dno

ANDdname=，計算機系，;

例2,查詢哪些雇員的工資級別屬于第三級別？（工資級別表為

SALGRADE）

SQL>selectemp9salfromemp,salgrade

wheregrade=3andsal<2200andsal>1601;

多表連接

①當查詢多張表時，F(xiàn)ROM子句后的表名用“，”隔開；

②在WHERE子句后寫上相應的連接條件；

③在SELECT和WHERE后涉及表中的相同的列名時，需要

加上相應的表名，“表名.列名”。

④表名可以取別名，這樣在后面參考表名時可以使用簡短的

別名。一旦定義了表的別名，展木條SELECT語句中就不

能用表名去限制列名，應該用別名去限制列名。

多表連接

例，查詢英語口語不及格學生，輸出學生學號，姓名

及成績

SELECTstudent.sno,sname,grade

FROMstudent,sc,course

WHEREcname='英語口語'ANDgrade<60AND

o=oANDsc.sno=student.sno;

外連接

口一般連接，沒有被連接的記錄是不會出現(xiàn)在查詢結(jié)果中的，

外連接可以返回兩種記錄，一種是滿足連接條件的記錄，另

外一種是來自其中一個表的記錄，這些記錄因不滿足連接條

件而不能與另一個表的記錄連接.

□在ORACLE中，外連接可以通過在WHERE子句中：(+)

的使用來使用，例如：

表A與表B的左連接A.FIELD1=B.FIELD1(+),

右連接A.FIELD1(+尸B.FIELD1。

也可以使用LEFT|RIGHT|FULL實現(xiàn)左連接|右連接|全連接

外連接

□左連接A?FIELD1=B.FIELD1(+)

對于表A中所有的行，如果表B中沒有匹配的行，則表B中的

列返回NULL。

例，左連接

selectsname,sex,dnamefromstudent,dep

wherestudent.dno=dep.dno(+);

等價于

selectsname,sex,dnamefromstudentleftouterjoindep

on(student.dno=dep.dno);

外連接

□右連接A?FIELD1(+尸B.FIELD1

對于表B中所有的行，如果表A中沒有匹配的行，則表A中

的列返回NULL。

例，右連接

selectsname9sex,dnamefromstudent,dep

wherestudent.dno(+)=dep.dno;

等價于

selectsname9sex9dnamefromstudent

rightouterjoindep

on(student.dno=dep.dno);

外連接

全連接

selectsname,sex9dname

fromstudent

fullouterjoindep

on(student.dno=dep.dno);

自連接

在查詢語句中涉及到一張表中的不同行的列值，自連接

查詢僅涉及到該表與其自身的連接。為了表示不同的行，在

FROM子句中為一張表定義不同的別名，可以通過在列前加

上別名來引用同一張表中不同行的列值。

例，查詢EMP表中職工SMITH的上級領導信息，輸出該領導

的職工號和建加。

自連接查詢

selecte2.eno,e2.enamefromempel,empe2

whereel.ename=,SMITHlANDel.mgr=e2.eno;

子查詢

口在查詢語句中，可以通過調(diào)用一個查詢獲得并使用查詢結(jié)果。

把出現(xiàn)在查詢語句（或其他語句）中的查詢稱為，子查詢，，

也稱嵌套查詢。一般情況下，子查詢出現(xiàn)在SELECT語句的

WHERE子句中，也出現(xiàn)在FROM子句和HAVING子句中。

口子查詢比主查詢先執(zhí)行，子查詢的結(jié)果作為主查詢的條件使

用。

口一個子查詢可以返回一個簡單數(shù)據(jù)，一條記錄或一個包含多

行的夾系。

子查詢

口子查詢使用的原則：

>一個子查詢必須用括號引起來。

?在子查詢中可以使用兩類比較運算符：單行和多行。

>一個子查詢必須出現(xiàn)在運算符的右邊，

>子查詢可以在SELECT、INSERT.UPDATE.DELETE

等語句中使用。

?子查詢中不能包含一個ORDERBY子句。在SELECT語句

中只能有一個ORDERBY子句，如果要指定，該ORDER

BY子句必須是主查詢語句中的最后一個子句。

子查詢

在主查詢中可以使用子查詢的結(jié)果

例，

selectsnamefromstudent

wheredno=（selectdnofromdep

wheredname=，計算機系5）;

對于子查詢返回的結(jié)果是一個關系的情況（包括若干行和

歹U）,主查詢必須使用集合運算符進行處理

WHERE子句中的子查詢

□單行子查詢

單行子查詢只返回一行結(jié)果。單行子查詢使用一個單

行的運算符，常用等于運算符“二”。

例：查找與SMITH不在同一部門工作的所有雇員，輸出姓名

和部門號。

SQL>selectename,deptnofromemp

wheredeptno!=(selectdeptnofromemp

whereename=6SMITH5);

WHERE子句中的子查詢

□多行子查詢

多行子查詢即子查詢返回多行結(jié)果。可使用多行運算符：

IN、EXISTS.ANY、ALL,都可使用NOT運算符，對其結(jié)

果進行求反。

例：當EMP表中有多個雇員叫SMITH時，查找與SMITH不在

同一部門工作的所有雇員，輸出姓名和部門號。

SQL>selectename,deptnofromemp

wheredeptnonotin(selectdeptnofromemp

whereename=6SMITH5);

注意：當用戶不能確定結(jié)果集是否是多行時，在子查詢中應該

使用多行運算符。

WHERE子句中的子查詢

口相互關聯(lián)的子查詢

在子查詢中要使用主查詢的當前記錄值，這種嵌套的子查

詢稱為相互關聯(lián)的子查詢。在這類查詢語句中，主查詢處理

每一行時，相互關聯(lián)的子查詢執(zhí)行一次。

例，求沒有選修C2課程的學生姓名。

SELECT姓名

FROM學生

WHERENOTEXISTS

（SELECT*

FROM選課

WHERE學生.學號=學號AND課程號=￡2，）；

注意：①相關子查詢是不能獨立執(zhí)行的。

②列的作用范圍只能在當前查詢層及子查詢。

子查詢在其他語句中的應用

(1)在CREATETABLE語句中應用

CREATETABLE表名AS子查詢

創(chuàng)建一個新表時，如果該表的結(jié)構(gòu)與已存在的表相同時，可以

通過使用子查詢的方式建表，同時可插入初始數(shù)據(jù)。

例，建立計算機系學生表studl,并將計算機系的學生記錄從

student表中復刷到新表中。

Createtablestudl

Asselectsno,sname,sex,birthday,student.dno

Fromstudent,dep

Wheredname='計算機系'andstudent.dno=dep.dno;

子查詢在其他語句中的應用

(2)還可在INSERT語句中使用子查詢，可以通過子查詢將查

詢結(jié)果插入到已存在的表中。選擇插入的數(shù)據(jù)要與插入的表

結(jié)構(gòu)相符。

例：將emp中目前沒有單位的雇員記錄復制到new_emp表中

SQL>insertintonewemp

select*fromemp

wheredeptnoisnull;

子查詢在其他語句中的應用

(3)還可在UPDATE,DELETE語句中使用子查詢

例1：將所有雇員的工資改成相應部門的最大工資。

SQL>updateempsetsal=(selectmax(sal)fromemp,e

wheree.deptno=emp.deptno);

例2:刪除工資低于該部門平均工資的雇員記錄。

SQL>deletefromemp

wheresal<(selectavg(sal)fromemp,e

whereemp.deptno=e.deptno);

FROM子句中的子查詢

□在SELECT語句的FROM子句中出現(xiàn)的子查詢相當于一張表,

與定義和使用視圖類似。FROM子句市的子查詢用括號引起

來，并且可以給其取一個別名。

例：查詢每個部門的平均工資，并且找出每個部門中的每個

雇員的工資與其所在部門的平均工資的差。

SQL>selecte.deptno,e.ename,e.salsalary,a.average,

e.sal-a.averagedifffromemp,e,(select

deptno,avg(sal)averagefromemp

groupbydeptno)a

wheree.deptno=a.deptno

orderby1,2;

HAVING-f句中的子查詢

□在HAVING子句中使用子查詢，Oracle服務器先執(zhí)行子查詢,

然后將子查詢的結(jié)果返回各主查詢的HAVING子句。

例：查詢EMP表中平均工資超過30號部門平均工資的部門。

SQL>selectdeptno,avg(sal)fromemp

groupbydeptno

havingavg(sal)>

(selectavg(sal)fromemp

wheredeptno=30);

5.4數(shù)據(jù)基本類型

口字符型數(shù)據(jù)類型

包括CHAR、VARCHAR2(VARCHAR)、LONG、NCHAR和

NVARCHAR2幾種類型。

(DCHAR,描述定長的字符串，如果實際值不夠定

義的長度，系統(tǒng)將以空格填充。如果長度大于定

義長度將會觸發(fā)錯誤信息。聲明方式如下:CHAR

(L),

L為字符串長度，缺省為1,作為變量最大32767個

字符，作為數(shù)據(jù)存儲在ORACLE最大為2000個字節(jié)。

字符型數(shù)據(jù)類型

(2)VARCHAR2(VARCHAR),描述變長字符串。它的聲明方式

如下：

VARCHAR2(L)

L為字符串長度，沒有缺省值，作為變量最大32767個字節(jié)，作

為數(shù)據(jù)存儲在ORACLE中最大為4000個字節(jié)。在多字節(jié)語言環(huán)

境中，實際存儲的字符個數(shù)可能小于L值，

例如：當語言環(huán)境為中文(SIMPLIFIED

CHINESE_CHINA.ZHS16GBK)時，一個VARCHAR2(200)的數(shù)

據(jù)列可以保存200個英文字符或者100個漢字字符。

□因為VARCHAR2數(shù)據(jù)類型只存儲為該列所賦的字符(不加空

格)，所以VARCHAR2需要的存儲空間比CHAR數(shù)據(jù)類型要小。

□Oracle推薦使用VARCHAR2

字符型數(shù)據(jù)類型

(3)NCHAR、NVARCHAR2,國家字符集，與

環(huán)境變量NLS(NLS文件二多國語言資源文件)

指定的語言集密切相關，使用方法和CHAR、

VARCHAR2相同。

NUMBER數(shù)據(jù)類型

NUMBER,是以十進制格式進行存儲的，它便于存儲，但是

在計算上，系統(tǒng)會自動的將它轉(zhuǎn)換成為二進制進行運算。

它的定義方式為：

NUMBER(P,S),

P是精度，最大38位，S是刻度范圍，可在-84.?.127間取值。

例如：NUMBER(5,2)可以用來存儲表示

-999.99...999.99間的數(shù)值。

若在一行數(shù)據(jù)中的這個字段輸入575.316,則真正保存到字

段中的數(shù)值是575.32o

如：number(3,0),輸入575.316,真正保存的數(shù)據(jù)是

575o

P、S可以在定義時省略，例如：NUMBER(5)、NUMBER等；

DATE數(shù)據(jù)類型

從公元前4712年1月1日到公元4712年12月31日的所有合法

日期，默認為公元后。

Oracle其實在內(nèi)部是按7個字節(jié)來保存日期數(shù)據(jù)，在定

義中還包括小時、分、秒。

缺省格式為DD-MON-YY,如07-11月-00表示2000年11月7

日。

□Century

□Year

□Month

□Day

□Hour

□Minute

□Second

LONG數(shù)據(jù)類型

LONG：LONG數(shù)據(jù)類型可以存放2GB的字符數(shù)

據(jù)，它是從早期版本中繼承來的。現(xiàn)在如

果想存儲大容量的數(shù)據(jù)，Oracle推薦使用

CLOB和NCLOB數(shù)據(jù)類型。在表和SQL語句中

使用LONG類型有許多限制。

二進制數(shù)據(jù)類型

用來存儲二進制數(shù)據(jù)，不會在字符集間轉(zhuǎn)換。

(1)RA肌類似于CHAR,聲明方式RAW(L),L為長度，

以字節(jié)為單位，作為數(shù)據(jù)庫列最大2000,作為變量最

大32767字節(jié)。Oracle用這種格式來保存較小的圖形文

彳?；驇Ц袷降奈谋疚募鏜iceosoftWord文檔。

是一種較老的數(shù)據(jù)類型，將來會逐漸被BLOB、CLOB、

NCL0B等大的對象數(shù)據(jù)類型所取代。

(2)LONGRAW,類似于VARCHAR2,可變長二進制數(shù)據(jù)，

最大長度是2GB。Oracle用這種格式來保存較大的圖

形文件或帶格式的文本文禪，加MiceosoftWord支檔,

以及音頻、視頻等非文本文件。

在同一源表申木能向時宥long類型和longraw類型，

也是一種較老的數(shù)據(jù)類型，將來會逐漸被BLOB、CLOB、

NCLOB等大的對象數(shù)據(jù)類型所取代。

ROWID數(shù)據(jù)類型

□ROWID是一種特殊的列類型，稱之為偽列(邏輯列)

(pseudocolumn)。在創(chuàng)建基表時，自動創(chuàng)建。不能改

變。

□ROWID偽列在SQLSELECT語句中可以像普通列那樣被訪

問。Oracle數(shù)據(jù)庫中每行都有一個偽列。ROWID表示行

的地址，ROWID偽列用ROWID數(shù)據(jù)類型定義。

□ROWID與磁盤驅(qū)動的特定位置有關，因此，ROWID是獲得

行的最快方法。但是，行的ROWID會隨著卸載和重載數(shù)

據(jù)庫而發(fā)生變化，因此建議不要在事務中使用ROWID偽

列的值。例如，一旦當前應用已經(jīng)使用完記錄，就沒有

理由保存行的ROWID.不能通過任何SQL語句來設置標準

的ROWID偽列的值。

□列或變量可以定義成ROWID數(shù)據(jù)類型，但是Oracle不能

保證該列或變量的值是一個有效的ROWID.

大對象數(shù)據(jù)類型

LOB變量主要是用來存儲大量數(shù)據(jù)的數(shù)據(jù)庫字段，

最大可以存儲4G字節(jié)的內(nèi)容。用來保存較大的圖

形文件或帶格式的文本文件，如MiceosoftWord

文檔，以及香頻、視頻等非文本文件。主要

有：

□CL0B：和LONG類型相似，存儲單字節(jié)字符數(shù)據(jù)

□NCL0B：用來存儲定寬多字節(jié)字符數(shù)據(jù)。

□BLOB：和LONGRAW相似，用來存儲無結(jié)構(gòu)的二進

制數(shù)據(jù)。

□BFILE：它用來允許ORACLE對數(shù)據(jù)庫外存儲的大

型二進制文本進行只讀形式的訪問。

5.5函數(shù)

輸入輸出

函數(shù)

參數(shù)11..函數(shù)(Function)

■參數(shù)2,執(zhí)行作用

函數(shù)

□Oracle提供了大量的函數(shù)，可以在SQL和PL/SQL語句中調(diào)

用。

口函數(shù)有0個或多個參數(shù)，并且返回單個值。

口函數(shù)的一般格式：函數(shù)名（參數(shù)1,參數(shù)2,…，參數(shù)n）

□函數(shù)功能：

?計算數(shù)值

?修改單個數(shù)據(jù)項

?對行進行分組輸出

?修改日期的顯示格式

?轉(zhuǎn)換列的數(shù)據(jù)類型

數(shù)

函數(shù)按操作對象可分為兩種類型:

□單行函數(shù)

□聚組函數(shù)（多行函數(shù)）

函數(shù)

函數(shù)

單行函數(shù)多行函數(shù)

函數(shù)

口單行函數(shù)

單行函數(shù)只操作一行，并且每一行返回一個結(jié)果。

單行函數(shù)在從表中取出數(shù)據(jù)之前就知道要處理參數(shù)

的個數(shù)。

□分組函數(shù)

分組函數(shù)是對一組被查詢的行返回一個單一的值。

分組函數(shù)直到所有的數(shù)據(jù)從表中抽取出來并且組成

一個分類時，才知道要處理的參數(shù)的個數(shù)。

單行函數(shù)

單行函數(shù)用于操縱數(shù)據(jù)項，他們接受一個或多個

參數(shù)，并且對查詢的每個返回行返回一個值。

一個參數(shù)可以是下列數(shù)據(jù)之一：

□用戶提供的常數(shù)

□變量值

□列名

□表達式

單行函數(shù)

單行函數(shù)的特性包括：

□作用于查詢中返回的每一行

□每行返回一個結(jié)果

□可能返回一個與參數(shù)不同類型的數(shù)據(jù)值

□可能需要一個或多個參數(shù)

□能夠用在SELECT、WHERE和ORDERBY子

句中；可以嵌套

單行函數(shù)

□function_name\_{argl,arg2,...)]

在語法中：

functionname是函數(shù)的名字。

argl,arg2是由函數(shù)使用的任意參數(shù)，可以由

一個列名或者一個表達式提供。

單行函數(shù)

單行函數(shù)可以出現(xiàn)在查詢列表（但查詢語句

不能包含GROUPBY子句）、WHERE子句、

ORDERBY子句的表達式中，可以出現(xiàn)在UPDATE

語句的SET子句中，也可以出現(xiàn)在INSERT語句的

VALUES子句中。

單行函數(shù)

字符

通用HS數(shù)字

單行函數(shù)

轉(zhuǎn)換0期

單行函數(shù)

SQL和PL/SQL提供了很多種單行函數(shù)，根據(jù)函

數(shù)值的數(shù)據(jù)類型可劃分為：數(shù)值函數(shù)、字符函數(shù)、

日期函數(shù)、轉(zhuǎn)換函數(shù)、通用函數(shù)、其他函數(shù)、對象

引用函數(shù)、以及程序員編寫的存儲函數(shù)。

單行函數(shù)

口字符函數(shù)：接受字符輸入，可以返回字符或者數(shù)字值

口數(shù)值函數(shù)：接受數(shù)字輸入，返回數(shù)字值

□日期函數(shù)：對DATE數(shù)據(jù)類型的值進行運算

口轉(zhuǎn)換函數(shù)：從一個數(shù)據(jù)類型到另一個數(shù)據(jù)類型轉(zhuǎn)換一個

值

□通用函數(shù)：NVL、NVL2、NULLIF、

COALSECE.CASE.DECODE

單行函數(shù)

字符型函數(shù)：

□CONCAT(sl,s2)字符串連接，對字符串si和字符串s2進行連接。返回

連接后的字符串。如果si是NULL,則返回§2;如果§2是NULL,貝”返回

si；如果§1、s2都是NULL,則返回NULL。

如：selectconcat("micro',‘soft')fromdual;

□LOWER(s)變字母為小寫

□UPPER(s)變字母為大寫

□REPLACE(sl,s2[,s3])字符串查找/替換，該函數(shù)在字符串仃中查找所有

出現(xiàn)的§2串，并將找到的串用§3串替換。

小口:selectreplace(6thisisanapple4apple9,'example')fromdual;

□INSTR(sl,s2,[,n[,m])該函數(shù)在si串中查找s2串出現(xiàn)的位置，返回函數(shù)值。

參數(shù)n指示從si串的第n個位置開始找；參數(shù)m指示找出第幾次出現(xiàn)的那

個s2串。

小口:selectinstr(6thisisnotagoodexample,isit?,''is',4,2)29

單行函數(shù)

□SUBSTR(S,M[,N])取子串函數(shù)。該函數(shù)從s串中第m個字符起，取長度為

n的一個字串。

如：selectsubstr(athisisanexample9,12,4)fromdual;exam

□LENGTH(s)返回字符串s的長度，如果s為CHAR型，則長度包括所有尾

部空格。

如：selectlength(6microsoff)fromdual;9

□ASCII(c)返回字符串c中的第一個字符的ASCII碼。

□CHR(n)返回與n相等的二進制數(shù)(ascii碼)所代表的字符。

□INITCAP⑸該函數(shù)是將包含若干單詞的字符串s中的每一個單詞的首字

母大寫，其他字母小寫。

□TRANSLATE(s,from,to)該函數(shù)是將字符串s中出現(xiàn)的from字符列表中的

字符替換成to字符列表中的相應字符。

如：Selecttranslate(6thisisanexample','myis，；*****，)fromdual;

th******an*exa*ple

單行函數(shù)

□LPAD(sl,nJs2])左填充函數(shù)。該函數(shù)是在字符串si的左邊填

充字符s2,使字符串總長度達到參數(shù)n所規(guī)定的長度。如果

si的長度比n長，那么不填充，并只輸出si串的前一部分。

S2省略，就填充空格。

如：selectlpad(6verygood!',30,'ok!')

ok!ok!ok!ok!verygood!

□RPAD(sl,n,[s2])右填充函數(shù)。

□LTRIM(cl,[c2])該函數(shù)是刪除cl串中位于最左邊的、且出現(xiàn)

在c2串中的字符。如果c2參數(shù)省略，要刪除的字符就是空格

字符。

□RTRIM(cl,[,c2])該函數(shù)是刪除cl串中位于最右邊的、且出現(xiàn)

在c2串中的字符。

單行函數(shù)

數(shù)值函數(shù)：

□ABS(n)返回n的絕對值

如:Selectabs(-3.2)fromdual;3.2

口三角函數(shù)，SIN(n),COS(n),TAN(n),ASIN(n),ACOS(n),

ATAN(n)等

□CEIL(n)返回大于或等于n的最小整數(shù)

如：selectceil(-3.2)fromdual;3

□FLOOR(n)返回小于或等于n的最大整數(shù)

□EXP(n)返回基于n自然對數(shù)的平方

□LN(n)返回以e為底的n的自然對數(shù)[e(2.7182818)J

□LOG(nl,n2)返回以nl為底的n2的對數(shù)

□MOD(m,n)返回m除以n的余數(shù)

單行函數(shù)

□POWER(nl,n2)返回nl的n2次方

□ROUND(m,n)取小數(shù)點前后位置n四舍五入，n默認為0,

n>0,則四A五入為n位小數(shù)，n<0,四舍五人為小數(shù)點向左

第n位。

如：selectround(5678.1234<2)fromdual;5700

口TRUNC(m,n)與ROUND類似，只是不采用四舍五入，強行

截去不需要的部分。

□SIGN(n)n>0返回1,nvO返回n=0返回0

□SQRT(n)返回n的平方根

單行函數(shù)

日期函數(shù)

□SYSDATE返回當前日期，該函數(shù)沒有參數(shù)。

□ADD_MONTHS(d,n)該函數(shù)是在d所代表的日期上加n個月，

并將結(jié)果日期返回。

如：selectadd_months(sysdate,3)?fromdual;

□LAST_DAY(d)返回日期d所在的那個月份的最后一天。

□MONTHS_BETWEEN(dl,d2)返回dl和d2兩個日期之間相隔

幾個月。一

□ROUND(d[,fmt])該函數(shù)按照fmt指定的日期、時間單位精度

返回d所袤示的日期第一天。

如：Selectround(sysdate,year')fromdual;

1-1-2010

單行函數(shù)

轉(zhuǎn)換函數(shù)：

□TO_CHAR:將日期型或數(shù)值型數(shù)據(jù)轉(zhuǎn)換成字符串。

>TO_CHAR(d,fmt)將d所表示的日期型數(shù)據(jù)轉(zhuǎn)換成fmt所指定

的丙容及格式。該函數(shù)值為VARCHAR2類型。

例,TO_CHAR(SYSDATE,YYYY-MONTH-DD)

>TO_CHAR(n,fmt)將NUMBER類型的數(shù)據(jù)n按fmt格式轉(zhuǎn)換

為VXRCHAR2類型數(shù)據(jù)。

□TO_DATE(s,fmt)將CHAR或VARCHAR2類型的字符串數(shù)據(jù)

s轉(zhuǎn)換成DATE類型的數(shù)據(jù)。參數(shù)fmt說明s串所袤示的日期格

□TO_NUMBER(s,fmt)將CHAR或VARCHAR2類型的數(shù)據(jù)s轉(zhuǎn)

換成對應的數(shù)值型數(shù)據(jù)。

單行函數(shù)

通用函數(shù)：

□NVL(exprl,expr2)exprl和expr2為兩個表達式。當

exprl的值不空時，函數(shù)返回exprl的值；當exprl的值

為空時，函數(shù)返回expr2的值。

□NULLIF(exprl,expr2)o比較兩個表達式，如果相等，

函數(shù)返回空，如果不相等，函數(shù)返回第一個表達式。

第一個表達式不能為NULLo

□COALSECE(exprl,exprl,...exprn)。函數(shù)返回列表中

的第一個非空表達式。

組函數(shù)

□組函數(shù)返回基于一組記錄的值。組函數(shù)不處理NULL值，

也不返回NULL值。大部分組函數(shù)可以接受

DISTINCT和ALL兩個選項。

DISTINCT,該選項使得組函數(shù)只考慮不同值，（即去掉重

復值）。

ALL,該選項使得組函數(shù)考慮參數(shù)表達式的所有值（包括重

復值），默認值。

□組函數(shù)可以出現(xiàn)在查詢列表和HAVING子句中。在SELECT

子句中使用時，通常要求該語句帶GROUPBY子句。

組函數(shù)

口組函數(shù)的特點：

?一般對一組行進行操作，而不是對單一行。每一組只給

出一個結(jié)臬。

?用于返回一組數(shù)據(jù)的匯總信息(求和，求平均值)。

?組函數(shù)的參數(shù)的數(shù)據(jù)類型可以是數(shù)字型，字符型，日期型。

?如果將單行函數(shù)和組函數(shù)一起使用，必須有相應的

GROUPBY子打o

?多數(shù)組函數(shù)可以使用DISTINCT和ALL兩個選項。

?除了COUNT(*)以夕卜，所有的組函數(shù)都忽略NULL值。

可以使用NVL函數(shù)以別的值參代NULL。

組函數(shù)

□AVG(DISTINCT|ALLn)返回n的平均值。

例，查詢部門20職工的平均工資

SelectAVG(sal)fromemp

Wheredeptno=6209;

□COUNT({*|[DISTINCT|ALL]expr})返回查詢到的行數(shù)。

參數(shù)為*,返回所有的行，包括空行。

參數(shù)為expr,返回expr為非空的行數(shù)。

例，查詢計算機系的學生總數(shù)

selectCOUNT(*)fromstudent,dep

wherednanie=，計算機系1ANDdep.dno=student.dno;

組函數(shù)

□SUM({DISTINCT|ALL}n)返回n的總和。

例，統(tǒng)計職工工資總額

SelectSUM(sal)fromemp;

□MAX({DISTINCT|ALL}expr)返回expr最大值。

表達式是字符型，則返回VARCHAR2,數(shù)據(jù)庫字符集中排

序最高的最大；

表達式是日期型，則返回日期，最近的日期最大；

表達式是數(shù)字型，則返回數(shù)字。

□MIN({DISTINCT|ALL}expr)返回expr最小值，與MAN函數(shù)

相反。

組函數(shù)

例，查詢部門20職工的最高工資，最低工資

selectMAX(sal)fromempwheredeptno=20;

selectMin(sal)fromempwheredeptno=20;

組函數(shù)

□VARIANCE({DISTINCT|ALL}x)返回x的方差。

□STDDEV({DISTINCT|ALL}x)返回x的標準方差。是根據(jù)方

差的平方根得到的。

例，

selectvariance(sal)fromemp;

selectstddev(sal)fromemp;

函數(shù)的嵌套

口函數(shù)可以嵌套，以便一個函數(shù)的輸出作為另一個函數(shù)的輸入。

嵌套函數(shù)的優(yōu)先順序基于它的位置，從最里層開始執(zhí)行直到

最外層，并且從左到右。

□嵌套函數(shù)可以在組函數(shù)內(nèi)包含單行函數(shù)，也可以在單行函數(shù)

內(nèi)包含組函數(shù)，可以在單行函數(shù)內(nèi)包含單行函數(shù)，也可以在

組函數(shù)內(nèi)包含組函數(shù)。

查詢結(jié)果的報表輸出

□使用SQL*PLUS的格式命令使得查詢結(jié)果按報表方式輸出。

□通過SQL*PLUS的格式命令進行：

>定義一個列的寬度

>顯示有意義的列標題

>對數(shù)字型和日期型數(shù)值進行格式化

>隱藏字符列

>顯示頁標題、頁號。

查詢結(jié)果的報表輸出

口格式化列，使用COLUMN命令格式化列標題，格式化列

的顯示格式；設置列的格式參照其他列的格式；列出或清

除列的格式。

①格式化列標題

SQL>COLUMN列名HEADING列標題

例：SQL>columnSNAMEHEADING姓名

SQL>columnSNAMEHEADING'STUDENT|姓

名，

使用T將列標題分隔上下兩行

②格式化列的顯示格式

SQL>COLUMN列名FORMAT格式描述

查詢結(jié)果的報表輸出

③設置列的格式參照其他列的格式，包括列標題和格

式

SQL>COLUMN歹4名1LIKE列名2

[HEADING列標題]

其中，列名2是已經(jīng)格式化的列。

④列出或清除列的格式設置

COLUMN列名——列出某一列的格式設置

COLUMN列名CLEAR——清除某一列的格式設

置

CLEARCOLUMNS——清除所有列的格式設置

⑤隱藏列的輸出

SQL>COLUMN列名NOPRINT

查詢結(jié)果的報表輸出

□用空行和總計使報表簡潔

報表在輸出時分組，對組內(nèi)的數(shù)據(jù)進行組函數(shù)運算，則必

須使用BREAK命令先設置分組，分組后組內(nèi)的數(shù)據(jù)重復值只

顯示一次。然后使用COMPUTE命令對組內(nèi)的數(shù)據(jù)進行運算。

①設置分組列

BREAKON分組列名一按某一列分組

BREAKON分組列名SKIPn—按某一列分組，兩組之間空n行

BREAKON分組列名SKIPPAGE^按某一列分組，兩組之間

換頁

BREAKONREPORT—按報表分組，整個報表為一組

查詢結(jié)果的報表輸出

注意：

①每次只有一個BREAK命令起作用，但一次可以在

多個列上使用BREAK命令

SQL>BREAKON歹4名1ON歹4名2

②在SELECT語句中帶有ORDERBY子句與BREAK

對應，如果BREAK設置使用BREAKON分組列名,

貝帕ELECT語句中必須按分組列排序。如果BREAK語

句使用RE