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

數(shù)據(jù)庫系統(tǒng)概論第3章關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL

熟練正確的使用SQL完成對數(shù)據(jù)庫的查詢、插入、刪除、更新操作。用SQL語言正確完成復(fù)雜查詢，掌握SQL語言強(qiáng)大的查詢功能。

教學(xué)內(nèi)容

牢固掌握SQL，達(dá)到舉一反三的掌握SQL的功能。同時通過實(shí)踐,體會面向過程的語言和SQL的區(qū)別和優(yōu)點(diǎn)。體會關(guān)系數(shù)據(jù)庫系統(tǒng)為數(shù)據(jù)庫應(yīng)用系統(tǒng)的開發(fā)提供良好環(huán)境，減輕用戶負(fù)擔(dān)，提高用戶生產(chǎn)率的原因。

教學(xué)目標(biāo)§3關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL3.1SQL概述3.2學(xué)生-課程數(shù)據(jù)庫3.3數(shù)據(jù)定義3.4數(shù)據(jù)查詢3.5數(shù)據(jù)更新3.6空值的處理3.7視圖3.8小結(jié)§3.1SQL概述SQL語言集數(shù)據(jù)查詢、數(shù)據(jù)操縱、數(shù)據(jù)定義和數(shù)據(jù)控制功能于一體，充分體現(xiàn)了關(guān)系數(shù)據(jù)庫語言的特點(diǎn)和優(yōu)點(diǎn)。其主要特點(diǎn)包括：3.1.2SQL特點(diǎn)1.綜合統(tǒng)一2.高度非過程化3.面向集合的操作方式4.以同一種語法結(jié)構(gòu)提供多種使用方法5.語言簡潔，易學(xué)易用§3.1SQL概述

另外，在關(guān)系模型中，實(shí)體和實(shí)體間的聯(lián)系均用關(guān)系表示，這種數(shù)據(jù)結(jié)構(gòu)的單一性帶來了數(shù)據(jù)操作符的統(tǒng)一性，查找、插入、刪除、更新等每一種操作都只需一種操作符。1.綜合統(tǒng)一SQL語言集數(shù)據(jù)定義語言DDL、數(shù)據(jù)操縱語言DML、數(shù)據(jù)控制語言DCL的功能于一體，語言風(fēng)格統(tǒng)一，可以獨(dú)立完成數(shù)據(jù)庫生命周期中的全部活動?！?.1SQL概述2.高度非過程化

用SQL語言進(jìn)行數(shù)據(jù)庫操作時，用戶只需提出“做什么”，而不必指明“怎么做”。因此，用戶無需了解數(shù)據(jù)存取路徑，存取路徑的選擇以及SQL語句的操作過程由系統(tǒng)自動完成。這不但大大減輕了用戶負(fù)擔(dān)，而且有利于提高數(shù)據(jù)獨(dú)立性?！?.1SQL概述3.面向集合的操作方式

而SQL語言采用集合操作方式，不僅操作對象、查找結(jié)果可以是元組的集合，而且一次插入、刪除、更新操作的對象也可以是元組的集合。

非關(guān)系數(shù)據(jù)模型采用的是面向記錄的操作方式，操作對象是一條記錄。§3.1SQL概述4.以同一種語法結(jié)構(gòu)提供多種使用方法

作為嵌入式語言，SQL語句能夠嵌入到高級語言(例如C，C#，JAVA等)程序中，供程序員設(shè)計程序時使用。

作為自含式語言，它能夠獨(dú)立地用于聯(lián)機(jī)交互的使用方式，用戶可以在終端鍵盤上直接鍵入SQL命令對數(shù)據(jù)庫進(jìn)行操作；§3.1SQL概述5.語言簡潔，易學(xué)易用SQL語言功能極強(qiáng)，由于設(shè)計巧妙，語言十分簡潔，完成核心功能只用了9個動詞，如表所示：表3.2SQL的動詞§3.1SQL概述3.1.3SQL的基本概念SQL對關(guān)系數(shù)據(jù)庫模式的支持

基本表是本身獨(dú)立存在的表；一個或多個基本表對應(yīng)一個存儲文件，一個基本表可以帶若干索引，索引也存放在存儲文件中。

存儲文件的邏輯結(jié)構(gòu)組成了關(guān)系數(shù)據(jù)庫的內(nèi)模式；存儲文件的物理結(jié)構(gòu)是任意的，對用戶是透明的。視圖是從一個或幾個基本表導(dǎo)出的表。視圖本身不獨(dú)立存儲在數(shù)據(jù)庫中，即數(shù)據(jù)庫中只存放視圖的定義而不存放視圖對應(yīng)的數(shù)據(jù)，這些數(shù)據(jù)仍存放在導(dǎo)出視圖的基本表中，因此視圖是一個虛表。視圖在概念上與基本表等同，用戶可以在視圖上再定義視圖?！?.2學(xué)生-課程數(shù)據(jù)庫

定義一個學(xué)生-課程模式S-T，學(xué)生-課程數(shù)據(jù)庫中包含3張表：學(xué)生表：Student（Sno，Sname，Ssex，Sage，Sdept）課程表：Course（Cno，Cname，Cpno，Ccredit）學(xué)生選課表：SC（Sno，Cno，Grade）§3.3數(shù)據(jù)定義SQL語言支持?jǐn)?shù)據(jù)庫三級模式結(jié)構(gòu)內(nèi)模式中的基本對象：外模式中的基本對象：模式中的基本對象：表視圖索引§3.3數(shù)據(jù)定義3.3.1模式的定義與刪除1.定義模式CREATESCHEMA<模式名>AUTHORIZATION<用戶名>SQL語言使用CREATESCHEMA語句創(chuàng)建模式，其一般格式為：

要創(chuàng)建模式，調(diào)用該命令的用戶必須擁有DBA權(quán)限，或者獲得了DBA授予的CREATESCHEMA的權(quán)限?！?.3數(shù)據(jù)定義例3.1為用戶WANG定義一個學(xué)生-課程模式S-TCREATESCHEMA“S-T”AUTHORIZATIONWANG例3.2CREATESCHEMAAUTHORIZATIONWANG§3.3數(shù)據(jù)定義2.刪除模式SQL中刪除模式的語句：DROPSCHEMA<模式名>

<CASCADE|RESTRICT>

例3.4DROPSCHEMAZHANGCASCADE；§3.3數(shù)據(jù)定義3.3.2基本表的定義、刪除與修改1.定義基本表SQL語言使用CREATETABLE語句創(chuàng)建基本表，其一般格式為：CREATETABLE<表名>

（<列名><數(shù)據(jù)類型>[<列級完整性約束條件>][，<列名><數(shù)據(jù)類型>[<列級完整性約束條件>]]…[，<表級完整性約束條件>]）；<表名>：所要定義的基本表的名字<列名>：組成該表的各個屬性（列）<列級完整性約束條件>：涉及相應(yīng)屬性列的完整性約束條件<表級完整性約束條件>：涉及一個或多個屬性列的完整性約束條件§3.3數(shù)據(jù)定義

例題：建立一個職工表emp，它由職工號eno、姓名ename、性別sex、年齡age、部門dept五個屬性組成，其中職工號不能為空且取值惟一。CREATETABLEemp (enoCHAR(5)NOTNULLUNIQUE，

enameCHAR(8)，

sexCHAR(1)，

ageINT，

deptCHAR(16))；

執(zhí)行上面的CREATETABLE語句后，就在數(shù)據(jù)庫中建立了一個新的空的職工表emp，并將有關(guān)職工表的定義及有關(guān)約束條件存放在數(shù)據(jù)字典中。§3.3數(shù)據(jù)定義常用完整性約束主碼約束：PRIMARYKEY

唯一性約束：UNIQUE

非空值約束：NOTNULL

參照完整性約束PRIMARYKEY與

UNIQUE的區(qū)別？§3.3數(shù)據(jù)定義[例3.7]建立一個“學(xué)生選課”表SC，它由學(xué)號Sno、課程號Cno，修課成績Grade組成，其中(Sno,Cno)為主碼。

CREATETABLESC(SnoCHAR(5)，

CnoCHAR(3)，

Gradeint，

Primarykey(Sno,Cno))；表級完整性約束條件的例子：§3.3數(shù)據(jù)定義2.數(shù)據(jù)類型

在SQL中域的概念用數(shù)據(jù)類型來實(shí)現(xiàn)。定義表的各個屬性時需要指明其數(shù)據(jù)類型及長度。注意：不同的RDBMS中支持?jǐn)?shù)據(jù)類型不完全相同。

一個屬性選用哪種數(shù)據(jù)類型要根據(jù)實(shí)際情況來決定，一般要從兩個方面來考慮：一是取值范圍，二是要做哪些運(yùn)算。P83表3.4數(shù)據(jù)類型§3.3數(shù)據(jù)定義3.模式與表

模式與表的對應(yīng)關(guān)系是1：n，定義表時如何處理它所屬的模式存在三種方法：方法一、在表名中明顯地給出模式名；方法二、在創(chuàng)建模式語句中同時創(chuàng)建表；

方法三、設(shè)置所屬的模式，在創(chuàng)建表時表名不必給出模式名；§3.3數(shù)據(jù)定義4.修改基本表SQL語言用ALTERTABLE語句修改基本表，其一般格式為ALTERTABLE<表名>[ADD<新列名><數(shù)據(jù)類型>[完整性約束]][DROP<完整性約束名>][MODIFY<列名><數(shù)據(jù)類型>]；<表名>：要修改的基本表ADD子句：增加新列和新的完整性約束條件DROP子句：刪除指定的完整性約束條件MODIFY子句：用于修改列名和數(shù)據(jù)類型§3.3數(shù)據(jù)定義[例3.8]向Student表增加“入學(xué)時間”列，其數(shù)據(jù)類型為日期型。

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

ALTERTABLEStudentALTERCOLUMNSageSMALLINT；注：修改原有的列定義有可能會破壞已有數(shù)據(jù)[例]刪除學(xué)生姓名必須取唯一值的約束。ALTERTABLEStudentDROPUNIQUE(Sname)；§3.3數(shù)據(jù)定義5.刪除基本表

當(dāng)某個基本表不再需要時，可以使用SQL語句DROPTABLE進(jìn)行刪除，其一般格式為：DROPTABLE<表名>[RESTRICT|CASCADE]；

基本表刪除數(shù)據(jù)、表上的索引都刪除表上的視圖往往仍然保留，但無法引用。刪除基本表時，系統(tǒng)會從數(shù)據(jù)字典中刪去有關(guān)該基本表及其索引的描述?！?.3數(shù)據(jù)定義[例3.11]刪除Student表

DROPTABLE

StudentCASCADE;

