數(shù)據(jù)庫原理TheTheoryofDatabaseSystem第三章關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQLSQL概述數(shù)據(jù)定義功能本講主要內(nèi)容1974年由Boyce和Chamberlin提出，1975年～1979年IBM公司在SystemR原型系統(tǒng)上實(shí)現(xiàn)。3.1.1SQL的發(fā)展關(guān)系數(shù)據(jù)庫的標(biāo)準(zhǔn)語言，是數(shù)據(jù)庫領(lǐng)域中一個主流語言3.1SQL概述SQL/86：第一個SQL標(biāo)準(zhǔn)。由美國國家標(biāo)準(zhǔn)局（AmericanNationalStandardInstitute，簡稱ANSI）公布，1987年國際標(biāo)準(zhǔn)化組織（InternationalOrganizationforStandardization，簡稱ISO）通過。SQL標(biāo)準(zhǔn)SQL/89SQL/92（簡稱SQL2）SQL/99（簡稱SQL3）SQL/4正在進(jìn)行之中功能動詞數(shù)據(jù)庫查詢SELECT數(shù)據(jù)定義CREATE，DROPALTER數(shù)據(jù)操縱INSERT，UPDATE，DELETE數(shù)據(jù)控制GRANT，REVOKE(4)語言簡潔，易學(xué)易用3.1.2SQL的特點(diǎn)(3)高度非過程化(1)數(shù)據(jù)描述、操縱、控制等功能一體化(2)兩種使用方式，統(tǒng)一的語法結(jié)構(gòu)3.1SQL概述外模式模式內(nèi)模式3.1.3SQL體系結(jié)構(gòu)基本表4存儲文件2

SQL視圖1視圖2基本表1存儲文件1基本表2基本表3用戶基本表（BaseTable）。簡稱基表，是數(shù)據(jù)庫中實(shí)際存在的關(guān)系。視圖是從一個或幾個基表導(dǎo)出的表，它本身不實(shí)際存儲在數(shù)據(jù)庫中，只存放對視圖的定義信息（沒有對應(yīng)的數(shù)據(jù)）。因此，視圖是一個虛表或虛關(guān)系，而基表是一種實(shí)關(guān)系存儲文件。每個基表對應(yīng)一個存儲文件，一個基表還可以帶一個或幾個索引，存儲文件和索引一起構(gòu)成了關(guān)系數(shù)據(jù)庫的內(nèi)模式。3.2SQL的定義功能3.2.1基本表的定義CREATETABLE<表名>（<列名><數(shù)據(jù)類型>[<列級完整性約束條件>]

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

[，<表級完整性約束條件>]

）；[例1]建立一個“學(xué)生”表S，它由學(xué)號Sno、姓名Sname、性別Ssex、年齡Sage、所在系Sdept五個屬性組成。其中學(xué)號不能為空，值是唯一的，并且姓名取值也唯一。CREATETABLES(SnoCHAR(5)NOTNULLUNIQUE，SnameCHAR(20)UNIQUE，SsexCHAR(1)，SageINT，SdeptCHAR(15))；[例2]建立一個“課程”表C，它由課程號Cno、課程名Cname、學(xué)分Credit、先修課程Pro四個屬性組成。其中課程號不能為空，值是唯一的。CREATETABLEC(CnoCHAR(4)NOTNULLUNIQUE,CnameCHAR(15),CreditINT,ProCHAR(4));定義基本表（續(xù)）數(shù)據(jù)類型1)定長和變長字符串CHAR(n)VARCHAR(n)2)定長和變長二進(jìn)制串BIT(n)BITVARING(n)3)整型數(shù)INTSMALLINT4)浮點(diǎn)數(shù)FLOATDOUBLEPRECISION5)日期型DATE6)時間型TIME7)時標(biāo)TIMESTAMP定義基本表（續(xù)）常用完整性約束主碼約束：PRIMARYKEY唯一性約束：UNIQUE非空值約束：NOTNULL參照完整性約束PRIMARYKEY與

UNIQUE的區(qū)別？為學(xué)生表和課程表添加主碼：CREATETABLES(SnoCHAR(5)PrimaryKey，

SnameCHAR(20)，

SsexCHAR(1)，

SageINT，

SdeptCHAR(15))；CREATETABLEC(CnoCHAR(4)PrimaryKey,CnameCHAR(15),CreditINT,ProCHAR(4));[例3]建立一個“學(xué)生選課”表SC，它由學(xué)號Sno、課程號Cno，修課成績Grade組成，其中(Sno,Cno)為主碼。CREATETABLESC(SnoCHAR(5),CnoCHAR(3),GradeINT,Primarykey(Sno,Cno));主關(guān)鍵字的定義1）在列出關(guān)系模式的屬性時,在屬性及其類型后加上保留字PRIMARYKEY；2）在列出關(guān)系模式的所有屬性后，再附加一個聲明：

PRIMARYKEY（<屬性1>[，<屬性2>，…]）說明：如果關(guān)鍵字由多個屬性構(gòu)成，則必須使用第二種方法。外部關(guān)鍵字的定義1）如果外部關(guān)鍵字只有一個屬性，可以在它的屬性名和類型后面直接用“REFERENCES”說明它參照了某個表的某些屬性，其格式為：

