MySQL云數(shù)據(jù)庫(kù)應(yīng)用與實(shí)踐 課件匯 李寧 第1-7章 數(shù)據(jù)庫(kù)系統(tǒng)概述- 數(shù)據(jù)庫(kù)服務(wù)端編程

第一章

數(shù)據(jù)庫(kù)系統(tǒng)概述Database西北工業(yè)大學(xué)NorthwesternPolytechnicalUniversity數(shù)據(jù)庫(kù)目錄1.1

數(shù)據(jù)庫(kù)系統(tǒng)概述1.2.

數(shù)據(jù)建模1.3.MySQL關(guān)系數(shù)據(jù)庫(kù)1.4.云數(shù)據(jù)庫(kù)1.5.基于MySQL的云數(shù)據(jù)庫(kù)1.1數(shù)據(jù)庫(kù)系統(tǒng)概述數(shù)據(jù)(Data)數(shù)據(jù)庫(kù)(DataBase)數(shù)據(jù)庫(kù)管理系統(tǒng)(DBMS)數(shù)據(jù)庫(kù)系統(tǒng)(DBS)描述事物的符號(hào)記錄。數(shù)據(jù)庫(kù)中存儲(chǔ)的基本對(duì)象。數(shù)據(jù)(Data)數(shù)據(jù)庫(kù)(DataBase)存放數(shù)據(jù)的倉(cāng)庫(kù)，長(zhǎng)期儲(chǔ)存在計(jì)算機(jī)內(nèi)的，有組織的，可共享的數(shù)據(jù)集合。1.1數(shù)據(jù)庫(kù)系統(tǒng)概述廣義數(shù)據(jù)庫(kù)系統(tǒng)1.1數(shù)據(jù)庫(kù)系統(tǒng)概述常見(jiàn)DBMS1.2.1數(shù)據(jù)模型數(shù)據(jù)建模：現(xiàn)實(shí)世界中客觀對(duì)象的抽象過(guò)程DBMS支持的數(shù)據(jù)模型概念模型認(rèn)識(shí)抽象信息世界機(jī)器世界現(xiàn)實(shí)世界概念模型數(shù)據(jù)庫(kù)設(shè)計(jì)人員完成邏輯模型物理模型由DBMS完成概念模型邏輯模型數(shù)據(jù)庫(kù)設(shè)計(jì)人員完成現(xiàn)實(shí)世界1.2.2概念模型與E-R圖概念模型對(duì)現(xiàn)實(shí)世界的事物符號(hào)化的描述，為計(jì)算機(jī)處理做準(zhǔn)備。常用的概念模型是E-R(Entity-Relationship)圖。1.實(shí)體（Entity）客觀存在并可相互區(qū)別的事物稱為實(shí)體。2.屬性（Attribute）實(shí)體所具有的某一特性稱為屬性。3.碼（Key）唯一標(biāo)識(shí)實(shí)體的屬性集稱為碼。4.域（Domain）屬性的取值范圍稱為該屬性的域。5.實(shí)體型(EntityType)用實(shí)體名及其屬性名集合來(lái)抽象和刻畫(huà)同類實(shí)體稱為實(shí)體型6.實(shí)體集(EntitySet)同型實(shí)體的集合稱為實(shí)體集7.聯(lián)系(Relationship)實(shí)體內(nèi)部或者實(shí)體之間的聯(lián)系1.2.2概念模型與E-R圖概念模型(E-R圖)（P.P.S.Chen陳品山教授,1976提出）1.實(shí)體（Entity）2.屬性（Attribute）3.碼（Key）4.域（Domain）5.實(shí)體型(EntityType)6.實(shí)體集(EntitySet)7.聯(lián)系(Relationship)1.2.2概念模型與E-R圖實(shí)體型：矩形屬性：橢圓，用無(wú)向邊將其與相應(yīng)的實(shí)體連接起來(lái)聯(lián)系：菱形，用無(wú)向邊將其與相應(yīng)的實(shí)體連接起來(lái)，同時(shí)表明聯(lián)系的類型（1:1,1:n或m:n）用E-R圖建立概念模型例:學(xué)生實(shí)體具有學(xué)號(hào)、姓名、性別、年齡等屬性,用E-R圖表示學(xué)生學(xué)號(hào)年齡性別姓名1.2.2概念模型與E-R圖聯(lián)系(Relationship)a1

a2

a3

a4

b1

b2

b3

b4

A

B

共有三種類型：1：1

1：n

m:na1

a2

a3

b1

b2

b3

b4

b5

A

B

a1

a2

a3

a4

b1

b2

b3

b4

A

B

1對(duì)1聯(lián)系1對(duì)多聯(lián)系多對(duì)多聯(lián)系1.2.2概念模型與E-R圖擴(kuò)展的ER圖復(fù)合屬性1.2.2概念模型與E-R圖擴(kuò)展的ER圖ISA聯(lián)系（類的實(shí)例,isa;繼承；例如：本科生/研究生和學(xué)生）分類屬性：學(xué)生的2個(gè)子類（有兩個(gè)分類屬性值）不相交約束：一個(gè)實(shí)體不能同時(shí)屬于多個(gè)類別（×號(hào)）若沒(méi)有×號(hào)，表明是可重疊約束1.2.2概念模型與E-R圖擴(kuò)展的ER圖基數(shù)約束基數(shù)約束：min:max:每個(gè)實(shí)體參與聯(lián)系集中的次數(shù)

min=1:

強(qiáng)制參與約束，實(shí)體集中的每個(gè)實(shí)體在聯(lián)系集中至少一次

min=0:

非強(qiáng)制參與約束，實(shí)體集中的部分實(shí)體可以不出現(xiàn)在聯(lián)系集中學(xué)生選修課程20..300..*學(xué)生的約束（每個(gè)學(xué)生都要選課，且選課數(shù)[20,30]范圍內(nèi)）課程的約束（某個(gè)課程號(hào)可以零次或者多次）本例引自中文教第六版材P2171.2.3邏輯模型與關(guān)系模型域：屬性的取值范圍。關(guān)系模式：對(duì)關(guān)系結(jié)構(gòu)的描述，定義如下： 關(guān)系名（屬性1，屬性1，…，屬性n）例：學(xué)生（學(xué)號(hào)，姓名，年齡，性別，系，年級(jí)）關(guān)系數(shù)據(jù)模型數(shù)據(jù)結(jié)構(gòu)：在用戶觀點(diǎn)下，關(guān)系模型中數(shù)據(jù)的邏輯結(jié)構(gòu)是一張二維表。1.2.4物理模型應(yīng)用程序1應(yīng)用程序3應(yīng)用程序4應(yīng)用程序5應(yīng)用程序2外模式1外模式3外模式2模式內(nèi)模式數(shù)據(jù)外模式／模式映像模式／內(nèi)模式映像1.3MySQL數(shù)據(jù)庫(kù)1.3MySQL數(shù)據(jù)庫(kù)

MySQL數(shù)據(jù)庫(kù)管理系統(tǒng)內(nèi)核架構(gòu)1.4云數(shù)據(jù)庫(kù)以云服務(wù)模式部署的數(shù)據(jù)庫(kù)稱為云數(shù)據(jù)庫(kù)。新型按需付費(fèi)的云數(shù)據(jù)庫(kù)與傳統(tǒng)的數(shù)據(jù)庫(kù)相比，具有以下特征：無(wú)需自建機(jī)房，進(jìn)行設(shè)備采購(gòu)和軟件安裝，開(kāi)箱即用，方便快捷?？筛鶕?jù)需求靈活變化，易彈性伸縮，提供與業(yè)務(wù)匹配的存儲(chǔ)能力和計(jì)算能力。不受地域限制，只需具有聯(lián)網(wǎng)設(shè)備，即可訪問(wèn)操控?cái)?shù)據(jù)庫(kù)。同時(shí)，該模式也方便進(jìn)行資源共享。專業(yè)的運(yùn)維支持，包括安全檢查、性能調(diào)優(yōu)、容災(zāi)、備份、恢復(fù)、監(jiān)控、遷移等，同時(shí)提供基于Web的自助管理模式。1.4云數(shù)據(jù)庫(kù)1.5基于MySQL的云數(shù)據(jù)庫(kù)華為云數(shù)據(jù)庫(kù)GaussDB（forMySQL）(引自華為官網(wǎng))合作QQ：243001978第二章

