MySQL數(shù)據庫基礎與實踐 第2版 課件 CH01 數(shù)據庫設計基礎

MySQL數(shù)據庫開發(fā)與應用1數(shù)據庫設計基礎第章本章內容1.1

數(shù)據庫設計概述1.2

關系模型1.3E-R圖1.4MySQL存儲引擎1.5

本章總結1.6實踐與應用1.1數(shù)據庫設計概述數(shù)據庫（DstabaseDB）是“按照某種數(shù)據結構對數(shù)據進行組織、存儲和管理的容器”，簡單的說就是用來存儲、管理數(shù)據的容器。數(shù)據庫系統(tǒng)（DstabaseSystem，DBS）是指在計算機中引入數(shù)據庫后的系統(tǒng)，一般由數(shù)據庫、數(shù)據庫管理系統(tǒng)、應用程序、數(shù)據庫管理員組成。1.1數(shù)據庫設計概述數(shù)據庫管理系統(tǒng)（DstabaseManagementSystem，DBMS）是一個管理、控制數(shù)據庫容器中各種數(shù)據庫對象的系統(tǒng)軟件。

數(shù)據庫管理系統(tǒng)按照一定的數(shù)據模型組織數(shù)據，常用的模型包括“層次模型”、“網狀模型”、“關系模型”，以及“面向對象模型”等，基于“關系模型”的數(shù)據庫管理系統(tǒng)稱為關系數(shù)據庫管理系統(tǒng)（RelationalDatabaseManagementSystem，RDBMS）。

1.1數(shù)據庫設計概述關系數(shù)據庫概念是由E.F.Codd博士于1976年發(fā)表的《關于大型共享數(shù)據庫數(shù)據的關系模型》論文中提出，論文中闡述了關系數(shù)據庫模型及其原理，并將其用于數(shù)據庫系統(tǒng)。使用關系模型對數(shù)據進行組織、存儲和管理的數(shù)據庫稱為關系數(shù)據庫，關系數(shù)據庫系統(tǒng)是支持關系數(shù)據模型的數(shù)據庫系統(tǒng)。在關系數(shù)據庫中所謂的“關系”，實際上是一張二維表，表是邏輯結構而不是物理結構，系統(tǒng)在物理層可以使用任何有效的存儲結構來存儲數(shù)據1.1.2結構化查詢語言SQL結構化查詢語言（StructuredQueryLanage，SQL）是一種專門用來與數(shù)據庫通信的語言，其利用一些簡單的句子構成基本的語法來存取數(shù)據庫中的內容，便于用戶從數(shù)據庫中獲得及操作所需數(shù)據。SQL語言具有以下特點：（1）SQL語言是非過程化語言。（2）SQL語言是統(tǒng)一的語言。（3）SQL語言是關系數(shù)據庫的公共語言。1.1.2結構化查詢語言SQLSQL語言由四部分組成：（1）數(shù)據定義語言DDL（DataDefinitionLanguage,DDL）。（2）數(shù)據操縱語言DML(DataManipulationLauaguage，DML)。（3）數(shù)據控制語言DCL(DataControlLauguage，DCL)。（4）嵌入式SQL語言的使用規(guī)定。1.1.2結構化查詢語言SQL根據SQL語言的四部分我們得到SQL的數(shù)據定義、數(shù)據查詢、數(shù)據操縱，以及數(shù)據控制的四個基本功能，表1-1列出了實現(xiàn)其功能動詞。表1-1SQL功能及包含的主要動詞SQL功能動詞數(shù)據定義CREATE、DROP、ALTER數(shù)據查詢SELECT數(shù)據操縱INSERT、UPDATE、DELETE數(shù)據控制GRANT、REVOKE表1-1SQL功能及包含的主要動詞1.1.3數(shù)據庫設計基本步驟（1）需求分析階段需求分析是數(shù)據庫設計的第一步，也是整個設計過程的基礎，本階段的主要任務是對現(xiàn)實世界要處理的對象（公司、部門、企業(yè)）進行詳細調查，在了解現(xiàn)行系統(tǒng)的概況、確定新系統(tǒng)功能的過程中，收集支持系統(tǒng)目標的基礎數(shù)據及其處理方法。1.1.3數(shù)據庫設計基本步驟（2）概念結構設計階段概念結構設計階段是整個數(shù)據庫設計的關鍵。通過對用戶需求進行綜合、歸納與抽象，形成一個獨立于具體DBMS的概念模型。(3)邏輯結構設計階段邏輯結構設計階段將概念結構轉換為某個DBMS所支持的數(shù)據模型，并將其性能進行優(yōu)化。1.1.3數(shù)據庫設計基本步驟（4）數(shù)據庫物理結構設計階段數(shù)據庫物理結構設計階段是利用數(shù)據庫管理系統(tǒng)提供的方法、技術，對已經確定的數(shù)據庫邏輯結構，以較優(yōu)的存儲結構、數(shù)據存取路徑、合理的數(shù)據存儲位置以及存儲分配，設計出一個高效的、可實現(xiàn)的物理數(shù)據庫結構。

