學(xué)習(xí)數(shù)據(jù)庫(kù)技術(shù)全第3章語(yǔ)言

華東理工大學(xué)信息學(xué)院

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

掌握了解SQL語(yǔ)言的概念和定義SQL語(yǔ)言發(fā)展的過(guò)程SQL語(yǔ)言的基本概念SQL語(yǔ)言的特點(diǎn)體會(huì)面向過(guò)程的語(yǔ)言和SQL語(yǔ)言的區(qū)別和優(yōu)點(diǎn)體會(huì)關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)為數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)的開(kāi)發(fā)提供良好的環(huán)境，減輕用戶負(fù)擔(dān)，提高用戶生產(chǎn)率的原因需要舉一反三的：1熟練而正確地使用SQL語(yǔ)言完成對(duì)數(shù)據(jù)庫(kù)的各種各樣的查詢。掌握插入操作。掌握刪除操作。掌握更新操作。掌握創(chuàng)建視圖的方法難點(diǎn):用SQL語(yǔ)言正確完成對(duì)數(shù)據(jù)庫(kù)復(fù)雜查詢。在學(xué)習(xí)的過(guò)程中一定要多練習(xí)，并在SQLServer上進(jìn)行實(shí)際運(yùn)行，檢查查詢的結(jié)果是否正確。只有通過(guò)大量練習(xí)，才能真正達(dá)到舉一反三的熟練程度。第3章關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言--SQL3.1SQL概述3.2學(xué)生-課程數(shù)據(jù)庫(kù)3.3數(shù)據(jù)定義3.4數(shù)據(jù)查詢3.5數(shù)據(jù)更新3.6視圖3.1SQL概述SQL的特點(diǎn)：1綜合統(tǒng)一2高度非過(guò)程化3面向集合的操作方式4以同一種語(yǔ)法結(jié)構(gòu)提供兩種使用方式5語(yǔ)言簡(jiǎn)潔，易學(xué)易用SQL對(duì)關(guān)系數(shù)據(jù)庫(kù)模式的支持一個(gè)關(guān)系就對(duì)應(yīng)于一個(gè)基本表數(shù)據(jù)庫(kù)的內(nèi)模式只存放視圖的定義而不存放視圖對(duì)應(yīng)的數(shù)據(jù)從一個(gè)或幾個(gè)基本表導(dǎo)出的表。它本身不獨(dú)立存儲(chǔ)在數(shù)據(jù)庫(kù)中

學(xué)生-課程數(shù)據(jù)庫(kù)本章用學(xué)生-課程數(shù)據(jù)庫(kù)作為一個(gè)例子來(lái)講解SQL的數(shù)據(jù)定義、數(shù)據(jù)操縱、數(shù)據(jù)查詢和數(shù)據(jù)控制語(yǔ)句的具體應(yīng)用。該數(shù)據(jù)庫(kù)有三個(gè)表：學(xué)生表：Student(Sno,Sname,Ssex,Sage,Sdept)課程表：Course(Cno,Cname,Cpno,Ccredit)學(xué)生選課表：SC(Sno,Cno,Grade)注：加下劃線的為該關(guān)系的主碼。3.2數(shù)據(jù)定義3.2.1基本表的定義、刪除與修改3.2.2索引的建立與刪除3.2.1基本表定義、刪除與修改1、定義基本表2、修改基本表3、刪除基本表

[例1]建立一個(gè)“學(xué)生”表Student，它由學(xué)號(hào)Sno、姓名Sname、性別Ssex、年齡Sage、所在系Sdept五個(gè)屬性組成。其中學(xué)號(hào)不能為空，值是唯一的，并且姓名取值也唯一。

CREATETABLEStudent(SnoCHAR(7)NOTNULLUNIQUE，

SnameCHAR(20)UNIQUE，

SsexCHAR(1)，

SageSMALLINT，

SdeptCHAR(15))；1、定義基本表命令表名列名類型名例題1、定義基本表常用完整性約束主碼約束：PRIMARYKEY唯一性約束：UNIQUE非空值約束：NOTNULL參照完整性約束FOREIGNKEY…REFERENCESPRIMARYKEY與

UNIQUE的區(qū)別？[例2]建立一個(gè)“學(xué)生選課”表SC，它由學(xué)號(hào)Sno、課程號(hào)Cno，修課成績(jī)Grade組成，其中(Sno,Cno)為主碼。CREATETABLESC(SnoCHAR(7),CnoCHAR(4),Gradeint,PRIMARYKEY(Sno,Cno),

FOREIGNKEY(Sno)REFERENCESStudent(Sno),

FOREIGNKEY(Cno)REFENERCESCourse(Cno));2、修改基本表ALTERTABLE<表名>[ADD<新列名><數(shù)據(jù)類型>[完整性約束]][DROP<完整性約束名>][ALTERCOLUMN<列名><數(shù)據(jù)類型>]；<表名>：要修改的基本表ADD子句：增加新列和新的完整性約束條件DROP子句：刪除指定的完整性約束條件ALTERCOLUMN：修改原有的列定義[例3]向Student表增加“入學(xué)時(shí)間”列，其數(shù)據(jù)類型為日期型。ALTERTABLEStudentADDS_entranceDATE；不論基本表中原來(lái)是否已有數(shù)據(jù)，新增加的列一律為空值。[例4]將年齡的數(shù)據(jù)類型改為半字長(zhǎng)整數(shù)。ALTERTABLEStudentALTERCOLUMNSageSMALLINT；注：修改原有的列定義有可能會(huì)破壞已有數(shù)據(jù)[例5]刪除學(xué)生姓名必須取唯一值的約束。ALTERTABLECourseADDUNIQUE(Cname)；ALTERTABLEStudentDROPUNIQUE(Sname)；