REFERENCES<表名>(<屬性>)2）在CREATETABLE語句的屬性列表后面增加一個或幾個外部關(guān)鍵字說明，其格式為：

FOREIGNKEY(<屬性>)REFERENCES<表名>(<屬性>)19為學(xué)生選課表建立外碼：CREATETABLESC(SnoCHAR(5)REFERENCESS(Sno),CnoCHAR(3)REFERENCESC(Cno),GradeINT,PRIMARYKEY(Sno,Cno));20CREATETABLESC(SNOCHAR(8),CNOCHAR(4),GRADESMALLINT,PRIMARYKEY(SNO,CNO),

FOREIGNKEY(SNO)REFERENCESS(SNO),FOREIGNKEY(CNO)REFERENCESC(CNO));3.2.2基本表的修改和刪除1、修改基本表ALTERTABLE<表名>[ADD<新列名><數(shù)據(jù)類型>[完整性約束]][DROP<列名>|<完整性約束名>][ALTER<列名><數(shù)據(jù)類型>]；<表名>：要修改的基本表ADD子句：增加新列和新的完整性約束條件DROP子句：刪除指定的列或完整性約束條件ALTER子句：用于修改列名和數(shù)據(jù)類型[例4]向?qū)W生表增加“入學(xué)時間”列，其數(shù)據(jù)類型為日期型。

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

語句格式（續(xù)）刪除屬性列例：刪除學(xué)生表中“入學(xué)時間”屬性列。ALTERTABLESDropScome；注意：若一個屬性被說明為NOTNULL，則不允許修改或刪除。

[例5]將年齡的數(shù)據(jù)類型改為半字長整數(shù)。

ALTERTABLESALTERSageSMALLINT；注：修改原有的列定義有可能會破壞已有數(shù)據(jù)[例6]刪除學(xué)生姓名必須取唯一值的約束。

ALTERTABLESDROPUNIQUE(Sname)；2、刪除基本表

DROPTABLE<表名>;

刪除基本表時，系統(tǒng)會從數(shù)據(jù)字典中刪去有關(guān)該基本表及其索引的描述基本表刪除數(shù)據(jù)、表上的索引都刪除；表上的視圖往往仍然保留，但無法引用[例7]刪除學(xué)生表

DROPTABLES;關(guān)于缺省值(不適用于ACCESS)可以在定義屬性時增加保留字DEFAULT和一個合適的值。例如：性別CHAR（1）DEFAULT‘男’；年齡SMALLINTDEFAULT1；修改默認(rèn)值

ALTERTABLESADDCONSTRAINTddDEFAULT‘男’

FORsex;在SQLSERVER2005中CREATETABLE

