目 錄
第1章 初識PostgreSQL 1
1.1 數據庫基礎 1
1.1.1什么是數據庫 1
1.1.2表 2
1.1.3數據類型 2
1.1.4主鍵 2
1.1.5什么是開源 2
1.2 數據庫技術構成 3
1.2.1數據庫系統 3
1.2.2SQL語言 4
1.2.3數據庫訪問技術 5
1.3 什么是PostgreSQL 6
1.3.1客戶機-服務器軟件 6
1.3.2PostgreSQL發展歷程 6
1.3.3PostgreSQL的優勢 7
1.4 如何學習PostgreSQL 7
第2章 PostgreSQL 9.6的安裝與配置 9
2.1PostgreSQL 9.6的新功能 9
2.2 安裝與啟動PostgreSQL9.6 10
2.2.1下載PostgreSQL 9.6安裝包 10
2.2.2安裝PostgreSQL 9.6 13
2.2.3啟動PostgreSQL服務器 15
2.3pgAdmin 4的基本操作 17
2.3.1pgAdmin 4的啟動與連接 18
2.3.2pgAdmin 4的界面簡介 19
2.3.3配置PostgreSQL服務器的屬性 24
2.3.4執行SQL查詢語句 25
2.4 常見問題及解答 25
疑問1：連接pgAdmin時提示“服務器未監聽”的錯誤怎么辦？ 26
疑問2：如何修改服務器登錄密碼？ 26
2.5 本章小結 26
2.6 經典習題 26
第3章 數據庫的基本操作 27
3.1 創建數據庫 27
3.1.1使用對象瀏覽器創建數據庫 27
3.1.2使用SQL創建數據庫 29
3.2 修改數據庫的屬性 31
3.2.1使用對象瀏覽器修改數據庫的屬性 31
3.2.2使用SQL語句修改數據庫的屬性 32
3.3 刪除數據庫 32
3.3.1使用對象瀏覽器刪除數據庫 32
3.3.2使用SQL語句刪除數據庫 33
3.4 綜合案例——數據庫的創建和刪除 33
3.5 常見問題及解答 35
疑問1：如何使用SQL語句創建具有一定條件的數據庫？ 35
疑問2：使用DROP語句時需要注意什么問題？ 35
3.6 經典習題 35
第4章 數據表的基本操作 36
4.1 創建數據表 36
4.1.1創建數據表的基本方法 36
4.1.2使用主鍵約束 41
4.1.3使用外鍵約束 43
4.1.4使用非空約束 45
4.1.5使用唯一性約束 45
4.1.6使用默認約束 47
4.2 修改數據表 47
4.2.1修改表名 48
4.2.2修改字段的數據類型 48
4.2.3修改字段名 50
4.2.4添加字段 51
4.2.5刪除字段 52
4.2.6刪除表的外鍵約束 53
4.3 刪除數據表 54
4.3.1刪除沒有被關聯的表 54
4.3.2刪除被其他表關聯的主表 55
4.4 綜合案例——數據表的基本操作 56
4.5 常見問題及解答 62
疑問1：刪除和修改表時需注意什么問題？ 62
疑問2：每一個表中都要有一個主鍵嗎？ 62
4.6 經典習題 62
第5章 數據類型和運算符 64
5.1PostgreSQL數據類型介紹 64
5.1.1整數類型 64
5.1.2浮點數類型 65
5.1.3任意精度類型 66
5.1.4日期與時間類型 66
5.1.5字符串類型 71
5.1.6二進制類型 73
5.1.7布爾類型 73
5.1.8數組類型 74
5.2 如何選擇數據類型 75
5.3 常見運算符介紹 75
5.3.1運算符概述 75
5.3.2算術運算符 76
5.3.3比較運算符 77
5.3.4邏輯運算符 83
5.3.5運算符的優先級 84
5.4 綜合案例——運算符的使用 85
5.5 常見問題及解答 87
疑問1：PostgreSQL中可以存儲文件嗎？ 87
疑問2：二進制和普通字符串的區別是什么？ 87
5.6 經典習題 87
第6章 PostgreSQL函數 88
6.1PostgreSQL函數簡介 88
6.2 數學函數 88
6.2.1絕對值函數ABS(x)和返回圓周率的函數PI() 89
6.2.2平方根函數SQRT(x)和求余函數MOD(x,y) 89
6.2.3獲取整數的函數CEIL(x)、CEILING(x)和FLOOR(x) 90
6.2.4四舍五入函數ROUND(x)和ROUND(x,y) 91
6.2.5符號函數SIGN(x) 91
6.2.6冪運算函數POW(x,y)、POWER(x,y)和EXP(x) 92
6.2.7對數運算函數LOG(x) 92
6.2.8角度與弧度相互轉換的函數RADIANS(x)和DEGREES(x) 93
6.2.9正弦函數SIN(x)和反正弦函數ASIN(x) 93
6.2.10余弦函數COS(x)和反余弦函數ACOS(x) 94
6.2.11正切函數TAN(x)、反正切函數ATAN(x)和余切函數COT(x) 94
6.3 字符串函數 95
6.3.1計算字符串字符數和字符串長度的函數 95
6.3.2合并字符串函數CONCAT(s1,s2,…)、CONCAT_WS(x,s1,s2,…) 96
6.3.3獲取指定長度的字符串的函數LEFT(s,n)和RIGHT(s,n) 97
6.3.4填充字符串的函數LPAD(s1,len,s2)和RPAD(s1,len,s2) 98
6.3.5刪除空格的函數LTRIM(s)、RTRIM(s)和TRIM(s) 98
6.3.6刪除指定字符串的函數TRIM(s1 FROM s) 99
6.3.7重復生成字符串的函數REPEAT(s,n) 100
6.3.8替換函數REPLACE(s,s1,s2) 100
6.3.9獲取子串的函數SUBSTRING(s,n,len) 100
6.3.10匹配子串開始位置的函數POSITION(str1 IN str) 101
6.3.11字符串逆序函數REVERSE(s) 101
6.4 日期和時間函數 101
6.4.1獲取當前日期的函數和獲取當前時間的函數 101
6.4.2獲取當前日期和時間的函數 102
6.4.3獲取日期指定值的函數 103
6.4.4日期和時間的運算操作 104
6.5 條件判斷函數 106
6.6 系統信息函數 107
6.6.1獲取PostgreSQL版本號 107
6.6.2獲取用戶名的函數 107
6.7 加密和解密函數 108
6.7.1加密函數MD5(str) 108
6.7.2加密函數ENCODE(str,pswd_str) 108
6.7.3解密函數DECODE(crypt_str,pswd_str) 108
6.8 改變數據類型的函數 109
6.9 綜合案例——PostgreSQL函數的使用 109
6.10常見問題及解答 112
疑問1：如何從日期時間值中獲取年、月、日等部分日期或時間值？ 112
疑問2：如何計算年齡？ 112
6.11經典習題 113
第7章 插入、更新與刪除數據 114
7.1 插入數據 114
7.1.1為表的所有字段插入數據 114
7.1.2為表的指定字段插入數據 116
7.1.3同時插入多條記錄 117
7.1.4將查詢結果插入表中 118
7.2 更新數據 120
7.3 刪除數據 122
7.4 綜合案例——記錄的插入、更新和刪除 124
7.5 常見問題及解答 130
疑問1：插入記錄時可以不指定字段名稱嗎？ 130
疑問2：更新或者刪除表時必須指定WHERE子句嗎？ 130
7.6 經典習題 130
第8章 查詢數據 132
8.1 基本查詢語句 132
8.2 單表查詢 134
8.2.1查詢所有字段 134
8.2.2查詢指定字段 135
8.2.3查詢指定記錄 137
8.2.4帶IN關鍵字的查詢 138
8.2.5帶BETWEEN AND的范圍查詢 139
8.2.6帶LIKE的字符匹配查詢 140
8.2.7空值查詢 141
8.2.8帶AND的多條件查詢 142
8.2.9帶OR的多條件查詢 143
8.2.10查詢結果不重復 144
8.2.11對查詢結果排序 145
8.2.12分組查詢 147
8.2.13用LIMIT限制查詢結果的數量 149
8.3 使用集合函數查詢 150
8.3.1COUNT()函數 150
8.3.2SUM()函數 152
8.3.3AVG()函數 153
8.3.4MAX()函數 154
8.3.5MIN()函數 155
8.4 連接查詢 156
8.4.1內連接查詢 156
8.4.2外連接查詢 158
8.4.3復合條件連接查詢 160
8.5 子查詢 161
8.5.1帶ANY、SOME關鍵字的子查詢 161
8.5.2帶ALL關鍵字的子查詢 161
8.5.3帶EXISTS關鍵字的子查詢 162
8.5.4帶IN關鍵字的子查詢 164
8.5.5帶比較運算符的子查詢 165
8.6 合并查詢結果 166
8.7 為表和字段取別名 168
8.7.1為表取別名 168
8.7.2為字段取別名 170
8.8 使用正則表達式查詢 171
8.8.1查詢以特定字符或字符串開頭的記錄 172
8.8.2查詢以特定字符或字符串結尾的記錄 173
8.8.3用“.”符號替代字符串中的任意一個字符 173
8.8.4使用“*”和“ ”匹配多個字符 174
8.8.5匹配指定字符串 174
8.8.6匹配指定字符中的任意一個 175
8.8.7匹配指定字符以外的字符 176
8.8.8使用{M}或者{M,N}指定字符串連續出現的次數 176
8.9 綜合案例——數據表查詢操作 177
8.10常見問題及解答 183
疑問1：DISTINCT可以應用于所有列嗎？ 183
疑問2：ORDER BY可以和LIMIT混合使用嗎？ 183
疑問3：什么時候使用單引號？ 183
疑問4：在WHERE子句中必須使用圓括號嗎？ 183
疑問5：為什么通配符格式正確卻沒有查找出符合條件的記錄？ 183
8.11經典習題 184
第9章 索引 185
9.1 索引簡介 185
9.1.1索引的含義和特點 185
9.1.2索引的分類 186
9.1.3索引的設計原則 187
9.2 創建索引 187
9.2.1使用pgAdmin創建索引 187
9.2.2使用SQL語句創建索引 188
9.3 重命名索引 191
9.4 刪除索引 192
9.5 綜合案例——創建索引 193
9.6 常見問題及解答 195
疑問1：應該如何正確使用索引？ 195
疑問2：為什么盡量使用短索引？ 195
9.7 經典習題 195
第10章 視圖 197
10.1視圖概述 197
10.1.1視圖的含義 197
10.1.2視圖的作用 198
10.2創建視圖 198
10.2.1創建視圖的語法形式 199
10.2.2在單表上創建視圖 199
10.2.3在多表上創建視圖 200
10.3查看視圖 201
10.3.1使用pgAdmin圖形化工具查看視圖 201
10.3.2使用SQL語句在views表中查看視圖詳細信息 202
10.4刪除視圖 203
10.4.1使用pgAdmin圖形化工具刪除視圖 203
10.4.2使用SQL語句刪除視圖 203
10.5綜合案例——視圖應用 204
10.6常見問題及解答 208
疑問1：PostgreSQL中視圖和表的區別以及聯系是什么？ 208
疑問2：如何修改視圖的屬性？ 208
10.7經典習題 209
第11章 觸發器 210
11.1什么是觸發器和觸發器函數 210
11.2創建觸發器 211
11.3查看和修改觸發器 212
11.4使用觸發器 213
11.5刪除觸發器 214
11.6綜合案例——觸發器的使用 215
11.7常見問題及解答 217
疑問1：使用觸發器時應注意什么問題？ 217
疑問2：為什么要及時刪除不需要的觸發器？ 218
11.8經典習題 218
第12章 事務處理與并發控制 219
12.1事務管理簡介 219
12.1.1事務的含義 219
12.1.2事務的屬性 220
12.1.3事務塊管理的常用語句 220
12.1.4事務的應用案例 221
12.2PostgreSQL的并發控制 222
12.3鎖機制 223
12.3.1鎖的類型 224
12.3.2死鎖 225
12.3.3鎖的應用案例 226
12.4常見問題及解答 227
疑問1：事務和鎖在應用上的區別是什么？ 227
疑問2：事務和鎖有什么關系？ 227
12.5經典習題 227
第13章 PostgreSQL用戶管理 228
13.1組角色管理 228
13.1.1創建組角色 228
13.1.2查看和修改組角色 230
13.1.3刪除組角色 231
13.2角色的各種權限 232
13.3 賬戶管理 234
13.3.1創建用戶 234
13.3.2刪除用戶 236
13.3.3修改用戶密碼 237
13.4組角色和用戶權限管理 238
13.4.1對組角色授權 238
13.4.2對用戶授權 239
13.4.3收回組角色權限 239
13.4.4收回用戶權限 240
13.5數據庫權限管理 240
13.5.1修改數據庫的擁有者 240
13.5.2增加用戶的數據表權限 241
13.6綜合案例——綜合管理用戶權限 243
13.7常見問題及解答 244
疑問1：如何撤銷用戶對數據表的操作權限？ 244
疑問2：組角色和登錄角色之間的區別是什么？ 244
疑問3：如何使用超級用戶權限？ 245
13.8經典習題 245
第14章 數據備份與還原 246
14.1數據備份 246
14.1.1使用pgAdmin 4備份數據庫 246
14.1.2使用pg_dump工具備份數據庫 248
14.1.3使用pg_dumpall工具備份整個服務器的數據庫 250
14.2數據還原 251
14.2.1使用pgAdmin 4還原數據庫 251
14.2.2使用psql還原數據庫 252
14.2.3使用pg_restore快速還原數據庫 254
14.3數據庫遷移 255
14.3.1相同版本的PostgreSQL數據庫之間的遷移 255
14.3.2不同版本的PostgreSQL數據庫之間的遷移 255
14.3.3不同類型數據庫之間的遷移 255
14.4綜合案例——數據的備份與恢復 256
14.5常見問題及解答 256
疑問1：pgdump備份的文件只能在PostgreSQL中使用嗎？ 256
疑問2：使用pgAdmin恢復數據庫時需要注意什么問題？ 256
14.6經典習題 257
第15章 性能優化 258
15.1優化簡介 258
15.2優化查詢 258
15.2.1分析查詢語句EXPLAIN 258
15.2.2索引對查詢速度的影響 259
15.2.3優化子查詢 260
15.3優化數據庫結構 261
15.3.1將字段很多的表分解成多個表 261
15.3.2增加中間表 262
15.3.3增加冗余字段 263
15.3.4優化插入記錄的速度 263
15.3.5分析表的統計信息 264
15.4優化PostgreSQL服務器 265
15.4.1優化服務器硬件 265
15.4.2優化PostgreSQL的參數 265
15.5綜合案例——優化PostgreSQL服務器 266
15.6常見問題及解答 267
疑問1：是不是索引建立得越多越好？ 267
疑問2：如何更新緩沖區的緩沖？ 267
15.7經典習題 267
第16章 高可用、負載均衡和數據復制 268
16.1常見的數據同步解決方案 268
16.2日志傳送備用服務器 269
16.2.1日志傳送概述 269
16.2.2設置備用服務器 270
16.3數據的流復制 271
16.3.1流復制概述 271
16.3.2身份驗證 272
16.4數據的同步復制 272
16.4.1同步復制概述 272
16.4.2同步復制的應用案例 273
16.5常見問題及解答 277
疑問1：如何監控系統中鎖的情況？ 277
疑問2：什么是數據分區？ 277
第17章 服務器配置與數據庫監控 278
17.1服務器配置 278
17.1.1服務器配置的文件 278
17.1.2連接與認證 280
17.1.3資源消耗 281
17.1.4預寫式日志 283
17.1.5查詢規劃 284
17.1.6錯誤報告和日志 285
17.1.7運行時統計 286
17.1.8自動清理 287
17.1.9客戶端連接配置 287
17.1.10鎖管理 288
17.1.11版本和平臺兼容性 289
17.2監控數據庫的活動 289
17.2.1配置統計收集器 289
17.2.2查看收集到的統計信息 290
17.3監控磁盤的使用 293
17.3.1監控磁盤的使用量 293
17.3.2磁盤滿導致的失效 295
17.4綜合實戰——查看監控磁盤的使用情況 295
17.5常見問題及解答 297
疑問1：當服務器配置出現沖突時采用什么優先級？ 297
疑問2：為什么有時磁盤沒有寫滿性能卻很差？ 297
第18章 內部結構 298
18.1PostgreSQL的內部概述 298
18.1.1查詢經過的路徑 298
18.1.2建立連接 299
18.1.3分析器階段 300
18.1.4PostgreSQL規則系統 300
18.1.5規劃器/優化器 301
18.1.6執行器 302
18.2PostgreSQL的內部系統表 303
18.2.1數據表 303
18.2.2系統視圖 308
18.3PostgreSQL的內部前端/后端協議 312
18.3.1概述 312
18.3.2消息流 312
18.3.3消息數據類型 317
18.3.4消息格式 317
18.3.5錯誤和通知消息字段 326
18.4PostgreSQL的編碼約定 326
18.4.1格式 326
18.4.2報告服務器里的錯誤 327
18.4.3錯誤消息風格指南 328
18.5基因查詢優化器 331
18.5.1作為復雜優化問題的查詢處理 331
18.5.2基因算法 331
18.5.3PostgreSQL里的基因查詢優化 332
18.6索引訪問方法接口定義 333
18.6.1索引的系統表記錄 333
18.6.2索引訪問方法函數 334
18.6.3索引掃描 337
18.6.4索引唯一性檢查 338
18.6.5索引開銷估計函數 339
18.7GiST索引 340
18.7.1GiST簡介 340
18.7.2可擴展性 340
18.7.3實現方法 341
18.8數據庫物理存儲 341
18.8.1數據庫文件布局 341
18.8.2TOAST 342
18.8.3數據庫分頁文件 344
18.9BKI后端接口 346
18.9.1BKI文件格式 346
18.9.2BKI命令 346
18.9.3系統初始化的BKI文件結構 347
18.9.4例子 348
18.10常見問題及解答 348
疑問1：什么是GIN索引？ 348
疑問2：使用索引鎖需要注意什么？ 348
新書資訊