[例6]增加課程名稱必須取唯一值的約束。

間接刪除把表中要保留的列及其內(nèi)容復(fù)制到一個(gè)新表中刪除原表再將新表重命名為原表名刪除屬性列。3、刪除基本表DROPTABLE<表名>[RESTRICT|CASCADE];若選擇RESTRICT：則該表的刪除是有限制條件的。欲刪除的基本表不能被其他表的約束所引用（如CHECK，F(xiàn)OREIGNKEY等約束），不能有視圖，不能有觸發(fā)器，不能有存儲(chǔ)過(guò)程或函數(shù)等。如果存在這些依賴該表的對(duì)象，則此表不能被刪除。3、刪除基本表若選擇CASCADE：則該表的刪除是沒(méi)有限制條件的。在刪除基本表的同時(shí)，相關(guān)的依賴對(duì)象，如約束（如CHECK，F(xiàn)OREIGNKEY等約束）、視圖、觸發(fā)器、存儲(chǔ)過(guò)程或函數(shù)等。都將一起被刪除。缺省情況：RESTRICT例題[例7]刪除Student表

DROPTABLE

StudentCASCADE;注：基本表的定義一旦被刪除，不僅表中的數(shù)據(jù)和此表的定義將被刪除，而且此表上建立的索引、視圖、觸發(fā)器等有關(guān)對(duì)象一般也都將被一起刪除。注：不同的DBMS的實(shí)現(xiàn)細(xì)節(jié)和處理策略會(huì)有差別。3.2.2索引的建立與刪除建立索引是加快查詢速度的有效手段建立索引DBA或表的屬主（即建立表的人）根據(jù)需要建立有些DBMS自動(dòng)建立以下列上的索引

PRIMARYKEYUNIQUE維護(hù)索引

DBMS自動(dòng)完成

使用索引

DBMS自動(dòng)選擇是否使用索引以及使用哪些索引1、建立索引語(yǔ)句格式CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…)； 用<表名>指定要建索引的基本表名字索引可以建立在該表的一列或多列上，各列名之間用逗號(hào)分隔用<次序>指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCUNIQUE表明此索引的每一個(gè)索引值只對(duì)應(yīng)唯一的數(shù)據(jù)記錄CLUSTER表示要建立的索引是聚簇索引1、建立索引

[例8]為學(xué)生-課程數(shù)據(jù)庫(kù)中的Student，Course，SC三個(gè)表建立索引。其中Student表按學(xué)號(hào)升序建唯一索引，Course表按課程號(hào)升序建唯一索引，SC表按學(xué)號(hào)升序和課程號(hào)降序建唯一索引。CREATEUNIQUEINDEXStusnoONStudent(Sno)；CREATEUNIQUEINDEXCoucnoONCourse(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；命令名索引名索引1、建立索引唯一值索引對(duì)于已含重復(fù)值的屬性列不能建UNIQUE索引對(duì)某個(gè)列建立UNIQUE索引后，插入新記錄時(shí)DBMS會(huì)自動(dòng)檢查新記錄在該列上是否取了重復(fù)值。這相當(dāng)于增加了一個(gè)UNIQUE約束1、建立索引聚簇索引建立聚簇索引后，基表中數(shù)據(jù)也需要按指定的聚簇屬性值的升序或降序存放。也即聚簇索引的索引項(xiàng)順序與表中記錄的物理順序一致例：CREATECLUSTERINDEXStusnameONStudent(Sname)；在Student表的Sname（姓名）列上建立一個(gè)聚簇索引，那么Student表中的記錄將按照Sname值的升序存放

1、建立索引在一個(gè)基本表上最多只能建立一個(gè)聚簇索引聚簇索引的用途：對(duì)于某些類型的查詢，可以提高查詢效率。聚簇索引的適用范圍很少對(duì)基表進(jìn)行增刪操作進(jìn)行聚簇索引的很少進(jìn)行更新操作2、刪除索引DROPINDEX<索引名>；刪除索引時(shí)，系統(tǒng)會(huì)從數(shù)據(jù)字典中刪去有關(guān)該索引的描述。[例7]刪除Student表的Stusname索引。

DROPINDEXStusname；SQL的數(shù)據(jù)定義語(yǔ)句總結(jié)3.3數(shù)據(jù)查詢3.3.1單表查詢3.3.2連接查詢3.3.3嵌套查詢3.3.4集合查詢SELECT[ALL|DISTINCT]<目標(biāo)列表達(dá)式>[，<目標(biāo)列表達(dá)式>]…FROM<表名或視圖名>[，<表名或視圖名>]…[WHERE<條件表達(dá)式>][GROUPBY<列名1>[HAVING<條件表達(dá)式>]][ORDERBY<列名2>[ASC|DESC]]；

SELECT子句：指定要顯示的屬性列FROM子句：指定查詢對(duì)象(基本表或視圖)WHERE子句：指定查詢條件GROUPBY子句：對(duì)查詢結(jié)果按指定列的值分組，該屬性列值相等的元組為一個(gè)組。通常會(huì)在每組中作用集函數(shù)。HAVING短語(yǔ)：篩選出只有滿足指定條件的組ORDERBY子句：對(duì)查詢結(jié)果表按指定列值的升序或降序排序3.3.1

單表查詢

查詢僅涉及一個(gè)表，是一種最簡(jiǎn)單的查詢操作1、選擇表中的若干列2、選擇表中的若干元組3、對(duì)查詢結(jié)果排序4、使用集函數(shù)5、對(duì)查詢結(jié)果分組1、選擇表中若干列(1)查詢指定列(2)查詢?nèi)苛?3)查詢經(jīng)過(guò)計(jì)算的值1.查詢指定列[例1]查詢?nèi)w學(xué)生的學(xué)號(hào)與姓名。SELECTSno，SnameFROMStudent；