S(SNO

CHAR(8)PRIMARYKEY,SNAMECHAR(8),SEXCHAR(2)DEFAULT'男'

CHECK(SEX='男'OR

SEX='女'),AGETINYINTDEFAULT20,DEPTCHAR(30)DEFAULT'計(jì)算機(jī)學(xué)院');3.2.3索引的建立與刪除索引是與表或視圖關(guān)聯(lián)的磁盤上結(jié)構(gòu)，可以加快從表或視圖中檢索行的速度。索引包含由表或視圖中的一列或多列生成的鍵。這些鍵存儲在一個結(jié)構(gòu)（B樹）中，使數(shù)據(jù)庫可以快速有效地查找與鍵值關(guān)聯(lián)的行。在ACCESS中有重復(fù)索引與非重復(fù)索引SqlServer索引的分類聚集索引聚集索引根據(jù)數(shù)據(jù)行的鍵值在表或視圖中排序和存儲這些數(shù)據(jù)行。每個表只能有一個聚集索引。只有當(dāng)表包含聚集索引時，表中的數(shù)據(jù)行才按排序順序存儲。如果表具有聚集索引，則該表稱為聚集表。如果表沒有聚集索引，則其數(shù)據(jù)行存儲在一個稱為堆的無序結(jié)構(gòu)中。SqlServer索引的分類非聚集索引非聚集索引具有獨(dú)立于數(shù)據(jù)行的結(jié)構(gòu)。非聚集索引包含非聚集索引鍵值，并且每個鍵值項(xiàng)都有指向包含該鍵值的數(shù)據(jù)行的指針。從非聚集索引中的索引行指向數(shù)據(jù)行的指針稱為行定位器。SqlServer索引的分類聚集索引和非聚集索引都可以是唯一的。索引也可以不是唯一的，即多行可以共享同一鍵值。在創(chuàng)建PRIMARYKEY約束時，如果不存在該表的聚集索引且未指定唯一非聚集索引，則將自動對一列或多列創(chuàng)建唯一聚集索引。主鍵列不允許空值。在創(chuàng)建UNIQUE約束時，默認(rèn)情況下將創(chuàng)建唯一非聚集索引，以便強(qiáng)制UNIQUE約束。如果不存在該表的聚集索引，則可以指定唯一聚集索引。一、建立索引語句格式CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…)； 用<表名>指定要建索引的基本表名字索引可以建立在該表的一列或多列上，各列名之間用逗號分隔用<次序>指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCUNIQUE表明此索引的每一個索引值只對應(yīng)唯一的數(shù)據(jù)記錄CLUSTER表示要建立的索引是聚集索引[例8]為學(xué)生-課程數(shù)據(jù)庫中的S，C，SC三個表建立索引。其中S表按學(xué)號升序建唯一索引，C表按課程號升序建唯一索引，SC表按學(xué)號升序和課程號降序建唯一索引。CREATEUNIQUEINDEXStusnoONS(Sno)；CREATEUNIQUEINDEXCoucnoONC(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；建立索引（續(xù)）唯一值索引對于已含重復(fù)值的屬性列不能建UNIQUE索引對某個列建立UNIQUE索引后，插入新記錄時DBMS會自動檢查新記錄在該列上是否取了重復(fù)值。這相當(dāng)于增加了一個UNIQUE約束。建立索引（續(xù)）聚簇索引建立聚簇索引后，基表中數(shù)據(jù)也需要按指定的聚簇屬性值的升序或降序存放。也即聚簇索引的索引項(xiàng)順序與表中記錄的物理順序一致例：CREATECLUSTERINDEXStusnameONS(Sname)；在S表的Sname（姓名）列上建立一個聚簇索引，而且S表中的記錄將按照Sname值的升序存放。建立索引（續(xù)）在一個基本表上最多只能建立一個聚簇索引聚簇索引的用途：對于某些類型的查詢，可以提高查詢效率聚簇索引的適用范圍

很少對基表進(jìn)行增刪操作很少對其中的變長列進(jìn)行修改操作

二、刪除索引DROPINDEX<索引名>；刪除索引時，系統(tǒng)會從數(shù)據(jù)字典中刪去有關(guān)該索引的描述。[例9]刪除S表的Stusname索引。

DROPINDEXStusname；使用索引的技巧小表不需要索引數(shù)據(jù)列中有較多不相同數(shù)據(jù)時可使用索引查詢要返回的數(shù)據(jù)很少時可用索引需要經(jīng)常更新數(shù)據(jù)時不宜用索引只有主索引或聚集索引才會引起數(shù)據(jù)表中數(shù)據(jù)的排序數(shù)據(jù)操縱功能——數(shù)據(jù)查詢本講主要內(nèi)容3.3查詢3.3.1概述3.3.2單表查詢3.3.3連接查詢3.3.4集合查詢3.3.5嵌套查詢3.3.6小結(jié)3.3.1概述語句格式SELECT[ALL|DISTINCT]<目標(biāo)列表達(dá)式>[，<目標(biāo)列表達(dá)式>]…FROM<表名或視圖名>[,<表名或視圖名>]…[WHERE<條件表達(dá)式>][GROUPBY<列名1>[HAVING<條件表達(dá)式>]][ORDERBY<列名2>[ASC|DESC]]；

示例數(shù)據(jù)庫學(xué)生-課程數(shù)據(jù)庫學(xué)生表：S(Sno，Sname，Ssex，Sage，Sdept)課程表：C(Cno，Cname，Cpno，Ccredit)

學(xué)生選課表：SC(Sno，Cno，Grade)3.3查詢3.3.1概述3.3.2單表查詢3.3.3連接查詢3.3.4集合查詢3.3.5嵌套查詢3.3.6小結(jié)3.3.2單表查詢查詢僅涉及一個表，是一種最簡單的查詢操作一、選擇表中的若干列（投影）二、選擇表中的若干元組（選擇）三、對查詢結(jié)果排序四、使用集函數(shù)五、對查詢結(jié)果分組查詢指定列[例1]查詢?nèi)w學(xué)生的學(xué)號與姓名。SELECTSno，SnameFROMS；

[例2]查詢?nèi)w學(xué)生的姓名、學(xué)號、所在系。SELECTSname，Sno，SdeptFROMS；查詢?nèi)苛衃例3]查詢?nèi)w學(xué)生的詳細(xì)記錄。

SELECTSno,Sname,Ssex,Sage,Sdept

FROMS；

或

SELECT*FROMS；

3.查詢經(jīng)過計(jì)算的值SELECT子句的<目標(biāo)列表達(dá)式>為表達(dá)式算術(shù)表達(dá)式字符串常量函數(shù)列別名等3.查詢經(jīng)過計(jì)算的值[例4]查詢?nèi)w學(xué)生的姓名及其出生年份。SELECTSname，year(now())-SageFROMS；

輸出結(jié)果：

SnameExpr1001----------------------

李勇1976

劉晨1977

王名1978

張立19783.查詢經(jīng)過計(jì)算的值使用列別名改變查詢結(jié)果的列標(biāo)題：SELECTSname,2000-SageasBirthYearFROMS；

輸出結(jié)果：

SnameBirthYear----------------------

李勇1976

劉晨1977

王名1978

