目 錄
第1章 初識PostgreSQL. 1
1.1 數(shù)據(jù)庫基礎(chǔ)... 1
1.1.1 什么是數(shù)據(jù)庫... 1
1.1.2 表... 2
1.1.3 數(shù)據(jù)類型... 2
1.1.4 主鍵... 2
1.1.5 什么是開源... 3
1.2 數(shù)據(jù)庫技術(shù)構(gòu)成... 3
1.2.1 數(shù)據(jù)庫系統(tǒng)... 3
1.2.2 SQL語言... 3
1.2.3 數(shù)據(jù)庫訪問技術(shù)... 5
1.3 什么是PostgreSQL.. 6
1.3.1 客戶機-服務器軟件... 6
1.3.2 PostgreSQL發(fā)展歷程... 6
1.3.3 PostgreSQL的優(yōu)勢... 7
1.4 如何學習PostgreSQL.. 7
第2章 PostgreSQL 11的安裝與配置... 9
2.1 安裝與啟動PostgreSQL 11.2. 9
2.1.1 下載PostgreSQL 11.2安裝包... 9
2.1.2 安裝PostgreSQL 11.2. 11
2.1.3 啟動PostgreSQL服務器... 16
2.2 pgAdmin 4的基本操作... 18
2.2.1 pgAdmin 4的啟動與連接... 18
2.2.2 pgAdmin 4的界面簡介... 19
2.2.3 配置PostgreSQL服務器的屬性... 25
2.2.4 執(zhí)行SQL查詢語句... 26
2.3 使用psql工具登錄PostgreSQL數(shù)據(jù)庫... 27
2.4 Linux系統(tǒng)下安裝PostgreSQL 11. 28
2.5 常見問題及解答... 29
疑問1：連接pgAdmin時提示“服務器未監(jiān)聽”的錯誤怎么辦？... 29
疑問2：如何修改服務器登錄密碼？... 29
2.6 本章小結(jié)... 30
2.7 經(jīng)典習題... 30
第3章 數(shù)據(jù)庫的基本操作... 31
3.1 創(chuàng)建數(shù)據(jù)庫... 31
3.1.1 使用對象瀏覽器創(chuàng)建數(shù)據(jù)庫... 31
3.1.2 使用SQL創(chuàng)建數(shù)據(jù)庫... 34
3.2 修改數(shù)據(jù)庫的屬性... 35
3.2.1 使用對象瀏覽器修改數(shù)據(jù)庫的屬性... 35
3.2.2 使用SQL語句修改數(shù)據(jù)庫屬性... 36
3.3 刪除數(shù)據(jù)庫... 37
3.3.1 使用對象瀏覽器刪除數(shù)據(jù)庫... 37
3.3.2 使用SQL語句刪除數(shù)據(jù)庫... 38
3.4 綜合案例——數(shù)據(jù)庫的創(chuàng)建和刪除... 39
3.5 常見問題及解答... 40
疑問1：如何使用SQL語句創(chuàng)建具有一定條件的數(shù)據(jù)庫？... 40
疑問2：使用DROP語句要注意什么問題？... 40
3.6 經(jīng)典習題... 41
第4章 數(shù)據(jù)表的基本操作... 42
4.1 創(chuàng)建數(shù)據(jù)表... 42
4.1.1 創(chuàng)建數(shù)據(jù)表... 42
4.1.2 使用主鍵約束... 47
4.1.3 使用外鍵約束... 48
4.1.4 使用非空約束... 51
4.1.5 使用性約束... 51
4.1.6 使用默認約束... 53
4.2 修改數(shù)據(jù)表... 53
4.2.1 修改表名... 53
4.2.2 修改字段的數(shù)據(jù)類型... 54
4.2.3 修改字段名... 55
4.2.4 添加字段... 56
4.2.5 刪除字段... 58
4.2.6 刪除表的外鍵約束... 58
4.3 刪除數(shù)據(jù)表... 60
4.3.1 刪除沒有被關(guān)聯(lián)的表... 60
4.3.2 刪除被其他表關(guān)聯(lián)的主表... 61
4.4 PostgreSQL 11的新特性——新增帶默認值的字段不再重寫數(shù)據(jù)表... 63
4.5 綜合案例——數(shù)據(jù)表的基本操作... 64
4.6 常見問題及解答... 71
疑問1：表刪除和修改操作時需注意什么問題？... 71
疑問2：每一個表中都要有一個主鍵嗎？... 71
4.7 經(jīng)典習題... 71
第5章 數(shù)據(jù)類型和運算符... 73
5.1 PostgreSQL數(shù)據(jù)類型介紹... 73
5.1.1 整數(shù)類型... 73
5.1.2 浮點數(shù)類型... 74
5.1.3 任意精度類型... 75
5.1.4 日期與時間類型... 75
5.1.5 字符串類型... 82
5.1.6 二進制類型... 84
5.1.7 布爾類型... 85
5.1.8 數(shù)組類型... 85
5.2 如何選擇數(shù)據(jù)類型... 86
5.3 常見運算符介紹... 87
5.3.1 運算符概述... 87
5.3.2 算術(shù)運算符... 88
5.3.3 比較運算符... 90
5.3.4 邏輯運算符... 97
5.3.5 運算符的優(yōu)先級... 99
5.4 綜合案例——運算符的使用... 100
5.5 常見問題及解答... 102
疑問1：PostgreSQL中可以存儲文件嗎？... 102
疑問2：二進制和普通字符串的區(qū)別是什么？... 102
5.6 經(jīng)典習題... 102
第6章 PostgreSQL函數(shù)... 103
6.1 PostgreSQL函數(shù)簡介... 103
6.2 數(shù)學函數(shù)... 103
6.2.1 值函數(shù)ABS(x)和返回圓周率的函數(shù)PI() 104
6.2.2 平方根函數(shù)SQRT(x)和求余函數(shù)MOD(x,y) 104
6.2.3 獲取整數(shù)的函數(shù)CEIL(x)、CEILING(x)和FLOOR(x) 105
6.2.4 四舍五入函數(shù)ROUND(x)和ROUND(x,y) 106
6.2.5 符號函數(shù)SIGN(x) 107
6.2.6 冪運算函數(shù)POW(x,y)、POWER(x,y)和EXP(x) 108
6.2.7 對數(shù)運算函數(shù)LOG(x) 109
6.2.8 角度與弧度相互轉(zhuǎn)換的函數(shù)RADIANS(x)和DEGREES(x) 109
6.2.9 正弦函數(shù)SIN(x)和反正弦函數(shù)ASIN(x) 110
6.2.10 余弦函數(shù)COS(x)和反余弦函數(shù)ACOS(x) 111
6.2.11 正切函數(shù)、反正切函數(shù)和余切函數(shù)... 112
6.3 字符串函數(shù)... 113
6.3.1 計算字符串字符數(shù)的函數(shù)和字符串長度的函數(shù)... 113
6.3.2 合并字符串函數(shù)CONCAT(s1,s2,…)、CONCAT_WS(x,s1,s2,…) 114
6.3.3 獲取指定長度的字符串的函數(shù)LEFT(s,n)和RIGHT(s,n) 115
6.3.4 填充字符串的函數(shù)LPAD(s1,len,s2)和RPAD(s1,len,s2) 116
6.3.5 刪除空格的函數(shù)LTRIM(s)、RTRIM(s)和TRIM(s) 117
6.3.6 刪除指定字符串的函數(shù)TRIM(s1 FROM s) 119
6.3.7 重復生成字符串的函數(shù)REPEAT(s,n) 119
6.3.8 替換函數(shù)REPLACE(s,s1,s2) 120
6.3.9 獲取子串的函數(shù)SUBSTRING(s,n,len) 120
6.3.10 匹配子串開始位置的函數(shù)POSITION(str1 IN str) 121
6.3.11 字符串逆序的函數(shù)REVERSE(s) 121
6.4 日期和時間函數(shù)... 122
6.4.1 獲取當前日期的函數(shù)和獲取當前時間的函數(shù)... 122
6.4.2 獲取當前日期和時間的函數(shù)... 123
6.4.3 獲取日期的指定值的函數(shù)EXTRACT(type FROM d) 124
6.4.4 日期和時間的運算操作... 126
6.5 條件判斷函數(shù)... 129
6.6 系統(tǒng)信息函數(shù)... 130
6.6.1 獲取PostgreSQL版本號... 130
6.6.2 獲取用戶名的函數(shù)... 131
6.7 加密函數(shù)... 131
6.7.1 加密函數(shù)MD5(str) 131
6.7.2 加密函數(shù)ENCODE(str,pswd_str) 132
6.7.3 解密函數(shù)DECODE(crypt_str,pswd_str) 132
6.8 改變數(shù)據(jù)類型的函數(shù)... 133
6.9 綜合案例——PostgreSQL函數(shù)的使用... 133
6.10 常見問題及解答... 137
疑問1：如何從日期時間值中獲取年、月、日等部分日期或時間值？... 137
疑問2：如何計算年齡？... 137
6.11 經(jīng)典習題... 137
第7章 插入、更新與刪除數(shù)據(jù)... 139
7.1 插入數(shù)據(jù)... 139
7.1.1 為表的所有字段插入數(shù)據(jù)... 139
7.1.2 為表的指定字段插入數(shù)據(jù)... 142
7.1.3 同時插入多條記錄... 143
7.1.4 將查詢結(jié)果插入到表中... 145
7.2 更新數(shù)據(jù)... 147
7.3 刪除數(shù)據(jù)... 149
7.4 綜合案例——記錄的插入、更新和刪除... 152
7.5 常見問題及解答... 158
疑問1：插入記錄時可以不指定字段名稱嗎？... 158
疑問2：更新或者刪除表時必須指定WHERE子句嗎？... 158
7.6 經(jīng)典習題... 158
第8章 查詢數(shù)據(jù)... 160
8.1 基本查詢語句... 160
8.2 單表查詢... 162
8.2.1 查詢所有字段... 163
8.2.2 查詢指定字段... 164
8.2.3 查詢指定記錄... 166
8.2.4 帶IN關(guān)鍵字的查詢... 168
8.2.5 帶BETWEEN AND的范圍查詢... 170
8.2.6 帶LIKE的字符匹配查詢... 171
8.2.7 查詢空值... 172
8.2.8 帶AND的多條件查詢... 174
8.2.9 帶OR的多條件查詢... 175
8.2.10 查詢結(jié)果不重復... 176
8.2.11 對查詢結(jié)果排序... 177
8.2.12 分組查詢... 180
8.2.13 用LIMIT限制查詢結(jié)果的數(shù)量... 182
8.3 使用集合函數(shù)查詢... 184
8.3.1 COUNT()函數(shù)... 184
8.3.2 SUM()函數(shù)... 186
8.3.3 AVG()函數(shù)... 187
8.3.4 MAX()函數(shù)... 188
8.3.5 MIN()函數(shù)... 190
8.4 連接查詢... 191
8.4.1 內(nèi)連接查詢... 191
8.4.2 外連接查詢... 194
8.4.3 復合條件連接查詢... 196
8.5 子查詢... 198
8.5.1 帶ANY、SOME關(guān)鍵字的子查詢... 198
8.5.2 帶ALL關(guān)鍵字的子查詢... 199
8.5.3 帶EXISTS關(guān)鍵字的子查詢... 199
8.5.4 帶IN關(guān)鍵字的子查詢... 201
8.5.5 帶比較運算符的子查詢... 203
8.6 合并查詢結(jié)果... 205
8.7 為表和字段取別名... 208
8.7.1 為表取別名... 208
8.7.2 為字段取別名... 210
8.8 使用正則表達式查詢... 212
8.8.1 查詢以特定字符或字符串開頭的記錄... 213
8.8.2 查詢以特定字符或字符串結(jié)尾的記錄... 213
8.8.3 用符號"."來替代字符串中的任意一個字符... 214
8.8.4 使用"*"和" "來匹配多個字符... 215
8.8.5 匹配指定字符串... 216
8.8.6 匹配指定字符中的任意一個... 217
8.8.7 匹配指定字符以外的字符... 218
8.8.8 使用{M}或者{M,N}來指定字符串連續(xù)出現(xiàn)的次數(shù)... 219
8.9 PostgreSQL 11新特性——psql新增gdesc選項... 220
8.10 綜合案例——數(shù)據(jù)表查詢操作... 220
8.11 常見問題及解答... 228
疑問1：DISTINCT可以應用于所有的列嗎？... 228
疑問2：ORDER BY可以和LIMIT混合使用嗎？... 229
疑問3：什么時候使用單引號？... 229
疑問4：在WHERE子句中必須使用圓括號嗎？... 229
疑問5：為什么使用通配符格式正確，卻沒有查找出符合條件的記錄？... 229
8.12 經(jīng)典習題... 229
第9章 索引... 230
9.1 索引簡介... 230
9.1.1 索引的含義和特點... 230
9.1.2 索引的分類... 231
9.1.3 索引的設(shè)計原則... 232
9.2 創(chuàng)建索引... 232
9.2.1 使用pgAdmin創(chuàng)建索引... 232
9.2.2 使用SQL語句創(chuàng)建索引... 233
9.3 重命名索引... 236
9.4 刪除索引... 237
9.5 PostgreSQL 11的新特性1 ——創(chuàng)建索引時支持INCLUDE方式... 238
9.6 PostgreSQL 11的新特性2——執(zhí)行并行創(chuàng)建索引... 239
9.7 綜合案例——創(chuàng)建索引... 240
9.8 常見問題及解答... 242
疑問1：索引對數(shù)據(jù)庫性能如此重要，應該如何使用它？... 242
疑問2：為什么盡量使用短索引？... 242
9.9 經(jīng)典習題... 242
第10章 視圖... 244
10.1 視圖概述... 244
10.1.1 視圖的含義... 244
10.1.2 視圖的作用... 245
10.2 創(chuàng)建視圖... 246
10.2.1 創(chuàng)建視圖的語法形式... 246
10.2.2 在單表上創(chuàng)建視圖... 246
10.2.3 在多表上創(chuàng)建視圖... 248
10.3 查看視圖... 248
10.3.1 使用pgAdmin圖形化工具查看視圖... 248
10.3.2 在views表中查看視圖詳細信息... 249
10.4 刪除視圖... 250
10.4.1 使用pgAdmin圖形化工具刪除視圖... 250
10.4.2 使用SQL語句刪除視圖... 251
10.5 綜合案例——視圖應用... 252
10.6 常見問題及解答... 257
疑問1：在PostgreSQL中，視圖和表的區(qū)別以及聯(lián)系是什么？... 257
疑問2：如何修改視圖的屬性？... 257
10.7 經(jīng)典習題... 257
第11章 觸發(fā)器... 258
11.1 什么是觸發(fā)器和觸發(fā)器函數(shù)... 258
11.2 創(chuàng)建觸發(fā)器... 259
11.3 查看和修改觸發(fā)器... 260
11.4 觸發(fā)器的使用... 261
11.5 刪除觸發(fā)器... 262
11.6 綜合案例——觸發(fā)器使用... 263
11.7 常見問題及解答... 266
疑問1：使用觸發(fā)器時應注意什么問題？... 266
疑問2：為什么要及時刪除不再需要的觸發(fā)器... 267
11.8 經(jīng)典習題... 267
第12章 事務處理與并發(fā)控制... 268
12.1 事務管理簡介... 268
12.1.1 事務的含義... 268
12.1.2 事務的屬性... 269
12.1.3 事務塊管理的常用語句... 269
12.1.4 事務的應用案例... 270
12.2 PostgreSQL的并發(fā)控制... 271
12.3 鎖機制... 273
12.3.1 鎖的類型... 273
12.3.2 死鎖... 274
12.4 鎖的應用案例... 275
12.5 PostgreSQL 11的新特性——支持存儲過程... 276
12.6 常見問題及解答... 279
疑問1：事務和鎖在應用上的區(qū)別是什么？... 279
疑問2：事務和鎖有什么關(guān)系？... 279
12.7 經(jīng)典習題... 279
第13章 PostgreSQL用戶管理... 280
13.1 組角色管理... 280
13.1.1 創(chuàng)建組角色... 280
13.1.2 查看和修改組角色... 282
13.1.3 刪除組角色... 284
13.2 角色的各種權(quán)限... 286
13.3 賬戶管理... 287
13.3.1 創(chuàng)建用戶... 287
13.3.2 刪除用戶... 290
13.3.3 修改用戶密碼... 291
13.4 組角色和用戶權(quán)限管理... 292
13.4.1 對組角色授權(quán)... 292
13.4.2 對用戶授權(quán)... 293
13.4.3 收回組角色權(quán)限... 294
13.4.4 收回用戶權(quán)限... 294
13.5 數(shù)據(jù)庫權(quán)限管理... 295
13.5.1 修改數(shù)據(jù)庫的擁有者... 295
13.5.2 增加用戶的數(shù)據(jù)表權(quán)限... 297
13.6 PostgreSQL 11的新特性1——新增3個默認角色... 298
13.7 PostgreSQL 11的新特性2——下放4個系統(tǒng)函數(shù)... 299
13.8 綜合案例——綜合管理用戶權(quán)限... 300
13.9 常見問題及解答... 302
疑問1：如何撤銷用戶對數(shù)據(jù)表的操作權(quán)限？... 302
疑問2：組角色和登錄角色之間的區(qū)別是什么？... 302
疑問3：為什么要謹慎使用超級用戶權(quán)限？... 302
13.10 經(jīng)典習題... 302
第14章 數(shù)據(jù)備份與還原... 304
14.1 數(shù)據(jù)備份... 304
14.1.1 使用pgAdmin 4備份數(shù)據(jù)庫... 304
14.1.2 使用pg_dump工具備份數(shù)據(jù)庫... 306
14.1.3 使用pg_dumpall工具備份整個服務器的數(shù)據(jù)庫... 309
14.2 數(shù)據(jù)還原... 310
14.2.1 使用pgAdmin 4還原數(shù)據(jù)庫... 310
14.2.2 使用psql還原數(shù)據(jù)庫... 311
14.2.3 使用pg_restore快速還原數(shù)據(jù)庫... 313
14.3 數(shù)據(jù)庫遷移... 314
14.3.1 相同版本的PostgreSQL數(shù)據(jù)庫之間的遷移... 314
14.3.2 不同版本的PostgreSQL數(shù)據(jù)庫之間的遷移... 314
14.3.3 不同數(shù)據(jù)庫之間遷移... 314
14.4 綜合案例——數(shù)據(jù)的備份與恢復... 315
14.5 常見問題及解答... 315
疑問1：pgdump備份的文件只能在PostgreSQL中使用嗎？... 315
疑問2：使用pgAdmin恢復數(shù)據(jù)庫時需要注意什么問題？... 316
14.6 經(jīng)典習題... 316
第15章 性能優(yōu)化... 317
15.1 優(yōu)化簡介... 317
15.2 優(yōu)化查詢... 317
15.2.1 分析查詢語句EXPLAIN.. 318
15.2.2 索引對查詢速度的影響... 318
15.2.3 優(yōu)化子查詢... 319
15.3 優(yōu)化數(shù)據(jù)庫結(jié)構(gòu)... 320
15.3.1 將字段很多的表分解成多個表... 320
15.3.2 增加中間表... 321
15.3.3 增加冗余字段... 322
15.3.4 優(yōu)化插入記錄的速度... 323
15.3.5 分析表的統(tǒng)計信息... 323
15.4 優(yōu)化PostgreSQL服務器... 324
15.4.1 優(yōu)化服務器硬件... 324
15.4.2 優(yōu)化PostgreSQL的參數(shù)... 325
15.5 PostgreSQL 11的新特性1——新增5個記錄錯誤的變量... 325
15.6 PostgreSQL 11的新特性2——增加對JIT編譯的支持... 326
15.7 PostgreSQL 11的新特性3——支持并行哈希連接... 328
15.8 綜合案例——優(yōu)化PostgreSQL服務器... 331
15.9 常見問題及解答... 332
疑問1：是不是索引建立的越多越好？... 332
疑問2：如何更新緩沖區(qū)的緩沖？... 332
15.10 經(jīng)典習題... 332
第16章 高可用、負載均衡和數(shù)據(jù)復制... 333
16.1 常見的數(shù)據(jù)同步解決方案... 333
16.2 日志傳送備用服務器... 334
16.2.1 日志傳送概述... 334
16.2.2 設(shè)置備用服務器... 335
16.3 數(shù)據(jù)的流復制... 336
16.3.1 流復制概述... 336
16.3.2 身份驗證... 337
16.4 數(shù)據(jù)的同步復制... 337
16.4.1 同步復制概述... 338
16.4.2 同步復制的應用案例... 338
16.5 PostgreSQL 11的新特性1——分區(qū)表增加哈希分區(qū)... 342
16.6 PostgreSQL 11的新特性2——分區(qū)表支持創(chuàng)建主鍵和索引... 344
16.7 PostgreSQL 11的新特性3——分區(qū)表支持UPDATE分區(qū)鍵... 346
16.8 常見問題及解答... 347
疑問1：如何監(jiān)控系統(tǒng)中鎖的情況？... 347
疑問2：什么是數(shù)據(jù)分區(qū)？... 348
第17章 服務器配置與數(shù)據(jù)庫監(jiān)控... 349
17.1 服務器配置... 349
17.1.1 服務器配置的文件... 349
17.1.2 連接與認證... 351
17.1.3 資源消耗... 353
17.1.4 預寫式日志... 355
17.1.5 查詢規(guī)劃... 355
17.1.6 錯誤報告和日志... 356
17.1.7 運行時統(tǒng)計... 358
17.1.8 自動清理... 358
17.1.9 客戶端連接默認... 359
17.1.10 鎖管理... 360
17.1.11 版本和平臺兼容性... 360
17.2 監(jiān)控數(shù)據(jù)庫的活動... 360
17.2.1 配置統(tǒng)計收集器... 361
17.2.2 查看收集到的統(tǒng)計信息... 361
17.3 監(jiān)控磁盤的使用... 364
17.3.1 監(jiān)控磁盤的使用量... 364
17.3.2 磁盤滿導致的失效... 366
17.4 綜合實戰(zhàn)——查看監(jiān)控磁盤的使用情況... 367
17.5 常見問題及解答... 368
疑問1：當服務器配置出現(xiàn)沖突時，采用什么方式的優(yōu)先級？... 368
疑問2：為什么有時候磁盤沒有寫滿性能仍然很低？... 369
第18章 內(nèi)部結(jié)構(gòu)... 370
18.1 PostgreSQL的內(nèi)部概述... 370
18.1.1 查詢經(jīng)過的路徑... 370
18.1.2 如何建立連接... 371
18.1.3 分析器階段... 372
18.1.4 PostgreSQL規(guī)則系統(tǒng)... 372
18.1.5 規(guī)劃器/優(yōu)化器... 373
18.1.6 執(zhí)行器... 374
18.2 PostgreSQL的內(nèi)部系統(tǒng)表... 375
18.2.1 數(shù)據(jù)表... 375
18.2.2 系統(tǒng)視圖... 380
18.3 PostgreSQL的內(nèi)部前端/后端協(xié)議... 385
18.3.1 概述... 385
18.3.2 消息流... 385
18.3.3 消息數(shù)據(jù)類型... 390
18.3.4 消息格式... 390
18.3.5 錯誤和通知消息字段... 398
18.4 PostgreSQL的編碼約定... 399
18.4.1 格式... 399
18.4.2 報告服務器里的錯誤... 399
18.4.3 錯誤消息風格指南... 401
18.5 基因查詢優(yōu)化器... 404
18.5.1 作為復雜優(yōu)化問題的查詢處理... 404
18.5.2 基因算法... 404
18.5.3 PostgreSQL里的基因查詢優(yōu)化（GEQO）... 405
18.6 索引訪問方法接口定義... 406
18.6.1 索引的系統(tǒng)表記錄... 406
18.6.2 索引訪問方法函數(shù)... 407
18.6.3 索引掃描... 410
18.6.4 索引性檢查... 411
18.6.5 索引開銷估計函數(shù)... 412
18.7 GiST索引... 413
18.7.1 GiST簡介... 413
18.7.2 GiST的可擴展性... 414
18.7.3 實現(xiàn)方法... 414
18.8 數(shù)據(jù)庫物理存儲... 415
18.8.1 數(shù)據(jù)庫文件布局... 415
18.8.2 TOAST. 416
18.8.3 數(shù)據(jù)庫分頁文件... 417
18.9 BKI后端接口... 420
18.9.1 BKI文件格式... 420
18.9.2 BKI命令... 420
18.9.3 系統(tǒng)初始化的BKI文件結(jié)構(gòu)... 421
18.9.4 例子... 422
18.10 常見問題及解答... 422
疑問1：什么是GIN索引？... 422
疑問2：使用索引鎖需要注意什么？... 422
鄂公網(wǎng)安備 42010302001612號 