數(shù)據(jù)庫(kù)設(shè)計(jì)基礎(chǔ)知識(shí)Database西北工業(yè)大學(xué)NorthwesternPolytechnicalUniversity數(shù)據(jù)庫(kù)目錄2.1數(shù)據(jù)庫(kù)設(shè)計(jì)概述2.2需求分析2.3概念結(jié)構(gòu)設(shè)計(jì)2.4邏輯結(jié)構(gòu)設(shè)計(jì)2.5數(shù)據(jù)庫(kù)的物理設(shè)計(jì)2.6數(shù)據(jù)庫(kù)實(shí)施2.7數(shù)據(jù)庫(kù)運(yùn)行和維護(hù)2.1數(shù)據(jù)庫(kù)系統(tǒng)概述數(shù)據(jù)庫(kù)設(shè)計(jì)步驟與階段性設(shè)計(jì)成果2.2需求分析需求分析的任務(wù)需求分析就是通過(guò)詳細(xì)調(diào)查現(xiàn)實(shí)世界要處理的對(duì)象（組織、部門、企業(yè)等），充分了解原系統(tǒng)（手工系統(tǒng)或計(jì)算機(jī)系統(tǒng)）工作概況，明確用戶的各種需求，并在此基礎(chǔ)上確定新系統(tǒng)的功能。信息要求用戶需要從數(shù)據(jù)庫(kù)中獲得信息的內(nèi)容與性質(zhì)，由此導(dǎo)出數(shù)據(jù)要求，即在數(shù)據(jù)庫(kù)中需要存儲(chǔ)哪些數(shù)據(jù)。處理要求用戶對(duì)處理功能的要求，對(duì)處理的響應(yīng)時(shí)間的要求，對(duì)處理方式的要求(批處理/聯(lián)機(jī)處理)。安全性完整性要求數(shù)據(jù)自身的約束，數(shù)據(jù)之間的約束關(guān)系，以及數(shù)據(jù)的敏感分析，訪問(wèn)及數(shù)據(jù)數(shù)據(jù)的用戶級(jí)別等。2.2需求分析—數(shù)據(jù)流圖與數(shù)據(jù)字典需求分析結(jié)果的分析和表達(dá)方法？數(shù)據(jù)流圖數(shù)據(jù)字典數(shù)據(jù)項(xiàng)數(shù)據(jù)結(jié)構(gòu)數(shù)據(jù)流數(shù)據(jù)存儲(chǔ)處理過(guò)程2.2需求分析—數(shù)據(jù)流圖與數(shù)據(jù)字典數(shù)據(jù)流圖分析各項(xiàng)業(yè)務(wù)的執(zhí)行過(guò)程和期間的數(shù)據(jù)聯(lián)系，分析的結(jié)果以數(shù)據(jù)流圖(DataFlowdiagram，DFD)的形式描述。數(shù)據(jù)流圖的表達(dá)方式：圓角矩形：表示一次處理過(guò)程(P)有向線：表示數(shù)據(jù)流雙線段：表示存儲(chǔ)的數(shù)據(jù)(D)分析方法：通常采用SA(StructuredAnalysis)方法自頂向下的方法，即從最上層的系統(tǒng)組織機(jī)構(gòu)入手，采用逐層分解的方式分析系統(tǒng)；或先勾勒出業(yè)務(wù)流程的主要階段，再對(duì)每一階段進(jìn)行細(xì)分。數(shù)據(jù)記錄流出數(shù)據(jù)流入數(shù)據(jù)處理2.2需求分析—數(shù)據(jù)流圖與數(shù)據(jù)字典數(shù)據(jù)流圖示例：2.2需求分析—數(shù)據(jù)流圖與數(shù)據(jù)字典數(shù)據(jù)字典描述2.2需求分析—數(shù)據(jù)流圖與數(shù)據(jù)字典數(shù)據(jù)字典描述2.2需求分析—數(shù)據(jù)流圖與數(shù)據(jù)字典數(shù)據(jù)字典描述2.2需求分析—數(shù)據(jù)流圖與數(shù)據(jù)字典數(shù)據(jù)字典描述2.3概念結(jié)構(gòu)設(shè)計(jì)數(shù)據(jù)抽象，局部視圖設(shè)計(jì)視圖集成DFDDD總E-R圖邏輯結(jié)構(gòu)設(shè)計(jì)返回用戶征求意見(jiàn)到滿意為止需求分析分E-R圖

1)抽象數(shù)據(jù)+設(shè)計(jì)局部視圖

2)集成局部視圖，得到全體的概念結(jié)構(gòu)2.3概念結(jié)構(gòu)設(shè)計(jì)步驟一：抽象數(shù)據(jù)1)分類2）聚集ismemberof：例如實(shí)體型ispartof：例如屬性3）概括issubsetof：例如超/子類實(shí)體型本科生研究生學(xué)生超類子類學(xué)生學(xué)號(hào)姓名學(xué)生張穎王平2.3概念結(jié)構(gòu)設(shè)計(jì)步驟二：設(shè)計(jì)局部視圖(1)選擇局部應(yīng)用

恰當(dāng)選擇各級(jí)數(shù)據(jù)流圖中的某一層的某一部分業(yè)務(wù)，準(zhǔn)備設(shè)計(jì)局部E-R圖。通常選擇數(shù)據(jù)流圖的中間級(jí)，這一級(jí)實(shí)體描述準(zhǔn)確，實(shí)體間的聯(lián)系也比較清晰。(2)設(shè)計(jì)分E-R圖

對(duì)于每一局部應(yīng)用，其所用到的數(shù)據(jù)都應(yīng)該收集在數(shù)據(jù)字典中了，依照該局部應(yīng)用的數(shù)據(jù)流圖，從數(shù)據(jù)字典中提取出數(shù)據(jù)，使用抽象機(jī)制，確定局部應(yīng)用中的實(shí)體、實(shí)體的屬性、實(shí)體標(biāo)識(shí)符及實(shí)體間的聯(lián)系及其類型。重要原則：能作為屬性對(duì)待的盡量作為屬性對(duì)待2.3概念結(jié)構(gòu)設(shè)計(jì)確定實(shí)體與屬性的兩條準(zhǔn)則(1)屬性是不可再分的數(shù)據(jù)項(xiàng)，屬性不可以再有屬性；(2)屬性不能與其他實(shí)體發(fā)生聯(lián)系，聯(lián)系只能是實(shí)體與實(shí)體之間的。例：職工(職工號(hào)，姓名，年齡，職稱)，其中的職稱如果與工資、住房和福利掛勾(即有聯(lián)系)，則應(yīng)該單獨(dú)作為實(shí)體，而職工與職稱間構(gòu)成聯(lián)系。職工職工號(hào)姓名年齡職稱職工職工號(hào)姓名年齡職稱職稱名工資住房福利聘任2.3概念結(jié)構(gòu)設(shè)計(jì)步驟三：視圖集成選擇兩個(gè)具有相同實(shí)體的E-R圖，通過(guò)相同實(shí)體將兩個(gè)E-R圖連結(jié)起來(lái)構(gòu)成一個(gè)E-R圖，然后再與其它E-R圖連結(jié)直到將所有的局部E-R圖全部連結(jié)成一個(gè)E-R圖，稱為全局E-R圖。教師編號(hào)姓名學(xué)歷課程代課MN課程號(hào)課程名學(xué)分教師編號(hào)姓名學(xué)歷M學(xué)生學(xué)號(hào)姓名性別年齡成績(jī)學(xué)習(xí)課程N(yùn)課程號(hào)課程名學(xué)分MN代課學(xué)生姓名性別年齡M成績(jī)課程學(xué)習(xí)N課程號(hào)課程名學(xué)分學(xué)號(hào)2.3概念結(jié)構(gòu)設(shè)計(jì)？視圖集成的步驟1.合并E-R圖，生成初步E-R圖屬性沖突

