數(shù)據(jù)庫(kù)系統(tǒng)簡(jiǎn)明教程ch3-SQL

第二章關(guān)系數(shù)據(jù)庫(kù)上節(jié)回顧關(guān)系是數(shù)據(jù)結(jié)構(gòu)是一張二維表關(guān)系模型的基本操作關(guān)系的完整性約束關(guān)系代數(shù)是用代數(shù)的方法由已知關(guān)系構(gòu)造新的關(guān)系關(guān)系演算是用謂詞演算的方式構(gòu)造新的關(guān)系關(guān)系代數(shù)、元組演算和域演算在表達(dá)和操作能力上是等價(jià)的有專門的關(guān)系代數(shù)語(yǔ)言(ISBL)和元組演算語(yǔ)言(QUEL)等來(lái)完成關(guān)系代數(shù)表達(dá)式和元組演算表達(dá)式在關(guān)系上的實(shí)現(xiàn)。

缺點(diǎn)：這些語(yǔ)言太過數(shù)學(xué)化，不易掌握。第三章關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言－SQLSQL是什么？1972年，IBM開發(fā)SystemR實(shí)驗(yàn)系統(tǒng)，配置查詢語(yǔ)言SQUARE(SpecifyingQueriesAsRelationalExpression)，以數(shù)學(xué)符號(hào)為主。1974年由Boyce和Chamberlin將SQUARE語(yǔ)言修改為SEQUEL(StructuredEnglishQUEryLanguage)語(yǔ)言，采用英文和結(jié)構(gòu)式的語(yǔ)法規(guī)則。后來(lái)SEQUEL簡(jiǎn)稱為SQL(StructuredQueryLanguage，結(jié)構(gòu)化查詢語(yǔ)言)。1986年，ANSI批準(zhǔn)SQL作為關(guān)系數(shù)據(jù)庫(kù)語(yǔ)言的美國(guó)標(biāo)準(zhǔn)，1987年ISO采納此標(biāo)準(zhǔn)，統(tǒng)稱為SQL-86。并于1989進(jìn)行擴(kuò)充，增強(qiáng)完整性約束，稱為SQL89。1992年，ANSI公布新標(biāo)準(zhǔn)SQL-92，增加了許多新特性，如新的數(shù)據(jù)類型，更豐富的數(shù)據(jù)操作，更強(qiáng)的完整性、安全性支持。又稱為SQL2。1999年，ANSI公布SQL-99標(biāo)準(zhǔn)，增加了對(duì)面向?qū)ο蟮闹С?。又稱為SQL3。3.1SQL概述SQL的特點(diǎn)：(1)綜合統(tǒng)一集DDL、DML、DCL于一體。(2)高度非過程化不用考慮如何實(shí)現(xiàn)，只需提出“做什么”，不關(guān)心“怎么做“。(3)面向集合的操作方式查詢、插入、刪除、更新操作對(duì)象及結(jié)果都是集合。(4)以同一種語(yǔ)法結(jié)構(gòu)提供兩種使用方法可交互式和嵌入式使用。(5)以簡(jiǎn)捷的自然語(yǔ)言作為操作語(yǔ)言定義了少量的關(guān)鍵字實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)的定義、操縱和控制功能。3.1SQL概述SQL語(yǔ)言所使用的9個(gè)核心動(dòng)詞SQL功能動(dòng)詞數(shù)據(jù)查詢SELECT數(shù)據(jù)定義CREATE，DROP，ALTER數(shù)據(jù)操縱INSERT，UPDATE，DELETE數(shù)據(jù)控制GRANT，REVOKE3.1SQL概述SQL與三級(jí)模式體系結(jié)構(gòu)SQL語(yǔ)句基本表1基本表2基本表3基本表4視圖1視圖2存儲(chǔ)文件1存儲(chǔ)文件2外模式模式內(nèi)模式3.2數(shù)據(jù)定義

SQL提供了專門的語(yǔ)言用來(lái)定義數(shù)據(jù)庫(kù)、表、索引等數(shù)據(jù)庫(kù)對(duì)象，這些語(yǔ)言被稱作數(shù)據(jù)庫(kù)定義語(yǔ)言（DataDefinitionLanguage，DDL）。SQL的數(shù)據(jù)定義語(yǔ)句：操作對(duì)象索引視圖CREATETABLE表修改刪除創(chuàng)建操作方式CREATEVIEWCREATEINDEXDROPTABLEDROPVIEWDROPINDEXALTERTABLE3.2數(shù)據(jù)定義SQL中的標(biāo)準(zhǔn)數(shù)據(jù)類型CHARACTER 字符型

NUMERIC 數(shù)值型DECIMAL 壓縮十進(jìn)制數(shù)INTEGER 全字長(zhǎng)二進(jìn)制數(shù)SMALLINT 半字長(zhǎng)二進(jìn)制數(shù)FLOAT 雙字長(zhǎng)浮點(diǎn)數(shù)REAL 單精度實(shí)數(shù)型DOUBLEPRECISION雙單精度實(shí)數(shù)型3.2數(shù)據(jù)定義創(chuàng)建基本表CREATETABLE<表名>

(<列名><數(shù)據(jù)類型>[<列級(jí)完整性約束條件>][，<列名><數(shù)據(jù)類型>[<列級(jí)完整性約束條件>]]…[，<表級(jí)完整性約束條件>])；<表名>：所要定義的基本表的名字<列名>：組成該表的各個(gè)屬性（列）<列級(jí)完整性約束條件>：涉及相應(yīng)屬性列的完整性約束條件<表級(jí)完整性約束條件>：涉及一個(gè)或多個(gè)屬性列的完整性約束條件3.2數(shù)據(jù)定義常用完整性約束(1)主碼約束：PRIMARYKEY (2)唯一性約束：UNIQUE(不能取相同值但允許多個(gè)空值) (3)非空值約束：NOTNULL (4)參照完整性約束：

