軟件開(kāi)發(fā)技術(shù)數(shù)據(jù)庫(kù)復(fù)習(xí)市公開(kāi)課金獎(jiǎng)市賽課一等獎(jiǎng)?wù)n件

1、數(shù)據(jù)庫(kù)第三章第1頁(yè)數(shù)據(jù)庫(kù)設(shè)計(jì)過(guò)程E/R圖到關(guān)系模式轉(zhuǎn)換關(guān)系模式規(guī)范化需求分析概念模式設(shè)計(jì)邏輯模式設(shè)計(jì)數(shù)據(jù)庫(kù)實(shí)施E/R圖UMLRDBMS模式存放模式數(shù)據(jù)庫(kù)運(yùn)行和維護(hù)第2頁(yè)概念模式設(shè)計(jì)ER圖實(shí)體聯(lián)絡(luò)模型（Entity-Relationship Model，簡(jiǎn)稱E-R模型）第3頁(yè)E-R模型基本概念 實(shí)體(entity)：客觀存在，能夠相互區(qū)分事物稱為實(shí)體。 實(shí)體集(entity set)：性質(zhì)相同同類(lèi)實(shí)體集合，稱實(shí)體集。 屬性(attribute)：實(shí)體有很多特征，每一個(gè)特征稱為屬性。每一個(gè)屬性有一個(gè)值域，其類(lèi)型能夠是整數(shù)型、實(shí)數(shù)型、字符串型等。 實(shí)體標(biāo)識(shí)符(identifier)：能惟一標(biāo)識(shí)實(shí)體屬

2、性或?qū)傩约Q 為實(shí)體標(biāo)識(shí)符。有時(shí)也稱為關(guān)鍵碼(key)，或簡(jiǎn)稱為鍵。 聯(lián)絡(luò)(relationship)是實(shí)體之間相互關(guān)系。 數(shù)據(jù)模型: 能表示實(shí)體類(lèi)型及實(shí)體間聯(lián)絡(luò)模型稱為“數(shù)據(jù)模型”。 第4頁(yè)E-R圖基本組成部分 E/R圖有四個(gè)主要部分：（1）實(shí)體集，用矩形表示。（2）屬性，用橢圓形表示。（3）聯(lián)絡(luò)，用菱形表示。（4）直線，表示聯(lián)絡(luò)類(lèi)型及實(shí)體與實(shí)體之間聯(lián)絡(luò)；第5頁(yè)數(shù)據(jù)聯(lián)絡(luò) 聯(lián)絡(luò)（relationship）是實(shí)體之間相互關(guān)系。 二元聯(lián)絡(luò)有以下三種類(lèi)型：一對(duì)一聯(lián)絡(luò)：實(shí)體集E1中每個(gè)實(shí)體至多和實(shí)體集E2中一個(gè)實(shí)體有聯(lián)絡(luò)，反之亦然，那么實(shí)體集E1和E2聯(lián)絡(luò)稱為“一對(duì)一聯(lián)絡(luò)”，記為“1:1”。一對(duì)多聯(lián)

3、絡(luò)：實(shí)體集E1中每個(gè)實(shí)體能夠與實(shí)體集E2中任意個(gè)（零個(gè)或多個(gè)）實(shí)體間有聯(lián)絡(luò)，而E2中每個(gè)實(shí)體至多和E1中一個(gè)實(shí)體有聯(lián)絡(luò)，那么稱E1對(duì)E2聯(lián)絡(luò)是“一對(duì)多聯(lián)絡(luò)”，記為“1:N”。 多對(duì)多聯(lián)絡(luò)：假如實(shí)體集E1中每個(gè)實(shí)體能夠與實(shí)體集E2中任意個(gè)（零個(gè)或多個(gè)）實(shí)體有聯(lián)絡(luò)，反之亦然，那么稱E1和E2聯(lián)絡(luò)是“多對(duì)多聯(lián)絡(luò)”，記為“M:N”。 第6頁(yè)三種聯(lián)絡(luò)E-R圖實(shí)體1實(shí)體2聯(lián)絡(luò)實(shí)體1實(shí)體2聯(lián)絡(luò)實(shí)體1實(shí)體2聯(lián)絡(luò) 11聯(lián)絡(luò) 1n聯(lián)絡(luò) mn聯(lián)絡(luò)第7頁(yè)三種聯(lián)絡(luò)E-R圖客人飛機(jī)座位號(hào)聯(lián)絡(luò)院系教師聯(lián)絡(luò)課程學(xué)生聯(lián)絡(luò) 11聯(lián)絡(luò) 1n聯(lián)絡(luò) mn聯(lián)絡(luò)第8頁(yè)E-R圖實(shí)例例：設(shè)計(jì)一個(gè)學(xué)生選課數(shù)據(jù)庫(kù)。要求包含學(xué)生(student)、