(5)

數(shù)據庫實施階段

在數(shù)據庫實施階段運用DBMS提供的數(shù)據語言（例如，SQL）以及宿主語言（例如，C），根據邏輯設計和物理設計的結果建立數(shù)據庫，編制與調試應用程序，組織數(shù)據入庫，并進行試運行。1.1.3數(shù)據庫設計基本步驟（6）數(shù)據庫運行與維護階段

數(shù)據庫應用系統(tǒng)經過試運行后即可投入正式運行，在運行過程中需要不斷對其進行調整、修改與完善。

1.2關系模型

關系模型是目前最重要的也是應用最廣泛的數(shù)據模型。簡言之，關系就是一張二維表，由行和列組成。關系模型將數(shù)據模型組織成表格的形式，這種表格在數(shù)學上稱為關系。

1.2.1數(shù)據庫和表

關系型數(shù)據庫是由多個表和其他數(shù)據庫對象組成的，表是一種最基本的數(shù)據庫對象，由行和列組成，類似電子表格。一個關系數(shù)據庫通常包含多個二維表（稱為數(shù)據庫表或表），從而實現(xiàn)所設計的應用中各類信息的存儲和維護。在關系數(shù)據庫中，如果存在多個表，則表與表之間也會因為字段的關系產生關聯(lián)，關聯(lián)性由主鍵、外鍵所體現(xiàn)的參照關系實現(xiàn)。關系數(shù)據庫不僅包含表，還包含其他數(shù)據庫對象，如，關系圖、試圖、存儲過程和索引等，所以，通常提到關系數(shù)據庫就是指一些相關的表和其他數(shù)據庫對象的集合。

1.2.1數(shù)據庫和表

如表1-2所示的課程表中收集了教師申報課程的相關信息，包括課程名、課程編號、人數(shù)上限、授課教師、課程性質及課程狀態(tài)信息，構成一張二維表。

課程名課程編號人數(shù)上限授課教師課程性質狀態(tài)C語言程序設計1620902060孫老師必修未審核MySQL數(shù)據庫設計1630962090李老師必修未審核物聯(lián)網導論1630949040王老師選修未審核專業(yè)外語1620910170田老師比修未審核1.2.2列和表數(shù)據表中的列也稱為字段，用一個列名也稱為字段名標記。除了字段名行，表中每一行都稱為一條記錄。例如表1-2中共有4個字段、4條記錄。如果想查找“MySQL數(shù)據庫設計”這門課程的授課教師，則可以查找“MySQL數(shù)據庫設計”所在的行與字段“授課教師”所在的列關聯(lián)相交處獲得。初看上去，關系數(shù)據庫中的一個數(shù)據表與一個不存在“合并單元”的Excel相似，但是同一個數(shù)據表的字段名不允許重復，而且為了優(yōu)化存儲空間便于數(shù)據排序，數(shù)據庫表的每一列要求指定數(shù)據類型。1.2.3主鍵與外鍵