[例2]查詢?nèi)w學(xué)生的姓名、學(xué)號(hào)、所在系。SELECTSname，Sno，SdeptFROMStudent；2.查詢?nèi)苛衃例3]查詢?nèi)w學(xué)生的詳細(xì)記錄。SELECTSno，Sname，Ssex，Sage，SdeptFROMStudent；或SELECT*FROMStudent；3.查詢經(jīng)過(guò)計(jì)算的值SELECT子句的<目標(biāo)列表達(dá)式>為表達(dá)式算術(shù)表達(dá)式字符串常量函數(shù)列別名其它3.查詢經(jīng)過(guò)計(jì)算的值[例4]查全體學(xué)生的姓名及其出生年份。SELECTSname，2013-SageFROMStudent；

輸出結(jié)果：

Sname2013-Sage----------------------

李勇1993

劉晨1994

王名1995

張立19943.查詢經(jīng)過(guò)計(jì)算的值[例5]查詢?nèi)w學(xué)生的姓名、出生年份和所有系，要求用小寫(xiě)字母表示所有系名。SELECTSname，‘YearofBirth:’，2013-Sage，ISLOWER(Sdept)FROMStudent；輸出結(jié)果：

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

李勇YearofBirth:1988cs

劉晨YearofBirth:1989cs

王名YearofBirth:1990ma

張立YearofBirth:1989is[例5.1]使用列別名改變查詢結(jié)果的列標(biāo)題SELECTSnameNAME，'YearofBirth:’

BIRTH，

2013-SageBIRTHDAY，ISLOWER(Sdept)DEPARTMENTFROM

Student；輸出結(jié)果：

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

李勇YearofBirth:1993cs

劉晨YearofBirth:1994cs

王名YearofBirth:1995ma

張立YearofBirth:1994is2、選擇表中的若干元組(1)消除取值重復(fù)的行(2)查詢滿足條件的元組(1)消除取值重復(fù)的行在SELECT子句中使用DISTINCT短語(yǔ)假設(shè)SC表中有下列數(shù)據(jù)

SnoCnoGrade---------------------------20111211922011121285201112238820111222902011122380ALL與DISTINCT[例6]查詢選修了課程的學(xué)生學(xué)號(hào)。(1)SELECTSnoFROMSC;

或(默認(rèn)ALL)SELECTALLSnoFROMSC;

結(jié)果：Sno------------20111212011121201112120111222011122

(2)SELECTDISTINCTSnoFROMSC;

結(jié)果：

Sno---------------20111212011122例題注意DISTINCT短語(yǔ)的作用范圍是所有目標(biāo)列例：查詢選修課程的各種成績(jī)錯(cuò)誤的寫(xiě)法SELECTDISTINCTCno，DISTINCTGradeFROMSC;正確的寫(xiě)法

SELECTDISTINCTCno，GradeFROMSC;

2.查詢滿足條件的元組WHERE子句常用的查詢條件(1)比較大小在WHERE子句的<比較條件>中使用比較運(yùn)算符=，>，<，>=，<=，!=或<>，!>，!<，邏輯運(yùn)算符NOT+比較運(yùn)算符[例6]查詢所有年齡在20歲以下的學(xué)生姓名及其年齡。

SELECTSname，Sage

FROMStudentWHERESage<20；或SELECTSname，SageFROMStudentWHERENOTSage>=20；

(2)確定范圍使用謂詞BETWEEN…AND…NOTBETWEEN…AND…[例7]查詢年齡在20~23歲（包括20歲和23歲）之間的學(xué)生的姓名、系別和年齡。

SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；[例8]查詢年齡不在20~23歲之間的學(xué)生姓名、系別和年齡。SELECTSname，Sdept，SageFROMStudentWHERESageNOTBETWEEN20AND23；(3)確定集合使用謂詞IN<值表>,NOTIN<值表><值表>：用逗號(hào)分隔的一組取值[例9]查詢信息系（IS）、數(shù)學(xué)系（MA）和計(jì)算機(jī)科學(xué)系（CS）學(xué)生的姓名和性別。SELECTSname，SsexFROMStudentWHERESdeptIN('IS'，'MA'，'CS');[例10]查詢既不是信息系、數(shù)學(xué)系，也不是計(jì)算機(jī)科學(xué)系的學(xué)生的姓名和性別。SELECTSname，SsexFROMStudent WHERESdeptNOTIN('IS'，'MA'，'CS');(4)字符串匹配

[NOT]LIKE‘<匹配串>’[ESCAPE‘<換碼字符>’]<匹配串>：指定匹配模板匹配模板：固定字符串或含通配符的字符串當(dāng)匹配模板為固定字符串時(shí)，可以用=運(yùn)算符取代LIKE謂詞用!=或<>運(yùn)算符取代NOTLIKE謂詞通配符%(百分號(hào))代表任意長(zhǎng)度（長(zhǎng)度可以為0）的字符串例：a%b表示以a開(kāi)頭，以b結(jié)尾的任意長(zhǎng)度的字符串。如acb，addgb，ab等都滿足該匹配串_(下橫線)代表任意單個(gè)字符例：a_b表示以a開(kāi)頭，以b結(jié)尾的長(zhǎng)度為3的任意字符串。如acb，afb等都滿足該匹配串ESCAPE短語(yǔ)：當(dāng)用戶要查詢的字符串本身就含有%或_時(shí)，要使用ESCAPE'<換碼字符>'短語(yǔ)對(duì)通配符進(jìn)行轉(zhuǎn)義。例題1)匹配模板為固定字符串