4、課程(course)和教師(teacher)信息。 實(shí)體中存在以下聯(lián)絡(luò)： 一個(gè)學(xué)生能夠選修多門(mén)課，而每門(mén)課也能由多個(gè)學(xué)生選修；一位教師能夠教多門(mén)課，每門(mén)課也能夠由多個(gè)教師講授；用E-R圖來(lái)描述此數(shù)據(jù)庫(kù)。 學(xué)生信息：學(xué)號(hào)(sNo)、學(xué)生姓名(sName)、年紀(jì)(age)、地址(sAddress)課程信息：課程號(hào)(cNo)、課程名(cName)、課程學(xué)分(credit)教師信息：工號(hào)(tNo)、姓名(tName)、教師地址(tAddress)第9頁(yè)學(xué)生-課程-教師E-R圖studentcourseteachertaketeachcreditscNamecNumsAddressagesNamesNo

5、tAddresstNametNoscore注意：假如一個(gè)聯(lián)絡(luò)含有屬性，則這些屬性也與該聯(lián)絡(luò)連接起來(lái)。TeachCourseNo第10頁(yè)概念模式設(shè)計(jì)UML從用例圖分析出對(duì)象StudentStudentstudentNostudentNostudentNamestudentNamesexsexdepartmentIddepartmentIdmajormajor第11頁(yè)概念模式設(shè)計(jì)UMLTeacherteacherIdteacherNamesex對(duì)象之間關(guān)系DepartmentDepartmentIddeptNamelocation工作第12頁(yè)概念模式設(shè)計(jì)UML對(duì)象之間關(guān)系分類(lèi)一元關(guān)系第13頁(yè)概念模

6、式設(shè)計(jì)UMLTeacherteacherIdteacherNamesex對(duì)象之間關(guān)系分類(lèi)DepartmentDepartmentIddeptNamelocation工作二元關(guān)系第14頁(yè)概念模式設(shè)計(jì)UMLTeacherteacherIdteacherNamesex關(guān)系重?cái)?shù)DepartmentDepartmentIddeptNamelocation1.n1重?cái)?shù)1.n重?cái)?shù)1第15頁(yè)概念模式設(shè)計(jì)UML關(guān)聯(lián)2個(gè)對(duì)象關(guān)聯(lián)產(chǎn)生屬性第16頁(yè)關(guān)系模型結(jié)構(gòu) - 數(shù)據(jù)模型三要素之一 一個(gè)關(guān)系邏輯結(jié)構(gòu)是一張二維表，它由行和列組成。關(guān)系關(guān)系名第17頁(yè)元組：表中一行即為一個(gè)元組或統(tǒng)計(jì)；字段：表中一列，列值稱為字段值，列名

