目录
源代码
第一篇 数据库原理与应用基础
第 1章数据库概论 ················································································································3
1.1
数据库基本概念 ·······································································································3
1.1.1
数据库 ···········································································································3
1.1.2
数据库管理系统 ···························································································4
1.1.3
数据库系统 ···································································································4
1.1.4
数据管理技术的发展 ···················································································6
1.2
数据模型 ···················································································································8
1.2.1
数据建模 ·······································································································8
1.2.2
概念模型 ·······································································································9
1.2.3
数据模型的三要素 ·····················································································10
1.2.4
层次模型、网状模型和关系模型 ······························································10
1.
3数据库系统结构 ·····································································································12
1.3.1
数据库系统中模式的概念 ··········································································12
1.3.2
数据库系统的三级模式结构 ······································································12
1.3.3
数据库的两级映像功能和数据独立性 ······················································13
1.4
数据库新技术 ·········································································································14
1.4.1
大数据 ·········································································································14
1.4.2
并行数据库系统 ·························································································19
1.4.3
分布式数据库系统 ·····················································································21
1.4.4
数据仓库 ·····································································································22
1.4.5
联机分析处理 ·····························································································24
1.4.6
数据挖掘 ·····································································································27
1.5
小结 ························································································································29 习题 1 ······························································································································30
第 2章关系数据库系统模型 ······························································································32
2.1
关系模型 ·················································································································32
2.1.1
关系数据结构 ·····························································································32
2.1.2
关系操作 ·····································································································35
2.1.3
关系的完整性 ·····························································································36
2.2
关系代数 ·················································································································38
2.2.1
传统的集合运算 ·························································································38
2.2.2
专门的关系运算 ·························································································40
2.2.3
关系代数综合举例 ·····················································································46
2.3
关系演算 ·················································································································48
2.3.1
元组关系演算 ·····························································································48
2.3.2
域关系演算 ·································································································50
2.4
小结 ························································································································52 习题 2 ······························································································································53
第 3章关系数据库标准语言 SQL ·····················································································56
3.1
SQL概述 ················································································································56
3.1.1
SQL的基本概念 ·························································································57
3.1.2
SQL的组成 ································································································57
3.1.3
SQL的特点 ································································································58
3.2
数据定义 ·················································································································59
3.2.1
数据库的定义和删除 ·················································································59
3.2.2
数据类型 ·····································································································59
3.2.3
基本表的定义、修改和删除 ······································································60
3.3
数据查询 ·················································································································64
3.3.1
单表查询 ·····································································································65
3.3.2
连接查询 ·····································································································72
3.3.3
嵌套查询 ·····································································································76
3.3.4
联合查询 ·····································································································80
3.4
数据更新 ·················································································································81
3.4.1
插入数据 ·····································································································81
3.4.2
修改数据 ·····································································································83
3.4.3
删除数据 ·····································································································84
3.5
视图和索引 ·············································································································86
3.5.1
视图·············································································································86
3.5.2
索引·············································································································88
3.6
小结 ························································································································90 习题 3 ······························································································································90
第 4章数据库完整性 ··········································································································94
4.1
数据库完整性概述 ·································································································94
4.2
实体完整性 ·············································································································95
4.2.1
实体完整性定义 ·························································································95
4.2.2 实体完整性检查和违约处理 ······································································96
4.3 参照完整性 ·············································································································96
4.3.1 参照完整性定义 ·························································································96
4.3.2 参照完整性检查和违约处理 ······································································96
4.4 用户定义的完整性 ·································································································98
4.4.1 属性上的约束 ·····························································································99
4.4.2 元组上的约束 ···························································································100
4.5 完整性约束命名子句 ···························································································100
4.6 完整性约束删除和修改 ·······················································································101
4.7 触发器···················································································································102
4.7.1 创建触发器 ·······························································································103
4.7.2 激活触发器 ·······························································································106
4.7.3 删除触发器 ·······························································································106
4.8 小结 ······················································································································106 习题 4 ····························································································································107
第 5章数据库安全性 ········································································································110
5.1 数据库安全性概述 ·······························································································110
5.1.1 数据库的不安全因素 ···············································································110
5.1.2 安全标准 ···································································································111
5.2 数据库安全性控制 ·······························································································112
5.2.1 用户标识和鉴别 ·······················································································112
5.2.2 存取控制策略 ···························································································113
5.2.3 自主存取控制 ···························································································114
5.2.4 强制存取控制 ···························································································114
5.3 视图机制 ···············································································································115
5.4 审计 ······················································································································115
5.5 数据加密 ···············································································································115
5.6 MySQL安全设置 ·································································································116
5.6.1 MySQL访问控制 ·····················································································116
5.6.2 用户管理 ···································································································117
5.6.3 权限管理 ···································································································119
5.6.4 角色管理 ···································································································124
5.7 小结 ······················································································································125 习题 5 ····························································································································126
6.1 常量、变量、运算符和表达式 ············································································129
6.1.1
常量···········································································································129
6.1.2
变量···········································································································130
6.1.3
运算符和表达式 ·······················································································131
6.2
系统函数 ···············································································································132
6.2.1
数学函数 ···································································································132
6.2.2
字符串函数 ·······························································································133
6.2.3
日期和时间函数 ·······················································································135
6.2.4
其他函数 ···································································································136
6.3
自定义函数 ···········································································································137
6.4
流程控制语句 ·······································································································138
6.4.1
BEGIN…END语句块、局部变量 ··························································138
6.4.2
重置语句结束标记和注释 ········································································139
6.4.3
条件语句 ···································································································141
6.4.4
循环语句 ···································································································143
6.5
存储过程 ···············································································································145
6.5.1
创建存储过程 ···························································································146
6.5.2
调用存储过程 ···························································································146
6.5.3
存储过程的参数 ·······················································································146
6.5.4
存储过程的删除 ·······················································································149
6.6
游标 ······················································································································149
6.6.1
声明游标 ···································································································149
6.6.2
打开游标 ···································································································149
6.6.3
读取数据 ···································································································150
6.6.4
关闭游标 ···································································································150
6.7
小结 ······················································································································151 习题 6 ····························································································································152
第 6章数据库编程 ············································································································129
目 录
第二篇 数据库管理和维护
第 7章事务和并发控制 ····································································································157
7.1
事务的基本概念 ···································································································157
7.1.1
事务的概念 ·······························································································157
7.1.2
事务特性 ···································································································157
7.1.3
MySQL事务控制语句 ·············································································158
7.2
并发控制 ···············································································································163
7.2.1
并发执行可能引起的问题 ········································································164
7.2.2
事务隔离级别 ···························································································165
7.2.3
MySQL事务隔离级别设置 ·····································································166
7.3 封锁 ······················································································································171
7.3.1 锁···············································································································171
7.3.2 封锁协议 ···································································································172
7.3.3 封锁带来的问题 ·······················································································174
7.3.4 并发调度的可串行性 ···············································································176
7.3.5 两阶段封锁协议 ·······················································································178
7.4 封锁的粒度 ···········································································································180
7.4.1 多粒度封锁 ·······························································································180
7.4.2 意向锁 ·······································································································181
7.5 MySQL的并发控制 ·····························································································182
7.6 小结 ······················································································································185 习题 7 ····························································································································186
第 8章数据库恢复技术 ····································································································190
8.1 数据库恢复概述 ···································································································190
8.2 故障的种类 ···········································································································190
8.2.1 事务内部的故障 ·······················································································190
8.2.2 系统故障 ···································································································191
8.2.3 介质故障 ···································································································191
8.3 事务访问数据方式 ·······························································································192
8.4 恢复实现技术 ·······································································································193
8.4.1 数据转储 ···································································································193
8.4.2 登记日志文件 ···························································································194
8.5 数据库恢复策略 ···································································································196
8.5.1 事务故障的恢复 ·······················································································196
8.5.2 系统故障的恢复 ·······················································································196
8.5.3 介质故障的恢复 ·······················································································197
8.6 检查点技术和数据库镜像 ···················································································197
8.6.1 具有检查点的恢复技术 ···········································································197
8.6.2 数据库镜像 ·······························································································199
8.7 MySQL数据库备份和恢复 ·················································································200
8.7.1 备份数据 ···································································································200
8.7.2 恢复数据 ···································································································204
8.7.3 使用二进制日志文件恢复数据 ································································206
8.8 小结 ······················································································································209 习题 8 ····························································································································210
目 录
第三篇 数据库设计和应用开发
第 9章关系数据理论 ········································································································217
9.1
问题的提出 ···········································································································217
9.2
规范化···················································································································220
9.2.1 函数依赖、码和范式 ···············································································220
9.2.2 1NF············································································································221
9.2.3 2NF············································································································222
9.2.4 3NF············································································································224
9.2.5 BCNF ········································································································225
9.2.6
多值依赖与 4NF ·······················································································226
9.2.7
规范化小结 ·······························································································228
9.3
数据依赖的公理系统 ···························································································229
9.3.1
Armstrong公理系统 ·················································································229
9.3.2
闭包及其计算 ···························································································230
9.3.3
确定候选码 ·······························································································232
9.3.4
函数依赖集的等价和最小函数依赖集 ····················································233
9.4
关系模式的分解 ···································································································235
9.4.1
模式分解的定义 ·······················································································235
9.4.2
分解的无损连接性 ···················································································236
9.4.3
分解的保持依赖性 ···················································································238
9.4.4
模式分解的算法 ·······················································································238
9.5
小结 ······················································································································239 习题 9 ····························································································································240
第 10章数据库设计 ··········································································································244
10.1
数据库设计概述 ·································································································244
10.2
需求分析 ·············································································································247
10.3
概念结构设计 ·····································································································249
10.3.1
概念结构的特点和设计步骤 ·······························································249
10.3.2
E-R模型 ·······························································································251
10.4
逻辑结构设计 ·····································································································252
10.4.1
逻辑结构设计的步骤 ···········································································252
10.4.2
E-R模型向关系模型的转换 ································································253
10.4.3
数据模型的优化和设计外模式 ···························································255
10.5
物理结构设计 ·····································································································256
10.6
数据库实施 ·········································································································257
10.7 数据库运行和维护 ·····························································································258
10.8 小结 ····················································································································258 习题 10 ··························································································································259
第 11章数据库应用系统设计案例 ···················································································263
11.1系统需求分析 ·····································································································263
11.1.1 需求分析的任务 ···················································································263
11.1.2 数据流图·······························································································265
11.1.3 数据字典·······························································································265
11.2数据库概念结构设计 ·························································································268
11.2.1 设计实体 E-R图···················································································268
11.2.2 设计局部 E-R图···················································································269
11.2.3 设计全局 E-R图···················································································270
11.3数据库逻辑结构设计 ·························································································272
11.3.1 E-R图向关系模式的转换 ····································································272
11.3.2 关系模式的优化 ···················································································273
11.3.3 基本关系表的结构 ···············································································274
11.4数据库物理结构设计 ·························································································276
11.4.1 定义关系模式 ·······················································································276
11.4.2 定义用户外模式 ···················································································278
11.4.3 定义索引·······························································································278
11.5小结·····················································································································279
第四篇 数据库实验
实验准备 1——MySQL 8.0安装与配置 ·············································································283 实验准备 2——实验与章节对应表 ·····················································································287 实验 1数据定义 ·················································································································288 实验 2数据查询 ·················································································································291 实验 3数据更新 ·················································································································296 实验 4视图和索引 ·············································································································299 实验 5数据库完整性 ·········································································································303 实验 6安全管理 ·················································································································308
实验 7存储过程和游标 ·····································································································310
目 录
实验 8事务和并发控制 ·····································································································314 实验 9备份和恢复 ·············································································································318 附录 A案例数据库：学生信息数据库 studb的表结构和样本数据 ·································320 附录 B实验数据库：商店数据库 shopexpm的表结构和样本数据 ···································323 参考文献 ·······························································································································326