FOREIGNKEY<列名>REFERENCES<表名>(<列名>)3.2數(shù)據(jù)定義[例1]建立一個(gè)“學(xué)生”表Student，它由學(xué)號(hào)Sno、姓名Sname、性別Ssex、年齡Sage、所在系Sdept五個(gè)屬性構(gòu)成。其中學(xué)號(hào)不能為空，值是唯一的。CREATETABLEStudent(

SnoCHAR(5)NOTNULLDEFAULT'',

SnameCHAR(6)DEFAULT'',

SsexCHAR(2)DEFAULT'', SageINTDEFAULT0,

SdeptCHAR(2)DEFAULT'',

ConstraintStudentPKPrimaryKey(Sno),Sno為主鍵的約束

ConstraintSageCKCheck(Sage>0ANDSage<150)

3.2數(shù)據(jù)定義[例2]建立一個(gè)“學(xué)生選課”表SC，它由學(xué)號(hào)Sno、課程號(hào)Cno，修課成績(jī)Grade組成，其中(Sno,Cno)為主碼。CREATETABLESC(

SnoCHAR(5),

CnoCHAR(3),Gradeint,

PRIMARYKEY(Sno,Cno),

FOREIGNKEY(Sno)REFERENCES

S(Sno),

FOREIGNKEY(Cno)REFERENCES

C(Cno))；3.2數(shù)據(jù)定義刪除基本表

DROPTABLE<表名>;

[例3]刪除Student表

DROPTABLEStudent;

基本表刪除后，表里的數(shù)據(jù)、表上的索引都會(huì)被刪除，表上的視圖往往仍然保留，但無(wú)法引用。 刪除基本表時(shí)，系統(tǒng)會(huì)從數(shù)據(jù)字典中刪去有關(guān)該基本表及其索引的描述。3.2數(shù)據(jù)定義修改基本表 ALTERTABLE<表名>[ADD<新列名><數(shù)據(jù)類型>[完整性約束]][DROP<完整性約束名><列名>][MODIFY<列名><數(shù)據(jù)類型>]；<表名>：要修改的基本表ADD子句：增加新列和新的完整性約束條件DROP子句：刪除指定的完整性約束條件MODIFY子句：用于修改列名和數(shù)據(jù)類型3.2數(shù)據(jù)定義[例4]向Student表增加“入學(xué)時(shí)間”列，其數(shù)據(jù)類型為日期型。ALTERTABLEStudentADDScomeDATETIMEDEFAULTGETDATE()；

注：不論基本表中原來(lái)是否已有數(shù)據(jù)，新增加的列一律為為當(dāng)前日期[例5]將學(xué)生成績(jī)的數(shù)據(jù)類型改為實(shí)型。

ALTERTABLESCALTERCOLUMNGradeReal；

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

ALTERTABLEStudentDROPUNIQUE(Sname);3.2數(shù)據(jù)定義刪除屬性列 間接刪除把表中要保留的列及其內(nèi)容復(fù)制到一個(gè)新表中刪除原表再將新表重命名為原表名 直接刪除屬性列:(SQL-99)

例：ALTERTABLEStudentDropScome;3.2數(shù)據(jù)定義索引1902男錢七052002女趙六042001男王五032101男李四021901女張三01sagesdepssexsnamesnoSsnopointer0102030405pointersage212019Search-key項(xiàng)常用的組織方式是順序或Hash排列。唯一索引(UniqueIndex)：每一個(gè)索引值只對(duì)應(yīng)唯一的數(shù)據(jù)記錄。當(dāng)建立單一索引后，索引項(xiàng)不可以再插入已有值，但可以多個(gè)空值。同樣，當(dāng)建立單一索引時(shí)，如果待索引項(xiàng)存在相同值則不能建立。聚簇索引(ClusterIndex)：索引項(xiàng)順序與表中數(shù)據(jù)記錄的物理順序一致。Index_snoIndex_sageSearch-keypointer3.2數(shù)據(jù)定義創(chuàng)建索引：CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…)； 用<表名>指定要建索引的基本表名字索引可以建立在該表的一列或多列上，各列名之間用逗號(hào)分隔用<次序>指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCUNIQUE表明此索引的每一個(gè)索引值只對(duì)應(yīng)唯一的數(shù)據(jù)記錄CLUSTER表示要建立的索引是聚簇索引3.2數(shù)據(jù)定義[例7]設(shè)有一個(gè)供應(yīng)商S、零件P、工程項(xiàng)目J、供銷情況SPJ，希望建立四個(gè)索引。其中：供應(yīng)商S按Sno的升序；零件P按Pno的升序；工程項(xiàng)目J按Jno的升序；供銷情況SPJ按Sno的升序，按Pno的降序按Jno的升序。解：

CREATEUNIQUEINDEXS_SNOONS(Sno)；

CREATEUNIQUEINDEXP_PNOONP(Pno)；

CREATEUNIQUEINDEXJ_JNOONJ(Jno)；

CREATEUNIQUEINDEXSPJ_NOONSPJ(SnoASC，PnoDESC，JNOASC)。3.2數(shù)據(jù)定義刪除索引：

DROPINDEX<索引名>；注：刪除索引時(shí)，系統(tǒng)會(huì)從數(shù)據(jù)字典中刪去有關(guān)該索引的描述。[例8]

刪除Student表的Stusname索引。

DROPINDEXStusname