7、稱為字段名值域：字段值取值范圍元組 (統(tǒng)計(jì)）屬性 (字段)第18頁(yè)主鍵：能唯一標(biāo)識(shí)一個(gè)元組屬性集合。外鍵：若屬性組F不是關(guān)系R1主鍵，但它是關(guān)系R2主 鍵，則F稱是關(guān)系R1外鍵。主鍵主鍵外鍵第19頁(yè)關(guān)系完整性域完整性 實(shí)體完整性 參考完整性 第20頁(yè)1）第一范式（First Normal Form，1NF） 定義： 假如一個(gè)關(guān)系模式R全部屬性都是不可分基 本 數(shù)據(jù)項(xiàng)，則關(guān)系R屬于第一范式。p第21頁(yè)2）第二范式（ Second Normal Form，2NF ） 定義：若關(guān)系模式R屬于第一范式，且每個(gè)非主屬性都完 全函數(shù)依賴于主鍵，則R屬于第二范式。t第22頁(yè)3）第三范式定義：假如一個(gè)關(guān)系R是

8、第二范式，而且每個(gè)非主屬性 都不傳遞依賴于主鍵，則R屬于第三范式。 第23頁(yè)4、E-R(UML)模型到關(guān)系模式轉(zhuǎn)換1）實(shí)體(對(duì)象)到關(guān)系模式轉(zhuǎn)換2）聯(lián)絡(luò)(關(guān)系)到關(guān)系模式轉(zhuǎn)換第24頁(yè)1）實(shí)體到關(guān)系模式轉(zhuǎn)換 規(guī)則：將實(shí)體名稱作為關(guān)系名稱，將實(shí)體屬性作為關(guān)系屬性，并用下劃線標(biāo)識(shí)出主屬性。 studentcoursetakesNosnamesaddressage cNo cnamecreditsgradestudent（sNo,sName,sex,age），表示student關(guān)系，其中sNo為主鍵。course（cNo,cName,credits），表示course關(guān)系，其中cNo為主鍵。第25頁(yè)2

9、）聯(lián)絡(luò)到關(guān)系模式轉(zhuǎn)換 1:1 若實(shí)體間聯(lián)絡(luò)是1:1，能夠在兩個(gè)實(shí)體轉(zhuǎn)換成兩個(gè)關(guān)系模式后，然后在其中任意一個(gè)關(guān)系模式屬性集中加入另一個(gè)關(guān)系模式主鍵和聯(lián)絡(luò)本身屬性。 也就是說(shuō)1:1聯(lián)絡(luò)不單獨(dú)轉(zhuǎn)換為一個(gè)獨(dú)立關(guān)系。第26頁(yè)2）聯(lián)絡(luò)到關(guān)系模式轉(zhuǎn)換 1:N 若實(shí)體間聯(lián)絡(luò)是1:N，則在N端實(shí)體轉(zhuǎn)換成關(guān)系模式中加入1端實(shí)體主鍵和聯(lián)絡(luò)屬性。 一樣1:N聯(lián)絡(luò)也不獨(dú)立組成一個(gè)關(guān)系。TeacherteacherIdteacherNamesexDepartmentDepartmentIddeptNamelocation工作二元關(guān)系第27頁(yè)2）聯(lián)絡(luò)到關(guān)系模式轉(zhuǎn)換 M:N若實(shí)體間聯(lián)絡(luò)是M:N，則將聯(lián)絡(luò)轉(zhuǎn)換成一個(gè)關(guān)系模式，其

10、屬性為兩端實(shí)體主鍵加上聯(lián)絡(luò)本身屬性，而聯(lián)絡(luò)關(guān)系主鍵為一復(fù)合鍵，通常是兩端實(shí)體主鍵組合。 第28頁(yè)對(duì)于“學(xué)生選課”E/R圖課程和學(xué)生之間多對(duì)多聯(lián)絡(luò)，可轉(zhuǎn)換成關(guān)系模式： take（sNo,cNo,grade） 主鍵為復(fù)合鍵sNo,cNo。 studentcoursetakesNosnamesaddressage cNo cnamecreditsgrade例：第29頁(yè)關(guān)系數(shù)據(jù)庫(kù)組織表1表2表3DBColumn or FieldRecord第30頁(yè)SQL 字段類(lèi)型Char varchar int dateTime第31頁(yè)字段屬性 請(qǐng)與Insert字段長(zhǎng)度 對(duì)于字符類(lèi)型，比如char和varchar，定

11、義字段同時(shí)能夠指定字段存放字符個(gè)數(shù)，比如定義姓名字段為 name varchar(20)，表示該字段最多存放20個(gè)字符。數(shù)值型字段，除了decimal由用戶自己定義長(zhǎng)度和小數(shù)位數(shù)外，其它如int, float等，系統(tǒng)自定義其占用字節(jié)數(shù)。小數(shù)位數(shù) 用于decimal和貨幣類(lèi)型字段標(biāo)識(shí)種子 用于數(shù)據(jù)庫(kù)表統(tǒng)計(jì)號(hào)自動(dòng)增加，經(jīng)慣用于做表中統(tǒng)計(jì)主鍵。用于整型數(shù)值.默認(rèn)值 當(dāng)表中新增加一個(gè)統(tǒng)計(jì)時(shí)，又不想詳細(xì)指定某個(gè)字段值。能夠給它一個(gè)默認(rèn)值。非空限制 用于限制字段值不能為空，比如學(xué)生姓名字段，每一個(gè)統(tǒng)計(jì)都必須有值，因?yàn)閷W(xué)生不能沒(méi)有名字。索引 對(duì)字段進(jìn)行索引，能夠經(jīng)過(guò)設(shè)定該字段為查詢條件來(lái)加緊數(shù)據(jù)檢索速度和統(tǒng)