張立1978二、選擇表中的若干元組消除取值重復(fù)的行查詢滿足條件的元組說明SQL查詢語句的結(jié)果也是一個關(guān)系。關(guān)系代數(shù)中基于關(guān)系是一個集合這樣的數(shù)學(xué)概念，因此，重復(fù)的元組不會在關(guān)系中出現(xiàn)。但在實(shí)踐當(dāng)中，要刪除查詢結(jié)果中的重復(fù)元組是相當(dāng)費(fèi)時的！所以在商用的數(shù)據(jù)庫產(chǎn)品中，允許在關(guān)系和SQL表達(dá)式的結(jié)果中出現(xiàn)重復(fù)元組。1.消除取值重復(fù)的行在SELECT子句中使用DISTINCT短語假設(shè)SC表中有下列數(shù)據(jù)

SnoCnoGrade---------------------9500119295001285950013889500229095002380ALL與DISTINCT[例5]查詢選修了課程的學(xué)生學(xué)號。(1)SELECTSnoFROMSC;

或

SELECTALLSnoFROMSC;(默認(rèn)ALL)結(jié)果：Sno-------9500195001950019500295002

(2)SELECTDISTINCTSnoFROMSC;

結(jié)果：

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

SELECTDISTINCTCno，GradeFROMSC;

2.查詢滿足條件的元組帶有where子句的查詢常用的查詢條件查詢條件 謂詞比較 =,<>,>,>=,<,<=算術(shù)運(yùn)算 +-*/確定范圍 BetweenAnd, NotBetweenAnd確定集合 IN,NOTIN字符匹配 Like,NotLike空值 ISNULL,ISNOTNULL多重條件 AND,OR(1)比較大小在WHERE子句的<比較條件>中使用比較運(yùn)算符=，>，<，>=，<=，!=或<>，!>，!<，邏輯運(yùn)算符NOT+比較運(yùn)算符[例6]查詢所有年齡在20歲以下的學(xué)生姓名及其年齡。

SELECTSname，SageFROMSWHERESage<20；或

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

SELECTSname，Sdept，SageFROMSWHERESageBETWEEN20AND23；

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

(3)確定集合使用謂詞：IN<值表>,NOTIN<值表><值表>：用逗號分隔的一組取值[例9]查詢信息系（IS）、數(shù)學(xué)系（MA）和計(jì)算機(jī)科學(xué)系（CS）學(xué)生的姓名和性別。SELECTSname，SsexFROMSWHERESdeptIN('IS'，'MA'，'CS');(3)確定集合[例10]查詢既不是信息系、數(shù)學(xué)系，也不是計(jì)算機(jī)科學(xué)系的學(xué)生的姓名和性別。SELECTSname，SsexFROMS WHERESdeptNOTIN('IS'，'MA'，'CS');(4)字符串匹配[NOT]LIKE‘<匹配串>’[ESCAPE‘<轉(zhuǎn)義字符>’]<匹配串>：指定匹配模板匹配模板：固定字符串或含通配符的字符串當(dāng)匹配模板為固定字符串時，可以用=運(yùn)算符取代LIKE謂詞用!=或<>運(yùn)算符取代NOTLIKE謂詞通配符%(百分號)代表任意長度（長度可以為0）的字符串例：a%b表示以a開頭，以b結(jié)尾的任意長度的字符串。如acb，addgb，ab等都滿足該匹配串。_(下橫線)代表任意單個字符例：a_b表示以a開頭，以b結(jié)尾的長度為3的任意字符串。如acb，afb等都滿足該匹配串。ESCAPE短語當(dāng)用戶要查詢的字符串本身就含有%或_時，要使用ESCAPE‘<轉(zhuǎn)義字符>’

短語對通配符進(jìn)行轉(zhuǎn)義。如：like'__\%'escape'\'

在access中查找通配符用[]1)匹配模板為固定字符串[例11]查詢學(xué)號為95001的學(xué)生的詳細(xì)情況。

SELECT*FROMSWHERESnoLIKE'95001'；等價于：

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

SELECTSname，Sno，SsexFROMSWHERESnameLIKE‘劉%’；匹配模板為含通配符的字符串（續(xù)）[例13]查詢姓"歐陽"且全名為三個漢字的學(xué)生的姓名。

SELECTSnameFROMSWHERESnameLIKE'歐陽__'；匹配模板為含通配符的字符串（續(xù)）[例14]查詢名字中第2個字為"陽"字的學(xué)生的姓名和學(xué)號。

SELECTSname，SnoFROMSWHERESnameLIKE'__陽%'；匹配模板為含通配符的字符串（續(xù)）[例15]查詢所有不姓劉的學(xué)生姓名。

SELECTSname，Sno，SsexFROMSWHERESnameNOTLIKE'劉%'；3)使用轉(zhuǎn)義字符將通配符轉(zhuǎn)義為普通字符

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

SELECTCno，CcreditFROMCWHERECnameLIKE'DB\_Design'

ESCAPE'\‘;使用換碼字符將通配符轉(zhuǎn)義為普通字符(續(xù))[例17]查詢以"DB_"開頭，且倒數(shù)第3個字符為i的課程的詳細(xì)情況。

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

