第3章應(yīng)用數(shù)據(jù)庫(kù)設(shè)計(jì)與創(chuàng)建幻燈片課件

第3章應(yīng)用數(shù)據(jù)庫(kù)設(shè)計(jì)與創(chuàng)建倚動(dòng)實(shí)驗(yàn)室CONTENTS3.1數(shù)據(jù)庫(kù)基礎(chǔ)目錄3.2系統(tǒng)所涉的實(shí)體及其屬性3.3項(xiàng)目數(shù)據(jù)庫(kù)表3.4MySQL數(shù)據(jù)庫(kù)的構(gòu)建3.5數(shù)據(jù)庫(kù)操作數(shù)據(jù)庫(kù)，顧名思義，就是應(yīng)用中所需要的數(shù)據(jù)都保存在其中，數(shù)據(jù)庫(kù)設(shè)計(jì)的簡(jiǎn)單與復(fù)雜也直接影響到應(yīng)用設(shè)計(jì)的框架和結(jié)構(gòu)，所以，在數(shù)據(jù)庫(kù)設(shè)計(jì)時(shí)，需要用戶花費(fèi)比較多得時(shí)間去思考應(yīng)用中所需要的數(shù)據(jù)，并將他們整理成數(shù)據(jù)表。3.1數(shù)據(jù)庫(kù)基礎(chǔ)3.1.1基本概念在人們平常所提到的數(shù)據(jù)處理概念中，最主要的就是“數(shù)據(jù)”和“信息”，那么他們各自代表什么含義呢？其實(shí)數(shù)據(jù)和信息二者之間的關(guān)系是密不可分的，信息是對(duì)現(xiàn)實(shí)世界中存在的客觀實(shí)體、現(xiàn)象和關(guān)系進(jìn)行描述的具有特定意義的數(shù)據(jù)，是經(jīng)過(guò)加工處理的數(shù)據(jù)?！?shù)據(jù)：數(shù)據(jù)從字面上理解就是用來(lái)描述客觀事物的符號(hào)、標(biāo)記、圖形等信息的組合；那么從實(shí)際應(yīng)用上來(lái)看，數(shù)據(jù)就是能夠直觀的體現(xiàn)出與應(yīng)用相關(guān)的信息。·信息：以數(shù)據(jù)為載體的對(duì)客觀實(shí)際存在的事物、事件和概念的抽象反應(yīng)，是經(jīng)過(guò)加工處理過(guò)的數(shù)據(jù)?！?shù)據(jù)處理：數(shù)據(jù)處理就是將數(shù)據(jù)進(jìn)行相應(yīng)的加工，處理之后成為信息。數(shù)據(jù)處理是將數(shù)據(jù)轉(zhuǎn)換成信息的過(guò)程，是指對(duì)信息進(jìn)行收集、整理、存儲(chǔ)、加工以及傳播等一系列活動(dòng)的總和。我們可以簡(jiǎn)單的運(yùn)用下面的公式表述數(shù)據(jù)、信息和數(shù)據(jù)處理三者之間的關(guān)系：信息=數(shù)據(jù)+數(shù)據(jù)處理數(shù)據(jù)是原材料，而信息則是產(chǎn)品，數(shù)據(jù)處理的真正意義是為了產(chǎn)生信息而去處理相關(guān)的數(shù)據(jù)。數(shù)據(jù)庫(kù)設(shè)計(jì)的過(guò)程主要是三個(gè)階段，需求分析階段、數(shù)據(jù)庫(kù)設(shè)計(jì)與實(shí)施階段和數(shù)據(jù)庫(kù)維護(hù)階段。（1）需求分析階段（2）數(shù)據(jù)庫(kù)設(shè)計(jì)與實(shí)施階段（3）數(shù)據(jù)庫(kù)維護(hù)有些教科書(shū)上將數(shù)據(jù)庫(kù)的設(shè)計(jì)分為6個(gè)階段，這樣就將設(shè)計(jì)階段進(jìn)行更加詳細(xì)的劃分。1.需求分析階段2.概念設(shè)計(jì)階段3.邏輯設(shè)計(jì)階段4.物理設(shè)計(jì)階段5.數(shù)據(jù)庫(kù)實(shí)施階段6.數(shù)據(jù)庫(kù)運(yùn)行和維護(hù)階段3.1.2數(shù)據(jù)庫(kù)設(shè)計(jì)的基本過(guò)程創(chuàng)建數(shù)據(jù)庫(kù)表首先我們要知道，數(shù)據(jù)庫(kù)表是二維表，包含行與列，創(chuàng)建表包括定義列名稱、數(shù)據(jù)類型和約束等等。這些都是在創(chuàng)建數(shù)據(jù)表中完成。列名稱的選取最好是取一個(gè)具有代表意義的名稱，比如學(xué)生姓名用SName，而不要去使用沒(méi)有代表意義的名稱，那樣會(huì)使得閱讀表產(chǎn)生困難，不易于理解；數(shù)據(jù)類型是定義字段的取值類型和取值范圍，用戶也可以自定義數(shù)據(jù)類型；表約束則是更進(jìn)一步限制了列的取值范圍。這些約束包括下面幾種類型：·主鍵約束：限制列的取值不為空，而且不能重復(fù)?！ね怄I約束：限制列的取值受其他列或者其他表中列的取值范圍約束。·列取值范圍約束：限制列的取值必須是有意義的，比如性別只能取“男”或“女”等。·取值是否允許為空等。通過(guò)SQL語(yǔ)句創(chuàng)建表的一般格式：CREATETABLE<表名>(<列名><數(shù)據(jù)類型>[列級(jí)完整性約束條件][,<列名><數(shù)據(jù)類型>[列級(jí)完整性約束條件]]……[,<表級(jí)完整性約束條件>])下面我們通過(guò)一個(gè)創(chuàng)建“用戶”表User來(lái)詳細(xì)了解創(chuàng)建表的方法。可以在Sql-Server或MySQL工具中輸入代碼。1.CREATETABLEUser(2.UNo

char(10)NOTNULL,3.UName

char(10)NOTNULL,4.USex

char(2)5.UAge

tinyint,6.UArea

char(10)7.UEmail

char(20),8.PRIMARYKEY(UNo))在用戶表中，我們定義了6個(gè)字段，分別是用戶編號(hào)，用戶名，用戶性別，用戶年齡、用戶地區(qū)和電子郵件地址，如代碼中2-6行所示。其中用戶編號(hào)和用戶名是不為空的，最后將用戶編號(hào)設(shè)置為主鍵約束。這樣一張數(shù)據(jù)庫(kù)表就創(chuàng)建好了。添加數(shù)據(jù)插入數(shù)據(jù)有兩種方式：一是向表中添加一行數(shù)據(jù)，二是一次添加多條數(shù)據(jù)。（1）