12、計(jì)、分組速度。關(guān)鍵字 關(guān)系模式中主鍵，用于唯一標(biāo)識(shí)數(shù)據(jù)庫(kù)表中統(tǒng)計(jì)。第32頁(yè)簡(jiǎn)單SQL 查詢語(yǔ)句 Select SELECT 字段1,字段2,| * FROM 表名 WHERE 條件表示式第33頁(yè)結(jié)構(gòu)化查詢語(yǔ)言SQLSQL語(yǔ)言是一個(gè)關(guān)系數(shù)據(jù)庫(kù)語(yǔ)言，是結(jié)構(gòu)化查詢語(yǔ)言（Structured Query Language）縮寫(xiě)，它包含查詢定義操縱控制SQL使用方法有兩種用戶交互方式聯(lián)機(jī)使用，稱為交互式SQL；另一個(gè)是作為子語(yǔ)言嵌入到其它程序設(shè)計(jì)語(yǔ)言中使用，稱為嵌入式SQL。第34頁(yè)SQL表示式SQL表示式可分為兩類(lèi)：值表示式邏輯表示式。值表示式包含幾個(gè)：數(shù)值表示式：實(shí)現(xiàn)數(shù)值型數(shù)據(jù)算術(shù)運(yùn)算；字串值表示式

13、完成了字串操作；日期時(shí)間值表示式處理日期時(shí)間數(shù)據(jù)。邏輯運(yùn)算包含與（AND）、或（OR）、否（NOT）。SQL還提供了比較謂詞（=、!=、=）、范圍謂詞（IN、LIKE、IS）和存在謂詞（EXIST）。第35頁(yè)函數(shù)SQL內(nèi)置函數(shù)有三類(lèi)：統(tǒng)計(jì)函數(shù):COUNT、SUM、AVG、MAX和MIN字串處理函數(shù) len, substring(studentName,1,2)大小寫(xiě)轉(zhuǎn)換函數(shù)（Upper,Lower）時(shí)間日期處理函數(shù)。 時(shí)間日期處理函數(shù)用于返回當(dāng)前日期與時(shí)間。慣用有三個(gè)：getDate (返回當(dāng)前日期時(shí)間)。Year, Month, Day函數(shù)第36頁(yè)數(shù)據(jù)檢索語(yǔ)句select 語(yǔ)法格式為： SE

14、LECT ALL|DISTINCT 目標(biāo)列FROM 基本表 （或視圖）as 表別名WHERE 條件表示式GROUP BY 列名 HAVING 條件表示式ORDER BY 列名 ASC/DESC說(shuō)明 DISTINCT為去掉查詢結(jié)果中重復(fù)行，默認(rèn)為ALL，既列出全部統(tǒng)計(jì)；目標(biāo)列格式能夠?qū)憺椋毫忻? as 別名1,列名n as 別名n，省略別名，返回結(jié)果集中以列名標(biāo)識(shí)，不然以別名標(biāo)識(shí)，假如目標(biāo)列用*表示，則返回表中全部列；From子句指明了從那些表或視圖中得到數(shù)據(jù)，全部列名都必須存在于From后表中,或出現(xiàn)于left join, inner join, full outer join之后表，假如列名

