數(shù)據(jù)庫原理與應(yīng)用 教學(xué)課件 作者 林 小 玲第3章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言

上海大學(xué)自動(dòng)化系林小玲第3章關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言3

3.1.SQL概述3.2.學(xué)生-課程數(shù)據(jù)庫3.3.數(shù)據(jù)定義3.4.數(shù)據(jù)查詢3.5.數(shù)據(jù)更新第3章關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL本章內(nèi)容3.6.視圖4

3.1SQL概述SQL（StructuredQueryLanguage）:結(jié)構(gòu)化查詢語言，是關(guān)系數(shù)據(jù)庫的標(biāo)準(zhǔn)語言。SQL是一個(gè)通用的、功能極強(qiáng)的關(guān)系數(shù)據(jù)庫語言。5

SQL概述(續(xù))3.1.2SQL的特點(diǎn)3.1.3SQL的基本概念3.1.1SQL的產(chǎn)生和發(fā)展

6

3.1.1SQL的產(chǎn)生和發(fā)展標(biāo)準(zhǔn)發(fā)布日期SQL/861986年SQL/89(FIPS127-1)1989年SQL/921992年SQL991999年SQL20032003年7

3.1.2SQL的特點(diǎn)SQL語言是類似于英語的自然語言，簡潔易用；SQL語言是一種非過程語言；SQL語言是一種面向集合的語言；SQL語言既是自含式語言，又是嵌入式語言；SQL語言具有數(shù)據(jù)查詢、數(shù)據(jù)定義、數(shù)據(jù)操縱和數(shù)據(jù)控制四種功能。8

3.1.3SQL的基本概念SQL視圖2視圖1基本表2基本表1基本表3基本表4存儲(chǔ)文件2存儲(chǔ)文件1外模式模式內(nèi)模式SQL支持關(guān)系數(shù)據(jù)庫三級模式結(jié)構(gòu):9

SQL的基本概念（續(xù)）基本表本身獨(dú)立存在的表SQL中一個(gè)關(guān)系就對應(yīng)一個(gè)基本表一個(gè)(或多個(gè))基本表對應(yīng)一個(gè)存儲(chǔ)文件一個(gè)表可以帶若干索引存儲(chǔ)文件邏輯結(jié)構(gòu)組成了關(guān)系數(shù)據(jù)庫的內(nèi)模式物理結(jié)構(gòu)是任意的，對用戶透明視圖從一個(gè)或幾個(gè)基本表導(dǎo)出的表數(shù)據(jù)庫中只存放視圖的定義而不存放視圖對應(yīng)的數(shù)據(jù)視圖是一個(gè)虛表用戶可以在視圖上再定義視圖10

3.2學(xué)生-課程數(shù)據(jù)庫學(xué)生-課程模式S-T:

學(xué)生表：Student(Sno,Sname,Ssex,Sage,Sdept)

課程表：Course(Cno,Cname,Cpno,Ccredit)

學(xué)生選課表：SC(Sno,Cno,Grade)

11

Student表學(xué)號Sno姓名Sname性別

Ssex年齡

Sage所在系

Sdept200915121200915122200915123200915125李勇劉晨王敏張立男女女男20191819CSCSMAIS12

Course表課程號Cno課程名Cname先行課Cpno學(xué)分Ccredit1234567數(shù)據(jù)庫數(shù)學(xué)信息系統(tǒng)操作系統(tǒng)數(shù)據(jù)結(jié)構(gòu)數(shù)據(jù)處理C語言51676424342413

SC表學(xué)號Sno課程號

Cno成績

Grade

200915121200915121200915121200915122200915122

12323

928588908014

3.3.2索引的建立與修改3.3.1基本表的定義、刪除與修改

3.3數(shù)據(jù)定義15

3.3.1基本表的定義、刪除與修改二、數(shù)據(jù)類型一、定義基本表

三、修改基本表

四、刪除基本表

16

一、定義基本表CREATETABLE<表名>

（<列名><數(shù)據(jù)類型>[<列級完整性約束條件>]

[，<列名><數(shù)據(jù)類型>[<列級完整性約束條件>]]…[，<表級完整性約束條件>]）；

如果完整性約束條件涉及到該表的多個(gè)屬性列，則必須定義在表級上，否則既可以定義在列級也可以定義在表級。

17

定義學(xué)生表Student[例5]建立“學(xué)生”表Student，學(xué)號是主碼，姓名取值唯一。

CREATETABLEStudent (SnoCHAR(9)PRIMARYKEY，/*列級完整性約束條件*/

SnameCHAR(20)UNIQUE，/*Sname取唯一值*/

SsexCHAR(2)，

SageSMALLINT，

SdeptCHAR(20))；主碼18

定義課程表Course

[例6]建立一個(gè)“課程”表CourseCREATETABLECourse(CnoCHAR(4)PRIMARYKEY，

CnameCHAR(40)，

CpnoCHAR(4)，

CcreditSMALLINT，

FOREIGNKEY(Cpno)REFERENCESCourse(Cno));先修課Cpno是外碼被參照表是Course被參照列是Cno19

定義學(xué)生選課表SC[例7]建立一個(gè)“學(xué)生選課”表SC CREATETABLESC(SnoCHAR(9)，

CnoCHAR(4)，

GradeSMALLINT，

PRIMARYKEY(Sno，Cno)，

/*主碼由兩個(gè)屬性構(gòu)成，必須作為表級完整性進(jìn)行定義*/ FOREIGNKEY(Sno)REFERENCESStudent(Sno)，

/*表級完整性約束條件，Sno是外碼，被參照表是Student*/ FOREIGNKEY(Cno)REFERENCESCourse(Cno)/*表級完整性約束條件，Cno是外碼，被參照表是Course*/ );20