--屬性域不同（取值范圍，類型等）

--屬性取值單位不同（公斤/斤等）命名沖突

--同名異義

--異名同義結(jié)構(gòu)沖突

--同一實(shí)體在不同的分E-R圖中有不同的屬性；

--同一對(duì)象在E-R圖A中被抽象為實(shí)體,在E-R圖B中又被抽象為屬性；

--同一個(gè)聯(lián)系在不同的分E-R圖中聯(lián)系的種類不同解決方法：--統(tǒng)一--綜合2.3概念結(jié)構(gòu)設(shè)計(jì)？視圖集成的步驟2.消除不必要的冗余，設(shè)計(jì)基本E-R圖1)由于Q3=Q2*Q1，Q4=∑Q5，故Q3，Q4多余，2)同時(shí)“使用”聯(lián)系也可以由“構(gòu)成”和“消耗”傳遞表達(dá)，故“使用”多余，可以去掉該聯(lián)系及其屬性。產(chǎn)品零件材料倉(cāng)庫(kù)構(gòu)成消耗存放零件數(shù)Q1使用用量Q3耗用量Q2存放量Q5mnmmnnn1存放量Q42.4邏輯結(jié)構(gòu)設(shè)計(jì)？1.E-R圖向關(guān)系模型的轉(zhuǎn)換轉(zhuǎn)換內(nèi)容

將實(shí)體、實(shí)體的屬性和實(shí)體之間的聯(lián)系轉(zhuǎn)化為關(guān)系模式轉(zhuǎn)換方法–實(shí)體型的轉(zhuǎn)換一個(gè)實(shí)體型轉(zhuǎn)換為一個(gè)關(guān)系模式。

--實(shí)體型的屬性轉(zhuǎn)換為關(guān)系的屬性

--實(shí)體標(biāo)識(shí)符轉(zhuǎn)換為關(guān)系的碼例：學(xué)生實(shí)體E-R圖如右所示：學(xué)生學(xué)號(hào)出生日期年級(jí)所在系平均成績(jī)姓名轉(zhuǎn)換為如下關(guān)系模式：

學(xué)生(學(xué)號(hào),姓名,出生日期,所在系,年級(jí),平均成績(jī))2.4邏輯結(jié)構(gòu)設(shè)計(jì)轉(zhuǎn)換方法-聯(lián)系的轉(zhuǎn)換(1:1)轉(zhuǎn)換成如下關(guān)系模式：班級(jí)(班號(hào),系別,班主任,入學(xué)時(shí)間)班長(zhǎng)(學(xué)號(hào),姓名,性別,年齡)班級(jí)-班長(zhǎng)(班號(hào),學(xué)號(hào),任期)班級(jí)班級(jí)-班長(zhǎng)班長(zhǎng)111:1聯(lián)系任期一個(gè)1:1

聯(lián)系可以轉(zhuǎn)換為一個(gè)獨(dú)立的關(guān)系模式，也可以與任意一端對(duì)應(yīng)的關(guān)系模式合并。①轉(zhuǎn)換為一個(gè)獨(dú)立的關(guān)系模式關(guān)系的屬性：與該聯(lián)系相連的各實(shí)體的碼以及聯(lián)系本身的屬性關(guān)系的候選碼：每個(gè)實(shí)體的碼均是該關(guān)系的候選碼2.4邏輯結(jié)構(gòu)設(shè)計(jì)班級(jí)班級(jí)-班長(zhǎng)班長(zhǎng)111:1聯(lián)系任期②與某一端對(duì)應(yīng)的關(guān)系模式合并合并后關(guān)系的屬性：加入另一關(guān)系的碼和聯(lián)系本身的屬性合并后關(guān)系的碼：不變轉(zhuǎn)換成如下關(guān)系模式：班級(jí)(班號(hào),系別,班主任,入學(xué)時(shí)間,

班長(zhǎng)學(xué)號(hào),班長(zhǎng)任期)班長(zhǎng)(學(xué)號(hào),姓名,性別,年齡)

或班級(jí)(班號(hào),系別,班主任,入學(xué)時(shí)間)班長(zhǎng)(學(xué)號(hào),姓名,性別,年齡,

班長(zhǎng)班號(hào),任期)轉(zhuǎn)換方法-聯(lián)系的轉(zhuǎn)換(1:1)2.4邏輯結(jié)構(gòu)設(shè)計(jì)2）一個(gè)1:n

聯(lián)系可以轉(zhuǎn)換為一個(gè)獨(dú)立的關(guān)系模式，也可以與n端對(duì)應(yīng)的關(guān)系模式合并。①轉(zhuǎn)換為一個(gè)獨(dú)立的關(guān)系模式關(guān)系的屬性：與該聯(lián)系相連的各實(shí)體的碼以及聯(lián)系本身的屬性關(guān)系的候選碼：n端實(shí)體的碼班級(jí)成員學(xué)生1n1:n聯(lián)系轉(zhuǎn)換成如下關(guān)系模式：班級(jí)(班號(hào),系別,班主任,入學(xué)時(shí)間)學(xué)生(學(xué)號(hào),姓名,性別,年齡)成員(學(xué)號(hào),班號(hào))2.4邏輯結(jié)構(gòu)設(shè)計(jì)②與n端對(duì)應(yīng)的關(guān)系模式合并-合并后關(guān)系的屬性：在n端關(guān)系中加入1端關(guān)系的碼和聯(lián)系本身的屬性-合并后關(guān)系的碼：不變轉(zhuǎn)換方法-聯(lián)系的轉(zhuǎn)換(1:N)班級(jí)成員學(xué)生1n1:n聯(lián)系轉(zhuǎn)換成如下關(guān)系模式：班級(jí)(班號(hào),系別,班主任,入學(xué)時(shí)間)學(xué)生(學(xué)號(hào),姓名,性別,年齡,班號(hào))

