數(shù)據(jù)庫系統(tǒng)概論市公開課一等獎省賽課獲獎?wù)n件

遵義醫(yī)學(xué)院醫(yī)學(xué)信息工程系數(shù)據(jù)庫系統(tǒng)概論AnIntroductiontoDatabaseSystem第三章關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQLAnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第1頁3.1SQL概述SQL特點1.綜合統(tǒng)一2.高度非過程化3.面向集合操作方式4.以同一個語法結(jié)構(gòu)提供兩種使用方法5.語言簡練，易學(xué)易用AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第2頁AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第3頁3.2數(shù)據(jù)定義AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第4頁3.2.1定義語句格式CREATETABLE<表名><列名><數(shù)據(jù)類型>[<列級完整性約束條件>][，<列名><數(shù)據(jù)類型>[<列級完整性約束條件>]]…[，<表級完整性約束條件>]）；<表名>：所要定義基本表名字<列名>：組成該表各個屬性（列）<列級完整性約束條件>：包括對應(yīng)屬性列完整性約束條件<表級完整性約束條件>：包括一個或多個屬性列完整性約束條件AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第5頁例題：[例1]建立一個“學(xué)生”表Student，它由學(xué)號Sno、姓名Sname、性別Ssex、年紀(jì)Sage、所在系Sdept五個屬性組成。其中學(xué)號不能為空，值是唯一，而且姓名取值也唯一。CREATETABLEStudent(SnoCHAR(5)NOTNULLUNIQUE，SnameCHAR(20)UNIQUE，SsexCHAR(1)，SageINT，SdeptCHAR(15))；AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第6頁定義基本表慣用完整性約束主碼約束：PRIMARYKEY唯一性約束：UNIQUE非空值約束：NOTNULL參考完整性約束PRIMARYKEY與UNIQUE區(qū)分？AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第7頁例題：[例2]建立一個“學(xué)生選課”表SC，它由學(xué)號Sno、課程號Cno，修課成績Grade組成，其中(Sno,Cno)為主碼。CREATETABLESC(SnoCHAR(5),CnoCHAR(3),Gradeint,Primarykey(Sno,Cno));AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第8頁例題AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第9頁三、刪除基本表 DROPTABLE<表名>;

基本表刪除數(shù)據(jù)、表上索引都刪除表上視圖往往依然保留，但無法引用刪除基本表時，系統(tǒng)會從數(shù)據(jù)字典中刪去相關(guān)該基本表及其索引描述。（標(biāo)準(zhǔn)中沒有，認(rèn)為表建立后就永久存在）AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第10頁例題[例5]刪除Student表DROPTABLEStudent;AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第11頁二、修改基本表ALTERTABLE<表名>[ADD<新列名><數(shù)據(jù)類型>[完整性約束]][DROP<完整性約束名>][MODIFY<列名><數(shù)據(jù)類型>]；<表名>：要修改基本表ADD子句：增加新列和新完整性約束條件DROP子句：刪除指定完整性約束條件MODIFY子句：用于修改列名和數(shù)據(jù)類型AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第12頁例題[例2]向Student表增加“入課時間”列，其數(shù)據(jù)類型為日期型。ALTERTABLEStudentADDScomeDATE；不論基本表中原來是否已經(jīng)有數(shù)據(jù)，新增加列一律為空值。

AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第13頁語句格式刪除屬性列直接/間接刪除把表中要保留列及其內(nèi)容復(fù)制到一個新表中刪除原表再將新表重命名為原表名直接刪除屬性列:(新)例：ALTERTABLEStudentDropScome；AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第14頁

[例3]將年紀(jì)數(shù)據(jù)類型改為半字長整數(shù)。ALTERTABLEStudentMODIFYSageSMALLINT；注：修改原有列定義有可能會破壞已經(jīng)有數(shù)據(jù)AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第15頁例題[例4]刪除學(xué)生姓名必須取唯一值約束。 ALTERTABLEStudentDROPUNIQUE(Sname)；AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第16頁3.2.2建立與刪除索引建立索引是加緊查詢速度有效伎倆建立索引DBA或表屬主（即建立表人）依據(jù)需要建立有些DBMS自動建立以以下上索引PRIMARYKEYUNIQUEAnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第17頁維護索引DBMS自動完成

