數(shù)據(jù)庫(kù)系統(tǒng)概論第3章課件

第三章關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL3.1SQL概述3.2數(shù)據(jù)定義3.3數(shù)據(jù)查詢3.4數(shù)據(jù)更新3.5視圖第三章關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL3.1SQL概述13.1SQL概述SQL（StructuredQueryLanguage）結(jié)構(gòu)化查詢語(yǔ)言，是關(guān)系數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)語(yǔ)言數(shù)據(jù)查詢數(shù)據(jù)定義數(shù)據(jù)操縱數(shù)據(jù)控制3.1SQL概述SQL（StructuredQuery2數(shù)據(jù)庫(kù)系統(tǒng)概論第3章課件SQL視圖2視圖1基本表2基本表1基本表3基本表4存儲(chǔ)文件2存儲(chǔ)文件1外模式模式內(nèi)模式SQL支持關(guān)系數(shù)據(jù)庫(kù)三級(jí)模式結(jié)構(gòu)SQL視圖2視圖1基本表2基本表1基本表3基本表4存儲(chǔ)文件243.2數(shù)據(jù)定義SQL的數(shù)據(jù)定義功能:3.2數(shù)據(jù)定義SQL的數(shù)據(jù)定義功能:53.2.1模式的定義與刪除CREATESCHEMA<模式名>AUTHORIZATION<用戶名>[<表定義子句>|<視圖定義子句>|<授權(quán)定義子句>]CREATESCHEMAStuCourseAUTHORIZATIONWANG;DROPSCHEMA<模式名><CASCADE|RESTRICT>DROPSCHEMAStuCourseRESTRICT3.2.1模式的定義與刪除CREATESCHEMA<模63.2.2基本表的定義、刪除與修改（1）基本表的定義CREATETABLE<表名>（<列名><數(shù)據(jù)類型>[<列級(jí)完整性約束條件>][，<列名><數(shù)據(jù)類型>[<列級(jí)完整性約束條件>]]…[，<表級(jí)完整性約束條件>]）；3.2.2基本表的定義、刪除與修改（1）基本表的定義7課程表Course[例]建立一個(gè)“課程”表CourseCREATETABLECourse(CnoCHAR(4)PRIMARYKEY，CnameCHAR(40)，CpnoCHAR(4)， CcreditSMALLINT，F(xiàn)OREIGNKEY(Cpno)REFERENCESCourse(Cno));先修課Cpno是外碼被參照表是Course被參照列是Cno課程表Course[例]建立一個(gè)“課程”表Course先8問題下面建立一個(gè)“學(xué)生選課”表SC方法是否正確？ CREATESC (SnoCHAR(9)PRIMARYKEYnull， CnoCHAR(4)PRIMARYKEY， GradeSMALLINTnull， FOREIGNKEY(Sno)REFERENCESStudent(Sno)， FOREIGNKEY(Cno)REFERENCESCourse(Cno)， );問題下面建立一個(gè)“學(xué)生選課”表SC方法是否正確？9Sqlserver主要數(shù)據(jù)類型數(shù)據(jù)類型符號(hào)標(biāo)識(shí)數(shù)據(jù)類型符號(hào)標(biāo)識(shí)整數(shù)型bigint,int,smallint,tinyintUnicode字符型nchar,nvarchar、精確數(shù)值型decimal,numeric文本型text,ntext浮點(diǎn)型float,real二進(jìn)制型binary,varbinary貨幣型money,smallmoney日期時(shí)間類型datetime,smalldatetime,date,time位型bit時(shí)間戳型timestamp字符型char,varchar圖像型image其他cursor,sql_variant,table,uniqueidentifier,xmlSqlserver主要數(shù)據(jù)類型數(shù)據(jù)類型符號(hào)標(biāo)3.2.2基本表的定義、刪除與修改（2）修改基本表ALTERTABLE<表名>[ADD<新列名><數(shù)據(jù)類型>[完整性約束]][DROP<完整性約束名>][ALTERCOLUMN<列名><數(shù)據(jù)類型>]；例：向Student表增加“入學(xué)時(shí)間”列，其數(shù)據(jù)類型為日期型。ALTERTABLEStudentADDS_entranceDATE；3.2.2基本表的定義、刪除與修改（2）修改基本表113.2.2基本表的定義、刪除與修改（3） 刪除基本表DROPTABLE<表名>［RESTRICT|CASCADE］；例:刪除Student表，同時(shí)刪除表上定義的索引等其他對(duì)象DROPTABLEStudentCASCADE;3.2.2基本表的定義、刪除與修改（3） 刪除基本表123.2.3索引的建立與刪除索引的建立CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…)；CREATECLUSTERINDEXStusnameONStudent(Sname)；索引的刪除DROPINDEXStusname；3.2.3索引的建立與刪除索引的建立133.3數(shù)據(jù)查詢SELECT[ALL|DISTINCT]<目標(biāo)列表達(dá)式>[，<目標(biāo)列表達(dá)式>]…FROM<表名或視圖名>[，<表名或視圖名>]…[WHERE<條件表達(dá)式>][GROUPBY<列名1>[HAVING<條件表達(dá)式>]][ORDERBY<列名2>[ASC|DESC]]；