注：實(shí)際使用中通常采用這種方法以減少關(guān)系模式，因?yàn)槎嘁粋€(gè)關(guān)系模式就意味著查詢過(guò)程中要進(jìn)行連接運(yùn)算，而降低查詢的效率。2.4邏輯結(jié)構(gòu)設(shè)計(jì)3)一個(gè)m:n聯(lián)系轉(zhuǎn)換為一個(gè)關(guān)系模式。關(guān)系的屬性：與該聯(lián)系相連的各實(shí)體的碼以及聯(lián)系本身的屬性關(guān)系的碼：至少包含各實(shí)體碼的組合（聯(lián)系的屬性是否包含在該關(guān)系的碼中需要根據(jù)具體語(yǔ)義判斷）m:n聯(lián)系課程選修學(xué)生mn成績(jī)選修日期轉(zhuǎn)換成如下關(guān)系模式：課程(課程號(hào),課程名,學(xué)分,先修課號(hào))學(xué)生(學(xué)號(hào),姓名,性別,年齡)選修(學(xué)號(hào),課程號(hào),成績(jī))若一個(gè)學(xué)生可以多次選修一門課程，則轉(zhuǎn)換成如下關(guān)系模式：選修(學(xué)號(hào),課程號(hào)，選修日期,成績(jī))2.4邏輯結(jié)構(gòu)設(shè)計(jì)4)三個(gè)或三個(gè)以上實(shí)體間的多元聯(lián)系轉(zhuǎn)換為關(guān)系模式。關(guān)系的屬性：與該多元聯(lián)系相連的各實(shí)體的碼以及聯(lián)系本身的屬性關(guān)系的碼：至少包含各實(shí)體碼的組合（聯(lián)系的屬性是否包含在該關(guān)系的碼中需要根據(jù)具體語(yǔ)義判斷）轉(zhuǎn)換方法-聯(lián)系的轉(zhuǎn)換(三個(gè)實(shí)體以上)項(xiàng)目供應(yīng)商零件供應(yīng)供應(yīng)量轉(zhuǎn)換成如下關(guān)系模式：供應(yīng)商(供應(yīng)商代號(hào),供應(yīng)商名稱,供應(yīng)商狀態(tài),供應(yīng)商所在城市)項(xiàng)目(項(xiàng)目代碼,項(xiàng)目名,項(xiàng)目所在地)零件(零件代碼,零件名,顏色,重量)供應(yīng)(供應(yīng)商代號(hào),項(xiàng)目代碼,零件代碼,供應(yīng)量)2.4邏輯結(jié)構(gòu)設(shè)計(jì)——邏輯模式優(yōu)化數(shù)據(jù)模型的優(yōu)化優(yōu)化的原因：E-R圖轉(zhuǎn)換之后得到的一組關(guān)系模式未必是最“好”的關(guān)系模式的優(yōu)化應(yīng)該從以下幾方面實(shí)施：(1)消除冗余函數(shù)依賴，關(guān)系模式規(guī)范化計(jì)算Fm,分解關(guān)系模式使其達(dá)到3NF或BCNF，對(duì)于存在MVD的關(guān)系模式應(yīng)達(dá)到4NF。(2)對(duì)關(guān)系模式進(jìn)行必要的合并對(duì)具有關(guān)聯(lián)的關(guān)系模式進(jìn)行合并，通常這類關(guān)系模式會(huì)經(jīng)常被查詢而頻繁地進(jìn)行連接運(yùn)算而降低查詢的效率(3)進(jìn)行合理的分解(不是為了達(dá)到更高范式)

主要是為了提高數(shù)據(jù)操作的效率和存儲(chǔ)空間的利用率2.4邏輯結(jié)構(gòu)設(shè)計(jì)——邏輯模式優(yōu)化數(shù)據(jù)模型的優(yōu)化-兩種常用的分解方法水平分解將元組分解成組（80/20原則，提高并發(fā)事務(wù)的處理）垂直分解

--將屬性分解成組（經(jīng)常一起使用的分解為一組）

--垂直分解注意事項(xiàng)：①應(yīng)從所有事務(wù)執(zhí)行的總效率出發(fā)考慮分解的必要性；②垂直分解必須不損失關(guān)系模式語(yǔ)義(保持無(wú)損連接性和

保持函數(shù)依賴)。2.5物理結(jié)構(gòu)設(shè)計(jì)？物理設(shè)計(jì)的任務(wù)給一個(gè)給定的數(shù)據(jù)模型選取一個(gè)合適的物理結(jié)構(gòu)，包括：存儲(chǔ)結(jié)構(gòu)存取方法？物理設(shè)計(jì)的目標(biāo)數(shù)據(jù)庫(kù)上運(yùn)行的各種事務(wù)響應(yīng)時(shí)間小存儲(chǔ)空間利用率高事務(wù)吞吐率大2.5物理結(jié)構(gòu)設(shè)計(jì)(1)詳細(xì)分析要運(yùn)行的事務(wù)，獲取各種設(shè)計(jì)所需參數(shù)(2)了解RDBMS的內(nèi)部特征：主要是存取方法和存儲(chǔ)結(jié)構(gòu)？物理設(shè)計(jì)的方法(1)確定數(shù)據(jù)庫(kù)的物理結(jié)構(gòu)(2)對(duì)物理結(jié)構(gòu)進(jìn)行評(píng)價(jià)（時(shí)間和空間）(3)滿足要求則進(jìn)行物理實(shí)施階段，否則重新設(shè)計(jì)？物理設(shè)計(jì)的步驟2.5物理結(jié)構(gòu)設(shè)計(jì)索引存取1.哪些屬性列建立索引？選擇索引的啟發(fā)式規(guī)則：如果一個(gè)(或一組)屬性經(jīng)常在查詢條件中出現(xiàn)，則考慮在這個(gè)(或這組)屬性上建立索引(或組合索引)。如果一個(gè)屬性經(jīng)常作為最大值和最小值等聚集函數(shù)的參數(shù)，則考慮在這個(gè)屬性上建立索引。如果一個(gè)(或一組)屬性經(jīng)常在連接操作的連接條件中出現(xiàn)，則考慮在這個(gè)(或這組)屬性上建立索引。關(guān)系上定義的索引數(shù)過(guò)多會(huì)帶來(lái)較多的額外開(kāi)銷維護(hù)索引的開(kāi)銷查找索引的開(kāi)銷還需考慮：數(shù)據(jù)分布2.6數(shù)據(jù)庫(kù)的實(shí)施用DDL定義數(shù)據(jù)庫(kù)結(jié)構(gòu)組織數(shù)據(jù)入庫(kù):費(fèi)時(shí)費(fèi)力編制與調(diào)試應(yīng)用程序數(shù)據(jù)庫(kù)試運(yùn)行數(shù)據(jù)庫(kù)運(yùn)行維護(hù)（巡檢與修復(fù)）數(shù)據(jù)庫(kù)實(shí)施的工作內(nèi)容2.7數(shù)據(jù)庫(kù)的運(yùn)行與維護(hù)數(shù)據(jù)庫(kù)運(yùn)行維護(hù)日常巡檢數(shù)據(jù)庫(kù)轉(zhuǎn)儲(chǔ)與備份安全性與完整性控制性能監(jiān)控、分析與改進(jìn)監(jiān)控參數(shù)系統(tǒng)參數(shù)合作QQ：243001978第三章

基于MySQL的云數(shù)據(jù)庫(kù)環(huán)境構(gòu)建Database西北工業(yè)大學(xué)NorthwesternPolytechnicalUniversity數(shù)據(jù)庫(kù)目錄3.1

實(shí)戰(zhàn)目標(biāo)與準(zhǔn)備3.2

云數(shù)據(jù)庫(kù)RDSforMySQL環(huán)境部署3.3

客戶端環(huán)境配置3.4

多用戶訪問(wèn)同一個(gè)云數(shù)據(jù)庫(kù)實(shí)例的環(huán)境構(gòu)建云數(shù)據(jù)庫(kù)RDSforMySQL環(huán)境部署云數(shù)據(jù)庫(kù)購(gòu)買云數(shù)據(jù)庫(kù)RDSforMySQL環(huán)境部署云數(shù)據(jù)庫(kù)RDSforMySQL環(huán)境部署彈性公網(wǎng)IP購(gòu)買與綁定云數(shù)據(jù)庫(kù)RDSforMySQL環(huán)境部署通過(guò)mysqlworkbench客戶端連接遠(yuǎn)程華為云數(shù)據(jù)庫(kù)華為云賬戶添加子用戶合作QQ：243001978第四章

數(shù)據(jù)庫(kù)的管理Database西北工業(yè)大學(xué)NorthwesternPolytechnicalUniversity數(shù)據(jù)庫(kù)目錄4.1

實(shí)戰(zhàn)目標(biāo)與準(zhǔn)備4.2基于MYSQL的云數(shù)據(jù)庫(kù)架構(gòu)4.3

