SQL 編程思想：基於 5種主流數據庫代碼實現

董旭陽
數據庫架構師、CSDN博客專家，畢業於北京航空航天大學，擁有十多年數據庫管理與開發經驗，專注於數據庫領域的知識分享，榮獲Oracle OCP和Redhat RHCE證書。
目前在一家全球性的金融公司從事數據庫架構設計工作。

第 1 章 一切皆關係.................................................................................................................1
1.1 數據庫 ...................................................................................................................................1
1.1.1 數據庫的發展歷史 ...................................................................................................2
1.1.2 數據庫管理系統 .......................................................................................................3
1.2 關係型數據庫 .......................................................................................................................4
1.2.1 數據結構 ...................................................................................................................4
1.2.2 關係操作 ...................................................................................................................5
1.2.3 完整性約束 ...............................................................................................................5
1.3 SQL 簡介...............................................................................................................................6
1.3.1 SQL 的歷史...............................................................................................................7
1.3.2 語法特性 ...................................................................................................................7
1.3.3 面向集合 ...................................................................................................................8
1.3.4 標準與實現 ...............................................................................................................9
1.4 小結 ..................................................................................................................................... 10

第 2 章 查詢初體驗...............................................................................................................11
2.1 基本檢索功能 ..................................................................................................................... 11
2.1.1 查詢指定字段 ......................................................................................................... 12
2.1.2 查詢全部字段 ......................................................................................................... 13
2.1.3 快速查詢信息 ......................................................................................................... 13
2.2 實現數據過濾 ..................................................................................................................... 14
2.2.1 簡單過濾條件 ......................................................................................................... 15
2.2.2 空值判斷條件 ......................................................................................................... 17
2.2.3 文本模糊查找 ......................................................................................................... 18
2.2.4 組合過濾條件 ......................................................................................................... 21
2.2.5 排除重複數據 ......................................................................................................... 23
2.3 從無序到有序 ..................................................................................................................... 24
2.3.1 基於單個字段排序 .................................................................................................24
2.3.2 基於多個字段排序 .................................................................................................25
2.3.3 基於表達式排序 ..................................................................................................... 26
2.3.4 空值的排序位置 ..................................................................................................... 27
2.3.5 中文的排序方式 ..................................................................................................... 28
2.4 限定結果數量 ..................................................................................................................... 30
2.4.1 Top-N 排行榜.......................................................................................................... 30
2.4.2 數據分頁顯示 ......................................................................................................... 31
2.5 SQL 註釋............................................................................................................................. 34
2.5.1 單行註釋 ................................................................................................................. 34
2.5.2 多行註釋 ................................................................................................................. 34
2.5.3 特殊註釋 ................................................................................................................. 35
2.6 小結 ..................................................................................................................................... 35

第 3 章 邏輯處理功能 ...........................................................................................................36
3.1 函數和運算 ......................................................................................................................... 36
3.1.1 函數概述 ................................................................................................................. 37
3.1.2 數值函數 ................................................................................................................. 37
3.1.3 字符函數 ................................................................................................................. 41
3.1.4 日期函數 ................................................................................................................. 47
3.1.5 轉換函數 ................................................................................................................. 50
3.1.6 案例分析 ................................................................................................................. 51
3.2 使用別名 ............................................................................................................................. 53
3.2.1 列別名 ..................................................................................................................... 53
3.2.2 表別名 ..................................................................................................................... 54
3.3 條件表達式 ......................................................................................................................... 54
3.3.1 簡單 CASE 表達式 .................................................................................................55
3.3.2 搜索 CASE 表達式 .................................................................................................56
3.3.3 DECODE 函數........................................................................................................ 58
3.3.4 IF 函數..................................................................................................................... 59
3.3.5 IIF 函數 ................................................................................................................... 59
3.3.6 案例分析 ................................................................................................................. 60
3.4 小結 ..................................................................................................................................... 61

