SQLServer数据库技术手册.zip_sqlserver_sqlserver2022使用教学资源-CSDN文库

共1个文件

docx：1个

版权申诉

sql_server

36 浏览量 2022-09-22 15:41:14 上传评论收藏 21.92MB ZIP 举报

资源推荐

资源详情

资源评论

收起资源包目录

SQL Server数据库技术手册.zip （1个子文件）

SQL Server数据库技术手册.docx 21.95MB

SQL Server 数据库技术手册

一、数据库简介 ..........................................................................................................................................................9

1、数据库系统概述 ............................................................................................................................................9

数据库技术的发展 ......................................................................................................................................9

数据库系统组成 ........................................................................................................................................10

“数据库”与“数据库管理系统”.........................................................................................................11

数据库能做什么 ........................................................................................................................................12

2、数据库体系结构 ..........................................................................................................................................12

什么是模式 ................................................................................................................................................12

三级模式结构 ............................................................................................................................................12

3、数据模型 ......................................................................................................................................................14

数据模型的分类 ........................................................................................................................................14

E-R 模型......................................................................................................................................................14

层次模型 ....................................................................................................................................................16

网状模型 ....................................................................................................................................................18

关系模型 ....................................................................................................................................................19

4、常见数据库 ..................................................................................................................................................20

Access.........................................................................................................................................................20

SQL Server .................................................................................................................................................21

Oracle..........................................................................................................................................................23

MySQL .......................................................................................................................................................25

5、数据库分类 ..................................................................................................................................................25

6、数据仓库 ......................................................................................................................................................25

维（Dimension）.......................................................................................................................................25

层次结构（Hierarchy）.............................................................................................................................26

多维数据集（Cube） ................................................................................................................................29

数据模型与数据建模 ................................................................................................................................30

数据模型的定义 ........................................................................................................................................30

事实表和维度表的分界线.........................................................................................................................31

钻取 ............................................................................................................................................................32

交叉分析 ....................................................................................................................................................32

7、SQL Server 设计规范...................................................................................................................................33

常见的字段类型选择 ................................................................................................................................33

约束与索引 ................................................................................................................................................33

索引设计准则 ............................................................................................................................................34

不要给选择性低的字段创建单列索引.....................................................................................................35

充分利用唯一索引 ....................................................................................................................................35

表索引数不超过 6 个（这个规则只是携程 DBA 经过试验之后制定的。。。）...............................35

SQL 查询....................................................................................................................................................36

禁止在数据库做复杂运算.........................................................................................................................37

禁止使用 SELECT * ..................................................................................................................................37

禁止在索引列上使用函数或计算.............................................................................................................37

禁止在索引列上使用函数或计算.............................................................................................................38

LIKE 查询的索引问题 ...............................................................................................................................38

禁止使用游标 ............................................................................................................................................38

禁止使用触发器 ........................................................................................................................................38

SQL Server 数据库技术手册

禁止在查询里指定索引.............................................................................................................................38

变量/参数/关联字段类型必须与字段类型一致（这是我之前不太关注的） .......................................39

参数化查询 ................................................................................................................................................39

限制 JOIN 个数 .........................................................................................................................................40

限制 IN 子句中条件个数...........................................................................................................................40

尽量避免大事务操作 ................................................................................................................................41

关闭影响的行计数信息返回.....................................................................................................................41

除非必要，尽量让所有的 select 语句都必须加上 NOLOCK...............................................................41

使用 UNION ALL 替换 UNION................................................................................................................42

查询大量数据使用分页或 TOP................................................................................................................42

递归查询层次限制 ....................................................................................................................................42

临时表与表变量 ........................................................................................................................................42

使用本地变量选择中庸执行计划.............................................................................................................42

尽量避免使用 OR 运算符.........................................................................................................................42

增加事务异常处理机制.............................................................................................................................43

输出列使用二段式命名格式.....................................................................................................................43

架构设计 ....................................................................................................................................................43

读写分离 ....................................................................................................................................................43

Schema 解耦.............................................................................................................................................43

数据生命周期 ............................................................................................................................................43

日志类型的表应分区或分表.....................................................................................................................43

频繁写入的表，需要分区或分表.............................................................................................................43

二、SQL Server 2016..................................................................................................................................................44

1、简介 ..............................................................................................................................................................44

2、特点 ..............................................................................................................................................................45

新的组件功能 ............................................................................................................................................46

混合云技术 ................................................................................................................................................46

3、安装 SQL Server 2016...................................................................................................................................47

安装必备 ....................................................................................................................................................47

SQL Server 2016 的安装 ............................................................................................................................49

SQL Server 2016 的卸载 ............................................................................................................................72

