數(shù)據(jù)庫知識點(diǎn)整理全

1、UNIT1四個(gè)根本概念1.數(shù)據(jù)Data:數(shù)據(jù)庫中存儲(chǔ)的根本對象2.數(shù)據(jù)庫的定義：數(shù)據(jù)庫Database,簡稱DB是長期儲(chǔ)存在計(jì)算機(jī)內(nèi)、有組織的、可共享的大_*據(jù)集合3.數(shù)據(jù)庫治理系統(tǒng)簡稱DBMS：位于用戶與操作系統(tǒng)之間的一層數(shù)據(jù)治理軟件系統(tǒng)軟件,用途:科學(xué)地組織和存儲(chǔ)數(shù)據(jù)；高效地獲取和維護(hù)數(shù)據(jù)主要功能：數(shù)據(jù)定義功能；數(shù)據(jù)操縱功能；數(shù)據(jù)庫的運(yùn)行治理；數(shù)據(jù)庫的建立和維護(hù)功能實(shí)用程序4.數(shù)據(jù)庫系統(tǒng)DatabaseSystem,簡稱DBS：指在計(jì)算機(jī)系統(tǒng)中引入數(shù)據(jù)庫后的系統(tǒng)數(shù)據(jù)庫系統(tǒng)的構(gòu)成數(shù)據(jù)庫數(shù)據(jù)庫治理系統(tǒng)及其開發(fā)工具應(yīng)用系統(tǒng)數(shù)據(jù)庫治理員DBA和用戶數(shù)據(jù)治理技術(shù)的開展過程人工治理階段文件系統(tǒng)階段數(shù)據(jù)

2、庫系統(tǒng)階段數(shù)據(jù)庫系統(tǒng)治理數(shù)據(jù)的特點(diǎn)如下1數(shù)據(jù)共享性高、冗余少；2數(shù)據(jù)結(jié)卞化；3數(shù)據(jù)獨(dú)立性高；4由DBMS進(jìn)行統(tǒng)一的數(shù)據(jù)限制功能數(shù)據(jù)模型用來抽象、表示和處理現(xiàn)實(shí)世界中的數(shù)據(jù)和信息的工具.通俗地講數(shù)據(jù)模型就是現(xiàn)實(shí)世界數(shù)據(jù)的模擬.數(shù)據(jù)模型三要素.數(shù)據(jù)結(jié)構(gòu)：是所研究的對象類型的集合,它是刻畫一個(gè)數(shù)據(jù)模型性質(zhì)最重要的方面；數(shù)據(jù)結(jié)構(gòu)是對系統(tǒng)靜態(tài)特性的描述數(shù)據(jù)操作：對數(shù)據(jù)庫中數(shù)據(jù)允許執(zhí)行的操作及有關(guān)的操作規(guī)那么；對數(shù)據(jù)庫中數(shù)據(jù)的操作主要有查詢和更改包括插入、修改、刪除；數(shù)據(jù)操作是對系統(tǒng)動(dòng)態(tài)特性的描述數(shù)據(jù)的約束條件：數(shù)據(jù)及其聯(lián)系應(yīng)該滿足的條件限制E-R圖實(shí)體：矩形框表示屬性：橢圓形或圓角矩形表示組織層數(shù)據(jù)模型

3、層次模型網(wǎng)狀模型關(guān)系模型用“二維表來表示數(shù)據(jù)之間的聯(lián)系根本概念：關(guān)系Relation:一個(gè)關(guān)系對應(yīng)通常說的一張表元組記錄：表中的一行屬性字段：表中的一列,給每一個(gè)屬性名稱即屬性名分量：元組中的一個(gè)屬性值,分量為最小單位,不可分主碼Key:表中的某個(gè)屬性組,它可以唯一確定一個(gè)元組.域Domain:屬性的取值范圍.關(guān)系模式：對關(guān)系的描述.一般表示為：關(guān)系名屬性1,屬性2,屬性n關(guān)系模型的數(shù)據(jù)完整性約束實(shí)體完整性參照完整性用戶定義的完整性DBS三級模式結(jié)構(gòu)：外模式、概念模式、內(nèi)模式一個(gè)數(shù)據(jù)庫只有一個(gè)內(nèi)模式數(shù)據(jù)庫系統(tǒng)的模式結(jié)構(gòu)聯(lián)系：菱形表示UNIT2在進(jìn)行數(shù)據(jù)庫的操作時(shí),會(huì)出現(xiàn)以下幾方面的問題：1.數(shù)

