數(shù)據(jù)庫(kù)原理第3章關(guān)系數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)語(yǔ)言SQL

數(shù)據(jù)庫(kù)原理

PrinciplesofDatabase

第3章關(guān)系數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)語(yǔ)言SQL

湖南工學(xué)院計(jì)算機(jī)系

庫(kù)原理

第3章關(guān)系數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)語(yǔ)言SQL

■【本章掌握內(nèi)容】

1、數(shù)據(jù)定義語(yǔ)言的使用方法

2、數(shù)據(jù)操作語(yǔ)言的使用方法

【本章了解內(nèi)容】

1、存儲(chǔ)過程的編寫

2、觸發(fā)器的編寫

第3章關(guān)系數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)語(yǔ)言SQL

.3.1SQL概述

■3.2SQL的數(shù)據(jù)定義

-3.3SQL的數(shù)據(jù)操縱

-3.4視圖

-3.5觸發(fā)器

數(shù)據(jù)庫(kù)原理

3.1SQL概述

■SQL最早是1974年由Boyce和Chamberlin提出，

并作為IBM公司研制的關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)原

型SystemR的一部分付諸實(shí)施的。

■現(xiàn)在SQL已經(jīng)成了關(guān)系數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)語(yǔ)言，并

且發(fā)展了三個(gè)主要標(biāo)準(zhǔn)，WANSI（美國(guó)國(guó)家標(biāo)

準(zhǔn)機(jī)構(gòu)）SQL；對(duì)ANSISQL修改后在1992年采

納的標(biāo)準(zhǔn)，稱為SQL-92或SQL2；最近又出了

SQL-99,也稱SQL3標(biāo)準(zhǔn)。

數(shù)據(jù)庫(kù)原理

3.1.1SQL的特點(diǎn)

■1.綜合統(tǒng)一

■(1)數(shù)據(jù)定義語(yǔ)言(DataDe行nitionLanguage,

DDL)oDDL用于定義數(shù)據(jù)庫(kù)的邏輯機(jī)構(gòu)，是對(duì)關(guān)

系模式一級(jí)的定義，包括基本表、視圖及索引的定

義。

■(2)數(shù)據(jù)查詢語(yǔ)言(DataQueryLanguage,

DQL)oDQL用于查詢數(shù)據(jù)。

■(3)數(shù)據(jù)操縱語(yǔ)言(DataManipulationLanguage,

DML)oDML用于對(duì)關(guān)系模式中的具體數(shù)據(jù)進(jìn)行增、

冊(cè)h改等操作。

■(4)數(shù)據(jù)控制語(yǔ)言(DataControlLanguage,

DCL)oDCL用于數(shù)據(jù)訪問權(quán)限的控制。

數(shù)據(jù)庫(kù)原理

2.高度非過程化

-用戶無需了解存取路徑，存取路徑的選擇以及SQL

語(yǔ)句的操作過程由系統(tǒng)自動(dòng)完成。這不但大大減輕

了用戶負(fù)擔(dān)，而且有利于提高數(shù)據(jù)獨(dú)立性。

3.用同一種語(yǔ)法結(jié)構(gòu)提供兩種使用方式

-SQL語(yǔ)言既是自含式語(yǔ)言，又是嵌入式語(yǔ)言。作為

自含式語(yǔ)言，它能夠獨(dú)立地用于聯(lián)機(jī)交互的使用方

式，用戶可以在終端鍵盤上直接輸入SQL命令對(duì)數(shù)

據(jù)庫(kù)進(jìn)行操作。作為嵌入式語(yǔ)言，SQL語(yǔ)句能夠嵌

入到高級(jí)語(yǔ)言（例如C）程序中，供程序員設(shè)計(jì)程

序時(shí)使用。

4語(yǔ)言簡(jiǎn)潔，易學(xué)易用

數(shù)據(jù)庫(kù)原理

3.1.2SQL語(yǔ)言的基本概念

數(shù)據(jù)庫(kù)的體系結(jié)構(gòu)分為三級(jí)，SQL也支持這三級(jí)模式結(jié)構(gòu)

■1.基本表(BaseTable)

基本表是模式的基本內(nèi)容。實(shí)際存儲(chǔ)在數(shù)據(jù)庫(kù)中的表對(duì)應(yīng)

一個(gè)實(shí)際存在的關(guān)系。

■2.視圖(View)

視圖是外模式的基本單位，用戶可以通過視圖使用數(shù)據(jù)庫(kù)

中基于基本表的數(shù)據(jù)。視圖是從其它表(包括其它視圖)

史導(dǎo)出的表，它僅是保存在數(shù)據(jù)的一種邏輯定義字典中,

本身并不獨(dú)立存儲(chǔ)在數(shù)據(jù)庫(kù)中，因此視圖是一種虛表。

■3.存儲(chǔ)文件

存儲(chǔ)文件是內(nèi)模式的基本單位。一個(gè)基本表對(duì)應(yīng)一個(gè)或多

個(gè)存儲(chǔ)文件，一個(gè)存儲(chǔ)文件可以存放在一個(gè)或多個(gè)基本

表，一個(gè)基本表可以有若干個(gè)索弓，索引同樣存放在存

儲(chǔ)文件市。存儲(chǔ)文件的存儲(chǔ)結(jié)構(gòu)對(duì)用戶來說是透明的。

數(shù)據(jù)庫(kù)原理

SQL支持的數(shù)據(jù)庫(kù)體系結(jié)構(gòu)

SQL用戶SQL用戶

外模式

基本表2基本表3

存儲(chǔ)文件1存儲(chǔ)文件2存儲(chǔ)文件3內(nèi)模式

數(shù)據(jù)庫(kù)原理

3.2SQL的數(shù)據(jù)定義

-通過SQL語(yǔ)言的數(shù)據(jù)定義功能，可以完成

基本表、視圖、索引的創(chuàng)建和修改

數(shù)據(jù)庫(kù)原理

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

1）數(shù)值型

-INTEGER：定義數(shù)據(jù)類型為整數(shù)類型，它的精

度（總宥效彳立）由我行機(jī)箱確定。

■SMALLINT：定義數(shù)據(jù)類型為短整數(shù)類型，它

的精度由執(zhí)行機(jī)構(gòu)確定。