關系型數(shù)據庫中的一個表是由行和列組成的，并且要求表中的每行記錄必須唯一。在設計表時，可以通過定義主鍵（primarykey）來保證記錄（實體）的唯一性。

一個表的主鍵由一個或多個字段組成，值具有唯一性，且不允許去控制，主鍵的作用是唯一的標識表中的每一條記錄。1.2.3主鍵與外鍵

例如在表1-3中，可以用“學號”字段作為主鍵，但是不能使用“姓名”字段作為主鍵，因為存在同名現(xiàn)象，無法保證唯一性，有時候表中也有可能沒有一個字段具有唯一性，即沒有任何字段可以作為主鍵，這時候可以考慮使用兩個或兩個以上字段的組合作為主鍵。學號課程編號成績141800701620902098141800711630962095141800831630949087171800861620910190表1-3主鍵外鍵關系1.2.4約束

設計表時，可對表中的一個字段或多個字段的組合設置約束條件，由數(shù)據庫管理系統(tǒng)（如MySQL）自動檢測輸入的數(shù)據是否滿足約束條件，不滿足約束條件的數(shù)據將被數(shù)據庫管理系統(tǒng)拒絕錄入。約束分為表級約束和字段級約束，表級約束是對表中幾個字段的約束，字段級約束是對表中一個字段的約束1.2.4約束幾種常見的約束形式如下：（1）主鍵約束主鍵用來保證表中每條記錄的唯一性，因此在設計數(shù)據庫表時，建議為所有的數(shù)據庫表都定義一個主鍵，用于保證數(shù)據庫表中記錄的唯一性。（2）外鍵約束外鍵約束主要用于定義表與表之間的某種關系。（3）非空約束如果在一個字段中允許不輸入數(shù)據，可以將該字段定義為null，如果在一個字段中必須輸入數(shù)據，則應當將該字段定義為notnull。如果設置某個字段的非空約束，直接在該字段的數(shù)據類型后面加上“notnull”關鍵字即可。1.2.4約束（4）唯一性約束如果一個字段值不允許重復，則應當對該字段添加唯一性（unique）約束。與主鍵約束不同，一張表中可以存在多個唯一性約束，滿足唯一性約束的字段可以取null值。如果設置某個字段為唯一性約束，直接在該字段的數(shù)據類型后面加上“unique”關鍵字即可。（5）默認約束默認值字段用于指定一個字段的默認值，當尚未在該字段中輸入數(shù)據時，該字段中將自動填入這個默認值。（6）檢查約束檢查（check）約束用于檢查字段的輸入值是否滿足指定的條件，在表中輸入或者修改記錄時，如果不符合檢查約束指定的條件，則數(shù)據不能寫入該字段。1.2.4約束（7）自增約束自增（AUTO_INCREMENT）約束是MySQL唯一擴展的完整性約束，當向數(shù)據庫表中插入新記錄時，字段上的值會自動生成唯一的ID。在具體設置自增約束時，一個數(shù)據庫表中只能有一個字段使用該約束，該字段數(shù)據類型必須是整型類型。由于設置自增約束后的字段會生成唯一的ID，所以該字段也經常會被設置為主鍵。MySQL中通過SQL語句的AUTO_INCREMENT來實現(xiàn)。（8）刪除約束在MySQL數(shù)據庫在，一個字段的所有約束都可以用altertable命令刪除。1.3E-R圖

關系數(shù)據庫設計一般要從數(shù)據模型E-R圖（Entity-RelationshipDiagram，E-R圖）設計開始。