基本表一旦被刪除，表中的數(shù)據(jù)和在此表上建立的索引都將自動被刪除掉，而建立在此表上的視圖雖仍然保留，但已無法引用。因此，執(zhí)行刪除基本表操作一定要格外小心。§3.3數(shù)據(jù)定義3.3.3索引的建立與刪除

一般說來，建立與刪除索引由數(shù)據(jù)庫管理員DBA或表的屬主(即建立表的人)負(fù)責(zé)完成。系統(tǒng)在存取數(shù)據(jù)時會自動選擇合適的索引作為存取路徑，用戶不必也不能選擇索引。建立索引是加快表的查詢速度的有效手段?！?.3數(shù)據(jù)定義1.建立索引語句格式CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…)； 用<表名>指定要建索引的基本表名字索引可以建立在該表的一列或多列上，各列名之間用逗號分隔用<次序>指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCUNIQUE表明此索引的每一個索引值只對應(yīng)唯一的數(shù)據(jù)記錄CLUSTER表示要建立的索引是聚簇索引§3.3數(shù)據(jù)定義在一個基本表上最多只能建立一個聚簇索引聚簇索引的用途：對于某些類型的查詢，可以提高查詢效率聚簇索引的適用范圍很少對基表進(jìn)行增刪操作很少對其中的變長列進(jìn)行修改操作[例3.13]為學(xué)生-課程數(shù)據(jù)庫中的Student，Course，SC三個表建立索引。其中Student表按學(xué)號升序建唯一索引，Course表按課程號升序建唯一索引，SC表按學(xué)號升序和課程號降序建唯一索引。

CREATEUNIQUEINDEXStusnoONStudent(Sno)；CREATEUNIQUEINDEXCoucnoONCourse(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；§3.3數(shù)據(jù)定義2.修改索引

在SQL語言中，修改索引使用ALTERINDEX語句，其一般格式為：ALTERINDEX<舊索引名>RENAMETO<新索引名>；[例3.14]將SC表的SCno索引名改為SCSno。

ALTERINDEXSCnoRENAMETOSCSno；

§3.3數(shù)據(jù)定義3.刪除索引

建立索引是為了減少查詢操作的時間，但如果數(shù)據(jù)增、刪、改頻繁，系統(tǒng)會花費(fèi)許多時間來維護(hù)索引。這時，可以刪除一些不必要的索引。在SQL語言中，刪除索引使用DROPINDEX語句，其一般格式為：

DROPINDEX<索引名>[例3.15]刪除Student表上的Stusname索引。

DROPINDEXStudent；刪除索引時，系統(tǒng)會同時從數(shù)據(jù)字典中刪去有關(guān)該索引的描述?！?.3數(shù)據(jù)定義3.3.4數(shù)據(jù)字典

數(shù)據(jù)字典最重要的作用是作為分析階段的工具。在結(jié)構(gòu)化分析中，數(shù)據(jù)字典的作用是給數(shù)據(jù)流圖上每個成分加以定義和說明，數(shù)據(jù)字典中嚴(yán)密一致的定義有助于改進(jìn)分析員和用戶的通信。

在查詢優(yōu)化和查詢處理時，數(shù)據(jù)字典中的信息是重要的依據(jù)。§3.4數(shù)據(jù)查詢語句格式：SELECT[ALL|DISTINCT]<目標(biāo)列表達(dá)式>[，<目標(biāo)列表達(dá)式>]…FROM<表名或視圖名>[，<表名或視圖名>]…[WHERE<條件表達(dá)式>][GROUPBY<列名1>[HAVING<條件表達(dá)式>]][ORDERBY<列名2>[ASC|DESC]]；

§3.4數(shù)據(jù)查詢SELECT子句：指定要顯示的屬性列FROM子句：指定查詢對象(基本表或視圖)WHERE子句：指定查詢條件GROUPBY子句：對查詢結(jié)果按指定列的值分組，該屬性列值相等的元組為一個組。通常會在每組中作用集函數(shù)。HAVING短語：篩選出只有滿足指定條件的組ORDERBY子句：對查詢結(jié)果表按指定列值的升序或降序排序§3.4數(shù)據(jù)查詢3.4.1單表查詢3.4.2連接查詢3.4.3嵌套查詢3.4.4集合查詢3.4.5基于派生表的查詢3.4.6SELECT語句的一般格式§3.4數(shù)據(jù)查詢3.4.1單表查詢查詢僅涉及一個表，是一種最簡單的查詢操作一、選擇表中的若干列二、選擇表中的若干元組三、對查詢結(jié)果排序四、使用集函數(shù)五、對查詢結(jié)果分組§3.4數(shù)據(jù)查詢一、選擇表中的若干列1、查詢指定列2、查詢?nèi)苛?、查詢經(jīng)過計算的值§3.4數(shù)據(jù)查詢1、查詢指定列[例3.16]查詢?nèi)w學(xué)生的學(xué)號與姓名。SELECTSno，SnameFROMStudent；

§3.4數(shù)據(jù)查詢2、查詢?nèi)苛衃例3.18]查詢?nèi)w學(xué)生的詳細(xì)記錄。SELECTSno，Sname，Ssex，Sage，SdeptFROMStudent；//方法1SELECT*FROMStudent；//方法2§3.4數(shù)據(jù)查詢3、查詢經(jīng)過計算的值SELECT子句的<目標(biāo)列表達(dá)式>為下列表達(dá)式：算術(shù)表達(dá)式字符串常量函數(shù)列別名其他§3.4數(shù)據(jù)查詢[例3.20]查詢?nèi)w學(xué)生的姓名、出生年份和所有系，要求用小寫字母表示所有系名。