使用索引DBMS自動選擇是否使用索引以及使用哪些索引AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第18頁一、建立索引語句格式CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…)； 用<表名>指定要建索引基本表名字索引能夠建立在該表一列或多列上，各列名之間用逗號分隔用<次序>指定索引值排列次序，升序：ASC，降序：DESC。缺省值：ASCUNIQUE表明此索引每一個索引值只對應(yīng)唯一數(shù)據(jù)統(tǒng)計CLUSTER表示要建立索引是聚簇索引AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第19頁例題：[例6]為學(xué)生-課程數(shù)據(jù)庫中Student，Course，SC三個表建立索引。其中Student表按學(xué)號升序建唯一索引，Course表按課程號升序建唯一索引，SC表按學(xué)號升序和課程號降序建唯一索引。AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第20頁CREATEUNIQUEINDEXStusnoONStudent(Sno)；CREATEUNIQUEINDEXCoucnoONCourse(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第21頁建立索引唯一值索引對于已含重復(fù)值屬性列不能建UNIQUE索引對某個列建立UNIQUE索引后，插入新統(tǒng)計時DBMS會自動檢驗新統(tǒng)計在該列上是否取了重復(fù)值。這相當(dāng)于增加了一個UNIQUE約束AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第22頁聚簇索引建立聚簇索引后，基表中數(shù)據(jù)也需要按指定聚簇屬性值升序或降序存放。也即聚簇索引索引項次序與表中統(tǒng)計物理次序一致AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第23頁例：CREATECLUSTERINDEXStusnameONStudent(Sname)；在Student表Sname（姓名）列上建立一個聚簇索引，而且Student表中統(tǒng)計將按照Sname值升序存放。AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第24頁建立索引在一個基本表上最多只能建立一個聚簇索引聚簇索引用途：對于一些類型查詢，能夠提升查詢效率聚簇索引適用范圍極少對基表進行增刪操作極少對其中變長列進行修改操作AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第25頁二、刪除索引DROPINDEX<索引名>；刪除索引時，系統(tǒng)會從數(shù)據(jù)字典中刪去相關(guān)該索引描述。[例7]刪除Student表Stusname索引。 DROPINDEXStusname；AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第26頁3.3.1數(shù)據(jù)查詢概述語句格式SELECT[ALL|DISTINCT]<目標(biāo)列表示式>[，<目標(biāo)列表示式>]…FROM<表名或視圖名>[，<表名或視圖名>]…[WHERE<條件表示式>][GROUPBY<列名1>[HAVING<條件表示式>]][ORDERBY<列名2>[ASC|DESC]]；

AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第27頁語句格式SELECT子句：指定要顯示屬性列FROM子句：指定查詢對象(基本表或視圖)WHERE子句：指定查詢條件

GROUPBY子句：對查詢結(jié)果按指定列值分組，該屬性列值相等元組為一個組。通常會在每組中作用集函數(shù)。HAVING短語：篩選出只有滿足指定條件組ORDERBY子句：對查詢結(jié)果表按指定列值升序或降序排序AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第28頁示例數(shù)據(jù)庫學(xué)生-課程數(shù)據(jù)庫學(xué)生表：Student(Sno，Sname，Ssex，Sage，Sdept)課程表：Course(Cno，Cname，Cpno，Ccredit)學(xué)生選課表：SC(Sno，Cno，Grade)AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第29頁3.3.2單表查詢查詢僅包括一個表，是一個最簡單查詢操作一、選擇表中若干列二、選擇表中若干元組三、對查詢結(jié)果排序四、使用集函數(shù)五、對查詢結(jié)果分組AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第30頁查詢指定列[例1]查詢?nèi)w學(xué)生學(xué)號與姓名。SELECTSno，SnameFROMStudent；

[例2]查詢?nèi)w學(xué)生姓名、學(xué)號、所在系。SELECTSname，Sno，SdeptFROMStudent；AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第31頁查詢?nèi)苛衃例3]查詢?nèi)w學(xué)生詳細(xì)統(tǒng)計。SELECTSno，Sname，Ssex，Sage，SdeptFROMStudent；或SELECT*FROMStudent；AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第32頁3.查詢經(jīng)過計算值SELECT子句<目標(biāo)列表示式>為表示式算術(shù)表示式字符串常量函數(shù)列別名等AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第33頁3.查詢經(jīng)過計算值[例4]查全體學(xué)生姓名及其出生年份。SELECTSname，-SageFROMStudent；

輸出結(jié)果：Sname-Sage----------------------李勇1976劉晨1977王名1978張立1978AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第34頁3.查詢經(jīng)過計算值[例5]查詢?nèi)w學(xué)生姓名、出生年份和全部系，要求用小寫字母表示全部系名。SELECTSname，‘YearofBirth:’，-Sage，ISLOWER(Sdept)FROMStudent；AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第35頁例題輸出結(jié)果：Sname'YearofBirth:'-SageISLOWER(Sdept)----------------------------------------------李勇YearofBirth:1976cs劉晨YearofBirth:1977is王名YearofBirth:1978ma張立YearofBirth:1977isAnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第36頁[例5.1]使用列別名改變查詢結(jié)果列標(biāo)題SELECTSnameNAME，'YearofBirth:'