二、數(shù)據(jù)類型SQL中域的概念用數(shù)據(jù)類型來實(shí)現(xiàn)定義表的屬性時(shí)需要指明其數(shù)據(jù)類型及長度選用哪種數(shù)據(jù)類型由二個(gè)因素決定

取值范圍要做哪些運(yùn)算21

數(shù)據(jù)類型(續(xù))數(shù)據(jù)類型含義CHAR(n)長度為n的定長字符串VARCHAR(n)最大長度為n的變長字符串INT長整數(shù)（也可以寫作INTEGER）SMALLINT短整數(shù)NUMERIC(p，d)定點(diǎn)數(shù)，由p位數(shù)字（不包括符號、小數(shù)點(diǎn)）組成，小數(shù)后面有d位數(shù)字REAL取決于機(jī)器精度的浮點(diǎn)數(shù)DoublePrecision取決于機(jī)器精度的雙精度浮點(diǎn)數(shù)FLOAT(n)浮點(diǎn)數(shù)，精度至少為n位數(shù)字DATE日期，包含年、月、日，格式為YYYY-MM-DDTIME時(shí)間，包含一日的時(shí)、分、秒，格式為HH:MM:SS22

三、修改基本表ALTERTABLE<表名>[ADD<新列名><數(shù)據(jù)類型>[完整性約束]][DROP<完整性約束名>][ALTERCOLUMN

<列名><數(shù)據(jù)類型>]；

23

修改基本表（續(xù)）[例8]向Student表增加“入學(xué)時(shí)間”列，其數(shù)據(jù)類型為日期型。

ALTERTABLEStudentADD

S_entrance

DATE；不論基本表中原來是否已有數(shù)據(jù)，新增加的列一律為空值。

[例9]將年齡的數(shù)據(jù)類型由字符型（假設(shè)原來的數(shù)據(jù)類型是字符型）改為整數(shù)。

ALTERTABLEStudentALTERCOLUMNSageINT；[例10]增加課程名稱必須取唯一值的約束條件。

ALTERTABLECourseADD

UNIQUE(Cname);

24

四、刪除基本表

DROPTABLE<表名>［RESTRICT|CASCADE］；RESTRICT：刪除表是有限制的。欲刪除的基本表不能被其他表的約束所引用如果存在依賴該表的對象，則此表不能被刪除CASCADE：刪除該表沒有限制。在刪除基本表的同時(shí)，相關(guān)的依賴對象一起刪除25

刪除基本表(續(xù))

[例11]刪除Student表

DROPTABLEStudentCASCADE;基本表定義被刪除，數(shù)據(jù)被刪除表上建立的索引、視圖、觸發(fā)器等一般也將被刪除26

刪除基本表（續(xù)）［例12］若表上建有視圖，選擇RESTRICT時(shí)表不能刪除

CREATEVIEWIS_Student

AS SELECTSno，Sname，Sage FROMStudent WHERESdept=‘IS’；/*創(chuàng)建了一個(gè)視圖*/

DROPTABLEStudentRESTRICT;/*執(zhí)行刪除操作出錯(cuò)*/

--ERROR:cannotdroptableStudentbecauseotherobjectsdependonit

27

刪除基本表（續(xù)）[例12]如果選擇CASCADE時(shí)可以刪除表，視圖也自動(dòng)被刪除DROPTABLEStudentCASCADE; --NOTICE:dropcascadestoviewIS_Student

SELECT*FROMIS_Student;--ERROR:relation"IS_Student"doesnotexist

28

刪除基本表（續(xù)）序號標(biāo)準(zhǔn)及主流數(shù)據(jù)庫的處理方式依賴基本表的對象SQL99KingbaseESORACLE9iMSSQLSERVER2000RCRCC1.索引無規(guī)定√√√√√2.視圖×√×√√保留√保留√保留3.DEFAULT，PRIMARYKEY，CHECK（只含該表的列）NOTNULL等約束√√√√√√√4.ForeignKey×√×√×√×5.TRIGGER×√×√√√√6.函數(shù)或存儲(chǔ)過程×√√保留√保留√保留√保留√保留DROPTABLE時(shí)，SQL99與3個(gè)RDBMS的處理策略比較R表示RESTRICT,C表示CASCADE

'×'表示不能刪除基本表，'√'表示能刪除基本表，‘保留’表示刪除基本表后，還保留依賴對象

29

3.3.2索引的建立與刪除建立索引的目的：加快查詢速度誰可以建立索引DBA或表的屬主（即建立表的人,DB_Owner）DBMS一般會(huì)自動(dòng)建立以下列上的索引

PRIMARYKEYUNIQUE誰維護(hù)索引

DBMS自動(dòng)完成

使用索引

DBMS自動(dòng)選擇是否使用索引以及使用哪些索引30

索引的建立與刪除（續(xù)）RDBMS中索引一般采用B+樹、HASH索引來實(shí)現(xiàn)B+樹索引具有動(dòng)態(tài)平衡的優(yōu)點(diǎn)HASH索引具有查找速度快的特點(diǎn)采用B+樹，還是HASH索引則由具體的RDBMS來決定索引是關(guān)系數(shù)據(jù)庫的內(nèi)部實(shí)現(xiàn)技術(shù)，屬于內(nèi)模式的范疇

CREATEINDEX語句定義索引時(shí)，可以定義索引是唯一索引、非唯一索引或聚簇索引31

索引的建立與刪除（續(xù)）二、刪除索引一、定義索引

32

一、建立索引語句格式CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…) [例13]CREATECLUSTERINDEXStusnameONStudent(Sname)；在Student表的Sname（姓名）列上建立一個(gè)聚簇索引在最經(jīng)常查詢的列上建立聚簇索引以提高查詢效率一個(gè)基本表上最多只能建立一個(gè)聚簇索引經(jīng)常更新的列不宜建立聚簇索引

