MySQL數據庫管理與開發（慕課版 第2版）AIGC高效編程

賽奎春，明日科技，已經在我社出版多本程序設計類書籍,包括“技術方案寶典”、“開發技術大全”、“自學手冊”、“參考大全”等多個系列，大都取得比較好的市場反響。

第 1章 數據庫設計概述 1

1.1　數據庫概述　2

1.1.1　數據庫與數據庫管理系統　2

1.1.2　數據模型　3

1.1.3　結構化查詢語言SQL　3

1.2　數據庫的體系結構　3

1.2.1　數據庫三級模式結構　3

1.2.2　三級模式之間的映射　4

1.3　E-R圖　4

1.3.1　實體和屬性　4

　實例：設計圖書館管理系統的圖書實體圖

1.3.2　關系　5

1.3.3　E-R圖的設計原則　5

　實例：設計圖書館管理系統的E-R圖

1.4　數據庫設計　6

1.4.1　為實體建立數據表　6

　實例：根據圖書實體建立圖書數據表

1.4.2　為表建立主鍵或外鍵　7

1.4.3　為字段選擇合適的數據類型　8

1.4.4　定義約束條件　9

小結　9

習題　9

第　2章 MySQL概述　10

2.1　為什麼選擇MySQL數據庫　11

2.1.1　什麼是MySQL數據庫　11

2.1.2　MySQL的優勢　11

2.1.3　MySQL的發展史　11

2.2　MySQL特性　11

2.3　MySQL服務器的安裝與配置　12

2.3.1　下載MySQL　12

2.3.2　MySQL環境的安裝　13

2.3.3　啟動、連接、斷開和停止MySQL服務器　17

2.4　MySQL常用圖形管理工具　22

小結　22

上機指導　22

習題　24

第3章　MySQL數據類型與

運算符　25

3.1　MySQL數據類型　26

3.1.1　數字類型　26

　實例：查詢指定數據類型的字段值

3.1.2　字符串類型　27

3.1.3　日期和時間數據類型　28

　實例：獲取當前的系統時間

3.2　MySQL運算符　29

3.2.1　算術運算符　30

　實例：使用算術運算符進行運算

3.2.2　比較運算符　30

　實例：使用“=”運算符查詢記錄

3.2.3　邏輯運算符　35

　實例：使用“&&”運算符進行邏輯運算

3.2.4　位運算符　37

3.2.5　運算符的優先級　37

小結　38

上機指導　38

習題　39

第4章　MySQL存儲引擎　40

4.1　MySQL架構　41

4.2　存儲引擎的應用　41

4.2.1　查詢MySQL中支持的存儲

引擎　42

　實例：查詢默認的存儲引擎

4.2.2　InnoDB存儲引擎　43

4.2.3　MyISAM存儲引擎　44

4.2.4　MEMORY存儲引擎　45

4.2.5　如何選擇存儲引擎　46

小結　46

上機指導　46

習題　51

第5章　MySQL數據庫管理　52

5.1　創建數據庫　53

5.1.1　通過CREATE DATABASE語句創建數據庫　53

　實例：創建圖書館管理系統數據庫

5.1.2　通過CREATE SCHEMA語句創建數據庫　53

　實例：通過CREATE SCHEMA語句創建名稱為db_shop的數據庫

5.1.3　創建指定字符集的數據庫　54

　實例：創建使用gbk字符集的數據庫

5.1.4　創建數據庫前判斷是否存在同名數據庫　54

　實例：創建圖書館管理系統數據庫前判斷是否存在同名數據庫

5.2　查看數據庫　55

　實例：查看服務器中的所有數據庫

5.3　選擇數據庫　55

　實例：設置默認的數據庫

5.4　修改數據庫　56

　實例：修改數據庫使用的字符集

5.5　刪除數據庫　57

　實例：刪除名稱為db_sales的數據庫

小結　57

上機指導　57

習題　58

第6章　MySQL表結構管理　59

6.1　創建表　60

　實例：創建圖書館管理系統的圖書信息表

6.1.1　設置默認的存儲引擎　61

　實例：為圖書類型表設置使用MyISAM存儲引擎

6.1.2　設置自增類型字段　62

　實例：為圖書類型表設置自動編號

6.1.3　設置字符集　63

6.1.4　復制表結構　63

　實例：為圖書信息表建立副本

6.2　修改表結構　66

6.2.1　修改字段　66

　實例：在圖書信息表的副本中添加一個新的字段

6.2.2　修改約束條件　67

6.2.3　修改表的其他選項　69