3.3數(shù)據(jù)查詢SELECT[ALL|DISTINCT]<143.3數(shù)據(jù)查詢3.3.1單表查詢3.3.2連接查詢3.3.3嵌套查詢3.3.4集合查詢3.3.5Select語(yǔ)句的一般形式

3.3數(shù)據(jù)查詢3.3.1單表查詢153.3.1單表查詢查詢僅涉及一個(gè)表：選擇表中的若干列選擇表中的若干元組ORDERBY子句聚集函數(shù)GROUPBY子句Having子句3.3.1單表查詢查詢僅涉及一個(gè)表：16選擇表中的若干列[例1]選擇部分列

SELECTSno，SnameFROMStudent；

[例]選擇所有列SELECT*FROMStudent[例]獲得經(jīng)過計(jì)算的列SELECTSname，2012-SageFROMStudent；[例]使用列別名SELECTSname，2012-Sageyear-of-birthFROMStudent；

選擇表中的若干列[例1]選擇部分列17選擇表中的若干元組（1）消除取值重復(fù)的行：DISTINCT

SELECTDISTINCTSnoFROMSC；

選擇表中的若干元組（1）消除取值重復(fù)的行：DISTINCT18選擇表中的若干元組（2）查詢條件謂詞比較=，>，<，>=，<=，!=，<>，!>，!<；NOT+上述比較運(yùn)算符確定范圍BETWEENAND，NOTBETWEENAND確定集合IN，NOTIN字符匹配LIKE，NOTLIKE空值ISNULL，ISNOTNULL多重條件（邏輯運(yùn)算）AND，OR，NOT選擇表中的若干元組（2）查詢條件謂詞比比較大小SELECTDISTINCTSnoFROMSCWHEREGrade<60；確定范圍SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；

比較大小20確定集合SELECTSname，Ssex FROMStudent WHERESdeptIN('IS'，'MA'，'CS');字符匹配SELECT*FROMStudentWHERESnameLIKE‘劉%’；確定集合21空值的查詢SELECTSno，CnoFROMSCWHEREGradeISNULL多重條件查詢

SELECTSnameFROMStudentWHERESdept='CS'ANDSage<20；空值的查詢22ORDERBY子句可以按一個(gè)或多個(gè)屬性列排序SELECT*FROMStudentORDERBYSdept，SageDESC；當(dāng)排序列含空值時(shí)ASC：排序列為空值的元組最后顯示DESC：排序列為空值的元組最先顯示ORDERBY子句可以按一個(gè)或多個(gè)屬性列排序23聚集函數(shù)計(jì)數(shù)COUNT（[DISTINCT|ALL]<列名>）計(jì)算總和SUM（[DISTINCT|ALL]<列名>） 計(jì)算平均值A(chǔ)VG（[DISTINCT|ALL]<列名>）最值

MAX（[DISTINCT|ALL]<列名>） MIN（[DISTINCT|ALL]<列名>）聚集函數(shù)計(jì)數(shù)24

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

SELECTCOUNT(*)FROMStudent；

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

SELECTCOUNT(DISTINCTSno)FROMSC；[例]查詢學(xué)生總?cè)藬?shù)。25GROUPBY子句對(duì)查詢的中間結(jié)果表進(jìn)行分組，以細(xì)化聚集函數(shù)的作用[例]求各個(gè)課程號(hào)及相應(yīng)的選課人數(shù)。SELECTCno，COUNT(Sno)FROMSCGROUPBYCno；CnoCOUNT(Sno)122234344433548GROUPBY子句對(duì)查詢的中間結(jié)果表進(jìn)行分組，以細(xì)化聚集26having子句對(duì)分組后的數(shù)據(jù)進(jìn)行篩選，選出滿足條件的組[例]查詢選修了3門以上課程的學(xué)生學(xué)號(hào)。

SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)>3；

having子句對(duì)分組后的數(shù)據(jù)進(jìn)行篩選，選出滿足條件的組27問題Select、from、where、orderby、groupby、having子句在查詢過程中的執(zhí)行順序？問題Select、from、where、orderby、g283.3.2連接查詢連接條件或連接謂詞：用來(lái)連接兩個(gè)表的條件

