數(shù)據庫原理

PrincipleofDatabaseSystems第三章關系數(shù)據庫標準語言SQL劉潔liujie@2第三章關系數(shù)據庫標準語言SQL3.1SQL概述3.2學生—課程數(shù)據庫3.3數(shù)據定義3.4數(shù)據查詢3.5數(shù)據更新3.6視圖3.7小結33.1SQL概述SQL(StructuredQueryLanguage)

結構化查詢語言，是關系數(shù)據庫的標準語言SQL是一個通用的、功能極強的關系數(shù)據庫語言43.1SQL概述3.1.1SQL的產生與發(fā)展3.1.2SQL的特點3.1.3SQL的基本概念5SQL標準的進展過程標準大致頁數(shù)發(fā)布日期SQL/861986年10月SQL/89

(FIPS127-1)120頁1989年SQL/92622頁1992年SQL991700頁1999年SQL20033600頁2003年63.1.2SQL的特點1.綜合統(tǒng)一2.高度非過程化3.面向集合的操作方式4.以同一種語法結構提供多種使用方式5.語言簡潔，易學易用71.綜合統(tǒng)一SQL集數(shù)據定義語言DDL，數(shù)據操縱語言DML，數(shù)據控制語言DCL的功能于一體可以獨立完成數(shù)據庫生命周期中的全部活動用戶在數(shù)據庫投入運行后，可根據需要隨時逐步修改模式，不影響數(shù)據庫的運行數(shù)據操作符統(tǒng)一，查找、插入、刪除、更新等每一種操作都只需要一種操作符82.高度非過程化非關系數(shù)據模型的數(shù)據操縱語言是“面向過程”的語言，必須指定存取路徑SQL只要提出“做什么”，無須指明“怎么做”，因此不需要了解存取路徑存取路徑的選擇以及SQL的操作過程由系統(tǒng)自動完成93.面向集合的操作方式非關系數(shù)據模型采用面向記錄的操作方式，操作對象是一條記錄SQL采用集合操作方式操作對象、查找結果可以是元組的集合一次插入、刪除、更新操作的對象可以是元組的集合104.以同一種語法結構提供多種使用方式SQL既是獨立的語言，又是嵌入式語言能夠獨立地用于聯(lián)機交互的使用方式SQL能夠嵌入到高級語言（例如C，C++，Java）程序中，供程序員設計程序時使用115.語言簡潔，易學易用SQL功能極強，完成核心功能只用了9個動詞12SQL對關系數(shù)據庫模式的支持(Page81)基本表1基本表2基本表3基本表4存儲文件1存儲文件2視圖1視圖2SQL外模式模式內模式13基本表本身獨立存在的表SQL中一個關系就對應一個基本表一個(或多個)基本表對應一個存儲文件一個表可以帶若干索引，索引也存放在存儲文件中存儲文件邏輯結構組成了關系數(shù)據庫的內模式物理結構是任意的，對用戶透明視圖從一個或幾個基本表導出的表數(shù)據庫中只存放視圖的定義而不存放視圖對應數(shù)據視圖是一個虛表用戶可以在視圖上再定義視圖143.2學生—

課程數(shù)據庫學生-課程模式S-T學生表Student(Sno,Sname,Ssex,Sage,Sdept)課程表：Course(Cno,Cname,Cpno,Ccredit)學生選課表SC(Sno,Cno,Grade)153.3數(shù)據定義SQL的數(shù)據定義功能:模式定義、表定義、視圖定義和索引定義操作對象操作方式創(chuàng)建刪除修改模式CREATESCHEMADROPSCHEMA表CREATETABLEDROPTABLEALTERTABLE視圖CREATEVIEWDROPVIEW索引CREATEINDEXDROPINDEX163.3數(shù)據定義3.3.1模式的定義與刪除3.3.2基本表的定義、刪除與修改3.3.3索引的建立與刪除17一、定義模式定義模式實際上定義了一個命名空間在這個空間中可以進一步定義該模式包含的數(shù)據庫對象，例如基本表、視圖、索引等在CREATESCHEMA中可以接受CREATETABLE，CREATEVIEW和GRANT子句CREATESCHEMA<模式名>AUTHORIZATION<用戶名>[<表定義子句>|<視圖定義子句>|<授權定義子句>]18定義模式例1.定義一個學生-課程模式S-TCREATESCHEMA“S-T”AUTHORIZATIONWANG;

