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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

20、每個學(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 對不對？例7:列出選修課程大于等于三門課程學(xué)生，并按學(xué)號排序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頁內(nèi)連接查詢 例8 查找軟件開發(fā)課程成績在80分以上學(xué)生姓名、學(xué)號、年紀以及詳細成績。分析：直接觀察表tblSelectCourse和tblStudent，能夠發(fā)覺，經(jīng)過tblSelectCourse能夠得到學(xué)生學(xué)號，但無法得到學(xué)生名字和年紀，但缺乏信息能夠經(jīng)過表tblStudent得到，表tblStud

22、ent和tblSelectCoursestudentNo假如統(tǒng)一起來，就能夠唯一得到所需要信息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頁例8另外寫法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頁嵌套查詢 例9：找出成績(不分課程)大于80分學(xué)生姓名Select studentName from tblStudent where studentNo in (select studentNo from tblSelectCourse where score80)這里，select studentNo from sc where score80返回一個集合，第二個查詢使用In關(guān)鍵字，判定表S中studentNo是否在返回結(jié)果

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

25、981-9-2男J01數(shù)據(jù)庫010101J0199010503顧爭1988-2-7男02郭潔1988-1-5女學(xué)生表 tblStudent 課程表tblCourse 學(xué)生修課表 tblSelectCourse第48頁左連接 查詢 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īng)統(tǒng)計可能有，也可能沒有情況下， 第50頁SQL數(shù)據(jù)更新語句 語法數(shù)據(jù)更新語句 UpdateSQL數(shù)據(jù)更新語句Update修改表中原有數(shù)據(jù)統(tǒng)計與Select 語句不一樣，Update語句不存在多表聯(lián)合操作，每個語句一次只能針對一個表。Update語法格式為：update 表 set 字段名 = 表示式, 字段名=表示式,. where條件第51頁Update 例子例 11：將全部學(xué)生年紀加1歲update tblStudent set age = age+1 例12：將學(xué)號為011101學(xué)生J01課程成績加