[例11]查詢學(xué)號(hào)為2011121的學(xué)生的詳細(xì)情況。

SELECT*FROMStudentWHERESno

LIKE'2011121'；等價(jià)于：

SELECT*FROMStudentWHERESno='2011121'；等價(jià)2)匹配模板為含通配符的字符串[例12]查詢所有姓劉學(xué)生的姓名、學(xué)號(hào)和性別。

SELECTSname，Sno，SsexFROMStudentWHERESnameLIKE‘劉%’；[例13]查詢姓"歐陽(yáng)"且全名為三個(gè)漢字的學(xué)生的姓名。

SELECTSnameFROMStudentWHERESnameLIKE'歐陽(yáng)__'；2)匹配模板為含通配符的字符串[例14]查詢名字中第2個(gè)字為"陽(yáng)"字的學(xué)生的姓名和學(xué)號(hào)。

SELECTSname，SnoFROMStudentWHERESnameLIKE'__陽(yáng)%'；[例15]查詢所有不姓劉的學(xué)生姓名。

SELECTSname，Sno，SsexFROMStudentWHERESnameNOTLIKE'劉%'；3)使用換碼字符將通配符轉(zhuǎn)義為普通字符[例16]查詢DB_Design課程的課程號(hào)和學(xué)分。

SELECTCno，CcreditFROMCourseWHERECnameLIKE'DB\_Design'ESCAPE'\'[例17]查詢以"DB_"開(kāi)頭，且倒數(shù)第3個(gè)字符為i的課程的詳細(xì)情況。

SELECT*FROMCourseWHERECnameLIKE'DB\_%i__'ESCAPE'\'；(5)涉及空值的查詢

使用謂詞ISNULL或ISNOTNULL

“ISNULL”

不能用“=NULL”

代替[例19]查所有有成績(jī)的學(xué)生學(xué)號(hào)和課程號(hào)。

SELECTSno，CnoFROMSCWHEREGradeISNOTNULL；[例18]某些學(xué)生選修課程后沒(méi)有參加考試，所以有選課記錄，但沒(méi)有考試成績(jī)。查詢?nèi)鄙俪煽?jī)的學(xué)生的學(xué)號(hào)和相應(yīng)的課程號(hào)。

SELECTSno，CnoFROMSCWHEREGradeISNULL；(6)多重條件查詢用邏輯運(yùn)算符AND和OR來(lái)聯(lián)結(jié)多個(gè)查詢條件

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

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

[NOT]IN[NOT]BETWEEN…AND…[例20]查詢計(jì)算機(jī)系年齡在20歲以下的學(xué)生姓名。

SELECTSnameFROMStudentWHERESdept='CS'ANDSage<20；改寫(xiě)[例9][例9]查詢信息系（IS）、數(shù)學(xué)系（MA）和計(jì)算機(jī)科學(xué)系（CS）學(xué)生的姓名和性別。SELECTSname，SsexFROMStudentWHERESdeptIN('IS'，'MA'，'CS')可改寫(xiě)為：SELECTSname，SsexFROMStudentWHERESdept='IS'ORSdept='MA'ORSdept='CS'；改寫(xiě)[例8][例8]查詢年齡在20~23歲（包括20歲和23歲）之間的學(xué)生的姓名、系別和年齡。

SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；

可改寫(xiě)為：

SELECTSname，Sdept，SageFROMStudentWHERESage>=20ANDSage<=23；3、對(duì)查詢結(jié)果排序

使用ORDERBY子句可以按一個(gè)或多個(gè)屬性列排序升序：ASC；降序：DESC；缺省值為升序當(dāng)排序列含空值時(shí)ASC：排序列為空值的元組最后顯示DESC：排序列為空值的元組最先顯示[例21]查詢選修了3號(hào)課程的學(xué)生的學(xué)號(hào)及其成績(jī)，查詢結(jié)果按分?jǐn)?shù)降序排列。

SELECTSno，GradeFROMSCWHERECno='3'ORDERBYGradeDESC；查詢結(jié)果

SnoGrade--------------95010950249500792950038295010829500975950146195002553、對(duì)查詢結(jié)果排序[例22]查詢?nèi)w學(xué)生情況，查詢結(jié)果按所在系的系號(hào)升序排列，同一系中的學(xué)生按年齡降序排列。

SELECT*FROMStudentORDERBYSdept，SageDESC；4、使用集函數(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]<列名>） DISTINCT短語(yǔ)：在計(jì)算時(shí)要取消指定列中的重復(fù)值A(chǔ)LL短語(yǔ)：不取消重復(fù)值A(chǔ)LL為缺省值4、使用集函數(shù)[例23]查詢學(xué)生總?cè)藬?shù)。

SELECTCOUNT(*)FROMStudent；

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

SELECTCOUNT(DISTINCTSno)FROMSC；注：用DISTINCT以避免重復(fù)計(jì)算學(xué)生人數(shù)4、使用集函數(shù)[例25]計(jì)算1號(hào)課程的學(xué)生平均成績(jī)。

SELECTAVG(Grade)FROMSCWHERECno='1'；

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

SELECTMAX(Grade)FROMSCWHERCno='1'；4、使用聚集函數(shù)[例27]查詢學(xué)號(hào)為“200215012”選修課程的總學(xué)分?jǐn)?shù)。

SELECTSUM(Ccredit)FROMSC，CourseWHERESno=‘200215012’ANDSC.Cno=Course.Cno；

注：在聚集函數(shù)遇到空值時(shí)，除COUNT(*)外，都跳過(guò)空值而只處理非空值。

5、對(duì)查詢結(jié)果分組使用GROUPBY子句分組細(xì)化集函數(shù)的作用對(duì)象未對(duì)查詢結(jié)果分組，集函數(shù)將作用于整個(gè)查詢結(jié)果對(duì)查詢結(jié)果分組后，集函數(shù)將分別作用于每個(gè)組GROUPBY子句的作用對(duì)象是查詢的中間結(jié)果表分組方法：按指定的一列或多列值分組，值相等的為一組使用GROUPBY子句后，SELECT子句的列名列表中只能出現(xiàn)分組屬性和集函數(shù)

5、對(duì)查詢結(jié)果分組[例28]求各個(gè)課程號(hào)及相應(yīng)的選課人數(shù)。

SELECTCno，COUNT(Sno)FROMSCGROUPBYCno；

結(jié)果

CnoCOUNT(Sno)

122

234 344 433 548[例29]查詢選修了3門(mén)以上課程的學(xué)生學(xué)號(hào)。

SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)>=3；

只有滿足HAVING短語(yǔ)指定條件的組才輸出HAVING短語(yǔ)與WHERE子句的區(qū)別：作用對(duì)象不同WHERE子句作用于基表或視圖，從中選擇滿足條件的元組。HAVING短語(yǔ)作用于組，從中選擇滿足條件的組。

例題[例30]查詢有3門(mén)以上課程是90分以上的學(xué)生的學(xué)號(hào)及（90分以上的）課程數(shù)

SELECTSno,COUNT(*)FROMSCWHEREGrade>=90GROUPBYSnoHAVINGCOUNT(*)>=3;

3.3.2連接查詢同時(shí)涉及多個(gè)表的查詢稱為連接查詢用來(lái)連接兩個(gè)表的條件稱為連接條件或連接謂詞一般格式：[<表名1>.]<列名1><比較運(yùn)算符>[<表名2>.]<列名2>

比較運(yùn)算符：=、>、<、>=、<=、!=[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>連接字段連接謂詞中的列名稱為連接字段連接條件中的各連接字段類型必須是可比的，但不必是相同的連接操作的執(zhí)行過(guò)程嵌套循環(huán)法(NESTED-LOOP)首先在表1中找到第一個(gè)元組，然后從頭開(kāi)始掃描表2，逐一查找滿足連接條件的元組，找到后就將表1中的第一個(gè)元組與該元組拼接起來(lái)，形成結(jié)果表中一個(gè)元組。表2全部查找完后，再找表1中第二個(gè)元組，然后再?gòu)念^開(kāi)始掃描表2，逐一查找滿足連接條件的元組，找到后就將表1中的第二個(gè)元組與該元組拼接起來(lái)，形成結(jié)果表中一個(gè)元組。重復(fù)上述操作，直到表1中的全部元組都處理完畢連接操作的過(guò)程：

表1

表2

第一個(gè)元組第二個(gè)元組滿足連接條件的元組下一個(gè)元組連接條件拼接成結(jié)果表中元組排序合并法(SORT-MERGE)常用于=連接首先按連接屬性對(duì)表1和表2排序?qū)Ρ?的第一個(gè)元組，從頭開(kāi)始掃描表2，順序查找滿足連接條件的元組，找到后就將表1中的第一個(gè)元組與該元組拼接起來(lái)，形成結(jié)果表中一個(gè)元組。當(dāng)遇到表2中第一條大于表1連接字段值的元組時(shí)，對(duì)表2的查詢不再繼續(xù)找到表1的第二條元組，然后從剛才的中斷點(diǎn)處繼續(xù)順序掃描表2，查找滿足連接條件的元組，找到后就將表1中的第一個(gè)元組與該元組拼接起來(lái)，形成結(jié)果表中一個(gè)元組。直接遇到表2中大于表1連接字段值的元組時(shí)，對(duì)表2的查詢不再繼續(xù)重復(fù)上述操作，直到表1或表2中的全部元組都處理完畢為止95001張明1995002李樟199500119095001290950013909500229095002190索引連接(INDEX-JOIN)對(duì)表2按連接字段建立索引對(duì)表1中的每個(gè)元組，依次根據(jù)其連接字段值查詢表2的索引，從中找到滿足條件的元組，找到后就將表1中的第一個(gè)元組與該元組拼接起來(lái)，形成結(jié)果表中一個(gè)元組95001張明1995002李樟1995001190950021909500139095002290950012901、內(nèi)連接[例31]查詢每個(gè)學(xué)生及其選修課程的情況。SELECTStudent.*，SC.*FROMStudent，SCWHEREStudent.Sno=SC.Sno；（1）等值連接連接運(yùn)算符為=的連接操作

[<表名1>.]<列名1>=[<表名2>.]<列名2>任何子句中引用表1和表2中同名屬性時(shí)，都必須加表名前綴。引用唯一屬性名時(shí)可以加也可以省略表名前綴。Student表

Sno

SnameSsexSageSdept2011121

李勇

男20CS2011122

劉晨

女19CS2011123

王敏

女18MA2011125

張立

男19IS