BIRTH，-SageBIRTHDAY,ISLOWER(Sdept)DEPARTMENTFROMStudent；輸出結(jié)果：NAMEBIRTHBIRTHDAYDEPARTMENT------------------------------------------------------李勇YearofBirth:1976cs劉晨YearofBirth:1977is王名YearofBirth:1978ma張立YearofBirth:1977isAnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第37頁二、選擇表中若干元組消除取值重復(fù)行查詢滿足條件元組AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第38頁1.消除取值重復(fù)行在SELECT子句中使用DISTINCT短語假設(shè)SC表中有以下數(shù)據(jù) SnoCnoGrade---------------------9500119295001285950013889500229095002380AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第39頁ALL與DISTINCT[例6]查詢選修了課程學(xué)生學(xué)號。(1)SELECTSnoFROMSC; 或(默認(rèn)ALL)SELECTALLSnoFROMSC;

結(jié)果：Sno-------9500195001950019500295002AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第40頁(2)SELECTDISTINCTSnoFROMSC;

結(jié)果：Sno-------9500195002AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第41頁注意DISTINCT短語作用范圍是全部目標(biāo)列例：查詢選修課程各種成績錯誤寫法SELECTDISTINCTCno，DISTINCTGradeFROMSC;正確寫法SELECTDISTINCTCno，GradeFROMSC;

AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第42頁2.查詢滿足條件元組WHERE子句慣用查詢條件AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第43頁(1)比較大小在WHERE子句<比較條件>中使用比較運算符=，>，<，>=，<=，!=或<>，!>，!<，邏輯運算符NOT+比較運算符AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第44頁[例8]查詢?nèi)磕昙o(jì)在20歲以下學(xué)生姓名及其年紀(jì)。SELECTSname，SageFROMStudentWHERESage<20；或SELECTSname，SageFROMStudentWHERENOTSage>=20；AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第45頁(2)確定范圍使用謂詞BETWEEN…AND…NOTBETWEEN…AND…[例10]查詢年紀(jì)在20~23歲（包含20歲和23歲）之間學(xué)生姓名、系別和年紀(jì)。SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第46頁[例11]查詢年紀(jì)不在20~23歲之間學(xué)生姓名、系別和年紀(jì)。SELECTSname，Sdept，SageFROMStudentWHERESageNOTBETWEEN20AND23；AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第47頁(3)確定集合使用謂詞IN<值表>,NOTIN<值表><值表>：用逗號分隔一組取值[例12]查詢信息系（IS）、數(shù)學(xué)系（MA）和計算機科學(xué)系（CS）學(xué)生姓名和性別。SELECTSname，SsexFROMStudentWHERESdeptIN('IS'，'MA'，'CS');AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第48頁(3)確定集合[例13]查詢既不是信息系、數(shù)學(xué)系，也不是計算機科學(xué)系學(xué)生姓名和性別。SELECTSname，SsexFROMStudent WHERESdeptNOTIN('IS'，'MA'，'CS');AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第49頁(4)字符串匹配[NOT]LIKE‘<匹配串>’[ESCAPE‘<換碼字符>’]<匹配串>：指定匹配模板匹配模板：固定字符串或含通配符字符串當(dāng)匹配模板為固定字符串時，能夠用=運算符取代LIKE謂詞用!=或<>運算符取代NOTLIKE謂詞AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第50頁通配符%(百分號)代表任意長度（長度可認(rèn)為0）字符串。例：a%b表示以a開頭，以b結(jié)尾任意長度字符串。如acb，addgb，ab等都滿足該匹配串_(下橫線)代表任意單個字符。例：a_b表示以a開頭，以b結(jié)尾長度為3任意字符串。如acb，afb等都滿足該匹配串AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第51頁ESCAPE短語：當(dāng)用戶要查詢字符串本身就含有%或_時，要使用ESCAPE'<換碼字符>'短語對通配符進行轉(zhuǎn)義。AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第52頁例題1)匹配模板為固定字符串[例14]查詢學(xué)號為95001學(xué)生詳細(xì)情況。SELECT*FROMStudentWHERESnoLIKE'95001'；等價于： SELECT*FROMStudentWHERESno='95001'；AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第53頁2)匹配模板為含通配符字符串[例15]查詢?nèi)啃談W(xué)生姓名、學(xué)號和性別。SELECTSname，Sno，SsexFROMStudentWHERESnameLIKE‘劉%’；AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第54頁[例16]查詢姓"歐陽"且全名為三個漢字學(xué)生姓名。SELECTSnameFROMStudentWHERESnameLIKE'歐陽__'；AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第55頁[例17]查詢名字中第2個字為"陽"字學(xué)生姓名和學(xué)號。SELECTSname，SnoFROMStudentWHERESnameLIKE'__陽%'；AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第56頁[例18]查詢?nèi)坎恍談W(xué)生姓名。SELECTSname，Sno，SsexFROMStudentWHERESnameNOTLIKE'劉%'；AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第57頁[例19]查詢DB_Design課程課程號和學(xué)分。SELECTCno，CcreditFROMCourseWHERECnameLIKE'DB\_Design'