SQL標(biāo)準(zhǔn)中沒有定義對(duì)索引的修改功能，而采用刪除后重新定義索引的方式實(shí)現(xiàn)。3.2數(shù)據(jù)定義唯一索引(唯一值索引)對(duì)于已含重復(fù)值的屬性列不能建UNIQUE索引對(duì)某個(gè)列建立UNIQUE索引后，插入新記錄時(shí)DBMS會(huì)自動(dòng)檢查新記錄在該列上是否取了重復(fù)值。這相當(dāng)于增加了一個(gè)UNIQUE約束聚簇索引(聚集索引)建立聚簇索引后，基表中數(shù)據(jù)也需要按指定的聚簇屬性值的升序或降序存放。也即聚簇索引的索引項(xiàng)順序與表中記錄的物理順序一致在一個(gè)基本表上最多只能建立一個(gè)聚簇索引聚簇索引的用途：對(duì)于某些類型的查詢，可以提高查詢效率聚簇索引的適用范圍很少對(duì)基表進(jìn)行增刪操作很少對(duì)其中的變長(zhǎng)列進(jìn)行修改操作3.2數(shù)據(jù)定義建立索引是加快查詢速度的有效手段建立索引DBA或表的屬主（即建立表的人）根據(jù)需要建立有些DBMS自動(dòng)建立以下列上的索引

PRIMARYKEYUNIQUE維護(hù)索引

DBMS自動(dòng)完成

使用索引

DBMS自動(dòng)選擇是否使用索引以及使用哪些索引3.3查詢查詢語(yǔ)法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查詢語(yǔ)句的執(zhí)行過程讀取FROM子句中基本表、視圖的數(shù)據(jù)，執(zhí)行笛卡爾積操作。選取滿足WHERE子句中給出的條件表達(dá)式的元組。按GROUP子句中指定列的值分組，同時(shí)提取滿足HAVING子句中組條件表達(dá)式的那些組。按SELECT子句中給出的列名或列表達(dá)式求值輸出。ORDER子句對(duì)輸出的目標(biāo)表進(jìn)行排序，按附加說明ASC升序排列，或按DESC降序排列。3.3查詢本節(jié)所用到的數(shù)據(jù)庫(kù)：學(xué)生-課程數(shù)據(jù)庫(kù) 學(xué)生表：Student(Sno，Sname，Ssex，Sage，Sdept)

學(xué)號(hào)sno姓名sname性別ssex年齡sage所在系Sdept95001李勇男20CS95002劉晨女19IS95003王敏女18MA95004張立男19IS3.3查詢課程表：Course(Cno，Cname，Cpno，Ccredit)課程號(hào)Cno課程名Cname先行課Cpno學(xué)分Ccredit1數(shù)據(jù)庫(kù)542數(shù)學(xué)

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

27PASCAL語(yǔ)言643.3查詢 學(xué)生選課表：SC(Sno，Cno，Grade)學(xué)號(hào)Sno課程號(hào)Cno成績(jī)Grade95001192950012859500138895002290950023803.3查詢單表查詢－－只涉及到一個(gè)表的查詢一、選擇表中的若干列(π)[例1]查詢?nèi)w學(xué)生的學(xué)號(hào)與姓名及所在系別。SELECTSno，Sname,Sdept

FROMStudent；

//等價(jià)于πsno,Sname,Sdept

(Student)可以用AS子句重新指定查詢結(jié)果列名的顯示上例可以寫成：SELECTSnoAS學(xué)號(hào),SnameAS姓名,SdeptAS所在系

FROMStudent[例2]查詢?nèi)苛?。SELECTSno,Sname,Ssex，Sage，Sdept

FROMStudent；

或SELECT* //表示將表中的列全部按序輸出FROMStudent；3.3查詢1）目標(biāo)列(SELECT子句)為表達(dá)式的查詢目標(biāo)列形式：算術(shù)表達(dá)式、字符串常量、函數(shù)、列別名等。[例3]查詢?nèi)w學(xué)生的姓名及其出生年份。 SELECTSname,YEAR(DETDATE())-ageAS出生年份

FROMStudent；

輸出結(jié)果：

Sname

出生年份----------------------

李勇1986劉晨1987王敏1988張立1987

3.3查詢[例4]查詢?nèi)w學(xué)生的姓名、出生年份和所在系，要求用小寫字母表示所在系名。

SELECTSname，'出生年份'，2006-Sage，LOWER(Sdept)FROMStudent；輸出結(jié)果：

Sname

出生年份2006-SageLOWER(Sdept)-------------------------------------------------

李勇出生年份1986 cs

劉晨出生年份1987 is

王敏出生年份1988 ma

張立出生年份1987 is3.3查詢3）消除結(jié)果中取值重復(fù)的行－－在SELECT子句中使用DISTINCT短語(yǔ)。[例65]

查詢選修了課程的學(xué)生學(xué)號(hào)。(1)SELECTSnoFROMSC;或(默認(rèn)ALL)SELECTALLSnoFROMSC;結(jié)果：Sno

-------9500195001950019500295002

(2)SELECTDISTINCT

SnoFROMSC;結(jié)果：

Sno

-------9500195002

3.3查詢注意：DISTINCT短語(yǔ)的作用范圍是所有目標(biāo)列例：查詢選修課程的各種成績(jī)錯(cuò)誤的寫法

SELECTDISTINCTCno，DISTINCTGrade FROMSC;正確的寫法

SELECTDISTINCTCno，Grade FROMSC;

注：在投影(π)運(yùn)算的定義中直接去掉了結(jié)果中的重復(fù)元組，在SQL中必須在SELECT子句中用DISTINCT明確指定才能去掉重復(fù)列。3.3查詢二、選擇表中的滿足條件的若干元組(σ)－－使用WHERE子句WHERE子句常用的查詢條件：AND，OR多重條件ISNULL，ISNOTNULL空值LIKE，NOTLIKE字符匹配IN，NOTIN確定集合BETWEENAND，NOTBETWEENAND確定范圍=，<，>，<=，>=，<>，!=，!>，!<；NOT+上述比較符比較謂詞查詢條件3.3查詢1）使用比較運(yùn)算符確定元組[例6]查詢計(jì)算機(jī)系全體學(xué)生的信息。 SELECT* FROMStudent WHERESdept=‘CS’; //等價(jià)于σSdept