第 4 章 數據分組與匯總........................................................................................................62
4.1 數據匯總 ............................................................................................................................. 62
4.1.1 聚合函數 ................................................................................................................. 63
4.1.2 使用 COUNT 函數統計行數..................................................................................63
4.1.3 使用 AVG 函數計算ping均值...................................................................................64
4.1.4 使用 SUM 函數計算總和....................................................................................... 66
4.1.5 使用 MAX 函數返回#大值 ..................................................................................66
4.1.6 使用 MIN 函數返回#小值....................................................................................67
4.1.7 使用 LISTAGG 函數連接字符串 ..........................................................................67
4.2 數據分組 ............................................................................................................................. 69
4.2.1 創建數據分組 ......................................................................................................... 69
4.2.2 進行組內匯總 ......................................................................................................... 70
4.2.3 空值分為一組 ......................................................................................................... 71
4.2.4 常見的語法問題 ..................................................................................................... 72
4.3 再次過濾數據 ..................................................................................................................... 73
4.3.1 使用 HAVING 過濾分組結果................................................................................73
4.3.2 WHERE 與 HAVING 的區別 ................................................................................73
4.4 多維數據分析 ..................................................................................................................... 74
4.4.1 小計、合計與總計 .................................................................................................74
4.4.2 交叉統計報表 ......................................................................................................... 75
4.4.3 自定義維度統計 ..................................................................................................... 76
4.4.4 GROUPING 函數.................................................................................................... 78
4.5 案例分析 ............................................................................................................................. 79
4.5.1 案例一：實現行列轉換 ......................................................................................... 79
4.5.2 案例二：銷售數據分析 ......................................................................................... 80
4.6 小結 ..................................................................................................................................... 83

第 5 章 空值問題...................................................................................................................84
5.1 三值邏輯 ............................................................................................................................. 85
5.2 空值的比較 ......................................................................................................................... 86
5.3 空值的分組 ......................................................................................................................... 88
5.4 空值的排序 ......................................................................................................................... 88
5.5 函數中的空值 ..................................................................................................................... 89
5.6 空值處理函數 ..................................................................................................................... 90
5.7 空值與約束 ......................................................................................................................... 93
5.8 案例分析 ............................................................................................................................. 95
5.9 小結 ..................................................................................................................................... 97

第 6 章 連接多個表...............................................................................................................98
6.1 連接的語法與類型 ............................................................................................................. 99
6.1.1 使用 FROM 和 WHERE 連接兩個表....................................................................99
6.1.2 使用 JOIN 和 ON 連接兩個表...............................................................................99
6.1.3 連接查詢的類型 ................................................................................................... 100
6.2 內連接 ............................................................................................................................... 100
6.2.1 等值連接 ............................................................................................................... 100
6.2.2 非等值連接 ........................................................................................................... 101
6.3 外連接 ............................................................................................................................... 101
6.3.1 左外連接 ............................................................................................................... 101
6.3.2 右外連接 ............................................................................................................... 103
6.3.3 全外連接 ............................................................................................................... 104
6.4 交叉連接 ........................................................................................................................... 105
6.5 自然連接 ........................................................................................................................... 106
6.6 自連接 ............................................................................................................................... 107
6.7 連接多個表 ....................................................................................................................... 108
6.8 案例分析 ........................................................................................................................... 109
6.8.1 案例一：生成數字序列 ....................................................................................... 109
6.8.2 案例二：員工考勤記錄 ....................................................................................... 110
6.9 小結 ................................................................................................................................... 112

第 7 章 嵌套子查詢.............................................................................................................113
7.1 查詢中的查詢 ................................................................................................................... 113
7.2 標量子查詢 ....................................................................................................................... 115
7.3 行子查詢 .....................