E-R圖既可以表示現(xiàn)實世界中的事物，又可以表示事物之間的關系，它描述了軟件系統(tǒng)的數(shù)據存儲需求，其中E表示實體，R表示關系，所以E-R圖由實體、屬性和關系3個要素構成，通過一組與實體、屬性和關系相關的概念可以很好的描述信息世界。1.3.1實體和屬性

（1）實體E-R圖中的實體表示現(xiàn)實世界具有相同屬性描述的事物的集合，不是某一個具體事物，而是一類事物的統(tǒng)稱。E-R圖中的實體通常使用矩形表示.

在設計E-R圖時，一個E-R圖中通常包含多個實體，每個實體由實體名唯一標記。開發(fā)數(shù)據庫時每個實體對應于數(shù)據庫中的一張數(shù)據庫表，每個實體的具體取值對應于數(shù)據庫表中的一條記錄。例如“選課系統(tǒng)”中，“課程”是一個實體，“課程”實體對應于“課程”數(shù)據庫表，而“課程名”為MySQL數(shù)據庫設計，“人數(shù)上限”為90的課程是課程實體的具體取值，對應于“課程”數(shù)據庫表中的一條記錄。1.3.1實體和屬性(2)屬性E-R圖中的屬性通常表示實體的某種特征，也可以使用屬性表示實體間關系的特征。一個實體通常包含多個屬性，每個屬性由屬性名唯一標記，畫在橢圓內,E-R圖中的實體的屬性對應于數(shù)據庫表的字段。圖1-2課程實體及屬性圖1-3學生實體及屬性1.3.2實體與屬性關系

在現(xiàn)實世界任何事物都不是孤立存在的，事物之間或事物內部是有聯(lián)系的。這些聯(lián)系在信息世界反映為實體間的關系和實體內部的關系。實體內部的關系指的是組成實體的各屬性之間存在的聯(lián)系；實體之間的關系指的是不同實體之間的聯(lián)系。

E-R圖中的關系主要是來討論實體間存在的聯(lián)系，在E-R圖中，聯(lián)系用菱形來表示，菱形框內寫明聯(lián)系的名稱，并且用連線將聯(lián)系框與它所關聯(lián)的實體連接起來，并且在連線旁邊標明關系的類型。1.3.2實體與屬性關系 E-R圖中實體間的關系類型一般有三種一對一關系（1：1）、一對多關系（1：n）、多對多關系（m：n）（1） 一對一關系（1：1）對于實體集A中的每一個實體，實體集B中至多有一個（可以沒有）實體與之聯(lián)系，反之亦然，則實體集A與實體集B具有一對一關系（1：1）。（2） 一對多關系（1：n）對于實體集A中的每一個實體，實體集B中有n個實體（n>=0）與之聯(lián)系，而對于實體級B中的每個實體，實體級A中至多只有一個實體與之聯(lián)系，則稱實體集A與實體集B具有一對多關系。1.3.2實體與屬性關系（3）多對多關系（m：n）對于實體集A中每個實體，實體集B中有n個實體（n>=0）與之聯(lián)系，反之，對于實體集B中的每個實體，實體集A中有m個實體（m>=0）與之聯(lián)系，則稱實體A與實體B具有多對多關系。圖1-4兩個實體之間的關系類型1.3.2實體與屬性關系E-R不僅能夠描述兩個實體之間的關系也可以描述兩個以上實體或者一個實體內的關系。如圖1-5為三個實體之間的關系，對于供應商、項目和零件三個實體，一個供應商可以供給多個項目多種零件；一個項目可以使用不同供應商的多種零件；一個零件可以由多個供應商供給多個項目。圖1-5三個實體間的關系1.3.2實體與屬性關系如圖1-6是一個單個實體內的關系，在高等學校，教師通常是按照學院或者系進行管理的，每位教師由一個院長或者主任直接領導，而院長或系主任領導本院或者本系的多名教師，由于院長或者系主任都是教師中的一員，因此教師實體內部存在著領導與被領導的一對多的關系。圖1-6單個實體內的關系1.3.3E-R圖的設計原則