ESCAPE'\'3)使用換碼字符將通配符轉(zhuǎn)義為普通字符AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第58頁[例20]查詢以"DB_"開頭，且倒數(shù)第3個字符為i課程詳細(xì)情況。SELECT*FROMCourseWHERECnameLIKE'DB\_%i__'ESCAPE'\'；AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第59頁(5)包括空值查詢使用謂詞ISNULL或ISNOTNULL“ISNULL”不能用“=NULL”代替[例21]一些學(xué)生選修課程后沒有參加考試，所以有選課統(tǒng)計，但沒有考試成績。查詢?nèi)狈Τ煽儗W(xué)生學(xué)號和對應(yīng)課程號。SELECTSno，CnoFROMSCWHEREGradeISNULL；AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第60頁[例22]查全部有成績學(xué)生學(xué)號和課程號。SELECTSno，CnoFROMSCWHEREGradeISNOTNULL；AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第61頁(6)多重條件查詢用邏輯運算符AND和OR來聯(lián)結(jié)多個查詢條件AND優(yōu)先級高于OR能夠用括號改變優(yōu)先級可用來實現(xiàn)各種其它謂詞[NOT]IN[NOT]BETWEEN…AND…AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第62頁例題：[例23]查詢計算機系年紀(jì)在20歲以下學(xué)生姓名。SELECTSnameFROMStudentWHERESdept='CS'ANDSage<20；AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第63頁改寫[例12][例12]查詢信息系（IS）、數(shù)學(xué)系（MA）和計算機科學(xué)系（CS）學(xué)生姓名和性別。SELECTSname，SsexFROMStudentWHERESdeptIN('IS'，'MA'，'CS')可改寫為：SELECTSname，SsexFROMStudentWHERESdept='IS'ORSdept='MA'ORSdept='CS'；AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第64頁改寫[例10][例10]查詢年紀(jì)在20~23歲（包含20歲和23歲）之間學(xué)生姓名、系別和年紀(jì)。

SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；可改寫為：SELECTSname，Sdept，SageFROMStudentWHERESage>=20ANDSage<=23；AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第65頁三、對查詢結(jié)果排序 使用ORDERBY子句能夠按一個或多個屬性列排序升序：ASC；降序：DESC；缺省值為升序當(dāng)排序列含空值時ASC：排序列為空值元組最終顯示DESC：排序列為空值元組最先顯示AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第66頁[例24]查詢選修了3號課程學(xué)生學(xué)號及其成績，查詢結(jié)果按分?jǐn)?shù)降序排列。SELECTSno，GradeFROMSCWHERECno='3'ORDERBYGradeDESC；AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第67頁查詢結(jié)果SnoGrade--------------9501095024950079295003829501082950097595014619500255AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第68頁[例25]查詢?nèi)w學(xué)生情況，查詢結(jié)果按所在系系號升序排列，同一系中學(xué)生按年紀(jì)降序排列。SELECT*FROMStudentORDERBYSdept，SageDESC；AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第69頁四、使用集函數(shù)5類主要集函數(shù)計數(shù)COUNT（[DISTINCT|ALL]*）COUNT（[DISTINCT|ALL]<列名>）計算總和SUM（[DISTINCT|ALL]<列名>） 計算平均值A(chǔ)VG（[DISTINCT|ALL]<列名>）AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第70頁求最大值MAX（[DISTINCT|ALL]<列名>） 求最小值MIN（[DISTINCT|ALL]<列名>） DISTINCT短語：在計算時要取消指定列中重復(fù)值A(chǔ)LL短語：不取消重復(fù)值A(chǔ)LL為缺省值A(chǔ)nIntroductiontoDatabaseSystem數(shù)據(jù)庫系統(tǒng)概論第71頁[例26]查詢學(xué)生總?cè)藬?shù)。SELECTCOUNT(*)FROMStudent；

[例27]查詢選修了課程學(xué)生人數(shù)。SELECTCOUNT(DISTINCTSno)FROMSC；注：用DISTINCT以防止重復(fù)計算學(xué)生人數(shù)AnIntroductiontoDatabaseSystem數(shù)據(jù)庫系