為用戶WANG定義了一個模式S-T例2.CREATESCHEMAAUTHORIZATIONWANG;<模式名>隱含為用戶名WANG如果沒有指定<模式名>，那么<模式名>隱含為<用戶名>19例3.CREATESCHEMATESTAUTHORIZATIONZHANGCREATETABLETAB1(COL1SMALLINT，COL2INT，COL3CHAR(20)，COL4NUMERIC(10，3)，COL5DECIMAL(5，2));為用戶ZHANG創(chuàng)建了一個模式TEST，并在其中定義了一個表TAB120二、刪除模式DROPSCHEMA<模式名><CASCADE|RESTRICT>CASCADE(級聯(lián))：刪除模式的同時把該模式中所有的數(shù)據庫對象全部刪除RESTRICT(限制)：如果該模式中定義了下屬的數(shù)據庫對象（如表、視圖等），則拒絕該刪除語句的執(zhí)行。當該模式中沒有任何下屬的對象時才能執(zhí)行DROPSCHEMA語句21例4.DROPSCHEMAZHANGCASCADE;刪除模式ZHANG同時該模式中定義的表TAB1也被刪除223.3數(shù)據定義3.3.1模式的定義與刪除3.3.2基本表的定義、刪除與修改3.3.3索引的建立與刪除233.3.2基本表的定義、刪除與修改一、定義基本表CREATETABLE<表名>(<列名><數(shù)據類型>[列級完整性約束條件][，<列名><數(shù)據類型>[列級完整性約束條件]]…[，<表級完整性約束條件>]);如果完整性約束條件涉及到該表的多個屬性列，則必須定義在表級上，否則既可以定義在列級也可以定義在表級。24例5.建立“學生”表Student，學號是主碼，姓名取值唯一CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY，SnameCHAR(20)UNIQUE，SsexCHAR(2)，SageSMALLINT，SdeptCHAR(20));25例6.建立一個“課程”表CourseCREATETABLECourse(CnoCHAR(4)PRIMARYKEY,CnameCHAR(40),CpnoCHAR(4),CcreditSMALLINT,FOREIGNKEY(Cpno)REFERENCESCourse(Cno)/*表級完整性約束條件，Cpno是外碼，被參照表是Course，被參照列是Cno*/);26例7.建立一個“學生選課”表SCCREATETABLESC(SnoCHAR(9)，CnoCHAR(4)，GradeSMALLINT，PRIMARYKEY(Sno,Cno)，/*主碼由兩個屬性構成，必須作為表級完整性進行定義*/FOREIGNKEY(Sno)REFERENCESStudent(Sno)，/*表級完整性約束條件，Sno是外碼，被參照表是Student*/FOREIGNKEY(Cno)REFERENCESCourse(Cno)/*表級完整性約束條件，Cno是外碼，被參照表是Course*/);27二、數(shù)據類型SQL中域的概念用數(shù)據類型來實現(xiàn)定義表的各個屬性時需要指明其數(shù)據類型及長度選用哪種數(shù)據類型？取值范圍要做哪些運算28二、數(shù)據類型2930三、模式與表每一個基本表都屬于某一個模式一個模式包含多個基本表定義基本表所屬模式方法一：在表名中明顯地給出模式名Createtable“S-T”.Student（）;/*模式名為S-T*/Createtable“S-T”.Cource（）;Createtable“S-T”.SC（）;方法二：在創(chuàng)建模式語句中同時創(chuàng)建表方法三：設置所屬的模式31模式與表（續(xù)）創(chuàng)建基本表（其他數(shù)據庫對象也一樣）時，若沒有指定模式，系統(tǒng)根據搜索路徑來確定該對象所屬的模式RDBMS會使用模式列表中第一個存在的模式作為數(shù)據庫對象的模式名若搜索路徑中的模式名都不存在，系統(tǒng)將給出錯誤顯示當前的搜索路徑：SHOWsearch_path;搜索路徑的當前默認值是：$user，PUBLIC，含義：先搜索與用戶名相同的模式名，如果該模式名不存在，則使用PUBLIC模式32模式與表（續(xù)）DBA用戶可以設置搜索路徑，然后定義基本表SETsearch_pathTO“S-T”，PUBLIC;CreatetableStudent（）;建立了S-T.Student基本表RDBMS發(fā)現(xiàn)搜索路徑中第一個模式名S-T存在，就把該模式作為基本表Student所屬的模式33四、修改基本表ALTERTABLE<表名>[ADD<新列名><數(shù)據類型>[完整性約束]][DROP<完整性約束名>][ALTERCOLUMN<列名><數(shù)據類型>];34修改基本表（續(xù)）例8.向Student表增加“入學時間”列，其數(shù)據類型為日期型ALTERTABLEStudentADDS_entranceDATE;不論基本表中原來是否已有數(shù)據，新增加的列一律為空值35例9.將年齡的數(shù)據類型由字符型（假設原來的數(shù)據類型是字符型）改為整數(shù)ALTERTABLEStudentALTERCOLUMNSageINT;例10.增加課程名稱必須取唯一值的約束條件ALTERTABLECourseADDUNIQUE(Cname);36五、刪除基本表DROPTABLE<表名>［RESTRICT|CASCADE］;RESTRICT：刪除表是有限制條件的欲刪除的基本表不能被其他表的約束所引用如果存在依賴該表的對象，則此表不能被刪除CASCADE：刪除該表沒有限制在刪除基本表的同時，相關的依賴對象一起刪除37刪除基本表(續(xù))例11.刪除Student表DROPTABLEStudentCASCADE;基本表定義被刪除，數(shù)據被刪除表上建立的索引、視圖、觸發(fā)器等有關對象一般也將被刪除38刪除基本表（續(xù)）例12.若表上建有視圖，選擇RESTRICT時表不能刪除CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept='IS';DROPTABLEStudentRESTRICT;--ERROR:cannotdroptableStudentbecauseotherobjectsdependonit39刪除基本表（續(xù)）例12.如果選擇CASCADE時可以刪除表，視圖也自動被刪除DROPTABLEStudentCASCADE;SELECT*FROMIS_Student;--ERROR:relation"IS_Student"doesnotexist403.3數(shù)據定義3.3.1模式的定義與刪除3.3.2基本表的定義、刪除與修改3.3.3索引的建立與刪除413.3.3索引的建立與刪除建立索引的目的：加快查詢速度誰可以建立索引？DBA或建立表的人(owner)DBMS一般會自動建立以下列上的索引PRIMARYKEYUNIQUE誰維護索引？DBMS自動完成使用索引DBMS自動選擇是否使用索引以及使用哪些索引42索引RDBMS中索引一般采用B+樹、HASH索引來實現(xiàn)B+樹索引具有動態(tài)平衡的優(yōu)點HASH索引具有查找速度快的特點采用B+樹，還是HASH索引由具體的RDBMS來決定索引是關系數(shù)據庫的內部實現(xiàn)技術，屬于內模式的范疇CREATEINDEX語句定義索引時，可以定義索引是唯一索引、非唯一索引或聚簇索引43一、建立索引語句格式CREATE[UNIQUE][CLUSTER]INDEX

