SQL编程思想:基于 5 种主流数据库代码实现 董旭阳 著 专业科技 文轩网

第 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 SQL 编程思想：基于 5 种主流数据库代码实现 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 ? VIII ? 目录 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 函数计算平均值...................................................................................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 ? IX ? SQL 编程思想：基于 5 种主流数据库代码实现 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 ? X ? 目录 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 ? XI ? SQL 编程思想：基于 5 种主流数据库代码实现 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 案例分析：移动平均值 .....................................................................................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 ? XII ? 目录 第 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 ? XIII ? SQL 编程思想：基于 5 种主流数据库代码实现 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 ? XIV ? 目录 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 ? XV ? SQL 编程思想：基于 5 种主流数据库代码实现 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 ? XVI ? 目录 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 ? XVII ?