SELECTSname，'YearofBirth:'，2000-SageLOWER(Sdept)FROMStudent；

輸出結(jié)果：

Sname'YearofBirth:'2000-SageLOWER(Sdept)----------------------------------------------

李勇YearofBirth:1976cs

劉晨YearofBirth:1977is

王名YearofBirth:1978ma

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

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

結(jié)果：Sno-------95001950019500195002950021.消除取值重復(fù)的行§3.4數(shù)據(jù)查詢SELECTDISTINCTSnoFROMSC;

結(jié)果：

Sno-------9500195002SELECTSnoFROMSC;

等價于(默認(rèn)ALL)SELECTALLSnoFROMSC;§3.4數(shù)據(jù)查詢注意DISTINCT短語的作用范圍是所有目標(biāo)列例：查詢選修課程的各種成績錯誤的寫法SELECTDISTINCTCno，DISTINCTGradeFROMSC;正確的寫法SELECTDISTINCTCno，GradeFROMSC;

§3.4數(shù)據(jù)查詢2.查詢滿足條件的元組重點(diǎn)內(nèi)容(1)比較大小(2)確定范圍(3)確定集合(4)字符串匹配(5)涉及空值的查詢(6)多重條件查詢§3.4數(shù)據(jù)查詢(1)比較大小在WHERE子句的<比較條件>中使用比較運(yùn)算符=，>，<，>=，<=，!=或<>，!>，!<，邏輯運(yùn)算符NOT+比較運(yùn)算符[例3.23]查詢所有年齡在20歲以下的學(xué)生姓名及其年齡。

SELECTSname，SageFROMStudentWHERESage<20；或SELECTSname，SageFROMStudentWHERENOTSage>=20；§3.4數(shù)據(jù)查詢使用謂詞BETWEEN…AND…NOTBETWEEN…AND…[例3.25]查詢年齡在20~23歲（包括20歲和23歲）之間的學(xué)生的姓名、系別和年齡。

SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；

(2)確定范圍§3.4數(shù)據(jù)查詢使用謂詞IN<值表>,NOTIN<值表>

<值表>：用逗號分隔的一組取值[例3.27]查詢信息系（IS）、數(shù)學(xué)系（MA）和計算機(jī)科學(xué)系（CS）學(xué)生的姓名和性別。SELECTSname，SsexFROMStudentWHERESdeptIN('IS'，'MA'，'CS');(3)確定集合DatabaseSystemSummary§3.4數(shù)據(jù)查詢[NOT]LIKE‘<匹配串>’[ESCAPE‘<換碼字符>’]<匹配串>：指定匹配模板匹配模板：固定字符串或含通配符的字符串當(dāng)匹配模板為固定字符串時，可以用=運(yùn)算符取代LIKE謂詞用!=或<>運(yùn)算符取代NOTLIKE謂詞(4)字符串匹配§3.4數(shù)據(jù)查詢%(百分號)代表任意長度（長度可以為0）的字符串例：a%b表示以a開頭，以b結(jié)尾的任意長度的字符串。如acb，addgb，ab等都滿足該匹配串_(下橫線)代表任意單個字符例：a_b表示以a開頭，以b結(jié)尾的長度為3的任意字符串。如acb，afb等都滿足該匹配串ESCAPE短語：當(dāng)用戶要查詢的字符串本身就含有%或_時，要使用ESCAPE'<換碼字符>'短語對通配符進(jìn)行轉(zhuǎn)義。§3.4數(shù)據(jù)查詢1)匹配模板為固定字符串[例]查詢學(xué)號為95001的學(xué)生的詳細(xì)情況。SELECT*FROMStudentWHERESnoLIKE'95001'；等價于：SELECT*FROMStudentWHERESno='95001'；§3.4數(shù)據(jù)查詢2)匹配模板為含通配符的字符串[例3.30]查詢所有姓劉學(xué)生的姓名、學(xué)號和性別。

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

SELECTSnameFROMStudentWHERESnameLIKE'歐陽__'；§3.4數(shù)據(jù)查詢3)使用換碼字符將通配符轉(zhuǎn)義為普通字符

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

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

SELECT*FROMCourseWHERECnameLIKE'DB\_%i__'ESCAPE'\'；§3.4數(shù)據(jù)查詢

使用謂詞ISNULL或ISNOTNULL“ISNULL”不能用“=NULL”代替[例3.36]某些學(xué)生選修課程后沒有參加考試，所以有選課記錄，但沒有考試成績。查詢?nèi)鄙俪煽兊膶W(xué)生的學(xué)號和相應(yīng)的課程號。

SELECTSno，CnoFROMSCWHEREGradeISNULL；(5)涉及空值的查詢§3.4數(shù)據(jù)查詢用邏輯運(yùn)算符AND和OR來聯(lián)結(jié)多個查詢條件

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

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

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

SELECTSnameFROMStudentWHERESdept='CS'ANDSage<20；§3.4數(shù)據(jù)查詢

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

SELECTSno，GradeFROMSCWHERECno='3'ORDERBYGradeDESC；查詢結(jié)果SnoGrade--------------9501095024950079295003829501082950097595014619500265§3.4數(shù)據(jù)查詢四、使用聚集函數(shù)5類主要集函數(shù)計數(shù)COUNT（[DISTINCT|ALL]*）COUNT（[DISTINCT|ALL]<列名>）計算總和SUM（[DISTINCT|ALL]<列名>） 計算平均值A(chǔ)VG（[DISTINCT|ALL]<列名>）§3.4數(shù)據(jù)查詢求最大值MAX（[DISTINCT|ALL]<列名>）

