第一章

數據庫系統概述Database西北工業大學NorthwesternPolytechnicalUniversity數據庫目錄1.1

數據庫系統概述1.2.

數據建模1.3.MySQL關系數據庫1.4.云數據庫1.5.基于MySQL的云數據庫1.1數據庫系統概述數據(Data)數據庫(DataBase)數據庫管理系統(DBMS)數據庫系統(DBS)描述事物的符號記錄。數據庫中存儲的基本對象。數據(Data)數據庫(DataBase)存放數據的倉庫，長期儲存在計算機內的，有組織的，可共享的數據集合。1.1數據庫系統概述廣義數據庫系統1.1數據庫系統概述常見DBMS1.2.1數據模型數據建模：現實世界中客觀對象的抽象過程DBMS支持的數據模型概念模型認識抽象信息世界機器世界現實世界概念模型數據庫設計人員完成邏輯模型物理模型由DBMS完成概念模型邏輯模型數據庫設計人員完成現實世界1.2.2概念模型與E-R圖概念模型對現實世界的事物符號化的描述，為計算機處理做準備。常用的概念模型是E-R(Entity-Relationship)圖。1.實體（Entity）客觀存在并可相互區別的事物稱為實體。2.屬性（Attribute）實體所具有的某一特性稱為屬性。3.碼（Key）唯一標識實體的屬性集稱為碼。4.域（Domain）屬性的取值范圍稱為該屬性的域。5.實體型(EntityType)用實體名及其屬性名集合來抽象和刻畫同類實體稱為實體型6.實體集(EntitySet)同型實體的集合稱為實體集7.聯系(Relationship)實體內部或者實體之間的聯系1.2.2概念模型與E-R圖概念模型(E-R圖)（P.P.S.Chen陳品山教授,1976提出）1.實體（Entity）2.屬性（Attribute）3.碼（Key）4.域（Domain）5.實體型(EntityType)6.實體集(EntitySet)7.聯系(Relationship)1.2.2概念模型與E-R圖實體型：矩形屬性：橢圓，用無向邊將其與相應的實體連接起來聯系：菱形，用無向邊將其與相應的實體連接起來，同時表明聯系的類型（1:1,1:n或m:n）用E-R圖建立概念模型例:學生實體具有學號、姓名、性別、年齡等屬性,用E-R圖表示學生學號年齡性別姓名1.2.2概念模型與E-R圖聯系(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對1聯系1對多聯系多對多聯系1.2.2概念模型與E-R圖擴展的ER圖復合屬性1.2.2概念模型與E-R圖擴展的ER圖ISA聯系（類的實例,isa;繼承；例如：本科生/研究生和學生）分類屬性：學生的2個子類（有兩個分類屬性值）不相交約束：一個實體不能同時屬于多個類別（×號）若沒有×號，表明是可重疊約束1.2.2概念模型與E-R圖擴展的ER圖基數約束基數約束：min:max:每個實體參與聯系集中的次數

min=1:

強制參與約束，實體集中的每個實體在聯系集中至少一次

min=0:

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

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

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

1)抽象數據+設計局部視圖

2)集成局部視圖，得到全體的概念結構2.3概念結構設計步驟一：抽象數據1)分類2）聚集ismemberof：例如實體型ispartof：例如屬性3）概括issubsetof：例如超/子類實體型本科生研究生學生超類子類學生學號姓名學生張穎王平2.3概念結構設計步驟二：設計局部視圖(1)選擇局部應用

恰當選擇各級數據流圖中的某一層的某一部分業務，準備設計局部E-R圖。通常選擇數據流圖的中間級，這一級實體描述準確，實體間的聯系也比較清晰。(2)設計分E-R圖

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

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

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

--同名異義

--異名同義結構沖突

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

--同一對象在E-R圖A中被抽象為實體,在E-R圖B中又被抽象為屬性；

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

將實體、實體的屬性和實體之間的聯系轉化為關系模式轉換方法–實體型的轉換一個實體型轉換為一個關系模式。

--實體型的屬性轉換為關系的屬性

--實體標識符轉換為關系的碼例：學生實體E-R圖如右所示：學生學號出生日期年級所在系平均成績姓名轉換為如下關系模式：