...................................................................................................... 115
7.4 表子查詢 ........................................................................................................................... 116
7.4.1 WHERE 條件中的子查詢 ....................................................................................116
7.4.2 ALL、ANY 運算符.............................................................................................. 117
7.4.3 FROM 子句中的子查詢....................................................................................... 119
7.5 關聯子查詢 ....................................................................................................................... 121
7.6 橫向子查詢 ....................................................................................................................... 122
7.7 EXISTS 運算符................................................................................................................. 123
7.8 案例分析 ........................................................................................................................... 126
7.8.1 案例一：月度銷售冠#軍 ....................................................................................... 126
7.8.2 案例二：銷售增長之星 ....................................................................................... 127
7.9 小結 ................................................................................................................................... 128

第 8 章 表的集合運算 .........................................................................................................129
8.1 集合運算 ........................................................................................................................... 129
8.2 交集求同 ........................................................................................................................... 130
8.3 並集存異 ........................................................................................................................... 132
8.4 差集排他 ........................................................................................................................... 134
8.5 集合運算與排序 ............................................................................................................... 136
8.6 運算符的優先級 ............................................................................................................... 137
8.7 案例分析 ........................................................................................................................... 139
8.7.1 案例一：優秀員工分析 ......................................................................................... 139
8.7.2 案例二：用戶權限管理 ......................................................................................... 142
8.8 小結 ................................................................................................................................... 144

第 9 章 通用表表達式 .........................................................................................................145
9.1 表即變量 ........................................................................................................................... 145
9.2 強大的遞歸 ....................................................................................................................... 148
9.2.1 遞歸查詢語法 ....................................................................................................... 149
9.2.2 生成數字序列 ....................................................................................................... 149
9.2.3 遍歷層次結構 ....................................................................................................... 150
9.2.4 遞歸的終止 ........................................................................................................... 153
9.3 案例分析：社交網絡關係................................................................................................ 154
9.3.1 數據結構 ............................................................................................................... 154
9.3.2 好友關係分析 ....................................................................................................... 157
9.3.3 粉絲關係分析 ....................................................................................................... 161
9.4 小結 ................................................................................................................................... 163

第 10 章 窗口函數...............................................................................................................164
10.1 窗口函數定義 ................................................................................................................. 164
10.1.1 創建數據分區 ..................................................................................................... 166
10.1.2 分區內的排序 ..................................................................................................... 167
10.1.3 指定窗口大小 ....

................................................................................................. 168
10.1.4 窗口函數分類 ..................................................................................................... 169
10.2 聚合窗口函數 ................................................................................................................. 170
10.2.1 案例分析：移動ping均值 .....................................................................................170
10.2.2 案例分析：累計求和 ......................................................................................... 171
10.3 排名窗口函數 ................................................................................................................. 173
10.3.1 案例分析：分類排名 ......................................................................................... 174
10.3.2 案例分析：累積分佈 ......................................................................................... 176
10.4 取值窗口函數 ................................................................................................................. 177
10.4.1 案例分析：環比、同比分析..............................................................................177
10.4.2 案例分析：複合增長率 .....................................................................................179
10.5 小結 ................................................................................................................................. 181

第 11 章 數據的增刪改合....................................................................................................182
11.1 插入數據 ......................................................................................................................... 182
11.1.1 插入單行記錄 ..................................................................................................... 183
11.1.2 插入多行記錄 ..................................................................................................... 185
11.1.3 複製數據 ............................................................................................................. 186
11.2 更新數據 ......................................................................................................................... 187
11.2.1 單表更新 ............................................................................................................. 187
11.2.2 關聯更新 ............................................................................................................. 188
11.3 刪除數據 ......................................................................................................................... 189
11.3.1 單表刪除 ............................................................................................................. 189
11.3.2 關聯刪除 ............................................................................................................. 189
11.3.3 快速刪除全表數據 ............................................................................................. 190
11.4 合併數據 ......................................................................................................................... 191
11.4.1 標準合併語句 ..................................................................................................... 191
11.4.2 非標準合併語句 .................................................................................................194
11.5 外鍵約束與級聯操作 ..................................................................................................... 195
11.5.1 違反外鍵約束 ..................................................................................................... 195
11.5.2 級聯更新和刪除 .................................................................................................196
11.6 小結 ................................................................................................................................. 198