=‘CS'(Student)[例7]查詢所有年齡在18歲以下的學(xué)生姓名及其年齡。

SELECTSname，Sage

FROMStudentWHERESage<18；或

SELECTSname,Sage FROMStudent WHERENOTSage>=18；注：不等于在SQL中不能寫成“≠”，應(yīng)該為“<>”，Why?3.3查詢[例8]查詢考試成績(jī)有不及格的學(xué)生的學(xué)號(hào)。 SELECTDISTINCTSno

FROMSC WHERE(Grade<60)3.3查詢2）確定范圍[例9]查詢年齡在18～23歲（包括20歲和23歲）之間的學(xué)生的姓名、系別和年齡。

SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN18AND23；[例10]

查詢年齡不在18～23歲之間的學(xué)生姓名、系別和年齡。SELECTSname,Sdept,SageFROMStudentWHERESageNOTBETWEEN18AND23；3.3查詢3）確定集合[例11]

查詢信息系（IS）和計(jì)算機(jī)科學(xué)系（CS）學(xué)生的姓名和性別。 SELECTSname,SsexFROMStudentWHERESdeptIN('IS','CS')；[例12]

查詢不是信息系（IS）和計(jì)算機(jī)科學(xué)系（CS）學(xué)生的姓名和性別。 SELECTSname,SsexFROMStudentWHERESdeptNOTIN('IS','CS')；3.3查詢4）字符匹配[NOT]LIKE‘<匹配串>’[ESCAPE‘<換碼字符>’]<匹配串>：指定匹配模板。匹配模板：固定字符串或含通配符的字符串。

當(dāng)匹配模板為固定字符串時(shí)，可以用=運(yùn)算符取代LIKE謂詞，用!=或<>運(yùn)算符取代NOTLIKE謂詞。%(百分號(hào))：代表任意長(zhǎng)度（長(zhǎng)度可以為0）的字符串例：a%b表示以a開頭，以b結(jié)尾的任意長(zhǎng)度的字符串。如acb，addgb，ab

等都滿足該匹配串。_(下橫線)：代表任意單個(gè)字符例：a_b表示以a開頭，以b結(jié)尾的長(zhǎng)度為3的任意字符串。如acb，afb等都滿足該匹配串當(dāng)用戶要查詢的字符串本身就含有%或_時(shí)，要使用ESCAPE'<換碼字符>'短語(yǔ)對(duì)通配符進(jìn)行轉(zhuǎn)義。3.3查詢匹配模板為固定字符串

[例13]

查詢學(xué)號(hào)為03830141的學(xué)生的詳細(xì)情況。 SELECT*FROMStudentWHERESnoLIKE'03830141'；等價(jià)于：

SELECT*FROMStudentWHERESno=03830141'；3.3查詢匹配模板為含通配符的字符串[例14]

查詢所有姓張學(xué)生的姓名、學(xué)號(hào)和性別。

SELECTSname,Sno,Ssex FROMStudentWHERESnameLIKE‘張%’；[例15]查詢所有不姓“張”的學(xué)生姓名。 SELECTSname

FROMStudent WHERE(SnameNOTLIKE‘張％’)[例16]查詢姓"司馬"且全名為三個(gè)漢字的學(xué)生的姓名。

SELECTSnameFROMStudentWHERESnameLIKE‘司馬__’；[例17]查詢名字中第2個(gè)字為"馬"字的學(xué)生的姓名和學(xué)號(hào)。

SELECTSname，SnoFROMStudentWHERESnameLIKE'__馬%'；3.3查詢用轉(zhuǎn)義符將通配符轉(zhuǎn)義為普通字符[例18]

查詢C_PROGRAM課程的課程號(hào)和學(xué)分。

SELECTCno，CcreditFROMCourseWHERECnameLIKE‘C\_PROGRAM’ESCAPE‘\’；[例19]

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

SELECT*FROMCourseWHERECnameLIKE‘C\_%i__'ESCAPE'\'；3.3查詢5）涉及空值的查詢使用謂詞ISNULL

或ISNOTNULL“ISNULL”不能用“=NULL”代替[例20]

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

查詢所有有成績(jī)的學(xué)生學(xué)號(hào)和課程號(hào)。

SELECTSno,Cno FROMSC WHEREGradeISNOTNULL；3.3查詢6）多重條件查詢 用邏輯運(yùn)算符AND和OR來(lái)聯(lián)結(jié)多個(gè)查詢條件AND的優(yōu)先級(jí)高于OR可以用括號(hào)改變優(yōu)先級(jí) 可用來(lái)連接多種其他謂詞條件，如：

AθB[NOT]BETWEEN…AND…[NOT]INLIKE，NOTLIKEISNULL，ISNOTNULL可用作其他部分謂詞條件的等價(jià)替換

[NOT]BETWEEN…AND…

[NOT]IN3.3查詢[例23]

查詢計(jì)算機(jī)系年齡在18歲以下的學(xué)生姓名。 SELECTSname FROMStudent WHERESdept='CS'ANDSage<18；[例10]中查詢年齡在18~23歲（包括18歲和23歲）之間的學(xué)生的姓名、系別和年齡。

也可改寫為：

SELECTSname,Sdept,SageFROMStudentWHERESage>=18ANDSage<=23；3.3查詢?nèi)?、?duì)查詢結(jié)果排序輸出使用ORDERBY子句可以按一個(gè)或多個(gè)屬性列排序升序：ASC；降序：DESC；缺省值為升序當(dāng)排序列含空值時(shí)ASC：排序列為空值的元組最后顯示DESC：排序列為空值的元組最先顯示（將空值作為最大值來(lái)理解）3.3查詢[例23]查詢選修了3號(hào)課程的學(xué)生的學(xué)號(hào)及其成績(jī)，查詢結(jié)果按分?jǐn)?shù)降序排列。

SELECTSno,Grade FROMSC WHERECno='3' ORDERBYGradeDESC；查詢結(jié)果：