33

建立索引（續(xù)）

[例14]為學(xué)生-課程數(shù)據(jù)庫中的Student，Course，SC三個(gè)表建立索引。CREATEUNIQUEINDEXStusnoONStudent(Sno)；CREATEUNIQUEINDEXCoucnoONCourse(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；

Student表按學(xué)號(Sno)升序建唯一索引

Course表按課程號(Cno)升序建唯一索引

SC表按學(xué)號(Sno)升序和課程號(Cno)降序建唯一索引34

二、刪除索引DROPINDEX<索引名>；

刪除索引時(shí)，系統(tǒng)會(huì)從數(shù)據(jù)字典中刪去有關(guān)該索引的描述。[例15]刪除Student表的Stusname索引

DROPINDEXStusname35

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

SELECT[ALL|DISTINCT]<目標(biāo)列表達(dá)式>[，<目標(biāo)列表達(dá)式>]…FROM

<表名或視圖名>[，<表名或視圖名>]…[WHERE<條件表達(dá)式>][GROUPBY<列名1>[HAVING<條件表達(dá)式>]][ORDERBY<列名2>[ASC|DESC]]；

36

3.4數(shù)據(jù)查詢3.4.2連接查詢3.4.3嵌套查詢

3.4.1單表查詢3.4.4集合查詢

3.4.5Select語句的一般形式

37

3.4.1單表查詢二、選擇表中的若干元組三、ORDERBY子句

一、選擇表中的若干列四、聚集函數(shù)

五、GROUPBY子句

38

一、選擇表中的若干列2.查詢?nèi)苛?.查詢經(jīng)過計(jì)算的值

1.查詢指定列39

1.查詢指定列

[例1]查詢?nèi)w學(xué)生的學(xué)號與姓名。

SELECTSno，Sname

FROMStudent；

[例2]查詢?nèi)w學(xué)生的姓名、學(xué)號、所在系。

SELECTSname，Sno，Sdept

FROMStudent；

40

2.查詢?nèi)苛羞x出所有屬性列：在SELECT關(guān)鍵字后面列出所有列名將<目標(biāo)列表達(dá)式>指定為*

[例3]查詢?nèi)w學(xué)生的詳細(xì)記錄。SELECTSno，Sname，Ssex，Sage，Sdept

FROMStudent；或SELECT*FROMStudent；

41

3.查詢經(jīng)過計(jì)算的值SELECT子句的<目標(biāo)列表達(dá)式>可以為：算術(shù)表達(dá)式字符串常量函數(shù)列別名

42

[例4]查全體學(xué)生的姓名及其出生年份。

SELECTSname，2010-Sage/*假定當(dāng)年的年份為2010年*/FROMStudent；

輸出結(jié)果：

Sname2010-Sage-------------------

李勇 1990

劉晨 1991

王敏 1992

張立 1991查詢經(jīng)過計(jì)算的值（續(xù)）43

查詢經(jīng)過計(jì)算的值（續(xù)）[例5]查詢?nèi)w學(xué)生的姓名、出生年份和所有系，要求用小寫字母表示所有系名

SELECTSname，‘YearofBirth:'，2010-Sage，ISLOWER(Sdept)FROMStudent；

輸出結(jié)果：

Sname'YearofBirth:'2010-SageISLOWER(Sdept)-----------------------------------------------------------------------------------

李勇YearofBirth:1990cs

劉晨YearofBirth:1991is

王敏YearofBirth:1992ma

張立YearofBirth:1991is44

查詢經(jīng)過計(jì)算的值（續(xù)）使用列別名改變查詢結(jié)果的列標(biāo)題:

SELECTSname

NAME，'YearofBirth:’BIRTH，

2010-SageBIRTHDAY，LOWER(Sdept)DEPARTMENT FROMStudent；輸出結(jié)果：

NAMEBIRTHBIRTHDAYDEPARTMENT-----------------------------------

李勇YearofBirth:1990cs

劉晨YearofBirth:1991is

王敏YearofBirth:1992ma

張立YearofBirth:1991is45

二、選擇表中的若干元組2.查詢滿足條件的元組1.取消取值重復(fù)的行

46

1.消除取值重復(fù)的行 如果沒有指定DISTINCT關(guān)鍵詞，則缺省為ALL

[例6]查詢選修了課程的學(xué)生學(xué)號。

SELECTSnoFROMSC；

等價(jià)于：

SELECTALLSnoFROMSC；

執(zhí)行上面的SELECT語句后，結(jié)果為：

Sno

-------------------- 200915121 200915121 200915121 200915122 20091512247

消除取值重復(fù)的行（續(xù)）指定DISTINCT關(guān)鍵詞，去掉表中重復(fù)的行

SELECTDISTINCT

Sno

FROMSC；

執(zhí)行結(jié)果：

Sno

------------- 200915121 20091512248

2.查詢滿足條件的元組查詢條件謂詞比較=，>，<，>=，<=，!=，<>，!>，!<；NOT+上述比較運(yùn)算符確定范圍BETWEENAND，NOTBETWEENAND確定集合IN，NOTIN字符匹配LIKE，NOTLIKE空值ISNULL，ISNOTNULL多重條件（邏輯運(yùn)算）AND，OR，NOT常用的查詢條件表49

（1）比較大小[例7]查詢計(jì)算機(jī)科學(xué)系全體學(xué)生的名單。

SELECTSname

FROMStudentWHERESdept=‘CS’；[例8]查詢所有年齡在20歲以下的學(xué)生姓名及其年齡。

SELECTSname，SageFROMStudentWHERESage<20；[例9]查詢考試成績有不及格的學(xué)生的學(xué)號。SELECTDISTINCT