示例數(shù)據(jù)庫(kù)4.4數(shù)據(jù)庫(kù)的創(chuàng)建與修改4.5數(shù)據(jù)庫(kù)的查看4.6數(shù)據(jù)庫(kù)的刪除4.7數(shù)據(jù)庫(kù)的備份與還原4.8數(shù)據(jù)遷移服務(wù)4.1實(shí)戰(zhàn)目標(biāo)與準(zhǔn)備實(shí)戰(zhàn)目標(biāo)本章目標(biāo)是通過(guò)圖形用戶界面、Web界面以及命令行等操作模式，學(xué)習(xí)對(duì)數(shù)據(jù)庫(kù)的管理，了解數(shù)據(jù)庫(kù)管理和維護(hù)中的需求和對(duì)應(yīng)處理方式，掌握數(shù)據(jù)庫(kù)管理和遷移的基本方法。掌握創(chuàng)建、修改和刪除數(shù)據(jù)庫(kù)的方法。掌握數(shù)據(jù)庫(kù)備份和還原的方法。了解數(shù)據(jù)庫(kù)的數(shù)據(jù)遷移服務(wù)。4.2基于MYSQL的云數(shù)據(jù)庫(kù)架構(gòu)AmazonAurora云數(shù)據(jù)庫(kù)

架構(gòu)圖（引自參考文獻(xiàn)[1]）4.3示例數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù)SPJ_MNG該數(shù)據(jù)庫(kù)用于管理若干個(gè)零件生產(chǎn)商以及相應(yīng)的產(chǎn)品信息。其中包含四張表：供應(yīng)商表S，零件表P，工程項(xiàng)目表J，

供應(yīng)情況表SPJ。

供應(yīng)商表S(SNO,SNAME,STATUS,CITY)

零件表P(PNO,PNAME,COLOR,WEIGHT)

工程項(xiàng)目表J(JNO,JNAME,CITY)

供應(yīng)情況表SPJ(SNO,PNO,JNO,QTY)4.3示例數(shù)據(jù)庫(kù)2.數(shù)據(jù)庫(kù)Student該數(shù)據(jù)庫(kù)用于管理學(xué)生、課程及選課情況的基本信息，包含基本表S，C，SC。

S(SNO,SNAME,SGENDER,SBIRTH,SDEPT,SAGE)

C(CNO,CNAME,CPNO,CREDIT)

SC(SNO,CNO,GRADE)4.3示例數(shù)據(jù)庫(kù)3.數(shù)據(jù)庫(kù)University

該數(shù)據(jù)庫(kù)屬于student庫(kù)的擴(kuò)展版，為方便區(qū)分兩個(gè)庫(kù)，university中的表名和表中的屬性和外鍵關(guān)系做了一定的調(diào)整，該庫(kù)用于管理學(xué)生、院系、教師、課程及選課情況等的基本信息。Students（SNO,SNAME,SGENDER,SBIRTH,SDEPT,SAGE）Courses（CNO,CNAME,CPNO,CREDIT）Depts（DNO,DNAME,DUILDING,DEAN,TEL）Instructors（INO,INAME,DNO,SALARY）Teaches（TNO,CNO,SECNO,SEMESTER,YEAR）Takes（SNO,CNO,SECNO,SEMESTER,YEAR,GRADE）Section（CNO,SECNO,SEMESTER,YEAR,BUILDING,ROOMNO）4.4數(shù)據(jù)庫(kù)的創(chuàng)建與修改通過(guò)華為云WEB界面創(chuàng)建數(shù)據(jù)庫(kù)4.4數(shù)據(jù)庫(kù)的創(chuàng)建與修改通過(guò)GUI應(yīng)用程序創(chuàng)建數(shù)據(jù)庫(kù)4.4數(shù)據(jù)庫(kù)的創(chuàng)建與修改通過(guò)命令行創(chuàng)建數(shù)據(jù)庫(kù)4.5數(shù)據(jù)庫(kù)的查看4.6數(shù)據(jù)庫(kù)的刪除4.7數(shù)據(jù)庫(kù)的備份與還原1.數(shù)據(jù)庫(kù)邏輯備份操作4.7數(shù)據(jù)庫(kù)的備份與還原2.數(shù)據(jù)庫(kù)還原操作4.7數(shù)據(jù)庫(kù)的備份與還原3.物理備份2.數(shù)據(jù)庫(kù)命令行備份還原-mysqldump-mysql-hlocalhost-uroot-pstudent<d:\student.sql-LOADDATAINFILE'C:/ProgramData/MySQL/MySQLServer8.0/Uploads/s.txt'INTOTABLEsFIELDSTERMINATEDBY',';4.8數(shù)據(jù)遷移服務(wù)異構(gòu)數(shù)據(jù)庫(kù)之間或者相同數(shù)據(jù)庫(kù)不同版本之間的數(shù)據(jù)遷移或者數(shù)據(jù)同步工具PDI（kettle）

SSMADataX...合作QQ：243001978第四章

數(shù)據(jù)庫(kù)的管理Database西北工業(yè)大學(xué)NorthwesternPolytechnicalUniversity數(shù)據(jù)庫(kù)目錄5.1

實(shí)戰(zhàn)目標(biāo)與準(zhǔn)備5.2MySQL的存儲(chǔ)引擎5.3

表的創(chuàng)建與管理5.4表的完整性約束5.5表的索引管理5.6表的安全性控制5.7視圖的創(chuàng)建與管理5.1實(shí)戰(zhàn)目標(biāo)與準(zhǔn)備實(shí)戰(zhàn)目標(biāo)本章目標(biāo)是掌握基于MySQL的云數(shù)據(jù)庫(kù)環(huán)境中基本表和視圖的創(chuàng)建與管理等的基本操作方法，具體以華為云數(shù)據(jù)庫(kù)MySQL為例展開(kāi)介紹。熟練掌握數(shù)據(jù)庫(kù)和基本表的創(chuàng)建、管理方法。熟練掌握SQL語(yǔ)句設(shè)置完整性約束、安全性的方法。熟練掌握SQL語(yǔ)句進(jìn)行索引管理的方法。熟練掌握SQL語(yǔ)句創(chuàng)建、插入、修改和刪除視圖的方法5.2MySQL的存儲(chǔ)引擎插件式的存儲(chǔ)引擎5.3表的創(chuàng)建與管理5.3表的創(chuàng)建與管理SQL語(yǔ)句CREATE/DROPTABLEALTERTABLE語(yǔ)法格式：CREATETABLE<表名>(<列名><數(shù)據(jù)類型>[完整性約束]

[，<列名><數(shù)據(jù)類型>[完整性約束]]…

[,<表級(jí)完整性約束>]);

<表名>：所要定義的基本表的名字<列名>：組成該表的各個(gè)屬性（列）<列級(jí)完整性約束條件>：涉及相應(yīng)屬性列的完整性約束條件<表級(jí)完整性約束條件>：涉及一個(gè)或多個(gè)屬性列的完整性約束條件5.3表的創(chuàng)建與管理[例]建立一個(gè)“學(xué)生”表Student，它由學(xué)號(hào)Sno、姓名Sname、性別Sgender、出生日期Sbirthdate、所在系Smajor五個(gè)屬性構(gòu)成。其中學(xué)號(hào)不能為空，并且姓名取值也唯一。

CREATETABLEStudent(SnoCHAR(8)NOTNULL,SnameCHAR(20)unique,SgenderCHAR(6),Sbirthdate

Date,SmajorCHAR(40),

PRIMARYKEY(Sno))5.4表的完整性約束5.4表的完整性約束在CREATETABLE中用FOREIGNKEY短語(yǔ)定義哪些列為外碼用REFERENCES短語(yǔ)指明這些外碼參照哪些表的主碼［例］定義SC中的參照完整性

CREATETABLESC(SnoCHAR(8)NOTNULL，

CnoCHAR(5)NOTNULL，

GradeSMALLINT，

SemesterCHAR(5),TeachingclassCHAR(8),

PRIMARYKEY(Sno，Cno)，/*在表級(jí)定義實(shí)體完整性*/

FOREIGNKEY(Sno)REFERENCESStudent(Sno)，

/*在表級(jí)定義參照完整性*/

FOREIGNKEY(Cno)REFERENCESCourse(Cno)

/*在表級(jí)定義參照完整性*/);5.4表的完整性約束CONSTRAINT約束定義［例］建立學(xué)生登記表Student，要求學(xué)號(hào)在10000000~29999999之間，姓名不能取空值，出生在1980年以后，性別只能是“男”或“女”。