15、存在于From后多個(gè)表中，則必須標(biāo)明該列起源于哪個(gè)表，格式為 表名.列名；目標(biāo)列能夠是SQL中庫(kù)函數(shù)或常數(shù)表示式。如Sum、Count、Avg等函數(shù)。第37頁(yè)Select 語(yǔ)句Where 子句有雙重作用，一是返回需要統(tǒng)計(jì)，另一個(gè)是建立多表之間內(nèi)連接聯(lián)合查詢。 GROUP BY 用來(lái)對(duì)查詢結(jié)果進(jìn)行分組，把某一列值相同統(tǒng)計(jì)分成一組，與統(tǒng)計(jì)函數(shù)如Sum、Count等聯(lián)合使用；在有Group子句時(shí)，能夠深入使用HAVING子句對(duì)分組后記過(guò)進(jìn)行過(guò)濾顯示，是分組提取條件；ORDER BY 是查詢結(jié)果排序方式，后面跟ASC為升序，DESC為降序,默認(rèn)為升序。第38頁(yè)關(guān)系實(shí)例studentNonamebirt

16、hdaysexcourseNocourseNamestudentNocourseNoscore030318古待1986-1-2女S01高數(shù)一020301F0998020301張名1989-1-1男L02數(shù)值算法010101L0278010201張簡(jiǎn)1981-2-2女J04操作系統(tǒng)010101J0489030501許昌1987-9-8男F09德語(yǔ)010201J0179030502劉放1988-1-9男F01日語(yǔ)010101J0491010101李燦1981-9-2男J01數(shù)據(jù)庫(kù)010101J0199010503顧爭(zhēng)1988-2-7男02郭潔1988-1-5女學(xué)生表 tblStudent 課程表t

17、blCourse 學(xué)生修課表 tblSelectCourse第39頁(yè)SQL例例1：查看學(xué)生表中全部數(shù)據(jù)，即列出全部學(xué)生Select * from tblStudent這里 * 代表表中全部列，上述語(yǔ)句等價(jià)以下語(yǔ)句：Select studentNo, studentName, birthday, sex From tblStudent這時(shí)，表中有多少條統(tǒng)計(jì)，數(shù)據(jù)庫(kù)系統(tǒng)就列出多少條統(tǒng)計(jì)。第40頁(yè)Where子句子例2：查詢學(xué)生表tblStudent中全部女生學(xué)號(hào)、姓名和年紀(jì)Select studentNo, studentName, birthdayfrom tblStudent where sex

18、=女例3： 查詢學(xué)生修課表中成績(jī)大于80分學(xué)生：Select * from tblSelectCourse where score80在where條件中，能夠使用邏輯運(yùn)算符和比較關(guān)系符，包含：=、!=、=。也能夠使用關(guān)鍵字，如：between、like、in等。注意，假如值是字符串或者字符、日期類(lèi)型，就要用單引號(hào)把它括起來(lái)，比如例2中where條件。例4 查詢操作系統(tǒng)成績(jī)?cè)?0到80之間學(xué)生學(xué)號(hào)select studentNo from tblSelectCourse where score between 70 and 80 and courseNo=J003第41頁(yè)日期型數(shù)據(jù)處理羅列出每個(gè)

19、同學(xué)年紀(jì)現(xiàn)在有了生日，當(dāng)前日期能夠用getDate()函數(shù)取得，日期型字段年份能夠用Year函數(shù)取得Select year(getDate()- year(birthday)From tblStudent第42頁(yè)order by 子句 例5：查詢02級(jí)學(xué)生選課信息，按學(xué)號(hào)從小到大排列。因?yàn)閷W(xué)號(hào)頭兩位代表學(xué)生年級(jí)，所以能夠這么完成SQL語(yǔ)句編寫(xiě)：Select studentNo,courseNofrom tblSelectCourse where studentNo like 02% order by studentNo這里%代表任意個(gè)字符。第43頁(yè)group by 和Having子句 例6：求

20、每個(gè)學(xué)生課程總分(大于260分同學(xué)羅列)Select sc.studentNo, studentName, sum(score) as totfrom tblSelectCourse sc, tblStudent s where s.studentNo=sc.studentNo group by sc.studentNo, studentName having sum(score) 260改為 having tot 260 對(duì)不對(duì)？例7:列出選修課程大于等于三門(mén)課程學(xué)生，并按學(xué)號(hào)排序select s.studentName,sc.studentNo, count(*) as courses f

