PostgreSQL即學(xué)即用（第3版）

前言 　　xiii
第 1 章　基礎(chǔ)知識(shí) 　　1
1．1　為什么應(yīng)該選擇PostgreSQL 　　1
1．2　不適用PostgreSQL 的場(chǎng)景 　　3
1．3　如何獲得PostgreSQL 　　3
1．4　管理工具 　　4
1．4．1　psql 　　4
1．4．2　pgAdmin 　　4
1．4．3　phpPgAdmin 　　6
1．4．4　Adminer 　　6
1．5　PostgreSQL 數(shù)據(jù)庫(kù)對(duì)象 　　7
1．6　最新版本的PostgreSQL 中引入的新特性 　　12
1．6．1　為什么要升級(jí) 　　12
1．6．2　PostgreSQL 10 中引入的新特性 　　12
1．6．3　PostgreSQL 9．6 中引入的新特性 　　13
1．6．4　PostgreSQL 9．5 中引入的新特性 　　14
1．6．5　PostgreSQL 9．4 中引入的新特性 　　15
1．7　數(shù)據(jù)庫(kù)驅(qū)動(dòng)程序 　　17
1．8　如何獲得幫助 　　18
1．9　PostgreSQL 的主要衍生版本 　　18
第 2 章　數(shù)據(jù)庫(kù)管理 　　19
2．1　配置文件 　　19
2．1．1　讓配置文件生效 　　20
2．1．2　postgresql．conf 　　21
2．1．3　pg_hba．conf　　25
2．2　連接管理 　　27
2．3　角色 　　29
2．3．1　創(chuàng)建可登錄角色 　　30
2．3．2　創(chuàng)建組角色 　　30
2．4　創(chuàng)建database 　　33
2．4．1　模板數(shù)據(jù)庫(kù) 　　33
2．4．2　schema 的使用 　　34
2．5　權(quán)限管理 　　36
2．5．1　權(quán)限的類(lèi)型 　　36
2．5．2　入門(mén)介紹 　　36
2．5．3　GRANT 　　37
2．5．4　默認(rèn)權(quán)限 　　38
2．5．5　PostgreSQL 權(quán)限體系中一些與眾不同的特點(diǎn) 　　39
2．6　擴(kuò)展包機(jī)制 　　39
2．6．1　擴(kuò)展包的安裝 　　40
2．6．2　通用擴(kuò)展包 　　42
2．7　備份與恢復(fù) 　　44
2．7．1　使用pg_dump 進(jìn)行有選擇性的備份 　　45
2．7．2　使用pg_dumpall 進(jìn)行全局備份 　　46
2．7．3　數(shù)據(jù)恢復(fù) 　　47
2．8　基于表空間機(jī)制進(jìn)行存儲(chǔ)管理 　　48
2．8．1　表空間的創(chuàng)建 　　49
2．8．2　在表空間之間遷移對(duì)象 　　49
2．9　禁止的行為 　　49
2．9．1　切記不要?jiǎng)h除PostgreSQL 系統(tǒng)文件 　　50
2．9．2　不要把操作系統(tǒng)管理員權(quán)限授予PostgreSQL 的系統(tǒng)賬號(hào) 　　50
2．9．3　不要把shared_buffers 緩存區(qū)設(shè)置得過(guò)大 　　51
2．9．4　不要將PostgreSQL 服務(wù)器的偵聽(tīng)端口設(shè)為一個(gè)已被其他程序占用的端口 　　51
第3 章　psql 工具 　　52
3．1　環(huán)境變量 　　52
3．2　psql 的兩種操作模式：交互模式與非交互模式 　　 53
3．3　定制psql 操作環(huán)境 　　54
3．3．1　自定義psql 界面提示符 　　55
3．3．2　語(yǔ)句執(zhí)行時(shí)間統(tǒng)計(jì) 　　56
3．3．3　事務(wù)自動(dòng)提交 　　56
3．3．4　命令別名 　　56
3．3．5　取出前面執(zhí)行過(guò)的命令行 　　57
3．4　psql 使用技巧 　　57
3．4．1　執(zhí)行shell 命令 　　57
3．4．2　用watch 命令重復(fù)執(zhí)行語(yǔ)句 　　57
3．4．3　顯示對(duì)象信息 　　58
3．4．4　行轉(zhuǎn)列視圖 　　59
3．4．5　執(zhí)行動(dòng)態(tài)SQL 　　59
3．5　使用psql 實(shí)現(xiàn)數(shù)據(jù)的導(dǎo)入和導(dǎo)出 　　60
3．5．1　使用psql 進(jìn)行數(shù)據(jù)導(dǎo)入 　　60
3．5．2　使用psql 進(jìn)行數(shù)據(jù)導(dǎo)出 　　61
3．5．3　從外部程序復(fù)制數(shù)據(jù)以及將數(shù)據(jù)復(fù)制到外部程序 　　62
3．6　使用psql 制作簡(jiǎn)單的報(bào)表 　　62
第4 章　pgAdmin 的使用　　65
4．1　pgAdmin 入門(mén) 　　65
4．1．1　功能概覽 　　66
4．1．2　如何連接到PostgreSQL 服務(wù)器 　　67
4．1．3　pgAdmin 界面導(dǎo)航 　　68
4．2　pgAdmin 功能特性介紹 　　68
4．2．1　根據(jù)表定義自動(dòng)生成SQL 語(yǔ)句 　　69
4．2．2　在pgAdmin3 中調(diào)用psql 　　69
4．2．3　在pgAdmin3 中編輯postgresql．conf 和pg_hba．conf 文件 　　70
4．2．4　創(chuàng)建數(shù)據(jù)庫(kù)對(duì)象并設(shè)置權(quán)限 　　70
4．2．5　數(shù)據(jù)導(dǎo)入和導(dǎo)出 　　73
4．2．6　備份與恢復(fù) 　　74
4．3　pgScript 腳本機(jī)制 　　78
4．4　以圖形化方式解釋執(zhí)行計(jì)劃 　　80
4．5　使用pgAgent 執(zhí)行定時(shí)任務(wù) 　　81
4．5．1　安裝pgAgent 　　81
4．5．2　規(guī)劃定時(shí)任務(wù) 　　82
4．5．3　一些有用的pgAgent 相關(guān)查詢(xún)語(yǔ)句 　　84
第5 章　數(shù)據(jù)類(lèi)型 　　85
5．1　數(shù)值類(lèi)型 　　85
5．1．1　serial 類(lèi)型 　　85
5．1．2　生成數(shù)組序列的函數(shù) 　　86
5．2　文本類(lèi)型 　　87
5．2．1　字符串函數(shù) 　　87
5．2．2　將字符串拆分為數(shù)組、表或者子字符串 　　88
5．2．3　正則表達(dá)式和模式匹配 　　89
5．3　時(shí)間類(lèi)型 　　90
5．3．1　時(shí)區(qū)詳解 　　92
5．3．2　日期時(shí)間類(lèi)型的運(yùn)算符和函數(shù) 　　93
5．4　數(shù)組類(lèi)型 　　95
5．4．1　數(shù)組構(gòu)造函數(shù) 　　96
5．4．2　將數(shù)組元素展開(kāi)為記錄行 　　97
5．4．3　數(shù)組的拆分與連接 　　98
5．4．4　引用數(shù)組中的元素 　　99
5．4．5　數(shù)組包含性檢查 　　99
5．5　區(qū)間類(lèi)型 　　100
5．5．1　離散區(qū)間和連續(xù)區(qū)間 　　100
5．5．2　原生支持的區(qū)間類(lèi)型 　　100
5．5．3　定義區(qū)間的方法 　　101
5．5．4　定義含區(qū)間類(lèi)型字段的表 　　102
5．5．5　適用于區(qū)間類(lèi)型的運(yùn)算符 　　102
5．6　JSON 數(shù)據(jù)類(lèi)型 　　103
5．6．1　插入JSON 數(shù)據(jù) 　　103
5．6．2　查詢(xún)JSON 數(shù)據(jù) 　　104
5．6．3　輸出JSON 數(shù)據(jù) 　　105
5．6．4　JSON 類(lèi)型的二進(jìn)制版本：jsonb 　　106
5．6．5　編輯JSONB 類(lèi)型的數(shù)據(jù) 　　108
5．7　XML 數(shù)據(jù)類(lèi)型 　　109
5．7．1　插入XML 數(shù)據(jù)　　110
5．7．2　查詢(xún)XML 數(shù)據(jù)　　110
5．8　全文檢索 　　112
5．8．1　FTS 配置庫(kù)　　113
5．8．2　TSVector 原始文本向量 　　115
5．8．3　TSQueries 檢索條件向量 　　117
5．8．4　使用全文檢索 　　119
5．8．5　對(duì)檢索結(jié)果進(jìn)行排序 　　120
5．8．6　全文檢索向量信息的裁減 　　121
5．8．7　全文檢索機(jī)制對(duì)JSON 和JSONB 數(shù)據(jù)類(lèi)型的支持 　　122
5．9　自定義數(shù)據(jù)類(lèi)型和復(fù)合數(shù)據(jù)類(lèi)型 　　122
5．9．1　所有表都有一個(gè)對(duì)應(yīng)的自定義數(shù)據(jù)類(lèi)型 　　123
5．9．2　構(gòu)建自定義數(shù)據(jù)類(lèi)型 　　124
5．9．3　復(fù)合類(lèi)型中的空值處理 　　124
5．9．4　為自定義數(shù)據(jù)類(lèi)型構(gòu)建運(yùn)算符和函數(shù) 　　125
第6 章　表、約束和索引 　　126
6．1　表 　　126
6．1．1　基本的建表操作 　　126
6．1．2　繼承表 　　128
6．1．3　原生分區(qū)表支持 　　128
6．1．4　無(wú)日志表 　　130
6．1．5　TYPE OF 　　131
6．2　約束機(jī)制 　　132
6．2．1　外鍵約束 　　132
6．2．2　唯一性約束 　　133
6．2．3　check 約束 　　133
6．2．4　排他性約束 　　133
6．3　索引 　　134
6．3．1　PostgreSQL 原生支持的索引類(lèi)型 　　135
6．3．2　運(yùn)算符類(lèi) 　　137
6．3．3　函數(shù)索引 　　138
6．3．4　基于部分記錄的索引 　　138
6．3．5　多列索引 　　140
第7 章　PostgreSQL 的特色SQL 語(yǔ)法 　　141
7．1　視圖 　　141
7．1．1　單表視圖 　　142
7．1．2　使用觸發(fā)器來(lái)更新視圖 　　143
7．1．3　物化視圖 　　145
7．2　靈活易用的PostgreSQL 專(zhuān)有SQL 語(yǔ)法 　　146
7．2．1　DISTINCT ON 　　147
7．2．2　LIMIT 和OFFSET 關(guān)鍵字 　　147
7．2．3　簡(jiǎn)化的類(lèi)型轉(zhuǎn)換語(yǔ)法 　　148
7．2．4　一次性插入多條記錄 　　148
7．2．5　使用ILIKE 實(shí)現(xiàn)不區(qū)分大小寫(xiě)的查詢(xún) 　　148
7．2．6　使用ANY 運(yùn)算符進(jìn)行數(shù)組搜索 　　149
7．2．7　可以返回結(jié)果集的函數(shù) 　　149
7．2．8　限制對(duì)繼承表的DELETE、UPDATE、INSERT 操作的影響范圍 　　150
7．2．9　DELETE USING 語(yǔ)法 　　150
7．2．10　將修改影響到的記錄行返回給用戶(hù) 　　150
7．2．11　UPSERT：INSERT 時(shí)如果主鍵沖突則進(jìn)行UPDATE 　　151
7．2．12　在查詢(xún)中使用復(fù)合數(shù)據(jù)類(lèi)型 　　152
7．2．13　使用$ 文本引用符 　　153
7．2．14　DO 　　154
7．2．15　適用于聚合操作的FILTER 子句 　　155
7．2．16　查詢(xún)百分位數(shù)與最高出現(xiàn)頻率數(shù) 　　157
7．3　窗口函數(shù) 　　158
7．3．1　PARTITION BY 子句 　　159
7．3．2　ORDER BY 子句 　　160
7．4　CTE 表達(dá)式 　　162
7．4．1　基本CTE 用法介紹 　　162
7．4．2　可寫(xiě)CTE 用法介紹 　　163
7．4．3　遞歸CTE 用法介紹 　　163
7．5　LATERAL 橫向關(guān)聯(lián)語(yǔ)法 　　165
7．6　WITH ORDINALITY 子句 　　166
7．7　GROUPING SETS、CUBE 和ROLLUP 語(yǔ)法 　　168
第8 章　函數(shù)編寫(xiě) 　　171
8．1　PostgreSQL 函數(shù)功能剖析 　　171
8．1．1　函數(shù)功能基礎(chǔ)知識(shí)介紹 　　171
8．1．2　觸發(fā)器和觸發(fā)器函數(shù) 　　174
8．1．3　聚合操作 　　175
8．1．4　受信與非受信語(yǔ)言 　　176
8．2　使用SQL 語(yǔ)言來(lái)編寫(xiě)函數(shù) 　　177
8．2．1　編寫(xiě)基本的SQL 函數(shù) 　　177
8．2．2　使用SQL 語(yǔ)言編寫(xiě)聚合函數(shù) 　　179
8．3　使用PL/pgSQL 語(yǔ)言編寫(xiě)函數(shù)　　181
8．3．1　編寫(xiě)基礎(chǔ)的PL/pgSQL 函數(shù) 　　181
8．3．2　使用PL/pgSQL 編寫(xiě)觸發(fā)器函數(shù) 　　181
8．4　使用PL/Python 語(yǔ)言編寫(xiě)函數(shù) 　　182
8．5　使用PL/V8、PL/CoffeeScript 以及PL/LiveScript 語(yǔ)言來(lái)編寫(xiě)函數(shù) 　　184
8．5．1　編寫(xiě)基本的函數(shù) 　　185
8．5．2　使用PL/V8 來(lái)編寫(xiě)聚合函數(shù) 　　186
8．5．3　使用PL/V8 編寫(xiě)窗口函數(shù) 　　187
第9 章　查詢(xún)性能調(diào)優(yōu) 　　190
9．1　通過(guò)EXPLAIN 命令查看語(yǔ)句執(zhí)行計(jì)劃 　　190
9．1．1　EXPLAIN 選項(xiàng) 　　190
9．1．2　運(yùn)行示例以及輸出內(nèi)容解釋 　　191
9．1．3　圖形化展示執(zhí)行計(jì)劃 　　193
9．2　搜集語(yǔ)句的執(zhí)行統(tǒng)計(jì)信息 　　195
9．3　編寫(xiě)更好的SQL 語(yǔ)句 　　196
9．3．1　在SELECT 語(yǔ)句中濫用子查詢(xún) 　　196
9．3．2　盡量避免使用SELECT * 語(yǔ)法 　　198
9．3．3　善用CASE 語(yǔ)法 　　199
9．3．4　使用Filter 語(yǔ)法替代CASE 語(yǔ)法 　　200
9．4　并行化語(yǔ)句執(zhí)行 　　201
9．4．1　并行化的執(zhí)行計(jì)劃是什么樣子 　　201
9．4．2　并行化掃描 　　204
9．4．3　并行化關(guān)聯(lián)操作 　　205
9．5　人工干預(yù)規(guī)劃器生成執(zhí)行計(jì)劃的過(guò)程 　　205
9．5．1　策略設(shè)置 　　205
9．5．2　你的索引被用到了嗎 　　206
9．5．3　表的統(tǒng)計(jì)信息 　　207
9．5．4　磁盤(pán)頁(yè)的隨機(jī)訪問(wèn)成本以及磁盤(pán)驅(qū)動(dòng)器的性能 　　209
9．6　數(shù)據(jù)緩存機(jī)制 　　209
第 10 章　復(fù)制與外部數(shù)據(jù) 　　211
10．1　復(fù)制功能概覽 　　211
10．1．1　復(fù)制功能涉及的術(shù)語(yǔ) 　　212
10．1．2　復(fù)制機(jī)制的演進(jìn) 　　214
10．1．3　第三方復(fù)制解決方案 　　214
10．2　復(fù)制環(huán)境的搭建 　　215
10．2．1　主服務(wù)器的配置 　　215
10．2．2　為從屬服務(wù)器配置全量復(fù)制環(huán)境 　　216
10．2．3　啟動(dòng)流復(fù)制進(jìn)程 　　217
10．2．4　使用邏輯復(fù)制實(shí)現(xiàn)部分表或者部分database 的復(fù)制 　　218
10．3　外部數(shù)據(jù)封裝器 　　219
10．3．1　查詢(xún)平面文件 　　220
10．3．2　以不規(guī)則數(shù)組的形式查詢(xún)不規(guī)范的平面文件 　　220
10．3．3　查詢(xún)其他PostgreSQL 服務(wù)實(shí)例上的數(shù)據(jù) 　　222
10．3．4　使用ogr_fdw 查詢(xún)其他二維表形式的數(shù)據(jù)源 　　224
10．3．5　查詢(xún)非傳統(tǒng)數(shù)據(jù)源 　　226
附錄A　PostgreSQL 的安裝 　　228
附錄B　PostgreSQL 自帶的命令行工具 　　233
作者簡(jiǎn)介 　　242
封面介紹 　　242