[<表1>.]<列1><比較運(yùn)算符>

[<表2>.]<列2>[<表1>.]<列1>BETWEEN[<表2>.]<列2>AND[<表2>.]<列3>連接操作的執(zhí)行方法嵌套循環(huán)法(NESTED-LOOP)排序合并法(SORT-MERGE)索引連接(INDEX-JOIN3.3.2連接查詢連接條件或連接謂詞：用來(lái)連接兩個(gè)表的條29連接查詢的種類一、等值與非等值連接查詢二、自身連接三、外連接四、復(fù)合條件連接連接查詢的種類一、等值與非等值連接查詢30等值與非等值連接查詢查詢學(xué)生的姓名及獲得的成績(jī)SELECTStudent.sname,SC.gradeFROMStudent,SC WHEREStudent.Sno=SC.Sno注意：當(dāng)兩個(gè)以上的表具有相同的列名時(shí)，一定要再列名前加上表名作為限制問題：此查詢有多少條記錄滿足條件？等值與非等值連接查詢查詢學(xué)生的姓名及獲得的成績(jī)問題：此查詢31自身連接查詢存在間接先行課程的課程號(hào)及其間接先行課程號(hào)SELECTFIRST.Cno，SECOND.CpnopcnoFROMCourseFIRST，CourseSECONDWHEREFIRST.Cpno=SECOND.Cno；CnoPcno173556自身連接查詢存在間接先行課程的課程號(hào)及其間接先行課程號(hào)Cn32外連接(1)查詢每門課程號(hào)及其間接先行課程號(hào)（如果存在的話）SELECTFIRST.Cno，SECOND.CpnoFROMCourseFIRSTLEFTOUTJOINCourseSECONDON（FIRST.Cno=SECOND.Cpno)

FoSecond.cpno172null354null566null7null外連接(1)查詢每門課程號(hào)及其間接先行課程號(hào)（如果存在的話）33外連接（2）

左外連接列出左邊關(guān)系（如本例Student）中所有的元組右外連接列出右邊關(guān)系中所有的元組全外連接列出兩個(gè)連接表中的所有元組外連接（2）左外連接34復(fù)合條件連接復(fù)合條件連接：WHERE子句中含多個(gè)連接條件查詢選修2號(hào)課程且成績(jī)?cè)?0分以上的所有學(xué)生 SELECTStudent.Sno,Sname FROMStudent,SC WHEREStudent.Sno=SC.SnoANDSC.Cno=‘2’ANDSC.Grade>90；

復(fù)合條件連接復(fù)合條件連接：WHERE子句中含多個(gè)連接條件353.3.3嵌套查詢一個(gè)SELECT-FROM-WHERE語(yǔ)句稱為一個(gè)查詢塊嵌套查詢:嵌套兩個(gè)以上的查詢塊SELECTSname /*外層查詢/父查詢*/FROMStudentWHERESnoIN（SELECTSno/*內(nèi)層查詢/子查詢*/FROMSCWHERECno=‘2’）；

3.3.3嵌套查詢一個(gè)SELECT-FROM-WHERE語(yǔ)句36帶有比較運(yùn)算符的子查詢（1）當(dāng)能確切知道內(nèi)層查詢返回單值時(shí)，可用比較運(yùn)算符（>，<，=，>=，<=，!=或<>）。與ANY、some、ALL謂詞配合使用帶有比較運(yùn)算符的子查詢（1）當(dāng)能確切知道內(nèi)層查詢返回單值時(shí)37帶有比較運(yùn)算符的子查詢（2）查詢與劉晨同一學(xué)院的學(xué)生SELECTSno，Sname，SdeptFROMStudentWHERESdept=(SELECTSdeptFROMStudentWHERESname=‘劉晨’)；帶有比較運(yùn)算符的子查詢（2）查詢與劉晨同一學(xué)院的學(xué)生38帶有比較運(yùn)算符的子查詢（3）［例41］找出每個(gè)學(xué)生超過他選修課程平均成績(jī)的課程號(hào)。

SELECTSno，CnoFROMSCxWHEREGrade>=(SELECTAVG(Grade) FROMSCy

WHEREy.Sno=x.Sno);相關(guān)子查詢帶有比較運(yùn)算符的子查詢（3）［例41］找出每個(gè)學(xué)生超過他選修39帶有ANY（SOME）或ALL謂詞的子查詢（1）某一值需要與多值比較時(shí)的妥協(xié)方法（配合比較運(yùn)算符使用）