使用謂詞ISNULL或ISNOTNULL

“ISNULL”

不能用“=NULL”

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

SELECTSno，CnoFROMSCWHEREGradeISNULL；[例19]查所有有成績的學(xué)生學(xué)號和課程號。

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

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

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

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

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

SELECTSname，Sdept，SageFROMSWHERESageBETWEEN20AND23；可改寫為：SELECTSname，Sdept，SageFROMSWHERESage>=20ANDSage<=23；三、對查詢結(jié)果排序

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

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

SELECT*FROMSORDERBYSdept，SageDESC；四、使用集函數(shù)5類主要集函數(shù)計(jì)數(shù)COUNT（[DISTINCT|ALL]*）COUNT（[DISTINCT|ALL]<列名>）計(jì)算總和SUM（[DISTINCT|ALL]<列名>） 計(jì)算平均值A(chǔ)VG（[DISTINCT|ALL]<列名>）使用集函數(shù)（續(xù)）求最大值MAX（[DISTINCT|ALL]<列名> 求最小值MIN（[DISTINCT|ALL]<列名> DISTINCT短語：在計(jì)算時要取消指定列中的重復(fù)值A(chǔ)LL短語：不取消重復(fù)值A(chǔ)LL為缺省值[例25]查詢學(xué)生總?cè)藬?shù)。

SELECTCOUNT(*)FROMS；

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

SELECTCOUNT(DISTINCTSno)FROMSC；注：用DISTINCT以避免重復(fù)計(jì)算學(xué)生人數(shù)[例27]計(jì)算1號課程的學(xué)生平均成績。

SELECTAVG(Grade)FROMSCWHERECno='1'；

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

SELECTMAX(Grade)FROMSCWHERCno='1'；五、對查詢結(jié)果分組使用GROUPBY子句分組 細(xì)化集函數(shù)的作用對象未對查詢結(jié)果分組，集函數(shù)將作用于整個查詢結(jié)果對查詢結(jié)果分組后，集函數(shù)將分別作用于每個組[例29]求各個課程號及相應(yīng)的選課人數(shù)。

SELECTCno，COUNT(Sno)FROMSCGROUPBYCno；

結(jié)果：

CnoCOUNT(Sno) 122 234 344 433 548對查詢結(jié)果分組（續(xù)）GROUPBY子句的作用對象是查詢的中間結(jié)果表分組方法：按指定的一列或多列值分組，值相等的為一組使用GROUPBY子句后，SELECT子句的列名列表中只能出現(xiàn)分組屬性和集函數(shù)使用HAVING短語篩選最終輸出結(jié)果[例30]查詢選修了3門以上課程的學(xué)生學(xué)號。

SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)>3；

[例31]查詢有3門以上課程是90分以上的學(xué)生的學(xué)號及（90分以上的）課程數(shù)

SELECTSno,COUNT(*)FROMSCWHEREGrade>=90GROUPBYSnoHAVINGCOUNT(*)>=3;使用HAVING短語篩選最終輸出結(jié)果只有滿足HAVING短語指定條件的組才輸出HAVING短語與WHERE子句的區(qū)別：作用對象不同WHERE子句作用于基表或視圖，從中選擇滿足條件的元組。HAVING短語作用于組，從中選擇滿足條件的組。3.3查詢3.3.1概述3.3.2單表查詢3.3.3連接查詢3.3.4集合查詢3.3.5嵌套查詢3.3.6小結(jié)3.3.3連接查詢同時涉及多個表的查詢稱為連接查詢用來連接兩個表的條件稱為連接條件或連接謂詞一般格式：[<表名1>.]<列名1><比較運(yùn)算符>[<表名2>.]<列名2>

比較運(yùn)算符：=、>、<、>=、<=、!=連接查詢（續(xù)）SQL中連接查詢的主要類型 廣義笛卡爾積 等值連接(含自然連接)

非等值連接查詢 自身連接查詢 外連接查詢 復(fù)合條件連接查詢一、廣義笛卡爾積不帶連接謂詞的連接很少使用例：

SELECTS.*,SC.*FROMS,SC；二、等值與非等值連接查詢等值連接、自然連接、非等值連接[例32]查詢學(xué)生及其選修課程的情況等價于：

SELECTS.*，SC.*FROMS，SCWHERES.Sno=SC.Sno；SELECTS.*，SC.*FROMSINNERJOINSCONS.Sno=SC.Sno;等值連接連接運(yùn)算符為=的連接操作

[<表名1>.]<列名1>=[<表名2>.]<列名2>任何子句中引用表1和表2中同名屬性時，都必須加表名前綴。引用唯一屬性名時可以加也可以省略表名前綴。假設(shè)S表、SC表分別有下列數(shù)據(jù)：

S表SnoSnameSsexSageSdept95001

李勇

男20CS95002

劉晨

女19IS95003

王敏

女18MA95004

張立

