第三章關(guān)系數(shù)據(jù)庫標準語言SQL

第三章關(guān)系數(shù)據(jù)庫標準語言SQL§3.1SQL概述§3.2學生－課程數(shù)據(jù)庫§3.3數(shù)據(jù)定義§3.4查詢§3.5數(shù)據(jù)更新§3.6視圖

SQL（StructuredQueryLanguage）語言來源于20世紀70年代IBM的一個被稱為SEQUEL(StructuredEnglishQueryLanguage)的研究項目。20世紀80年代，SQL由ANSI進行了標準化，它包括了定義和操作數(shù)據(jù)的指令。由于它具有功能豐富、使用方式靈活、語言簡潔易學等突出特點，在計算機界深受廣大用戶歡迎，許多數(shù)據(jù)庫生產(chǎn)廠家都相繼推出各自支持SQL標準。1998年4月，ISO提出了具有完整性特征的SQL，并將其定為國際標準，推薦它為標準關(guān)系數(shù)據(jù)庫語言。1990年，我國也頒布了《信息處理系統(tǒng)數(shù)據(jù)庫語言SQL》，將其定為中國國家標準?！?.1SQL概述§3.1SQL概述SQL的特點1.綜合統(tǒng)一SQL集查詢、DDL、DML、DCL(數(shù)據(jù)控制語言)為一體。用SQL語言可實現(xiàn)DB生命周期的全部活動。2.高度非過程化SQL語言是非過程化語言（第四代語言）。3.面向集合的操作方式4.以同一種語法結(jié)構(gòu)提供兩種使用方法(自含式語言，嵌入式語言)聯(lián)機交互使用方式；嵌入某種高級程序設(shè)計語言。5.語言簡捷，易學易用SQL支持關(guān)系數(shù)據(jù)庫三級模式基本表：本身獨立存在的表。一個關(guān)系就對應一個基本表。（模式）存儲文件：一個（或多個）基本表對應一個存儲文件，一個表可帶若干索引。索引也存放在存儲文件中。存儲文件的邏輯結(jié)構(gòu)組成了關(guān)系數(shù)據(jù)庫的內(nèi)模式。存儲文件的物理結(jié)構(gòu)是任意的，對用戶是透明的。（內(nèi)模式）視圖：從一個或幾個基本表導出的表。是一個虛表，數(shù)據(jù)庫中僅存視圖定義，不存放視圖對應的數(shù)據(jù)。并且用戶可以在視圖上再定義視圖。（外模式）用戶外模式模式內(nèi)模式Storefile1Storefile2Basetable1Basetable2Basetable3Basetable4View1View2SQLSQL對關(guān)系數(shù)據(jù)庫模式的支持SQL命令的種類SQL語句可以在命令窗口中執(zhí)行，也可以作為查詢或視圖(的內(nèi)容)被使用，還可以在程序文件被執(zhí)行SQL命令動詞——————————————————————SQL功能命令動詞——————————————————————數(shù)據(jù)查詢SELECT數(shù)據(jù)定義CREATE、DROP、ALTER數(shù)據(jù)操作INSERT、UPDATE、DELETE數(shù)據(jù)控制GRANT、REVOKE——————————————————————§3.2學生－課程數(shù)據(jù)庫CSCSMAIS20191819男女女男李勇劉晨王敏張立200215121200215122200215123200215125所在系Sdept年齡Sage性別Ssex姓名Sname學號SnoStudent424342451676數(shù)據(jù)庫數(shù)學信息系統(tǒng)操作系統(tǒng)數(shù)據(jù)結(jié)構(gòu)數(shù)據(jù)處理

PASCAL語言1234567學分Ccredit先行課Cpno課程名Cname課程號CnoCourse928588908012323200215121200215121200215121200215122200215122成績Grade課程號Cno學號SnoSC§3.3數(shù)據(jù)定義DROPINDEXCREATEINDEX索引DROPVIEWCREATEVIEW視圖ALTERTABLEDROPTABLECREATETABLE表DROPSCHEMACREATESCHEMA模式修改刪除創(chuàng)建操作方式操作對象表3.2SQL的數(shù)據(jù)定義語句§3.2.1模式的定義與刪除（1）定義模式CREATESCHEMA<模式名>AUTHORIZATION<用戶名>如果沒有指定<模式名>，那么<模式名>隱含為<用戶名>要創(chuàng)建模式，調(diào)用該命令的應用必須擁有DBA權(quán)限，或者獲得了DBA授予的創(chuàng)建模式的權(quán)限[例1]

定義一個學生－課程模式S-TCREATESCHEMA“S-T”AUTHORIZATIONWANG;為用戶WANG定義了一個模式S-T[例2]

CREATESCHEMAAUTHORIZATIONWANG;該語句沒有指定<模式名>，所以<模式名>隱含為用戶名WANG說明：定義模式實際上定義了一個命名空間，在這個空間中可以進一步定義該模式包含的數(shù)據(jù)庫對象，如基本表、視圖、索引等

CREATESCHEMA<模式名>AUTHORIZATION<用戶名>[<表定義子句>|<視圖定義子句>|<授權(quán)定義子句>][例3]

CREATESCHEMATESTAUTHORIZATIONZHANGCREATETABLETAB1(COL1SMALLINT,COL2INT,COL3CHAR(20),COL4NUMERIC(10,3),COL5DECIMAL(5,2));該語句為用戶ZHANG創(chuàng)建了一個模式TEST，并且在其中定義了一個表TAB1(2)刪除模式