Sno

FROMSCWHEREGrade<60；

50

（2）確定范圍謂詞: BETWEEN…AND… NOTBETWEEN…AND…

[例10]查詢年齡在20~23歲（包括20歲和23歲）之間的學(xué)生的姓名、系別和年齡

SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；[例11]查詢年齡不在20~23歲之間的學(xué)生姓名、系別和年齡

SELECTSname，Sdept，Sage FROMStudent WHERESageNOTBETWEEN20AND23；51

（3）確定集合謂詞：IN<值表>,NOTIN<值表>

[例12]查詢信息系（IS）、數(shù)學(xué)系（MA）和計(jì)算機(jī)科學(xué)系（CS）學(xué)生的姓名和性別。

SELECTSname，Ssex

FROMStudent WHERESdeptIN('IS'，'MA'，'CS');[例13]查詢既不是信息系、數(shù)學(xué)系，也不是計(jì)算機(jī)科學(xué)系的學(xué)生的姓名和性別。

SELECTSname，Ssex

FROMStudent WHERESdeptNOTIN('IS'，'MA'，'CS');52

（4）字符匹配謂詞：

[NOT]LIKE‘<匹配串>’[ESCAPE‘<換碼字符>’]匹配串為固定字符串[例14]查詢學(xué)號為200215121的學(xué)生的詳細(xì)情況。

SELECT*FROMStudentWHERESno

LIKE‘200215121'；等價(jià)于：

SELECT*FROMStudentWHERESno='200215121'；53

字符匹配（續(xù)）

2)匹配串為含通配符的字符串[例15]查詢所有姓劉學(xué)生的姓名、學(xué)號和性別。

SELECTSname，Sno，Ssex

FROMStudentWHERESname

LIKE‘劉%’；

[例16]查詢姓"歐陽"且全名為三個(gè)漢字的學(xué)生的姓名。

SELECTSname

FROMStudentWHERESname

LIKE'歐陽__'；54

字符匹配（續(xù)）[例17]查詢名字中第2個(gè)字為"陽"字的學(xué)生的姓名和學(xué)號。

SELECTSname，Sno

FROMStudentWHERESname

LIKE‘__陽%’；

[例18]查詢所有不姓劉的學(xué)生姓名。

SELECTSname，Sno，Ssex

FROMStudentWHERESname

NOTLIKE'劉%'；

55

字符匹配（續(xù)）3)使用換碼字符將通配符轉(zhuǎn)義為普通字符

[例19]查詢DB_Design課程的課程號和學(xué)分。

SELECTCno，Ccredit

FROMCourseWHERECnameLIKE'DB\_Design'ESCAPE'\‘；

[例20]查詢以"DB_"開頭，且倒數(shù)第3個(gè)字符為i的課程的詳細(xì)情況。

SELECT*FROMCourseWHERECnameLIKE'DB\_%i__'ESCAPE'\‘；

ESCAPE'＼'表示“＼”為換碼字符56

（5）涉及空值的查詢謂詞：ISNULL

或ISNOTNULL

“IS”不能用“=”代替

[例21]某些學(xué)生選修課程后沒有參加考試，所以有選課記錄，但沒有考試成績。查詢?nèi)鄙俪煽兊膶W(xué)生的學(xué)號和相應(yīng)的課程號。

SELECTSno，Cno

FROMSCWHEREGradeISNULL[例22]查所有有成績的學(xué)生學(xué)號和課程號。

SELECTSno，Cno

FROMSCWHEREGradeISNOTNULL；57

（6）多重條件查詢邏輯運(yùn)算符：AND和OR來聯(lián)結(jié)多個(gè)查詢條件

AND的優(yōu)先級高于OR

可以用括號改變優(yōu)先級可用來實(shí)現(xiàn)多種其他謂詞

[NOT]IN[NOT]BETWEEN…AND…58

多重條件查詢（續(xù)）[例23]查詢計(jì)算機(jī)系年齡在20歲以下的學(xué)生姓名。

SELECTSname

FROMStudentWHERESdept='CS'ANDSage<20；59

多重條件查詢（續(xù)）改寫[例12][例12]查詢信息系（IS）、數(shù)學(xué)系（MA）和計(jì)算機(jī)科學(xué)系（CS）學(xué)生的姓名和性別。SELECTSname，Ssex

FROMStudentWHERESdeptIN('IS'，'MA'，'CS')可改寫為：SELECTSname，Ssex

FROMStudentWHERESdept='IS'OR

Sdept='MA'OR

Sdept='CS'；

60

三、ORDERBY子句ORDERBY子句可以按一個(gè)或多個(gè)屬性列排序升序：ASC；降序：DESC；缺省值為升序當(dāng)排序列含空值時(shí)ASC：排序列為空值的元組最后顯示DESC：排序列為空值的元組最先顯示61

ORDERBY子句（續(xù)）[例24]查詢選修了3號課程的學(xué)生的學(xué)號及其成績，查詢結(jié)果按分?jǐn)?shù)降序排列。

SELECTSno，GradeFROMSCWHERECno='3'ORDERBYGradeDESC；[例25]查詢?nèi)w學(xué)生情況，查詢結(jié)果按所在系的系號升序排列，同一系中的學(xué)生按年齡降序排列。

SELECT*FROMStudentORDERBYSdept，SageDESC；62

四、聚集函數(shù)聚集函數(shù)：計(jì)數(shù)COUNT（[DISTINCT|ALL]*）COUNT（[DISTINCT|ALL]<列名>）計(jì)算總和SUM（[DISTINCT|ALL]<列名>） 計(jì)算平均值A(chǔ)VG（[DISTINCT|ALL]<列名>）最大最小值

MAX（[DISTINCT|ALL]<列名>）