學生(學號,姓名,出生日期,所在系,年級,平均成績)2.4邏輯結構設計轉換方法-聯系的轉換(1:1)轉換成如下關系模式：班級(班號,系別,班主任,入學時間)班長(學號,姓名,性別,年齡)班級-班長(班號,學號,任期)班級班級-班長班長111:1聯系任期一個1:1

聯系可以轉換為一個獨立的關系模式，也可以與任意一端對應的關系模式合并。①轉換為一個獨立的關系模式關系的屬性：與該聯系相連的各實體的碼以及聯系本身的屬性關系的候選碼：每個實體的碼均是該關系的候選碼2.4邏輯結構設計班級班級-班長班長111:1聯系任期②與某一端對應的關系模式合并合并后關系的屬性：加入另一關系的碼和聯系本身的屬性合并后關系的碼：不變轉換成如下關系模式：班級(班號,系別,班主任,入學時間,

班長學號,班長任期)班長(學號,姓名,性別,年齡)

或班級(班號,系別,班主任,入學時間)班長(學號,姓名,性別,年齡,

班長班號,任期)轉換方法-聯系的轉換(1:1)2.4邏輯結構設計2）一個1:n

聯系可以轉換為一個獨立的關系模式，也可以與n端對應的關系模式合并。①轉換為一個獨立的關系模式關系的屬性：與該聯系相連的各實體的碼以及聯系本身的屬性關系的候選碼：n端實體的碼班級成員學生1n1:n聯系轉換成如下關系模式：班級(班號,系別,班主任,入學時間)學生(學號,姓名,性別,年齡)成員(學號,班號)2.4邏輯結構設計②與n端對應的關系模式合并-合并后關系的屬性：在n端關系中加入1端關系的碼和聯系本身的屬性-合并后關系的碼：不變轉換方法-聯系的轉換(1:N)班級成員學生1n1:n聯系轉換成如下關系模式：班級(班號,系別,班主任,入學時間)學生(學號,姓名,性別,年齡,班號)

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

主要是為了提高數據操作的效率和存儲空間的利用率2.4邏輯結構設計——邏輯模式優化數據模型的優化-兩種常用的分解方法水平分解將元組分解成組（80/20原則，提高并發事務的處理）垂直分解

--將屬性分解成組（經常一起使用的分解為一組）