<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…);UNIQUE表明此索引的每一個索引值只對應唯一的數(shù)據記錄CLUSTER(聚簇索引)是指索引項的順序與表中記錄的物理順序一致的索引組織44建立索引（續(xù)）例13.CREATECLUSTERINDEXStusnameONStudent(Sname);在Student表的Sname（姓名）列上建立一個聚簇索引在最經常查詢的列上建立聚簇索引以提高查詢效率一個基本表上最多只能建立一個聚簇索引經常更新的列不宜建立聚簇索引45例14.為學生-課程數(shù)據庫中的Student，Course，SC三個表建立索引CREATEUNIQUEINDEXStusnoONStudent(Sno);CREATEUNIQUEINDEXCoucnoONCourse(Cno);CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC);Student表按學號升序建唯一索引Course表按課程號升序建唯一索引SC表按學號升序和課程號降序建唯一索引46二、刪除索引DROPINDEX<索引名>;刪除索引時，系統(tǒng)會從數(shù)據字典中刪去有關該索引的描述例15.刪除Student表的Stusname索引DROPINDEXStusname;47第三章關系數(shù)據庫標準語言SQL3.1SQL概述3.2學生—課程數(shù)據庫3.3數(shù)據定義3.4數(shù)據查詢3.5數(shù)據更新3.6視圖3.7小結48數(shù)據查詢語句格式SELECT[ALL|DISTINCT]<目標列表達式>[，<目標列表達式>]…FROM<表名或視圖名>[，<表名或視圖名>]…[WHERE<條件表達式>][GROUPBY<列名1>[HAVING<條件表達式>]][ORDERBY<列名2>[ASC|DESC]];493.4數(shù)據查詢3.4.1單表查詢3.4.2連接查詢3.4.3嵌套查詢3.4.4集合查詢3.4.5Select語句的一般格式503.4.1單表查詢僅涉及一個表的查詢選擇表中的若干列選擇表中的若干元組ORDERBY子句聚集函數(shù)(aggregatefunction)GROUPBY子句51一、選擇表中的若干列查詢指定列例1.查詢全體學生的學號與姓名SELECTSno,SnameFROMStudent;例2.查詢全體學生的姓名、學號、所在系SELECTSname,Sno,SdeptFROMStudent;522.查詢全部列選出所有屬性列在SELECT關鍵字后面列出所有列名將<目標列表達式>指定為*例3.查詢全體學生的詳細記錄SELECTSno，Sname，Ssex，Sage，SdeptFROMStudent;或SELECT*FROMStudent;533.查詢經過計算的值SELECT子句的<目標列表達式>不僅可以是表中的屬性列，也可以是表達式算術表達式字符串常量函數(shù)列別名54查詢經過計算的值（續(xù)）例4.查全體學生的姓名及其出生年份SELECTSname，2011-SageFROMStudent;55查詢經過計算的值（續(xù)）例5.查詢全體學生的姓名、出生年份和所在的院系，要求用小寫字母表示所有系名SELECTSname，‘YearofBirth:'，2011-Sage，LOWER(Sdept)FROMStudent;56查詢經過計算的值（續(xù)）使用列別名改變查詢結果的列標題SELECTSnameNAME，'YearofBirth:’BIRTH，2011-SageBIRTHDAY，LOWER(Sdept)DEPARTMENTFROMStudent;573.4.1單表查詢查詢僅涉及一個表選擇表中的若干列選擇表中的若干元組ORDERBY子句聚集函數(shù)GROUPBY子句58二、選擇表中的若干元組如果沒有指定DISTINCT關鍵詞，則缺省為ALL例6.查詢選修了課程的學生學號SELECTSnoFROMSC;等價于：SELECTALLSnoFROMSC;執(zhí)行上面的SELECT語句后，結果為：59消除取值重復的行（續(xù)）指定DISTINCT關鍵詞，去掉表中重復的行SELECTDISTINCTSnoFROMSC;602.查詢滿足條件的元組表3.4常用的查詢條件(Page94)61(1)比較大小例7.查詢計算機科學系全體學生的名單SELECTSnameFROMStudentWHERESdept=‘CS’；62例8.查詢所有年齡在20歲以下的學生姓名及其年齡SELECTSname，SageFROMStudentWHERESage<20；63例9.查詢考試成績有不及格的學生的學號SELECTDISTINCTSnoFROMSCWHEREGrade<60；64（2）確定范圍謂詞:BETWEEN…AND…NOTBETWEEN…AND…例10.查詢年齡在20~23歲（包括20歲和23歲）之間的學生的姓名、系別和年齡SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；65例11.查詢年齡不在20~23歲之間的學生姓名、系別和年齡SELECTSname，Sdept，SageFROMStudentWHERESageNOTBETWEEN20AND23；66(3)確定集合謂詞：IN<值表>,NOTIN<值表>例12.查詢信息系（IS）、數(shù)學系（MA）和計算機科學系（CS）學生的姓名和性別SELECTSname，SsexFROMStudentWHERESdeptIN('IS'，'MA'，'CS');67例13.查詢既不是信息系、數(shù)學系，也不是計算機科學系的學生姓名和性別SELECTSname，SsexFROMStudentWHERESdeptNOTIN('IS'，'MA'，'CS');68(4)字符匹配謂詞：[NOT]LIKE‘<匹配串>’[ESCAPE‘<換碼字符>’](1)匹配串為固定字符串例14.查詢學號為200215121的學生的詳細情況SELECT*FROMStudentWHERESnoLIKE‘200215121'；等價于：SELECT*FROMStudentWHERESno='200215121'；69(2)匹配串為含通配符的字符串例15.查詢所有姓劉學生的姓名、學號和性別SELECTSname，Sno，SsexFROMStudentWHERESnameLIKE‘劉%’；70例16.查詢姓“歐陽”且全名為三個漢字的學生的姓名SELECTSnameFROMStudentWHERESnameLIKE'歐陽__'；71字符匹配例18.查詢所有不姓劉的學生姓名SELECTSname，Sno，SsexFROMStudentWHERESnameNOTLIKE'劉%'；72字符匹配(3)使用換碼字符將通配符轉義為普通字符例19.查詢DB_Design課程的課程號和學分SELECTCno，CcreditFROMCourseWHERECnameLIKE‘DB\_Design’ESCAPE‘\’；ESCAPE

