計算思維導(dǎo)論（第2版）課件：數(shù)據(jù)庫技術(shù)基礎(chǔ)

數(shù)據(jù)庫技術(shù)基礎(chǔ)學(xué)生選課系統(tǒng)成績管理系統(tǒng)學(xué)籍管理系統(tǒng)圖書管理系統(tǒng)安全門禁系統(tǒng)火車訂票系統(tǒng)6.1數(shù)據(jù)管理技術(shù)的發(fā)展數(shù)據(jù)庫管理三個階段人工管理階段1）數(shù)據(jù)不保存。2）沒有文件的概念。數(shù)據(jù)由每個程序的程序員自行組織和安排。3）一組數(shù)據(jù)對應(yīng)一個程序?？赡軐?dǎo)致大量的數(shù)據(jù)重復(fù)。4）沒有形成完整的數(shù)據(jù)管理的概念，更沒有對數(shù)據(jù)進行管理的軟件系統(tǒng)。程序和數(shù)據(jù)不具有獨立性。文件系統(tǒng)階段1）數(shù)據(jù)可以長期保存在外存儲器（如磁盤）上，可以由多個程序反復(fù)使用。2）數(shù)據(jù)的物理結(jié)構(gòu)和邏輯結(jié)構(gòu)有了一定的區(qū)別，但較簡單。3）程序和數(shù)據(jù)之間有了一定的獨立性。4）出現(xiàn)了多種文件存儲形式，但文件之間是獨立的，它們之間的聯(lián)系要通過程序去構(gòu)造，文件的共享性還比較差。2數(shù)據(jù)庫系統(tǒng)階段1）數(shù)據(jù)庫能夠根據(jù)不同的需要、不同的方法組織數(shù)據(jù)，最大限度地提高用戶或應(yīng)用程序訪問數(shù)據(jù)的效率。2）數(shù)據(jù)庫不僅能夠保存數(shù)據(jù)本身，還能保存數(shù)據(jù)之間的相互聯(lián)系，保證了對數(shù)據(jù)修改的一致性。3）在數(shù)據(jù)庫中，相同的數(shù)據(jù)可以共享。4）數(shù)據(jù)具有較高的獨立性。5）提供了一整套的安全機制來保證數(shù)據(jù)的安全、可靠。6）可以給數(shù)據(jù)庫中的數(shù)據(jù)定義一些約束條件來保證數(shù)據(jù)的正確性（也稱完整性）。36.2數(shù)據(jù)庫系統(tǒng)的設(shè)計方法基本概念長期保存在計算機外存上的、有結(jié)構(gòu)的、可共享的數(shù)據(jù)集合DBSDBDBADBMS是位于用戶和操作系統(tǒng)之間的軟件。實現(xiàn)對數(shù)據(jù)的存儲和管理。由DB、DBMS、應(yīng)用系統(tǒng)、DBA、用戶等構(gòu)成數(shù)據(jù)庫管理員用戶DBADBMSOSDB數(shù)據(jù)庫系統(tǒng)的組成概念模型需求分析32145設(shè)計圖紙建筑沙盤建筑備料項目施工項目驗收6030201060405設(shè)計過程需求分析用戶需求（需求分析報告——合同）概念設(shè)計概念模型（E-R模型——建筑圖紙）邏輯設(shè)計數(shù)據(jù)模型（關(guān)系模型——建筑沙盤）物理設(shè)計DBMS（數(shù)據(jù)存儲和管理——建筑備料）具體實施建立DB、寫代碼——項目施工運行維護運行、維護——項目驗收模型模型是現(xiàn)實世界特征的模擬和抽象。應(yīng)滿足三方面的要求，一是比較真實地模擬現(xiàn)實世界；二是容易為人所理解。三是便于在計算機上實現(xiàn)。數(shù)據(jù)庫設(shè)計的兩種重要模型概念模型——建筑圖紙按用戶的觀點對數(shù)據(jù)和信息進行建模，獨立于具體的機器和DBMS數(shù)據(jù)模型——建筑沙盤它是按算機系統(tǒng)的觀點對數(shù)據(jù)建模，主要用于DBMS的實現(xiàn)，數(shù)據(jù)模型是數(shù)據(jù)庫系統(tǒng)的核心和基礎(chǔ)。跟具體的機器和DBMS有關(guān)?，F(xiàn)實世界信息世界機器世界抽象轉(zhuǎn)換概念模型不依賴于具體機器和DBMS數(shù)據(jù)模型依賴于具體機器和DBMS321現(xiàn)實世界中客觀存在并可以相互區(qū)分的事物。如學(xué)生、課程、零件等都是實體。實體______45同一類型的實體的集合稱為實體集。如全體學(xué)生、所有課程、所有零件等實體集：用矩形表示____________________實體的特征。一種實體可以由若干個屬性來刻畫。如，學(xué)號、姓名、性別、年齡等屬性：用橢圓表示____________________用于唯一標(biāo)識一個實體。例如，學(xué)生實體可以用學(xué)號來唯一標(biāo)識碼______實體集之間的對應(yīng)關(guān)系（一對一、一對多、多對多）聯(lián)系：用菱形表示__________________概念模型：是對信息世界的建模，有多種表示方法，其中，最常用的是“實體-聯(lián)系模型”，簡稱E-R模型。E-R模型用E-R圖來描述現(xiàn)實世界的概念模型。在E-R圖中，實體用矩形表示，屬性用橢圓形表示，并用無向邊將其與實體連接起來。學(xué)生選修課程mn成績課程號課程名學(xué)分學(xué)號姓名…成績管理系統(tǒng)用一組二維表表示實體及實體間的聯(lián)系用網(wǎng)狀結(jié)構(gòu)表示實體及實體間的聯(lián)系數(shù)據(jù)模型層次模型網(wǎng)狀模型關(guān)系模型123用樹型結(jié)構(gòu)表示實體及實體間的聯(lián)系√關(guān)系一個關(guān)系對應(yīng)于一張二維表關(guān)系名關(guān)系名稱，即表名字段表中的一列，即屬性字段名字段名稱，即屬性名域字段的取值范圍記錄表中的一行，即元組關(guān)系模式候選碼主碼外碼（非）主屬性關(guān)系的描述。表名(字段1，字段2，…，字段n)候選關(guān)鍵字，能唯一識別一條記錄（元組）?；蚍Q主鍵，多個候選碼中被選中那個或稱外鍵，非本表主碼，而是另外表主碼主屬性：候選碼和主碼非主屬性：不是候選碼和主碼的屬性業(yè)主飼養(yǎng)寵物11概念模型舉例：一對一E-R圖關(guān)系數(shù)據(jù)模型：業(yè)主（人員編號，姓名，地址，寵物編號）人員編號姓名寵物編號寵物名地址健康證寵物（寵物編號，寵物名，健康證）為銀行設(shè)計一個數(shù)據(jù)庫，包括顧客和帳戶的信息。顧客信息包括姓名，地址，電話，社會保險號。帳戶包括編號，類型（例如存款，支票），和金額。畫出一對多E-R模型。顧客—帳戶顧客姓名地址電話保險號1m編號類型金額帳戶帳戶編號類型金額保險號保險號姓名地址電話顧客帳戶為銀行設(shè)計一個數(shù)據(jù)庫，包括顧客和帳戶的信息。顧客信息包括姓名，地址，電話，社會保險號。帳戶包括編號，類型（例如存款，支票），和金額。畫出E-R模型。學(xué)生(學(xué)號,姓名,性別,...)，學(xué)號為主鍵。課程(課程號,課程名稱,...)，課程號為主鍵。選修(學(xué)號,課程號,成績)，學(xué)號和課程號為聯(lián)合主鍵。E-R模型矩形、橢圓、菱形、三個聯(lián)系轉(zhuǎn)換原則一個實體轉(zhuǎn)換為一張表一對一、一對多、多對多兩個重要模型概念模型（圖紙）數(shù)據(jù)模型（沙盤）關(guān)系模型關(guān)系、屬性、記錄、關(guān)系模式、主鍵、外鍵規(guī)范化設(shè)計重要范式34562設(shè)計過程需求分析、概念設(shè)計、邏輯設(shè)計、物理設(shè)計、具體實施、運行維護1規(guī)范化設(shè)計256134關(guān)系中的每一個屬性必須是不可再分的基本數(shù)據(jù)項每一列中的數(shù)據(jù)項具有相同的數(shù)據(jù)類型每一列的名稱在一個關(guān)系中是唯一的列次序可以是任意的關(guān)系中任意兩行不能相同行次序可以是任意的（1）數(shù)據(jù)冗余（1）數(shù)據(jù)冗余（2）插入異常（3）刪除異常學(xué)號姓名性別課程號課程名學(xué)分成績101張三男001思維導(dǎo)論1.598101張三男002數(shù)據(jù)庫280102李四女001思維導(dǎo)論1.592存在問題：（4）其他…（2）插入異常（3）刪除異常（4）維護困難有太多相互之間聯(lián)系的屬性保存在同一關(guān)系中，這就造成了因一種信息被捆綁在其它信息上而產(chǎn)生的信息之間的相互依附存儲的問題——數(shù)據(jù)依賴出現(xiàn)問題的原因：學(xué)號姓名性別課程號課程名學(xué)分成績101張三男001思維導(dǎo)論1.598101張三男002數(shù)據(jù)庫280102李四女001思維導(dǎo)論1.592學(xué)號姓名性別101張三男101張三男102李四女課程號課程名學(xué)分001思維導(dǎo)論1.5002數(shù)據(jù)庫2001思維導(dǎo)論1.5學(xué)號課程號成績101001981010028010200192學(xué)生表課程表選修表數(shù)據(jù)庫的規(guī)范化設(shè)計A、一定要嚴(yán)格恪守規(guī)范化原則B、差不多就得，適可而止就行投票1NF屬性不可分學(xué)號姓名省市…郵編1張三遼寧鐵嶺…1234562李四北京北京…9876541簡單拆分法優(yōu)點：簡單缺點：不統(tǒng)一、檢索困難學(xué)號姓名通訊地址省市…郵編1張三遼寧鐵嶺…1234562李四北京北京…987654學(xué)號姓名通訊地址1張三遼寧鐵嶺…1234562李四北京…9876541NF-屬性不可分簡單拆分法：將嵌套表摘出2外鍵法學(xué)號姓名1張三2李四地址編號省市…郵編學(xué)號1遼寧鐵嶺…12345612北京北京…9876542優(yōu)點：不丟失信息缺點：右側(cè)表中，外鍵學(xué)號非必須，如缺省、則看不出隸屬關(guān)系學(xué)號姓名通訊地址省市…郵編1張三遼寧鐵嶺…1234562李四北京北京…987654外鍵法：產(chǎn)生新表后，將原表主鍵作為新表外鍵3弱實體法學(xué)號姓名1張三2李四學(xué)號地址編號省市…郵編11遼寧鐵嶺…12345622北京北京…987654優(yōu)點：不丟失信息缺點：右側(cè)表中，學(xué)號非必須，看不出隸屬關(guān)系學(xué)號姓名通訊地址省市…郵編1張三遼寧鐵嶺…1234562李四北京北京…987654弱實體法：一個實體對于另一個實體而言，具有很強的依賴關(guān)系，利用聯(lián)合主鍵表示這種依賴1NF2NF屬性不可分非主屬性不能部分依賴于主碼而應(yīng)完全依賴于主鍵學(xué)號課程號成績課程名稱學(xué)分1198計算思維31285數(shù)據(jù)庫42180計算思維32275數(shù)據(jù)庫4主鍵：學(xué)號+課程號聯(lián)合主鍵∵學(xué)號+課程號