--垂直分解注意事項：①應從所有事務執行的總效率出發考慮分解的必要性；②垂直分解必須不損失關系模式語義(保持無損連接性和

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

基于MySQL的云數據庫環境構建Database西北工業大學NorthwesternPolytechnicalUniversity數據庫目錄3.1

實戰目標與準備3.2

云數據庫RDSforMySQL環境部署3.3

客戶端環境配置3.4

多用戶訪問同一個云數據庫實例的環境構建云數據庫RDSforMySQL環境部署云數據庫購買云數據庫RDSforMySQL環境部署云數據庫RDSforMySQL環境部署彈性公網IP購買與綁定云數據庫RDSforMySQL環境部署通過mysqlworkbench客戶端連接遠程華為云數據庫華為云賬戶添加子用戶合作QQ：243001978第四章

數據庫的管理Database西北工業大學NorthwesternPolytechnicalUniversity數據庫目錄4.1

實戰目標與準備4.2基于MYSQL的云數據庫架構4.3

示例數據庫4.4數據庫的創建與修改4.5數據庫的查看4.6數據庫的刪除4.7數據庫的備份與還原4.8數據遷移服務4.1實戰目標與準備實戰目標本章目標是通過圖形用戶界面、Web界面以及命令行等操作模式，學習對數據庫的管理，了解數據庫管理和維護中的需求和對應處理方式，掌握數據庫管理和遷移的基本方法。掌握創建、修改和刪除數據庫的方法。掌握數據庫備份和還原的方法。了解數據庫的數據遷移服務。4.2基于MYSQL的云數據庫架構AmazonAurora云數據庫

架構圖（引自參考文獻[1]）4.3示例數據庫數據庫SPJ_MNG該數據庫用于管理若干個零件生產商以及相應的產品信息。其中包含四張表：供應商表S，零件表P，工程項目表J，

供應情況表SPJ。

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

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

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

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

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

C(CNO,CNAME,CPNO,CREDIT)

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

該數據庫屬于student庫的擴展版，為方便區分兩個庫，university中的表名和表中的屬性和外鍵關系做了一定的調整，該庫用于管理學生、院系、教師、課程及選課情況等的基本信息。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數據庫的創建與修改通過華為云WEB界面創建數據庫4.4數據庫的創建與修改通過GUI應用程序創建數據庫4.4數據庫的創建與修改通過命令行創建數據庫4.5數據庫的查看4.6數據庫的刪除4.7數據庫的備份與還原1.數據庫邏輯備份操作4.7數據庫的備份與還原2.數據庫還原操作4.7數據庫的備份與還原3.物理備份2.數據庫命令行備份還原-mysqldump-mysql-hlocalhost-uroot-pstudent<d:\student.sql-LOADDATAINFILE'C:/ProgramData/MySQL/MySQLServer8.0/Uploads/s.txt'INTOTABLEsFIELDSTERMINATEDBY',';4.8數據遷移服務異構數據庫之間或者相同數據庫不同版本之間的數據遷移或者數據同步工具PDI（kettle）

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

數據庫的管理Database西北工業大學NorthwesternPolytechnicalUniversity數據庫目錄5.1

實戰目標與準備5.2MySQL的存儲引擎5.3

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

[，<列名><數據類型>[完整性約束]]…

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

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

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

Date,SmajorCHAR(40),

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

CREATETABLESC(SnoCHAR(8)NOTNULL，

CnoCHAR(5)NOTNULL，

GradeSMALLINT，

SemesterCHAR(5),TeachingclassCHAR(8),

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

FOREIGNKEY(Sno)REFERENCESStudent(Sno)，

/*在表級定義參照完整性*/

FOREIGNKEY(Cno)REFERENCESCourse(Cno)

/*在表級定義參照完整性*/);5.4表的完整性約束CONSTRAINT約束定義［例］建立學生登記表Student，要求學號在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個約束條件，包括主碼約束（命名為StudentKey）以及C1、C2、C3、C4四個列級約束。mysql中替換成CHECK(snameisNOTNULL),5.4表的完整性約束CONSTRAINT約束修改［例］修改表Student中的約束條件，要求學號改為在900000~999999之間，出生日期改為1985年之后。可以先刪除原來的約束條件，再增加新的約束條件

ALTERTABLEStudent

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

ALTERTABLEStudent

DROPCONSTRAINTC3;

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

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

-唯一索引

-全文索引物理實現方式：

-聚簇索引

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

-單一索引

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

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

語法格式：例子：

CREATEINDEXidx_nameONs

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

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

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

[ON<對象類型><對象名>]TO<用戶>[，<用戶>]…

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

REVOKE<權限>[,<權限>]...[ON<對象類型><對象名>]

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

[CASCADE|RESTRICT][例]把用戶U4修改學生學號的權限收回。

REVOKEUPDATE(Sno)ONTABLEStudentFROMU4；5.6表的安全性控制MySQL中的權限相關概念用戶名（user）：訪問數據庫用戶名角色（role）：用戶權限的組合（主體集合）相關SQL語句：createuseru1identifiedby'123';

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

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

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

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

通過視圖進行增刪改操作時，不得破壞視圖定義中的謂詞條件

（即子查詢中的條件表達式）LOCAL：只在本視圖檢查新行。CASCADED：根據該視圖和所有底層基視圖上的條件檢查新行。5.7視圖的創建與管理例：建立IS專業學生的視圖，并要求透過該視圖進行的更新操作只涉及IS專業的學生。

CREATEVIEWIS_StudentASSELECTSno，Sname，SbirthdateFROMStudentWHERESmajor='IS'

WITHCHECKOPTION從單個基本表導出只是去掉了基本表的某些行和某些列,但是保留了碼，這類視圖稱為行列子集視圖。5.7視圖的創建與管理從用戶角度：查詢視圖與查詢基本表相同DBMS實現視圖查詢的方法

視圖消解法（ViewResolution）進行有效性檢查，檢查查詢的表、視圖等是否存在。如果存在，則從數據字典中取出視圖的定義把視圖定義中的子查詢與用戶的查詢結合起來，轉換成等價的對基本表的查詢執行修正后的查詢。合作QQ：243001978第四章

數據庫的管理Database西北工業大學NorthwesternPolytechnicalUniversity數據庫目錄6.1

實戰目標與準備6.2基本表數據插入6.3

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

UPDATE<表名>

SET<列名>=<表達式>[，<列名>=<表達式>]…[WHERE<條件>]功能：修改指定表中滿足WHERE子句條件的元組SET子句－－指定修改方式，要修改的列和修改后取值WHERE子句指定要修改的元組缺省表示要修改表中的所有元組注：只能對一個表進行修改！6.4基本表數據刪除DELETEFROM<表名>[WHERE<條件>]功能刪除指定表中滿足WHERE子句條件的元組WHERE子句指定要刪除的元組缺省表示要修改表中的所有元組例：刪除學號為1004的學生記錄DELETEFROMstudentWHEREsno='1004'6.5基本表數據查詢Select[ALL|DISTINCT]

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

IS專業學生的視圖定義(視圖定義例1)：

CREATEVIEWIS_StudentASSELECTSno,Sname,SbirhdateFROMStudentWHERESmajor='IS';視圖消解法：根據視圖定義將對視圖的查詢轉換為對基本表的查詢，轉換后的查詢語句如下：

SELECTSno,SbirthdateFROMStudentWHERESmajor='IS'

AND

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

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

數據庫服務端編程Database西北工業大學NorthwesternPolytechnicalUniversity數據庫目錄7.1

實戰目標與準備7.2變量7.3

函數7.4存儲過程7.5SQL控制流程語句7.6游標7.7觸發器7.8預處理SQL語句7.9銀行場景化綜合實戰7.1實戰目標與準備實戰目標數據庫服務端編程指編寫運行在數據庫服務端的程序，具體主要包括服務端的存儲過程、自定義函數、觸發器等。為了便于描述，本書將存儲過程、用戶自定義函數、觸發器等不同形式的數據庫服務端程序統稱為SQL程序。掌握MySQL數據庫服務端編程的基本知識，包括編寫服務端程序需要的變量、控制流程語句、游標、存儲過程、自定義函數、觸發器、事件等。7.2變量全局變量（global，系統變量，不能自定義，可改值）會話變量（session，系統變量，不能自定義，可改值）用戶變量（@var，用戶可以自定義）局部變量（參數,declare）

7.2變量MySQL的用戶變量

@var，以“@”開頭，可以作用于當前整個連接,但是若當前連接斷開后,所定義的用戶變量都會消失。可以在存儲過程之間傳遞全局范圍的變量。

賦值：set@count=1;

selectcount(id)into@count

fromitemswhereprice<99;讀取：select@count；7.2變量MySQL的局部變量

注意：

1）支持SQL的數據類型

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

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

方法體

return數據類型；end$$delimiter；select函數名

7.3函數——用戶自定義函數要求：指定年和月時，編寫一個自定義函數：求該年該月的平均氣溫。某氣象站有一張表temperature，每天在2點，8點，14點，20點自動采集溫度7.3函數——用戶自定義函數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點，8點，14點，20點自動采集溫度7.4存儲過程delimiter$$createprocedure存儲過程名(參數)Begin

方法體end$$delimiter；call存儲過程名

7.4存儲過程MYSQL存儲過程/存儲函數完整語法7.4存儲過程存儲過程示例7.4存儲過程MySQL的參數傳遞存儲過程：IN，OUT，INOUT類型函數：所有參數為IN類型7.5SQL控制流程語句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控制流程語句MySQL流程控制：循環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控制流程語句MySQL流程控制：循環WHILE[begin_label:]WHILEsearch_conditionDOstatement_listENDWHILE[end_label]CREATEPROCEDURE

dowhile()BEGINDECLAREv1INTDEFAULT5;

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

ENDWHILE;END;7.5SQL控制流程語句MySQL流程控制：循環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游標游標

若查詢SQL只返回一條記錄可以放入一個變量，當返回

多條記錄時，需使用游標逐行處理結果集。聲明游標(DECLARE)打開游標(OPEN)使用游標讀取數據(FETCH)關閉游標(CLOSE)7.6游標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觸發器1.觸發器定義-MySQL7.7觸發器2.觸發器的分類（DML）BEFORE觸發器AFT