MySQL核心技術(shù)與最佳實(shí)踐

目　錄
第一篇　關(guān)系數(shù)據(jù)庫(kù)設(shè)計(jì)
第1章　數(shù)據(jù)庫(kù)設(shè)計(jì)概述　2
1．1　數(shù)據(jù)庫(kù)概述　2
1．1．1　關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)　2
1．1．2　關(guān)系數(shù)據(jù)庫(kù)　3
1．1．3　結(jié)構(gòu)化查詢語言SQL　4
1．2　數(shù)據(jù)庫(kù)設(shè)計(jì)的相關(guān)知識(shí)　5
1．2．1　商業(yè)知識(shí)和溝通技能　5
1．2．2　數(shù)據(jù)庫(kù)設(shè)計(jì)輔助工具　5
1．2．3　“選課系統(tǒng)”概述　6
1．2．4　定義問題域　7
1．2．5　編碼規(guī)范　8
1．3　E-R圖　9
1．3．1　實(shí)體和屬性　9
1．3．2　關(guān)系　10
1．3．3　E-R圖的設(shè)計(jì)原則　12
1．4　關(guān)系數(shù)據(jù)庫(kù)設(shè)計(jì)　14
1．4．1　為每個(gè)實(shí)體建立一張數(shù)據(jù)庫(kù)表　15
1．4．2　為每張表定義一個(gè)主鍵　15
1．4．3　增加外鍵表示一對(duì)多關(guān)系　16
1．4．4　建立新表表示多對(duì)多關(guān)系　18
1．4．5　為字段選擇合適的數(shù)據(jù)類型　19
1．4．6　定義約束(constraint)條件　19
1．4．7　評(píng)價(jià)數(shù)據(jù)庫(kù)表設(shè)計(jì)的質(zhì)量　21
1．4．8　使用規(guī)范化減少數(shù)據(jù)冗余　21
1．4．9　避免數(shù)據(jù)經(jīng)常發(fā)生變化　25
習(xí)題　26
第二篇　MySQL基礎(chǔ)
第2章　MySQL基礎(chǔ)知識(shí)　30
2．1　MySQL概述　30
2．1．1　MySQL的特點(diǎn)　30
2．1．2　MySQL服務(wù)的安裝　32
2．1．3　MySQL服務(wù)的配置　34
2．1．4　啟動(dòng)與停止MySQL服務(wù)　40
2．1．5　MySQL配置文件　41
2．1．6　MySQL客戶機(jī)　41
2．1．7　連接MySQL服務(wù)器　42
2．2　字符集以及字符序設(shè)置　44
2．2．1　字符集及字符序概念　44
2．2．2　MySQL字符集與字符序　45
2．2．3　MySQL字符集的轉(zhuǎn)換過程　46
2．2．4　MySQL字符集的設(shè)置　47
2．2．5　SQL腳本文件　48
2．3　MySQL數(shù)據(jù)庫(kù)管理　49
2．3．1　創(chuàng)建數(shù)據(jù)庫(kù)　49
2．3．2　查看數(shù)據(jù)庫(kù)　50
2．3．3　顯示數(shù)據(jù)庫(kù)結(jié)構(gòu)　50
2．3．4　選擇當(dāng)前操作的數(shù)據(jù)庫(kù)　51
2．3．5　刪除數(shù)據(jù)庫(kù)　51
2．4　MySQL表管理　51
2．4．1　MyISAM和InnoDB存儲(chǔ)引擎　51
2．4．2　設(shè)置默認(rèn)的存儲(chǔ)引擎　52
2．4．3　創(chuàng)建數(shù)據(jù)庫(kù)表　53
2．4．4　顯示表結(jié)構(gòu)　54
2．4．5　表記錄的管理　54
2．4．6　InnoDB表空間　57
2．4．7　刪除表　59
2．5　系統(tǒng)變量　59
2．5．1　全局系統(tǒng)變量與會(huì)話系統(tǒng)變量　59
2．5．2　查看系統(tǒng)變量的值　60
2．5．3　設(shè)置系統(tǒng)變量的值　62
2．6　MySQL數(shù)據(jù)庫(kù)備份和恢復(fù)　63
習(xí)題　64
第3章　MySQL表結(jié)構(gòu)的管理　66
3．1　MySQL數(shù)據(jù)類型　66
3．1．1　MySQL整數(shù)類型　66
3．1．2　MySQL小數(shù)類型　67
3．1．3　MySQL字符串類型　67
3．1．4　MySQL日期類型　68
3．1．5　MySQL復(fù)合類型　71
3．1．6　MySQL二進(jìn)制類型　72
3．1．7　選擇合適的數(shù)據(jù)類型　72
3．2　創(chuàng)建表　73
3．2．1　設(shè)置約束　74
3．2．2　設(shè)置自增型字段　78
3．2．3　其他選項(xiàng)的設(shè)置　78
3．2．4　創(chuàng)建“選課系統(tǒng)”數(shù)據(jù)庫(kù)表　79
3．2．5　復(fù)制一個(gè)表結(jié)構(gòu)　80
3．3　修改表結(jié)構(gòu)　81
3．3．1　修改字段相關(guān)信息　81
3．3．2　修改約束條件　82
3．3．3　修改表的其他選項(xiàng)　83
3．3．4　修改表名　83
3．4　刪除表　84
3．5　索引　84
3．5．1　理解索引　84
3．5．2　索引關(guān)鍵字的選取原則　87
3．5．3　索引與約束　88
3．5．4　創(chuàng)建索引　88
3．5．5　刪除索引　90
習(xí)題　90
第4章　表記錄的更新操作　91
4．1　表記錄的插入　91
4．1．1　使用insert語句插入新記錄　91
4．1．2　更新操作與字符集　94
4．1．3　關(guān)于自增型字段　94
4．1．4　批量插入多條記錄　95
4．1．5　使用insert…select插入結(jié)果集　96
4．1．6　使用replace插入新記錄　96
4．2　表記錄的修改　97
4．3　表記錄的刪除　98
4．3．1　使用delete刪除表記錄　98
4．3．2　使用truncate清空表記錄　98
4．4　MySQL特殊字符序列　100
習(xí)題　102
第5章　表記錄的檢索　103
5．1　select語句概述　103
5．1．1　使用select子句指定字段列表　104
5．1．2　使用謂詞過濾記錄　105
5．1．3　使用from子句指定數(shù)據(jù)源　105
5．1．4　多表連接　109
5．2　使用where子句過濾結(jié)果集　109
5．2．1　使用單一的條件過濾結(jié)果集　110
5．2．2　is NULL運(yùn)算符　110
5．2．3　select語句與字符集　111
5．2．4　使用邏輯運(yùn)算符　112
5．2．5　使用like進(jìn)行模糊查詢　115
5．3　使用order by子句對(duì)結(jié)果集排序　116
5．4　使用聚合函數(shù)匯總結(jié)果集　117
5．5　使用group by子句對(duì)記錄分組統(tǒng)計(jì)　118
5．5．1　group by子句與聚合函數(shù)　118
5．5．2　group by子句與having子句　119
5．5．3　group by子句與group_concat()函數(shù)　120
5．5．4　group by子句與with rollup選項(xiàng)　120
5．6　合并結(jié)果集　121
5．7　子查詢　122
5．7．1　子查詢與比較運(yùn)算符　122
5．7．2　子查詢與in運(yùn)算符　123
5．7．3　子查詢與exists邏輯運(yùn)算符　124
5．7．4　子查詢與any運(yùn)算符　125
5．7．5　子查詢與all運(yùn)算符　126
5．8　選課系統(tǒng)綜合查詢　126
5．9　使用正則表達(dá)式模糊查詢　129
5．10　全文檢索　131
5．10．1　全文檢索的簡(jiǎn)單應(yīng)用　132
5．10．2　全文檢索方式　135
5．10．3　布爾檢索模式的復(fù)雜應(yīng)用　135
5．10．4　MySQL全文檢索的注意事項(xiàng)　137
5．10．5　InnoDB表的全文檢索　137
習(xí)題　138
第三篇　MySQL編程
第6章　MySQL編程基礎(chǔ)　140
6．1　MySQL編程基礎(chǔ)知識(shí)　140
6．1．1　常量　140
6．1．2　用戶自定義變量　142
6．1．3　運(yùn)算符與表達(dá)式　147
6．1．4　begin-end語句塊　149
6．1．5　重置命令結(jié)束標(biāo)記　149
6．2　自定義函數(shù)　150
6．2．1　創(chuàng)建自定義函數(shù)的語法格式　150
6．2．2　函數(shù)的創(chuàng)建與調(diào)用　151
6．2．3　函數(shù)的維護(hù)　153
6．2．4　條件控制語句　154
6．2．5　循環(huán)語句　156
6．3　系統(tǒng)函數(shù)　160
6．3．1　數(shù)學(xué)函數(shù)　160
6．3．2　字符串函數(shù)　162
6．3．3　數(shù)據(jù)類型轉(zhuǎn)換函數(shù)　168
6．3．4　條件控制函數(shù)　169
6．3．5　系統(tǒng)信息函數(shù)　170
6．3．6　日期和時(shí)間函數(shù)　171
6．3．7　其他常用的MySQL函數(shù)　177
6．4　中文全文檢索的模擬實(shí)現(xiàn)　179
習(xí)題　181
第7章　視圖與觸發(fā)器　183
7．1　視圖　183
7．1．1　創(chuàng)建視圖　183
7．1．2　查看視圖的定義　184
7．1．3　視圖在“選課系統(tǒng)”中的應(yīng)用　185
7．1．4　視圖的作用　186
7．1．5　刪除視圖　187
7．1．6　檢查視圖　187
7．1．7　local與cascade檢查視圖　188
7．2　觸發(fā)器　189
7．2．1　準(zhǔn)備工作　190
7．2．2　使用觸發(fā)器實(shí)現(xiàn)檢查約束　191
7．2．3　使用觸發(fā)器維護(hù)冗余數(shù)據(jù)　192
7．2．4　使用觸發(fā)器模擬外鍵級(jí)聯(lián)選項(xiàng)　193
7．2．5　查看觸發(fā)器的定義　194
7．2．6　刪除觸發(fā)器　195
7．2．7　使用觸發(fā)器的注意事項(xiàng)　195
7．3　臨時(shí)表　196
7．3．1　臨時(shí)表概述　196
7．3．2　臨時(shí)表的創(chuàng)建、查看與刪除　196
7．3．3　“選課系統(tǒng)”中臨時(shí)表的使用　197
7．3．4　使用臨時(shí)表的注意事項(xiàng)　198
7．4　派生表(derived table)　198
7．5　子查詢、視圖、臨時(shí)表、派生表　199
習(xí)題　200
第8章　存儲(chǔ)過程與游標(biāo)　201
8．1　存儲(chǔ)過程　201
8．1．1　創(chuàng)建存儲(chǔ)過程的語法格式　201
8．1．2　存儲(chǔ)過程的調(diào)用　202
8．1．3　“選課系統(tǒng)”的存儲(chǔ)過程　203
8．1．4　查看存儲(chǔ)過程的定義　206
8．1．5　刪除存儲(chǔ)過程　208
8．1．6　存儲(chǔ)過程與函數(shù)的比較　208
8．2　錯(cuò)誤觸發(fā)條件和錯(cuò)誤處理　209
8．2．1　自定義錯(cuò)誤處理程序　209
8．2．2　自定義錯(cuò)誤觸發(fā)條件　211
8．2．3　自定義錯(cuò)誤處理程序說明　212
8．3　游標(biāo)　212
8．3．1　使用游標(biāo)　212
8．3．2　游標(biāo)在“選課系統(tǒng)”中的使用　213
8．4　預(yù)處理SQL語句　215
8．4．1　預(yù)處理SQL語句使用步驟　215
8．4．2　“選課系統(tǒng)”中預(yù)處理SQL語句的使用　216
8．4．3　預(yù)處理SQL語句的復(fù)雜應(yīng)用　217
8．4．4　靜態(tài)SQL語句與預(yù)處理SQL語句　219
8．5　存儲(chǔ)程序的說明　219
習(xí)題　220
第9章　事務(wù)機(jī)制與鎖機(jī)制　221
9．1　事務(wù)機(jī)制　221
9．1．1　事務(wù)機(jī)制的必要性　221
9．1．2　關(guān)閉MySQL自動(dòng)提交　222
9．1．3　回滾　223
9．1．4　提交　224
9．1．5　事務(wù)　225
9．1．6　保存點(diǎn)　226
9．1．7　“選課系統(tǒng)”中的事務(wù)　229
9．2　鎖機(jī)制　231
9．2．1　鎖機(jī)制的必要性　231
9．2．2　MySQL鎖機(jī)制的基礎(chǔ)知識(shí)　232
9．2．3　MyISAM表的表級(jí)鎖　235
9．2．4　InnoDB表的行級(jí)鎖　238
9．2．5　“選課系統(tǒng)”中的行級(jí)鎖　240
9．2．6　InnoDB表的意向鎖　242
9．2．7　InnoDB行級(jí)鎖與索引之間的關(guān)系　243
9．2．8　間隙鎖與死鎖　245
9．2．9　死鎖與鎖等待　247
9．3　事務(wù)的ACID特性　249
9．3．1　事務(wù)的ACID特性　249
9．3．2　事務(wù)的隔離級(jí)別與并發(fā)問題　250
9．3．3　設(shè)置事務(wù)的隔離級(jí)別　251
9．3．4　使用間隙鎖避免幻讀現(xiàn)象　255
9．4　事務(wù)與鎖機(jī)制注意事項(xiàng)257
習(xí)題　257
第四篇　綜合實(shí)訓(xùn)
第10章　網(wǎng)上選課系統(tǒng)的開發(fā)　260
10．1　PHP預(yù)備知識(shí)　260
10．1．1　為何選用B/S結(jié)構(gòu)以及PHP腳本語言　260
10．1．2　PHP腳本語言概述　261
10．1．3　PHP腳本程序的工作流程　262
10．1．4　Web服務(wù)器的部署　264
10．1．5　注意事項(xiàng)　266
10．2　軟件開發(fā)生命周期SDLC　267
10．3　網(wǎng)上選課系統(tǒng)的系統(tǒng)規(guī)劃　267
10．3．1　網(wǎng)上選課系統(tǒng)的目標(biāo)　267
10．3．2　網(wǎng)上選課系統(tǒng)的可行性分析　268
10．3．3　網(wǎng)上選課系統(tǒng)的項(xiàng)目進(jìn)度表　268
10．3．4　網(wǎng)上選課系統(tǒng)的人員分工　269
10．4　網(wǎng)上選課系統(tǒng)的系統(tǒng)分析　269
10．4．1　網(wǎng)上選課系統(tǒng)的功能需求分析　270
10．4．2　網(wǎng)上選課系統(tǒng)的非功能需求分析　272
10．5　網(wǎng)上選課系統(tǒng)的系統(tǒng)設(shè)計(jì)　272
10．6　網(wǎng)上選課系統(tǒng)的系統(tǒng)實(shí)施　274
10．6．1　準(zhǔn)備工作　274
10．6．2　制作PHP連接MySQL服務(wù)器函數(shù)　275
10．6．3　制作PHP權(quán)限系統(tǒng)函數(shù)　276
10．6．4　首頁(yè)index．php的開發(fā)　277
10．6．5　教師注冊(cè)模塊的開發(fā)　278
10．6．6　登錄模塊的開發(fā)　281
10．6．7　注銷模塊的開發(fā)　283
10．6．8　添加班級(jí)模塊的開發(fā)　283
10．6．9　學(xué)生注冊(cè)模塊的開發(fā)　284
10．6．10　密碼重置模塊　285
10．6．11　申報(bào)課程模塊　286
10．6．12　課程列表顯示模塊　287
10．6．13　審核申報(bào)課程　289
10．6．14　取消已審核課程　290
10．6．15　瀏覽自己申報(bào)的課程　290
10．6．16　刪除課程　291
10．6．17　學(xué)生選修或者調(diào)換已經(jīng)審核的課程　292
10．6．18　查看自己選修的課程　293
10．6．19　取消選修課程　294
10．6．20　查看課程的學(xué)生信息列表　295
10．6．21　查看選修人數(shù)少于30人的課程信息　296
10．7　界面設(shè)計(jì)與MVC模式　296
10．8　網(wǎng)上選課系統(tǒng)的測(cè)試　298
習(xí)題　　299
第五篇　MySQL管理及維護(hù)
第11章　MySQL日志與事件　302
11．1　MySQL日志　302
11．1．1　數(shù)據(jù)皆需要緩存　302
11．1．2　MySQL錯(cuò)誤日志　303
11．1．3　MySQL普通查詢?nèi)罩尽?03
11．1．4　MySQL慢查詢?nèi)罩尽?04
11．1．5　MySQL慢查詢?nèi)罩镜牟榭础?05
11．2　二進(jìn)制日志　307
11．2．1　啟動(dòng)二進(jìn)制日志　307
11．2．2　二進(jìn)制日志文件內(nèi)容的查看　308
11．2．3　獲取當(dāng)前的二進(jìn)制日志文件及偏移位置　310
11．2．4　使用二進(jìn)制日志恢復(fù)數(shù)據(jù)庫(kù)　310
11．2．5　與MySQL二進(jìn)制日志有關(guān)的參數(shù)　311
11．2．6　二進(jìn)制日志的清理　312
11．3　InnoDB事務(wù)日志　312
11．4　日志文件的備份　314
11．5　MySQL事件　314
11．5．1　創(chuàng)建MySQL事件　314
11．5．2　開啟MySQL事件調(diào)度器　316
11．5．3　查看MySQL事件的定義　316
11．5．4　刪除MySQL事件的定義　317
習(xí)題　317
第12章　MySQL備份與恢復(fù)　319
12．1　備份與恢復(fù)概述　319
12．1．1　防止數(shù)據(jù)丟失的方法　319
12．1．2　數(shù)據(jù)備份概述　320
12．2　邏輯備份與邏輯恢復(fù)　321
12．2．1　使用select…into outfile…備份表數(shù)據(jù)　321
12．2．2　恢復(fù)表數(shù)據(jù)　322
12．2．3　使用mysqldump備份數(shù)據(jù)庫(kù)　325
12．2．4　邏輯備份綜合示例　326
12．2．5　二進(jìn)制日志恢復(fù)數(shù)據(jù)庫(kù)的綜合示例　327
12．3　物理備份與熱備份　329
12．3．1　物理備份的所需文件　329
12．3．2　MyISAM表物理備份、熱備份工具　329
12．3．3　InnoDB表物理備份、熱備份工具　330
12．3．4　Xtrabackup的工作原理　331
12．3．5　使用Xtrabackup實(shí)現(xiàn)完全備份與增量備份　333
12．3．6　使用Xtrabackup實(shí)現(xiàn)數(shù)據(jù)庫(kù)恢復(fù)　333
12．4　MySQL復(fù)制(replication)　336
12．4．1　MySQL復(fù)制的實(shí)現(xiàn)原理　337
12．4．2　MySQL復(fù)制的實(shí)現(xiàn)　337
12．4．3　復(fù)制線程的狀態(tài)　339
12．4．4　與復(fù)制有關(guān)的文件　341
12．4．5　復(fù)制的配置　342
12．4．6　MySQL復(fù)制模式　344
12．4．7　自增型字段沖突問題　345
12．4．8　事件損壞　345
12．4．9　小結(jié)　346
12．5　備份策略　346
習(xí)題　347
第13章　MySQL安全管理　348
13．1　MySQL安全管理　348
13．2　MySQL賬戶與權(quán)限表　350
13．2．1　grant命令語法格式　350
13．2．2　創(chuàng)建MySQL服務(wù)實(shí)例級(jí)別的賬戶　351
13．2．3　創(chuàng)建MySQL數(shù)據(jù)庫(kù)級(jí)別的賬戶　353
13．2．4　創(chuàng)建MySQL表級(jí)別的賬戶　356
13．2．5　創(chuàng)建MySQL字段級(jí)別的賬戶　357
13．2．6　創(chuàng)建MySQL存儲(chǔ)程序級(jí)別的賬戶　359
13．2．7　注意事項(xiàng)　360
13．2．8　host權(quán)限表　361
13．3　MySQL賬戶管理　362
13．3．1　使用create user創(chuàng)建MySQL賬戶　362
13．3．2　查看MySQL賬戶信息　362
13．3．3　刪除已有賬戶　363
13．3．4　修改已有賬戶的信息　363
13．3．5　MySQL賬戶的資源次數(shù)限制　364
13．3．6　使用更新語句修改MySQL賬戶信息　365
13．3．7　權(quán)限的生效時(shí)機(jī)　365
13．4　MySQL安全管理的注意事項(xiàng)　365
13．4．1　MySQL賬戶名重名問題　366
13．4．2　root賬號(hào)密碼丟失問題　368
13．4．3　消除MySQL安全隱患　368
13．4．4　MySQL安全管理的其他注意事項(xiàng)　370
習(xí)題　371
第14章　MySQL優(yōu)化　372
14．1　基礎(chǔ)知識(shí)　372
14．1．1　影響數(shù)據(jù)庫(kù)系統(tǒng)性能的組件　372
14．1．2　參數(shù)信息與狀態(tài)信息　373
14．1．3　緩存機(jī)制　373
14．1．4　緩存的分類　374
14．1．5　超時(shí)　375
14．2　MySQL連接的優(yōu)化　377
14．3　緩存的優(yōu)化　380
14．3．1　查詢緩存Query Cache　380
14．3．2　結(jié)果集緩存　382
14．3．3　排序緩存　382
14．3．4　join連接緩存　384
14．3．5　表緩存Cache與表結(jié)構(gòu)定義緩存Cache　385
14．3．6　表掃描緩存buffer　387
14．3．7　MyISAM索引緩存buffer　388
14．3．8　日志緩存　390
14．3．9　預(yù)讀機(jī)制　391
14．3．10　延遲更新　393
14．3．11　內(nèi)存表與臨時(shí)表　396
14．4　InnoDB優(yōu)化　398
14．4．1　InnoDB緩存池　398
14．4．2　InnoDB緩存池內(nèi)部結(jié)構(gòu)　399
14．4．3　InnoDB緩存池預(yù)熱　400
14．4．4　InnoDB緩存池的狀態(tài)信息　401
14．4．5　常見的InnoDB參數(shù)設(shè)置　402
14．4．6　InnoDB實(shí)時(shí)監(jiān)控　402
14．5　優(yōu)化表　403
14．5．1　優(yōu)化表結(jié)構(gòu)　403
14．5．2　表的拆分　403
14．5．3　分區(qū)　404
14．5．4　表分析與表檢查　406
14．5．5　NULL值對(duì)統(tǒng)計(jì)信息的影響　409
14．5．6　記錄的格式　409
14．6　SQL語句優(yōu)化　411
14．6．1　了解SQL的執(zhí)行頻率　411
14．6．2　定位執(zhí)行效率較低的SQL語句　413
14．6．3　分析select語句　413
14．6．4　索引與select語句　416
14．6．5　分頁(yè)與select　418
14．6．6　SQL語句其他注意事項(xiàng)　420
14．6．7　profiling性能分析工具　421
14．7　鎖資源的優(yōu)化　422
14．7．1　表級(jí)鎖資源競(jìng)爭(zhēng)　422
14．7．2　表級(jí)鎖資源競(jìng)爭(zhēng)的解決方案　422
14．7．3　行級(jí)鎖的資源競(jìng)爭(zhēng)　423
14．7．4　行級(jí)鎖資源競(jìng)爭(zhēng)的解決方案　423
14．7．5　禁用InnoDB間隙鎖　424
14．7．6　事務(wù)監(jiān)控與鎖監(jiān)控　424
14．7．7　元數(shù)據(jù)鎖metadata locks　425
第15章　MySQL Cluster　427
15．1　MySQL Cluster簡(jiǎn)介　427
15．1．1　MySQL集群的組成　427
15．1．2　MySQL集群架構(gòu)　429
15．2　MySQL Cluster環(huán)境搭建　429
15．2．1　準(zhǔn)備工作　429
15．2．2　數(shù)據(jù)節(jié)點(diǎn)的配置　430
15．2．3　SQL節(jié)點(diǎn)的配置　431
15．2．4　管理節(jié)點(diǎn)的配置　433
15．2．5　MySQL集群的啟動(dòng)　434
15．2．6　集群測(cè)試　437
參考文獻(xiàn)　439