SnoGrade--------------95001 8895002 803.3查詢[例24]

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

SELECT* FROMStudent ORDERBYSdept,SageDESC；查詢結(jié)果：

Sno

SnameSsexSage

Sdept-----------------------------------95001李勇男 20CS95002劉晨女19IS95004張立男19IS95003

王敏女18MA

3.3查詢四、使用集函數(shù)(AggregateFunctions) 5類主要集函數(shù)：(1)計(jì)數(shù)COUNT（[DISTINCT|ALL]*）COUNT（[DISTINCT|ALL]<列名>）(2)計(jì)算總和SUM（[DISTINCT|ALL]<列名>） (3)計(jì)算平均值A(chǔ)VG（[DISTINCT|ALL]<列名>）(4)求最大值MAX（[DISTINCT|ALL]<列名>） (5)求最小值MIN（[DISTINCT|ALL]<列名>）3.3查詢[例25]

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

SELECTCOUNT(*) FROMStudent；[例26]

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

SELECTCOUNT(DISTINCTSno) FROMSC；

注：用DISTINCT以避免重復(fù)計(jì)算學(xué)生人數(shù)[例27]

查詢選修1號(hào)課程的學(xué)生平均成績(jī)。

SELECTAVG(Grade)FROMSCWHERCno='1'；[例27]

查詢選修1號(hào)課程的學(xué)生平均成績(jī)。

SELECTAVG(Grade)FROMSCWHERCno='1'；[例28]

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

SELECTMAX(Grade)FROMSCWHERCno='1'；3.3查詢五、對(duì)查詢結(jié)果分組輸出

－－使用GROUPBY子句分組

作用：細(xì)化集函數(shù)的作用對(duì)象未對(duì)查詢結(jié)果分組，集函數(shù)將作用于整個(gè)查詢結(jié)果對(duì)查詢結(jié)果分組后，集函數(shù)將分別作用于每個(gè)組GROUPBY子句的作用對(duì)象是查詢的中間結(jié)果表分組方法：按指定的一列或多列值分組，值相等的為一組使用GROUPBY子句后，SELECT子句的列名列表中只能出現(xiàn)分組屬性和集函數(shù)[例29]求各個(gè)課程號(hào)及相應(yīng)的選課人數(shù)。 SELECTCno,COUNT(Sno) FROMSC GROUPBYCno;查詢結(jié)果：

Cno

COUNT(Sno)1222343444335483.3查詢[例30]查詢選修了3門以上課程的學(xué)生學(xué)號(hào)。

SELECTSno FROMSCGROUPBYSnoHAVINGCOUNT(*)>3；先用GROUPBY子句按Sno分組，再用聚集函數(shù)COUNT對(duì)每一組計(jì)數(shù)。HAVING短語(yǔ)指定選擇組的條件，只有滿足條件的組才會(huì)被選出來(lái)。WHERE子句與HAVING短語(yǔ)的區(qū)別在于作用對(duì)象不同。WHERE子句作用于基本表或視圖，從中選擇滿足條件的元組。HAVING短語(yǔ)作用于組，從中選擇滿足條件的組。3.3查詢連接查詢－－將兩個(gè)(以上)表連接進(jìn)行查詢同時(shí)涉及多個(gè)表的查詢連接查詢的意義等價(jià)于關(guān)系代數(shù)中的θ連接、等值連接和自然連接。一、等值與非等值連接查詢[例31]

查詢每個(gè)學(xué)生及其選修課程的情況。SELECTStudent.*,SC.*FROMStudent,SCWHEREStudent.Sno=SC.Sno；//等值連接[例32]自然連接Student和SC表。SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudent,SCWHEREStudent.Sno=SC.Sno；//自然連接注：WHERE子句中參與比較的兩個(gè)屬性名若在另一表中存在，則要加所屬表名作前輟以區(qū)別，無(wú)同名則可省前輟。3.3查詢連接操作的執(zhí)行過程(1)首先在表1中找到第一個(gè)元組，然后從頭開始掃描表2，逐一查找滿足連接件的元組，找到后就將表1中的第一個(gè)元組與該元組拼接起來(lái)，形成結(jié)果表中一個(gè)元組。(2)表2全部查找完后，再找表1中第二個(gè)元組，然后再?gòu)念^開始掃描表2，逐一查找滿足連接條件的元組，找到后就將表1中的第二個(gè)元組與該元組拼接起來(lái)，形成結(jié)果表中一個(gè)元組。(3)重復(fù)上述操作，直到表1中的全部元組都處理完畢提高查詢效率的方法：對(duì)表2按連接字段建立索引對(duì)表1中的每個(gè)元組，依次根據(jù)其連接字段值查詢表2的索引，從中找到滿足條件的元組，找到后就將表1中的第一個(gè)元組與該元組拼接起來(lái)，形成結(jié)果表中一個(gè)元組3.3查詢SnoSnameSsexSageSdept95001李勇男20CS95002劉晨女19IS95003王敏女18MA95004張立男19ISSnoCnoGrade9500119295001285950013889500229095002380Student表SC表Student.snoSnameSsexSageSdeptSC.SnoCnoGrade95001李勇男20CS9500119295001李勇男20CS9500128595001李勇男20CS9500138895002劉晨女19IS9500229095002劉晨女19IS95002380查詢結(jié)果：自然連接去掉此列3.3查詢二、自身連接－－一個(gè)表與其自己進(jìn)行連接[例32]查詢每一門課的間接先修課（即先修課的先修課）。CnoCnameCpnoCcredit1數(shù)據(jù)庫(kù)542數(shù)學(xué)23信息系統(tǒng)144操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理27PASCAL語(yǔ)言64CnoCnameCpnoCcredit5數(shù)據(jù)結(jié)構(gòu)741數(shù)據(jù)庫(kù)546數(shù)據(jù)處理27PASCAL語(yǔ)言646數(shù)據(jù)處理2關(guān)系代數(shù)表達(dá)式：π1,7(σ3=5(SC×SC))元組演算表達(dá)式： {t|?u?v(C(u)∧C(v)∧u[3]=v[1]∧t[1]=u[1]∧t[2]=v[3])}3.3查詢二、自身連接－－一個(gè)表與其自己進(jìn)行連接[例33]查詢每一門課的間接先修課（即先修課的先修課）。