4、據(jù)冗余；2.插入異常；3.刪除異常；4.更新異常好的關(guān)系模式應(yīng)預(yù)防以上問題函數(shù)依賴設(shè)有關(guān)系模式R(A1,A2,An),X和丫均為A1,A2,An的子集XfY,(1Y不包含于X,那么稱XfY是非平凡的函數(shù)依賴.X-Y,那么稱X為決定因子.Y函數(shù)不依賴于X,那么記作X從外到里依此增加模式分解的準(zhǔn)那么：模式分解具有無損連接性；模式分解能夠保持函數(shù)依賴?標(biāo)準(zhǔn)化理論主要是研究關(guān)系中各屬性之間的依賴關(guān)系,根據(jù)依賴關(guān)系的不同,我們介紹了不包含子屬性的第一范式,到消除了屬性間的局部依賴關(guān)系的第二范式,?再到消除了屬性間的傳遞依賴關(guān)系的第三范式,最后到每個(gè)決定因子都必須是候選碼白BCNE?范式的每一次升級都是通

5、過模式分解實(shí)現(xiàn)的,在進(jìn)行模式分解時(shí)應(yīng)注意保持分解后的關(guān)系能夠具有無損連接性并能保持原有的函數(shù)依賴關(guān)系.?對于一般的數(shù)據(jù)庫應(yīng)用來說,設(shè)計(jì)到第三范式就足夠了.由于標(biāo)準(zhǔn)化程度越高得越細(xì),表的個(gè)數(shù)越多,那么在檢索操作時(shí)會(huì)因連接而降低檢索效率.例：S-D-L(Sno,Dept,Loc)有函數(shù)依賴：Sno-Dept,Dept-Loc不是第三范式的.至少可以有三種分解方案,分別為：方案1:SL(Sno,Loc),D-L(Dept,Loc),將S-D-L分解投影得到SL和D-L關(guān)系方案2：SD(Sno,Dept),S-L(Sno,Loc)將s sI:I:i i一分解投影得到snsn和5-L5-L美毫如果假蹌挈

6、生 40403 3 從 D D2 2 岳 M M 到了 IXIXA A 系,那么需在表 m-Dm-DC CSO3,D2SO3,D2J J改為 4so4so3,3,LKOLKO,M,M 時(shí)坯需矍在表b-Lb-Lt.SEjmt.SEjm 改為 CSO3,Ll.CSO3,Ll.如梁這兩個(gè)修改沒有同時(shí)進(jìn)行.圖數(shù)堀庫中就會(huì)出現(xiàn)不一致 G 軾廠延是由于這樣分解將到的兩不人惠欖 W 沒有小監(jiān) MiMi 來的的曲他*關(guān)第玷眼的.座白的函她依 JftgptJftgpt-*Sc-*Sc 在分后*在了兩個(gè)關(guān)第模式上因此分解方案 E E 沒有保林原有的函數(shù)依箱式奉,也不是好的分*方昧“結(jié)論：方案2 2滿足無損連接性曰

7、沒衣保持倒仃的函救依賴關(guān)凝.SnoSnoTsinTsinSOISOI：Sft2Sft2ririS03S03T2T2S04S04L3L3SnoSnoLoc-Loc-SUISUILlLlS03S031.21.2SO4SO4T.lT.l自燃在接SnoSnoDeptDeptLocSOISOImLlLlSO2SO2D2D21,2l2l2LNLNS04S04D3D3LlLl衣2 23 3如 果如 果如 果如 果范式SOISOIMJSnoSno1 15eji5ejiLocLocSOISOIDlDlLlLlSOISOI03031111SO2SO2D2D2L2L2號 Q3Q3D2D2L2L2S4!4S4!411

8、11111111SO4SO41.11.1結(jié)論二方宰1 1不泄足無拗讓按rtrtXfY,并且X,那么記作X?Y.,分解S-I,S-I,門然然接樹2 22 2方案3:SD(Sno,Dept),D-L(Dept,Loc)方案3:將S-D-L分解投影得到S-D和D-L關(guān)系結(jié)論：方案3既滿足無損連接性,又保持原有的函數(shù)依賴關(guān)系.故它是一個(gè)好的分解方法UNIT3Sql功能命令動(dòng)詞數(shù)據(jù)定義CREATEDROPALTER數(shù)據(jù)查詢SELECT數(shù)據(jù)操縱INSERTUPDATEDELETE數(shù)據(jù)限制GRANTREVOKESQL的數(shù)據(jù)類型：數(shù)值型；字符串型；日期時(shí)間類型；貨幣類型數(shù)據(jù)定義功能操作對象創(chuàng)立語句刪除語句修改

9、語句根本表CREATETABLEDROPTABLEALTERTABLE索引CREATEINDEXDROPINDEX視圖CREATEVIEWDROPVIEW數(shù)據(jù)庫CREATEDATABASEDROPDATABASEALTERDATABASEDeptLocDIDILlLl1)21)2L2L2D3D3LlLl自然連接SnoDeptLocSOISOIDIDILlLlS02S02D2D2L2L2S03S03D2D2L2L2S04S04D3D3LlLl表建立表CREATETABLE1網(wǎng)名諛明救鼾集昭. .ISno-聿號字母中,titi立為T TShjar!Shjar!ft*字符中,住地為來空MgMg字料罪

