任务1
数据库基础······························································································································ 1
任务描述······················································································································································ 2
知识储备······················································································································································ 2
1.1 数据库系统········································································································································· 2
1.1.1 信息、数据和数据库················································································································· 2
1.1.2 数据库系统···································································································································· 3
1.2 数据模型·············································································································································· 5
1.2.1 数据模型的概念·························································································································· 5
1.2.2 数据模型的组成要素················································································································· 6
1.2.3 数据模型的应用层次················································································································· 6
1.3 关系型数据库·································································································································· 11
1.3.1 关系模型······································································································································ 11
1.3.2 关系型数据库的概念和特点································································································· 16
1.3.3 关系型数据库管理系统·········································································································· 17
任务实施···················································································································································· 20
任务评价···················································································································································· 22
课后习题···················································································································································· 23
任务2
数据库设计··························································································································· 25
任务描述···················································································································································· 26
知识储备···················································································································································· 26
2.1 需求分析············································································································································ 27
2.1.1 需求分析的内容························································································································ 27
2.1.2 需求分析的方法························································································································ 28
2.2 概念设计············································································································································ 29
2.2.1 概念设计的方法························································································································ 29
2.2.2 概念设计的步骤························································································································ 30
2.3 逻辑设计············································································································································ 34
2.3.1 E-R 模型到关系模型的转换································································································ 35
2.3.2 关系模式的规范化··················································································································· 36
2.3.3 关系模式的评价与改进·········································································································· 39
2.4 物理设计············································································································································ 39
2.4.1 物理设计的任务························································································································ 40
2.4.2 物理设计的评价························································································································ 42
2.5 数据库实施······································································································································· 42
2.6 数据库运行与维护························································································································· 44
2.6.1 数据库的运行····························································································································· 44
2.6.2 数据库的维护····························································································································· 44
任务实施···················································································································································· 46
任务评价···················································································································································· 50
课后习题···················································································································································· 51
任务3 SQL Server 数据库管理系统·························································································· 53
任务描述···················································································································································· 54
知识储备···················································································································································· 54
3.1
SQL Server 数据库概述················································································································ 54
3.1.1 SQL Server 的发展历程········································································································· 54
3.1.2 SQL Server 2022 版本介绍·································································································· 56
3.1.3 SQL Server 的管理工具········································································································· 56
3.1.4 SQL Server 2022 的运行环境要求···················································································· 58
3.2
SQL Server 数据库的体系结构·································································································· 59
3.2.1 SQL Server 2022 的组成······································································································· 59
3.2.2 SQL Server 的数据库种类···································································································· 60
3.2.3 SQL Server 数据库的存储结构··························································································· 61
任务实施···················································································································································· 63
任务评价···················································································································································· 91
课后习题···················································································································································· 92
任务4 T-SQL 编程语言基础········································································································ 94
任务描述···················································································································································· 95
知识储备···················································································································································· 95
4.1 标准SQL 语言与T-SQL············································································································· 95
4.1.1 标准SQL 语言基础················································································································· 95
4.1.2 T-SQL 与标准SQL 的区别与联系···················································································· 98
4.2
T-SQL 语言基础······························································································································ 99
4.2.1 数据类型······································································································································ 99
4.2.2 常量与变量······························································································································· 100
4.2.3 表达式与运算符······················································································································ 101
4.2.4 系统内置函数·························································································································· 102
4.2.5 流程控制语句·························································································································· 104
4.2.6 错误处理···································································································································· 108
4.2.7 延迟执行···································································································································· 108
任务实施·················································································································································· 110
任务评价·················································································································································· 118
课后习题·················································································································································· 119
任务5 SQL Server 数据库/表操作·························································································· 121
任务描述·················································································································································· 122
知识储备·················································································································································· 122
5.1 使用T-SQL 管理数据库··········································································································· 122
5.1.1 创建数据库······························································································································· 122
5.1.2 查看数据库······························································································································· 123
5.1.3 选择数据库······························································································································· 124
5.1.4 修改数据库······························································································································· 125
5.1.5 删除数据库······························································································································· 126
5.2 使用T-SQL 管理数据表··········································································································· 126
5.2.1 创建数据表······························································································································· 126
5.2.2 修改数据表······························································································································· 128
5.2.3 删除数据表······························································································································· 133
任务实施·················································································································································· 135
任务评价·················································································································································· 152
课后习题·················································································································································· 153
任务6 SQL Server 数据操作····································································································· 155
任务描述·················································································································································· 156
知识储备·················································································································································· 156
6.1 使用T-SQL 管理数据················································································································ 156
6.1.1 插入数据···································································································································· 156
6.1.2 更新数据···································································································································· 158
6.1.3 删除数据···································································································································· 159
6.2 使用T-SQL 查询数据················································································································ 161
6.2.1 基本查询语句·························································································································· 161
6.2.2 无数据源查询·························································································································· 162
6.2.3 单表查询···································································································································· 163
6.2.4 多表查询···································································································································· 172
6.2.5 嵌套查询···································································································································· 175
任务实施·················································································································································· 177
任务评价·················································································································································· 185
课后习题·················································································································································· 186
任务7
数据库编程对象················································································································ 189
任务描述·················································································································································· 190
知识储备·················································································································································· 190
7.1 索引··················································································································································· 190
7.1.1 索引概述···································································································································· 190
7.1.2 索引操作···································································································································· 193
7.2 视图··················································································································································· 194
7.2.1 视图概述···································································································································· 195
7.2.2 视图操作···································································································································· 195
7.3 存储过程········································································································································· 198
7.3.1 存储过程概述·························································································································· 198
7.3.2 存储过程操作·························································································································· 199
7.4 触发器·············································································································································· 202
7.4.1 触发器概述······························································································································· 202
7.4.2 触发器操作······························································································································· 203
7.5 游标··················································································································································· 206
7.5.1 游标概述···································································································································· 206
7.5.2 游标操作···································································································································· 206
7.6 批处理与脚本································································································································ 208
7.6.1 批处理········································································································································ 208
7.6.2 脚本············································································································································· 212
7.7 事务··················································································································································· 212
7.7.1 事务概述···································································································································· 212
7.7.2 事务操作···································································································································· 214
任务实施·················································································································································· 216
任务评价·················································································································································· 229
课后习题·················································································································································· 230
任务8
数据库安全管理················································································································ 233
任务描述·················································································································································· 234
知识储备·················································································································································· 234
8.1 数据库安全····································································································································· 234
8.1.1 数据库安全概述······················································································································ 234
8.1.2 数据库安全技术、策略与机制·························································································· 236
8.2 用户与权限管理··························································································································· 237
8.2.1 用户管理···································································································································· 237
8.2.2 权限管理···································································································································· 240
8.3 数据备份与恢复··························································································································· 241
8.3.1 数据备份···································································································································· 241
8.3.2 数据恢复···································································································································· 243
任务实施·················································································································································· 246
任务评价·················································································································································· 250
课后习题·················································································································································· 251
课后习题答案········································································································································ 254
参考文献·················································································································································· 256