CREATETABLEStudent(SnoCHAR(8)

CONSTRAINTC1CHECK(SnoBETWEEN‘10000000’AND’29999999’)，

SnameCHAR(20)

CONSTRAINTC2NOTNULL,

SbirthdateDATE

CONSTRAINTC3CHECK(Sbirthdate>’1980-1-1’)，

SgenderCHAR(2)CONSTRAINTC4CHECK(SgenderIN('男'，'女'))，

CONSTRAINTStudentKeyPRIMARYKEY(Sno))；在Student表上建立了5個(gè)約束條件，包括主碼約束（命名為StudentKey）以及C1、C2、C3、C4四個(gè)列級(jí)約束。mysql中替換成CHECK(snameisNOTNULL),5.4表的完整性約束CONSTRAINT約束修改［例］修改表Student中的約束條件，要求學(xué)號(hào)改為在900000~999999之間，出生日期改為1985年之后?？梢韵葎h除原來(lái)的約束條件，再增加新的約束條件

ALTERTABLEStudent

DROPCONSTRAINTC1;ALTERTABLEStudentADDCONSTRAINTC1CHECK(SnoBETWEEN‘900000’AND‘999999’);

ALTERTABLEStudent

DROPCONSTRAINTC3;

ALTERTABLEStudentADDCONSTRAINTC3CHECK(Sbirthdate>‘1985-1-1’)；5.5表的索引管理無(wú)索引的查詢：順序依次遍歷表中的每行(全表掃描)索引：加速數(shù)據(jù)查詢的輔助數(shù)據(jù)結(jié)構(gòu)5.5表的索引管理索引表數(shù)據(jù)表音序查字法，部首查字法5.5表的索引管理索引的類型功能邏輯：

-主鍵索引(唯一且非空，每張表1個(gè)，聚簇索引)-普通索引

-唯一索引

-全文索引物理實(shí)現(xiàn)方式：

-聚簇索引

-非聚簇索引(二級(jí)索引或者輔助索引，需要回表)字段個(gè)數(shù)：

-單一索引

-聯(lián)合索引:最左原則，index:(a,b,c）,則支持a;a,b;

a,b,c查詢5.5表的索引管理5.5表的索引管理索引的定義CREATEINDEX

語(yǔ)法格式：例子：

CREATEINDEXidx_nameONs

(sname);5.5表的索引管理索引的刪除DROPINDEX

語(yǔ)法格式：DROPINDEX<索引名>ON<表名>[例]刪除Student表的Stusname索引。DROPINDEXStusnameONStudent

或者ALTERTABLEstudentdropindexStusname;SQL標(biāo)準(zhǔn)中沒(méi)有定義對(duì)索引的修改功能，而采用刪除后重新定義索引的方式實(shí)現(xiàn)。5.5表的索引管理Explain：查看查詢的執(zhí)行計(jì)劃（性能分析之必備!）若期望強(qiáng)制指定查詢時(shí)使用某個(gè)索引:selectsno,snamefromuserinfouseindex(idx_name)whereuser_id>005.5表的索引管理列名描述idselect查詢的序列號(hào)，表示查詢中執(zhí)行select子句或表的順序select_type查詢類型table表名partitions匹配的分區(qū)信息type針對(duì)單表的訪問(wèn)方法possible_keys可能用到的索引key實(shí)際使用的索引列名描述key_len實(shí)際使用的索引長(zhǎng)度ref當(dāng)使用索引等值查詢時(shí)，與索引列進(jìn)行等值匹配的對(duì)象信息rows預(yù)估的需要讀取的記錄數(shù)filtered針對(duì)預(yù)估的需要讀取的記錄，經(jīng)過(guò)搜索條件過(guò)濾后剩余記錄條數(shù)的百分比Extra一些額外的信息5.5表的索引管理select_type：SIMPLEPRIMARYUNIONDEPENDENTUNIONUNIONRESULTSUBQUERYDEPENDENTSUBQUERYDERIVEDtype：systemconsteq_refreffulltextref_or_nullindex_mergeunique_subqueryindex_subqueryrangeindexall（全表掃描）通常按照?qǐng)?zhí)行速度：system>const>eq_ref>ref>range>index>all/doc/refman/8.0ut.html5.5表的索引管理創(chuàng)建一個(gè)使用memory存儲(chǔ)引擎的表（對(duì)比Hash索引與B樹(shù)索引）createtablememt1(id1intnotnull,c1intdefaultnull,c2intdefaultnull,primarykey(id1))engine=memorycreateindexindex_nameusinghash/btreeontable_name(...)查看索引：showkeysfromtablename;5.6表的安全性控制5.6表的安全性控制授權(quán)GRANT<權(quán)限>[，權(quán)限]…

[ON<對(duì)象類型><對(duì)象名>]TO<用戶>[，<用戶>]…

[WITHGRANTOPTION]1.DBA擁有數(shù)據(jù)庫(kù)操作的所有權(quán)限，他可以將權(quán)限賦予其他用戶。2.建立數(shù)據(jù)庫(kù)對(duì)象用戶稱為該對(duì)象的屬主(OWNER)，他擁有該對(duì)象的所有操作權(quán)限。3.接受權(quán)限的用戶可以是一個(gè)或多個(gè)具體用戶，也可以是全體用戶(PUBLIC)。4.WITHGRANTOPTION：決定是否有傳播權(quán)限的權(quán)利不允許循環(huán)傳播5.6表的安全性控制收回權(quán)限REVOKE語(yǔ)句的一般格式為：

REVOKE<權(quán)限>[,<權(quán)限>]...[ON<對(duì)象類型><對(duì)象名>]

FROM<用戶>[,<用戶>]...

[CASCADE|RESTRICT][例]把用戶U4修改學(xué)生學(xué)號(hào)的權(quán)限收回。

REVOKEUPDATE(Sno)ONTABLEStudentFROMU4；5.6表的安全性控制MySQL中的權(quán)限相關(guān)概念用戶名（user）：訪問(wèn)數(shù)據(jù)庫(kù)用戶名角色（role）：用戶權(quán)限的組合（主體集合）相關(guān)SQL語(yǔ)句：createuseru1identifiedby'123';

createroler1;grantselectonstudent.*to'r1'@'%';grant'r1'@'%'to'u1'@'%';flushprivileges;查看所有用戶:selectuser,hostfrommysql.user;查看某個(gè)用戶權(quán)限：showgrantsfor'user1'@'localhost'1.MySQL設(shè)置權(quán)限后需要刷新：flushprivileges;2.ROLE需要激活后權(quán)限才生效：SETDEFAULTROLE命令或者SETglobalactivate_all_roles_on_login=ON5.6表的安全性控制在MySQL中的應(yīng)用：

例：對(duì)于表s：授予角色r2具有所有權(quán)限，u1具有查詢、插入和修改學(xué)生系別的權(quán)限，u1的查詢權(quán)限可以傳播。給角色r1授予對(duì)所有表的查詢權(quán)限 grantallonstor2;grantupdateonsto'u1'@'%'withgrantoption;grantupdateonsto'u1'@'localhost'withgrantoption;grantupdateonsto'u1'@'08'withgrantoption;grantinsert,update(sdept)onstou1;grantselecton*.*tor1;例：對(duì)于表s：收回用戶r2的所有權(quán)限，收回u1的查詢權(quán)限。

revokeallonsfromr2revokeselectonstudentfromu1@%5.7視圖的創(chuàng)建與管理5.7視圖的創(chuàng)建與管理視圖定義格式：CREATEVIEW<視圖名>[(<列名>[,<列名>]…)]

AS<子查詢>[WITHCHECKOPTION]DBMS執(zhí)行CREATEVIEW語(yǔ)句時(shí)只是把視圖的定義存入數(shù)據(jù)字典，并不執(zhí)行其中的SELECT語(yǔ)句WITHCHECKOPTION