求最小值MIN（[DISTINCT|ALL]<列名>） DISTINCT短語：在計算時要取消指

定列中的重復(fù)值 ALL短語：不取消重復(fù)值 ALL為缺省值§3.4數(shù)據(jù)查詢[例3.41]查詢學(xué)生總?cè)藬?shù)。

SELECTCOUNT(*)FROMStudent；

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

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

SELECTAVG(Grade)FROMSCWHERECno='1'；

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

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

SELECTCno，COUNT(Sno)FROMSCGROUPBYCno；

結(jié)果

CnoCOUNT(Sno) 122 234 344 433 548§3.4數(shù)據(jù)查詢GROUPBY子句的作用對象是查詢的中間結(jié)果表分組方法：按指定的一列或多列值分組，值相等的為一組使用GROUPBY子句后，SELECT子句的列名列表中只能出現(xiàn)分組屬性和集函數(shù)GROUPBY子句的注意事項(xiàng)：§3.4數(shù)據(jù)查詢[例]查詢有3門以上課程是90分以上的學(xué)生的學(xué)號及（90分以上的）課程數(shù)

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

使用HAVING短語篩選最終輸出結(jié)果§3.4數(shù)據(jù)查詢只有滿足HAVING短語指定條件的組才輸出HAVING短語與WHERE子句的區(qū)別：作用對象不同WHERE子句作用于基表或視圖，從中選擇滿足條件的元組。HAVING短語作用于組，從中選擇滿足條件的組。

Having短語的注意事項(xiàng)：§3.4數(shù)據(jù)查詢3.4.2連接查詢同時涉及多個表的查詢稱為連接查詢用來連接兩個表的條件稱為連接條件或連接謂詞一般格式：[<表名1>.]<列名1><比較運(yùn)算符>[<表名2>.]<列名2>

比較運(yùn)算符：=、>、<、>=、<=、!=[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>§3.4數(shù)據(jù)查詢連接字段：連接謂詞中的列名稱為連接字段。連接條件中的各連接字段類型必須是可比的，但不必是相同的。§3.4數(shù)據(jù)查詢SQL中連接查詢的主要類型：一、等值連接(含自然連接)與非等值連接查詢二、自身連接查詢?nèi)?、外連接查詢四、多表連接五、廣義笛卡爾積§3.4數(shù)據(jù)查詢一、等值連接(含自然連接)與非等值查詢連接運(yùn)算符為=的連接操作。[<表名1>.]<列名1>=[<表名2>.]<列名2>任何子句中引用表1和表2中同名屬性時，都必須加表名前綴。引用唯一屬性名時可以加也可以省略表名前綴。[例3.49]查詢每個學(xué)生及其選修課程的情況。SELECTStudent.*，SC.*FROMStudent，SCWHEREStudent.Sno=SC.Sno；§3.4數(shù)據(jù)查詢假設(shè)Student表、SC表分別有下列數(shù)據(jù)：SnoSnameSsexSageSdept95001

李勇

男20CS95002

劉晨

女19IS95003

王敏

女18MA95004

張立

男19ISSC表：

SnoCnoGrade9500119295001285950019500295002323889080Student表：§3.4數(shù)據(jù)查詢結(jié)果表

Student.SnoSnameSsexSageSdeptSC.SnoCnoGrade

95001李勇男20 CS 9500119295001李勇男20 CS 9500128595001李勇男20 CS 9500138895002劉晨女19 IS 9500229095002劉晨女19 IS 95002380

這張表有什么問題？§3.4數(shù)據(jù)查詢自然連接等值連接的一種特殊情況，把目標(biāo)列中重復(fù)的屬性列去掉。[例3.50]對[例3.49]用自然連接完成。

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

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

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

SELECTFIRST.Cno，SECOND.CpnoFROMCourseFIRST，CourseSECONDWHEREFIRST.Cpno=SECOND.Cno；查詢結(jié)果

173556

cnocpno

§3.4數(shù)據(jù)查詢?nèi)?、外連接查詢外連接與普通連接的區(qū)別：普通連接操作只輸出滿足連接條件的元組外連接操作以指定表為連接主體，將主體表中不滿足連接條件的元組一并輸出§3.4數(shù)據(jù)查詢[例3.53]查詢每個學(xué)生及其選修課程的情況包括沒有選修課程的學(xué)生。

SELECTStudent.Sno，Sname，Ssex， Sage，Sdept，Cno，GradeFROMStudent

LEFTOUTERJOINSC ON(Student.Sno=SC.Sno)；結(jié)果：

Student.SnoSnameSsexSageSdeptCnoGrade95001李勇男20CS19295001李勇男20CS28595002劉晨女19IS38095003王敏女18MANULLNULL95004張立男19ISNULLNULL§3.4數(shù)據(jù)查詢四、多表連接兩個以上的表進(jìn)行連接，稱為多表連接。

[例3.54]查詢學(xué)生的學(xué)號、姓名、選修的課程及成績。

SELECTStudent.Sno,Sname,Cname,GradeFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.Cno§3.4數(shù)據(jù)查詢復(fù)合條件連接WHERE子句中含多個連接條件時，稱為復(fù)合條件連接

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

