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

AnIntroductiontoDatabaseSystem中國人民大學信息學院

王珊數(shù)據(jù)庫系統(tǒng)概論AnIntroductiontoDatabaseSystem第三章關系數(shù)據(jù)庫標準語言SQLAnIntroductiontoDatabaseSystem第三章

關系數(shù)據(jù)庫標準語言SQL3.1SQL概述3.2學生-課程數(shù)據(jù)庫3.3數(shù)據(jù)定義3.4數(shù)據(jù)查詢3.5數(shù)據(jù)更新3.6視圖3.7小結(jié)AnIntroductiontoDatabaseSystem關系數(shù)據(jù)庫的3級模式結(jié)構(gòu)SQL視圖2視圖1基本表2基本表1基本表3基本表4存儲文件2存儲文件1外模式模式內(nèi)模式復習第1章外模式—視圖+部分基本表

3級模式的概念：模式——基本表

內(nèi)模式—存儲文件AnIntroductiontoDatabaseSystem關系模型的數(shù)據(jù)結(jié)構(gòu)基本表本身獨立存在的表SQL中一個關系就對應一個基本表一個(或多個)基本表對應一個存儲文件一個表可以帶若干索引存儲文件邏輯結(jié)構(gòu)組成了關系數(shù)據(jù)庫的內(nèi)模式物理結(jié)構(gòu)是任意的，對用戶透明視圖從一個或幾個基本表導出的表數(shù)據(jù)庫中只存放視圖的定義而不存放視圖對應的數(shù)據(jù)視圖是一個虛表用戶可以在視圖上再定義視圖AnIntroductiontoDatabaseSystem3.3數(shù)據(jù)定義3.3.1模式的定義與刪除3.3.2基本表的定義、刪除與修改3.3.3索引的建立與刪除SequentialFile201040306050807010090SequentialFile按照某個屬性排序的文件順序文件上的索引SequentialFile201040306050807010090稠密索引（DenseIndex）102030405060708090100110120OneitemforeachRecordItem:(Searchkey,pointertorecord)B樹和B+樹B樹能自動保持與數(shù)據(jù)文件大小適應的索引層次平衡樹能對所使用的存儲空間進行管理，使每個塊處于全滿半滿之間B+樹特殊的B樹，內(nèi)部結(jié)點只存儲索引塊葉結(jié)點用指針連接4.3.1B+樹指向碼值為

31的記錄指向碼值為

37的記錄指向碼值為

41的記錄指向順序集上的

下一個葉結(jié)點典型的葉結(jié)點指向碼值

K<23指向碼值

23≤K<31指向碼值

31≤K<43指向碼值

K≥43典型的非葉結(jié)點參數(shù)n=3:每個塊存放碼值的最大個數(shù):3最小碼數(shù)：m最小指針數(shù)：pB+樹指針和碼的數(shù)量最大指針數(shù)最大碼數(shù)最小指針數(shù)(指向數(shù)據(jù))最小碼數(shù)內(nèi)部結(jié)點n+1n(n+1)/2(n+1)/2-1葉結(jié)點n+1n(n+1)/2(n+1)/2根結(jié)點n+1n11AnIntroductiontoDatabaseSystemB+樹中的查找查找鍵為37的記錄13<3731<37<43找到37鍵值為37的記錄IO數(shù)：4若把第一、第二層結(jié)點保存在緩沖區(qū)

IO數(shù)：2AnIntroductiontoDatabaseSystemB+樹中的查找(若索引塊全在磁盤)查找鍵為37的記錄讀入root節(jié)點,IO=1根據(jù)條件13<37,讀取下一節(jié)點塊讀入下一節(jié)點,IO=2根據(jù)條件31<37<43,讀取下一節(jié)點塊讀入下一節(jié)點,IO=3,找到鍵值37,讀取記錄指針,讀取記錄所在的塊MainMemoryDisk讀入數(shù)據(jù)塊,IO=4,根據(jù)記錄指針找到鍵值為37的記錄AnIntroductiontoDatabaseSystemB+樹中的查找