DROPSCHEMA<模式名><CASCADE|RESTRICT>其中CASCADE和RESTRICT，兩者必選其一CASCADE（級聯(lián)），表示在刪除模式的同時把該模式中所有的數(shù)據(jù)庫對象全部一起刪除RESTRICT（限制），表示如果該模式中已經(jīng)定義了下屬的數(shù)據(jù)庫對象（如表、視圖等），則拒絕該刪除語句的執(zhí)行，只有當該模式中沒有任何下屬的對象時才能執(zhí)行DROPSCHEMA語句例4：

DROPSCHEMATESTCASCADE;該語句刪除了模式TEST。同時，該模式中已經(jīng)定義的表TAB1也被刪除了§3.3.2基本表的定義、刪除與修改一、定義基本表CREATETABLE<表名>（<列名><數(shù)據(jù)類型>[<列級完整性約束條件>][，<列名><數(shù)據(jù)類型>[<列級完整性約束條件>]]…[，<表級完整性約束條件>]）；<表名>：所要定義的基本表的名字<列名>：組成該表的各個屬性（列）<列級完整性約束條件>：涉及相應屬性列的完整性約束條件<表級完整性約束條件>：涉及一個或多個屬性列的完整性約束條件[例5]

建立一個“學生”表Student，它由學號Sno、姓名Sname、性別Ssex、年齡Sage、所在系Sdept五個屬性組成。其中學號不能為空，且為主碼，值是唯一的，并且姓名取值也唯一。

CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY，SnameCHAR(20)UNIQUE，SsexCHAR(2)，SageSMALLINT，SdeptCHAR(20))；系統(tǒng)執(zhí)行上面的語句后，在數(shù)據(jù)庫中建立一個新的空的“學生”表Student，并將有關(guān)“學生”表的定義及有關(guān)約束條件存放在數(shù)據(jù)字典中常用完整性約束主碼約束：PRIMARYKEY唯一性約束：UNIQUE非空值約束：NOTNULL參照完整性約束:FOREIGNKEY問題：PRIMARYKEY與UNIQUE的區(qū)別？[例6]

建立一個“課程”表Course。CREATETABLECourse(CnoCHAR(4)PRIMARYKEY,CnameCHAR(40),CpnoCHAR(4),CcreditSMALLINT,FOREIGNKEYCpnoREFERENCESCourse(Cno)/*表級完整性約束條件，Cpno是外碼，被參照表是Course，被參照列是Cno*/

);說明：參照表和被參照表可以是同一個表[例7]

建立學生選課表SC。CREATETABLESC(SnoCHAR(7),CnoCHAR(4),GradeSMALLINT),PRIMARYKEY(Sno,Cno),/*主碼由兩個屬性構(gòu)成，必須作為表級完整性進行定義*/FOREIGNKEY（Sno）REFERENCESStudent(Sno)/*表級完整性約束條件，Sno是外碼，被參照表是Student*/

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

);二、數(shù)據(jù)類型數(shù)據(jù)類型含義CHAR(n)長度為n的定長字符串VARCHAR(n)最大長度為n的變長字符串INT長整數(shù)（INTEGER）SMALLINT短整數(shù)NUMERIC(p,d)定點數(shù)，由p位數(shù)字（不包括符號、小數(shù)點）組成，小數(shù)后面有d位數(shù)字REAL取決于機器精度的浮點數(shù)DoublePrecision取決于機器精度的雙精度浮點數(shù)FLOAT(n)浮點數(shù)，精度至少為n位數(shù)字DATE日期，格式為YYYY-MM-DDTIME時間，格式為HH.MM.SS每一個基本表都屬于某一個模式，一個模式包含多個基本表，定義模式的方法有三種：方法一：在表名中明顯地給出模式名Createtable“S-T”.Student(……);Createtable“S-T”.Course(……);Createtable“S-T”.SC(……);方法二：在創(chuàng)建模式語句中同時創(chuàng)建表方法三：設(shè)置所屬的模式，這樣在創(chuàng)建表時表名中不必給出模式名三、模式與表當用戶創(chuàng)建基本表時若沒有指定模式，系統(tǒng)根據(jù)搜索路徑來確定該對象所屬的模式。搜索路徑包含一組模式列表，RDBMS會使用模式列表中第一個存在的模式作為數(shù)據(jù)庫對象的模式名。若搜索路徑中的模式名都不存在，系統(tǒng)將給出模式SHOW

search_path搜索路徑的當前默認值是：$user,PUBLIC其含義是首先搜索與用戶名相同的模式名，如果該模式名不存在，則使用PUBLIC模式四、修改基本表

ALTERTABLE<表名>[ADD<新列名><數(shù)據(jù)類型>[完整性約束]][DROPCOLUMN<列名>][MODIFY<列名><數(shù)據(jù)類型>][DROP<完整性約束名>]；其中：ADD：增加新列和新列的完整性約束條件DROP：刪除指定列MODIFY：修改原有列的數(shù)據(jù)類型DROP<完整性約束名>：刪除原有的完整性約束[例8]

向Student表增加“入學時間”列，其數(shù)據(jù)類型為日期型ALTERTABLEStudentADDScomeDATE;注：不論基本表中原來是否已有數(shù)據(jù)，新增加的列一律為空值[例9]