比較含義比較含義>ANY大于子查詢結(jié)果中的某個(gè)值<=ANY小于等于子查詢結(jié)果中的某個(gè)值>ALL大于子查詢結(jié)果中的所有值<=ALL小于等于子查詢結(jié)果中的所有值<ANY小于子查詢結(jié)果中的某個(gè)值=ANY等于子查詢結(jié)果中的某個(gè)值<ALL小于子查詢結(jié)果中的所有值=ALL等于子查詢結(jié)果中的所有值>=ANY大于等于子查詢結(jié)果中的某個(gè)值!=（或<>）ANY不等于子查詢結(jié)果中的某個(gè)值>=ALL大于等于子查詢結(jié)果中的所有值!=（或<>）ALL不等于子查詢結(jié)果中的任何一個(gè)值

帶有ANY（SOME）或ALL謂詞的子查詢（1）某一值需要與40帶有ANY（SOME）或ALL謂詞的子查詢(2)[例]查詢其他系中比計(jì)算機(jī)科學(xué)某一學(xué)生年齡小的學(xué)生姓名和年齡SELECTSname，SageFROMStudentWHERESage<ANY(SELECTSageFROMStudentWHERESdept='CS')

ANDSdept<>‘CS’;帶有ANY（SOME）或ALL謂詞的子查詢(2)[例]查41帶有EXISTS謂詞的子查詢(1)EXISTS(…)->trueEXISTS()->FalseNOTEXISTS()->trueNOTEXISTS(…)->false帶有EXISTS謂詞的子查詢(1)EXISTS(…)->42[例]查詢所有選修了1號(hào)課程的學(xué)生姓名。SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno='1')；

[例]查詢所有選修了1號(hào)課程的學(xué)生姓名。43[例]查詢選修了全部課程的學(xué)生姓名。

SELECTSnameFROMStudentWHERENOTEXISTS（SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=Course.Cno））；[例]查詢選修了全部課程的學(xué)生姓名。44[例]查詢至少選修了學(xué)生200215122選修的全部課程的學(xué)生號(hào)碼。SELECTDISTINCTSnoFROMSCSCXWHERENOTEXISTS(SELECT*FROMSCSCYWHERESCY.Sno='200215122'ANDNOTEXISTS(SELECT*FROMSCSCZWHERESCZ.Sno=SCX.SnoANDSCZ.Cno=SCY.Cno))；[例]查詢至少選修了學(xué)生200215122選修的全部課程的453.3.4集合查詢并SELECTSnoFROMSCWHERECno='1'UNIONSELECTSnoFROMSCWHERECno='2'；；交SELECTSnoFROMSCWHERECno='1‘INTERSECTSELECTSnoFROMSCWHERECno=‘2’；差SELECTSnoFROMSCWHERECno='1‘EXCEPTSELECTSnoFROMSCWHERECno='2'；3.3.4集合查詢并交差463.3.5SELECT語(yǔ)句的一般格式SELECT[ALL|DISTINCT]<目標(biāo)列表達(dá)式>[別名][，<目標(biāo)列表達(dá)式>[別名]]…FROM

<表名或視圖名>[別名][，<表名或視圖名>[別名]]…[WHERE<條件表達(dá)式>][GROUPBY<列名1>[HAVING

<條件表達(dá)式>]][ORDERBY<列名2>[ASC|DESC]

3.3.5SELECT語(yǔ)句的一般格式SELECT[AL473.4數(shù)據(jù)更新3.4.1插入數(shù)據(jù)3.4.2修改數(shù)據(jù)3.4.3刪除數(shù)據(jù)

3.4數(shù)據(jù)更新3.4.1插入數(shù)據(jù)483.4.1插入數(shù)據(jù)（1）插入單行將一個(gè)新學(xué)生元組（學(xué)號(hào)：201115128；課程號(hào)：2；成績(jī)：95）插入到SC表中。

INSERTINTOSC(Sno,Cno,grade)VALUES('201115128'，'

2'，95)；3.4.1插入數(shù)據(jù)（1）插入單行INSERTINT493.4.1插入數(shù)據(jù)（2）同時(shí)插入多行計(jì)算每個(gè)系的學(xué)生的平均年齡，并把結(jié)果存入在表avgage（Sdept，age）（假定表avgage已經(jīng)存在）INSERTINTOavgage（sdept，age）SELECTSdept，AVG(Sage)FROMStudentGROUPBYSdept3.4.1插入數(shù)據(jù)（2）同時(shí)插入多行503.4.2修改數(shù)據(jù)語(yǔ)句格式UPDATE<表名>SET<列名>=<表達(dá)式>[，<列名>=<表達(dá)式>]…[WHERE<條件>]；UPDATEStudentSETSage=22WHERESno='200215121'；注意：修改后的數(shù)據(jù)必須滿足表的完整性約束3.4.2修改數(shù)據(jù)語(yǔ)句格式513.4.3刪除