SnoCnoGrade20111211922011121285201112138820111222902011122380結(jié)果Student.SnoSnameSsexSageSdeptSC.SnoCnoGrade

2011121李勇男20CS20111211922011121李勇男20CS20111212852011121李勇男20CS20111213882011122劉晨女19IS20111222902011122劉晨女19IS2011122380

(2)自然連接等值連接的一種特殊情況，把目標(biāo)列中重復(fù)的屬性列去掉。[例32]對(duì)[例31]用自然連接完成。

SELECTStudent.Sno，Sname，Ssex，Sage， Sdept，Cno，GradeFROMStudent，SCWHEREStudent.Sno=SC.Sno；(3)非等值連接查詢連接運(yùn)算符不是=的連接操作

[<表名1>.]<列名1><比較運(yùn)算符>[<表名2>.]<列名2>比較運(yùn)算符：>、<、>=、<=、!=[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>

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

SELECTFIRST.Cno，SECOND.CpnoFROMCourseFIRST，CourseSECONDWHEREFIRST.Cpno=SECOND.Cno；FIRST表（COURSE表）SECOND表（COURSE表）

CnoCpno173556查詢結(jié)果分析：1、Course中，課號(hào)→直接先行課號(hào)→間接先行課，所以需要自身連接。2、為Course表取兩個(gè)別名，一個(gè)是FIRST，另一個(gè)是SECOND。2外連接（OuterJoin）外連接與普通連接的區(qū)別普通連接操作只輸出滿足連接條件的元組外連接操作以指定表為連接主體，將主體表中不滿足連接條件的元組一并輸出左外連接和左右連接

[例36]查詢每個(gè)學(xué)生及其選修課程的情況包括沒(méi)有選修課程的學(xué)生----用外連接操作SELECTStudent.Sno，Sname，Ssex， Sage，Sdept，Cno，GradeFROMStudentLEFTOUTJOINSCON(Student.Sno=SC.Sno)；3交叉連接不帶連接謂詞的連接很少使用例：

SELECTStudent.*,SC.*FROMStudent，SC

交叉連接是不帶連接謂詞的連接。它返回被連接的兩個(gè)表的廣義笛卡爾積。4復(fù)合條件連接WHERE子句中含多個(gè)連接條件時(shí)，稱為復(fù)合條件連接[例37]查詢選修2號(hào)課程且成績(jī)?cè)?0分以上的所有學(xué)生的學(xué)號(hào)、姓名SELECTStudent.Sno,Student.SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoAND/*連接謂詞*/SC.Cno='2'AND/*其他限定條件*/SC.Grade>90；

/*其他限定條件*/5多表連接[例33]查詢每個(gè)學(xué)生的學(xué)號(hào)、姓名、選修的課程名及成績(jī)。

SELECTStudent.Sno，Sname，Cname，GradeFROMStudent，SC，CourseWHEREStudent.Sno=SC.SnoandSC.Cno=Course.Cno；

結(jié)果：

Student.SnoSnameCnameGrade 2011121李勇數(shù)據(jù)庫(kù)922011121李勇數(shù)學(xué)852011121李勇信息系統(tǒng)882011122劉晨數(shù)學(xué)902011122劉晨信息系統(tǒng)80例34查詢每個(gè)學(xué)生的學(xué)號(hào)、姓名、選修的課程名及成績(jī)查詢結(jié)果：3.3.3嵌套查詢

SELECTSname外層查詢/父查詢

FROMStudentWHERESnoIN

（SELECTSno內(nèi)層查詢/子查詢

FROMSCWHERECno='2'）；將一個(gè)查詢塊嵌套在另一個(gè)查詢塊的WHERE子句或HAVING短語(yǔ)的條件中的查詢稱為嵌套查詢一個(gè)SELECT-FROM-WHERE語(yǔ)句稱為一個(gè)查詢塊子查詢不能使用ORDERBY子句結(jié)構(gòu)化引出子查詢的謂詞1、帶有IN謂詞的子查詢2、帶有比較運(yùn)算符的子查詢3、帶有ANY或ALL謂詞的子查詢4、帶有EXISTS謂詞的子查詢1、帶有IN謂詞的子查詢[例34]查詢與“劉晨”在同一個(gè)系學(xué)習(xí)的學(xué)生。

此查詢要求可以分步來(lái)完成①確定“劉晨”所在系名

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

Sdept CS②查找所有在CS系學(xué)習(xí)的學(xué)生。

SELECTSno，Sname，SdeptFROMStudentWHERESdept='CS'；結(jié)果為：SnoSnameSdept2011121李勇CS2011122劉晨CS

1、帶有IN謂詞的子查詢

構(gòu)造嵌套查詢將第一步查詢嵌入到第二步查詢的條件中

SELECTSno，Sname，SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname=‘

劉晨’)；此查詢?yōu)椴幌嚓P(guān)子查詢。DBMS求解該查詢時(shí)也是分步去做的。

用自身連接完成本查詢要求

SELECTS1.Sno，S1.Sname，S1.SdeptFROMStudentS1，StudentS2WHERES1.Sdept=S2.SdeptAND

S2.Sname='劉晨'；父查詢和子查詢中的表均可以定義別名

SELECTSno，Sname，SdeptFROMStudentS1WHERES1.SdeptIN(SELECTSdeptFROMStudentS2WHERES2.Sname=‘

劉晨’)；1、帶有IN謂詞的子查詢[例35]查詢選修了課程名為“信息系統(tǒng)”的學(xué)生學(xué)號(hào)和姓名

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