21、rom tblStudent s, tblSelectCourse sc where sc.studentNo=s.studentNo group by sc.studentNo, s.studentName having count(*)=3 order by sc.studentNo第44頁(yè)內(nèi)連接查詢 例8 查找軟件開(kāi)發(fā)課程成績(jī)?cè)?0分以上學(xué)生姓名、學(xué)號(hào)、年紀(jì)以及詳細(xì)成績(jī)。分析：直接觀察表tblSelectCourse和tblStudent，能夠發(fā)覺(jué)，經(jīng)過(guò)tblSelectCourse能夠得到學(xué)生學(xué)號(hào)，但無(wú)法得到學(xué)生名字和年紀(jì)，但缺乏信息能夠經(jīng)過(guò)表tblStudent得到，表tblStud

22、ent和tblSelectCoursestudentNo假如統(tǒng)一起來(lái)，就能夠唯一得到所需要信息Select s.studentNo, studentName, year(getDate()-year(birthday), score from tblStudent s, tblSelectCourse sc where s.studentNo=sc.studentNo and courseNo=J001 and score80第45頁(yè)例8另外寫(xiě)法Select s.studentNo, studentName, birthday, score from tblStudent sinner joi

23、n tblSelectCourse sc on s.studentNo=sc.studentNo where courseNo=J001 and score80第46頁(yè)嵌套查詢 例9：找出成績(jī)(不分課程)大于80分學(xué)生姓名Select studentName from tblStudent where studentNo in (select studentNo from tblSelectCourse where score80)這里，select studentNo from sc where score80返回一個(gè)集合，第二個(gè)查詢使用In關(guān)鍵字，判定表S中studentNo是否在返回結(jié)果

24、集中。第47頁(yè)左連接查詢問(wèn)題：班主任查詢班級(jí)學(xué)生某門(mén)課成績(jī)表，假如一個(gè)學(xué)生沒(méi)選該課程，用內(nèi)連接查詢能得到什么結(jié)果？studentNonamebirthdaysexcourseNocourseNamestudentNocourseNoscore030318古待1986-1-2女S01高數(shù)一020301F0998020301張名1989-1-1男L02數(shù)值算法010101L0278010201張簡(jiǎn)1981-2-2女J04操作系統(tǒng)010101J0489030501許昌1987-9-8男F09德語(yǔ)010201J0179030502劉放1988-1-9男F01日語(yǔ)010101J0491010101李燦1

25、981-9-2男J01數(shù)據(jù)庫(kù)010101J0199010503顧爭(zhēng)1988-2-7男02郭潔1988-1-5女學(xué)生表 tblStudent 課程表tblCourse 學(xué)生修課表 tblSelectCourse第48頁(yè)左連接 查詢 Left joinSelect studentName, a.studentNo, b.courseNo, courseNameFrom tblStudent aLeft join tblSelectCourse b on (a.studentNo = b.studentNoAnd b.courseNo=F001)Left join tblCourse c on b.courseNo=c.courseNo第49頁(yè)什么時(shí)候用左連接左表在右表中對(duì)應(yīng)統(tǒng)計(jì)可能有，也可能沒(méi)有情況下， 第50頁(yè)SQL數(shù)據(jù)更新語(yǔ)句 語(yǔ)法數(shù)據(jù)更新語(yǔ)句 UpdateSQL數(shù)據(jù)更新語(yǔ)句Update修改表中原有數(shù)據(jù)統(tǒng)計(jì)與Select 語(yǔ)句不一樣，Update語(yǔ)句不存在多表聯(lián)合操作，每個(gè)語(yǔ)句一次只能針對(duì)一個(gè)表。Update語(yǔ)法格式為：update 表 set 字段名 = 表示式, 字段名=表示式,. where條件第51頁(yè)Update 例子例 11：將全部學(xué)生年紀(jì)加1歲update tblStudent set age = age+1 例12：將學(xué)號(hào)為011101學(xué)生J01課程成績(jī)加