數(shù)據庫設計通常采用“一事一地”原則，可以從實體與屬性方面體現(xiàn)。（1） 屬性應該存在且僅存在于某一個地方（實體或者關聯(lián)）。該原則確保了數(shù)據庫中的某個數(shù)據僅存儲于某個數(shù)據庫表中，避免了同一數(shù)據存儲于多個數(shù)據庫表中，避免了數(shù)據冗余。（2） 實體是一個單獨的個體，不能存在于另一個實體中成為其屬性。該原則確保了一個數(shù)據庫表中不能包含另一個數(shù)據庫表，即不能出現(xiàn)“表中套表”的現(xiàn)象。

1.3.3E-R圖的設計原則

在“選課系統(tǒng)”中，學生選課時需要提供學號、姓名、班級名、所屬院校名及聯(lián)系方式等信息。學號、姓名以及聯(lián)系方式需要作為學生實體的屬性出現(xiàn)，而班級名和院系名則無法作為學生屬性出現(xiàn)。如果將班級名和院系名也作為學生實體的屬性，那么學生實體存在（學號、姓名、聯(lián)系方式、班級名、院系名）5個屬性，學生實體中出現(xiàn)了“表中套表”的現(xiàn)象，違背了“一事一地”原則。應該將“班級名”屬性與“院系名”屬性抽取出來，放入“班級”實體中，將一個“大”的“實體”分解中兩個“小”的實體，并且建立班級實體與學生實體之間的一對多關系.1.3.3E-R圖的設計原則（3） 同一個實體在同一個E-R圖僅出現(xiàn)一次。當同一個E-R圖中兩個實體間存在多種關系時，為了表示實體間的多種關系，建立不要讓同一個實體出現(xiàn)多次。

1.3.3E-R圖的設計原則

本著“一事一地”原則對“選課系統(tǒng)”進行設計，得到所有的“部分”E-R圖，并將其合并成為“選課系統(tǒng)”E-R圖如圖1-9所示，其中共有實體4個分別為教師、課程、學生和班級，每個實體包含的屬性實體間的關系如E-R圖所示。1.4MySQL存儲引擎

MySQL數(shù)據庫中典型的數(shù)據庫對象包括表、視圖、索引、存儲過程、函數(shù)、觸發(fā)器等，表是其中最為重要的數(shù)據庫對象。使用SQL語句“createtable表名”即可創(chuàng)建一個數(shù)據庫表，在創(chuàng)建數(shù)據庫表之前，必須首先明確該表的存儲引擎。1.4MySQL存儲引擎

存儲引擎實際上是如何存儲數(shù)據、如何為存儲的數(shù)據建立索引和如何更新、查詢數(shù)據。在關系數(shù)據庫中，數(shù)據以表的形式存儲，所以存儲引擎也可以稱為表類型。MySQL中的數(shù)據用各種不同的技術存儲在文件（或者內存）中。這些技術中的每一種技術都使用不同的存儲機制、索引技巧、鎖定水平并且最終提供廣泛的不同的功能和能力。通過選擇不同的技術，數(shù)據庫開發(fā)人員可以獲得額外的速度或者功能，從而改善所設計應用的整體功能。1.4MySQL存儲引擎 MySQL默認配置了許多不同的存儲引擎，可以預先設置或者在MySQL服務器中啟用。開發(fā)人員可以根據需要選擇適用于服務器、數(shù)據庫和表格的存儲引擎，以便在選擇如何存儲信息、如何檢索這些信息以及需要數(shù)據結合什么性能和功能的時候為設計提供最大的靈活性。與其他數(shù)據庫管理系統(tǒng)不同，MySQL提供了插件式（pluggable）的存儲引擎，存儲引擎是基于表的。同一個數(shù)據庫，不同的表，存儲引擎可以不同；同一個數(shù)據庫表在不同的場合可以應用于不同的存儲引擎。1.4.1InnoDB存儲引擎