將年齡的數(shù)據(jù)類型改為半字長整數(shù)。ALTERTABLEStudentMODIFYCOLUMNSageSMALLINT；注：修改原有的列定義有可能會破壞已有數(shù)據(jù)[例10]

刪除學生姓名必須取唯一值的約束。ALTERTABLEStudentDROPUNIQUE(Sname)；五、刪除基本表

DROPTABLE<表名>[RESTRICT|CASCADE];

基本表刪除，表中的數(shù)據(jù)、表上的索引都刪除表上的視圖往往仍然保留，但無法引用刪除基本表時，系統(tǒng)會從數(shù)據(jù)字典中刪去有關(guān)該基本表及其索引的描述RESTRICT：表的刪除是有限制條件CASCADE：表的刪除沒有限制條件缺省情況是RESTRICT§3.3.3建立與刪除索引建立索引是加快查詢速度的有效手段建立索引DBA或表的屬主（即建立表的人）根據(jù)需要建立有些DBMS自動建立以下列上的索引

PRIMARYKEYUNIQUE維護索引

DBMS自動完成使用索引

DBMS自動選擇是否使用索引以及使用哪些索引索引一經(jīng)建立，就由系統(tǒng)使用和維護它，不需用戶干預一、建立索引語句格式CREATE

[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…)； 用<表名>指定要建索引的基本表名字索引可以建立在該表的一列或多列上，各列名之間用逗號分隔用<次序>指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCUNIQUE表明此索引的每一個索引值只對應唯一的數(shù)據(jù)記錄CLUSTER表示要建立的索引是聚簇索引指索引項的順序與表中記錄的物理順序一致的索引組織[例14]

為學生-課程數(shù)據(jù)庫中的Student，Course，SC三個表建立索引。其中Student表按學號升序建唯一索引，Course表按課程號升序建唯一索引，SC表按學號升序和課程號降序建唯一索引。CREATEUNIQUEINDEXStusnoONStudent(Sno)；CREATEUNIQUEINDEXCoucnoONCourse(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；唯一值索引對于已含重復值的屬性列不能建UNIQUE索引對某個列建立UNIQUE索引后，插入新記錄時DBMS會自動檢查新記錄在該列上是否取了重復值。這相當于增加了一個UNIQUE約束聚簇索引建立聚簇索引后，基表中數(shù)據(jù)也需要按指定的聚簇屬性值的升序或降序存放。也即聚簇索引的索引項順序與表中記錄的物理順序一致一、建立索引例：在Student表的Sname（姓名）列上建立一個聚簇索引，而且Student表中的記錄將按照Sname值的升序存放CREATECLUSTERINDEXStusnameONStudent(Sname)；說明：在一個基本表上最多只能建立一個聚簇索引聚簇索引的用途：對于某些類型的查詢，可以提高查詢效率建立聚簇索引后，更新索引列數(shù)據(jù)時，往往導致表中記錄的物理順序的變更，代價較大，因此對于經(jīng)常更新的列不宜建立聚簇索引聚簇索引的適用范圍很少對基表進行增刪操作很少對其中的變長列進行修改操作建立索引原則記錄有一定規(guī)模某列在where子句中頻繁使用先裝數(shù)據(jù)，后建索引二、刪除索引語句格式

DROPINDEX<索引名>；刪除索引時，系統(tǒng)會從數(shù)據(jù)字典中刪去有關(guān)該索引的描述[例15]刪除Student表的Stusname索引DROPINDEXStusname；§3.4數(shù)據(jù)查詢語句格式：SELECT[ALL|DISTINCT]<目標列表達式>[，<目標列表達式>]…FROM<表名或視圖名>[，<表名或視圖名>]…[WHERE<條件表達式>][GROUPBY<列名1>[HAVING<條件表達式>]][ORDERBY<列名2>[ASC|DESC]]；其中：SELECT子句：指定要顯示的屬性列FROM子句：指定查詢對象(基本表或視圖)WHERE子句：指定查詢條件

GROUPBY子句：對查詢結(jié)果按指定列的值分組，該屬性列值相等的元組為一個組。通常會在每組中作用聚集函數(shù)。HAVING短語：篩選出只有滿足指定條件的組ORDERBY子句：對查詢結(jié)果表按指定列值的升序或降序排序

§3.4.1單表查詢查詢僅涉及一個表，是一種最簡單的查詢操作一、選擇表中的若干列二、選擇表中的若干元組三、對查詢結(jié)果排序四、聚集函數(shù)五、對查詢結(jié)果分組一、選擇表中的若干列1.查詢指定列[例1]

查詢?nèi)w學生的學號與姓名。SELECTSno，SnameFROMStudent；

[例2]

查詢?nèi)w學生的姓名、學號、所在系。SELECTSname，Sno，SdeptFROMStudent；2.查詢?nèi)苛衃例3]

查詢?nèi)w學生的詳細記錄。SELECTSno，Sname，Ssex，Sage，SdeptFROMStudent；或SELECT*FROMStudent；3.查詢經(jīng)過計算的值

SELECT子句的<目標列表達式>:表中的屬性列表達式算術(shù)表達式、字符串常量、函數(shù)、列別名等[例4]

查全體學生的姓名及其出生年份SELECTSname，2006-SageFROMStudent；

輸出結(jié)果：Sname2006-Sage----------------------李勇1976劉晨1977王名1978張立1978算術(shù)表達式[例5]