(若第一、二層索引塊在內(nèi)存)查找鍵為37的記錄讀取root節(jié)點,根據(jù)條件13<37,讀取下一節(jié)點塊讀取下一節(jié)點,根據(jù)條件31<37<43,讀取下一節(jié)點塊讀入下一節(jié)點,IO=1,找到鍵值37,讀取記錄指針,讀取記錄所在的塊MainMemoryDisk讀入數(shù)據(jù)塊,IO=2,根據(jù)記錄指針找到鍵值為37的記錄B+樹的插入三種情況：葉結(jié)點滿內(nèi)部結(jié)點滿動作否否把碼放在葉結(jié)點合適的位置是否1.分裂葉結(jié)點2.把碼的中間值放在內(nèi)部結(jié)點合適的位置3.左側(cè)的葉結(jié)點包括小于中間值的碼4.右側(cè)的葉結(jié)點包括大于或等于中間值的碼是是1.分裂葉節(jié)點2.左側(cè)的葉結(jié)點包括小于中間值的碼3.右側(cè)的葉結(jié)點包括大于或等于中間值的碼4.分裂內(nèi)部結(jié)點5.左側(cè)的內(nèi)部結(jié)點包括小于中間值的碼6.右側(cè)的內(nèi)部結(jié)點包括大于中間值的碼7.把碼的中間值放在上一級內(nèi)部結(jié)點合適的位置如果上一級內(nèi)部結(jié)點已滿，則繼續(xù)分裂上一級結(jié)點B+樹的插入插入碼40

（屬于情況3）401.分裂葉結(jié)點2.碼中間值上提

分裂內(nèi)部結(jié)點3.碼中間值上提(40,b)B+樹的插入插入碼40

（屬于情況3）1.分裂葉結(jié)點2.碼中間值上提

分裂內(nèi)部結(jié)點3.碼中間值上提13>4031<40<434041葉結(jié)點滿4340B+樹的刪除三種情況：葉結(jié)點小于最小充滿度內(nèi)部結(jié)點小于最小充滿度動作否否1.刪除葉結(jié)點中的碼2.對碼重新排序3.如果碼在上一級內(nèi)部結(jié)點出現(xiàn)，則用下一個碼替換是否1.組合葉結(jié)點和它的兄弟結(jié)點2.相應更改內(nèi)部結(jié)點以反映這種改變是是1.組合葉結(jié)點和它的兄弟結(jié)點2.相應更改內(nèi)部結(jié)點以反映這種改變3.組合內(nèi)部結(jié)點和它的兄弟結(jié)點

繼續(xù)組合內(nèi)部結(jié)點，直到找到一個滿足充滿度的結(jié)點或者到達根結(jié)點B+樹的刪除刪除碼7（屬于情況2）551.組合葉結(jié)點2.更改內(nèi)部結(jié)點以反映這種改變B+樹的刪除-刪除碼77<137<=74143577刪除碼7（屬于情況2）1.組合葉結(jié)點2.更改內(nèi)部結(jié)點以反映這種改變5B+樹的刪除刪除碼11（屬于情況3，需要遞歸更改內(nèi)部結(jié)點）1.組合葉結(jié)點5132.更改內(nèi)部結(jié)點以反映這種改變（需要遞歸）233143B+樹的刪除-刪除碼1111<135<11414351.組合葉結(jié)點2.更改內(nèi)部結(jié)點以反映這種改變5133123刪除碼11（屬于情況3，需要遞歸更改內(nèi)部結(jié)點）1113AnIntroductiontoDatabaseSystem3.4數(shù)據(jù)查詢3.4.1單表查詢3.4.2連接查詢3.4.3嵌套查詢3.4.4集合查詢3.4.5Select語句的一般形式

AnIntroductiontoDatabaseSystem一、等值與非等值連接查詢等值連接：連接運算符為=[例33]查詢每個學生及其選修課程的情況