6.2.4　修改表名　69

　實例：修改圖書信息表的副本為tb_books

6.3　刪除表　70

　實例：刪除圖書信息表的副本

6.4　設置索引　70

6.4.1　索引概述　70

6.4.2　創建索引　71

　實例：創建考生成績表並設置索引

　實例：為圖書信息表的書名字段設置索引

6.4.3　刪除索引　73

　實例：刪除圖書信息表的書名字段的索引

6.5　定義約束　74

6.5.1　定義主鍵約束　74

　實例：創建圖書館管理系統的管理員信息表並將id字段設置為主鍵約束

　實例：創建學生信息表並將學號和班級號設置為主鍵約束

6.5.2　定義候選鍵約束　76

　實例：將圖書信息表的書名字段設置為候選鍵約束

6.5.3　定義非空約束　77

　實例：創建圖書館管理系統的管理員信息表，並為其設置非空約束

6.5.4　定義CHECK約束　77

　實例：創建學生信息表，限制其age字段的值只能是7～18（不包括18）的數

　實例：創建圖書信息表，限制其del字段的值只能是0或1

小結　79

上機指導　79

習題　79

第7章　MySQL函數　81

7.1　MySQL函數概述　82

7.2　數學函數　82

7.2.1　ABS(x)函數　83

　實例：使用ABS(x)函數獲取數字絕對值

7.2.2　RAND()函數　84

　實例：運用RAND()函數獲取兩個隨機數

7.2.3　FLOOR(x)函數　84

　實例：應用FLOOR(x)函數求小於或等於指定數字的最大整數

7.2.4　PI()函數　84

　實例：使用PI()函數獲取圓周率

7.2.5　TRUNCATE(x,y)函數　85

　實例：使用TRUNCATE(x,y)函數獲取指定小數位數

7.2.6　ROUND(x)函數和ROUND(x,y)函數　85

　實例：使用ROUND(x)函數和ROUND(x,y)函數處理數字

7.2.7　SQRT(x)函數　86

　實例：使用SQRT(x)函數求指定數字的平方根

7.3　字符串函數　86

7.3.1　INSERT(s1,x,len,s2)函數　87

　實例：使用INSERT()函數進行字符串替換

7.3.2　UPPER(s),UCASE(s)函數　88

　實例：使用UPPER(s)函數和UCASE(s)函數將字母轉換為大寫

7.3.3　LEFT(s,n)函數　88

　實例：應用LEFT()函數返回指定字符串的前4個字符

7.3.4　RTRIM(s)函數　88

　實例：應用RTRIM函數去掉指定字符串結尾處的空格

7.3.5　SUBSTRING(s,n,len)函數　89

　實例：使用SUBSTRING()函數截取字符串

7.3.6　REVERSE(s)函數　89

　實例：使用REVERSE()函數反轉字符串

7.3.7　FIELD(s,s1,s2,…)函數　90

　實例：應用FIELD函數查找字符串

7.4　日期和時間函數　90

7.4.1　CURDATE()函數和

CURRENT_DATE()函數　91

　實例：使用CURDATE()和

CURRENT_DATE()函數獲取當前日期

7.4.2　CURTIME()函數和

CURRENT_TIME()函數　92

　實例：使用CURTIME()和

CURRENT_TIME()函數獲取當前時間

7.4.3　NOW()函數　92

　實例：使用5個函數獲取當前日期和時間

7.4.4　DATEDIFF(d1,d2)函數　92

　實例：使用DATEDIFF()函數計算兩個日期的間隔天數

7.4.5　ADDDATE(d,n)函數　93

　實例：使用ADDDATE(d,n)函數返回指定日期加上6天的日期

7.4.6　ADDDATE(d,

INTERVAL　expr type)函數　93

　實例：返回指定日期加上1年2個月後的日期

7.4.7　SUBDATE(d,n)函數　94

　實例：使用SUBDATE(d,n)函數返回指定日期減去6天後的日期

7.5　條件判斷函數　94

　實例：使用CASE WHEN語句執行分支操作

7.6　系統信息函數　95

　實例：演示4個系統信息函數的用法

7.7　加密函數　96

　實例：使用MD5(str)函數為指定字符串加密

7.8　其他函數　96

　實例：使用FORMAT(x，n)函數對數字進行格式化

小結　97

上機指導　98

習題　98

第8章　表記錄的更新操作　99

8.1　插入表記錄　100

8.1.1　使用INSERT…VALUES語句插入新記錄　100

　實例：向圖書館管理系統的管理員信息表中插入一條新記錄