MIN（[DISTINCT|ALL]<列名>）63

聚集函數(shù)（續(xù)）

[例26]查詢學(xué)生總?cè)藬?shù)。

SELECTCOUNT(*)FROMStudent；

[例27]查詢選修了課程的學(xué)生人數(shù)。

SELECTCOUNT(DISTINCT

Sno)FROMSC；

[例28]計(jì)算1號課程的學(xué)生平均成績。

SELECTAVG(Grade)FROMSCWHERECno='1'；64

聚集函數(shù)（續(xù)）

[例29]查詢選修1號課程的學(xué)生最高分?jǐn)?shù)。

SELECTMAX(Grade)FROMSCWHERCno='1'；

［例30］查詢學(xué)生200915012選修課程的總學(xué)分?jǐn)?shù)。(本例不是單表) SELECTSUM(Ccredit)FROMSC，CourseWHERSno='200915012'AND

SC.Cno=Course.Cno;65

五、GROUPBY子句GROUPBY子句分組：目的：細(xì)化聚集函數(shù)的作用對象

未對查詢結(jié)果分組，聚集函數(shù)將作用于整個(gè)查詢結(jié)果對查詢結(jié)果分組后，聚集函數(shù)將分別作用于每個(gè)組作用對象是查詢的中間結(jié)果表按指定的一列或多列值分組，值相等的為一組66

GROUPBY子句（續(xù)）[例31]求各個(gè)課程號及相應(yīng)的選課人數(shù)。

SELECTCno，COUNT(Sno)FROMSC

GROUPBYCno；

查詢結(jié)果：

Cno

COUNT(Sno) 122 234 344 433 54867

GROUPBY子句（續(xù)）[例32]查詢選修了3門以上課程的學(xué)生學(xué)號。

SELECTSno

FROMSCGROUPBYSno

HAVINGCOUNT(*)>3；

該例中*是指什么？

68

GROUPBY子句（續(xù)）HAVING短語與WHERE子句的區(qū)別：作用對象不同WHERE子句作用于基表或視圖，從中選擇滿足條件的元組HAVING短語作用于組，從中選擇滿足條件的組。

69

3.4.2連接查詢連接查詢：同時(shí)涉及多個(gè)表的查詢

連接條件或連接謂詞：用來連接兩個(gè)表的條件 一般格式：[<表名1>.]<列名1><比較運(yùn)算符>[<表名2>.]<列名2>[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>

連接字段：連接謂詞中的列名稱連接條件中的各連接字段類型必須是可比的，但名字不必是相同的70

連接操作的執(zhí)行過程嵌套循環(huán)法(NESTED-LOOP)首先在表1中找到第一個(gè)元組，然后從頭開始掃描表2

，逐一查找滿足連接件的元組，找到后就將表1中的第一個(gè)元組與該元組拼接起來，形成結(jié)果表中一個(gè)元組。表2全部查找完后，再從表1中得到第二個(gè)元組，然后再從頭開始掃描表2

，逐一查找滿足連接條件的元組，找到后就將表1中的第二個(gè)元組與該元組拼接起來，形成結(jié)果表中一個(gè)元組。重復(fù)上述操作，直到表1中的全部元組都處理完畢。71

排序合并法(SORT-MERGE)常用于=連接首先按連接屬性對表1和表2排序?qū)Ρ?的第一個(gè)元組，從頭開始掃描表2，順序查找滿足連接條件的元組，找到后就將表1中的第一個(gè)元組與該元組拼接起來，形成結(jié)果表中一個(gè)元組。當(dāng)遇到表2中第一條大于表1連接字段值的元組時(shí)，對表2的查詢不再繼續(xù)72

排序合并法(續(xù))找到表1的第二條元組，然后從剛才的中斷點(diǎn)處繼續(xù)順序掃描表2，查找滿足連接條件的元組，找到后就將表1中的第一個(gè)元組與該元組拼接起來，形成結(jié)果表中一個(gè)元組。直接遇到表2中大于表1連接字段值的元組時(shí)，對表2的查詢不再繼續(xù)重復(fù)上述操作，直到表1或表2中的全部元組都處理完畢為止。73

索引連接(INDEX-JOIN)對表2按連接字段建立索引對表1中的每個(gè)元組，依次根據(jù)其連接字段值查詢表2的索引，從中找到滿足條件的元組，找到后就將表1中的第一個(gè)元組與該元組拼接起來，形成結(jié)果表中一個(gè)元組

74

連接查詢（續(xù)）二、自身連接三、外連接

一、等值與非等值連接查詢

四、復(fù)合條件連接

75

一、等值與非等值連接查詢等值連接：連接運(yùn)算符為=[例33]查詢每個(gè)學(xué)生及其選修課程的情況

SELECTStudent.*，SC.* FROMStudent，SC WHEREStudent.Sno=SC.Sno；76

等值與非等值連接查詢（續(xù)）Student.Sno

Sname

Ssex

Sage

Sdept

SC.Sno

Cno

Grade

200915121

李勇男20

CS

200915121

1

92

200915121

李勇男20

CS

200915121

2

85

200915121

李勇男20

CS

200915121

3

88

200915122

劉晨女19

CS

200915122

2

90

200915122

劉晨女19

CS

200915122

3

80

查詢結(jié)果：77

等值與非等值連接查詢（續(xù)）自然連接：

[例34]對[例33]用自然連接完成。

SELECTStudent.Sno，Sname，Ssex，Sage，Sdept，Cno，GradeFROMStudent，SCWHEREStudent.Sno=SC.Sno；78

二、自身連接自身連接：一個(gè)表與其自己進(jìn)行連接需要給表起別名以示區(qū)別由于所有屬性名都是同名屬性，因此必須使用別名前綴[例35]查詢每一門課的間接先修課（即先修課的先修課）