單行數(shù)據(jù)的插入插入單行數(shù)據(jù)的SQL語(yǔ)句格式如下：INSERTINTO<表名>[(<列名表>)VALUES(<值列表>)]那么用戶在使用單行插入語(yǔ)句時(shí)應(yīng)該注意以下2點(diǎn)：1、值列表中的值與列名表中的列按順序?qū)?yīng)，要求它們的數(shù)據(jù)類型必須一致。2、如果<表名>后邊沒(méi)有指明列名，則新插入記錄的值的順序必須與表中列的順序一致，而且每一列都必須有值（可以為空）。例如，我們向User表中添加一條記錄INSERTINTOUserVALUES(‘2014011’,’張三’,’男’,18’,’江西’,45234123@’)這樣我們就在User表中添加了一條記錄。（2）多行數(shù)據(jù)的插入多行數(shù)據(jù)的插入實(shí)際上就是將某個(gè)查詢語(yǔ)句的結(jié)果插入到相應(yīng)的表中，插入多行數(shù)據(jù)的SQL語(yǔ)句格式如下：INSERTINTO<表名>[(<列表名>)子查詢語(yǔ)句]我們通常會(huì)在以下兩種情況下使用多行插入：1、當(dāng)用戶需要從表中提取數(shù)據(jù)，來(lái)生成新表時(shí)。2、當(dāng)表結(jié)構(gòu)變更之后，原有的數(shù)據(jù)可以重新置入。例如我們現(xiàn)在要求用戶表中所有用戶的平均年齡，并把結(jié)果存入新的數(shù)據(jù)庫(kù)表中。那么，我們首先要?jiǎng)?chuàng)建一張新的數(shù)據(jù)表UAveage。1.CREATETABLEUAveage(UAreachar(10),UAveagesmallint)2.INSERTINTOUAveage(UArea,UAveage)3.SELECTUArea,UAveage(UAge)FROMUser4.GROUPBYUAveage刪除數(shù)據(jù)刪除數(shù)據(jù)比較容易，但是需要考慮到刪除的條件是否是自己需要的，不要錯(cuò)刪了重要的數(shù)據(jù)。刪除數(shù)據(jù)的SQL語(yǔ)句格式如下：DELETEFROM<表名>[WHERE<刪除條件>]Where條件可以有可無(wú)，如果不帶where條件語(yǔ)句，那么就將刪除整張表，刪除后，表還是存在，只是內(nèi)容為空。例如我們刪除User表中年齡為20歲的用戶。1.DELETEFROMUser2.WHEREUAge=20IN3.(SELETEUNoFROMUser)這樣就從User表中刪除了年齡為20歲的用戶。如果需要?jiǎng)h除整張表中的數(shù)據(jù)，直接刪除即可。DELETEFROMUser修改數(shù)據(jù)在向表中添加了數(shù)據(jù)之后，如果需要再次修改數(shù)據(jù)，那么可以使用UPDATE語(yǔ)句來(lái)對(duì)數(shù)據(jù)進(jìn)行更新操作。修改數(shù)據(jù)的SQL語(yǔ)句格式如下：