成績、課程名稱、學(xué)分∵課程號

課程名稱、學(xué)分∴

課程名稱、學(xué)分依賴于主鍵中的一部分學(xué)號課程號成績1198128521802275課程號課程名稱學(xué)分1計算思維32數(shù)據(jù)庫4+賴依數(shù)函Y=AX+B←完全依賴非主屬性不能部分依賴于主碼1NF2NF3NF屬性不可分非主屬性不能部分依賴于主碼非主屬性不能傳遞依賴于主碼學(xué)號姓名系別辦公室地點1張三給排水學(xué)C2李四計算機學(xué)D主鍵：學(xué)號∵學(xué)號

系別，系別

辦公室地點∴學(xué)號

辦公室地點（傳遞依賴）學(xué)號姓名系別1張三給排水2李四計算機系別辦公室地點給排水學(xué)C計算機學(xué)D+↖非傳遞依賴圖非主屬性不能傳遞依賴于主碼沒有對錯之分，只有好壞之分。實踐中，嚴(yán)格恪守設(shè)計范式——不推薦實踐中，完全不考慮設(shè)計范式——更不推薦關(guān)于范式的思考范式滿足不同程度的標(biāo)準(zhǔn)要求稱為范式2NF消除非主屬性對主鍵的部分依賴關(guān)系的性質(zhì)數(shù)據(jù)項不可分、同列的數(shù)據(jù)類型一致、列名是唯一的、任意兩行記錄不相同、行列次序可以是任意的1NF數(shù)據(jù)項不可分，不能有表中有表3NF消除非主屬性對主鍵的傳遞依賴34562為什么要規(guī)范化設(shè)計數(shù)據(jù)冗余、插入異常、刪除異常、維護困難1關(guān)系代數(shù)傳統(tǒng)專門傳統(tǒng)的關(guān)系代數(shù)SR職工號姓名101張三102李四職工號姓名101張三103王五職工號姓名101張三102李四103王五R∪S兩個相同結(jié)構(gòu)關(guān)系的并是由屬于R或?qū)儆赟的記錄組成的集合并RSRS兩個相同結(jié)構(gòu)關(guān)系的交是由屬于R且屬于S的記錄組成的集合R職工號姓名101張三102李四S職工號姓名101張三103王五R∩S職工號姓名101張三交SRRS兩個相同結(jié)構(gòu)關(guān)系的差是由屬于R但不屬于S的元素組成的集合差SRRR1?R2職工號姓名102李四R1職工號姓名101張三102李四R2職工號姓名101張三103王五笛卡爾積：設(shè)R，S為集合，用R中元素為第一元素，S中元素為第二元素構(gòu)成有