使用 SQL Server 2016 帮助 .......................................................................................................................75

三、数据库操作 ........................................................................................................................................................76

1、数据库概述 ..................................................................................................................................................76

1.1 数据库基本概念 .................................................................................................................................76

1.2 数据库常用对象 .................................................................................................................................78

1.3 数据库的组成 .....................................................................................................................................79

2、数据表概述 ..................................................................................................................................................81

2.1 SQL Server 2016 基本数据类型 ..........................................................................................................81

2.2 用户自定义数据类型 .........................................................................................................................85

2.3 使用管理器管理数据表......................................................................................................................89

2.4 操作数据约束 .....................................................................................................................................98

2.5 临时表 ...............................................................................................................................................105

2.6 内存表 ...............................................................................................................................................110

3、SQL 语言的构成 ........................................................................................................................................112

4、数据库语句操作 ........................................................................................................................................113

4.1 创建数据库 .......................................................................................................................................113

4.2 删除数据库 .......................................................................................................................................142

4.3 创建数据表 .......................................................................................................................................146

SQL Server 数据库技术手册

4.4 创建视图 ...........................................................................................................................................148

4.5 删除视图 ...........................................................................................................................................148

4.6 建立索引 ...........................................................................................................................................148

4.7 删除索引 ...........................................................................................................................................149

4.8 使用管理器修改数据库....................................................................................................................149

4.9 修改数据库属性 ...............................................................................................................................153

4、SQL Server 的命名规则 .............................................................................................................................160

4.1 标识符 ...............................................................................................................................................160

4.2 对象命名规则 ...................................................................................................................................161

4.3 实例命名规则 ...................................................................................................................................162

四、数据库数据操控语句 ....................................................................................................................................162

1、查询语句 ....................................................................................................................................................162

1.1 一般查询 ...........................................................................................................................................162

1.2 带条件查询 .......................................................................................................................................163

1.3 模糊查询 ...........................................................................................................................................163

1.4 查询结果排序 ...................................................................................................................................164

1.5 限制查询返回的行数 .......................................................................................................................174

1.6 IN 用法...............................................................................................................................................175

1.7 别名的用法 .......................................................................................................................................175

1.8 多表查询 ...........................................................................................................................................176

1.9 JOIN 用法............................................................................................................................................177

1.10 UNION 用法 ......................................................................................................................................178

1.11 UNION ALL 用法 ..............................................................................................................................179

1.12 查询返回随机 n 行记录 .................................................................................................................179

2、插入语句 ....................................................................................................................................................180

3、更新语句 ....................................................................................................................................................181

4、删除语句 ............................................................................................................................................182

合并语句 ..................................................................................................................................................182

语法 ..........................................................................................................................................................184

参数 ..........................................................................................................................................................186

备注 ..........................................................................................................................................................188

触发器的实现 ..........................................................................................................................................188

权限 ..........................................................................................................................................................189

示例 ..........................................................................................................................................................189

五、数据库数据类型 ..............................................................................................................................................194

1、数据类型类别 ............................................................................................................................................195

2、数值数据类型 ............................................................................................................................................195

3、字符型数据类型 ........................................................................................................................................196

4、日期数据类型 ............................................................................................................................................196

5、混合型数据类型 ........................................................................................................................................197

5.1 BINARY 和 BIT .....................................................................................................................................197

5.2 大对象数据类型 ...............................................................................................................................197

5.3 CURSOR...............................................................................................................................................198

5.4 UNIQUEIDENTIFIER .............................................................................................................................198

5.5 SQL_VARTANT.....................................................................................................................................198

5.6 TABLE ..................................................................................................................................................198

5.7 XML .....................................................................................................................................................204

5.8 Geography（地理空间类型）...........................................................................................................204

5.9 HIERARCHYID ......................................................................................................................................206

SQL Server 数据库技术手册

5.10 TIMESTAMP ......................................................................................................................................206

5.11 自定义数据类型 .............................................................................................................................206

6、附带有 VARDECIMAL 的 DECIMAL 存储类型 ............................................................................................207

7、类型转换 ....................................................................................................................................................207

CAST 和 CONVERT ..................................................................................................................................207

六、视图 ..................................................................................................................................................................244

1、视图概述 ....................................................................................................................................................244

1.1 视图的类型 .......................................................................................................................................245

1.2 视图的优缺点 ...................................................................................................................................245

2、使用管理器管理视图 ................................................................................................................................246

2.1 创建新视图 .......................................................................................................................................246

2.2 查看视图信息 ...................................................................................................................................249

2.3 创建基于视图的视图 .......................................................................................................................254

2.4 删除视图 ...........................................................................................................................................255

2.5 更新视图 ...........................................................................................................................................257