男19ISSC表SnoCnoGrade9500119295001285950013889500229095002380等值連接結(jié)果表S.SnoSnameSsexSageSdeptSC.SnoCnoGrade95001李勇男20 CS 9500119295001李勇男20 CS 9500128595001李勇男20 CS 9500138895002劉晨女19 IS 9500229095002劉晨女19 IS 95002380

自然連接等值連接的一種特殊情況，把目標(biāo)列中重復(fù)的屬性列去掉。[例33]查詢學(xué)生及其選修課程的情況（用自然連接完成）。

SELECTS.Sno，Sname，Ssex，Sage， Sdept，Cno，GradeFROMS，SCWHERES.Sno=SC.Sno；非等值連接查詢連接運(yùn)算符不是=

的連接操作

[<表名1>.]<列名1><比較運(yùn)算符>[<表名2>.]<列名2>比較運(yùn)算符：>、<、>=、<=、!=三、自身連接一個表與其自己進(jìn)行連接，稱為表的自身連接；需要給表起別名以示區(qū)別；由于所有屬性名都是同名屬性，因此必須使用別名前綴。自身連接（續(xù)）[例34]查詢每一門課的間接先修課（即先修課的先修課）

SELECTFIRST.Cno，SECOND.CpnoFROMCasFIRST，CasSECONDWHEREFIRST.Cpno=SECOND.Cno；

FIRST表（C表）CnoCnameCpnoCcredit1數(shù)據(jù)庫542數(shù)學(xué)

23信息系統(tǒng)144操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理

27PASCAL語言64SECOND表（C表）CnoCnameCpnoCcredit1數(shù)據(jù)庫542數(shù)學(xué)

23信息系統(tǒng)144操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理

27PASCAL語言64查詢結(jié)果

173556

cnocpno四、外連接（OuterJoin）外連接與普通連接的區(qū)別普通連接操作只輸出滿足連接條件的元組外連接操作以指定表為連接主體，將主體表中不滿足連接條件的元組一并輸出

[例35]查詢每個學(xué)生及其選修課程的情況包括沒有選修課程的學(xué)生----用外連接操作

SELECTS.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMS,SCWHERES.Sno=SC.Sno(*)；或者

SELECTS.Sno,Sname,Ssex,Sage,Sdept,Cno，GradeFROMSLEFT(OUTER)JOIN

SC

ONS.Sno=SC.Sno；結(jié)果：

S.SnoSnameSsexSageSdeptCnoGrade

95001李勇男20CS19295001李勇男20CS28595001李勇男20CS38895002劉晨女19IS29095002劉晨女19IS38095003王敏女18MA95004張立男19IS外連接（續(xù)）左外連接LEFT(OUTER)JOIN<表名>ON<條件>;右外連接RIGHT(OUTER)JOIN<表名>ON<條件>;全外連接FULL(OUTER)JOIN<表名>ON<條件>;五、復(fù)合條件連接WHERE子句中含多個連接條件時，稱為復(fù)合條件連接[例36]查詢選修2號課程且成績在90分以上的所有學(xué)生的學(xué)號、姓名SELECTS.Sno,S.SnameFROMS,SCWHERES.Sno=SC.SnoAND/*連接謂詞*/SC.Cno='2'AND/*其他限定條件*/SC.Grade>90；

/*其他限定條件*/多表連接[例37]查詢每個學(xué)生的學(xué)號、姓名、選修的課程名及成績。

SELECTS.Sno，Sname，Cname，GradeFROMS，SC，CWHERES.Sno=SC.SnoandSC.Cno=C.Cno；

結(jié)果：

S.SnoSnameCnameGrade 95001李勇數(shù)據(jù)庫9295001李勇數(shù)學(xué)8595001李勇信息系統(tǒng)8895002劉晨數(shù)學(xué)9095002劉晨信息系統(tǒng)80TOP謂詞例：查詢成績前五名的學(xué)生姓名，課程名及成績。SELECTTOP5SNAME,CNAME,GRADEFROMS,SC,CWHERES.SNO=SC.SNOANDSC.CNO=C.CNOORDERBYGRADEDESC;3.3查詢3.3.1概述3.3.2單表查詢3.3.3連接查詢3.3.4集合查詢3.3.5嵌套查詢3.3.6小結(jié)3.3.4集合查詢標(biāo)準(zhǔn)SQL直接支持的集合操作種類并操作(UNION)一般商用數(shù)據(jù)庫支持的集合操作種類并操作(UNION)交操作(INTERSECT)差操作(EXCEPT)1．并操作形式

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