查詢?nèi)w學生的姓名、出生年份和所有系，要求用小寫字母表示所有系名。SELECTSname,’YearofBirth:’,2008-Sage,LOWER(Sdept)FROMStudent；函數(shù)字符串常量輸出結(jié)果：Sname'YearofBirth:'2008-SageLOWER(Sdept)----------------------------------------------------

李勇YearofBirth:1976cs劉晨YearofBirth:1977is王名YearofBirth:1978ma張立YearofBirth:1977is[例5.1]使用列別名改變查詢結(jié)果的列標題SELECTSnameNAME，'YearofBirth:’

BIRTH，2008-Sage

BIRTHDAY，LOWER(Sdept)DEPARTMENTFROMStudent；輸出結(jié)果：NAMEBIRTHBIRTHDAYDEPARTMENT------------------------------------------------------李勇YearofBirth:1976cs劉晨YearofBirth:1977is王名YearofBirth:1978ma張立YearofBirth:1977is二、選擇表中的若干元組消除取值重復的行查詢滿足條件的元組1.消除取值重復的行在SELECT子句中使用DISTINCT短語假設(shè)SC表中有下列數(shù)據(jù)

SnoCnoGrade---------------------9500119295001285950013889500229095002380ALL與DISTINCT的使用[例6]

查詢選修了課程的學生學號。(1)SELECTSnoFROMSC; 或(默認ALL)SELECTALLSnoFROMSC;

結(jié)果：Sno-------9500195001950019500295002(2)SELECTDISTINCTSnoFROMSC;

結(jié)果：Sno-------9500195002注:

DISTINCT短語的作用范圍是所有目標列例：查詢選修課程號，及成績錯誤：SELECTDISTINCTCno，DISTINCTGradeFROMSC;正確：SELECTDISTINCTCno，GradeFROMSC;

2.查詢滿足條件的元組WHERE子句常用的查詢條件(1)比較大小在WHERE子句的<比較條件>中使用比較運算符=，>，<，>=，<=，!=(或<>)，!>，!<邏輯運算符NOT+比較運算符[例8]

查詢所有年齡在20歲以下的學生姓名及其年齡。SELECTSname，SageFROMStudentWHERESage<20；或SELECTSname，SageFROMStudentWHERENOTSage>=20；(2)確定范圍使用謂詞：BETWEEN…AND…NOTBETWEEN…AND…[例10]

查詢年齡在20~23歲（包括20歲和23歲）之間的學生的姓名、系別和年齡。SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；[例11]

查詢年齡不在20~23歲之間的學生姓名、系別和年齡SELECTSname，Sdept，SageFROMStudentWHERESageNOTBETWEEN20AND23；

(3)確定集合使用謂詞IN<值表>,NOTIN<值表><值表>：用逗號分隔的一組取值[例12]查詢信息系（IS）、數(shù)學系（MA）和計算機科學系（CS）學生的姓名和性別。SELECTSname，SsexFROMStudentWHERESdeptIN('IS'，'MA'，'CS');(4)字符串匹配[NOT]LIKE‘<匹配串>’[ESCAPE‘<換碼字符>’]其中：<匹配串>：指定匹配模板匹配模板：固定字符串或含通配符的字符串當匹配模板為固定字符串時，可以用=運算符取代LIKE

謂詞用!=或<>運算符取代NOTLIKE

謂詞通配符%(百分號)：代表任意長度（長度可以為0）的字符串例：a%b表示以a開頭，以b結(jié)尾的任意長度的字符串。如acb，addgb，ab等都滿足該匹配串_(下橫線)：代表任意單個字符例：a_b表示以a開頭，以b結(jié)尾的長度為3的任意字符串。如acb，afb等都滿足該匹配串ESCAPE短語：當用戶要查詢的字符串本身就含有%或_時，要使用ESCAPE'<換碼字符>'短語對通配符進行轉(zhuǎn)義。1)匹配模板為固定字符串[例14]

查詢學號為95001的學生的詳細情況。SELECT*FROMStudentWHERESnoLIKE'95001'；等價于： SELECT*FROMStudentWHERESno=‘95001’；2)匹配模板為含通配符的字符串[例15]

查詢所有姓劉學生的姓名、學號和性別。SELECTSname，Sno，SsexFROMStudentWHERESnameLIKE‘劉%’；[例16]查詢姓"歐陽"且全名為三個漢字的學生的姓名。SELECTSnameFROMStudentWHERESnameLIKE'歐陽__'；[例17]

查詢名字中第2個字為"陽"字的學生的姓名和學號。SELECTSname，SnoFROMStudentWHERESnameLIKE‘__陽%’；[例18]查詢所有不姓劉的學生姓名。SELECTSname，Sno，SsexFROMStudentWHERESnameNOTLIKE'劉%'；3)使用換碼字符將通配符轉(zhuǎn)義為普通字符

[例19]查詢DB_Design課程的課程號和學分。

SELECTCno，CcreditFROMCourseWHERECnameLIKE‘DB\_Design’ESCAPE‘\’;[例20]

查詢以"DB_"開頭，且倒數(shù)第3個字符為i的課程的詳細情況。

SELECT*FROMCourseWHERECnameLIKE‘DB\_%i__’ESCAPE‘\’;(5)涉及空值的查詢使用謂詞ISNULL或ISNOTNULL“ISNULL”不能用“=NULL”代替[例21]

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