2.6 通过视图定义中的关键字搜索视图................................................................................................259

2.7 通过 T-SQL 语句获取视图定义........................................................................................................261

2.8 创建索引视图 ...................................................................................................................................262

2.9 创建分区视图 ...................................................................................................................................262

3、通过视图操作数据 ....................................................................................................................................263

3.1 在视图中插入数据记录....................................................................................................................263

3.2 在视图中修改数据记录....................................................................................................................264

3.3 在视图中删除数据记录....................................................................................................................264

4、系统视图 ....................................................................................................................................................265

4.1 INFORMATION_SCHEMA ....................................................................................................................266

4.2 兼容性视图 .......................................................................................................................................268

4.3 目录视图 ...........................................................................................................................................268

4.4 数据层应用程序视图 .......................................................................................................................268

4.5 动态管理视图和功能(DMVs) ...........................................................................................................268

4.6 系统视图查询实例 ...........................................................................................................................268

七、存储过程 ..........................................................................................................................................................313

1、存储过程概述 ............................................................................................................................................313

1.1 什么是存储过程 ...............................................................................................................................314

1.2 创建存储过程 ...................................................................................................................................315

1.3 管理存储过程 ...................................................................................................................................320

1.4 实用存储过程实例 ...........................................................................................................................334

2、目录存储过程 ............................................................................................................................................343

八、触发器 ..............................................................................................................................................................343

1、触发器概述 ................................................................................................................................................343

1.1 触发器的概念 ...................................................................................................................................344

1.2 触发器的优点 ...................................................................................................................................344

1.3 触发器的种类 ...................................................................................................................................344

2、创建触发器 ................................................................................................................................................345

2.1 创建 DML 触发器..............................................................................................................................345

2.2 创建 DDL 触发器...............................................................................................................................349

2.3 创建登录触发器 ...............................................................................................................................351

2.4 限制非工作时间操作数据................................................................................................................352

2.5 限制对保护数据的操作....................................................................................................................352

2.6 实现级联操作 ...................................................................................................................................353

SQL Server 数据库技术手册

3、管理触发器 ................................................................................................................................................354

3.1 查看触发器 .......................................................................................................................................354

3.2 修改触发器 .......................................................................................................................................356

3.3 重命名触发器 ...................................................................................................................................357

3.4 禁用和启用触发器 ...........................................................................................................................357

3.5 删除触发器 .......................................................................................................................................359

4、触发器元数据 ............................................................................................................................................360

4.1 数据库中列出触发器的定义............................................................................................................367

4.2 数据库中关键字搜索触发器............................................................................................................367

九、索引 ..................................................................................................................................................................368

1、索引的概述 ................................................................................................................................................368

2、索引的优缺点 ............................................................................................................................................368

2.1 索引的优点 .......................................................................................................................................369

2.2 索引的缺点 .......................................................................................................................................369

3、索引的分类 ................................................................................................................................................369

3.1 聚集索引 ...........................................................................................................................................369

3.2 非聚集索引 .......................................................................................................................................369

4、索引的操作 ................................................................................................................................................370

4.1 索引的创建 .......................................................................................................................................370

4.2 查看索引信息 ...................................................................................................................................375

4.3 索引的修改 .......................................................................................................................................377

4.4 索引的删除 .......................................................................................................................................380

4.5 设置索引选项 ...................................................................................................................................380

4.6 索引的分析与维护 ...........................................................................................................................381

4.7 索引元数据 .......................................................................................................................................389

5、全文索引 ....................................................................................................................................................399

5.1 使用 SSMS 创建全文索引.................................................................................................................399

5.2 使用 T-SQL 语句创建全文索引 ........................................................................................................408

5.3 使用 T-SQL 语句删除全文索引 ........................................................................................................409

5.4 全文目录 ...........................................................................................................................................409

5.5 小结 ...................................................................................................................................................411

6、游标 ............................................................................................................................................................412

6.1 游标的概述 .......................................................................................................................................412

7、列存储索引 ................................................................................................................................................416

7.1 数据存储和划分 ...............................................................................................................................427

7.2 数据操作 ...........................................................................................................................................429

7.3 优缺点 ...............................................................................................................................................431

7.4 疑问 ...................................................................................................................................................431

十、角色与权限管理 ..............................................................................................................................................432

一、概述 ........................................................................................................................................................432

二、 SQL Server 安全实现策略 ......................................................................................................................432

三、安全机制 5 个等级 ................................................................................................................................435

四、角色 ........................................................................................................................................................435

五、权限 ........................................................................................................................................................437

六、用户权限设置 ........................................................................................................................................438

十一、复制（数据发布与订阅）...........................................................................................................................442

一、介绍 ........................................................................................................................................................442