■NUMERIC（ps）:定義數(shù)據(jù)類型為數(shù)值型，并給

定精度P（總z的有效位）或標(biāo)度s（十進(jìn)制小數(shù)

點(diǎn)右面的位數(shù)）。

■FLOAT（p）:定義數(shù)據(jù)類型為浮點(diǎn)數(shù)值型，其精

度等于或大于給定的精度P。

■REAL:一定義數(shù)據(jù)類型為浮點(diǎn)數(shù)值型，它的精度

由執(zhí)行機(jī)構(gòu)確定。

數(shù)據(jù)庫(kù)原理

-DOUBL巨PRECISION:定義數(shù)據(jù)類型為雙精度浮點(diǎn)類型，它的精

度由執(zhí)行機(jī)構(gòu)確定。

1(2)字符類型

■CHARACTER(n):定義數(shù)據(jù)類型為字符串，并給定串長(zhǎng)度(字符

數(shù))O

-VARCHAR(n):定義可變長(zhǎng)度的字符串，其最大長(zhǎng)度為n。

(3)位串型

-BIT(n):定義數(shù)據(jù)類型為二進(jìn)制位串，其長(zhǎng)度為n。

-BITVARYING(n):定義可變長(zhǎng)的二進(jìn)制位串，其最大長(zhǎng)度為n。

(4)時(shí)間型

■DATETIM巳定義一個(gè)日期時(shí)間類型，日期和時(shí)間數(shù)據(jù)由有效的日

期或時(shí)間組成。

(5)布爾型

-BOOLEAN:定義布爾數(shù)，其值可以是TRUE(真)或FALSE(假)。

庫(kù)原理

3.2.1基本表的定義

CREATETABLE〈基本表名〉

(V列名1>V列數(shù)據(jù)類型〉［列完整性約

束］,

〈列名2>〈列數(shù)據(jù)類型〉［列完整性約

束］,

［表級(jí)完整性約束］)

數(shù)據(jù)庫(kù)原理

創(chuàng)建基本表例子

■例3.1創(chuàng)建員工關(guān)系表。

CREATETABLEEmployee

(EnoCHAR(5),

EnameVARCHAR(10),

SexCHAR(2),

MarryCHAR(2),

DnoCHAR(4))；

數(shù)據(jù)庫(kù)原理

■例3.2創(chuàng)建員工關(guān)系表時(shí)加入列完整性約束。

CREATETABLEEmployee

(EnoCHAR(5)NOTNULLUNIQUE,

EnameVARCHAR(10)NOTNULL,

SexCHAR(2)NOTNULL,

MarryCHAR(2)DEFAULT未'，

DnoCHAR(4)NULL)；

庫(kù)原理

3.2.2基本表的修改與刪除

ALTERTABLE〈基本表名〉

[ADD〈新列名〉〈列數(shù)據(jù)類型〉[列完整性

約束]]

[DROPCOLUMN〈歹U名〉]

[MODIFY〈歹U名〉v新的數(shù)據(jù)類型＞]

[ADDCONSTRAINT〈表級(jí)完整性約束＞]

[DROPCONSTRAINT〈表級(jí)完整性約束＞]

數(shù)據(jù)庫(kù)原理

基本表修改例子W

■例3.3在Employee表中增加一個(gè)Birth列（出

生）。

ALTERTALBEEmployee

ADDBirthDATETIMENULL；

■例3.4修改Employee表中Marry歹U為BOOLEAN。

ALTERTALBEEmployee

MODIFYMarryBOOLEAN；

■例3.5刪除Employee表中新增的Birth歹限

ALTERTALBEEmployee

DROPCOLUMNBirth；

數(shù)據(jù)庫(kù)原理

2.基本表的刪除

■當(dāng)數(shù)據(jù)庫(kù)某個(gè)基本表不再使用時(shí)，可以

將其刪除。當(dāng)一個(gè)基本表被刪除后，該

表中的所有數(shù)據(jù)連同該表建立的索引都

會(huì)被刪除。但由該表導(dǎo)出的視圖的定義

仍然存在數(shù)據(jù)字典當(dāng)中，只是無法使用。

DROPTABLE〈基本表名〉

數(shù)據(jù)庫(kù)原理

3.2.3索引的建立和維護(hù)

足了改善查詢性能，可以建立索引。

索引是根據(jù)表中的一列或若干列按照一定順序建立

的列值，與表中記錄行之間具有一一對(duì)應(yīng)關(guān)系的

輔助表結(jié)構(gòu)。索引屬于物理存儲(chǔ)的路徑概念，而

不是用戶使用的邏輯概念。建立在多個(gè)列上的索

引被稱為復(fù)合索引。

有兩種重要的索引:

■聚集索引(ClusteredIndex)

■非聚集索引(Non-clusteredIndex)。

數(shù)據(jù)庫(kù)原理

￥聚集索引

■聚集索引類似于按姓氏排列數(shù)據(jù)的電話

簿。由于聚集索引規(guī)定了數(shù)據(jù)在表中的

物理存儲(chǔ)順序，因此一個(gè)表只能包含一

個(gè)聚集索引。但該索引可以包含多個(gè)列

（組合索引），就像電話簿按姓氏和名

字進(jìn)行組織一樣。聚集索引對(duì)于那些經(jīng)

常要搜索范圍值的列特別有效。

數(shù)據(jù)庫(kù)原理

非聚集索引

■非聚集索引與書本中的索引類似。數(shù)據(jù)存儲(chǔ)在

一個(gè)地方，索引存儲(chǔ)在另一個(gè)地方，索引帶有

指針指向數(shù)據(jù)的存儲(chǔ)位置。索引中的項(xiàng)目按索

引鍵值的順序存儲(chǔ)，而表中的信息則按另一種

順序存儲(chǔ)（這可以由聚集索引規(guī)定）。與使用

書中索引的方式相似，DBMS在搜索數(shù)據(jù)值時(shí),

先對(duì)非聚集索引進(jìn)行搜索，找到數(shù)據(jù)值在表中

的位置，然后從該位置直接檢索數(shù)據(jù)

數(shù)據(jù)庫(kù)原理

CREATE[UNIQUE][CLUSTER]INDEX〈索弓I名