UPDATE<表名>SET<列名

=表達(dá)式>[,……n][WHERE<更新條件>]

<表名>代表需要修改數(shù)據(jù)的表的名稱。SET指定需要修改的列，表達(dá)式指定需要更新的數(shù)據(jù)內(nèi)容，WHERE子句可以不寫(xiě)，如果不寫(xiě)那么就會(huì)修改表中指定列的全部值。例如，我們現(xiàn)在更新表中學(xué)號(hào)為’2014011’用戶的年齡為22。1.UPDATEUserSETUAge=222.WHEREUNo=‘2014011’查詢數(shù)據(jù)SQL語(yǔ)言的核心就是數(shù)據(jù)的查詢，從龐大的數(shù)據(jù)找找出我們需要的數(shù)據(jù)是一件多么偉大的事啊。雖然它的命令只有SELECT一個(gè)，但是它可以涵蓋多個(gè)子句進(jìn)行組合查詢。我們簡(jiǎn)單介紹一下查詢語(yǔ)句的格式。1.SELECT[ALL|DISTINCT]<目標(biāo)列表達(dá)式>[,<目標(biāo)列表達(dá)式>]…2.FROM<表名或視圖名>[,<表名或視圖名>]…3.[WHERE<條件表達(dá)式>]4.[GROUPBY<列名1>[HAVING<條件表達(dá)式>]]5.[ORDERBY<列名2>[ASC|DESC]]

