數(shù)據(jù)庫系統(tǒng)概論(第五版)第3章(1)

,數(shù)據(jù)庫系統(tǒng)概論AnIntroductiontoDatabaseSystem,xx大學信息學院,第三章關系數(shù)據(jù)庫標準語言SQL,第三章關系數(shù)據(jù)庫標準語言SQL,3.1SQL概述3.2學生-課程數(shù)據(jù)庫3.3數(shù)據(jù)定義3.4數(shù)據(jù)查詢3.5數(shù)據(jù)更新3.6空值的處理3.7視圖3.8小結,3.1SQL概述,SQL（StructuredQueryLanguage）結構化查詢語言，是關系數(shù)據(jù)庫的標準語言SQL是一個通用的、功能極強的關系數(shù)據(jù)庫語言,SQL概述（續(xù)）,3.1.1SQL的產(chǎn)生與發(fā)展3.1.2SQL的特點3.1.3SQL的基本概念,SQL標準的進展過程,目前，沒有一個數(shù)據(jù)庫系統(tǒng)能夠支持SQL標準的所有概念和特性,3.1SQL概述,3.1.1SQL的產(chǎn)生與發(fā)展3.1.2SQL的特點3.1.3SQL的基本概念,3.1.2SQL的特點,綜合統(tǒng)一集數(shù)據(jù)定義語言（DDL），數(shù)據(jù)操縱語言（DML），數(shù)據(jù)控制語言（DCL）功能于一體?？梢元毩⑼瓿蓴?shù)據(jù)庫生命周期中的全部活動：定義和修改、刪除關系模式，定義和刪除視圖，插入數(shù)據(jù)，建立數(shù)據(jù)庫;對數(shù)據(jù)庫中的數(shù)據(jù)進行查詢和更新;數(shù)據(jù)庫重構和維護數(shù)據(jù)庫安全性、完整性控制，以及事務控制嵌入式SQL和動態(tài)SQL定義用戶數(shù)據(jù)庫投入運行后，可根據(jù)需要隨時逐步修改模式，不影響數(shù)據(jù)庫的運行。數(shù)據(jù)操作符統(tǒng)一,2.高度非過程化,非關系數(shù)據(jù)模型的數(shù)據(jù)操縱語言“面向過程”，必須指定存取路徑。SQL只要提出“做什么”，無須了解存取路徑。存取路徑的選擇以及SQL的操作過程由系統(tǒng)自動完成。,3.面向集合的操作方式,非關系數(shù)據(jù)模型采用面向記錄的操作方式，操作對象是一條記錄SQL采用集合操作方式操作對象、查找結果可以是元組的集合一次插入、刪除、更新操作的對象可以是元組的集合,4.以同一種語法結構提供多種使用方式,SQL是獨立的語言能夠獨立地用于聯(lián)機交互的使用方式SQL又是嵌入式語言SQL能夠嵌入到高級語言（例如C，C+，Java）程序中，供程序員設計程序時使用,.,5.語言簡潔，易學易用,SQL功能極強，完成核心功能只用了9個動詞。,3.1SQL概述,3.1.1SQL的產(chǎn)生與發(fā)展3.1.2SQL的特點3.1.3SQL的基本概念,SQL的基本概念（續(xù)）,SQL支持關系數(shù)據(jù)庫三級模式結構,SQL的基本概念（續(xù)）,基本表本身獨立存在的表SQL中一個關系就對應一個基本表一個（或多個）基本表對應一個存儲文件一個表可以帶若干索引,SQL的基本概念（續(xù)）,存儲文件邏輯結構組成了關系數(shù)據(jù)庫的內(nèi)模式物理結構對用戶是隱蔽的,SQL的基本概念（續(xù)）,視圖從一個或幾個基本表導出的表數(shù)據(jù)庫中只存放視圖的定義而不存放視圖對應的數(shù)據(jù)視圖是一個虛表用戶可以在視圖上再定義視圖,第三章關系數(shù)據(jù)庫標準語言SQL,3.1SQL概述3.2學生-課程數(shù)據(jù)庫3.3數(shù)據(jù)定義3.4數(shù)據(jù)查詢3.5數(shù)據(jù)更新3.6空值的處理3.7視圖3.8小結,3.2學生-課程數(shù)據(jù)庫,學生-課程模式S-T:學生表：Student(Sno,Sname,Ssex,Sage,Sdept)課程表：Course(Cno,Cname,Cpno,Ccredit)學生選課表：SC(Sno,Cno,Grade),.,Student表,.,Course表,.,SC表,第三章關系數(shù)據(jù)庫標準語言SQL,3.1SQL概述3.2學生-課程數(shù)據(jù)庫3.3數(shù)據(jù)定義3.4數(shù)據(jù)查詢3.5數(shù)據(jù)更新3.6空值的處理3.7視圖3.8小結,3.3數(shù)據(jù)定義,SQL的數(shù)據(jù)定義功能:模式定義表定義視圖和索引的定義,模式,現(xiàn)代關系數(shù)據(jù)庫管理系統(tǒng)提供了一個層次化的數(shù)據(jù)庫對象命名機制一個關系數(shù)據(jù)庫管理系統(tǒng)的實例（Instance）中可以建立多個數(shù)據(jù)庫一個數(shù)據(jù)庫中可以建立多個模式一個模式下通常包括多個表、視圖和索引等數(shù)據(jù)庫對象,數(shù)據(jù)庫（有的系統(tǒng)稱為目錄）,模式,表以及視圖、索引等,3.3數(shù)據(jù)定義,3.3.1模式的定義與刪除3.3.2基本表的定義、刪除與修改3.3.3索引的建立與刪除,1.定義模式,例3.1為用戶WANG定義一個學生-課程模式S-TCREATESCHEMA“S-T”AUTHORIZATIONWANG;例3.2CREATESCHEMAAUTHORIZATIONWANG;該語句沒有指定，隱含為,定義模式（續(xù)）,定義模式實際上定義了一個命名空間。在這個空間中可以定義該模式包含的數(shù)據(jù)庫對象，例如基本表、視圖、索引等。在CREATESCHEMA中可以接受CREATETABLE，CREATEVIEW和GRANT子句。CREATESCHEMAAUTHORIZATION|,定義模式（續(xù)）,例3.3為用戶ZHANG創(chuàng)建了一個模式TEST，并且在其中定義一個表TAB1CREATESCHEMATESTAUTHORIZATIONZHANGCREATETABLETAB1(COL1SMALLINT,COL2INT,COL3CHAR(20),COL4NUMERIC(10,3),COL5DECIMAL(5,2);,2.刪除模式,DROPSCHEMACASCADE（級聯(lián)）刪除模式的同時把該模式中所有的數(shù)據(jù)庫對象全部刪除RESTRICT（限制）如果該模式中定義了下屬的數(shù)據(jù)庫對象（如表、視圖等），則拒絕該刪除語句的執(zhí)行。僅當該模式中沒有任何下屬的對象時才能執(zhí)行。,刪除模式（續(xù)）,例3.4DROPSCHEMAZHANGCASCADE;刪除模式ZHANG同時該模式中定義的表TAB1也被刪除,3.3數(shù)據(jù)定義,3.3.1模式的定義與刪除3.3.2基本表的定義、刪除與修改3.3.3索引的建立與刪除,3.3.2基本表的定義、刪除與修改,定義基本表CREATETABLE(,);：所要定義的基本表的名字：組成該表的各個屬性（列）：涉及相應屬性列的完整性約束條件：涉及一個或多個屬性列的完整性約束條件如果完整性約束條件涉及到該表的多個屬性列，則必須定義在表級上，否則既可以定義在列級也可以定義在表級。,學生表Student,例3.5建立“學生”表Student。學號是主碼，姓名取值唯一。CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY,/*列級完整性約束條件,Sno是主碼*/SnameCHAR(20)UNIQUE,/*Sname取唯一值*/SsexCHAR(2),SageSMALLINT,SdeptCHAR(20);,主碼,UNIQUE約束,課程表Course,例3.6建立一個“課程”表CourseCREATETABLECourse(CnoCHAR(4)PRIMARYKEY,CnameCHAR(40),CpnoCHAR(4),CcreditSMALLINT，F(xiàn)OREIGNKEY(Cpno)REFERENCESCourse(Cno);,先修課,Cpno是外碼被參照表是Course被參照列是Cno,學生選課表SC,例3.7建立一個學生選課表SCCREATETABLESC(SnoCHAR(9),CnoCHAR(4),GradeSMALLINT，PRIMARYKEY(Sno,Cno),/*主碼由兩個屬性構成，必須作為表級完整性進行定義*/FOREIGNKEY(Sno)REFERENCESStudent(Sno),/*表級完整性約束條件，Sno是外碼，被參照表是Student*/FOREIGNKEY(Cno)REFERENCESCourse(Cno)/*表級完整性約束條件，Cno是外碼，被參照表是Course*/);,2.數(shù)據(jù)類型,SQL中域的概念用數(shù)據(jù)類型來實現(xiàn)定義表的屬性時需要指明其數(shù)據(jù)類型及長度選用哪種數(shù)據(jù)類型取值范圍要做哪些運算,.,數(shù)據(jù)類型（續(xù)）,3.模式與表,每一個基本表都屬于某一個模式一個模式包含多個基本表定義基本表所屬模式方法一：在表名中明顯地給出模式名CreatetableS-T.Student(.);/*模式名為S-T*/CreatetableS-T.Cource(.);CreatetableS-T.SC(.);方法二：在創(chuàng)建模式語句中同時創(chuàng)建表方法三：設置所屬的模式,模式與表（續(xù)）,創(chuàng)建基本表（其他數(shù)據(jù)庫對象也一樣）時，若沒有指定模式，系統(tǒng)根據(jù)搜索路徑來確定該對象所屬的模式關系數(shù)據(jù)庫管理系統(tǒng)會使用模式列表中第一個存在的模式作為數(shù)據(jù)庫對象的模式名若搜索路徑中的模式名都不存在，系統(tǒng)將給出錯誤顯示當前的搜索路徑：SHOWsearch_path;搜索路徑的當前默認值是：$user，PUBLIC,模式與表（續(xù)）,數(shù)據(jù)庫管理員用戶可以設置搜索路徑，然后定義基本表SETsearch_pathTOS-T,PUBLIC;CreatetableStudent(.);結果建立了S-T.Student基本表。關系數(shù)據(jù)庫管理系統(tǒng)發(fā)現(xiàn)搜索路徑中第一個模式名S-T，就把該模式作為基本表Student所屬的模式。,4.修改基本表,ALTERTABLEADDCOLUMN完整性約束ADDDROPCOLUMNCASCADE|RESTRICTDROPCONSTRAINTRESTRICT|CASCADEALTERCOLUMN;,修改基本表（續(xù)）,是要修改的基本表ADD子句用于增加新列、新的列級完整性約束條件和新的表級完整性約束條件DROPCOLUMN子句用于刪除表中的列如果指定了CASCADE短語，則自動刪除引用了該列的其他對象如果指定了RESTRICT短語，則如果該列被其他對象引用，關系數(shù)據(jù)庫管理系統(tǒng)將拒絕刪除該列DROPCONSTRAINT子句用于刪除指定的完整性約束條件ALTERCOLUMN子句用于修改原有的列定義，包括修改列名和數(shù)據(jù)類型,修改基本表（續(xù)）,例3.8向Student表增加“入學時間”列，其數(shù)據(jù)類型為日期型ALTERTABLEStudentADDS_entranceDATE;不管基本表中原來是否已有數(shù)據(jù)，新增加的列一律為空值,修改基本表（續(xù)）,例3.9將年齡的數(shù)據(jù)類型由字符型（假設原來的數(shù)據(jù)類型是字符型）改為整數(shù)。ALTERTABLEStudentALTERCOLUMNSageINT;例3.10增加課程名稱必須取唯一值的約束條件。ALTERTABLECourseADDUNIQUE(Cname);,5.刪除基本表,DROPTABLERESTRICT|CASCADE;RESTRICT：刪除表是有限制的。欲刪除的基本表不能被其他表的約束所引用如果存在依賴該表的對象，則此表不能被刪除CASCADE：刪除該表沒有限制。在刪除基本表的同時，相關的依賴對象一起刪除,刪除基本表（續(xù)）,例3.11刪除Student表DROPTABLEStudentCASCADE;基本表定義被刪除，數(shù)據(jù)被刪除表上建立的索引、視圖、觸發(fā)器等一般也將被刪除,刪除基本表（續(xù)）,例3.12若表上建有視圖，選擇RESTRICT時表不能刪除;選擇CASCADE時可以刪除表，視圖也自動刪除。CREATEVIEWIS_StudentASSELECTSno,Sname,SageFROMStudentWHERESdept=IS;DROPTABLEStudentRESTRICT;-ERROR:cannotdroptableStudentbecauseotherobjectsdependonit,刪除基本表（續(xù)）,例3.12續(xù)如果選擇CASCADE時可以刪除表，視圖也自動被刪除DROPTABLEStudentCASCADE;-NOTICE:dropcascadestoviewIS_StudentSELECT*FROMIS_Student;-ERROR:relationIS_Studentdoesnotexist,刪除基本表（續(xù)）,DROPTABLE時，SQL2011與3個RDBMS的處理策略比較,R表示RESTRICT,C表示CASCADE表示不能刪除基本表，表示能刪除基本表，保留表示刪除基本表后，還保留依賴對象,3.3數(shù)據(jù)定義,3.3.1模式的定義與刪除3.3.2基本表的定義、刪除與修改3.3.3索引的建立與刪除3.3.4數(shù)據(jù)字典,3.3.3索引的建立與刪除,建立索引的目的：加快查詢速度關系數(shù)據(jù)庫管理系統(tǒng)中常見索引：順序文件上的索引B+樹索引（參見愛課程網(wǎng)3.2節(jié)動畫B+樹的增刪改）散列（hash）索引位圖索引特點：B+樹索引具有動態(tài)平衡的優(yōu)點HASH索引具有查找速度快的特點,索引,誰可以建立索引數(shù)據(jù)庫管理員或表的屬主（即建立表的人）誰維護索引關系數(shù)據(jù)庫管理系統(tǒng)自動完成使用索引關系數(shù)據(jù)庫管理系統(tǒng)自動選擇合適的索引作為存取路徑，用戶不必也不能顯式地選擇索引,1.建立索引,語句格式CREATEUNIQUECLUSTERINDEXON(,);：要建索引的基本表的名字索引：可以建立在該表的一列或多列上，各列名之間用逗號分隔：指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCUNIQUE：此索引的每一個索引值只對應唯一的數(shù)據(jù)記錄CLUSTER：表示要建立的索引是聚簇索引,建立索引（續(xù)）,例3.13為學生-課程數(shù)據(jù)庫中的Student，Course，SC三個表建立索引。Student表按學號升序建唯一索引，Course表按課程號升序建唯一索引，SC表按學號升序和課程號降序建唯一索引CREATEUNIQUEINDEXStusnoONStudent(Sno);CREATEUNIQUEINDEXCoucnoONCourse(Cno);CREATEUNIQUEINDEXSCnoONSC(SnoASC,CnoDESC);,2.修改索引,ALTERINDEXRENAMETO例3.14將SC表的SCno索引名改為SCSnoALTERINDEXSCnoRENAMETOSCSno;,3.刪除索引,DROPINDEX;刪除索引時，系統(tǒng)會從數(shù)據(jù)字典中刪去有關該索引的描述。例3.15刪除Student表的Stusname索引DROPINDEXStusname;,3.3數(shù)據(jù)定義,3.3.1模式的定義與刪除3.3.2基本表的定義、刪除與修改3.3.3索引的建立與刪除3.3.4數(shù)據(jù)字典,數(shù)據(jù)字典,數(shù)據(jù)字典是關系數(shù)據(jù)庫管理系統(tǒng)內(nèi)部的一組系統(tǒng)表，它記錄了數(shù)據(jù)庫中所有定義信息：關系模式定義視圖定義索引定義完整性約束定義各類用戶對數(shù)據(jù)庫的操作權限統(tǒng)計信息等關系數(shù)據(jù)庫管理系統(tǒng)在執(zhí)行SQL的數(shù)據(jù)定義語句時，實際上就是在更新數(shù)據(jù)字典表中的相應信息。,第三章關系數(shù)據(jù)庫標準語言SQL,3.1SQL概述3.2學生-課程數(shù)據(jù)庫3.3數(shù)據(jù)定義3.4數(shù)據(jù)查詢3.5數(shù)據(jù)更新3.6空值的處理3.7視圖3.8小結,數(shù)據(jù)查詢,語句格式SELECTALL|DISTINCT,FROM,|(SELECT語句)ASWHEREGROUPBYHAVINGORDERBYASC|DESC;,數(shù)據(jù)查詢,SELECT子句：指定要顯示的屬性列FROM子句：指定查詢對象（基本表或視圖）WHERE子句：指定查詢條件GROUPBY子句：對查詢結果按指定列的值分組，該屬性列值相等的元組為一個組。通常會在每組中作用聚集函數(shù)。HAVING短語：只有滿足指定條件的組才予以輸出ORDERBY子句：對查詢結果表按指定列值的升序或降序排序,3.4數(shù)據(jù)查詢,3.4.1單表查詢3.4.2連接查詢3.4.3嵌套查詢3.4.4集合查詢3.4.5基于派生表的查詢3.4.6Select語句的一般形式,3.4.1單表查詢,查詢僅涉及一個表1.選擇表中的若干列2.選擇表中的若干元組3.ORDERBY子句4.聚集函數(shù)5.GROUPBY子句,1.選擇表中的若干列,查詢指定列例3.16查詢?nèi)w學生的學號與姓名。SELECTSno,SnameFROMStudent;例3.17查詢?nèi)w學生的姓名、學號、所在系。SELECTSname,Sno,SdeptFROMStudent;,選擇表中的若干列（續(xù)）,查詢?nèi)苛羞x出所有屬性列：在SELECT關鍵字后面列出所有列名將指定為*例3.18查詢?nèi)w學生的詳細記錄SELECTSno,Sname,Ssex,Sage,SdeptFROMStudent;或SELECT*FROMStudent;,查詢經(jīng)過計算的值SELECT子句的不僅可以為表中的屬性列，也可以是表達式例3.19查全體學生的姓名及其出生年份。SELECTSname,2014-Sage/*假設當時為2014年*/FROMStudent;輸出結果：Sname2014-Sage李勇1994劉晨1995王敏1996張立1995,查詢經(jīng)過計算的值（續(xù)）,例3.20查詢?nèi)w學生的姓名、出生年份和所在的院系，要求用小寫字母表示系名。SELECTSname,YearofBirth:,2014-Sage,LOWER(Sdept)FROMStudent;輸出結果：SnameYearofBirth:2014-SageLOWER(Sdept)李勇YearofBirth:1994cs劉晨YearofBirth:1995cs王敏YearofBirth:1996ma張立YearofBirth:1995is,查詢經(jīng)過計算的值（續(xù)）,使用列別名改變查詢結果的列標題:SELECTSnameNAME,YearofBirth:BIRTH,2014-SageBIRTHDAY,LOWER(Sdept)DEPARTMENTFROMStudent;輸出結果：NAMEBIRTHBIRTHDAYDEPARTMENT李勇YearofBirth:1994cs劉晨YearofBirth:1995cs王敏YearofBirth: