MySQL數(shù)據(jù)庫管理與開發(fā)（慕課版）

第1章 數(shù)據(jù)庫設(shè)計概述　1
1．1　數(shù)據(jù)庫概述　2
1．1．1　數(shù)據(jù)庫與數(shù)據(jù)庫管理系統(tǒng)　2
1．1．2　數(shù)據(jù)模型　3
1．1．3　結(jié)構(gòu)化查詢語言SQL　3
1．2　數(shù)據(jù)庫的體系結(jié)構(gòu)　3
1．2．1　數(shù)據(jù)庫三級模式結(jié)構(gòu)　3
1．2．2　三級模式之間的映射　4
1．3　E-R圖　4
1．3．1　實體和屬性　4
實例：設(shè)計圖書館管理系統(tǒng)的圖書實體圖
1．3．2　關(guān)系　5
1．3．3　E-R圖的設(shè)計原則　5
實例：設(shè)計圖書館管理系統(tǒng)的E-R圖
1．4　數(shù)據(jù)庫設(shè)計　6
1．4．1　為實體建立數(shù)據(jù)表　6
實例：根據(jù)圖書實體建立圖書數(shù)據(jù)表
1．4．2　為表建立主鍵或外鍵　7
1．4．3　為字段選擇合適的數(shù)據(jù)類型　7
1．4．4　定義約束條件　8
小結(jié)　8
習(xí)題　8
第2章　MySQL概述　9
2．1　為什么選擇MySQL數(shù)據(jù)庫　10
2．1．1　什么是MySQL數(shù)據(jù)庫　10
2．1．2　MySQL的優(yōu)勢　10
2．1．3　MySQL的發(fā)展史　10
2．2　MySQL特性　10
2．3　MySQL服務(wù)器的安裝與配置　11
2．3．1　下載MySQL　11
2．3．2　MySQL環(huán)境的安裝　13
2．3．3　啟動、連接、斷開和停止MySQL服務(wù)器　20
小結(jié)　23
上機指導(dǎo)　24
習(xí)題　25
第3章　MySQL數(shù)據(jù)庫管理　26
3．1　創(chuàng)建數(shù)據(jù)庫　27
3．1．1　通過CREATE DATABASE語句創(chuàng)建數(shù)據(jù)庫　27
實例：創(chuàng)建圖書館管理系統(tǒng)數(shù)據(jù)庫
3．1．2　通過CREATE SCHEMA語句創(chuàng)建數(shù)據(jù)庫　27
實例：通過CREATE SCHEMA語句創(chuàng)建名稱為db_library1的數(shù)據(jù)庫
3．1．3　創(chuàng)建指定字符集的數(shù)據(jù)庫　28
實例：創(chuàng)建使用GBK字符集的數(shù)據(jù)庫
3．1．4　創(chuàng)建數(shù)據(jù)庫前判斷是否存在同名數(shù)據(jù)庫　28
實例：創(chuàng)建圖書館管理系統(tǒng)數(shù)據(jù)庫前判斷是否存在同名數(shù)據(jù)庫
3．2　查看數(shù)據(jù)庫　29
實例：查看服務(wù)器中的所有數(shù)據(jù)庫
3．3　選擇數(shù)據(jù)庫　29
實例：設(shè)置默認(rèn)的數(shù)據(jù)庫
3．4　修改數(shù)據(jù)庫　30
實例：修改數(shù)據(jù)庫使用的字符集
3．5　刪除數(shù)據(jù)庫　31
實例：刪除3．1．4節(jié)創(chuàng)建的數(shù)據(jù)庫
3．6　數(shù)據(jù)庫存儲引擎的應(yīng)用　31
3．6．1　查詢MySQL中支持的存儲引擎　31
實例：查詢默認(rèn)的存儲引擎
3．6．2　InnoDB存儲引擎　33
3．6．3　MyISAM存儲引擎　34
3．6．4　MEMORY存儲引擎　34
3．6．5　如何選擇存儲引擎　35
小結(jié)　36
上機指導(dǎo)　36
習(xí)題　37
第4章　MySQL表結(jié)構(gòu)管理　38
4．1　MySQL數(shù)據(jù)類型　39
4．1．1　數(shù)字類型　39
4．1．2　字符串類型　40
4．1．3　日期和時間數(shù)據(jù)類型　41
4．2　創(chuàng)建表　41
實例：創(chuàng)建圖書館管理系統(tǒng)的圖書信息表
4．2．1　設(shè)置默認(rèn)的存儲引擎　43
實例：為圖書類型表設(shè)置使用MyISAM存儲引擎
4．2．2　設(shè)置自增類型字段　44
實例：為圖書類型表設(shè)置自動編號
4．2．3　設(shè)置字符集　45
4．2．4　復(fù)制表結(jié)構(gòu)　45
實例：為圖書信息表建立副本
4．3　修改表結(jié)構(gòu)　48
4．3．1　修改字段　48
實例：在圖書信息表的副本中添加一個新的字段
4．3．2　修改約束條件　49
4．3．3　修改表的其他選項　50
4．3．4　修改表名　50
實例：修改圖書信息表的副本為tb_books
4．4　刪除表　51
實例：刪除圖書信息表的副本
4．5　設(shè)置索引　51
4．5．1　索引概述　51
4．5．2　創(chuàng)建索引　52
實例：創(chuàng)建考生成績表并設(shè)置索引
實例：為圖書信息表的書名字段設(shè)置索引
4．5．3　刪除索引　55
實例：刪除圖書信息表的書名字段的索引
4．6　定義約束　56
4．6．1　定義主鍵約束　56
實例：創(chuàng)建圖書館管理系統(tǒng)的管理員信息表并將id字段設(shè)置為主鍵約束
實例：創(chuàng)建學(xué)生信息表并將學(xué)號和班級號設(shè)置為主鍵約束
4．6．2　定義候選鍵約束　57
實例：將圖書信息表的書名字段設(shè)置為候選鍵約束
4．6．3　定義非空約束　58
實例：創(chuàng)建圖書館管理系統(tǒng)的管理員信息表，并為其設(shè)置非空約束
4．6．4　定義CHECK約束　58
實例：創(chuàng)建學(xué)生信息表，限制其age字段的值只能是7～18（不包括18）的數(shù)
實例：創(chuàng)建圖書信息表，限制其typeid字段的值只能是類別表的id字段的值
小結(jié)　60
上機指導(dǎo)　60
習(xí)題　61
第5章　表記錄的更新操作　62
5．1　插入表記錄　63
5．1．1　使用INSERT…VALUES語句插入新記錄　63
實例：向圖書館管理系統(tǒng)的管理員信息表中插入一條新記錄
實例：向管理員信息表中插入記錄的一部分
5．1．2　插入多條記錄　65
實例：向管理員信息表中批量插入3條新記錄
5．1．3　使用INSERT…SELECT語句插入結(jié)果集　66
實例：從圖書館管理系統(tǒng)的借閱表中獲取部分借閱信息插入到歸還表中
5．1．4　使用REPLACE語句插入新記錄　68
5．2　修改表記錄　69
實例：將圖書館管理系統(tǒng)的借閱表中的是否歸還字段值設(shè)置為1
5．3　刪除表記錄　70
5．3．1　使用DELETE語句刪除表記錄　70
實例：將圖書館管理系統(tǒng)的管理員信息表中的名稱為admin的管理員刪除
5．3．2　使用TRUNCATE語句清空表記錄　71
實例：清空圖書館管理系統(tǒng)的管理員信息表
小結(jié)　71
上機指導(dǎo)　72
習(xí)題　72
第6章　表記錄的檢索　73
6．1　基本查詢語句　74
6．2　單表查詢　75
6．2．1　查詢所有字段　76
實例：查詢圖書館管理系統(tǒng)的圖書信息表的全部數(shù)據(jù)
6．2．2　查詢指定字段　76
實例：從圖書館管理系統(tǒng)的圖書信息表中查詢圖書的名稱和作者
6．2．3　查詢指定數(shù)據(jù)　76
實例：從圖書館管理系統(tǒng)的管理表中查詢名稱為mr的管理員
6．2．4　帶IN關(guān)鍵字的查詢　77
實例：從圖書館管理系統(tǒng)的圖書表中查詢位于左A-1或右A-1的圖書信息
6．2．5　帶BETWEEN AND的范圍查詢　78
實例：從圖書館管理系統(tǒng)的借閱表中查詢指定時間段內(nèi)的借閱信息
6．2．6　帶LIKE的字符匹配查詢　78
實例：對圖書館管理系統(tǒng)的圖書信息進行模糊查詢
6．2．7　用IS NULL關(guān)鍵字查詢空值　79
實例：查詢圖書館管理系統(tǒng)的讀者類型表中name字段為空的記錄
6．2．8　帶AND的多條件查詢　79
實例：判斷輸入的管理員賬號和密碼是否存在
6．2．9　帶OR的多條件查詢　80
實例：查詢圖書館管理系統(tǒng)的管理員表中名稱為mr或者mingrisoft的記錄
6．2．10　用DISTINCT關(guān)鍵字去除結(jié)果中的重復(fù)行　80
實例：從圖書館管理系統(tǒng)的讀者信息表中獲取職業(yè)
6．2．11　用ORDER BY關(guān)鍵字對查詢結(jié)果排序　81
實例：對圖書借閱信息進行排序
6．2．12　用GROUP BY關(guān)鍵字分組查詢　81
實例：分組統(tǒng)計每本圖書的借閱次數(shù)（3例）
6．2．13　用LIMIT限制查詢結(jié)果的數(shù)量　83
實例：查詢最后被借閱的3本圖書
實例：查詢從編號2開始的3條記錄
6．3　聚合函數(shù)查詢　83
6．3．1　COUNT()函數(shù)　83
實例：統(tǒng)計圖書館管理系統(tǒng)中的讀者人數(shù)
6．3．2　SUM()函數(shù)　84
實例：統(tǒng)計商品的銷售金額
6．3．3　AVG()函數(shù)　84
實例：計算學(xué)生的平均成績
6．3．4　MAX()函數(shù)　85
實例：計算學(xué)生表中的最高成績
6．3．5　MIN()函數(shù)　86
實例：計算學(xué)生表中的最低成績
6．4　連接查詢　86
6．4．1　內(nèi)連接查詢　86
實例：使用內(nèi)連接查詢出圖書的借閱信息
6．4．2　外連接查詢　88
實例：使用左外連接獲取圖書的最多借閱天數(shù)
實例：使用右外連接獲取圖書的最多借閱天數(shù)
6．4．3　復(fù)合條件連接查詢　89
實例：查詢出未歸還的圖書借閱信息
6．5　子查詢　90
6．5．1　帶IN關(guān)鍵字的子查詢　90
實例：查詢被借閱過的圖書信息
6．5．2　帶比較運算符的子查詢　91
實例：查詢考試成績?yōu)閮?yōu)秀的學(xué)生信息
6．5．3　帶EXISTS關(guān)鍵字的子查詢　92
實例：查詢已經(jīng)被借閱的圖書信息
6．5．4　帶ANY關(guān)鍵字的子查詢　93
實例：查詢比一年三班最低分高的全部學(xué)生信息
6．5．5　帶ALL關(guān)鍵字的子查詢　94
實例：查詢比一年三班最高分高的全部學(xué)生信息
6．6　合并查詢結(jié)果　95
實例：將圖書信息表1和圖書信息表2合并（2例）
6．7　定義表和字段的別名　96
6．7．1　為表取別名　96
實例：使用左連接查詢出圖書的完整信息，并為表指定別名
6．7．2　為字段取別名　96
實例：統(tǒng)計每本圖書的借閱次數(shù)，并取別名為degree
6．8　使用正則表達式查詢　97
6．8．1　匹配指定字符中的任意一個　98
實例：在圖書館管理系統(tǒng)中查詢包括字母k、r或s的管理員信息
6．8．2　使用“*”和“+”來匹配多個字符　99
實例：在圖書館管理系統(tǒng)中查詢E-mail地址不正確的讀者信息
小結(jié)　100
上機指導(dǎo)　100
習(xí)題　101
第7章　視圖　102
7．1　視圖概述　103
7．1．1　視圖的概念　103
7．1．2　視圖的作用　103
7．2　創(chuàng)建視圖　104
7．2．1　查看創(chuàng)建視圖的權(quán)限　104
實例：查看用戶是否具有創(chuàng)建視圖的權(quán)限
7．2．2　創(chuàng)建視圖　104
實例：創(chuàng)建保存完整圖書信息的視圖
7．2．3　創(chuàng)建視圖的注意事項　106
7．3　視圖操作　106
7．3．1　查看視圖　106
實例：查看圖書視圖的結(jié)構(gòu)
實例：查看圖書視圖的詳細(xì)定義
7．3．2　修改視圖　108
實例：修改圖書視圖的結(jié)構(gòu)
實例：使用ALTER語句修改圖書視圖的結(jié)構(gòu)
7．3．3　更新視圖　110
實例：更新圖書視圖中的數(shù)據(jù)
7．3．4　刪除視圖　112
實例：刪除已經(jīng)創(chuàng)建的圖書視圖
小結(jié)　113
上機指導(dǎo)　113
習(xí)題　114
第8章　觸發(fā)器　115
8．1　MySQL觸發(fā)器　116
8．1．1　創(chuàng)建MySQL觸發(fā)器　116
實例：保存圖書信息時，自動向日志表添加一條數(shù)據(jù)
8．1．2　創(chuàng)建具有多個執(zhí)行語句的觸發(fā)器　117
實例：刪除圖書信息時，分別向日志表和臨時表中各添加一條數(shù)據(jù)
8．2　查看觸發(fā)器　119
8．2．1　SHOW TRIGGERS　119
8．2．2　查看triggers表中的觸發(fā)器信息　120
8．3　使用觸發(fā)器　120
8．3．1　觸發(fā)器的執(zhí)行順序　121
實例：觸發(fā)器與表操作的執(zhí)行順序
8．3．2　使用觸發(fā)器維護冗余數(shù)據(jù)　122
實例：使用觸發(fā)器維護庫存數(shù)量
8．4　刪除觸發(fā)器　123
實例：刪除指定名稱的觸發(fā)器
小結(jié)　124
上機指導(dǎo)　124
習(xí)題　125
第9章　存儲過程與存儲函數(shù)　126
9．1　創(chuàng)建存儲過程與存儲函數(shù)　127
9．1．1　創(chuàng)建存儲過程　127
實例：創(chuàng)建一個統(tǒng)計指定圖書借閱次數(shù)的存儲過程
9．1．2　創(chuàng)建存儲函數(shù)　129
實例：創(chuàng)建一個統(tǒng)計圖書借閱次數(shù)的存儲函數(shù)
9．1．3　變量的應(yīng)用　130
實例：演示局部變量的有效范圍
實例：會話（全局）變量的舉例
9．1．4　光標(biāo)的運用　133
9．2　存儲過程和存儲函數(shù)的調(diào)用　134
9．2．1　調(diào)用存儲過程　134
實例：調(diào)用統(tǒng)計圖書借閱次數(shù)的存儲過程
9．2．2　調(diào)用存儲函數(shù)　135
實例：調(diào)用統(tǒng)計圖書借閱次數(shù)的存儲函數(shù)
9．3　查看存儲過程和函數(shù)　136
9．3．1　SHOW STATUS語句　136
9．3．2　SHOW CREATE語句　136
實例：查詢指定名稱的存儲過程
9．4　修改存儲過程和函數(shù)　137
實例：修改存儲過程
9．5　刪除存儲過程和函數(shù)　138
實例：刪除統(tǒng)計圖書借閱次數(shù)的存儲過程
實例：刪除統(tǒng)計圖書借閱次數(shù)的存儲函數(shù)
小結(jié)　138
上機指導(dǎo)　139
習(xí)題　140
第10章　備份與恢復(fù)　141
10．1　數(shù)據(jù)備份　142
10．1．1　使用mysqldump命令備份　142
實例：備份圖書館管理系統(tǒng)的數(shù)據(jù)庫
實例：備份db_library和db_library_gbk數(shù)據(jù)庫
實例：備份所有數(shù)據(jù)庫
10．1．2　直接復(fù)制整個數(shù)據(jù)庫目錄　145
10．1．3　使用mysqlhotcopy工具快速備份　145
10．2　數(shù)據(jù)恢復(fù)　146
10．2．1　使用mysql命令還原　146
實例：還原已經(jīng)備份的圖書館管理系統(tǒng)的數(shù)據(jù)庫
10．2．2　直接復(fù)制到數(shù)據(jù)庫目錄　147
10．3　數(shù)據(jù)庫遷移　147
10．3．1　MySQL數(shù)據(jù)庫之間的遷移　147
10．3．2　不同數(shù)據(jù)庫之間的遷移　148
10．4　表的導(dǎo)出和導(dǎo)入　148
10．4．1　用SELECT…INTO OUTFILE導(dǎo)出文本文件　148
實例：使用SELECT…INTO OUTFILE導(dǎo)出圖書館管理系統(tǒng)的圖書表的記錄
10．4．2　用mysqldump命令導(dǎo)出文本文件　150
實例：使用mysqldump命令導(dǎo)出圖書館管理系統(tǒng)的圖書表的記錄
10．4．3　用mysql命令導(dǎo)出文本文件　152
實例：使用mysql命令導(dǎo)出圖書館管理系統(tǒng)的圖書表的記錄
小結(jié)　153
上機指導(dǎo)　153
習(xí)題　155
第11章　MySQL性能優(yōu)化　156
11．1　優(yōu)化概述　157
11．1．1　分析MySQL數(shù)據(jù)庫的性能　157
11．1．2　通過profile工具分析語句消耗的性能　157
11．2　優(yōu)化查詢　158
11．2．1　分析查詢語句　159
實例：使用EXPLAIN語句分析一個查詢語句
11．2．2　索引對查詢速度的影響　160
實例：舉例分析索引對查詢速度的影響
11．2．3　使用索引查詢　161
實例：舉例分析應(yīng)用LIKE關(guān)鍵字優(yōu)化索引查詢
實例：通過EXPLAIN分析使用OR關(guān)鍵字的查詢
11．3　優(yōu)化數(shù)據(jù)庫結(jié)構(gòu)　162
11．3．1　將字段很多的表分解成多個表　163
實例：將學(xué)生表分解為學(xué)生表和備注表
11．3．2　增加中間表　163
實例：創(chuàng)建包含學(xué)生表常用信息表中間表
11．3．3　優(yōu)化插入記錄的速度　164
11．3．4　分析表、檢查表和優(yōu)化表　165
11．4　優(yōu)化多表查詢　166
實例：演示優(yōu)化多表查詢
11．5　優(yōu)化表設(shè)計　167
小結(jié)　168
上機指導(dǎo)　168
習(xí)題　169
第12章　事務(wù)與鎖機制　170
12．1　事務(wù)機制　171
12．1．1　事務(wù)的概念　171
12．1．2　事務(wù)機制的必要性　171
實例：模擬銀行轉(zhuǎn)賬驗證事務(wù)的必要性
12．1．3　關(guān)閉MySQL自動提交　173
12．1．4　事務(wù)回滾　174
實例：演示事務(wù)回滾
12．1．5　事務(wù)提交　175
12．1．6　MySQL中的事務(wù)　176
實例：實現(xiàn)出錯時回滾否則提交事務(wù)
12．1．7　回退點　178
實例：應(yīng)用回退點實現(xiàn)部分撤銷
12．2　鎖機制　179
12．2．1　MySQL鎖機制的基本知識　180
12．2．2　MyISAM表的表級鎖　181
實例：以讀方式鎖定用戶數(shù)據(jù)表
實例：以寫方式鎖定用戶表
12．2．3　InnoDB表的行級鎖　184
實例：通過事務(wù)實現(xiàn)延長行級鎖的生命周期
12．2．4　死鎖的概念與避免　186
12．3　事務(wù)的隔離級別　186
12．3．1　事務(wù)的隔離級別與并發(fā)問題　186
12．3．2　設(shè)置事務(wù)的隔離級別　187
小結(jié)　187
上機指導(dǎo)　188
習(xí)題　190
第13章　綜合開發(fā)案例——圖書館管理系統(tǒng)　191
13．1　開發(fā)背景　192
13．2　系統(tǒng)分析　192
13．2．1　需求分析　192
13．2．2　可行性研究　192
13．3　JSP預(yù)備知識　193
13．3．1　JSP概述　193
13．3．2　JSP的開發(fā)及運行環(huán)境　194
13．3．3　JSP頁面的基本構(gòu)成　195
13．4　系統(tǒng)設(shè)計　195
13．4．1　系統(tǒng)目標(biāo)　195
13．4．2　系統(tǒng)功能結(jié)構(gòu)　196
13．4．3　系統(tǒng)流程圖　196
13．4．4　開發(fā)環(huán)境　197
13．4．5　文件夾組織結(jié)構(gòu)　197
13．5　系統(tǒng)預(yù)覽　197
13．6　數(shù)據(jù)庫設(shè)計　199
13．6．1　實體圖設(shè)計　199
13．6．2　E-R圖設(shè)計　201
13．6．3　數(shù)據(jù)庫邏輯結(jié)構(gòu)設(shè)計　201
13．7　公共模塊設(shè)計　204
13．7．1　數(shù)據(jù)庫連接及操作類的編寫　205
13．7．2　字符串處理類的編寫　207
13．7．3　配置解決中文亂碼的過濾器　207
13．8　主界面設(shè)計　208
13．8．1　主界面概述　208
13．8．2　主界面的實現(xiàn)過程　209
13．9　管理員模塊設(shè)計　210
13．9．1　管理員模塊概述　210
13．9．2　編寫管理員模塊的實體類和Servlet控制類　210
13．9．3　系統(tǒng)登錄的實現(xiàn)過程　212
13．9．4　查看管理員的實現(xiàn)過程　215
13．9．5　添加管理員的實現(xiàn)過程　218
13．9．6　設(shè)置管理員權(quán)限的實現(xiàn)過程　221
13．9．7　刪除管理員的實現(xiàn)過程　224
13．10　圖書借還模塊設(shè)計　225
13．10．1　圖書借還模塊概述　225
13．10．2　編寫圖書借還模塊的實體類和Servlet控制類　226
13．10．3　圖書借閱的實現(xiàn)過程　227
13．10．4　圖書續(xù)借的實現(xiàn)過程　231
13．10．5　圖書歸還的實現(xiàn)過程　234
13．10．6　圖書借閱查詢的實現(xiàn)過程　235
小結(jié)　239
附錄　實驗　240
實驗1：安裝MySQL數(shù)據(jù)庫　241
實驗2：創(chuàng)建數(shù)據(jù)庫并指定使用的字符集　241
實驗3：創(chuàng)建和修改數(shù)據(jù)表　243
實驗4：使用SQL語句插入和更新記錄　244
實驗5：為表創(chuàng)建索引　246
實驗6：創(chuàng)建并使用約束　247
實驗7：模糊查詢數(shù)據(jù)　249
實驗8：查詢和匯總數(shù)據(jù)庫的數(shù)據(jù)　250
實驗9：創(chuàng)建視圖　251
實驗10：創(chuàng)建觸發(fā)器　252
實驗11：創(chuàng)建和使用存儲過程　254
實驗12：備份和恢復(fù)數(shù)據(jù)庫　255