　實例：向管理員信息表中插入記錄的一部分

8.1.2　插入多條記錄　102

　實例：向管理員信息表中批量插入多條記錄

8.1.3　使用INSERT…SELECT語句插入結果集　103

　實例：從圖書館管理系統的借閱表中獲取部分借閱信息插入到歸還表中

8.1.4　使用REPLACE語句插入新記錄　105

8.2　修改表記錄　106

　實例：將圖書館管理系統的借閱表中的是否歸還字段值設置為1

8.3　刪除表記錄　107

8.3.1　使用DELETE語句刪除表記錄　107

　實例：將圖書館管理系統的管理員信息表中的指定管理員刪除

8.3.2　使用TRUNCATE語句清空表記錄　108

　實例：清空圖書館管理系統的管理員信息表

小結　109

上機指導　109

習題　110

第9章　表記錄的檢索　111

9.1　基本查詢語句　112

9.2　單表查詢　114

9.2.1　查詢所有字段　114

　實例：查詢圖書館管理系統的圖書信息表的全部數據

9.2.2　查詢指定字段　114

　實例：從圖書館管理系統的圖書信息表中查詢圖書的名稱和作者

9.2.3　查詢指定數據　115

　實例：從圖書館管理系統的管理表中查詢名稱為mr的管理員

9.2.4　帶IN關鍵字的查詢　115

　實例：從圖書館管理系統的圖書信息表中查詢指定id的圖書信息

9.2.5　帶BETWEEN AND的範圍查詢　115

　實例：從圖書館管理系統的圖書信息表中查詢錄入時間在指定時間段內的圖書信息

9.2.6　帶LIKE的字符匹配查詢　116

　實例：對圖書館管理系統的圖書信息進行模糊查詢

9.2.7　用IS NULL關鍵字查詢空值　117

　實例：查詢圖書館管理系統的讀者類型表中name字段為空的記錄

9.2.8　帶AND的多條件查詢　117

　實例：判斷輸入的管理員賬號和密碼是否存在

9.2.9　帶OR的多條件查詢　118

　實例：查詢圖書館管理系統的管理員表中指定名稱的記錄

9.2.10　用DISTINCT關鍵字去除

結果中的重復行　118

　實例：使用DISTINCT關鍵字去除圖書信息表中指定字段的重復記錄

9.2.11　用ORDER BY關鍵字對查詢結果排序　119

　實例：對圖書借閱信息進行排序

9.2.12　用GROUP BY關鍵字分組

查詢　119

　實例：分組統計每本圖書的借閱次數

9.2.13　用LIMIT限制查詢結果的

數量　121

　實例：查詢最後被借閱的3本圖書

　實例：查詢從編號2開始的3條記錄

9.3　聚合函數查詢　121

9.3.1　COUNT()函數　121

　實例：統計圖書館管理系統中的圖書類型的個數

9.3.2　SUM()函數　122

　實例：統計商品的銷售金額

9.3.3　AVG()函數　122

　實例：計算學生的平均成績

9.3.4　MAX()函數　123

　實例：計算學生表中的最高成績

9.3.5　MIN()函數　123

　實例：計算學生表中的最低成績

9.4　連接查詢　124

9.4.1　內連接查詢　124

　實例：使用內連接查詢出圖書的借閱信息

9.4.2　外連接查詢　125

　實例：使用左外連接獲取圖書的最多借閱天數

　實例：對兩個數據表進行右外連接

9.4.3　復合條件連接查詢　127

　實例：查詢出未歸還的圖書借閱信息

9.5　子查詢　127

9.5.1　帶IN關鍵字的子查詢　128

　實例：查詢被借閱過的圖書信息

9.5.2　帶比較運算符的子查詢　128

　實例：查詢考試成績為優秀的學生信息

9.5.3　帶EXISTS關鍵字的子查詢　130

　實例：查詢已經被借閱的圖書信息

9.5.4　帶ANY關鍵字的子查詢　130

　實例：查詢比指定班級最低分高的全部學生信息

9.5.5　帶ALL關鍵字的子查詢　132

　實例：查詢比指定班級最高分高的全部學生信息

9.6　合並查詢結果　132

　實例：將圖書信息表1和圖書信息表2合並

9.7　定義表和字段的別名　134

9.7.1　為表取別名　134

　實例：使用左連接查詢出圖書的完整信息，並為表指定別名

9.7.2　為字段取別名　134

　實例：統計每本圖書的借閱次數，並取別名為times

9.8　使用正則表達式查詢　135

9.8.1　匹配指定字符中的任意一個　136