查所有有成績的學生學號和課程號。

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

AND的優(yōu)先級高于OR可以用括號改變優(yōu)先級可用來實現(xiàn)多種其他謂詞[NOT]IN[NOT]BETWEEN…AND…[例23]

查詢計算機系年齡在20歲以下的學生姓名。SELECTSnameFROMStudentWHERESdept='CS'ANDSage<20；改寫[例12][例12]

查詢信息系（IS）、數(shù)學系（MA）和計算機科學系（CS）學生的姓名和性別。SELECTSname，SsexFROMStudentWHERESdeptIN(‘IS’，’MA’，’CS’)可改寫為：

SELECTSname，SsexFROMStudentWHERESdept=‘IS‘ORSdept=‘MA’ORSdept=‘CS‘；改寫[例10][例10]

查詢年齡在20~23歲（包括20歲和23歲）之間的學生的姓名、系別和年齡。

SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；可改寫為：

SELECTSname，Sdept，SageFROMStudentWHERESage>=20ANDSage<=23；三、對查詢結(jié)果排序使用ORDERBY子句可以按一個或多個屬性列排序升序：ASC；降序：DESC；缺省值為升序當排序列含空值時ASC：排序列為空值的元組最后顯示DESC：排序列為空值的元組最先顯示[例24]

查詢選修了3號課程的學生的學號及其成績，查詢結(jié)果按分數(shù)降序排列。SELECTSno，GradeFROMSCWHERECno='3'ORDERBYGradeDESC；[例25]

查詢?nèi)w學生情況，查詢結(jié)果按所在系的系號升序排列，同一系中的學生按年齡降序排列。

SELECT*FROMStudentORDERBYSdept(ASC),SageDESC;

結(jié)果：SnoGrade--------------9501095024950079295003829501082950097595014619500255四、使用集函數(shù)5類主要集函數(shù)計數(shù)COUNT（[DISTINCT|ALL]*）統(tǒng)計元組個數(shù)COUNT（[DISTINCT|ALL]<列名>）統(tǒng)計一列中值的個數(shù)計算總和（列必須是數(shù)值型）SUM（[DISTINCT|ALL]<列名>）統(tǒng)計一列值的總和 計算平均值（列必須是數(shù)值型）AVG（[DISTINCT|ALL]<列名>）統(tǒng)計一列值的平均值求最大值MAX（[DISTINCT|ALL]<列名>）求一列中最大值求最小值MIN（[DISTINCT|ALL]<列名>）求一列中最小值其中：DISTINCT短語：在計算時要取消指定列中的重復值A(chǔ)LL短語：不取消重復值A(chǔ)LL為缺省值除COUNT（＊）外，其它函數(shù)均跳過空值而只處理非空值四、使用集函數(shù)[例26]

查詢學生總?cè)藬?shù)。SELECTCOUNT(*)FROMStudent；

[例27]查詢選修了課程的學生人數(shù)。SELECTCOUNT(DISTINCTSno)FROMSC；注：用DISTINCT以避免重復計算學生人數(shù)[例28]計算1號課程的學生平均成績。SELECTAVG(Grade)FROMSCWHERECno='1'；

[例29]

查詢選修1號課程的學生最高分數(shù)。SELECTMAX(Grade)FROMSCWHERCno='1'；五、對查詢結(jié)果分組使用GROUPBY子句分組 細化聚集函數(shù)的作用對象若未對查詢結(jié)果分組，聚集函數(shù)將作用于整個查詢結(jié)果若對查詢結(jié)果分組后，聚集函數(shù)將分別作用于每個組

[例30]

求各個課程號及相應的選課人數(shù)。SELECTCno，COUNT(Sno)FROMSCGROUPBYCno；

結(jié)果CnoCOUNT(Sno) 122 234 344 433 548GROUPBY子句的作用對象是查詢的中間結(jié)果表分組方法：按指定的一列或多列值分組，值相等的為一組使用GROUPBY子句后，SELECT子句的列名列表中只能出現(xiàn)分組屬性和集函數(shù)如果分組后仍要求按一定的條件對這些組進行篩選，最終只輸出滿足指定條件的組，則可以使用HAVING短語指定篩選條件只有滿足HAVING短語指定條件的組才輸出

五、對查詢結(jié)果分組

使用HAVING短語篩選最終輸出結(jié)果[例31]

查詢選修了3門以上課程的學生學號。SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)>3；[例32]

查詢有3門以上課程是90分以上的學生的學號及（90分以上的）課程數(shù)。

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

使用HAVING短語篩選最終輸出結(jié)果HAVING短語與WHERE子句的區(qū)別：作用對象不同WHERE子句作用于基表或視圖，從中選擇滿足條件的元組。WHERE子句中的謂詞在形成分組前起作用HAVING短語作用于組，從中選擇滿足條件的元組。HAVING子句中的謂詞在形成分組后才起作用§3.4.2連接查詢

同時涉及多個表的查詢稱為連接查詢用來連接兩個表的條件稱為連接條件或連接謂詞一般格式：[<表名1>.]<列名1><比較運算符>[<表名2>.]<列名2>