SELECTStudent.Sno,student.SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoAND/*連接謂詞*/SC.Cno='2'AND/*其他限定條件*/SC.Grade>90；/*其他限定條件*/§3.4數(shù)據(jù)查詢五、廣義笛卡爾積不帶連接謂詞的連接很少使用例：

SELECTStudent.*,SC.*FROMStudent,SC§3.4數(shù)據(jù)查詢3.4.3嵌套查詢嵌套查詢概述一個SELECT-FROM-WHERE語句稱為一個查詢塊。將一個查詢塊嵌套在另一個查詢塊的WHERE子句或

HAVING短語的條件中的查詢稱為嵌套查詢。SELECTSname 外層查詢/父查詢

FROMStudentWHERESnoIN

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

FROMSCWHERECno='2'）；§3.4數(shù)據(jù)查詢子查詢的限制

不能使用ORDERBY子句層層嵌套方式反映了SQL語言的結(jié)構(gòu)化有些嵌套查詢可以用連接運(yùn)算替代嵌套查詢分類不相關(guān)子查詢子查詢的查詢條件不依賴于父查詢相關(guān)子查詢子查詢的查詢條件依賴于父查詢§3.4數(shù)據(jù)查詢一、帶有IN謂詞的子查詢二、帶有比較運(yùn)算符的子查詢?nèi)?、帶有ANY或ALL謂詞的子查詢四、帶有EXISTS謂詞的子查詢嵌套查詢的分類：§3.4數(shù)據(jù)查詢一、帶有IN謂詞的子查詢[例3.55]查詢與“劉晨”在同一個系學(xué)習(xí)的學(xué)生。此查詢要求可以分步來完成①確定“劉晨”所在系名

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

Sdept IS§3.4數(shù)據(jù)查詢②查找所有在IS系學(xué)習(xí)的學(xué)生。

SELECTSno，Sname，SdeptFROMStudentWHERESdept='IS'；結(jié)果為：SnoSnameSdept95001劉晨IS95004張立IS§3.4數(shù)據(jù)查詢將第一步查詢嵌入到第二步查詢的條件中。

SELECTSno，Sname，SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname=‘劉晨’)；此查詢?yōu)椴幌嚓P(guān)子查詢。DBMS求解該查詢時也是分步去做的。§3.4數(shù)據(jù)查詢[例3.56]查詢選修了課程名為“信息系統(tǒng)”的學(xué)生學(xué)號和姓名。

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

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

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

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

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

WHERECname=‘信息系統(tǒng)’));§3.4數(shù)據(jù)查詢結(jié)果：

Sno Sname---------95001李勇

95002劉晨本例用連接查詢實(shí)現(xiàn)：

SELECTSno，SnameFROMStudent，SC，CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.Cname=‘信息系統(tǒng)’；§3.4數(shù)據(jù)查詢

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

例：假設(shè)一個學(xué)生只可能在一個系學(xué)習(xí)，并且必須屬于一個系，則在[例3.55]可以用=代替IN：

SELECTSno，Sname，SdeptFROMStudentWHERESdept=SELECTSdeptFROMStudentWHERESname='劉晨'；§3.4數(shù)據(jù)查詢

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

錯誤的例子：

SELECTSno，Sname，SdeptFROMStudentWHERE(SELECTSdeptFROMStudentWHERESname=‘劉晨’)=Sdept；§3.4數(shù)據(jù)查詢?nèi)в蠥NY或ALL謂詞的子查詢謂詞語義ANY：任意一個值（類似邏輯算符

）ALL：所有值（類似邏輯算符

）§3.4數(shù)據(jù)查詢[例]查詢其他系中比信息系任意一個(其中某一個)學(xué)生年齡小的學(xué)生姓名和年齡。

SELECTSname，SageFROMStudentWHERESage<ANY(SELECTSageFROMStudentWHERESdept='IS')ANDSdept<>'IS';/*注意這是父查詢塊中的條件*/§3.4數(shù)據(jù)查詢四、帶有EXISTS謂詞的子查詢1.EXISTS謂詞2.NOTEXISTS謂詞3.不同形式的查詢間的替換4.相關(guān)子查詢的效率5.用EXISTS/NOTEXISTS實(shí)現(xiàn)全稱量詞6.用EXISTS/NOTEXISTS實(shí)現(xiàn)邏輯蘊(yùn)函§3.4數(shù)據(jù)查詢標(biāo)準(zhǔn)SQL直接支持的集合操作種類并操作(UNION)一般商用數(shù)據(jù)庫支持的集合操作種類并操作(UNION)交操作(INTERSECT)差操作(MINUS)3.4.4集合查詢§3.4數(shù)據(jù)查詢1．并操作形式

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

SELECT*FROMStudentWHERESdept='CS'UNIONSELECT*FROMStudentWHERESage<=19；§3.4數(shù)據(jù)查詢2．交操作

標(biāo)準(zhǔn)SQL中沒有提供集合交操作，但可用其他方法間接實(shí)現(xiàn)。[例3.66]查詢計算機(jī)科學(xué)系的學(xué)生與年齡不大于19歲的學(xué)生的交集。SELECT*FROMStudentWHERESdept=‘cs’INTERSECTSELECT*FROMStudentWHERESage<=19;§3.4數(shù)據(jù)查詢3．差操作

標(biāo)準(zhǔn)SQL中沒有提供集合差操作，但可用其他方法間接實(shí)現(xiàn)。[例3.68]查詢計算機(jī)科學(xué)系的學(xué)生與年齡不大于19歲的學(xué)生的差集。SELECT*FROMStudentWHERESdept=‘cs’EXCEPTSELECT*FROMStudentWHERESage<=19;§3.4數(shù)據(jù)查詢3.3.5基于派生表的查詢