SELECTStudent.*，SC.* FROMStudent，SC WHEREStudent.Sno=SC.Sno；

/*連接謂詞*/AnIntroductiontoDatabaseSystem等值與非等值連接查詢（續(xù)）Student.SnoSnameSsexSageSdeptSC.SnoCnoGrade200215121李勇男20CS200215121192200215121李勇男20CS200215121285200215121李勇男20CS200215121388200215122劉晨女19CS200215122290200215122劉晨女19CS200215122380查詢結(jié)果：AnIntroductiontoDatabaseSystem等值與非等值連接查詢（續(xù)）自然連接：[例34]對[例33]用自然連接完成。

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

Cno，GradeFROMStudent，SCWHEREStudent.Sno=SC.Sno；AnIntroductiontoDatabaseSystem連接操作的執(zhí)行:嵌套循環(huán)法(NESTED-LOOP)

select*froms,spjwheres.sno=spj.sno;S表SNOSNAMESTATUSCITYS1精益20天津S2盛錫10北京S3東方紅30北京S4豐泰盛20天津S5為民30上海SPJ表SNOPNOJNOQTYS1P1J1200S2P1J3100S1P1J4700S1P2J2100S2P3J1400S2P3J2200S2P3J4500S2P3J5400S2P5J1400S2P5J2100S5P1J1200S3P3J1200S4P5J1100S4P6J3300S4P6J4200S3P2J4100S5P3J1200S1P6J2200S5P6J4500resultsSNOSNAMECITYQTYS1精益天津200S1精益天津700S1精益天津100S1精益天津200S2盛錫北京100……AnIntroductiontoDatabaseSystem連接操作的執(zhí)行過程嵌套循環(huán)法(NESTED-LOOP)首先在表1中找到第一個元組，然后從頭開始掃描表2，逐一查找滿足連接件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結(jié)果表中一個元組。表2全部查找完后，再找表1中第二個元組，然后再從頭開始掃描表2，逐一查找滿足連接條件的元組，找到后就將表1中的第二個元組與該元組拼接起來，形成結(jié)果表中一個元組。重復上述操作，直到表1中的全部元組都處理完畢

AnIntroductiontoDatabaseSystem連接操作的執(zhí)行:排序合并法(SORT-MERGE)S表SNOSNAMESTATUSCITYS1精益20天津S2盛錫10北京S3東方紅30北京S4豐泰盛20天津S5為民30上海SPJ表SNOPNOJNOQTYS1P1J1200S1P1J3100S1P1J4700S1P2J2100S2P3J1400S2P3J2200S2P3J4500S2P3J5400S2P5J1400S2P5J2100S3P1J1200S3P3J1200S4P5J1100S4P6J3300S4P6J4200S5P2J4100S5P3J1200S5P6J2200S5P6J4500resultsSNOSNAMECITYQTYS1精益天津200S1精益天津700S1精益天津100S1精益天津200S2盛錫北京100……AnIntroductiontoDatabaseSystem排序合并法(SORT-MERGE)常用于=連接首先按連接屬性對表1和表2排序?qū)Ρ?的第一個元組，從頭開始掃描表2，順序查找滿足連接條件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結(jié)果表中一個元組。當遇到表2中第一條大于表1連接字段值的元組時，對表2的查詢不再繼續(xù)AnIntroductiontoDatabaseSystem排序合并法找到表1的第二條元組，然后從剛才的中斷點處繼續(xù)順序掃描表2，查找滿足連接條件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結(jié)果表中一個元組。直接遇到表2中大于表1連接字段值的元組時，對表2的查詢不再繼續(xù)重復上述操作，直到表1或表2中的全部元組都處理完畢為止

AnIntroductiontoDatabaseSystem索引連接(INDEX-JOIN)

select*froms,spjwheres.sno=spj.sno;S表SNOSNAMESTATUSCITYS1精益20天津S2盛錫10北京S3東方紅30北京S4豐泰盛20天津S5為民30上海SPJ表SNOPNOJNOQTYS1P1