第 12 章 數據庫事務...........................................................................................................199
12.1 什麼是數據庫事務 ......................................................................................................... 199
12.2 事務的 ACID 屬性.......................................................................................................... 201
12.3 事務控制語句 ................................................................................................................. 201
12.3.1 開始事務 ............................................................................................................. 202
12.3.2 提交事務 ............................................................................................................. 203
12.3.3 撤銷事務 ............................................................................................................. 204
12.3.4 事務保存點 ......................................................................................................... 204
12.4 並發事務與隔離級別 ..................................................................................................... 205
12.4.1 並發問題 .......................

...................................................................................... 205
12.4.2 隔離級別 ............................................................................................................. 206
12.5 案例分析 ......................................................................................................................... 207
12.6 小結 ................................................................................................................................. 210

第 13 章 數據庫設計與實現 ................................................................................................211
13.1 實體關係圖 ..................................................................................................................... 211
13.1.1 實體..................................................................................................................... 212
13.1.2 屬性..................................................................................................................... 212
13.1.3 關係..................................................................................................................... 212
13.1.4 ERD 建模 ............................................................................................................ 213
13.2 規範化設計 ..................................................................................................................... 214
13.2.1 數據異常 ............................................................................................................. 214
13.2.2 第#一範式 ............................................................................................................. 215
13.2.3 第二範式 ............................................................................................................. 216
13.2.4 第三範式 ............................................................................................................. 217
13.2.5 主鍵與外鍵 ......................................................................................................... 218
13.2.6 反規範化 ............................................................................................................. 219
13.3 數據類型 ......................................................................................................................... 219
13.3.1 數字類型 ............................................................................................................. 220
13.3.2 字符串類型 ......................................................................................................... 221
13.3.3 日期時間類型 ..................................................................................................... 222
13.3.4 二進制類型 ......................................................................................................... 223
13.3.5 選擇合適的數據類型 ......................................................................................... 223
13.4 管理數據庫對象 ............................................................................................................. 224
13.4.1 常見對象 ............................................................................................................. 224
13.4.2 管理數據庫 ......................................................................................................... 225
13.4.3 管理模式 ............................................................................................................. 226
13.4.4 管理數據表 ......................................................................................................... 227
13.5 小結 ................................................................................................................................. 231

第 14 章 索引與性能優化....................................................................................................232
14.1 索引的原理 ..................................................................................................................... 232
14.1.1 聚集索引 ............................................................................................................. 234
14.1.2 非聚集索引 ......................................................................................................... 234
14.2 索引的類型 ..................................................................................................................... 235
14.2.1 唯#一索引與非唯#一索引 .....................................................................................235
14.2.2 單列索引與多列索引 ......................................................................................... 235
14.2.3 升序索引與降序索引 ......................................................................................... 236
14.2.4 函數索引 ............................................................................................................. 236
14.3 索引的維護 ..................................................................................................................... 236
14.3.1 創建索引 ....................

......................................................................................... 236
14.3.2 查看索引 ............................................................................................................. 237
14.3.3 刪除索引 ............................................................................................................. 237
14.3.4 註意事項 ............................................................................................................. 238
14.4 執行計劃 ......................................................................................................................... 238
14.4.1 查詢語句的執行過程 ......................................................................................... 238
14.4.2 查看 SQL 語句的執行計劃................................................................................241
14.5 查詢優化技巧 ................................................................................................................. 244
14.5.1 創建合適的索引 .................................................................................................244
14.5.2 避免索引失效 ..................................................................................................... 246
14.5.3 只返回需要的結果 ............................................................................................. 247
14.5.4 優化多表連接 ..................................................................................................... 248
14.5.5 盡量避免使用子查詢 ......................................................................................... 249
14.5.6 優化集合操作 ..................................................................................................... 251
14.5.7 不要使用 OFFSET 實現分頁.............................................................................251
14.5.8 記住 SQL 子句的邏輯執行順序........................................................................252
14.6 小結 ................................................................................................................................. 254