子查詢出現(xiàn)在FROM子句中，子查詢生成臨時的派生表，作為主查詢的查詢對象，派生表必須指定一個別名。

例3.57查詢每個學(xué)生超過自己選修課程平均成績的課程。 SelectSno，Cno FromSC,(SelectSno,Avg(Grade)FromSCGroupbySno)AsAvg_sc(avg_sno,avg_grade)WhereSC.Sno=Avg_sc.avg_snoand SC.Grade>=Avg.sc.avg_grade§3.4數(shù)據(jù)查詢3.3.6SELECT語句的一般格式SELECT[ALL|DISTINCT]<目標(biāo)列表達(dá)式>[別名][，<目標(biāo)列表達(dá)式>[別名]]…FROM<表名或視圖名>[別名][，<表名或視圖名>[別名]]…[WHERE<條件表達(dá)式>][GROUPBY<列名1>[，<列名1’>]...[HAVING<條件表達(dá)式>]][ORDERBY<列名2>[ASC|DESC][，<列名2>[ASC|DESC]]…]；§3.5數(shù)據(jù)更新3.5.1插入數(shù)據(jù)3.5.2修改數(shù)據(jù)3.5.3刪除數(shù)據(jù)§3.5數(shù)據(jù)更新3.5.1插入數(shù)據(jù)兩種插入數(shù)據(jù)方式：一、插入單個元組二、插入子查詢結(jié)果§3.5數(shù)據(jù)更新一、插入單個元組語句格式INSERTINTO<表名>[(<屬性列1>[，<屬性列2>…)]VALUES(<常量1>[，<常量2>]…)功能將新元組插入指定表中。§3.5數(shù)據(jù)更新子句指定要插入數(shù)據(jù)的表名及屬性列屬性列的順序可與表定義中的順序不一致沒有指定屬性列：表示要插入的是一條完整的元組，且屬性列屬性與表定義中的順序一致指定部分屬性列：插入的元組在其余屬性列上取空值

VALUES子句提供的值必須與INTO子句匹配值的個數(shù)值的類型§3.5數(shù)據(jù)更新[例3.69]將一個新學(xué)生記錄（學(xué)號：95020；姓名：陳冬；性別：男；所在系：IS；年齡：18歲）

插入到Student表中。

INSERTINTOStudentVALUES(‘95020’,‘陳冬’,‘男’,‘IS’,

18)；§3.5數(shù)據(jù)更新二、插入子查詢結(jié)果語句格式：

INSERTINTO<表名>[(<屬性列1>[，<屬性列2>…)]

子查詢；功能：將子查詢結(jié)果插入指定表中§3.5數(shù)據(jù)更新INTO子句(與插入單條元組類似)指定要插入數(shù)據(jù)的表名及屬性列屬性列的順序可與表定義中的順序不一致沒有指定屬性列：表示要插入的是一條完整的元組指定部分屬性列：插入的元組在其余屬性列上取空值子查詢SELECT子句目標(biāo)列必須與INTO子句匹配值的個數(shù)值的類型§3.5數(shù)據(jù)更新[例3.72]對每一個系，求學(xué)生的平均年齡，并把

結(jié)果存入數(shù)據(jù)庫。第一步：建表

CREATETABLEDept_age(SdeptCHAR(15)/*系名*/Avg_ageSMALLINT)；/*學(xué)生平均年齡*/

§3.5數(shù)據(jù)更新第二步：插入數(shù)據(jù)

INSERTINTODept_age(Sdept，Avg_age)SELECTSdept，AVG(Sage)FROMStudentGROUPBYSdept；§3.5數(shù)據(jù)更新3.5.2修改數(shù)據(jù)語句格式

UPDATE<表名>SET<列名>=<表達(dá)式>[，<列名>=<表達(dá)式>]…[WHERE<條件>]；功能

修改指定表中滿足WHERE子句條件的元組§3.5數(shù)據(jù)更新SET子句指定修改方式要修改的列修改后取值WHERE子句指定要修改的元組缺省表示要修改表中的所有元組§3.5數(shù)據(jù)更新三種修改方式一、修改某一個元組的值二、修改多個元組的值三、帶子查詢的修改語句§3.5數(shù)據(jù)更新一、修改某一個元組的值[例3.73]將學(xué)生95001的年齡改為22歲。

UPDATEStudentSETSage=22WHERESno='95001'；§3.5數(shù)據(jù)更新二、修改多個元組的值[例3.74]將所有學(xué)生的年齡增加1歲。

UPDATEStudentSETSage=Sage+1；§3.5數(shù)據(jù)更新[例3.75]將計算機(jī)科學(xué)系全體學(xué)生的成績置零。

UPDATESCSETGrade=0WHERE'CS'=(SELETESdeptFROMStudentWHEREStudent.Sno=SC.Sno)；三、帶子查詢的修改語句§3.5數(shù)據(jù)更新3.4.3刪除數(shù)據(jù)

DELETEFROM<表名>[WHERE<條件>]；功能刪除指定表中滿足WHERE子句條件的元組WHERE子句指定要刪除的元組缺省表示要修改表中的所有元組§3.5數(shù)據(jù)更新三種刪除方式：

1.刪除某一個元組的值

2.刪除多個元組的值

3.帶子查詢的刪除語句§3.5數(shù)據(jù)更新1.刪除某一個元組的值[例]刪除學(xué)號為95019的學(xué)生記錄。

DELETEFROMStudentWHERESno='95019'；§3.5數(shù)據(jù)更新2.刪除多個元組的值[例]刪除2號課程的所有選課記錄。

DELETEFROMSC；

WHERECno='2'；§3.5數(shù)據(jù)更新3.帶子查詢的刪除語句[例3.78]刪除計算機(jī)科學(xué)系所有學(xué)生的選課記錄。

DELETEFROMSCWHERE'CS'=(SELETESdeptFROMStudentWHEREStudent.Sno=SC.Sno)；§3.6空值的處理插入數(shù)據(jù)時未賦值InsertintoSC(Sno,Cno,Grade)values(‘98012’,’1’,NULL)明確定義空值 UpdateStudent SetSdept=NULL WhereSno=‘98012’外連接1.空值的產(chǎn)生§3.6空值的處理ISNULL或者ISNOTNULL2.空值的判斷[例3.81]從Student表中找出漏填數(shù)據(jù)的學(xué)生信息。

Select*FromStudentWhereSnameISNULLORSsexISNULLORSageISNULLORSdeptISNULL§3.6空值的處理用戶自定義完整性指定的非空約束NOTNULLUNIQUE約束

相應(yīng)屬性不能為空實(shí)體完整性約束主碼不能為空3.空值的約束§3.6空值的處理NULL與其他值的算術(shù)結(jié)果為NULLNULL與其他值的比較結(jié)果為NULLNULL的邏輯運(yùn)算：4.空值的運(yùn)算NULL&true=⊥NULL|true=trueNULL&false=falseNULL|false=⊥NULL&NULL=⊥NULL|NULL=⊥NOTNULL=⊥§3.6空值的處理[例3.83]選出選修1號課程的不及格以及缺考的學(xué)生。SelectSnoFromSCWhereCno=1and(Grade<60ORGradeisnull);§3.7視圖虛表，是從一個或幾個基本表（或視圖）導(dǎo)出的表。只存放視圖的定義，不會出現(xiàn)數(shù)據(jù)冗余?；碇械臄?shù)據(jù)發(fā)生變化，從視圖中查詢出的數(shù)據(jù)也隨之改變。視圖的特點(diǎn)：§3.7視圖

查詢刪除更新定義基于該視圖的新視圖基于視圖的操作：§3.7視圖3.7.1定義視圖3.7.2查詢視圖3.7.3更新視圖3.7.4視圖的作用§3.7視圖3.7.1定義視圖一、建立視圖二、刪除視圖§3.7視圖一、建立視圖語句格式

CREATEVIEW<視圖名>[(<列名>[，<列名>]…)]AS<子查詢>[WITHCHECKOPTION]；DBMS執(zhí)行CREATEVIEW語句時只是把視圖的定義存入數(shù)據(jù)字典，并不執(zhí)行其中的SELECT語句。在對視圖查詢時，按視圖的定義從基本表中將數(shù)據(jù)查出。§3.7視圖常見的視圖形式★

行列子集視圖★

WITHCHECKOPTION的視圖★

基于多個基表的視圖★

基于視圖的視圖★

帶表達(dá)式的視圖★

分組視圖§3.7視圖行列子集視圖定義：

[例3.84]建立信息系學(xué)生的視圖。

CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept='IS'；從單個基本表導(dǎo)出只是去掉了基本表的某些行和某些列保留了主碼§3.7視圖WITHCHECKOPTION視圖透過視圖進(jìn)行增刪改操作時，不得破壞視圖定義中的謂詞條件（即子查詢中的條件表達(dá)式）?！?.7視圖[例3.85]建立信息系學(xué)生的視圖，并要求透過該視圖進(jìn)行的更新操作只涉及信息系學(xué)生。

CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept='IS'WITHCHECKOPTION；§3.7視圖對IS_Student視圖的更新操作修改操作：DBMS自動加上Sdept='IS'的條件刪除操作：DBMS自動加上Sdept='IS'的條件插入操作：DBMS自動檢查Sdept屬性值是否為'IS'如果不是，則拒絕該插入操作。如果沒有提供Sdept屬性值，則自動定義Sdept為‘IS’?！?.7視圖基于多個基表的視圖[例3.86]建立信息系選修了1號課程的學(xué)生視圖。

CREATEVIEWIS_S1(Sno，Sname，Grade)ASSELECTStudent.Sno，Sname，GradeFROMStudent，SCWHERESdept='IS'ANDStudent.Sno=SC.SnoANDSC.Cno='1'；§3.7視圖基于視圖的視圖

[例3.87]建立信息系選修了1號課程且成績在90分以上的學(xué)生的視圖。

CREATEVIEWIS_S2ASSELECTSno，Sname，GradeFROMIS_S1WHEREGrade>=90；§3.7視圖帶表達(dá)式的視圖[例3.88]定義一個反映學(xué)生出生年份的視圖。

CREATEVIEWBT_S(Sno，Sname，Sbirth)ASSELECTSno，Sname，2000-SageFROMStudent★設(shè)置一些派生屬性列,也稱為虛擬列--Sbirth?！飵П磉_(dá)式的視圖必須明確定義組成視圖的各個屬性列名。§3.7視圖

分組視圖[例3.89]將學(xué)生的學(xué)號及他的平均成績定義為一個視圖假設(shè)SC表中“成績”列Grade為數(shù)字型。

CREA