比較運算符：=、>、<、>=、<=、!=[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>連接字段連接謂詞中的列名稱為連接字段連接條件中的各連接字段類型必須是可比的，但不必是相同的廣義笛卡爾積等值連接(含自然連接)非等值連接查詢自身連接查詢外連接查詢復合條件連接查詢SQL中連接查詢的主要類型一、廣義笛卡爾積不帶連接謂詞的連接很少使用例：

SELECTStudent.*,SC.*FROMStudent,SC二、等值與非等值連接查詢等值連接、自然連接非等值連接等值連接連接運算符為“=”的連接操作

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

[例32]

查詢每個學生及其選修課程的情況。SELECTStudent.*，SC.*FROMStudent，SCWHEREStudent.Sno=SC.Sno；/*將Student與SC中同一學生的元組連接起來*/Student表SC表Student.SnoSnameSsexSageSdeptSC.SnoCnoGrade95001李勇男20CS9500119295001李勇男20CS9500128595001李勇男20CS9500138895002劉晨女19IS9500229095002劉晨女19IS95002380等值連接后查詢結(jié)果為：SnoSnameSsexSageSdept95001李勇男20CS95001李勇男20CS95001李勇男20CS95002劉晨女19IS95002劉晨女19IS95003王敏女18MA95004張立男19ISSnoCnoGrade9500119295001285950013889500229095002380自然連接是等值連接的一種特殊情況，把目標列中重復的屬性列去掉[例33]

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

SELECT

Student.Sno，Sname，Ssex，Sage,Sdept，Cno，GradeFROMStudent，SCWHEREStudent.Sno=SC.Sno；Student.SnoSnameSsexSageSdeptCnoGrade95001李勇男20CS19295001李勇男20CS28595001李勇男20CS38895002劉晨女19IS29095002劉晨女19IS380非等值連接查詢連接運算符不是=

的連接操作[<表名1>.]<列名1><比較運算符>[<表名2>.]<列名2>比較運算符：>、<、>=、<=、!=[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>三、自身連接一個表與其自己進行連接，稱為表的自身連接需要給表起別名以示區(qū)別由于所有屬性名都是同名屬性，因此必須使用別名前綴[例34]

查詢每一門課的間接先修課（即先修課的先修課）

SELECTFIRST.Cno，SECOND.CpnoFROMCourseFIRST，CourseSECONDWHEREFIRST.Cpno=SECOND.Cno；

FIRST表（Course表）SECOND表（Course表）自身連接結(jié)果為：cnocpnocnoCnameCpnoCcredit1數(shù)據(jù)庫542數(shù)學23信息系統(tǒng)144操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理27PASCAL語言64cnoCnameCpnoCcredit1數(shù)據(jù)庫542數(shù)學23信息系統(tǒng)144操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理27PASCAL語言6417354567四、外連接（OuterJoin）外連接與普通連接的區(qū)別普通連接操作只輸出滿足連接條件的元組外連接操作以指定表為連接主體，將主體表中不滿足連接條件的元組一并輸出外連接分類：左外連接LEFTOUTJOIN:外連接符出現(xiàn)在連接條件的左邊右外連接RIGHTOUTJOIN:外連接符出現(xiàn)在連接條件的右邊全外連接FULLOUTJOIN[例35]

查詢每個學生及其選修課程的情況包括沒有選修課程的學生----用外連接操作

SELECTStudent.Sno，Sname，Ssex，Sage，Sdept，Cno，GradeFROMStudentLEFTOUTJOINSCON(Student.Sno=SC.Sno);/*也可以作用USING來去掉結(jié)果中的重復值：FROMStudentLEFTOUTJOINSCUSING(Sno);*/Student.SnoSnameSsexSageSdeptCnoGrade95001李勇男20CS19295001李勇男20CS28595001李勇男20CS38895002劉晨女19IS29095002劉晨女19IS38095003王敏女18MANULLNULL95004男張立19ISNULLNULL五、復合條件連接WHERE子句中含多個連接條件時，稱為復合條件連接[例37]查詢選修2號課程且成績在90分以上的所有學生的學號、姓名

SELECTStudent.Sno,student.SnameFROMStudent,SCWHEREStudent.Sno=SC.Sno

AND

/*連接謂詞*/

SC.Cno=‘2’AND

SC.Grade>90；

/*邏輯運算符其他限定條件*/多表連接[例38]

查詢每個學生的學號、姓名、選修的課程名及成績。

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

結(jié)果： Student.SnoSnameCnameGrade95001李勇數(shù)據(jù)庫9295001李勇數(shù)學8595001李勇信息系統(tǒng)8895002劉晨數(shù)學9095002劉晨信息系統(tǒng)80§3.4.3嵌套查詢一個SELECT-FROM-WHERE語句稱為一個查詢塊將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢

SELECTSname

外層查詢/父查詢

FROMStudentWHERESno

IN

（SELECTSno

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

FROMSCWHERECno=‘2’）；子查詢的限制不能使用ORDERBY子句，ORDERBY子句只能對最終結(jié)果排序?qū)訉忧短追绞椒从沉薙QL語言的結(jié)構(gòu)化有些嵌套查詢可以用連接運算替代嵌套查詢分類及求解方法分類：不相關(guān)子查詢：子查詢的查詢條件不依賴于父查詢相關(guān)子查詢：子查詢的查詢條件依賴于父查詢嵌套查詢求解方法不相關(guān)子查詢是由里向外逐層處理。即每個子查詢在上一級查詢處理之前求解，子查詢的結(jié)果用于建立其父查詢的查找條件。相關(guān)子查詢首先取外層查詢中表的第一個元組，根據(jù)它與內(nèi)層查詢相關(guān)的屬性值處理內(nèi)層查詢，若WHERE子句返回值為真，則取此元組放入結(jié)果表；然后再取外層表的下一個元組；重復這一過程，直至外層表全部檢查完為止引出子查詢的謂詞帶有IN謂詞的子查詢帶有比較運算符的子查詢帶有ANY或ALL謂詞的子查詢帶有EXISTS謂詞的子查詢一、帶有IN謂詞的子查詢[例37]

查詢與“劉晨”在同一個系學習的學生。(此查詢要求可以分步來完成，然后再構(gòu)造嵌套查詢)①確定“劉晨”所在系名

SELECTSdeptFROMStudentWHERESname='劉晨'； 結(jié)果為： Sdept IS②查找所有在IS系學習的學生。

SELECTSno，Sname，SdeptFROMStudentWHERESdept='IS'；結(jié)果為：SnoSnameSdept95001劉晨IS95004張立IS構(gòu)造嵌套查詢將第一步查詢嵌入到第二步查詢的條件中SELECTSno，Sname，SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname=‘劉晨’)；判斷此查詢是相關(guān)還是不相關(guān)子查詢？是不相關(guān)子查詢并且DBMS求解該查詢時也是分步去做的此查詢也可以用自身連接完成SELECTS1.Sno，S1.Sname，S1.SdeptFROMStudentS1，StudentS2WHERES1.Sdept=S2.SdeptAND