‘＼’

表示“

＼”

為換碼字符匹配串中緊跟在“

＼”

后的字符“_”不再具有通配符的含義，轉義為普通的“_”字符73例20.查詢以“DB_”開頭，且倒數(shù)第3個字符為i

的課程的詳細情況。SELECT*FROMCourseWHERECnameLIKE'DB\_%i__'ESCAPE‘\’；74(5)涉及空值的查詢謂詞：ISNULL或ISNOTNULL“IS”不能用“=”代替例21.某些學生選修課程后沒有參加考試，所以有選課記錄，但沒有考試成績。查詢缺少成績的學生的學號和相應的課程號SELECTSno，CnoFROMSCWHEREGradeISNULL；75例22.查所有有成績的學生學號和課程號SELECTSno，CnoFROMSCWHEREGradeISNOTNULL；76(6)多重條件查詢邏輯運算符：AND和OR來聯(lián)結多個查詢條件AND的優(yōu)先級高于OR可以用括號改變優(yōu)先級可用來實現(xiàn)多種其他謂詞[NOT]IN[NOT]BETWEEN…AND…77多重條件查詢例23.查詢計算機系年齡在20歲以下的學生姓名SELECTSnameFROMStudentWHERESdept='CS'ANDSage<20；78多重條件查詢改寫例12.