　實例：在圖書館管理系統中查詢包括字母t、k或h的管理員信息

9.8.2　使用“*”和“+”來匹配

多個字符　137

　實例：在圖書館管理系統中查詢指定的管理員信息

小結　137

上機指導　137

習題　139

第　10章 視圖　140

10.1　視圖概述　141

10.1.1　視圖的概念　141

10.1.2　視圖的作用　141

10.2　創建視圖　142

10.2.1　查看創建視圖的權限　142

　實例：查看用戶是否具有創建視圖的權限

10.2.2　創建視圖　142

　實例：創建保存完整圖書信息的視圖

10.2.3　創建視圖的註意事項　144

10.3　視圖操作　144

10.3.1　查看視圖　144

　實例：查看圖書視圖的結構

　實例：查看圖書視圖的詳細定義

10.3.2　修改視圖　146

　實例：修改圖書視圖的結構

　實例：使用ALTER語句修改圖書視圖的

結構

10.3.3　更新視圖　148

　實例：更新圖書視圖中的數據

10.3.4　刪除視圖　150

　實例：刪除已經創建的圖書視圖

小結　150

上機指導　150

習題　151

第　11章 觸發器　152

11.1　MySQL觸發器　153

11.1.1　創建MySQL觸發器　153

　實例：保存圖書信息時，自動向日誌表添加一條數據

11.1.2　創建具有多個執行語句的

觸發器　154

　實例：刪除圖書信息時，分別向日誌表和臨時表中各添加一條數據

11.2　查看觸發器　156

11.2.1　SHOW TRIGGERS　156

11.2.2　查看triggers表中的觸發器

信息　157

11.3　使用觸發器　157

11.3.1　觸發器的執行順序　158

　實例：觸發器與表操作的執行順序

11.3.2　使用觸發器維護冗余數據　159

　實例：使用觸發器維護庫存數量

11.4　刪除觸發器　160

　實例：刪除指定名稱的觸發器

小結　161

上機指導　161

習題　162

第　12章 存儲過程與存儲函數　163

12.1　創建存儲過程與存儲函數　164

12.1.1　創建存儲過程　164

　實例：創建一個統計指定圖書借閱次數的存儲過程

12.1.2　創建存儲函數　166

　實例：創建一個統計圖書借閱次數的存儲

函數

12.1.3　變量的應用　167

　實例：演示局部變量的有效範圍

　實例：全局變量的舉例

12.1.4　光標的運用　170

12.2　存儲過程和存儲函數的調用　171

12.2.1　調用存儲過程　171

　實例：調用統計圖書借閱次數的存儲過程

12.2.2　調用存儲函數　172

　實例：調用統計圖書借閱次數的存儲函數

12.3　查看存儲過程和函數　173

12.3.1　SHOW STATUS語句　173

12.3.2　SHOW CREATE語句　173

　實例：查詢指定名稱的存儲過程

12.4　修改存儲過程和函數　173

　實例：修改存儲過程

12.5　刪除存儲過程和函數　174

　實例：刪除統計圖書借閱次數的存儲過程

　實例：刪除統計圖書借閱次數的存儲函數

小結　175

上機指導　175

習題　177

第　13章 備份與恢復　178

13.1　數據備份　179

13.1.1　使用mysqldump命令備份　179

　實例：備份圖書館管理系統的數據庫

　實例：備份db_library和db_shop數據庫

　實例：備份所有數據庫

13.1.2　直接復制整個數據庫目錄　182

13.1.3　使用mysqlhotcopy工具快速

備份　182

13.2　數據恢復　183

13.2.1　使用mysql命令還原　183

　實例：還原已經備份的圖書館管理系統的數據庫

13.2.2　直接復制到數據庫目錄　184

13.3　數據庫遷移　184

13.3.1　MySQL數據庫之間的遷移　184

13.3.2　不同數據庫之間的遷移　185

13.4　表的導出和導入　185

13.4.1　用SELECT…INTO OUTFILE

導出文本文件　185

　實例：導出db_shop數據庫中的商品信息表的記錄

13.4.2　用mysqldump命令導出

文本文件　187

　實例：使用mysqldump命令導出圖書館管理系統的圖書表的記錄

13.4.3　用mysql命令導出文本文件　188

　實例：使用mysql命令導出圖書館管理系統的圖書表的記錄

小結　189

上機指導　189

習題　191

第　14章 MySQL性能優化　192

14.1　優化概述　193

14.1.1　分析MySQL數據庫的性能　193

14.1.2　通過profile工具分析語句