SELECTCA.Cno,CB.Cno FROMCourseCA,CourseCB, WHERECA.Cpno=CB.Cno;注：(1)一個(gè)表與其自己進(jìn)行連接，稱為表的自身連接(2)需要給表起別名以示區(qū)別(3)由于所有屬性名都是同名屬性，因此必須使用別名前綴第三章關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言－SQL上節(jié)回顧查詢的基本語(yǔ)句：SELECT[ALL|DISTINCT][＊|<columns>|<arithmeticexpressions>|<AggregateFunctions>][[AS]<alias>],…FROM<table>WHERE<conditions>GROUPBY<columnN>HAVING<inner-groupconfitions>ORDERBY<columnN>[ASC|DESC]特別注意：集函數(shù)只能用于SELECT子句和HAVING短語(yǔ)之中，而絕對(duì)不能出現(xiàn)在WHERE子句中。WHERE子句用于對(duì)查詢的表限定，而HAVING短語(yǔ)用于對(duì)結(jié)果集中的各分組進(jìn)行限定。3.3查詢?nèi)⑼膺B接（OuterJoin）SnoSnameSsexSageSdept95001李勇男20CS95002劉晨女19IS95003王敏女18MA95004張立男19ISSnoCnoGrade9500119295001285950013889500229095002380Student表SC表Student.snoSnameSsexSageSdeptCnoGrade95001李勇男20CS19295001李勇男20CS28595001李勇男20CS38895002劉晨女19IS29095002劉晨女19IS38095003王敏女18MA95004張立男19IS右外連接：StudentSC3.3查詢外連接與普通連接的區(qū)別普通連接操作只輸出滿足連接條件的元組外連接操作以指定表為連接主體，將主體表中不滿足連接條件的元組一并輸出外連接的構(gòu)建非主體表有一“萬(wàn)能”的虛行，該行全部由空值組成虛行可以和主體表中所有不滿足連接條件的元組進(jìn)行連接由于虛行各列全部是空值，因此與虛行連接的結(jié)果中，來(lái)自非主體表的屬性值全部是空值外連接的類別左外連接－－在連接條件的左邊出現(xiàn)空行()右外連接－－在連接條件的右邊出現(xiàn)空行()左右外連接－－在連接條件的左右兩邊出現(xiàn)空行()3.3查詢[例34]查詢每個(gè)學(xué)生及其選修課程的情況(含未選課的學(xué)生信息)。SELECTStudent.*,SC.Cno,SC.Grade FROMStudentLEFTJOINSCONStudent.Sno=SC.Sno等價(jià)于：

SELECTStudent.*,SC.Cno,SC.Grade FROMStudent,SCWHEREStudent.Sno*=SC.Sno注：在WHERE子句中的表名后面加外連接操作符(*)處于比較運(yùn)算符的“*”左、右的位置表明了DBMS執(zhí)行LEFTJOIN或RIGHTJOIN的差別。3.3查詢四、復(fù)合條件連接－－WHERE子句中含多個(gè)連接條件[例35]查詢選修C1號(hào)課程且成績(jī)?cè)?5分以上的所有學(xué)生的學(xué)號(hào)、姓名。SELECTStudent.Sno,student.SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoAND//連接謂詞 SC.Cno=‘C1'AND//其他限定條件

SC.Grade>90；//其他限定條件[例36]查詢每個(gè)學(xué)生的學(xué)號(hào)、姓名、選修的課程名及成績(jī)。

SELECTStudent.Sno，Sname，Cname，GradeFROMStudent，SC，CourseWHEREStudent.Sno=SC.SnoAND

SC.Cno=Course.Cno；3.3查詢嵌套查詢一個(gè)SELECT-FROM-WHERE語(yǔ)句稱為一個(gè)查詢塊將一個(gè)查詢塊嵌套在另一個(gè)查詢塊的WHERE子句或HAVING短語(yǔ)的條件中的查詢稱為嵌套查詢SELECTSname //

外層查詢/父查詢

FROMStudentWHERESnoIN (SELECTSno//

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

FROMSC WHERECno='2'）；子查詢的限制－－不能使用ORDERBY子句層層嵌套方式反映了SQL語(yǔ)言的結(jié)構(gòu)化有些嵌套查詢可以用連接運(yùn)算替代3.3查詢一、帶有IN謂詞的子查詢[例37]查詢與“張震”在同一個(gè)系學(xué)習(xí)的學(xué)生。SELECT* FROMStudentWHERESdeptIN( SELECTSdept FROMStudent WHERESname=’張震’)3.3查詢[例38]查詢選修了課程名為“數(shù)據(jù)庫(kù)”的學(xué)生學(xué)號(hào)和姓名。SELECTSno,SnameFROMStudentWHERESnoIN(SELECTSnoFROMSCWHERECno＝(SELECTCnoFROMCourseWHERECname='數(shù)據(jù)庫(kù)'

));①首先在Course關(guān)系中找出“數(shù)據(jù)庫(kù)”的課程號(hào)；②然后在SC關(guān)系中找出選修了該課程的學(xué)生學(xué)號(hào)；③最后在Student關(guān)系中取出Sno和Sname。查詢執(zhí)行過程：3.3查詢?nèi)?、帶有ANY或ALL謂詞的子查詢