FROMStudent取出Sno和SnameWHERESnoIN(SELECTSno②然后在SC關(guān)系中找出選

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

WHERECnoIN(SELECTCno①首先在Course關(guān)系中找出“信

FROMCourse息系統(tǒng)”的課程號(hào)，結(jié)果為3號(hào)

WHERECname=‘信息系統(tǒng)’));1、帶有IN謂詞的子查詢結(jié)果：

Sno Sname--------------2011121李勇

2011122劉晨1、帶有IN謂詞的子查詢用連接查詢

SELECTSno，SnameFROMStudent，SC，CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.Cname=‘信息系統(tǒng)’；1、帶有IN謂詞的子查詢2、帶有比較運(yùn)算符的子查詢

當(dāng)能確切知道內(nèi)層查詢返回單值時(shí)，可用比較運(yùn)算符（>，<，=，>=，<=，!=或<>）。與ANY或ALL謂詞配合使用例：假設(shè)一個(gè)學(xué)生只可能在一個(gè)系學(xué)習(xí)，并且必須屬于一個(gè)系，則在[例39]可以用=代替IN

：

SELECTSno，Sname，SdeptFROMStudentWHERESdept=SELECTSdeptFROMStudentWHERESname='劉晨'；1、帶有IN謂詞的子查詢

子查詢一定要跟在比較符之后

錯(cuò)誤的例子：

SELECTSno，Sname，SdeptFROMStudentWHERE(SELECTSdeptFROMStudentWHERESname=‘

劉晨’

)=Sdept；1、帶有IN謂詞的子查詢[例36]找出每個(gè)學(xué)生超過(guò)他選修課程平均成績(jī)的課程號(hào)。SELECTSno,CnoFROMSCxWHEREGrade>=(SELECTAVG(Grade)FROMSCyWHEREy.Sno=x.Sno)1、帶有IN謂詞的子查詢x是表SC的別名，又稱為元組變量，可以用來(lái)表示SC的一個(gè)元組。內(nèi)層查詢是一個(gè)學(xué)生所有選修課程平均成績(jī)的，至于哪個(gè)學(xué)生的平均成績(jī)要看參數(shù)x.Sno的值，而該值是與父查詢相關(guān)的，因此這類查詢稱為相關(guān)子查詢。1、帶有IN謂詞的子查詢3、帶有ANY或ALL謂詞的子查詢需要配合使用比較運(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é)果中的所有值（通常沒(méi)有實(shí)際意義）!=（或<>）ANY 不等于子查詢結(jié)果中的某個(gè)值!=（或<>）ALL 不等于子查詢結(jié)果中的任何一個(gè)值[例37]查詢其他系中比計(jì)算機(jī)科學(xué)系某一個(gè)學(xué)生年齡小的學(xué)生姓名和年齡

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

ANDSdept<>'CS';/*注意這是父查詢塊中的條件*/3、帶有ANY或ALL謂詞的子查詢結(jié)果

Sname Sage

王敏18

張立19執(zhí)行過(guò)程1.DBMS執(zhí)行此查詢時(shí)，首先處理子查詢，找出

CS系中所有學(xué)生的年齡，構(gòu)成一個(gè)集合(20，19)2.處理父查詢，找所有不是CS系且年齡小于

20或19的學(xué)生3、帶有ANY或ALL謂詞的子查詢[例37']：用集函數(shù)實(shí)現(xiàn)[例37]SELECTSname，SageFROMStudentWHERESage<(SELECTMAX(Sage)FROMStudentWHERESdept='CS')ANDSdept<>'CS’;3、帶有ANY或ALL謂詞的子查詢ANY和ALL謂詞有時(shí)可以用集函數(shù)實(shí)現(xiàn)ANY與ALL與集函數(shù)的對(duì)應(yīng)關(guān)系

=

<>或!=

<<=>>=ANY

IN

--

<MAX<=MAX>MIN>=MINALL--

NOTIN

<MIN<=MIN>MAX>=MAX用集函數(shù)實(shí)現(xiàn)子查詢通常比直接用ANY或ALL查詢效率要高，因?yàn)榍罢咄ǔＤ軌驕p少比較次數(shù)3、帶有ANY或ALL謂詞的子查詢[例38]查詢其他系中比計(jì)算機(jī)科學(xué)系所有學(xué)生年齡都小的學(xué)生姓名及年齡。方法一：用ALL謂詞

SELECTSname，SageFROMStudentWHERESage<ALL(SELECTSageFROMStudentWHERESdept='CS')ANDSdept<>'CS’;3、帶有ANY或ALL謂詞的子查詢結(jié)果

Sname Sage

王敏18執(zhí)行過(guò)程1.DBMS執(zhí)行此查詢時(shí)，首先處理子查詢，找出

CS系中所有學(xué)生的年齡，構(gòu)成一個(gè)集合(20，19)2.處理父查詢，找所有不是CS系且年齡小于

20，并且小于

19的學(xué)生3、帶有ANY或ALL謂詞的子查詢

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

SELECTSname，SageFROMStudentWHERESage<(SELECTMIN(Sage)FROMStudentWHERESdept='CS')ANDSdept<>'CS’;3、帶有ANY或ALL謂詞的子查詢4、帶有EXISTS謂詞的子查詢1.EXISTS謂詞2.NOTEXISTS謂詞3.不同形式的查詢間的替換4.相關(guān)子查詢的效率5.用EXISTS/NOTEXISTS實(shí)現(xiàn)全稱量詞6.用EXISTS/NOTEXISTS實(shí)現(xiàn)邏輯蘊(yùn)涵1)EXISTS謂詞存在量詞