消耗性能　193

14.2　優化查詢　194

14.2.1　分析查詢語句　195

　實例：使用EXPLAIN語句分析一個查詢

語句

14.2.2　索引對查詢速度的影響　196

　實例：舉例分析索引對查詢速度的影響

14.2.3　使用索引查詢　197

　實例：舉例分析應用LIKE關鍵字優化索引查詢

　實例：通過EXPLAIN分析使用OR關鍵字的查詢

14.3　優化數據庫結構　198

14.3.1　將字段很多的表分解成

多個表　198

　實例：將學生表分解為學生表和生日信息表

14.3.2　增加中間表　199

　實例：創建包含學生常用信息的中間表

14.3.3　優化插入記錄的速度　200

14.3.4　分析表、檢查表和優化表　200

　實例：分析指定數據表

14.4　優化多表查詢　202

　實例：演示優化多表查詢

14.5　優化表設計　203

小結　203

上機指導　203

習題　204

第　15章 事務與鎖機制　205

15.1　事務機制　206

15.1.1　事務的概念　206

15.1.2　事務機制的必要性　206

　實例：模擬銀行轉賬驗證事務的必要性

15.1.3　關閉MySQL自動提交　208

15.1.4　事務回滾　209

　實例：演示事務回滾

15.1.5　事務提交　210

15.1.6　MySQL中的事務　211

　實例：實現出錯時回滾否則提交事務

15.1.7　回退點　212

　實例：應用回退點實現部分撤銷

15.2　鎖機制　214

15.2.1　MySQL鎖機制的基本知識　214

15.2.2　MyISAM表的表級鎖　216

　實例：以讀方式鎖定用戶數據表

　實例：以寫方式鎖定用戶數據表

15.2.3　InnoDB表的行級鎖　219

　實例：通過事務實現延長行級鎖的生命周期

15.2.4　死鎖的概念與避免　220

15.3　事務的隔離級別　220

15.3.1　事務的隔離級別與並發問題　221

15.3.2　設置事務的隔離級別　221

小結　222

上機指導　222

習題　224

第　16章 綜合開發案例——基於Python Flask的Go購甄選商城　225

16.1　需求分析　226

16.2　系統設計　226

16.2.1　系統功能結構　226

16.2.2　系統業務流程　227

16.2.3　系統預覽　227

16.3　系統開發必備　231

16.3.1　系統開發環境　231

16.3.2　文件夾組織結構　231

16.4　技術準備　232

16.4.1　Flask-SQLAlchemy擴展　232

16.4.2　Flask-Migrate擴展　234

16.5　數據庫設計　236

16.5.1　數據庫概要說明　236

16.5.2　創建數據表　236

16.5.3　數據表關系　239

16.6　會員註冊模塊設計　240

16.6.1　會員註冊模塊概述　240

16.6.2　會員註冊頁面　241

16.6.3　驗證並保存註冊信息　246

16.7　會員登錄模塊設計　247

16.7.1　會員登錄模塊概述　247

16.7.2　創建會員登錄頁面　248

16.7.3　保存會員登錄狀態　251

16.7.4　會員退出功能　252

16.8　首頁模塊設計　252

16.8.1　首頁模塊概述　252

16.8.2　實現顯示最新上架商品功能　252

16.8.3　實現顯示打折商品功能　255

16.8.4　實現顯示熱門商品功能　256

16.9　購物車模塊設計　257

16.9.1　購物車模塊概述　258

16.9.2　實現顯示商品詳細信息功能　260

16.9.3　實現添加購物車功能　261

16.9.4　實現查看購物車功能　262

16.9.5　實現保存訂單功能　263

16.9.6　實現查看訂單功能　264

16.10　後臺功能模塊設計　265

16.10.1　後臺登錄模塊設計　265

16.10.2　商品管理模塊設計　267

16.10.3　銷量排行榜模塊設計　275

16.10.4　會員管理模塊設計　276

16.10.5　訂單管理模塊設計　277

小結　279

習題　279

附錄　實驗　280

實驗1：安裝MySQL數據庫　281

實驗2：創建數據庫並指定使用的

字符集　281

實驗3：創建和修改數據表　282

實驗4：使用SQL語句插入和更新記錄　284

實驗5：為表創建索引　285

實驗6：創建並使用約束　287

實驗7：模糊查詢數據　288

實驗8：查詢和匯總數據庫的數據　289

實驗9：創建視圖　290

實驗10：創建觸發器　291

實驗11：創建和使用存儲過程　293

實驗12：備份和恢復數據庫　294