第 15 章 視圖不是表...........................................................................................................255
15.1 視圖概述 ......................................................................................................................... 255
15.1.1 什麼是視圖 ......................................................................................................... 255
15.1.2 視圖的優缺點 ..................................................................................................... 256
15.2 管理視圖 ......................................................................................................................... 256
15.2.1 創建視圖 ............................................................................................................. 256
15.2.2 視圖中的 ORDER BY 子句 ...............................................................................258
15.2.3 修改視圖的定義 .................................................................................................258
15.2.4 刪除視圖 ............................................................................................................. 259
15.3 可更新視圖 ..................................................................................................................... 260
15.3.1 可更新視圖的限制 ............................................................................................. 261
15.3.2 通過視圖修改數據 ............................................................................................. 261
15.3.3 限制視圖的操作 .................................................................................................262
15.4 案例分析 ......................................................................................................................... 263
15.5 小結 ................................................................................................................................. 264

第 16 章 存儲過程和存儲函數.............................................................................................265
16.1 存儲過程概述 ................................................................................................................. 265
16.1.1 什麼是存儲過程 .................................................................................................266
16.1.2 存儲過程的優缺點 ............................................................................................. 266
16.2 管理存儲過程 ................................................................................................................. 267
16.2.1 創建存儲過程 ..................................................................................................... 267
16.2.2 執行存儲過程 ..................................................................................................... 271
16.2.3 修改存儲過程 ..................................................................................................... 272
16.2.4 刪除存儲過程 .................................................................................

.................... 272
16.3 使用存儲函數 ................................................................................................................. 272
16.3.1 存儲函數和存儲過程的區別..............................................................................273
16.3.2 創建存儲函數 ..................................................................................................... 273
16.3.3 調用存儲函數 ..................................................................................................... 275
16.3.4 修改存儲函數 ..................................................................................................... 276
16.3.5 刪除存儲函數 ..................................................................................................... 276
16.4 案例分析 ......................................................................................................................... 276
16.5 小結 ................................................................................................................................. 280

第 17 章 一觸即發的觸發器 ................................................................................................281
17.1 觸發器概述 ..................................................................................................................... 281
17.1.1 觸發器的原理 ..................................................................................................... 281
17.1.2 觸發器的分類 ..................................................................................................... 282
17.2 管理觸發器 ..................................................................................................................... 283
17.2.1 創建觸發器 ......................................................................................................... 283
17.2.2 驗證觸發器 ......................................................................................................... 287
17.2.3 查看觸發器 ......................................................................................................... 288
17.2.4 啟用、禁用觸發器 ............................................................................................. 289
17.2.5 刪除觸發器 ......................................................................................................... 290
17.3 案例分析 ......................................................................................................................... 290
17.3.1 案例一：禁止 DDL 操作 ...................................................................................290
17.3.2 案例二：替換視圖的 DML 操作.......................................................................293
17.4 小結 ................................................................................................................................. 296

第 18 章 超#越關係...............................................................................................................297
18.1 文檔存儲 ......................................................................................................................... 297
18.1.1 JSON 數據類型................................................................................................... 298
18.1.2 將 JSON 對象表示成 SQL 數據 ........................................................................303
18.1.3 將 SQL 數據表示成 JSON 對象 ........................................................................304
18.2 複雜事件 ......................................................................................................................... 305
18.2.1 行模式識別 ......................................................................................................... 305
18.2.2 分析股票曲線圖 .................................................................................................306
18.2.3 監控可疑的銀行轉賬 ......................................................................................... 308
18.3 多維數組 ......................................................................................................................... 310
18.3.1 數組的存儲和訪問 ............................................................................................. 310
18.3.2 數組處理函數 ..................................................................................................... 313
18.4 圖形存儲 ......................................................................................................................... 315
18.4.1 圖形查詢語言與 SQL/PGQ................................................................................316
18.4.2 社交網絡關係分析 ............................................................................................. 317
18.5 小結 ................................................................................................................................. 320

附錄 A SQL 常用語句速查表 ..............................................................................................321