序?qū)?，所有這樣的有序?qū)M成的集合叫做R與S的笛卡爾積，記作R×S。R職工號姓名101張三102李四職工號家屬號姓名1011張?zhí)?012張小三1021李太太1022李小四SRⅹS職工號姓名職工號家屬號姓名101張三1011張?zhí)?01張三1012張小三101張三1021李太太101張三1022李小四102李四1011張?zhí)?02李四1012張小三102李四1021李太太102李四1022李小四專門的關(guān)系代數(shù)選擇運算：是從關(guān)系中查找符合指定條件的記錄。

學(xué)生學(xué)號姓名性別年齡所在系別95001陳一男20計算機95002黃二女19會計95003張三女18計算機95004李四男19傳媒學(xué)號姓名性別年齡所在系別95002黃二女19會計95003張三女18計算機95004李四男19傳媒

查詢：年齡小于20的學(xué)生投影運算：是從關(guān)系中選取若干個屬性形成一個新的關(guān)系

學(xué)生學(xué)號姓名性別年齡所在系別95001陳一男20計算機95002黃二女19會計95003張三女18計算機95004李四男19傳媒所在系別計算機會計傳媒查詢：學(xué)生表都有哪些系別？連接運算：將兩個關(guān)系的若干屬性拼接成一個新關(guān)系。學(xué)號姓名性別95001陳一男95002黃二女學(xué)號課名成績95001數(shù)學(xué)8995001語文7095002數(shù)學(xué)9095002語文85Student.學(xué)號姓名性別Score.學(xué)號課名成績95001陳一男95001數(shù)學(xué)8995001陳一男95001語文7095002黃二女95002數(shù)學(xué)9095002黃二女95002語文85