謂詞語(yǔ)義：(1)ANY(SOME)：某些值(2)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é)果中的所有值（通常沒有實(shí)際意義）!=（或<>）ANY 不等于子查詢結(jié)果中的某個(gè)值!=（或<>）ALL 不等于子查詢結(jié)果中的任何一個(gè)值3.3查詢[例39]查詢其他系中比信息系某些學(xué)生年齡小的學(xué)生姓名和年齡。

SELECTSname,SageFROMStudentWHERESage<ANY(SELECTSage FROMStudent WHERESdept='IS')

ANDSdept<>'IS';//這是父查詢塊中的條件執(zhí)行過程1.DBMS執(zhí)行此查詢時(shí)，首先處理子查詢，找出IS系中所有學(xué)生的年齡，構(gòu)成一個(gè)集合(19，18)2.處理父查詢，找所有不是IS系且年齡小于19或18的學(xué)生結(jié)論：是不相關(guān)子查詢3.3查詢[例39]查詢其他系中比信息系某些學(xué)生年齡小的學(xué)生姓名和年齡。 使用集函數(shù)來(lái)實(shí)現(xiàn)，即其他系中比信息系最大年齡小的學(xué)生的姓名和年齡。

SELECTSname,SageFROMStudentWHERESage<(SELECTMAX(Sage) FROMStudent WHERESdept='IS') ANDSdept<>'IS';

3.3查詢[例40]查詢其他系中比信息系所有學(xué)生年齡都小的學(xué)生姓名及年齡。方法一：用ALL謂詞

SELECTSname,SageFROMStudentWHERESage<ALL(SELECTSage FROMStudent WHERESdept='IS') ANDSdept<>'IS';方法二：用集函數(shù)SELECTSname,SageFROMStudentWHERESage<(SELECTMIN(Sage) FROMStudent WHERESdept='IS') ANDSdept<>'IS';3.3查詢ANY和ALL謂詞與集函數(shù)的等價(jià)關(guān)系：

=<>或!=

<<=>>=ANY

IN--

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

<MIN<=MIN>MAX>=MAX注：用集函數(shù)實(shí)現(xiàn)子查詢通常比直接用ANY或ALL查詢效率要高，因?yàn)榍罢咄ǔＤ軌驕p少比較次數(shù)。3.3查詢四、帶有EXISTS謂詞的子查詢EXISTS謂詞的意義：是存在量詞在SQL中的應(yīng)用帶有EXISTS謂詞的子查詢不返回任何數(shù)據(jù)，只產(chǎn)生邏輯真值“true”或邏輯假值“false”。若內(nèi)層查詢結(jié)果非空，則返回真值若內(nèi)層查詢結(jié)果為空，則返回假值由EXISTS引出的子查詢，其目標(biāo)列表達(dá)式通常都用*，因?yàn)閹XISTS的子查詢只返回真值或假值，給出列名無(wú)實(shí)際意義3.3查詢[例41]查詢所有選修了C1號(hào)課程的學(xué)生姓名。 元組演算表達(dá)式： {t|?u?v(S(u)∧SC(v)∧u[1]=v[1]∧v[2]=‘C1’∧t[1]=u[2])}(1)元組演算公式是邏輯謂詞，它由一組原子公式經(jīng)過聯(lián)結(jié)詞進(jìn)行關(guān)聯(lián)，表達(dá)了查詢結(jié)果集中的元組應(yīng)當(dāng)滿足的條件，可以理解為它由查詢需要的表、條件限定和賦值三部分組成(這種解釋并不正確，但有助于我們寫出正確的元組演算表大式)。(2)第一個(gè)?和最后的賦值部分可以轉(zhuǎn)換為SELECT子句和FROM子句。(3)第二個(gè)及以后的?和條件限定語(yǔ)句可以轉(zhuǎn)換為子查詢作為WHERE子句的條件，?翻譯為EXISTS。3.3查詢[例41]查詢所有選修了C1號(hào)課程的學(xué)生姓名。 元組演算表達(dá)式： {t|?u?v(S(u)∧SC(v)∧u[1]=v[1]∧v[2]=‘C1’∧t[1]=u[2])}}SELECTSnameFROMStudentWHEREEXISTS (SELECT* FROMSC

WHERESno=Student.SnoAND Cno='C1')；＊此查詢可以理解為：輸出那些學(xué)生的姓名，當(dāng)在選課表中存在他(她)選修C1號(hào)課的記錄。3.3查詢[例41]查詢所有選修了C1號(hào)課程的學(xué)生姓名。SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoAND

Cno='C1')；此查詢的執(zhí)行過程：(1)首先取外層查詢中表的第一個(gè)元組，根據(jù)它與內(nèi)層查詢相關(guān)的屬性值處理內(nèi)層查詢，若WHERE子句返回值為真，則取此元組放入結(jié)果表；(2)然后再取外層表的下一個(gè)元組，重復(fù)這一過程，直至外層表全部檢查完為止。這種查詢稱為相關(guān)子查詢，即子查詢的條件與父查詢當(dāng)前值相關(guān)。3.3查詢[例42]

查詢沒有選修1號(hào)課程的學(xué)生姓名。＊此查詢可以理解為：輸出那些學(xué)生的姓名，當(dāng)在選課表中不存在他(她)選修1號(hào)課的記錄。＊元組演算表達(dá)式：{t|?u┐?v(S(u)∧SC(v)∧u[1]=v[1]∧v[2]=‘c1’∧t[1]=u[2])}＊SQL語(yǔ)句：SELECTSnameFROMStudentWHERENOTEXISTS (SELECT*FROMSCWHERESno=Student.SnoANDCno='C1')；注：此例用連接運(yùn)算無(wú)法實(shí)現(xiàn)！

此查詢?yōu)橄嚓P(guān)子查詢。3.3查詢注:(1)一些帶EXISTS或NOTEXISTS謂詞的子查詢不能被其他形式的子查詢等價(jià)替換