通過(guò)視圖進(jìn)行增刪改操作時(shí)，不得破壞視圖定義中的謂詞條件

（即子查詢中的條件表達(dá)式）LOCAL：只在本視圖檢查新行。CASCADED：根據(jù)該視圖和所有底層基視圖上的條件檢查新行。5.7視圖的創(chuàng)建與管理例：建立IS專業(yè)學(xué)生的視圖，并要求透過(guò)該視圖進(jìn)行的更新操作只涉及IS專業(yè)的學(xué)生。

CREATEVIEWIS_StudentASSELECTSno，Sname，SbirthdateFROMStudentWHERESmajor='IS'

WITHCHECKOPTION從單個(gè)基本表導(dǎo)出只是去掉了基本表的某些行和某些列,但是保留了碼，這類視圖稱為行列子集視圖。5.7視圖的創(chuàng)建與管理從用戶角度：查詢視圖與查詢基本表相同DBMS實(shí)現(xiàn)視圖查詢的方法

視圖消解法（ViewResolution）進(jìn)行有效性檢查，檢查查詢的表、視圖等是否存在。如果存在，則從數(shù)據(jù)字典中取出視圖的定義把視圖定義中的子查詢與用戶的查詢結(jié)合起來(lái)，轉(zhuǎn)換成等價(jià)的對(duì)基本表的查詢執(zhí)行修正后的查詢。合作QQ：243001978第四章

數(shù)據(jù)庫(kù)的管理Database西北工業(yè)大學(xué)NorthwesternPolytechnicalUniversity數(shù)據(jù)庫(kù)目錄6.1

實(shí)戰(zhàn)目標(biāo)與準(zhǔn)備6.2基本表數(shù)據(jù)插入6.3