SELECTFIRST.Cno，SECOND.Cpno

FROMCourseFIRST，CourseSECOND

WHEREFIRST.Cpno=SECOND.Cno；79

自身連接（續(xù)）Cno

Cname

Cpno

Ccredit

1

數(shù)據(jù)庫

5

4

2

數(shù)學(xué)

2

3

信息系統(tǒng)

1

4

4

操作系統(tǒng)

6

3

5

數(shù)據(jù)結(jié)構(gòu)

7

4

6

數(shù)據(jù)處理

2

7

PASCAL語言

6

4

SECOND表（Course表）80

自身連接（續(xù)）查詢結(jié)果：Cno

Pcno

1

7

3

5

5

6

81

三、外連接外連接與普通連接的區(qū)別普通連接操作只輸出滿足連接條件的元組外連接操作以指定表為連接主體，將主體表中不滿足連接條件的元組一并輸出[例36]改寫[例33]SELECTStudent.Sno，Sname，Ssex，Sage，Sdept，Cno，GradeFROMStudentLEFTOUTJOINSCON(Student.Sno=SC.Sno)；

82

外連接（續(xù)）執(zhí)行結(jié)果：Student.Sno

Sname

Ssex

Sage

Sdept

Cno

Grade

200915121

李勇男20

CS

1

92

200915121

李勇男20

CS

2

85

200915121

李勇男20

CS

3

88

200915122

劉晨女19

CS

2

90

200915122

劉晨女19

CS

3

80

200915123

王敏女18

MA

NULL

NULL

200915125

張立男19

IS

NULL

NULL

83

外連接（續(xù)）左外連接列出左邊關(guān)系（如本例Student）中所有的元組右外連接列出右邊關(guān)系中所有的元組84

四、復(fù)合條件連接復(fù)合條件連接：WHERE子句中含多個(gè)連接條件

[例37]查詢選修2號課程且成績在90分以上的所有學(xué)生

SELECTStudent.Sno,Sname

FROMStudent,SC WHEREStudent.Sno=SC.Sno

AND

/*連接謂詞*/

SC.Cno=‘2’AND

SC.Grade>90；

/*其他限定條件*/85

復(fù)合條件連接（續(xù)）[例38]查詢每個(gè)學(xué)生的學(xué)號、姓名、選修的課程名及成績

SELECTStudent.Sno，Sname，Cname，GradeFROMStudent，SC，Course/*多表連接*/WHEREStudent.Sno=SC.Sno

andSC.Cno=Course.Cno；

86

3.4.3嵌套查詢嵌套查詢概述一個(gè)SELECT-FROM-WHERE語句稱為一個(gè)查詢塊將一個(gè)查詢塊嵌套在另一個(gè)查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢

87

嵌套查詢(續(xù))

SELECTSname/*外層查詢/父查詢*/FROMStudentWHERESnoIN

（SELECTSno/*內(nèi)層查詢/子查詢*/FROMSCWHERECno='2'）；

88

嵌套查詢(續(xù))子查詢的限制不能使用ORDERBY子句層層嵌套方式反映了SQL語言的結(jié)構(gòu)化有些嵌套查詢可以用連接運(yùn)算替代89

嵌套查詢求解方法不相關(guān)子查詢：子查詢的查詢條件不依賴于父查詢由里向外逐層處理。即每個(gè)子查詢在上一級查詢處理之前求解，子查詢的結(jié)果用于建立其父查詢的查找條件。90

嵌套查詢求解方法（續(xù)）相關(guān)子查詢：子查詢的查詢條件依賴于父查詢首先取外層查詢中表的第一個(gè)元組，根據(jù)它與內(nèi)層查詢相關(guān)的屬性值處理內(nèi)層查詢，若WHERE子句返回值為真，則取此元組放入結(jié)果表然后再取外層表的下一個(gè)元組重復(fù)這一過程，直至外層表全部檢查完為止91

嵌套查詢（續(xù)）二、帶有比較運(yùn)算符的子查詢?nèi)?、帶有ANY(SOME)或ALL謂詞的子查詢

一、帶有IN謂詞的子查詢四、帶有EXISTS謂詞的子查詢

92

一、帶有IN謂詞的子查詢[例39]查詢與“劉晨”在同一個(gè)系學(xué)習(xí)的學(xué)生。此查詢要求可以分步來完成：①確定“劉晨”所在系名

SELECTSdept

FROMStudentWHERESname='劉晨'； 結(jié)果為：CS93

帶有IN謂詞的子查詢（續(xù)）②查找所有在IS系學(xué)習(xí)的學(xué)生。

SELECTSno，Sname，Sdept

FROMStudentWHERESdept='CS'；結(jié)果為：Sno

Sname

Sdept

200915121

李勇CS

200915122劉晨CS94

帶有IN謂詞的子查詢（續(xù)）將第一步查詢嵌入到第二步查詢的條件中

SELECTSno，Sname，Sdept

FROMStudent WHERESdept

IN(SELECTSdept

FROMStudentWHERESname=‘劉晨’)；此查詢?yōu)椴幌嚓P(guān)子查詢。95

帶有IN謂詞的子查詢（續(xù)）

用自身連接完成[例39]查詢要求

SELECTS1.Sno，S1.Sname，S1.SdeptFROMStudentS1，StudentS2

WHERES1.Sdept=S2.SdeptAND

S2.Sname='劉晨'；

96

帶有IN謂詞的子查詢（續(xù)）[例40]查詢選修了課程名為“信息系統(tǒng)”的學(xué)生學(xué)號和姓名

SELECTSno，Sname

③最后在Student關(guān)系中

FROMStudent取出Sno和Sname