S2.Sname='劉晨'；父查詢和子查詢中的表均可以定義別名SELECTSno，Sname，SdeptFROMStudentS1WHERES1.SdeptIN(SELECTSdeptFROMStudentS2WHERES2.Sname=‘劉晨’)；接上[例38]查詢選修了課程名為“信息系統(tǒng)”的學生學號和姓名。

SELECTSno，Sname③最后在Student關(guān)系中FROMStudent取出Sno和SnameWHERESnoIN(SELECTSno②然后在SC關(guān)系中找出選FROMSC修了3號課程的學生學號WHERECnoIN(SELECTCno①首先在Course關(guān)系中找出“信FROMCourse息系統(tǒng)”的課程號，結(jié)果為3號WHERECname=‘信息系統(tǒng)’));課程名課程號學生號學生姓名結(jié)果：SnoSname---------95001李勇95002劉晨courseSCStudent用連接查詢SELECTSno，SnameFROMStudent，SC，CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.Cname=‘信息系統(tǒng)’;接上二、帶有比較運算符的子查詢當能確切知道內(nèi)層查詢返回單值時，可用比較運算符（>，<，=，>=，<=，!=或<>）。與ANY或ALL謂詞配合使用例：[例37]可以用=代替IN：

SELECTSno，Sname，SdeptFROMStudentWHERESdept=(SELECTSdeptFROMStudentWHERESname='劉晨‘)；二、帶有比較運算符的子查詢

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

錯誤的例子：

SELECTSno，Sname，SdeptFROMStudentWHERE(SELECTSdeptFROMStudentWHERESname=‘劉晨’)=Sdept；三、帶有ANY或ALL謂詞的子查詢謂詞語義ANY：任意一個值A(chǔ)LL：所有值需要配合使用比較運算符>ANY 大于子查詢結(jié)果中的某個值

>ALL 大于子查詢結(jié)果中的所有值<ANY 小于子查詢結(jié)果中的某個值<ALL 小于子查詢結(jié)果中的所有值>=ANY 大于等于子查詢結(jié)果中的某個值>=ALL 大于等于子查詢結(jié)果中的所有值<=ANY 小于等于子查詢結(jié)果中的某個值<=ALL 小于等于子查詢結(jié)果中的所有值=ANY 等于子查詢結(jié)果中的某個值=ALL 等于子查詢結(jié)果中的所有值（通常沒有實際意義）!=（或<>）ANY 不等于子查詢結(jié)果中的某個值!=（或<>）ALL 不等于子查詢結(jié)果中的任何一個值[例39]

查詢其他系中比信息系任意一個(其中某一個)學生年齡小的學生姓名和年齡

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

ANDSdept<>'IS';

/*注意這是父查詢塊中的條件*/DBMS執(zhí)行過程：1.首先處理子查詢，找出IS系中所有學生的年齡，構(gòu)成一個集合(19，18)2.接著處理父查詢，找所有不是IS系且年齡小于19或18的學生ANY和ALL謂詞有時可以用聚集函數(shù)實現(xiàn)ANY與ALL與集函數(shù)的對應關(guān)系用集函數(shù)實現(xiàn)子查詢通常比直接用ANY或ALL查詢效率要高，因為前者通常能夠減少比較次數(shù)

=<>或!=

<<=>>=ANYIN--

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

<MIN<=MIN>MAX>=MAX[例39‘]：用集函數(shù)實現(xiàn)[例39]：查詢其他系中比信息系任意一個(其中某一個)學生年齡小的學生姓名和年齡

SELECTSname，SageFROMStudentWHERESage<(SELECTMAX(Sage)FROMStudentWHERESdept='IS')ANDSdept<>'IS’;[例40]

查詢其他系中比信息系所有學生年齡都小的學生姓名及年齡。方法一：用ALL謂詞

SELECTSname，SageFROMStudentWHERESage<ALL(SELECTSageFROMStudentWHERESdept='IS')ANDSdept<>'IS’;方法二：用聚集函數(shù)SELECTSname，SageFROMStudentWHERESage<(SELECTMIN(Sage)FROMStudentWHERESdept='IS')ANDSdept<>'IS’;四、帶有EXISTS謂詞的子查詢1.EXISTS謂詞2.NOTEXISTS謂詞3.不同形式的查詢間的替換4.相關(guān)子查詢的效率5.用EXISTS/NOTEXISTS實現(xiàn)全稱量詞6.用EXISTS/NOTEXISTS實現(xiàn)邏輯蘊函1.EXISTS謂詞存在量詞

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

[例41]

查詢所有選修了1號課程的學生姓名。思路分析：本查詢涉及Student和SC關(guān)系。在Student中依次取每個元組的Sno值，用此值去檢查SC關(guān)系。若SC中存在這樣的元組，其Sno值等于此Student.Sno值，并且其Cno=‘1’，則取此Student.Sname送入結(jié)果關(guān)系。方法一：用嵌套查詢SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSC/*相關(guān)子查詢*/WHERESno=Student.SnoANDCno='1')；方法二：用連接運算SELECTSnameFROMStudent,SCWHEREStudent.Sno=SC.SnoAND SC.Cno='1';