查詢信息系(IS)、數(shù)學系(MA)和計算機科學系（CS）學生的姓名和性別SELECTSname，SsexFROMStudentWHERESdeptIN('IS'，'MA'，'CS')；改寫為SELECTSname，SsexFROMStudentWHERESdept='IS'ORSdept='MA'ORSdept='CS'；79三、ORDERBY子句ORDERBY子句可以按一個或多個屬性列排序升序：ASC；降序：DESC；缺省值為升序當排序列含空值時ASC：排序列為空值的元組最后顯示DESC：排序列為空值的元組最先顯示80ORDERBY子句例24.查詢選修了3號課程的學生的學號及其成績，查詢結果按分數(shù)降序排列SELECTSno，GradeFROMSCWHERECno='3'ORDERBYGradeDESC；81例25.查詢全體學生情況，查詢結果按所在系的系號升序排列，同一系中的學生按年齡降序排列SELECT*FROMStudentORDERBYSdept，SageDESC；82四、聚集函數(shù)COUNT（[DISTINCT|ALL]*）統(tǒng)計元組個數(shù)COUNT（[DISTINCT|ALL]<列名>）統(tǒng)計一列中值的個數(shù)SUM（[DISTINCT|ALL]<列名>）計算一列值的總和（數(shù)值型）AVG（[DISTINCT|ALL]<列名>）計算一列值的平均值（數(shù)值型）MAX（[DISTINCT|ALL]<列名>）求一列值中的最大值MIN（[DISTINCT|ALL]<列名>）求一列值中的最小值83聚集函數(shù)例26.查詢學生總人數(shù)SELECTCOUNT(*)FROMStudent；例27.查詢選修了課程的學生人數(shù)SELECTCOUNT(DISTINCTSno)FROMSC；例28.計算1號課程的學生平均成績SELECTAVG(Grade)FROMSCWHERECno='1'；84聚集函數(shù)例29.查詢選修1號課程的學生最高分數(shù)SELECTMAX(Grade)FROMSCWHERCno=‘1’；例30.查詢學生200215012選修課程的總學分數(shù)SELECTSUM(Ccredit)FROMSC，CourseWHERSno='200215012'ANDSC.Cno=Course.Cno;85五、GROUPBY子句將查詢結果按某一列或多列的值分組，值相等的為一組對查詢結果分組的目的是為了細化聚集函數(shù)的作用對象未對查詢結果分組，聚集函數(shù)將作用于整個查詢結果對查詢結果分組后，聚集函數(shù)將分別作用于每個組作用對象是查詢的中間結果表86GROUPBY子句例31.求各個課程號及相應的選課人數(shù)SELECTCno，COUNT(Sno)FROMSCGROUPBYCno；87GROUPBY子句例32.查詢選修了3門以上課程的學生學號SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)>3；88HAVING短語與WHERE子句的區(qū)別作用對象不同WHERE子句作用于基本表或視圖，從中選擇滿足條件的元組HAVING短語作用于組，從中選擇滿足條件的組893.4數(shù)據查詢3.4.1單表查詢3.4.2連接查詢3.4.3嵌套查詢3.4.4集合查詢3.4.5Select語句的一般格式903.4.2連接查詢連接查詢：同時涉及多個表的查詢連接條件或連接謂詞：用來連接兩個表的條件一般格式[<表名1>.]<列名1><比較運算符>[<表名2>.]<列名2>[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>91一、等值與非等值連接查詢等值連接：連接運算符為=例33.查詢每個學生及其選修課程的情況SELECTStudent.*，SC.*FROMStudent,SCWHEREStudent.Sno=SC.Sno；92自然連接例34.對[例33]用自然連接完成SELECTStudent.Sno，Sname，Ssex，Sage，Sdept，Cno，GradeFROMStudent，SCWHEREStudent.Sno=SC.Sno；93二、自身連接自身連接：一個表與其自己進行連接需要給表起別名以示區(qū)別由于所有屬性名都是同名屬性，因此必須使用別名前綴94例35.查詢每一門課的間接先修課（即先修課的先修課）SELECTFIRST.Cno，SECOND.CpnoFROMCourseFIRST，CourseSECONDWHEREFIRST.Cpno=SECOND.Cno；9596三、外連接外連接與普通連接的區(qū)別普通連接操作只輸出滿足連接條件的元組外連接操作以指定表為連接主體，將主體表中不滿足連接條件的元組一并輸出97例36.改寫[例33]查詢每個學生及其選修課程的情況SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudentLEFTOUTERJOINSCON(Student.Sno=SC.Sno);98外連接左外連接：列出左邊關系中所有的元組右外連接：列出右邊關系中所有的元組99四、復合條件連接例37.查詢選修2號課程且成績在90分以上的所有學生SELECTStudent.Sno,SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno=‘2’ANDSC.Grade>90；100復合條件連接例38.查詢每個學生的學號、姓名、選修的課程名及成績SELECTStudent.Sno，Sname，Cname，GradeFROMStudent，SC，Course/*多表連接*/WHEREStudent.Sno=SC.SnoANDSC.Cno=Course.Cno；1013.4數(shù)據查詢3.4.1單表查詢3.4.2連接查詢3.4.3嵌套查詢3.4.4集合查詢3.4.5Select語句的一般格式102嵌套查詢一個SELECT-FROM-WHERE語句稱為一個查詢塊將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢(NestedQuery)103嵌套查詢SELECTSname/*外層查詢或父查詢*/FROMStudentWHERESnoIN(SELECTSno/*內層查詢或子查詢*/FROMSCWHERECno='2’)；104嵌套查詢子查詢的限制不能使用ORDERBY子句ORDERBY子句只能對最終查詢結果排序層層嵌套方式反映了SQL語言的結構化105嵌套查詢求解方法不相關子查詢：子查詢的查詢條件不依賴于父查詢由里向外逐層處理。即每個子查詢在上一級查詢處理之前求解，子查詢的結果用于建立其父查詢的查找條件106嵌套查詢求解方法相關子查詢：子查詢的查詢條件依賴于父查詢首先取外層查詢中表的第一個元組，根據它與內層查詢相關的屬性值處理內層查詢，若WHERE子句返回值為真，則取此元組放入結果表然后再取外層表的下一個元組重復這一過程，直至外層表全部檢查完為止1073.4.3嵌套查詢一、帶有IN謂詞的子查詢二、帶有比較運算符的子查詢三、帶有ANY（SOME）或ALL謂詞的子查詢四、帶有EXISTS謂詞的子查詢108一、帶有IN謂詞的子查詢例39.查詢與“劉晨”在同一個系學習的學生此查詢要求可以分步來完成(1)確定“劉晨”所在系名SELECTSdeptFROMStudentWHERESname='劉晨'；109帶有IN謂詞的子查詢(2)查找所有在CS系學習的學生SELECTSno，Sname，SdeptFROMStudentWHERESdept='CS'；110帶有IN謂詞的子查詢將第一步查詢嵌入到第二步查詢的條件中SELECTSno，Sname，SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname=‘劉晨’)；此查詢?yōu)椴幌嚓P子查詢111帶有IN謂詞的子查詢用自身連接完成[例39]查詢要求SELECTS1.Sno，S1.Sname，S1.SdeptFROMStudentS1，StudentS2WHERES1.Sdept=S2.SdeptANDS2.Sname='劉晨'；112帶有IN謂詞的子查詢例40.查詢選修了課程名為“信息系統(tǒng)”的學生學號和姓名SELECTSno，Sname③最后在Student關系中FROMStudent取出Sno和SnameWHERESnoIN(SELECTSno②然后在SC關系中找出選FROMSC修了3號課程的學生學號WHERECnoIN(SELECTCno①首先在Course關系中找出FROMCourse“信息系統(tǒng)”的課程號(3號)WHERECname=‘信息系統(tǒng)’));113帶有IN謂詞的子查詢用連接查詢實現(xiàn)[例40]SELECTStudent.Sno，SnameFROMStudent，SC，CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.Cname=‘信息系統(tǒng)’；1143.4.3嵌套查詢帶有IN謂詞的子查詢帶有比較運算符的子查詢帶有ANY（SOME）或ALL謂詞的子查詢帶有EXISTS謂詞的子查詢115二、帶有比較運算符的子查詢當能確切知道內層查詢返回單值時，可用比較運算符（>，<，=，>=，<=，!=或<>）與ANY或ALL謂詞配合使用116帶有比較運算符的子查詢例：假設一個學生只可能在一個系學習，并且必須屬于一個系，則在[例39]可以用=代替INSELECTSno，Sname，SdeptFROMStudentWHERESdept=(SELECTSdeptFROMStudentWHERESname=‘劉晨’)；117帶有比較運算符的子查詢子查詢一定要跟在比較符之后錯誤的例子SELECTSno，Sname，SdeptFROMStudentWHERE(SELECTSdeptFROMStudentWHERESname=‘劉晨’)=Sdept；118帶有比較運算符的子查詢例41.找出每個學生超過他選修課程平均成績的課程號SELECTSno，CnoFROMSCxWHEREGrade>=(SELECTAVG(Grade)FROMSCyWHEREy.Sno=x.Sno);相關子查詢119可能的執(zhí)行過程從外層查詢中取出SC的一個元組x，將元組x的Sno值（200215121）傳送給內層查詢SELECTAVG(Grade)FROMSCyWHEREy.Sno='200215121';執(zhí)行內層查詢，得到值88（近似值），用該值代替內層查詢，得到外層查詢SELECTSno，CnoFROMSCxWHEREGrade>=88；120執(zhí)行這個查詢，得到（200215121，1）（200215121，3）外層查詢取出下一個元組重復做上述1至3步驟，直到外層的SC元組全部處理完畢。結果為（200215121，1）（200215121，3）（200215122，2）1213.4.3嵌套查詢帶有IN謂詞的子查詢帶有比較運算符的子查詢帶有ANY（SOME）或ALL謂詞的子查詢帶有EXISTS謂詞的子查詢122三、帶有ANY（SOME）或ALL謂詞的子查詢謂詞語義ANY：任意一個值ALL：所有值123帶有ANY（SOME）或ALL謂詞的子查詢>ANY 大于子查詢結果中的某個值>ALL 大于子查詢結果中的所有值<ANY 小于子查詢結果中的某個值<ALL 小于子查詢結果中的所有值>=ANY 大于等于子查詢結果中的某個值>=ALL 大于等于子查詢結果中的所有值<=ANY 小于等于子查詢結果中的某個值<=ALL 小于等于子查詢結果中的所有值=ANY 等于子查詢結果中的某個值=ALL 等于子查詢結果中的所有值（通常沒有實際意義）!=（或<>）ANY 不等于子查詢結果中的某個值!=（或<>）ALL 不等于子查詢結果中的任何一個值124帶有ANY（SOME）或ALL謂詞的子查詢例42.查詢其他系中比計算機科學系某一學生年齡小的學生姓名和年齡125帶有ANY（SOME）或ALL謂詞的子查詢用聚集函數(shù)實現(xiàn)[例42]SELECTSname，SageFROMStudentWHERESage<(SELECTMAX(Sage)FROMStudentWHERESdept=‘CS')ANDSdept<>'CS’;126帶有ANY（SOME）或ALL謂詞的子查詢例43.查詢其他系中比計算機科學系所有學生年齡都小的學生姓名及年齡方法一：用ALL謂詞SELECTSname，SageFROMStudentWHERESage<ALL(SELECTSageFROMStudentWHERESdept='CS')ANDSdept<>'CS’;127方法二：用聚集函數(shù)SELECTSname，SageFROMStudentWHERESage<(SELECTMIN(Sage)FROMStudentWHERESdept='CS')ANDSdept<>'CS’;128帶有ANY（SOME）或ALL謂詞的子查詢表3.5ANY（或SOME），ALL謂詞與聚集函數(shù)、IN謂詞的等價轉換關系1293.4.3嵌套查詢帶有IN謂詞的子查詢帶有比較運算符的子查詢帶有ANY（SOME）或ALL謂詞的子查詢帶有EXISTS謂詞的子查詢130EXISTS謂詞存在量詞帶有EXISTS謂詞的子查詢不返回任何數(shù)據，只產生邏輯真值“true”或邏輯假值“false”若內層查詢結果非空，則外層的WHERE子句返回真值若內層查詢結果為空，則外層的WHERE子句返回假值由EXISTS引出的子查詢，其目標列表達式通常都用*，因為帶EXISTS的子查詢只返回真值或假值，給出列名無實際意義NOTEXISTS謂詞若內層查詢結果非空，則外層的WHERE子句返回假值若內層查詢結果為空，則外層的WHERE子句返回真值131帶有EXISTS謂詞的子查詢例44.查詢所有選修了1號課程的學生姓名思路分析本查詢涉及Student和SC關系在Student中依次取每個元組的Sno值，用此值去檢查SC關系若SC中存在這樣的元組，其Sno值等于此Student.Sno值，并且其Cno='1'，則取此Student.Sname送入結果關系132帶有EXISTS謂詞的子查詢用嵌套查詢SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno='1')；133帶有EXISTS謂詞的子查詢用連接運算SELECTSnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno='1';134帶有EXISTS謂詞的子查詢例45.查詢沒有選修1號課程的學生姓名SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno='1')；135帶有EXISTS謂詞的子查詢不同形式的查詢間的替換一些帶EXISTS或NOTEXISTS謂詞的子查詢不能被其他形式的子查詢等價替換所有帶IN謂詞、比較運算符、ANY和ALL謂詞的子查詢都能用帶EXISTS謂詞的子查詢等價替換用EXISTS/NOTEXISTS實現(xiàn)全稱量詞SQL語言中沒有全稱量詞（Forall）可以把帶有全稱量詞的謂詞轉換為等價的帶有存在量詞的謂詞(x)P(x(P))136帶有EXISTS謂詞的子查詢例39.查詢與“劉晨”在同一個系學習的學生?？梢杂脦XISTS謂詞的子查詢替換SELECTSno，Sname，SdeptFROMStudentS1WHEREEXISTS(SELECT*FROMStudentS2WHERES2.Sdept=S1.SdeptANDS2.Sname=‘劉晨’)；137例46.查詢選修了全部課程的學生姓名SELECTSnameFROMStudentWHERENOTEXISTS（SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=Course.Cno）);138用EXISTS/NOTEXISTS實現(xiàn)邏輯蘊函SQL語言中沒有蘊函(Implication)邏輯運算可以利用謂詞演算將邏輯蘊函謂詞等價轉換為pqpq139例47.查詢至少選修了學生200215122選修的全部課程的學生號碼解題思路：用邏輯蘊函表達：查詢學號為x的學生，對所有的課程y，只要200215122學生選修了課程y，則x也選修了y。形式化表示：用p表示謂詞“學生200215122選修了課程y”用q表示謂詞“學生x選修了課程y”(y)pq140等價變換變換后的語義不存在這樣的課程y，學生200215122選修了y，而學生x沒有選141用NOTEXISTS謂詞表示SELECTDISTINCTSnoFROMSCSCXWHERENOTEXISTS(SELECT*FROMSCSCYWHERESCY.Sno='200215122'ANDNOTEXISTS(SELECT*FROMSCSCZWHERESCZ.Sno=SCX.SnoANDSCZ.Cno=SCY.Cno))；1423.4.4集合查詢集合操作的種類并操作UNION交操作INTERSECT差操作EXCEPT參加集合操作的各查詢結果的列數(shù)必須相同；對應項的數(shù)據類型也必須相同143集合查詢例48.查詢計算機科學系的學生及年齡不大于19歲的學生方法一：SELECT*FROMStudentWHERESdept='CS'UNIONSELECT*FROMStudentWHERESage<=19；UNION：將多個查詢結果合并起來時，系統(tǒng)自動去掉重復元組UNIONALL：將多個查詢結果合并起來時，保留重復元組144方法二SELECTDISTINCT*FROMStudentWHERESdept='CS'ORSage<=19；145集合查詢例49.查詢選修了課程1或者選修了課程2的學生SELECTSnoFROMSCWHERECno='1'UNIONSELECTSnoFROMSCWHERECno='2'；146集合查詢例50.查詢計算機科學系的學生與年齡不大于19歲的學生的交集SELECT*FROMStudentWHERESdept='CS'INTERSECTSELECT*FROMStudentWHERESage<=19147集合查詢例50.實際上就是查詢計算機科學系中年齡不大于19歲的學生SELECT*FROMStudentWHERESdept='CS'ANDSage<=19；148集合查詢例51.查詢選修課程1的學生集合與選修課程2的學生集合的交集SELECTSnoFROMSCWHERECno='1'INTERSECTSELECTSnoFROMSCWHERECno='2'；149集合查詢例51.實際上是查詢既選修了課程1又選修了課程2的學生SELECTSnoFROMSCWHERECno='1'ANDSnoIN(SELECTSnoFROMSCWHERECno='2')；150例52.查詢計算機科學系的學生與年齡不大于19歲的學生的差集SELECT*FROMStudentWHERESdept='CS'EXCEPTSELECT*FROMStudentWHERESage<=19;151[例52]實際上是查詢計算機科學系中年齡大于19歲的學生SELECT*FROMStudentWHERESdept='CS'ANDSage>19；1523.4.5SELECT語句的一般格式SELECT[ALL|DISTINCT]<目標列表達式>[別名][，<目標列表達式>[別名]]…FROM<表名或視圖名>[別名][，<表名或視圖名>[別名]]…[WHERE<條件表達式>][GROUPBY<列名1>[HAVING<條件表達式>]][ORDERBY<列名2>[ASC|DESC]153第三章關系數(shù)據庫標準語言SQL3.1SQL概述3.2學生—課程數(shù)據庫3.3數(shù)據定義3.4數(shù)據查詢3.5數(shù)據更新3.6視圖3.7小結1543.5數(shù)據更新3.5.1插入數(shù)據3.5.2修改數(shù)據3.5.3刪除數(shù)據1553.5.1插入數(shù)據兩種插入數(shù)據方式插入元組插入子查詢結果可以一次插入多個元組156一、插入元組語句格式INSERTINTO<表名>[(<屬性列1>[，<屬性列2>…)]VALUES(<常量1>[，<常量2>]…)功能：將新元組插入指定表中157插入元組INTO子句屬性列的順序可與表定義中的順序不一致沒有指定屬性列，新插入元組必須在每個屬性列都有值指定部分屬性列VALUES子句提供的值必須與INTO子句匹配值的個數(shù)值的類型158插入元組例1.將一個新學生元組（學號：200215128；姓名：陳冬；性別：男；所在系：IS；年齡：18歲）插入到Student表中INSERTINTOStudent(Sno，Sname，Ssex，Sdept，Sage)VALUES('200215128'，'陳冬'，'男'，'IS'，18)；159插入元組例2.將學生張成民的信息插入到Student表中INSERTINTOStudentVALUES(‘200215126’，‘張成民’，‘男’，18，'CS');160插入元組例3.插入一條選課記錄('200215128'，'1')INSERTINTOSC(Sno，Cno)VALUES(‘200215128’，‘1’)；RDBMS將在新插入記錄的Grade列上自動賦空值INSERTINTOSCVALUES('200215128'，'1'，NULL)；161二、插入子查詢結果語句格式INSERTINTO<表名>[(<屬性列1>[，<屬性列2>…)]子查詢；功能將子查詢結果插入指定表中162插入子查詢結果例4.對每一個系，求學生的平均年齡，并把結果存入數(shù)據庫第一步：建表CREATETABLEDept_age(SdeptCHAR(15),Avg_ageSMALLINT)；163第二步：插入數(shù)據INSERTINTODept_age(Sdept，Avg_age)SELECTSdept，AVG(Sage)FROMStudentGROUPBYSdept；164RDBMS在執(zhí)行插入語句時會檢查所插入元組是否破壞表上已定義的完整性規(guī)則實體完整性參照完整性用戶定義的完整性NOTNULL約束UNIQUE約束值域約束1653.5.2.修改數(shù)據語句格式UPDATE<表名>SET<列名>=<表達式>[，<列名>=<表達式>]…[WHERE<條件>]；SET子句指定修改方式，要修改的列，修改后取值WHERE子句指定要修改的元組缺省表示要修改表中的所有元組166修改數(shù)據三種修改方式修改某一個元組的值修改多個元組的值帶子查詢的修改語句1671.修改某一個元組的值例5.將學生200215121的年齡改為22歲UPDATEStudentSETSage=22WHERESno='200215121'；1682.修改多個元組的值例6.將所有學生的年齡增加1歲UPDATEStudentSETSage=Sage+1；1693.帶子查詢的修改語句例7.將計算機科學系全體學生的成績置零UPDATESCSETGrade=0WHERE'CS'=(SELECTSdeptFROMStudentWHEREStudent.Sno=SC.Sno)；170修改數(shù)據RDBMS在執(zhí)行修改語句時會檢查修改操作是否破壞表上已定義的完整性規(guī)則實體完整性主碼不允許修改用戶定義的完整性NOTNULL約束UNIQUE約束值域約束1713.5.3刪除數(shù)據語句格式DELETEFROM<表名>[WHERE<條件>]；功能刪除指定表中滿足WHERE子句條件的元組WHERE子句指定要刪除的元組缺省表示要刪除表中的全部元組，表的定義仍在字典中172刪除數(shù)據三種刪除方式刪除某一個元組的值刪除多個元組的值帶子查詢的刪除語句1731.刪除某一個元組的值例8.刪除學號為200215128的學生記錄DELETEFROMStudentWHERESno=‘200215128'；1742.刪除多個元組的值例9.刪除所有的學生選課記錄DELETEFROMSC；1753.帶子查詢的刪除語句例10.刪除計算機科學系所有學生的選課記錄DELETEFROMSCWHERE'CS'=(SELETESdeptFROMStudentWHEREStudent.Sno=SC.Sno)；176第三章關系數(shù)據庫標準語言SQL3.1SQL概述3.2學生—課程數(shù)據庫3.3數(shù)據定義3.4數(shù)據查詢3.5數(shù)據更新3.6視圖3.7小結1773.6視圖虛表，是從一個或幾個基本表（或視圖）導出的表只存放視圖的定義，不存放視圖對應的數(shù)據基表中的數(shù)據發(fā)生變化，從視圖中查詢出的數(shù)據也隨之改變行列子集視圖：若一個視圖是從單個基本表導出的，并且只是去掉了基本表的某些行和某些列，但保留了主碼。1783.6視圖3.6.1定義視圖3.6.2查詢視圖3.6.3更新視圖3.6.4視圖的作用1793.6.1定義視圖CREATEVIEW<視圖名>[(<列名>[,<列名>]…)]AS<子查詢>[WITHCHECKOPTION]組成視圖的屬性列名：全部省略或全部指定子查詢不允許含有ORDERBY子句和DISTINCT短語180RDBMS執(zhí)行CREATEVIEW語句時只是把視圖定義存入數(shù)據字典，并不執(zhí)行其中的SELECT語句在對視圖查詢時，按視圖的定義從基本表中將數(shù)據查出181例1.建立信息系學生的視圖CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept='IS'；182例2.建立信息系學生的視圖，并要求進行修改和插入操作時仍需保證該視圖只有信息系的學生CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept='IS'WITHCHECKOPTION；183對IS_Student視圖的更新操作修改操作：自動加上Sdept='IS'的條件刪除操作：自動加上Sdept='IS'的條件插入操作：自動檢查Sdept屬性值是否為'IS'如果不是，則拒絕該插入操作如果沒有提