(SELECT*FROMSWHERESdept='CS‘)UNION(SELECT*FROMSWHERESage<=19)；方法二：

SELECT*FROMSWHERESdept='CS'ORSage<=19；[例39]查詢選修了課程1或者選修了課程2的學(xué)生。方法一：

(SELECTSnoFROMSCWHERECno='1')UNION(SELECTSnoFROMSCWHERECno='2')；方法二：

SELECTDISTINCTSnoFROMSCWHERECno='1'ORCno='2'；[例40]設(shè)數(shù)據(jù)庫中有一教師表Teacher(Tno,Tname,...)。查詢學(xué)校中所有師生的姓名。

(SELECTSnameFROMS)UNION(SELECTTnameFROMTeacher);2．交操作標(biāo)準(zhǔn)SQL中沒有提供集合交操作，但可用其他方法間接實(shí)現(xiàn)。有些DBMS支持交運(yùn)算：形式

<查詢塊>INTERSECT<查詢塊>[例41]查詢既選修了課程1又選修了課程2的學(xué)生學(xué)號。

(SELECTSC.SNOFROMSCWHERESC.CNO='1')

INTERSECT(SELECTSC.SNOFROMSCWHERESC.CNO='2');3．差操作標(biāo)準(zhǔn)SQL中沒有提供集合交操作，但可用其他方法間接實(shí)現(xiàn)。有些DBMS支持交運(yùn)算：形式

<查詢塊>EXCEPT<查詢塊>3．差操作[例42]查詢只選修課程1而沒有選修課程2的學(xué)生學(xué)號。

(SELECTSC.SNOFROMSCWHERESC.CNO='1')

EXCEPT(SELECTSC.SNOFROMSCWHERESC.CNO='2');3.3查詢3.3.1概述3.3.2單表查詢3.3.3連接查詢3.3.4集合查詢3.3.5嵌套查詢3.3.6小結(jié)3.3.5嵌套查詢嵌套查詢概述嵌套查詢分類嵌套查詢求解方法引出子查詢的謂詞

嵌套查詢(續(xù))嵌套查詢概述一個SELECT-FROM-WHERE語句稱為一個查詢塊將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢嵌套查詢(續(xù))例：查詢選修了2號課程的學(xué)生姓名。SELECTSname FROMSWHERESnoINSELECTSnoFROMSCWHERECno='2'內(nèi)層查詢/子查詢外層查詢/父查詢（）;[例41]查詢既選修了課程1又選修了課程2的學(xué)生學(xué)號。(解法二)

SELECTSnoFROMSCWHERECno='1'ANDSnoIN(SELECTSnoFROMSCWHERECno='2')；嵌套查詢分類不相關(guān)子查詢子查詢的查詢條件不依賴于父查詢相關(guān)子查詢子查詢的查詢條件依賴于父查詢嵌套查詢求解方法不相關(guān)子查詢是由里向外逐層處理。即每個子查詢在上一級查詢處理之前求解，子查詢的結(jié)果用于建立其父查詢的查找條件。嵌套查詢求解方法（續(xù)）相關(guān)子查詢首先取外層查詢中表的第一個元組，根據(jù)它與內(nèi)層查詢相關(guān)的屬性值處理內(nèi)層查詢，若WHERE子句返回值為真，則取此元組放入結(jié)果表；然后再取外層表的下一個元組；重復(fù)這一過程，直至外層表全部檢查完為止。引出子查詢的謂詞帶有IN謂詞的子查詢帶有比較運(yùn)算符的子查詢帶有ANY或ALL謂詞的子查詢帶有EXISTS謂詞的子查詢一、帶有IN謂詞的子查詢[例44]查詢與“劉晨”在同一個系學(xué)習(xí)的學(xué)生。

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

SELECTSdeptFROMSWHERESname='劉晨'； 結(jié)果為：SdeptIS帶有IN謂詞的子查詢（續(xù)）②查找所有在IS系學(xué)習(xí)的學(xué)生。

SELECTSno，Sname，SdeptFROMSWHERESdept='IS'；結(jié)果為：SnoSnameSdept95001劉晨IS95004張立IS

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

SELECTSno，Sname，SdeptFROMSWHERESdeptIN(SELECTSdeptFROMSWHERESname=‘

劉晨’)；此查詢?yōu)椴幌嚓P(guān)子查詢。DBMS求解該查詢時也是分步去做的。帶有IN謂詞的子查詢（續(xù)）

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

SELECTS1.Sno，S1.Sname，S1.SdeptFROMSasS1，SasS2WHERES1.Sdept=S2.SdeptAND

S2.Sname='劉晨'；帶有IN謂詞的子查詢（續(xù)）父查詢和子查詢中的表均可以定義別名