WHERESnoIN(SELECTSno

②然后在SC關(guān)系中找出選

FROMSC修了3號課程的學(xué)生學(xué)號

WHERECnoIN(SELECTCno

①首先在Course關(guān)系中找出

FROMCourse“信息系統(tǒng)”的課程號，為3號

WHERECname=‘信息系統(tǒng)’));97

帶有IN謂詞的子查詢（續(xù)）用連接查詢實(shí)現(xiàn)[例40]SELECTSno，Sname

FROMStudent，SC，CourseWHEREStudent.Sno=SC.SnoAND

SC.Cno=Course.CnoAND

Course.Cname=‘信息系統(tǒng)’；98

二、帶有比較運(yùn)算符的子查詢當(dāng)能確切知道內(nèi)層查詢返回單值時(shí)，可用比較運(yùn)算符（>，<，=，>=，<=，!=或<>）。與ANY或ALL謂詞配合使用

99

帶有比較運(yùn)算符的子查詢（續(xù)）例：假設(shè)一個(gè)學(xué)生只可能在一個(gè)系學(xué)習(xí)，并且必須屬于一個(gè)系，則在[例39]可以用=代替IN

：

SELECTSno，Sname，Sdept

FROMStudentWHERESdept

=

(SELECTSdept

FROMStudentWHERESname=‘劉晨’)；100

帶有比較運(yùn)算符的子查詢（續(xù)）子查詢一定要跟在比較符之后

錯(cuò)誤的例子：

SELECTSno，Sname，Sdept

FROMStudentWHERE(SELECTSdept

FROMStudentWHERESname=‘劉晨’)=Sdept；101

帶有比較運(yùn)算符的子查詢（續(xù)）［例41］找出每個(gè)學(xué)生超過他選修課程平均成績的課程號。

SELECTSno，Cno

FROMSCxWHEREGrade>=(SELECTAVG(Grade) FROMSCyWHEREy.Sno=x.Sno);相關(guān)子查詢102

帶有比較運(yùn)算符的子查詢（續(xù)）可能的執(zhí)行過程：1.從外層查詢中取出SC的一個(gè)元組x，將元組x的Sno值（200915121）傳送給內(nèi)層查詢。

SELECTAVG(Grade)FROMSCyWHEREy.Sno='200915121';

2.執(zhí)行內(nèi)層查詢，得到值88（近似值），用該值代替內(nèi)層查詢，得到外層查詢：

SELECTSno，Cno

FROMSCxWHEREGrade>=88；103

帶有比較運(yùn)算符的子查詢（續(xù)）3.執(zhí)行這個(gè)查詢，得到（200215121，1）（200215121，3）4.外層查詢?nèi)〕鱿乱粋€(gè)元組重復(fù)做上述1至3步驟，直到外層的SC元組全部處理完畢。結(jié)果為:

（200215121，1）（200215121，3）（200215122，2）104

三、帶有ANY或ALL謂詞的子查詢謂詞語義ANY：任意一個(gè)值A(chǔ)LL：所有值

105

帶有ANY或ALL謂詞的子查詢（續(xù)）需要配合使用比較運(yùn)算符：>ANY 大于子查詢結(jié)果中的某個(gè)值>ALL 大于子查詢結(jié)果中的所有值<ANY 小于子查詢結(jié)果中的某個(gè)值<ALL 小于子查詢結(jié)果中的所有值>=ANY 大于等于子查詢結(jié)果中的某個(gè)值>=ALL 大于等于子查詢結(jié)果中的所有值<=ANY 小于等于子查詢結(jié)果中的某個(gè)值<=ALL 小于等于子查詢結(jié)果中的所有值=ANY 等于子查詢結(jié)果中的某個(gè)值=ALL 等于子查詢結(jié)果中的所有值（通常沒有實(shí)際意義）!=（或<>）ANY 不等于子查詢結(jié)果中的某個(gè)值!=（或<>）ALL 不等于子查詢結(jié)果中的任何一個(gè)值106

[例42]查詢其他系中比計(jì)算機(jī)科學(xué)某一學(xué)生年齡小的學(xué)生姓名和年齡

SELECTSname，SageFROMStudentWHERESage<ANY(SELECTSageFROMStudentWHERESdept='CS')

ANDSdept<>‘CS';/*父查詢塊中的條件*/帶有ANY或ALL謂詞的子查詢（續(xù)）107

執(zhí)行過程：

1.RDBMS執(zhí)行此查詢時(shí)，首先處理子查詢，找出CS系中所有學(xué)生的年齡，構(gòu)成一個(gè)集合(20，19)2.處理父查詢，找所有不是CS系且年齡小于20或19的學(xué)生Sname

Sage

王敏18

張立19帶有ANY或ALL謂詞的子查詢（續(xù)）結(jié)果：108

用聚集函數(shù)實(shí)現(xiàn)[例42]