帶有EXISTS謂詞的子查詢不返回任何數(shù)據(jù)，只產(chǎn)生邏輯真值“true”或邏輯假值“false”。若內(nèi)層查詢結(jié)果非空，則返回真值若內(nèi)層查詢結(jié)果為空，則返回假值由EXISTS引出的子查詢，其目標(biāo)列表達(dá)式通常都用*，因?yàn)閹XISTS的子查詢只返回真值或假值，給出列名無(wú)實(shí)際意義2)NOTEXISTS謂詞4、帶有EXISTS謂詞的子查詢[例39]查詢所有選修了1號(hào)課程的學(xué)生姓名。用嵌套查詢

SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSC/*相關(guān)子查詢*/WHERESno=Student.SnoAND Cno=‘1’)4、帶有EXISTS謂詞的子查詢

查詢過(guò)程：

(1)取外層查詢Student表中的第一個(gè)元組，根據(jù)它與內(nèi)層查詢相關(guān)的屬性值（Sno）處理內(nèi)層查詢。

(2)若WHERE子句返回值為真，則取此元組放入結(jié)果表中。再取Student表中的下一個(gè)元組。直至Student表全部檢查完畢。

(3)再對(duì)結(jié)果表投影取Sname4、帶有EXISTS謂詞的子查詢STUDENTSCEXISTTRUEFALSEFALSEFALSE結(jié)果用連接運(yùn)算SELECTSnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno='1';4、帶有EXISTS謂詞的子查詢[例38]查詢沒(méi)有選修1號(hào)課程的學(xué)生姓名。

SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.Sno ANDCno='1')；此例用連接運(yùn)算難于實(shí)現(xiàn)

3)不同形式的查詢間的替換一些帶EXISTS或NOTEXISTS謂詞的子查詢不能被其他形式的子查詢等價(jià)替換所有帶IN謂詞、比較運(yùn)算符、ANY和ALL謂詞的子查詢都能用帶EXISTS謂詞的子查詢等價(jià)替換。

4、帶有EXISTS謂詞的子查詢4)用EXISTS/NOTEXISTS實(shí)現(xiàn)全稱量詞(難點(diǎn))SQL語(yǔ)言中沒(méi)有全稱量詞（Forall）可以把帶有全稱量詞的謂詞轉(zhuǎn)換為等價(jià)的帶有存在量詞的謂詞：

(x)P≡

(x(P))[例40]查詢選修了全部課程的學(xué)生姓名。

SELECTSname,Sno

FROMStudent某個(gè)學(xué)生

WHERENOT

EXISTS

不存在

(

SELECT*

FROMCourse一門(mén)課

WHERE

NOTEXISTS

不選修

(SELECT*

FROMSC

WHERE

Sno=Student.SnoANDCno=Course.Cno));4、帶有EXISTS謂詞的子查詢StudentCourseSCStudentCourseSCNOTEXISTSNOTEXISTSFALSEFALSEFALSETRUE5)用EXISTS/NOTEXISTS實(shí)現(xiàn)邏輯蘊(yùn)涵(難點(diǎn))SQL語(yǔ)言中沒(méi)有蘊(yùn)涵(Implication)邏輯運(yùn)算可以利用謂詞演算將邏輯蘊(yùn)涵謂詞等價(jià)轉(zhuǎn)換為：

pq≡p∨q4、帶有EXISTS謂詞的子查詢

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

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

則上述查詢?yōu)?(y)pq4、帶有EXISTS謂詞的子查詢等價(jià)變換：

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

y(p∧q)變換后語(yǔ)義：不存在這樣的課程y，學(xué)生95002選修了y，而學(xué)生x沒(méi)有選。4、帶有EXISTS謂詞的子查詢

SELECTDISTINCTSnoFROMSCSCX

WHERENOTEXISTS不存在一門(mén)課程

(SELECT*FROMSCSCYWHERESCY.Sno=‘95002’

95002號(hào)選了

ANDNOTEXISTS(SELECT*另一個(gè)學(xué)生

FROMSCSCZ

沒(méi)有選

WHERESCZ.Sno=SCX.SnoANDSCZ.Cno=SCY.Cno));4、帶有EXISTS謂詞的子查詢SCXSCYSCZSCXSCYSCZNOTEXISTSNOTEXISTSFALSEFALSETRUE結(jié)果3.3.4集合查詢集合操作種類1.并操作(UNION)2.交操作(INTERSECT)3.差操作(EXCEPT)注：1參加集合操作的各查詢結(jié)果的列數(shù)必須相同；2對(duì)應(yīng)項(xiàng)的數(shù)據(jù)類型也必須相同。1、并操作形式

<查詢塊R> UNION <查詢塊S>查詢結(jié)果為屬于R或?qū)儆赟的元組。參加UNION操作的各結(jié)果表的列數(shù)必須相同；對(duì)應(yīng)項(xiàng)的數(shù)據(jù)類型也必須相同[例42]查詢計(jì)算機(jī)科學(xué)系的學(xué)生及年齡不大于19歲的學(xué)生。方法一：

SELECT*FROMStudentWHERESdept='CS'UNIONSELECT*FROMStudentWHERESage<=19；1、并操作方法二：SELECTDISTINCT*FROMStudentWHERESdept='CS'ORSage<=19；[例43]查詢選修了課程1或者選修了課程2的學(xué)生。方法一：

SELECTSnoFROMSCWHERECno='1'UNIONSELECTSnoFROMSCWHERECno='2'；1、并操作方法二：SELECTDISTINCTSnoFROMSCWHERECno='1'ORCno='2'；[補(bǔ)充示例