SELECTSno，Sname，SdeptFROMSasS1WHERES1.SdeptIN(SELECTSdeptFROMSasS2WHERES2.Sname=‘

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

FROMC

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

WHERECnoINSELECTSno，SnameFROMSWHERESnoIN帶有IN謂詞的子查詢（續(xù)）用連接查詢

SELECTSno，SnameFROMS，SC，CWHERES.Sno=SC.SnoANDSC.Cno=C.CnoANDC.Cname=‘信息系統(tǒng)’；二、帶有比較運(yùn)算符的子查詢

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

：

SELECTSno，Sname，SdeptFROMSWHERESdept=(SELECTSdeptFROMSWHERESname='劉晨')；帶有比較運(yùn)算符的子查詢（續(xù)）

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

錯誤的例子：

SELECTSno，Sname，SdeptFROMSWHERE(SELECTSdeptFROMSWHERESname=‘

劉晨’

)

=Sdept；三、帶有ANY或ALL謂詞的子查詢謂詞語義ANY：任意一個值A(chǔ)LL：所有值帶有ANY或ALL謂詞的子查詢（續(xù)）需要配合使用比較運(yùn)算符>ANY 大于子查詢結(jié)果中的某個值

>ALL 大于子查詢結(jié)果中的所有值<ANY 小于子查詢結(jié)果中的某個值<ALL 小于子查詢結(jié)果中的所有值>=ANY 大于等于子查詢結(jié)果中的某個值>=ALL 大于等于子查詢結(jié)果中的所有值<=ANY 小于等于子查詢結(jié)果中的某個值<=ALL 小于等于子查詢結(jié)果中的所有值=ANY 等于子查詢結(jié)果中的某個值=ALL 等于子查詢結(jié)果中的所有值（通常無實(shí)際意義）!=（或<>）ANY不等于子查詢結(jié)果中的某個值!=（或<>）ALL不等于子查詢結(jié)果中的任何一個值帶有ANY或ALL謂詞的子查詢（續(xù)）[例46]查詢其他系中比信息系任意一個(其中某一個)學(xué)生年齡小的學(xué)生姓名和年齡

SELECTSname，SageFROMSWHERESage<ANY(SELECTSageFROMSWHERESdept='IS')

ANDSdept<>'IS';/*注意這是父查詢塊中的條件*/結(jié)果

Sname Sage

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

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

19或18的學(xué)生帶有ANY或ALL謂詞的子查詢（續(xù)）用集函數(shù)實(shí)現(xiàn)子查詢通常比直接用ANY或ALL查詢效率要高，因?yàn)榍罢咄ǔＤ軌驕p少比較次數(shù)帶有ANY或ALL謂詞的子查詢（續(xù)）[例46']：用集函數(shù)實(shí)現(xiàn)[例46]SELECTSname，SageFROMSWHERESage<(SELECTMAX(Sage)FROMSWHERESdept='IS')ANDSdept<>'IS’;帶有ANY或ALL謂詞的子查詢（續(xù)）[例47]查詢其他系中比信息系所有學(xué)生年齡都小的學(xué)生姓名及年齡。方法一：用ALL謂詞

SELECTSname，SageFROMSWHERESage<ALL(SELECTSageFROMSWHERESdept='IS')ANDSdept<>'IS’;帶有ANY或ALL謂詞的子查詢（續(xù)）

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

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

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

SELECTSnameFROMSWHEREEXISTS/*相關(guān)子查詢*/(SELECT*FROMSCWHERESno=S.SnoANDCno=‘1’)；

思路分析：本查詢涉及S和SC關(guān)系。在S中依次取每個元組的Sno值，用此值去檢查SC關(guān)系。若SC中存在這樣的元組，其Sno值等于此S.Sno值，并且其Cno=‘1’，則取此S.Sname送入結(jié)果關(guān)系。也可以用連接運(yùn)算實(shí)現(xiàn)SELECTSnameFROMS,SCWHERES.Sno=SC.SnoANDSC.Cno='1';[例49]查詢沒有選修1號課程的學(xué)生姓名。

SELECTSnameFROMSWHERENOTEXISTS(SELECT*FROMSCWHERESno=S.SnoANDCno=‘1’)；此例用連接運(yùn)算難于實(shí)現(xiàn)

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

（Forall）思考：查詢選修了全部課程的學(xué)生姓名。思路：構(gòu)建某個學(xué)生所選修的課程集合，記為A；構(gòu)建全部的課程集合，記為B；某個學(xué)生選修了全部課程，可以表示為：

?(B-A)165(SELECTCNO FROMC)

EXCEPT (SELECTDISTINCTCNO FROMSC WHERESC.SNO=XXX)沒有被某個學(xué)生選修的課程號166SELECT SNAMEFROMSWHERENOTEXISTS ((SELECTCNO FROMC)

EXCEPT

(SELECTCNO FROMSC WHERESC.SNO=S.SNO))某個學(xué)生所選的課程所有的課程除法表示方法1167SELECTSNAMEFROMSWHERENOTEXISTS(SELECTCNOFROMCWHERENOTEXISTS (SELECTDISTINCTCNO FROMSCWHERESC.CNO=C.CNO

andSC.SNO=S.SNO))除法表示方法2用NOTEXISTS表示EXCEPT[例50]查詢選修了全部課程的學(xué)生姓名。

SELECTSnameFROMSWHERENOTEXISTS(SELECT*FROMCWHERENOTEXISTS(SELECT*FROMSCWHERESno=S.SnoANDCno=C.Cno);[例51]查詢至少選修了學(xué)生95002選修的全部課程的學(xué)生學(xué)號。

SELECTDISTINCTSnoFROMSCasSCXWHERENOTEXISTS(SELECT*FROMSCasSCYWHERESCY.Sno='95002'ANDNOTEXISTS(SELECT*FROMSCasSCZWHERESCZ.Sno=SCX.SnoANDSCZ.Cno=SCY.Cno))；[52]求被所有的學(xué)生都選修了的課程名SELECT