與其他存儲引擎相比，InnoDB存儲引擎是事務（Transaction）安全的，并且支持外鍵。如果某張表主要提供聯(lián)機事務處理（OLTP）支持，需要執(zhí)行大量的增、刪、改操作（即insert、delete、update語句），處于事務安全方面考慮，InnoDB存儲引擎是較好的選擇。

對于支持事務的InnoDB表，影響速度的主要原因是打開了自動提交（autocommit）選項，或者程序沒有顯示調用“begintrasaction；”（開始事務）和“commit；”（提交事務），導致每條insert、delete或者update語句都自動開始事務和提交事務，嚴重影響了更新語句（insert、delete、update語句）的執(zhí)行效率。讓多條更新語句形成一個事務，可以大大提高更新操作的性能。從MySQL5.6版本開始，InnoDB存儲引擎的表已經支持全文索引，這大幅提升了InnoDB存儲引擎的檢索能力。1.4.1InnoDB存儲引擎

對于InnoDB存儲引擎的數(shù)據庫表而言，存在表空間的概念，InnoDB表空間分享表空間與獨享表空間。（1）共享表空間MySQL服務實例承載的所有數(shù)據庫的所有InnoDB表的數(shù)據信息、索引信息、各種元數(shù)據信息，以及事務的回滾（UNDO）信息，全部存放在共享表空間文件中。（2）獨享表空間如果將全局系統(tǒng)變量innodb_file_per_table的值設為ON（innodb_file_per_table的默認值為OFF），則之后再創(chuàng)建InnoDB存儲引擎的新表時，這些表的數(shù)據信息、索引信息將保存到獨享表空間文件中。1.4.2MyISAM存儲引擎

MyISAM存儲引擎是基于傳統(tǒng)的ISAM（IndexedSequentialAccessMethod有索引的順序訪問方法）類型，它是存儲記錄和文件的標注方法。與其他存儲引擎相比，MyISAM具有檢查和修復表格的大多數(shù)工具。

MyISAM表格可以被壓縮，且支持全文搜索。但它們不是事務安全，且不支持外鍵。如果事物回滾將造成不完全回滾，不具有原子性。如果執(zhí)行大量的查詢操作時，MyISAM是較好的選擇。1.4.2MyISAM存儲引擎

MyISAM存儲引擎是基于傳統(tǒng)的ISAM（IndexedSequentialAccessMethod有索引的順序訪問方法）類型，它是存儲記錄和文件的標注方法。與其他存儲引擎相比，MyISAM具有檢查和修復表格的大多數(shù)工具。

MyISAM表格可以被壓縮，且支持全文搜索。但它們不是事務安全，且不支持外鍵。如果事物回滾將造成不完全回滾，不具有原子性。如果執(zhí)行大量的查詢操作時，MyISAM是較好的選擇。1.4.3存儲引擎的選擇

選擇存儲引擎時，應根據應用特點選擇合適的存儲引擎。對于復雜的應用系統(tǒng)，還可以根據實際情況選擇多種存儲引擎進行結合。當不需要事務支持、并發(fā)相對較低、數(shù)據修改相對較少、以讀為主、數(shù)據一致性要求不高的場合適合選用MyISAM存儲引擎。當需要事務支持、行級鎖定對高并發(fā)有很好的適應能力，但需要確保查詢是通過索引完成，數(shù)據更新較為頻繁的場合，適合選用InnoDB存儲引擎。1.4.3存儲引擎的選擇

在采用InnoDB存儲引擎時需要注意：主鍵盡量小，避免給Secondaryindex帶來過大的空間負擔，避免全表掃描，因為使用表鎖，可可能緩存所有的索引和數(shù)據，提供響應速度，在大批量小插入時合理設置innodb_flush_log_at_trx_commit參數(shù)值，盡量自