學(xué)生學(xué)生+選修

選修學(xué)生

選修學(xué)生.學(xué)號=選修.學(xué)號查詢：選修了課程的學(xué)生信息傳統(tǒng)的關(guān)系代數(shù)交集并集差集笛卡爾積專門的關(guān)系代數(shù)選擇投影連接關(guān)系的完整性約束域完整性（用戶定義的完整性）用戶自定義的屬性的自身約束實體完整性實體完整性要求每個關(guān)系都必須有主碼，而主碼中的所有屬性即主屬性不能為空值。參照完整性外鍵參照主鍵存在。若屬性F是基本關(guān)系R的外碼，它與基本關(guān)系S的主碼相對應(yīng)，則對于R中每個元組在F上的值必須為：或者取空值（F的每個屬性值均為空值）；或者等于S中某個元組的主碼值。完整性約束6.3MySQL數(shù)據(jù)庫管理系統(tǒng)MySQL與NavicatMySQL是一個關(guān)系型數(shù)據(jù)庫管理系統(tǒng)。Navicat是一套快速、可靠的數(shù)據(jù)庫管理工具。所以Navicat和MySQL是一個管理和被管理的關(guān)系。MySQL數(shù)據(jù)庫默認(rèn)4個數(shù)據(jù)庫新建MySQL連接MySQL數(shù)據(jù)庫新建連接數(shù)據(jù)庫中對象MySQL數(shù)據(jù)庫的建立與維護排序規(guī)則中常見的后綴有三種，ci表示大小寫不敏感，cs表示區(qū)分大小寫，bin是以二進制數(shù)據(jù)存儲，且區(qū)分大小寫。utf8是針對Unicode的一種可變長度字符編碼新建數(shù)據(jù)庫建表舉例-表結(jié)構(gòu)學(xué)號姓名性別出生日期入學(xué)成績101張三男1998-4-5595102李四女1998-5-10690103王五男1997-6-15620課程號課程名稱學(xué)分001計算思維導(dǎo)論1.5002數(shù)據(jù)庫技術(shù)與應(yīng)用2學(xué)號課程號成績1010019810100280102001921020028910300170學(xué)生選修課程mn成績課程號課程名稱學(xué)分學(xué)號姓名…建表舉例-設(shè)計表建表舉例-enum類型建表舉例-聯(lián)合主鍵建表舉例-輸入數(shù)據(jù)直接輸入導(dǎo)入外部數(shù)據(jù)（支持的數(shù)據(jù)類型如右圖所示）Navicat創(chuàng)建查詢查詢創(chuàng)建工具查詢創(chuàng)建工具SQL查詢語句QSLSQL(StructuredQueryLanguage)語言：結(jié)構(gòu)化查詢語言，是操作關(guān)系數(shù)據(jù)庫的工業(yè)標(biāo)準(zhǔn)語言。該語言是既可以單獨執(zhí)行，直接操作數(shù)據(jù)庫，也可以嵌入到其他語言中執(zhí)行數(shù)據(jù)定義語言——————包含用來定義和管理數(shù)據(jù)庫以及庫對象的語句。數(shù)據(jù)操縱語言——————包含了用來查詢、添加、修改和刪除數(shù)據(jù)庫中數(shù)據(jù)的語句數(shù)據(jù)控制語言——————包含了用來設(shè)置或更改數(shù)據(jù)庫用戶或角色權(quán)限等的語句123數(shù)據(jù)操縱語言數(shù)據(jù)操縱語言的語句格式SELECT字段列表FROM表名稱列表[WHERE條件][GROUPBY分組字段名][HAVING條件表達(dá)式][ORDERBY排序字段[ASC|DESC]]SQL語句關(guān)系操作操作描述SELECT子句投影選擇滿足條件的列FROM子句連接單表或多表等值連接[WHERE子句]選擇選擇滿足條件的行[GROUPBY]分組按指定條件分組匯總[HAVING

]篩選按指定條件對分組數(shù)據(jù)進行篩選[ORDERBY]排序按指定條件排序數(shù)據(jù)操縱語言的語句格式例1：查詢“學(xué)生”表的所有學(xué)生的所有信息