＞

ONv基本表名〉(v列名〉[＜次序＞],[,〈列

名〉[v次序＞]]...)；

說明：

(1)UNIQUE：規(guī)定索引的每一個(gè)索引值只對(duì)應(yīng)

于表中的唯一記奈。

(2)CLUSTER：規(guī)定此索引為聚集索弓I。省略

CLUSTER則表示創(chuàng)建的索弓I為非聚集索引。

(3)〈次序〉：建立索引時(shí)指定列名的索引表是

ASC(升序)或DESC(降序)。數(shù)據(jù)庫(kù)原理

創(chuàng)建索引例子

■例3.7在Employee表的屬性列Eno上創(chuàng)建

一個(gè)非聚集索引。

CREATEINDEXIDX_DNO_ENO

ONEmployee(DnoASC,EnoASO；

■例3.8在Employee表的屬性列Ename上

創(chuàng)建一個(gè)聚集索引。

CREATECLUSTERINDEXIDX_ENAME

ONEmployee(EnameASO；

數(shù)據(jù)庫(kù)原理

2.刪除索引▲

I

-過多或不當(dāng)?shù)乃饕龝?huì)導(dǎo)致系統(tǒng)低效。用

戶在表中每加進(jìn)一個(gè)索引，數(shù)據(jù)庫(kù)就要

做更多的工作。過多的索引甚至?xí)?dǎo)致

索引碎片，降低系統(tǒng)效率。

■DROPINDEX〈索引名〉

數(shù)據(jù)庫(kù)原理

3.3SQL的數(shù)據(jù)操縱

-SQL語(yǔ)言的數(shù)據(jù)操縱功能主要包括查詢

(SELECT)、插入(INSERT)、冊(cè)ij除

(DELETE)和更新(update)四個(gè)方面。

數(shù)據(jù)庫(kù)原理

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

■SQL數(shù)據(jù)查詢是SQL語(yǔ)言中最重要、最豐富也

是最靈活的內(nèi)容。

SELECTv列名或表達(dá)式Al>,v列名或表達(dá)式

A2>,...，v列名或表達(dá)式An>

FROMv表名或視圖名Rl>,v表名或視圖名

R2〉,…〈表名或視圖名Rm>

WHEREP；

■查詢的基本結(jié)構(gòu)包括了三個(gè)字句：SELECT、

FROM、WHEREo

數(shù)據(jù)庫(kù)原理

X

(1)SELECT子句，對(duì)應(yīng)關(guān)系代數(shù)中的投影良

■算，用于列出查詢結(jié)果的各屬性。

=(2)FROM子句，對(duì)應(yīng)關(guān)系代數(shù)中的廣義笛卡

爾乘積，用于列出被查詢的關(guān)系：基本表或視

圖。

■(3)WHERE子句，對(duì)應(yīng)關(guān)系代數(shù)中的選擇謂

詞，這些謂詞涉及FROM子句中的關(guān)系的屬性，

用于指出連接、選擇等運(yùn)算要滿足的查詢條件。

-SQL數(shù)據(jù)查詢的基本結(jié)構(gòu)在關(guān)系代數(shù)中等價(jià)于：

/…XR2X...XRm\)

數(shù)據(jù)庫(kù)原理

SQL數(shù)據(jù)查詢的一般格式為：

SELECT[ALL|DISTINCT]〈列名或表達(dá)式〉

[別名1][,〈列名或表達(dá)式〉[別名2]]…