(2)所有帶IN謂詞、比較運(yùn)算符、ANY和ALL謂詞的子查詢都能用帶EXISTS謂詞的子查詢等價(jià)替換。例如查詢與“張震”在同一個(gè)系學(xué)習(xí)的學(xué)生。

可以理解為：輸出那些學(xué)生，當(dāng)他(她)所在系與“張震”所在系相同時(shí)。 用帶EXISTS謂詞的子查詢替換：

SELECTSno,Sname,SdeptFROMStudentS1WHEREEXISTS(SELECT* FROMStudentS2WHERES2.Sdept=S1.SdeptAND S2.Sname=‘張震'；3.3查詢用EXISTS/NOTEXISTS實(shí)現(xiàn)全稱量詞(難點(diǎn))SQL語(yǔ)言中沒有全稱量詞(ForAll)可以把帶有全稱量詞的謂詞轉(zhuǎn)換為等價(jià)的帶有存在量詞的謂詞：

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

元組演算表達(dá)式：{t|?u(S(u)∧?v(C(v)→?w(SC(w)∧w[1]=u[1]∧w[2]=v[1]))∧

t[1]=u[2])}{t|?u(S(u)∧?v(C(v)∧?w(SC(w)∧w[1]=u[1]∧w[2]=v[1]))∧

t[1]=u[2])}等價(jià)轉(zhuǎn)換自然語(yǔ)義：輸出這樣的學(xué)生，不存在某門課程，在他的選課記錄里沒有選這門課。3.3查詢[例43]查詢選修了全部課程的學(xué)生姓名。＊元組演算表達(dá)式{t|?u(S(u)∧?v(C(v)∧?w(SC(w)∧w[1]=u[1]∧w[2]=v[1]))∧t[1]=u[2])}＊

SQL查詢語(yǔ)句：

SELECTSnameFROMStudentWHERENOTEXISTS

(SELECT*FROMCourseWHERENOTEXISTS

(SELECT*FROMSCWHERESno=Student.SnoANDCno=Course.Cno

));3.3查詢[例44]查詢至少選修學(xué)生S1選修的全部課程的學(xué)生號(hào)碼。元組演算表達(dá)式：{t|?u(SC(u)∧?v(SC(v)∧v[1]=‘S1’→?w(SC(w)∧w[1]=u[1]∧w[2]=v[2]))∧t[1]=u[1])}等價(jià)轉(zhuǎn)換{t|?u(SC(u)∧?v(SC(v)∧v[1]=‘S1’∧?w(SC(w)∧w[1]=u[1]∧w[2]=v[2]))∧t[1]=u[1])}自然語(yǔ)義： 輸出這樣的學(xué)生，不存‘S1’選了的課程，在他的選課記錄中沒有出現(xiàn)。3.3查詢思考查詢至少選修學(xué)生95002選修的全部課程的學(xué)生號(hào)碼。＊元組演算表達(dá)式：{t|?u(SC(u)∧?v(SC(v)∧v[1]=‘95002’∧?w( SC(w)∧w[1]=u[1]∧w[2]=v[2]))∧t[1]=u[1])}＊SQL查詢語(yǔ)句：

SELECTDISTINCTSnoFROMSCSCXWHERENOTEXISTS(SELECT*FROMSCSCYWHERESCY.Sno='95002'ANDNOTEXISTS (SELECT*FROMSCSCZWHERESCZ.Sno=SCX.SnoAND

SCZ.Cno=SCY.Cno))；第三章關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言－SQL上節(jié)回顧連接查詢是通過連接條件(θ連接、等值連接和自然連接）將多個(gè)表連接成一張大表，然后從大表中查詢出結(jié)果集。嵌套查詢分為相關(guān)子查詢和不相關(guān)子查詢(1)不相關(guān)子查詢的執(zhí)行不依賴于父查詢的任何條件語(yǔ)句上不出現(xiàn)父查詢中的屬性執(zhí)行上首先運(yùn)行，且只運(yùn)行一次即可得到確定的結(jié)果集子查詢的結(jié)果集將作為父查詢的條件使用(2)相關(guān)子查詢的執(zhí)行與父查詢的當(dāng)前值相關(guān)語(yǔ)句上出現(xiàn)父查詢中的屬性執(zhí)行上父查詢的當(dāng)前值會(huì)作為子查詢的條件(3)子查詢的屬性不會(huì)出現(xiàn)在父查詢的輸出上(Select子句)3.3查詢集合查詢－－將兩個(gè)SELECT-FROM-WHERE查詢塊用集合操作語(yǔ)句聯(lián)結(jié)起來(lái)。集合操作命令：并操作(UNION)交操作(INTERSECT)差操作(MINUS)語(yǔ)句形式 <查詢塊>

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

SELECT*FROMStudentWHERESdept=‘MA'UNIONSELECT*FROMStudentWHERESage<=18；方法二：

SELECTDISTINCT*FROMStudentWHERESdept=‘MA'ORSage<=18；執(zhí)行效率分析

如果分別對(duì)Sdept和sage建立索引，則使用方法一的效率高。3.3查詢[例46]查詢數(shù)學(xué)系的學(xué)生與年齡小于18歲的學(xué)生的交集。SELECT* FROMStudentWHERESdept=’MA’ANDSage<=183.3查詢[例47]查詢數(shù)學(xué)系的學(xué)生與年齡不大于18歲的學(xué)生的差集。 方法一SELECTSnoFROMStudentWHERESdept='CS'ANDSage<=18;

方法二SELECTSnoFROMStudentWHEREStept='CS'MINUSSELECTSnoFROMStudentWHERESage>'18';3.3查詢查詢綜合舉例及注意事項(xiàng)一、別名的使用 (1)別名用于對(duì)輸出屬性列的重命名例：查詢?nèi)w學(xué)生的姓名、出生年份和所在系，并將輸出字段依次更名為：NAME、BIRTH、BIRTHDAY、DEPARTMENT。SELECTSname

NAME，'YearofBirth:’BIRTH