SELECTSname，SageFROMStudentWHERESage<(SELECTMAX(Sage)

FROMStudentWHERESdept=‘CS')ANDSdept<>'CS’;帶有ANY或ALL謂詞的子查詢（續(xù)）109

[例43]查詢其他系中比計(jì)算機(jī)科學(xué)系所有學(xué)生年齡都小的學(xué)生姓名及年齡。方法一：用ALL謂詞

SELECTSname，SageFROMStudentWHERESage<ALL(SELECTSageFROMStudentWHERESdept='CS'ANDSdept<>‘CS’;）帶有ANY或ALL謂詞的子查詢（續(xù)）110

方法二：用聚集函數(shù)

SELECTSname，SageFROMStudentWHERESage<(SELECTMIN(Sage)

FROMStudentWHERESdept='CS')ANDSdept<>'CS’;帶有ANY或ALL謂詞的子查詢（續(xù)）111

ANY（或SOME），ALL謂詞與聚集函數(shù)、IN謂詞的等價(jià)轉(zhuǎn)換關(guān)系表

=

<>或!=

<

<=

>

>=

ANY

IN

--

<MAX

<=MAX

>MIN

>=MIN

ALL

--

NOTIN

<MIN

<=MIN

>MAX

>=MAX

帶有ANY或ALL謂詞的子查詢（續(xù)）112

四、帶有EXISTS謂詞的子查詢1.EXISTS謂詞存在量詞

帶有EXISTS謂詞的子查詢不返回任何數(shù)據(jù)，只產(chǎn)生邏輯真值“true”或邏輯假值“false”。若內(nèi)層查詢結(jié)果非空，則外層的WHERE子句返回真值若內(nèi)層查詢結(jié)果為空，則外層的WHERE子句返回假值由EXISTS引出的子查詢，其目標(biāo)列表達(dá)式通常都用*，因?yàn)閹XISTS的子查詢只返回真值或假值，給出列名無實(shí)際意義2.NOTEXISTS謂詞若內(nèi)層查詢結(jié)果非空，則外層的WHERE子句返回假值若內(nèi)層查詢結(jié)果為空，則外層的WHERE子句返回真值113

帶有EXISTS謂詞的子查詢(續(xù))[例44]查詢所有選修了1號課程的學(xué)生姓名。

思路分析：本查詢涉及Student和SC關(guān)系在Student中依次取每個(gè)元組的Sno值，用此值去檢查SC關(guān)系若SC中存在這樣的元組，其Sno值等于此Student.Sno值，并且其Cno='1'，則取此Student.Sname送入結(jié)果關(guān)系114

帶有EXISTS謂詞的子查詢(續(xù))用嵌套查詢

SELECTSname

FROMStudent

WHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno='1')；

115

帶有EXISTS謂詞的子查詢(續(xù))用連接運(yùn)算

SELECTSname

FROMStudent,SC WHEREStudent.Sno=SC.SnoANDSC.Cno='1';

116

帶有EXISTS謂詞的子查詢(續(xù))[例45]查詢沒有選修1號課程的學(xué)生姓名。

SELECTSname

FROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno='1')；117

帶有EXISTS謂詞的子查詢(續(xù))不同形式的查詢間的替換一些帶EXISTS或NOTEXISTS謂詞的子查詢不能被其他形式的子查詢等價(jià)替換所有帶IN謂詞、比較運(yùn)算符、ANY和ALL謂詞的子查詢都能用帶EXISTS謂詞的子查詢等價(jià)替換用EXISTS/NOTEXISTS實(shí)現(xiàn)全稱量詞(難點(diǎn))SQL語言中沒有全稱量詞（Forall）可以把帶有全稱量詞的謂詞轉(zhuǎn)換為等價(jià)的帶有存在量詞的謂詞：

(x)P≡(x(P))

左表示：全部的x都滿足條件P；右表示：不存在這樣x，它不滿足條件P

118

帶有EXISTS謂詞的子查詢(續(xù))[例46]查詢選修了全部課程的學(xué)生姓名。

SELECTSname

FROMStudentWHERENOTEXISTS

（SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.Sno

ANDCno=Course.Cno））119

帶有EXISTS謂詞的子查詢(續(xù))用EXISTS/NOTEXISTS實(shí)現(xiàn)邏輯蘊(yùn)函(難點(diǎn))SQL語言中沒有蘊(yùn)函(Implication)邏輯運(yùn)算可以利用謂詞演算將邏輯蘊(yùn)函謂詞等價(jià)轉(zhuǎn)換為：

pq≡

p∨q

120

帶有EXISTS謂詞的子查詢(續(xù))

[例47]查詢至少選修了學(xué)生200215122選修的全部課程的學(xué)生號碼。解題思路：用邏輯蘊(yùn)函表達(dá)：查詢學(xué)號為x的學(xué)生，對所有的課程y，只要200215122學(xué)生選修了課程y，則x也選修了y。形式化表示： 用P表示謂詞“學(xué)生200215122選修了課程y”

用q表示謂詞“學(xué)生x選修了課程y”

則上述查詢?yōu)?(y)pq121

帶有EXISTS謂詞的子查詢(續(xù))等價(jià)變換：

(y)p

q≡(y((pq))≡(y((p∨q)))≡

y(p∧q)

變換后語義：不存在這樣的課程y，學(xué)生200215122選修了y，而學(xué)生x沒有選。122

帶有EXISTS謂詞的子查詢(續(xù))

用NOTEXISTS謂詞表示：

SELECTDISTINCTSno

FROMSCSCX

WHERENOTEXISTS(SELECT*FROMSCSCY

WHERESCY.Sno=‘200215122’ANDNOTEXISTS(SELECT*FROMSCSCZWHERESCZ.Sno=SCX.SnoANDSCZ.Cno=SCY.Cno))；123

3.4.4集合查詢集合操作的種類并操作UNION交操作INTERSECT(T-SQL不支持)

差操作EXCEPT(T-SQL不支持)參加集合操作的各查詢結(jié)果的列數(shù)必須相同；對應(yīng)項(xiàng)的數(shù)據(jù)類型也必須相同124

集合查詢（續(xù)）[例48]查詢計(jì)算機(jī)科學(xué)系的學(xué)生及所有系中年齡不大于19歲的學(xué)生。方法一：

SELECT*FROMStudentWHERESdept='CS'UNIONSELECT*FROMStudentWHERESage<=19；UNION：將多個(gè)查詢結(jié)果合并起來時(shí)，系統(tǒng)自動(dòng)去掉重復(fù)元組。UNIONALL：將多個(gè)查詢結(jié)果合并起來時(shí)，保留重復(fù)元組125

集合查詢（續(xù)）方法二：

SELECTDISTIN