SELECT*FROM學(xué)生例2：查詢“學(xué)生”表的前3條條記錄SELECT*FROMstudentORDERBY學(xué)號LIMIT3;例3：從“學(xué)生”表查詢一共有哪些班級。SELECTDISTINCT班級FROMstudent;SELECT例4：查詢“學(xué)生”表中的所有男生信息

SELECT*FROM學(xué)生

WHERE性別="男"SELECT例5：查詢“學(xué)生”表中所有姓趙的學(xué)生記錄。 SELECT* FROMstudent WHERE姓名LIKE'趙%';例6：查詢2004年12月1日以后出生的學(xué)生姓名和出生日期。SELECT* FROMstudent WHERE出生日期>'2004-12-01';例7：查詢“學(xué)生”表的所有非團員的男生信息。

SELECT* FROMstudent WHERENOT是否團員AND性別='男'SELECT例8：查詢成績在為良（80～89分之間）的學(xué)生的學(xué)號和成績。 SELECT學(xué)號,成績 FROMsc WHERE成績>=80and成績<=89;SELECT或：WHERE成績BETWEEN80AND89；例9：查詢非建筑學(xué)院的學(xué)生的姓名和性別。 SELECT姓名,性別 FROMstudent WHERE班級NOTLIKE'建%';例10：查詢“學(xué)生”表的每個人的姓名和出生日期，并按出生日期從前到后排序。