[例42]查詢沒有選修1號課程的學生姓名。

SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno='1')；此例用連接運算難于實現(xiàn)。3.不同形式的查詢間的替換一些帶EXISTS或NOTEXISTS謂詞的子查詢不能被其他形式的子查詢等價替換所有帶IN謂詞、比較運算符、ANY和ALL謂詞的子查詢都能用帶EXISTS謂詞的子查詢等價替換。四、帶有EXISTS謂詞的子查詢例：[例37]查詢與“劉晨”在同一個系學習的學生。可以用帶EXISTS謂詞的子查詢替換：

SELECTSno，Sname，SdeptFROMStudentS1WHEREEXISTS(SELECT*FROMStudentS2WHERES2.Sdept=S1.SdeptANDS2.Sname=‘劉晨‘)；4.用EXISTS/NOTEXISTS實現(xiàn)全稱量詞(難點)SQL語言中沒有全稱量詞（Forall）可以把帶有全稱量詞的謂詞轉(zhuǎn)換為等價的帶有存在量詞的謂詞：

(x)P≡(x(P))5.用EXISTS/NOTEXISTS實現(xiàn)邏輯蘊函(難點)SQL語言中沒有蘊函(Implication)邏輯運算可以利用謂詞演算將邏輯蘊函謂詞等價轉(zhuǎn)換為：

pq≡

p∨q

四、帶有EXISTS謂詞的子查詢[例43]

查詢選修了全部課程的學生姓名。

SELECTSnameFROMStudentWHERENOTEXISTS（SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=Course.Cno))；不存在沒有選修課程的學生不存在沒有學生選修的課程任意課程,所求的學生都選了不存在任何一門課程,所求的學生沒選[例44]

查詢至少選修了學生95002選修的全部課程的學生號解題思路：用邏輯蘊函表達：查詢學號為x的學生，對所有的課程y，只要95002學生選修了課程y，則x也選修了y。形式化表示：

用P表示謂詞“學生95002選修了課程y” 用q表示謂詞“學生x選修了課程y” 則上述查詢?yōu)?(y)pq等價變換： (y)pq≡(y((pq))≡(y((p∨q)≡

y(p∧q)變換后語義：不存在這樣的課程y，學生95002選修了y，而學生x沒有選。用NOTEXISTS謂詞表示：

SELECTDISTINCTSnoFROMSCSCXWHERENOTEXISTS(SELECT*FROMSCSCYWHERESCY.Sno='95002'ANDNOTEXISTS(SELECT*FROMSCSCZWHERESCZ.Sno=SCX.SnoANDSCZ.Cno=SCY.Cno))；任意課程,95002學生選了,所求的學生都選了不存在任何一門課程,99002學生選了,所求的學生沒選§3.4.4集合查詢標準SQL直接支持的集合操作種類并操作(UNION)一般商用數(shù)據(jù)庫支持的集合操作種類并操作(UNION)交操作(INTERSECT)差操作(EXCEPT)1．并操作形式 <查詢塊> UNION <查詢塊>注：參加UNION操作的各結(jié)果表的列數(shù)必須相同；對應項的數(shù)據(jù)類型也必須相同[例45]

查詢計算機科學系的學生或年齡不大于19歲的學生。方法一：

SELECT*FROMStudentWHERESdept='CS'UNIONSELECT*FROMStudentWHERESage<=19；方法二：

SELECTDISTINCT*FROMStudentWHERESdept='CS'ORSage<=19；注：使用UNION將多個查詢結(jié)果合并起來，系統(tǒng)會自動去掉重復元組[例46]

查詢選修了課程1或者選修了課程2的學生。方法一：SELECTSnoFROMSCWHERECno='1'UNIONSELECTSnoFROMSCWHERECno='2'；方法二：

SELECTDISTINCTSnoFROMSCWHERECno='1'ORCno='2'；[例47]

設(shè)數(shù)據(jù)庫中有一教師表Teacher(Tno,name,...)。查詢學校中所有師生的姓名。

SELECTSnameFROMStudentUNIONSELECTTnameFROMTeacher;2．交操作[例48]

查詢計算機科學系的學