FROMv表名或視圖名＞[表別名1][,〈表

名或視圖名＞[表別名

[WHERE〈條件表達(dá)式＞]

[GROUPBYV列名1＞][HAVING〈條件

表達(dá)式＞]

[ORDERBY〈歹U名2＞][ASQDESC]

數(shù)據(jù)庫(kù)原理

1.單表無條件查詢

-單表無條件查詢是指只含有SELECT子句

和FROM子句的查詢

SELECT[ALL|DISTINCT]〈列名或表達(dá)式

＞[別名1][,〈列名或表達(dá)式〉[別名

2]]…

FROMv表名或視圖名＞[表別名1][,〈表

名或視圖名＞[表別名

數(shù)據(jù)庫(kù)原理

①查詢關(guān)系中的指定列

■例3.10查詢所有學(xué)生的學(xué)號(hào)、姓名、年

齡。

SELECTSno,Sname,Age

FROMStudent;

-例3.11查詢所有課程的基本情況。

SELECTCno,Cname,Credit

FROMCourse

數(shù)據(jù)庫(kù)原理

②DISTINCT保留字的使用

當(dāng)查詢的結(jié)果只包含元表中的部分列時(shí)，結(jié)果

中可能會(huì)出現(xiàn)重復(fù)列，使用DISTINCT保留

字可以使重復(fù)列值只保留一個(gè)。

③查詢列中含有運(yùn)算的表達(dá)式

SELECT子句的目標(biāo)列中可以包含帶有十、一、

X、/的算術(shù)運(yùn)算表達(dá)式，其運(yùn)算對(duì)象為常

量或元組的屬性。

數(shù)據(jù)庫(kù)原理

列名中含有算術(shù)表達(dá)式例子

■【例3.13］查詢所有學(xué)生的學(xué)號(hào)、姓名

和出生年份。

SELECTSno,Sname,2005-Age

FROMStudent

數(shù)據(jù)庫(kù)原理

(4)查詢列中含有字符串常量

【例3.14】查詢每門課程的課程名和學(xué)分。

SELECTCname,'學(xué)分Credit

FROMCourse

■這種書寫方式可以使查詢結(jié)果增加一個(gè)

原關(guān)系里不存在的字符串常量列，元組

在該列上的每個(gè)值就是字符串常量。

數(shù)據(jù)庫(kù)原理

（5）查詢列中含有集函數(shù)

■COUNT(*)統(tǒng)計(jì)查詢結(jié)果中的元組個(gè)數(shù)

■COUNT（〈列名〉）統(tǒng)計(jì)查詢結(jié)果中一個(gè)列上值

的個(gè)數(shù)

■MAX（〈列名〉）計(jì)算查詢結(jié)果中一個(gè)列上的最大值

■MIN（〈列名〉）計(jì)算查詢結(jié)果中一個(gè)列上的

最小值

■SUM（〈列名〉）計(jì)算查詢結(jié)果中一個(gè)數(shù)值列上的總

和

■AVG（〈列名〉）計(jì)算查詢結(jié)果中一個(gè)數(shù)值列上的平

均值

①除COUNT（*）外，其他集函數(shù)都會(huì)先去掉空值再計(jì)

舁。

②在v列名〉前加入DISTINCT保留字，會(huì)將查詢結(jié)果中

重復(fù)的列去掉后再計(jì)算。

數(shù)據(jù)庫(kù)原理

列名含有函數(shù)運(yùn)算例子

■【例3.15]COUNT函數(shù)的使用。

■SELECTCOUNT(*)FROMStudent

統(tǒng)計(jì)學(xué)生表中的記錄數(shù)。

■SELECTCOUNT(Place)FROM

Student統(tǒng)計(jì)學(xué)生的籍貫(去掉空值)。

■SELECTCOUNT(DISTINCTPlace)

FROMStudent統(tǒng)計(jì)學(xué)生的籍貫種類數(shù)。

數(shù)據(jù)庫(kù)原理

2.單表帶條件查詢

■一般地，數(shù)據(jù)庫(kù)中的數(shù)據(jù)量都非常大，顯示表

中所有的行是很不現(xiàn)實(shí)的，可以在查詢的時(shí)候

根據(jù)查詢條件對(duì)表進(jìn)行水平分割，使用WHERE

子句即可實(shí)現(xiàn)。

SELECT[ALLDISTINCT]〈列名或表達(dá)式〉[別

名1][,〈列名或表達(dá)式〉[別名2]]…

FROMv表名或視圖名>[表別名1][,〈表名或

視圖名>[表別名2]]…

WHEREv條件表達(dá)式〉

數(shù)據(jù)庫(kù)原理

(1)使用關(guān)系運(yùn)算表達(dá)式的查詢

■使用比較運(yùn)算符的條件表達(dá)式的一般形式為:

v列名〉列名〉和〈列名常量值

■例3.17查詢籍貫是湖北的學(xué)生信息。

SELECT*

FROMStudent

WHERESace='湖」匕'

數(shù)據(jù)庫(kù)原理

■例3.18查詢選修了C01號(hào)課程且考試及

格的學(xué)生學(xué)號(hào)和成績(jī)。

SELECTSno,Grade

FROMStudy

WHERECno=5C0rANDGrade>=60

庫(kù)原理

(2)使用特殊運(yùn)算符

SI標(biāo)準(zhǔn)SQL允許在WHERE子句中使用特殊的運(yùn)算

符。

運(yùn)算符含義

IN、NOTIN判斷屬性值是否在一個(gè)集合內(nèi)

BETWEEN...AND...,NOT判斷屬性值是否在某個(gè)范圍中

BETWEEN...AND...

ISNULL、ISNOTNULL判斷屬性值是否為空

LIKE、NOTLIKE判斷字符串是臺(tái)匹配

庫(kù)原理

■例3.19查詢籍貫為湖北和福建兩地的學(xué)生信息。

SELECT*

FROMStudent

WHEREPlaceIN（'湖」匕福建’）

相當(dāng)于

SELECT*

FROMStudent

WHEREPlace='湖北'ORplace='福建')

庫(kù)原理

■例3.20從Study表中查詢考試成績(jī)?cè)?5和95之

間的學(xué)生學(xué)號(hào)。

SELECTSno

FROMStudy

WHEREGradeBETWEEN85AND95

SELECTSno

FROMStudy

WHEREGrade>=85ANDGrade<=95

數(shù)據(jù)庫(kù)原理

(3)字符串比較

■在SQL中可以使用關(guān)系運(yùn)算符來進(jìn)行字符串比

較。實(shí)際上比較的是它們的詞典順序(如字典

順序或字母表順序)。

■如果果a2.?.an和和b2...bm是兩個(gè)字符串,若

alvbl或al=bl且a2Vb2或al=bl,a2=b2且

a3Vb3,當(dāng)/7V/77并且1己2...3〃=131^2.?.13〃時(shí),

字符串a(chǎn)la2...az7Vbib2...b〃7,也就是第一個(gè)

字符串正好是第二個(gè)字符串的前綴。

■例如foddervfoo

數(shù)據(jù)庫(kù)原理

■SQL也提供了一種簡(jiǎn)單的模式匹配功能用

于字符串比較，可以使用LIKE和NOT

LIKE來實(shí)現(xiàn)二和v＞的比較功能

■基本格式為：

v列名〉LIKE/NOTLIKE〈字符串常數(shù)〉

庫(kù)原理

通配符

序符串常數(shù)中通常要使用通配符。在字符串常數(shù)中

除通配符外的其它字符只代表自己。通配符可以出

現(xiàn)在字符串的任何位置。但通配符出現(xiàn)在字符串首

時(shí)查詢效率會(huì)變慢。

通配符含義

%表示任意長(zhǎng)度的字符

串

_（下劃線）表示任意的單個(gè)字符

數(shù)據(jù)庫(kù)原理

-例3.21查詢姓王的學(xué)生的學(xué)號(hào)、姓名、年齡。

SELECTSno,Sname,Age

FROMStudent

WHERESnameLIKE'王％’

■若通配符本身就是字符串常量的內(nèi)容，則可增

加短語(yǔ)ESCAPE,使之轉(zhuǎn)義。

如：WHEREsnameLIKE'To\_%'ESCAPE、，

則緊跟在\后面的_不是通配符而是字符串常

量的一個(gè)字符而已。該查詢條件為：Sname屬

性值以“To」開頭，后面緊跟任意個(gè)字符的串。

庫(kù)原理

Il3.分組查詢和排序查詢

■⑴GROUPBY與HAVING

-含有GROUPBY的查詢稱為分組查詢。

GROUPBY子句把一個(gè)表按某一指定列（或

一些列）上的值相等的原則分組，然后再對(duì)

每組數(shù)據(jù)進(jìn)行規(guī)定的操作。分組查詢一般和

查詢列的集函數(shù)一起使用，當(dāng)使用GROUP

BY子句后，所有的集函數(shù)都將是對(duì)每一個(gè)組

進(jìn)行運(yùn)算，而不是對(duì)整個(gè)查詢結(jié)構(gòu)進(jìn)行運(yùn)算。

數(shù)據(jù)庫(kù)原理

■例3.22查詢每一門課程的平均得分。

在Study關(guān)系表中記錄著學(xué)生選修的每門課程和相應(yīng)的考試

I成績(jī)。由于一門課程可以有若干個(gè)學(xué)生學(xué)習(xí)，SELECT語(yǔ)

|句執(zhí)行時(shí)首先把表Study的全部數(shù)據(jù)行按相同課程號(hào)劃分

成組，即每二門課程都有一組學(xué)生和相應(yīng)的成績(jī)，然后

苒對(duì)各組執(zhí)行AVG(grade)

SELECTCno,AVG(Grade)

FROMStudy

GROUPBYCno

數(shù)據(jù)庫(kù)原理

詢課程的平均得分

CnoAVG(Grade)

C0175.5

C0283

C0375

C0473.5

C0597

數(shù)據(jù)庫(kù)原理

X

■例3.23查詢被3人以上選修的每一門課程

均成績(jī)、最高分、最低分。

SELECTCno,AVG(Grade),MAX(Grade),

MIN(Grade)

FROMStudy

GROUPBYCno

HAVINGCOUNT(*)>=3

■本例中SELECT語(yǔ)句執(zhí)行時(shí)首先按Cno把表

Study分組，然后對(duì)各組的記錄執(zhí)行

AVG(Grade)>MAX(Grade)>MIN(Grade)等集

函數(shù)，最后根據(jù)HAVING字句的條件表達(dá)式

COUNT(*)>=3過濾出組中記錄數(shù)在3條以上的

分組。

庫(kù)原理

(2)排序查詢

■SELECT子句的ORDERBY子句可使輸出的查詢結(jié)果

按照要求的順序排列。由于是控制輸出結(jié)果，因此

ORDERBY子句只能用于最終的查詢結(jié)果。基本格

式是：

■ORDERBY〈歹U名〉[ASC|DESC]

■有了ORDERBY子句后，SELECT語(yǔ)句的查詢結(jié)果表

中各元組將按照要求的順序排列：首先按第一個(gè)v

列名〉值排列；前一個(gè)V列名＞相同的，再按下一個(gè)

v列名〉值排列

數(shù)據(jù)庫(kù)原理

■例324查詢所有學(xué)生的基本信息，并按

年齡升序排列，年齡相同的按學(xué)號(hào)降序

排列。

SELECT*FROMStudent

ORDERBYAge,SnoDESC

數(shù)據(jù)庫(kù)原理

4,多表查詢

■在數(shù)據(jù)庫(kù)中通常存在著多個(gè)相互關(guān)聯(lián)的

表，用戶常常需要同時(shí)從多個(gè)表中找出

自己想要的數(shù)據(jù)，這就涉及到多個(gè)數(shù)據(jù)

表的查詢。SQL通過連接查詢、并操作、

交操作、差操作可以實(shí)現(xiàn)關(guān)系代數(shù)中的

運(yùn)算功能。

庫(kù)原理

(1)連接查詢

SQL提供在FROM子句中列出每個(gè)關(guān)系，然后在

SELECT子句和WHERE子句中引用FROM子句中的

關(guān)系的屬性，而WHERE子句中用來連接兩個(gè)關(guān)系

的條件稱為連接條件。

■例3.25查詢籍貫為湖北的學(xué)生的學(xué)號(hào)、選修的課

程號(hào)和相應(yīng)的考試成績(jī)。

SELECTStudent.Sno,Cno,Grade

FROMStudent,Study

WHEREStudent.Sno=Study.SnoANDPlaceLIKE

湖北，

數(shù)據(jù)庫(kù)原理

④在等值連接中，目標(biāo)列可能出現(xiàn)重復(fù)的

歹！J,例如:

SELECTStudent.*,Study■*

FROMStudent,Study

WHEREStudent-Sno=Study-SnoAND

PlaceLIKE'湖北'

■這里Student■Sno和Study■Sno是兩個(gè)

重復(fù)列

數(shù)據(jù)庫(kù)原理

■⑤還有一種特殊的連接運(yùn)算，它不帶連

接條件，稱為乘積運(yùn)算

SELECTStudent-Sno,Cno,Grade

FROMStudent,Study

兩個(gè)關(guān)系的乘積會(huì)產(chǎn)生大量沒有意義的元組，

并且這種操作要消耗大量的系統(tǒng)資源，一般

很少使用。

數(shù)據(jù)庫(kù)原理

可以進(jìn)行兩個(gè)以上的連接。

■例326查詢籍貫為湖北的學(xué)生的姓名、

選修的課程名稱和相應(yīng)的考試成績(jī)。

SELECTSname,Cname,Grade

FROMStudent,Study,Course

WHEREStudent.Sno=Study.Sno

ANDStudy.Cno=Course.Cno

ANDPlaceLIKE'湖北'

數(shù)據(jù)庫(kù)原理

(3)自身連接

■有一種連接是一個(gè)關(guān)系與自身進(jìn)行的連

接，這種連接稱做自身連接。SQL允許為

FROM子句中的關(guān)系R的每一次出現(xiàn)定義

一個(gè)別名。這樣在SELECT子句和WHERE

子句中的屬性前面就可以加上“別名

屬性名

數(shù)據(jù)庫(kù)原理

例327查詢籍貫相同的兩個(gè)學(xué)生的基本

信息。

SELECTA.*

FROMStudentA,StudentB

WHEREA.Place=B.PIace

■該例中要查詢的內(nèi)容屬于表Student。上面

的語(yǔ)句將表Student分別取兩個(gè)別名A、B。

這樣A、B相當(dāng)于內(nèi)容相同的兩個(gè)表。將A和

B中籍貫相同的元組進(jìn)行連接，經(jīng)過投影就

得到了滿足要求的結(jié)果。

數(shù)據(jù)庫(kù)原理

(3)并操作

■SQL使用UNION把查詢的結(jié)果并起來，并去掉

重復(fù)的元組，如果要保留所有重復(fù)，則必須使

用UNIONALLo

-【例3.28】查詢籍貫是湖北的學(xué)生以及姓張的

學(xué)生的基本信息。

SELECT*

FROMStudent

WHEREPlaceLIKE'湖北'

UNION

SELECT*

FROMStudent

WHERESnameLIKE'張％‘

數(shù)據(jù)庫(kù)原理

(4)交操作

■SQL使用INTERSECT把同時(shí)出現(xiàn)在兩個(gè)查詢中的結(jié)果取出,

實(shí)現(xiàn)交操作，并且也會(huì)去掉重復(fù)的元組，如果要保留所

有重復(fù)，則必須使用INTERSECTALL。

■例3.29查詢年齡大于18歲、姓張的學(xué)生的基本信息。

SELECT*

FROMStudent

WHEREAge>18

INTERSECT

SELECT*

FROMStudent

WHERESnameLIKE'張％|

數(shù)據(jù)庫(kù)原理

(5)差操作

SQL使用MINUS把出現(xiàn)在第一個(gè)查詢結(jié)果中但不

出現(xiàn)在第二個(gè)查詢結(jié)果中的元組取出，實(shí)現(xiàn)差

作。

■例330查詢年齡大于20歲的學(xué)生基本信息與女

生的基本信息的差集。

SELECT*

FROMStudent

WHEREAge>20

MINUS

SELECT*

FROMStudent

WHERESexLIKE女

數(shù)據(jù)庫(kù)原理

5.嵌套查詢

■嵌套查詢是指一個(gè)SELECT-FROM-WHERE查詢

塊嵌入在另一個(gè)SELECT-FROM-WHERE查詢

塊WHERE子句中的查詢。

■外層查詢稱為父查詢，內(nèi)層查詢稱為子查詢。

子查詢中還可以嵌套其它子查詢，即允許多層

嵌套查詢，其執(zhí)行過程是由里到外的，每一個(gè)

子查詢是在上一級(jí)查詢處理之前完成的

■在子查詢中不能使用ORDERBY子句。

數(shù)據(jù)庫(kù)原理

(1)返回單值的子查詢

■子查詢返回的檢索信息是單一的值。這類子查

詢看起來就像常量一樣，因此經(jīng)常把這類子查

詢的結(jié)果與父查詢的屬性用關(guān)系運(yùn)算符來比較。

■例331查詢選修了C語(yǔ)言的學(xué)生的學(xué)號(hào)和相應(yīng)

的考試成績(jī)。

SELECTSno,Grade

FROMStudy

WHERECno=

(SELECTCno

FROMCourse

WHERECnameLIKE'C語(yǔ)言')

數(shù)據(jù)庫(kù)原理

-例332查詢考試成績(jī)大于總平均分的學(xué)生學(xué)號(hào)。

SELECTDISTINCTSno

FROMStudy

WHEREGrade>

(SELECTAVG(Grade)

FROMStudy)

■在嵌套查詢中，只有能確切知道內(nèi)層查詢返回

的是單值時(shí)，才可以直接使用關(guān)系運(yùn)算符進(jìn)行

比較。

數(shù)據(jù)庫(kù)原理

(2)返回多值的子查詢

■使用ALL、ANY等謂詞來解決。

■例3.33查詢成績(jī)至少比選修了C02號(hào)課程的一個(gè)學(xué)生成績(jī)

低的學(xué)生學(xué)號(hào)。

SELECTSno

FROMStudy

WHEREGrade<ANY

(SELECTGrade

FROMStudy

WHERECno='C02')

ANDCno<>'C02'

■ANY運(yùn)算符表示至少一個(gè)或某一個(gè)，因此使用〈ANY就可表

示至少比某集合中的一個(gè)少的含義

數(shù)據(jù)庫(kù)原理

■實(shí)際上，比最大的值小就等價(jià)于〈ANY,

該例子可用聚合函數(shù)MAX來實(shí)現(xiàn)。

SELECTSno

FROMStudy

WHEREGrade<

(SELECTMAX(Grade)

FROMStudy

WHERECno='C02')

ANDCno<>'C02'

數(shù)據(jù)庫(kù)原理

■例334查詢成績(jī)比所有選修了C02號(hào)課程的學(xué)

生成績(jī)低的學(xué)生學(xué)號(hào)。

SELECTSno

FROMStudy

WHEREGrade<ALL

(SELECTGrade

FROMStudy

WHERECno='C02')

ANDCno<>'C02'

■ALL運(yùn)算符表示所有或者每個(gè)，因此使用〈ALL

就可表示至少比某集合所有都少的含義。實(shí)際

上，比最小的值小就等價(jià)于〈ALL

數(shù)據(jù)庫(kù)原理

■該例子可用聚合函數(shù)MIN來實(shí)現(xiàn)。

SELECTSno

FROMStudy

WHEREGrade<

(SELECTMIN(Grade)

FROMStudy

WHERECno=,C02,)

ANDCno<>C02'

數(shù)據(jù)庫(kù)原理

■對(duì)于在父查詢中需要判斷某個(gè)屬性的值與子查詢結(jié)果

中某人值相等的這類查詢可以用IN實(shí)現(xiàn)，也就是說可

以用IN來代替“二ANY”。

■例3.35查詢選修了C語(yǔ)言的學(xué)生的基本信息。

SELECT*

FROMStudent

WHERESnoIN

(SELECTSno

FROMStudy

WHERECnoIN

SELECTCno

FROMCourse

WHERECnameLIKE￡語(yǔ)言')

數(shù)據(jù)庫(kù)原理

(3)相關(guān)子查詢

■子查詢的查詢條件依賴于父查詢，每次

要對(duì)子查詢中的外部元組變量的某一項(xiàng)

賦值，這類子查詢稱為相關(guān)子查詢。

■在相關(guān)子查詢中經(jīng)常使用EXISTS謂詞。

子查詢中含有EXISTS謂詞后不會(huì)返回任

何結(jié)果，只得到“真”或“假”。

數(shù)據(jù)庫(kù)原理

【例3.36】查詢選修了C語(yǔ)言的學(xué)生的學(xué)號(hào)”

SELECTSno

FROMStudy

WHEREEXISTS

（SELECT*

FROMCourse

WHEREStudy.Cno=Course.CnoANDCnameLIKE（語(yǔ)言'）

首先取外層查詢中Study表的第一個(gè)元組，根據(jù)它

與內(nèi)層查詢相關(guān)的屬性值（即Cno值）處理內(nèi)層

查詢，若WHERE子句的返回值為真（即內(nèi)層查詢

結(jié)果非空），則取此元組放入結(jié)果表中

庫(kù)原理

■與EXISTS謂詞相對(duì)應(yīng)的是NOTEXISTS謂詞。使用

存在量詞NOTEXISTS后，若內(nèi)層查詢結(jié)果為空，

則外層的WHERE子句返回真值，否則返回假值。

■例337查詢所有沒選C04號(hào)課程的學(xué)生的姓名。

SELECTSname

FROMStudent

WHERENOTEXISTS

(SELECT*

FROMStudy

WHEREStudy.Sno=Student.SnoANDCno='C04')

數(shù)據(jù)庫(kù)原理

*'QL語(yǔ)言中沒有全稱量詞，因此必須利用謂詞演算將一個(gè)

f有全稱量詞的謂詞轉(zhuǎn)換為等價(jià)的帶有存在量詞的謂詞：

(Vx)夕三「(*(力))

■例3.38查詢選修了全部課程的學(xué)生姓名。

由于沒有全稱量詞，這里將題目的意思轉(zhuǎn)換成等價(jià)的存在量詞形式:

查詢這樣的學(xué)生姓名，沒有一門課程是他不選的。該查詢可以形

式化的表示如下：

■用躡示謂詞“該學(xué)生選課”

■用承示謂詞“課程”，則上述查詢可表示為，即“對(duì)于任何一門

課程該學(xué)生都選了"，將它轉(zhuǎn)換為等價(jià)的帶有存在量詞的謂詞后,

則表示為，即“沒有任何一門課程該學(xué)生不選”。

數(shù)據(jù)庫(kù)原理

■該查詢涉及3個(gè)關(guān)系表：存放學(xué)生姓名的Student表、

存放所有課程信息的Course表、存放學(xué)生選課信息的

Study關(guān)系表。其SQL語(yǔ)句如下：

SELECTSname

FROMStudent

WHERENOTEXISTS

(SELECT*

FROMCourse

WHERENOTEXISTS

(SELECT*

FROMStudy

WHERESno=Student.SnoANDCno=Course.Cno))

數(shù)據(jù)庫(kù)原理

■SQL語(yǔ)言中也沒有蘊(yùn)函邏輯運(yùn)算，因此必須利

用謂詞演算將一個(gè)邏輯蘊(yùn)函的謂詞轉(zhuǎn)換為等價(jià)

的帶有存在量詞的謂詞：

pfq三一p7q

數(shù)據(jù)庫(kù)原理

■」列3.39查詢至少選修了學(xué)生03061選修的全部課程的學(xué)生

孽號(hào)。

本題的查詢要求解釋如下：查詢這樣的學(xué)生，凡是03061選

修的課程，他都選了。換句話說，若有一個(gè)學(xué)號(hào)為知勺學(xué)

生，對(duì)所有的課程/只要學(xué)號(hào)為03061的學(xué)生選修了課程

y,則她選修了％那么就將他的學(xué)號(hào)選出來。該查詢可

以形式化如下：

■用「表示謂詞“學(xué)生03061選修了課程廣

■用速示謂詞“學(xué)生梃修了課程必

則上述查詢可表示為：該查詢可轉(zhuǎn)換為如下等價(jià)形式：

（Vv）（夕一夕）三「方（「（夕告夕））三「寺（」（?v夕））三-3y（pA「q）

數(shù)據(jù)庫(kù)原理

■它所表達(dá)的語(yǔ)義為：不存在這樣的課程％學(xué)生03061

選了％而學(xué)生櫻有選。用SQL語(yǔ)言可表示如下：

SELECTDISTINCTSno

FROMStudyX

WHERENOTEXISTS

(SELECT*

FROMStudyY

WHEREY.Sno='0306rANDNOTEXIST

(SELECT*

FROMStudyZ

WHEREZ.Sno=X.SnoANDZ.Cno=Y.Cno))

數(shù)據(jù)庫(kù)原理

3.3-2插入數(shù)據(jù)

當(dāng)基本表建立以后，就可以往表中插入數(shù)

據(jù)了，在SQL中插入數(shù)據(jù)使用INSERT語(yǔ)

句實(shí)現(xiàn)。INSERT語(yǔ)句有兩種插入形式：

■插入單個(gè)元組

■插入多個(gè)元組。

數(shù)據(jù)庫(kù)原理

1.插入單個(gè)元組

INSERTINTOv基本表名>［（v歹U名1>,<

列名2>,…，<列名n>）］

VALUES（〈列值1>,<列值2>,...，〈歹U

值n>）

■其中，〈基本表名〉指定要插入元組的表

的名字；〈歹U名1>,〈歹U名2>,…，〈歹U

名n>為要添加列值的列名序列；VALUES

后則一一對(duì)應(yīng)要添加列的輸入值。

數(shù)據(jù)庫(kù)原理

■例340在學(xué)生表中插入一個(gè)學(xué)生記錄

(04027,肖文，男，19,福建)。

INSERTINTOStudent

VALUES('04027',1肖文男',19,'福建')

■例341在學(xué)習(xí)表中插入一個(gè)學(xué)生選課記

錄(04027,05)o

INSERTINTOStudy(Sno,Cno)

VALUES('04027705')

數(shù)據(jù)庫(kù)原理

2.插入多個(gè)元組

INSERTINTOv基本表名>［（v歹U名1>,<

列名2>,…，<列名n>）］子查詢

■這種形式可將子查詢的結(jié)果集一次性插

入基本表中。如果列名序列省略，則子

查詢所得到的數(shù)據(jù)列必須和要插入數(shù)據(jù)

的基本表的數(shù)據(jù)列完全一致。如果列名

序列給出，則子查詢結(jié)果與列名序列要

一一對(duì)應(yīng)。

數(shù)據(jù)庫(kù)原理

■【例3.42］如果已建有課程平均分表

Course_avg(Cno,Average),其中average表

示每門藻程的平均分，向Course_avg表中插入

每門課程的平均分記錄。

INSERTINTOCourse_avg(Cno,Average)

SELECTCno,AVG(Grade)

FROMStudy

GROUPBYCno

數(shù)據(jù)庫(kù)原理

3.3.3刪除數(shù)據(jù)

■SQL提供了DELETE語(yǔ)句用于刪除每一個(gè)

表中的一行或多行記錄。DELETE是數(shù)據(jù)

操縱語(yǔ)句，只是刪除表中的每條記錄，

不能刪除表的定義。

DELETE語(yǔ)句的一般格式：

■DELETEFROM〈表名＞[WHERE〈條件＞]

數(shù)據(jù)庫(kù)原理

■例343刪除籍貫為湖北的學(xué)生基本信息。

DELETEFROMStudent

WHEREPlaceLIKE'湖北'

此查詢會(huì)將籍貫列上值為“湖北”的所有記錄全部刪除。

在WHERE條件中同樣可以使用復(fù)雜的子查詢。

■例344刪除成績(jī)不及格的學(xué)生的基本信息。

DELETEFROMStudent

WHERESnoIN

(SELECTSno

FROMStudy

WHEREGrade<60)

數(shù)據(jù)庫(kù)原理

3.3.4修改數(shù)據(jù)

■在SQL中使用UPDATE語(yǔ)句修改滿足指定

條件元組的指定列值。滿足指定條件的

元組可以是一個(gè)元組，也可以是多個(gè)元

組。UPDATE語(yǔ)句的一般格式為：

UPDATE〈基本表名〉

SETv列名〉=〈表達(dá)式〉[,v列名〉=〈表

達(dá)式…

[WHERE〈條件＞]

數(shù)據(jù)庫(kù)原理

■【例3.45】將數(shù)據(jù)庫(kù)原理的學(xué)分改為3。

UPDATECourse

SETCredit=3

WHERECnameLIKE'數(shù)據(jù)庫(kù)原理'

■在WHERE條件中同樣可以使用復(fù)雜的子查詢。

-【例3.46】將所有選了數(shù)據(jù)結(jié)構(gòu)的學(xué)生成績(jī)加5分。

UPDATEStudy

SETGrade=Grade+5

WHEREenoIN

（SELECTCnoFROMCourse

WHERECnameLIKE，數(shù)據(jù)結(jié)構(gòu)'）

數(shù)據(jù)庫(kù)原理

■3.4視圖

施鹵是外模式的基本單位，從用戶觀點(diǎn)來看,

視圖和基本表是一樣的。實(shí)際上視圖是從若

干個(gè)基本表或視圖導(dǎo)出來的表，因此當(dāng)基本

表的數(shù)據(jù)發(fā)生變化時(shí)，相應(yīng)的視圖數(shù)據(jù)也會(huì)

隨之改變。視圖定義后，可以和基本表一樣

被用戶查詢、更新，但通過視圖來更新基本

表中的數(shù)據(jù)要有一定的限制。

數(shù)據(jù)庫(kù)原理

3.4.1建立視圖

SQL語(yǔ)言用CREATEVIEW命令建立視圖，其一般

格式為：

CREATEVIEWv視圖名＞[（〈歹U名＞[,〈歹U名

＞]...）]

AS（子查詢）

[WITHCHECKOPTION]

■WITHCHECKOPTION是可選項(xiàng)，該選項(xiàng)表示

對(duì)所建視圖進(jìn)行INSERT、UPDATE和DELETE

操作時(shí)，讓系統(tǒng)檢查該操作的數(shù)據(jù)是否滿足子

查詢甲WHERE于句里限定的條件，若不滿足，

則系統(tǒng)拒絕執(zhí)彳亍。數(shù)據(jù)庫(kù)原理

-例3.47建立一個(gè)湖北考籍的學(xué)生信息視圖。

CREATEVIEWHBStudent

AS

SELECTSno,Sname,Sex,Age

FROMStudent

■WHEREPlaceLIKE'湖北'

-例3.48建立一個(gè)學(xué)習(xí)了C語(yǔ)言的所有學(xué)生信息的視圖。

CREATEVIEWC_stud_info(Sno,Sname,Sex,Place)

AS

SELECTSno,Sname,Sex,Place

FROMStudent

WHERESnoIN

(SELECTSno

FROMStudy

WHERECnoIN

(SELECTCno

FROMCourse

WHERECndmeLIKE'C語(yǔ)言'))

數(shù)據(jù)庫(kù)原理

3.4.2刪除視圖

■在SQL中刪除視圖使用DROPVIEW語(yǔ)句,

其一般格式為：

■DROPVIEW〈視圖名〉

■例3.49刪除視圖HBStudent。

DROPVIEWHBStudent

本例將從數(shù)據(jù)字典中刪除視圖HBStudent的定

義。

數(shù)據(jù)庫(kù)原理

3.4.3查詢視圖

視圖已經(jīng)建立，用戶就可對(duì)視圖進(jìn)行查詢操作。從用戶角

度來說，查詢視圖與查詢基本表是一樣的，可是視圖是存

在于數(shù)據(jù)庫(kù)當(dāng)中的虛表，所以DBMS執(zhí)行對(duì)視圖的查詢實(shí)

際上是根據(jù)視圖的定義轉(zhuǎn)換成等價(jià)的對(duì)基本表的查詢。

因此DBMS對(duì)某SELECT語(yǔ)句進(jìn)行處理時(shí)，若發(fā)現(xiàn)被查詢對(duì)

象是視圖，貝IDBMS將進(jìn)行下述操作：

(1)從數(shù)據(jù)字典中取出視圖的定義。

(2)把視圖定義的子查詢和本SELECT語(yǔ)句定義的查詢相結(jié)合，生

成等價(jià)的對(duì)基本表的查詢(此過程稱為視圖的消解)。

(3)執(zhí)行對(duì)基本表的查詢，把查詢結(jié)果(作為本次對(duì)視圖的查詢結(jié)

果)向用戶顯示。

庫(kù)原理

X

【例3.50】在例3.47建立的視圖HBStudent#&

找年齡大于20歲的學(xué)生基本信息。

SELECTSno,Sname,Sex,Age

FROMHBStudent

WHEREAge>20

本例在執(zhí)行時(shí)會(huì)