10、,恂更為2 2W W才或.女.年甘*瞅他|全一心窕/PlPl廝在基字符靠4 4帳原為整默認(rèn)為審事機(jī)墨1 1CREATETABLECREATETABLEStudStud電M(M(SroSroch9F(7ch9F(7jPRIMARYKEYPRIMARYKEYB BSn3frwSn3frwchar(10)char(10)NOTNULL,NOTNULL,S SMXMXth*.(2)th*.(2)CHECKStoCHECKSto$ $I I K K女)SoSo；騏Uni/inlUni/inlCHECK(SagoCHECK(Sago1 115ANDSage15ANDSageCHCH45i45isdwisd

11、wi計(jì)K 抬通那么軟隹兵H H反收0w0w, ,*由g g字拘中,fcJLMKJfcJLMKJ:訓(xùn)dtiiMta1曜也名|字符事,RDURDU出Ccnidal%餐里恒入Kl_Kl_kirrsEE中中ifiAiflifiAiflPvTiludPvTilud學(xué)H HCREATECREATETABLETABLECourtCourt ( (CnoCnochartW)NOTNULL,chartW)NOTNULL,CnflintCnflintu uMrMr建第NOTNULLNOTNULLCdrtdlECdrtdlEtlnyinttlnyintCHCHL LCKCK(Lcredtt0,(Lcredtt0,f

12、ifien*eBleren*eBlerUmyinlUmyinlCHECKSvne1.1cCHECKSvne1.1c,0),0),PorlPorlododkHkHCHECK(P4r|od0).CHECK(P4r|od0).J JCREATETft&LECREATETft&LESC(SC(33ChaitTJMOTNIULL,ChaitTJMOTNIULL,CnoCnoch4r(lD|lNOTNULL,ch4r(lD|lNOTNULL,&0&0加tiny!nt,tiny!nt,CHECK(Grad#0andGrade-100),PRIMARYKEY(CHECK(Gra

13、d#0andGradeFOREIGNKEY(SmREFERENCESStuderit|REFERENCESStuderit|Sn?1.Sn?1.i i_十I I例1.為SC表添加“修課類別歹U,此列的定義為：XKLBchar(4)ALTERTABLESCADDXKLBchar(4)NULL例2.將新添加的XKLB的類型改為char(6).ALTERTABLESCALTERCOLUMNXKLBchar(6)例3.刪除Course表的Period列ALTERTABLECourseDROPCOLUMNPeriod索引：聚簇索引、非聚簇索引：復(fù)合索引是將兩個(gè)字段或多個(gè)字段組合起來建立的索引,而單獨(dú)的字

14、段允許有重復(fù)的值.建立索引格式：createuniqueclusteredindex索弓I名on表名(列名1asc|desc,)unique：用于指定為表創(chuàng)立唯一索引,即不允許存在索引值相同的兩行.clustered：用于指定創(chuàng)立的索引為聚簇索引.默認(rèn)是非聚簇索引.asc升序,desc降序,默認(rèn)為asc例1：為學(xué)生表建立按學(xué)號升序索引createindexstudent_snoonstudent(sno)例2:為選課表按學(xué)號升序和課程號降序建唯一索引createuniqueindexSCIonsc(sno,cnodesc)例3:為學(xué)生表按系升序建立聚簇索引.createclusteredind

15、exTIonstudent(sdept)刪除索引語句根本格式：dropindex表名.索引名例4:刪除表SC的索引SCIdropindexSC.SCI注：索引名前一定要加：表名fMu豆:中樸,KrtKrt為T TJLWJLWL L明印細(xì)皿理地樸FlFlC1I0ilWSilWSvn*.Kitiovn*.Kition n酬啕即sizifnsizifn錯(cuò)刷底博安曹.-100100I ISELECT目標(biāo)歹U名序歹UFROM嗷據(jù)源WHERE檢索條件表達(dá)式GROUPBY分組依據(jù)列HAVING組提取條件ORDERBYjE序依據(jù)列刁ORDERBY排序依據(jù)列簡單查詢單表查詢例1.查詢?nèi)w學(xué)生的學(xué)號與姓名SELE

16、CTSnoSnameFROMStudent比擬大小例8.查詢所有年齡在20歲以下的學(xué)生的姓名及年齡.SELECTSname,SageFROMStudentWHERESage20或：SELECTSname,SageFROMStudentWHERENOTSage=20確定范圍BETWEENAND和NOTBETWEEN-AND確定集合ININ是一個(gè)邏輯運(yùn)算符,可以用來查找屬性值屬于指定集合的元組使用IN的格式為：列名NOTIN常量1,常量2,常量nIN的含義為：當(dāng)列中的值與IN中的某個(gè)常量值相等時(shí),那么結(jié)果為True,說明此記錄為符合查詢條件的記錄；NOTIN的含義正好相反：當(dāng)列中的值與某個(gè)常量值相

17、同時(shí),那么結(jié)果為False,說明此記錄為不符合查詢條件的記錄；例12.查詢信息系、數(shù)學(xué)系和計(jì)算機(jī)系學(xué)生的姓名和性別.SELECTSname,SsexFROMStudentWHERESdeptIN信息系,數(shù)學(xué)系,計(jì)算機(jī)系此句等價(jià)于：SELECTSname,SsexFROMStudentWHERESdept=信息系ORSdept=數(shù)學(xué)系ORSdept=計(jì)算機(jī)系字符匹配LIKE用于查找指定列名與匹配串常量匹配的元組模糊查詢匹配串是一種特殊的字符串,它不僅包含普通字符,還可以包括通配符.通配符用于表示任意的字符或字符串.:匹配任意一個(gè)字符；%:匹配0個(gè)或多個(gè)字符；:匹配中的任意一個(gè)字符；人：不匹配口中

18、的任意一個(gè)字符.LIKE運(yùn)算符的一般形式為：列名NOTLIKE匹配串在LIKE運(yùn)算符前邊也可以使用NOT運(yùn)算符,表示對結(jié)果取反.例14.查詢姓張的學(xué)生的詳細(xì)信息.SELECT*FROMStudentWHERESnameLIKE張%例15.查詢學(xué)生表中姓張、姓李和姓劉的學(xué)生的情況.SELECT*FROMStudentWHERESnameLIKE張李劉%例16.查詢名字中第2個(gè)字為小或大字的學(xué)生的姓名和學(xué)號.SELECTSname,SnoFROMStudentWHERESnameLIKE/出%例17.查詢所有不姓“劉的學(xué)生.SELECTSnameFROMStudentWHERESnameNOTLI

19、KE%-需要哪些列-來自于哪些表-根據(jù)什么條件查詢-對查詢結(jié)果進(jìn)行分組-指定組的選擇條件-對查詢結(jié)果進(jìn)行排序例18.從學(xué)生表中查詢學(xué)號的最后一位不是2、3、5的學(xué)生情況.SELECT*FROMStudentWHERESnoLIKE%A235涉及空值的查詢判斷取值為空的語句格式為：列名ISNULL判斷取值不為空的語句格式為：列名ISNOTNULL例19.查詢無測試成績的學(xué)生的學(xué)號和相應(yīng)的課程號.SELECTSno,CnoFROMSCWHEREGradeISNULL不可寫為WHEREGrade=null多重條件查詢在WHERE子句中可以使用邏輯運(yùn)算符AND和OR來組成多條件查詢用AND連接的條件表

20、示必須全部滿足所有的條件的結(jié)果才為True用OR連接的條件表示只要滿足其中一個(gè)條件結(jié)果即為True對查詢結(jié)果進(jìn)行排序排序子句的格式為：ORDERBY名ASC|DESC,nASC表示對列進(jìn)行升序排序,DESM示對列進(jìn)行降序排序.默認(rèn)為升序排序.使用計(jì)算函數(shù)匯總數(shù)據(jù)例26.計(jì)算9512101號學(xué)生的測試總成績之和SELECTSUM(Grade)FROMSCWHERESno=9512101,對查詢結(jié)果進(jìn)行分組計(jì)算一般形式為：GROUPBY分組依據(jù)列,nHAVING卻提取條件例28.統(tǒng)計(jì)每門課程的選課人數(shù),列出課程號和人數(shù).SELECTCnoasSM程號,COUNT(Sno)as選課人數(shù)FROMSCG

21、ROUPBYCno多表連接查詢1.內(nèi)連接內(nèi)連接的格式為：SELECT屬性或表達(dá)式列表FROM表1INNERJOIN表2ON屬連接條件例32.查詢每個(gè)學(xué)生及其修課的情況SELECT*FROMStudentINNERJOINSCONStudent.Sno=SC.Sno外連接外連接是只限制一張表中的數(shù)據(jù)必須滿足連接條件,而另一張表中數(shù)據(jù)可以不滿足連接條件.ANSI方式的外連接的語法格式為：FROM表1LEFT|RIGHTOUTERJOIN表2ON屬連接條件例39.查詢學(xué)生的修課情況,包括修了課程的學(xué)生和沒有修課的學(xué)生.SELECTStudent.Sno,Sname,Cno,GradeFROMStud

22、entLEFTOUTERJOINSCONStudent.Sno=SC.Sno也可以用右外連接實(shí)現(xiàn)：SELECTStudent.Sno,Sname,Cno,GradeFROMSCRIGHTOUTERJOINStudentONStudent.Sno=SC.Sno使用子查詢進(jìn)行基于集合的測試?yán)?0.查詢與“劉晨在同一個(gè)系學(xué)習(xí)的學(xué)生.SELECTSno,Sname,SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname=劉晨)使用子查詢進(jìn)行基于集合的測試?yán)?1.查詢成績?yōu)榇笥?0分的學(xué)生的學(xué)號、姓名.SELECTSno,SnameFR

23、OMStudentWHERESnoIN(SELECTSnoFROMSWHEREGrade90)使用子查詢進(jìn)行比擬測試通過比擬運(yùn)算符(=、=),將一個(gè)表達(dá)式的值與子查詢返回的值進(jìn)行比擬.例43.查詢修了C02課程且成績高于此課程的平均成績的學(xué)生的學(xué)號和成績.SELECTSno,GradeFROMSCWHERECno=c02andGrade(SELECTAVG(Grade)fromSCWHERECno=c02)使用子查詢進(jìn)行存在性測試?yán)?4.查詢選修了C01號課程的學(xué)生姓名.SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Studen

24、t.SnoANDCno=c01)數(shù)據(jù)更改功能插入數(shù)據(jù)INSERTS句的格式為：INSERTINTO襲名(列名表)VALUES(值列表)例1.將新生記錄(9521105,陳冬,男,信息系,18歲)插入到Student表中.INSERTINTOStudentVALUES(9521105,陳冬,男,18,信息I系)更新數(shù)據(jù)(UPDATE)UPDATED句的語法格式為：UPDATE表名SET列名=表達(dá)式,nWHEREM新條件例2.將9512101學(xué)生的年齡改為21歲.UPDATEStudentSETSage=21WHERESno=9512101刪除數(shù)據(jù)(DELETE)DELETED句的語法格式為：DE

25、LETEFROM表名WHERE0除條件例3.刪除計(jì)算機(jī)系不及格學(xué)生的修課記錄用子查詢實(shí)現(xiàn)：DELETEFROMSCWHEREGrade60ANDSnoIN(SELECTSnoFROMStudeWHERESdept=計(jì)算機(jī)系)用多表連接實(shí)現(xiàn)：DELETEFROMSCFROMSCJOINStudentONSC.Sno=Student.SnoWHERESdept=計(jì)算機(jī)系A(chǔ)NDGrade視圖列名表AS子查詢語句例1.建立信息系學(xué)生的視圖.CREATEVIEWIS_StudentASSELECTSno,Sname,SageFROMStudentWHERESdept=信息系例4.定義一個(gè)反映學(xué)生出生年份

26、的視圖CREATEVIEWBT_SSno,Sname,SbirthASSELECTSno,Sname,2006SageFROMStudent刪除視圖DROPVIEW硼圖名視圖可簡化查詢語句例：將信息系學(xué)生的視圖中學(xué)號為9512102的學(xué)生姓名改為“劉呈UPDATEIS_StudentSETSnameW呈WHERESno=9512102例：在信息系學(xué)生的視圖中找出年齡小于20歲的學(xué)生SELECTSno,SageFROMIS_StudentWHERESage,nLOGON,n例2:創(chuàng)立一個(gè)數(shù)據(jù)庫,數(shù)據(jù)庫名稱為：“人事信息數(shù)據(jù)庫,此數(shù)據(jù)庫包含一個(gè)數(shù)據(jù)文件和一個(gè)事務(wù)日志文件.數(shù)據(jù)文件只有主數(shù)據(jù)文件,其

27、邏輯文件名為“人事信息數(shù)據(jù)庫,其物理文件名為“人事信息數(shù)據(jù)庫.mdf,存放位置在默認(rèn)目錄下,其初始大小為i0MB,最大大小為30MB,自動(dòng)增長時(shí)的遞增量為5MB.事務(wù)日志文件的邏輯文件名為“人事信息日志,物理文件名為“人事信息日志.ldf,也存放在默認(rèn)目錄下,初始大小為3MB,最大大小為12MB,自動(dòng)增長時(shí)的遞增量為2MB.CREATEDATABASE人事信息數(shù)據(jù)庫ON(NAME=人事信息數(shù)據(jù)庫,人事信息數(shù)據(jù)庫.mdf,SIZE=10,MAXSIZE=30,FILEGROWTH=5)LOGON(NAME=人事信息日志,人事信息日志.ldf,SIZE=3,MAXSIZE=12,FILEGROWTH=2)使用Transact-SQL語句刪除數(shù)據(jù)庫語法格式為：DROPDATABAS瞰據(jù)庫名,n例：用SQL語句刪除Testi和Test2數(shù)據(jù)庫：DROPDATABASETesti,Test2使用Transact-SQL語句修改數(shù)據(jù)庫(ALTERDATABASE)創(chuàng)立觸發(fā)器(CREATETRIGGER)使用SQL語句治理權(quán)限(1)授權(quán)語句GRANT例1:為用戶useri授予Student表的查詢權(quán).GRANTSELECTONStudentTOuser1例2：為用戶user1授予SC表的查詢權(quán)和插入權(quán).GRANTSELECTINSERTONSCTOuser1例3:授予user1和