基本表數(shù)據(jù)修改6.4基本表數(shù)據(jù)刪除6.5基本表數(shù)據(jù)查詢6.6視圖數(shù)據(jù)的操作6.7數(shù)據(jù)查詢性能分析6.8數(shù)據(jù)查詢綜合實(shí)戰(zhàn)6.1實(shí)戰(zhàn)目標(biāo)與準(zhǔn)備實(shí)戰(zhàn)目標(biāo)本章的目標(biāo)是在掌握了數(shù)據(jù)庫(kù)和基本表的創(chuàng)建和管理的基礎(chǔ)上，學(xué)習(xí)如何通過(guò)一定的工具或SQL命令來(lái)對(duì)基本表中的數(shù)據(jù)進(jìn)行增、刪、改、查等基本操作。熟練掌握對(duì)基本表進(jìn)行數(shù)據(jù)插入、修改和刪除的SQL語(yǔ)句。熟練掌握數(shù)據(jù)查詢的SQL語(yǔ)句（簡(jiǎn)單查詢和復(fù)合查詢）。掌握對(duì)視圖的數(shù)據(jù)操作，并了解其與基本表數(shù)據(jù)操作的關(guān)系。掌握SQL語(yǔ)句查詢性能分析的基本知識(shí)。了解TPC-C基準(zhǔn)數(shù)據(jù)庫(kù)6.2基本表數(shù)據(jù)插入6.2基本表數(shù)據(jù)插入插入單個(gè)元組插入子查詢結(jié)果(可以是多個(gè)元組)INSERTINTO<表名>[(<屬性列1>[，<屬性列2>…)]VALUES(<常量1>[，<常量2>]…)功能:將新元組插入指定表中。INSERTINTO<表名>[(<屬性列1>[，<屬性列2>…)]子查詢功能:將子查詢結(jié)果插入指定表中6.3基本表數(shù)據(jù)修改語(yǔ)句格式

UPDATE<表名>

SET<列名>=<表達(dá)式>[，<列名>=<表達(dá)式>]…[WHERE<條件>]功能：修改指定表中滿足WHERE子句條件的元組SET子句－－指定修改方式，要修改的列和修改后取值WHERE子句指定要修改的元組缺省表示要修改表中的所有元組注：只能對(duì)一個(gè)表進(jìn)行修改！6.4基本表數(shù)據(jù)刪除DELETEFROM<表名>[WHERE<條件>]功能刪除指定表中滿足WHERE子句條件的元組WHERE子句指定要?jiǎng)h除的元組缺省表示要修改表中的所有元組例：刪除學(xué)號(hào)為1004的學(xué)生記錄DELETEFROMstudentWHEREsno='1004'6.5基本表數(shù)據(jù)查詢Select[ALL|DISTINCT]

<輸出屬性列表>From<一個(gè)或多個(gè)數(shù)據(jù)庫(kù)表或視圖>[Where<查詢條件>][GroupBy<分組條件>[HAVING<條件表達(dá)式>]][OrderBy<結(jié)果排序>[ASC|DESC]數(shù)據(jù)查詢語(yǔ)言單表查詢連接查詢、嵌套查詢、集合查詢基于派生表的查詢MySQL:limitSQLServer：top6.5基本表數(shù)據(jù)查詢6.6視圖數(shù)據(jù)操作針對(duì)視圖的查詢與基本表的操作相同例在IS學(xué)生的視圖中找出年齡小于20歲的學(xué)生。

IS專業(yè)學(xué)生的視圖定義(視圖定義例1)：

CREATEVIEWIS_StudentASSELECTSno,Sname,SbirhdateFROMStudentWHERESmajor='IS';視圖消解法：根據(jù)視圖定義將對(duì)視圖的查詢轉(zhuǎn)換為對(duì)基本表的查詢，轉(zhuǎn)換后的查詢語(yǔ)句如下：

SELECTSno,SbirthdateFROMStudentWHERESmajor='IS'

AND

year(curdate())-year(Sbirthdate）<20；查詢語(yǔ)句：SELECTSno,SbirthdateFROMIS_StudentWHEREyear(curdate())-year(Sbirthdate）<20;6.7數(shù)據(jù)查詢性能分析針對(duì)Student數(shù)據(jù)庫(kù)用至少三種不同的SQL語(yǔ)句進(jìn)行查詢：查詢選修了課程名為“數(shù)據(jù)庫(kù)原理”的學(xué)生學(xué)號(hào)和姓名，然后設(shè)計(jì)實(shí)驗(yàn)，用數(shù)據(jù)比較分析三種查詢的效率，并分析原因。

查詢計(jì)劃分析實(shí)際運(yùn)行對(duì)比注意：需要達(dá)到一定的數(shù)據(jù)量測(cè)試運(yùn)行時(shí)間可能才能體現(xiàn)出差異。6.8數(shù)據(jù)查詢綜合實(shí)戰(zhàn)基于以上TPC-C數(shù)據(jù)庫(kù)，按照要求設(shè)計(jì)查詢并用數(shù)據(jù)進(jìn)行驗(yàn)證。6.8數(shù)據(jù)查詢綜合實(shí)戰(zhàn)—TPCH利用dbgen生成測(cè)試數(shù)據(jù)6.8數(shù)據(jù)查詢綜合實(shí)戰(zhàn)—TPCH6.8數(shù)據(jù)查詢綜合實(shí)戰(zhàn)—TPCH合作QQ：243001978第七章

數(shù)據(jù)庫(kù)服務(wù)端編程Database西北工業(yè)大學(xué)NorthwesternPolytechnicalUniversity數(shù)據(jù)庫(kù)目錄7.1

實(shí)戰(zhàn)目標(biāo)與準(zhǔn)備7.2變量7.3

函數(shù)7.4存儲(chǔ)過(guò)程7.5SQL控制流程語(yǔ)句7.6游標(biāo)7.7觸發(fā)器7.8預(yù)處理SQL語(yǔ)句7.9銀行場(chǎng)景化綜合實(shí)戰(zhàn)7.1實(shí)戰(zhàn)目標(biāo)與準(zhǔn)備實(shí)戰(zhàn)目標(biāo)數(shù)據(jù)庫(kù)服務(wù)端編程指編寫(xiě)運(yùn)行在數(shù)據(jù)庫(kù)服務(wù)端的程序，具體主要包括服務(wù)端的存儲(chǔ)過(guò)程、自定義函數(shù)、觸發(fā)器等。為了便于描述，本書(shū)將存儲(chǔ)過(guò)程、用戶自定義函數(shù)、觸發(fā)器等不同形式的數(shù)據(jù)庫(kù)服務(wù)端程序統(tǒng)稱為SQL程序。掌握MySQL數(shù)據(jù)庫(kù)服務(wù)端編程的基本知識(shí)，包括編寫(xiě)服務(wù)端程序需要的變量、控制流程語(yǔ)句、游標(biāo)、存儲(chǔ)過(guò)程、自定義函數(shù)、觸發(fā)器、事件等。7.2變量全局變量（global，系統(tǒng)變量，不能自定義，可改值）會(huì)話變量（session，系統(tǒng)變量，不能自定義，可改值）用戶變量（@var，用戶可以自定義）局部變量（參數(shù),declare）

7.2變量MySQL的用戶變量

@var，以“@”開(kāi)頭，可以作用于當(dāng)前整個(gè)連接,但是若當(dāng)前連接斷開(kāi)后,所定義的用戶變量都會(huì)消失?？梢栽诖鎯?chǔ)過(guò)程之間傳遞全局范圍的變量。

賦值：set@count=1;

selectcount(id)into@count

fromitemswhereprice<99;讀?。簊elect@count；7.2變量MySQL的局部變量

注意：

1）支持SQL的數(shù)據(jù)類型

2）給出DEFAULT，該變量進(jìn)入BEGIN塊時(shí)初始化為該值.例：DECLARE語(yǔ)句部分聲明變量,SET進(jìn)行賦值。declarenamesvarchar(10)default'';declareiintdefault0;setnames=concat('test','');字符串拼接函數(shù)：concat7.2變量MySQL變量對(duì)比

7.3函數(shù)——系統(tǒng)內(nèi)置函數(shù)SQL常用的內(nèi)置函數(shù)可以分為：數(shù)學(xué)函數(shù)（如絕對(duì)值函數(shù)等）聚合函數(shù)（如求和、求平均函數(shù)等）字符串函數(shù)（如求字符串長(zhǎng)度、求子串函數(shù)等）日期和時(shí)間函數(shù)（如返回當(dāng)前日期函數(shù)等）格式化函數(shù)（如字符串轉(zhuǎn)IP地址函數(shù)等）控制流函數(shù)（如邏輯判斷函數(shù)等）加密函數(shù)（如使用密鑰對(duì)字符串加密函數(shù)等）系統(tǒng)信息函數(shù)（如返回當(dāng)前數(shù)據(jù)庫(kù)名、服務(wù)器版本函數(shù)等）7.3函數(shù)——用戶自定義函數(shù)delimiter$$createfunction函數(shù)名(參數(shù)類型)returns返回類型Begin

方法體

return數(shù)據(jù)類型；end$$delimiter；select函數(shù)名

7.3函數(shù)——用戶自定義函數(shù)要求：指定年和月時(shí)，編寫(xiě)一個(gè)自定義函數(shù)：求該年該月的平均氣溫。某氣象站有一張表temperature，每天在2點(diǎn)，8點(diǎn)，14點(diǎn)，20點(diǎn)自動(dòng)采集溫度7.3函數(shù)——用戶自定義函數(shù)DELIMITER$CREATEFUNCTIONtemp_avg(myyearint,mymonthint)RETURNSrealBEGINDECLAREtemprealDEFAULT0;SELECT(SUM(T02)+SUM(T08)+SUM(T14)+SUM(T20))/(Count(T02)+Count(T08)+Count(T14)+Count(T20))INTOtempFROMTemperatureWHEREYear=myyearANDMonth=mymonth;returntemp;END$某氣象站有一張表temperature，每天在2點(diǎn)，8點(diǎn)，14點(diǎn)，20點(diǎn)自動(dòng)采集溫度7.4存儲(chǔ)過(guò)程delimiter$$createprocedure存儲(chǔ)過(guò)程名(參數(shù))Begin

方法體end$$delimiter；call存儲(chǔ)過(guò)程名

7.4存儲(chǔ)過(guò)程MYSQL存儲(chǔ)過(guò)程/存儲(chǔ)函數(shù)完整語(yǔ)法7.4存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程示例7.4存儲(chǔ)過(guò)程MySQL的參數(shù)傳遞存儲(chǔ)過(guò)程：IN，OUT，INOUT類型函數(shù)：所有參數(shù)為IN類型7.5SQL控制流程語(yǔ)句MySQL流程控制：條件分支IFsearch_conditionTHENstatement_list[ELSEIFsearch_conditionTHENstatement_list]...[ELSEstatement_list]ENDIFCASEcase_value

WHENwhen_valueTHENstatement_list[WHENwhen_valueTHENstatement_list]...[ELSEstatement_list]ENDCASECASE

WHENsearch_conditionTHENstatement_list[WHENsearch_conditionTHENstatement_list]...[ELSEstatement_list]ENDCASEIFCASEWHEN7.5SQL控制流程語(yǔ)句MySQL流程控制：循環(huán)REPEAT[begin_label:]REPEATstatement_listUNTILsearch_conditionENDREPEAT[end_label]mysql>delimiter//mysql>CREATEPROCEDUREdorepeat(p1INT)BEGINSET@x=0;

REPEATSET@x=@x+1;

UNTIL@x>p1ENDREPEAT;END//QueryOK,0rowsaffected(0.00sec)mysql>CALLdorepeat(1000)//QueryOK,0rowsaffected(0.00sec)7.5SQL控制流程語(yǔ)句MySQL流程控制：循環(huán)WHILE[begin_label:]WHILEsearch_conditionDOstatement_listENDWHILE[end_label]CREATEPROCEDURE

dowhile()BEGINDECLAREv1INTDEFAULT5;

WHILEv1>0DO...SETv1=v1-1;

ENDWHILE;END;7.5SQL控制流程語(yǔ)句MySQL流程控制：循環(huán)LOOP[begin_label:]LOOPstatement_listENDLOOP[end_label]CREATEPROCEDUREdoiterate(p1INT)BEGIN

label1:LOOPSETp1=p1+1;IFp1<10THEN

ITERATElabel1;ENDIF;

LEAVElabel1;

ENDLOOP

label1;SET@x=p1;END;7.6游標(biāo)游標(biāo)

若查詢SQL只返回一條記錄可以放入一個(gè)變量，當(dāng)返回

多條記錄時(shí)，需使用游標(biāo)逐行處理結(jié)果集。聲明游標(biāo)(DECLARE)打開(kāi)游標(biāo)(OPEN)使用游標(biāo)讀取數(shù)據(jù)(FETCH)關(guān)閉游標(biāo)(CLOSE)7.6游標(biāo)delimiter$$createprocedureget_cs_s()begin

declareout_namesvarchar(100)default'';declaretmpvarchar(100)default'';declarep_snovarchar(5)default'';declareP_snamevarchar(10)default'';declaredonebooleandefault0;

declarecursor_namecursorforselectsno,snamefromswheresdept='CS';

declarecontinuehandlerforsqlstate'02000'

setdone=1;

--ER_SP_FETCH_NO_DATA

opencursor_name;

fetchcursor_nameintop_sno,p_sname;

repeat

settmp=concat(p_sno,'_',p_sname);setout_names=concat(out_names,tmp,'');

fetchcursor_nameintop_sno,p_sname;

untildone

endrepeat;

closecursor_name;

selectout_names;end$$delimiter;7.7觸發(fā)器1.觸發(fā)器定義-MySQL7.7觸發(fā)器2.觸發(fā)器的分類（DML）BEFORE觸發(fā)器AFT