我們根據(jù)WHERE條件子句從FROM子句制定的表或視圖中找到滿足條件的元組，再按照SELECT子句中的目標(biāo)列表達(dá)式，選出元組中的屬性值形成結(jié)果。以表的形式顯示出來(lái)。ORDERBY表示查詢的結(jié)果是按升序排列還是降序排列。3.2系統(tǒng)所涉的實(shí)體及其屬性“生源省市”實(shí)體主要保存我國(guó)各個(gè)省或直轄市的信息，如下圖3-1所示。圖3-1“生源省市”實(shí)體圖圖3-2“學(xué)校所在地區(qū)”實(shí)體圖“學(xué)校所在地區(qū)”實(shí)體主要保存學(xué)校所在省市縣的信息，如圖3-2所示。“批次”實(shí)體主要保存錄取的批次信息，包括一本、二本、三本、?？坪吞崆氨究乒参宸N，如圖3-3所示?！翱祁悺敝饕４鎸I(yè)類別，分為文史、理工、綜合、體育和藝術(shù)共五類，如圖3-4所示。圖3-3“批次”實(shí)體圖圖3-4“科類”實(shí)體圖“學(xué)?！钡闹饕獙傩园ǎ簩W(xué)校編號(hào)、學(xué)校代碼、學(xué)校名稱、是否211、是否985、是否教育部直屬以及學(xué)校所在地，如圖3-5所示。學(xué)校與學(xué)校所在地區(qū)為一對(duì)多的關(guān)系，如圖3-6所示。一個(gè)地區(qū)可以有多個(gè)學(xué)校，但一個(gè)學(xué)校只位于一個(gè)地區(qū)，當(dāng)學(xué)校在其它地區(qū)有分校時(shí)，將其看做是兩個(gè)學(xué)校。圖3-5“學(xué)?！睂?shí)體圖圖3-6學(xué)校與學(xué)校所在地區(qū)E-R圖每個(gè)學(xué)校有一個(gè)學(xué)校招生部門。學(xué)校招生部門包括聯(lián)系編號(hào)、學(xué)校名稱、用戶名（用于登錄）、密碼、聯(lián)系人姓名、郵箱、辦公室電話、手機(jī)號(hào)碼、QQ號(hào)、網(wǎng)址、被聯(lián)系次數(shù)、創(chuàng)建時(shí)間、最后登錄時(shí)間，如圖3-7所示?！罢猩块T聯(lián)系人”是學(xué)校招生部門的聯(lián)系人，主要包括，聯(lián)系人編號(hào)、聯(lián)系人姓名、職位、電話和郵箱，如圖3-8所示。圖3-7“學(xué)校招生部門”實(shí)體圖圖3-8“招生部門聯(lián)系人”實(shí)體圖“專業(yè)”主要包括屬性：專業(yè)編號(hào)、專業(yè)名稱、專業(yè)代碼，如圖3-9所示。學(xué)校每個(gè)專業(yè)有一個(gè)專業(yè)負(fù)責(zé)人，“專業(yè)負(fù)責(zé)人”主要包括：負(fù)責(zé)人編號(hào)、負(fù)責(zé)人姓名、負(fù)責(zé)人電話和負(fù)責(zé)人郵箱，如下圖3-10所示。圖3-9“專業(yè)”實(shí)體圖圖3-10“專業(yè)負(fù)責(zé)人”實(shí)體圖學(xué)校、專業(yè)及專業(yè)負(fù)責(zé)人有關(guān)系，一個(gè)學(xué)校開(kāi)辦多個(gè)專業(yè)，一個(gè)專業(yè)可以由多個(gè)專業(yè)開(kāi)辦，因此學(xué)校和專業(yè)是一對(duì)多的關(guān)系。一個(gè)專業(yè)擁有一個(gè)專業(yè)負(fù)責(zé)人，一個(gè)專業(yè)負(fù)責(zé)人負(fù)責(zé)一個(gè)專業(yè)，因此專業(yè)和專業(yè)負(fù)責(zé)人是一對(duì)一的關(guān)系，E-R圖如圖3-11所示。生源省市跟批次、科類等實(shí)體有關(guān)系，同時(shí)還應(yīng)包括年份、分?jǐn)?shù)和省控線編號(hào)屬性。其中生源省市與批次、科類的關(guān)系，均為一對(duì)多，只有批次、科類以及年份三個(gè)個(gè)因素才能唯一確定一個(gè)生源省市的省控線分?jǐn)?shù)，如圖3-12所示。圖3-11學(xué)校與專業(yè)E-R圖圖3-12省控線E-R圖學(xué)校跟生源省市、批次、科類、學(xué)校所在地區(qū)等實(shí)體有關(guān)系，同時(shí)還應(yīng)包括年份、平均分、投檔線、最高分屬性。其中學(xué)校與批次、生源省市、科類間的關(guān)系，均為一對(duì)多，學(xué)校與學(xué)校所在地區(qū)的關(guān)系是一對(duì)一的關(guān)系。只有生源省市、批次、科類以及年份四個(gè)因素才能唯一確定這一學(xué)校某年具體批次具體科類的錄取線。錄取線包括平均分和最高分兩項(xiàng)，如圖3-13所示。圖3-13學(xué)校錄取E-R圖學(xué)校跟專業(yè)、生源省市、批次、科類等實(shí)體有關(guān)系，同時(shí)還應(yīng)包括年份、省控線、最高分、平均分屬性。其中專業(yè)與學(xué)校、生源省市與學(xué)校、批次學(xué)校、科類與學(xué)校間的關(guān)系，均為一對(duì)多，只有專業(yè)、生源省市、批次、科類以及年份五個(gè)因素才能唯一確定這一學(xué)校某個(gè)專業(yè)某年具體批次具體科類的錄取線，如圖3-14所示。錄取線包括平均分和最高分兩項(xiàng)。注意：同一個(gè)專業(yè)在不同學(xué)校批次可能不同，有些專業(yè)即可招文科生也可招理科生。圖3-14學(xué)校與專業(yè)錄取E-R圖“高考咨訊”實(shí)體保存與高考相關(guān)的資訊信息，包括：咨訊編號(hào)、生源省市編號(hào)和咨訊內(nèi)容，如圖3-15所示。圖3-15“高考咨訊”實(shí)體圖“豹考通”App數(shù)據(jù)總體E-R圖如圖3-16所示。圖3-16“豹考通”App數(shù)據(jù)總體E-R圖3.3項(xiàng)目數(shù)據(jù)庫(kù)表專業(yè)表主要包括專業(yè)編號(hào)、專業(yè)名稱、專業(yè)代碼字段，如表3-1所示。表3-1專業(yè)表（specialty）地區(qū)表主要包括地區(qū)編號(hào)、地區(qū)名稱字段，如表3-2所示。表3-2地區(qū)表（area）批次表主要包括批次編號(hào)、批次名稱字段。批次分為：一本、二本、三本、?？?、提前本科，如表3-3所示。表3-3批次表（batch）科類表主要包括科類編號(hào)、科類名稱字段。批次分為：文史、理工、綜合、藝術(shù)、體育，如表3-4所示。表3-4科類表（category）學(xué)校表主要包括學(xué)校編號(hào)、學(xué)校名稱、學(xué)校代碼、學(xué)校所在地、是否211、是否985、是否直屬字段，如表3-5所示。表3-5學(xué)校表（school）省控線表主要包括省控線編號(hào)、省控線值、年份、批次編號(hào)、科類編號(hào)、生源地，如表3-6所示。表3-6省控線表（controlLine）學(xué)校錄取線表主要包括錄取線編號(hào)、年份、批次編號(hào)、科類編號(hào)、學(xué)校編號(hào)、生源地、錄取平均分、錄取最高分、投檔線、投檔最低排名、預(yù)測(cè)投檔線，如表3-7所示。表3-7學(xué)校錄取表（schoolRecruit）專業(yè)錄取線表主要包括錄取線編號(hào)、年份、批次編號(hào)、科類編號(hào)、學(xué)校編號(hào)、生源地、專業(yè)編號(hào)、專業(yè)名稱和專業(yè)錄取線，如表3-8所示。表3-8專業(yè)錄取表（majorRecruit）學(xué)校聯(lián)系信息包括聯(lián)系編號(hào)、學(xué)校編號(hào)、用戶名（用于登錄）、密碼、聯(lián)系人姓名、郵箱、辦公室電話、手機(jī)號(hào)碼、QQ號(hào)、網(wǎng)址、被聯(lián)系次數(shù)、創(chuàng)建時(shí)間、最后登錄時(shí)間，如表3-9所示。表3-9學(xué)校聯(lián)系信息表（schoolInfo）專業(yè)聯(lián)系信息包括專業(yè)聯(lián)系編號(hào)、學(xué)校編號(hào)、專業(yè)名稱、聯(lián)系人姓名、職位、郵箱、辦公室電話、手機(jī)號(hào)碼、QQ號(hào)，如表3-10所示。表3-10專業(yè)聯(lián)系信息表（specialtyInfo）3.4MySQL數(shù)據(jù)庫(kù)的構(gòu)建3.4.1MySQL特點(diǎn)在“豹考通”應(yīng)用案例中，我們的服務(wù)器選擇的是MySQL。MySQL是一個(gè)開(kāi)源的小型關(guān)系式數(shù)據(jù)庫(kù)管理系統(tǒng)，開(kāi)發(fā)者為瑞典MySQLAB公司。MySQL被廣泛應(yīng)用在Internet上的中小型網(wǎng)站中。由于其體積小、速度快、總體擁有成本低，尤其是開(kāi)源這一特點(diǎn)，許多中小型網(wǎng)站為了降低網(wǎng)站總體擁有成本而選擇了MySQL作為網(wǎng)站的數(shù)據(jù)庫(kù)。我們?cè)趹?yīng)用開(kāi)發(fā)時(shí)之所以選擇MySQL作為數(shù)據(jù)庫(kù)，是因?yàn)樗哂幸韵聨讉€(gè)特點(diǎn)：1.MySQL使用的核心是完