SELECT姓名,出生日期FROMstudentORDERBY出生日期ASC;ASC：按升序排序DESC：按降序排序SELECT例11：查詢?nèi)w學(xué)生的姓名和年齡。SELECT姓名,YEAR(CURRENT_DATE())-YEAR(出生日期)AS年齡FROMstudent;SELECT例12：查詢選修了3門課程以上的學(xué)生的學(xué)號和平均成績（平均成績保留1位小數(shù)）。SELECT學(xué)號,ROUND(AVG(成績),1)AS平均成績FROMscGROUPBY學(xué)號HAVINGCOUNT(*)>=3;

group

by

就是依據(jù)by

后面的規(guī)則對數(shù)據(jù)分組,所謂的分組就是將數(shù)據(jù)集劃分成若干個小組,針對若干個小組做處理。where子句和having子句的區(qū)別：where不能放在groupby后面having是跟groupby連在一起用的，放在groupby后面，此時的作用相當(dāng)于wherewhere后面的條件中不能有聚集函數(shù)，比如SUM(),AVG()等，而having可以。例13：查詢選修了“程序設(shè)計語言”課程的學(xué)生的學(xué)號、課程號、成績。SELECTSELECTsc.學(xué)號,course.課程號,sc.成績FROMcourse,scWHEREcourse.課程號=sc.課程號ANDcourse.課程名='程序設(shè)計語言';多表查詢或者，例12：查詢所有學(xué)生的學(xué)號、姓名、所選課程的名稱及課程成績。

SELECT學(xué)生.學(xué)號,姓名,課程名稱,成績

FROM學(xué)生,課程,選修

WHERE學(xué)生.學(xué)號=選修.學(xué)號AND

選修.課程號=課程.課程號學(xué)生（學(xué)號

,姓名,性別,出生日期,入學(xué)成績）課程（課程號

,課程名稱,學(xué)分）選修（學(xué)號，課程號，成績）增刪改語句INSERT語句用于向表中插入新的記錄格式：

INSERT

INTO

表名[(列名1,列名2,...,列名n)]

VALUES(值1,值2,...,值n)例1：向“課程”表添加一門新的課程，課程號為“009”，課程名稱為“結(jié)構(gòu)力學(xué)”，學(xué)分為3。INSE