SQL Server 2016数据库应用与开发

本书从教学实际需求出发，结合初学者的认知规律，由浅入深、循序渐进地讲解SQL Server 2016数据库管理与开发过程中的知识。全书体系完整、可操作性强，以大量例题对常用知识点操作进行示范，所有例题全部通过调试，内容涵盖了设计一个数据库应用系统要用到的主要知识。 

本书系统地介绍数据库系统的基本组成、SQL Server 2016的运行环境、数据库及各种常用数据库对象的创建和管理、TransactSQL及其应用、数据库的备份与恢复、数据转换、安全管理、自动化管理任务、复制与性能监视等。对数据库操作中较为常用的数据检索、数据完整性、视图、存储过程、触发器、并发控制等进行了详细的阐述，并给出了利用Java与SQL Server 2016开发的数据库应用系统案例。同时对SQL Server 2016的主要操作单元录制微课视频，以帮助读者更好地学习数据库的基本操作。
全书体系完整，结构安排合理，内容叙述翔实，例题丰富，可操作性强，内容涵盖了数据库方面要用到的主要知识。另外，本书还配有辅导教材《SQL Server 2016 数据库应用与开发习题解答与上机指导》，以帮助读者进一步巩固所学SQL Server数据库的知识。
本书适合作为高等院校本科、专科计算机及相关专业“数据库应用系统开发技术”课程的教材，也可供信息技术领域的科技工作者参考之用。

目录

第1章数据库系统概述

1.1数据库系统的基本概念

1.1.1信息与数据库

1.1.2结构化查询语言

1.1.3数据库管理系统

1.1.4数据库系统

1.2关系数据库理论

1.2.1概念模型及其表示方法

1.2.2数据模型

1.2.3关系运算

1.3设计数据库

1.3.1数据库设计的规范化

1.3.2数据库设计的主要内容

1.3.3数据库设计的过程

1.4小结

习题

第2章SQL Server 2016的运行环境

2.1SQL Server数据库简介

2.1.1SQL Server数据库的发展历程

2.1.2SQL Server 2016的新特性

2.2SQL Server 2016的系统要求

2.2.1SQL Server 2016版本

2.2.2SQL Server 2016安装环境要求

2.3SQL Server 2016的管理平台

2.3.1数据库引擎及Management Studio的使用

2.3.2SQL Server 2016的实例

2.3.3新建查询

2.3.4SQL Server 2016的服务项目

2.3.5系统数据库

2.4SQL Server 2016的服务器管理

2.4.1注册服务器

2.4.2启动、暂停和关闭服务器

2.4.3配置服务器

2.5SQL Server 2016的联机丛书和教程

2.5.1SQL Server文档的使用

2.5.2MSDN论坛的使用

2.5.3SQL Server教程的使用

2.6小结

习题

第3章创建与管理数据库

3.1数据库对象和数据库文件

3.1.1数据库的基本概念

3.1.2数据库的常用对象

3.1.3数据库的存储

3.2用户数据库创建与修改

3.2.1用户数据库的创建

3.2.2修改数据库

3.2.3数据库文件的脚本生成

3.3管理数据库

3.3.1查看数据库状态信息

3.3.2数据库的属性设置

3.3.3估算数据库大小

3.3.4收缩数据库

3.3.5分离和附加用户数据库

3.3.6联机和脱机用户数据库

3.3.7删除数据库

3.4文件组的创建

3.5数据库快照和数据分区管理

3.5.1数据库快照

3.5.2数据分区管理

3.6小结

习题

第4章表和数据完整性

4.1SQL Server 2016的数据类型

4.1.1数值类型

4.1.2字符类型

4.1.3日期和时间类型

4.1.4货币类型

4.1.5其他数据类型

4.2表的创建与维护

4.2.1有关表的基础知识

4.2.2表的创建

4.2.3数据浏览

4.2.4表结构的修改

4.2.5表数据的修改

4.2.6删除表

4.3数据的完整性

4.3.1数据完整性的类型

4.3.2约束

4.3.3规则

4.3.4默认值

4.3.5强制数据完整性

4.4数据库关系图

4.5数据的导入和导出

4.5.1数据转换概述

4.5.2导入数据

4.5.3导出数据

4.6小结

习题

第5章TransactSQL基础

5.1了解TransactSQL编程语言

5.1.1TransactSQL概述

5.1.2TransactSQL语句分类

5.2TransactSQL语法要素

5.2.1常用编码

5.2.2标识符

5.2.3常量

5.2.4变量

5.2.5注释

5.3TransactSQL运算符

5.3.1算术运算符

5.3.2比较运算符

5.3.3逻辑运算符

5.3.4字符串连接运算符

5.3.5位运算符

5.3.6赋值运算符

5.3.7运算符的优先级

5.4TransactSQL函数

5.4.1数学函数

5.4.2聚合函数

5.4.3日期和时间函数

5.4.4转换函数

5.4.5字符串函数

5.4.6自定义函数

5.5TransactSQL表达式

5.6TransactSQL控制流语句

5.6.1IF…ELSE语句

5.6.2BEGIN…END语句

5.6.3WHILE语句

5.6.4CASE语句

5.6.5其他语句

5.7小结

习题

第6章数据检索

6.1利用SELECT语句检索数据

6.1.1利用SELECT…FROM语句指定列

6.1.2利用WHERE子句指定行

6.1.3利用INTO子句生成新表

6.2数据过滤

6.2.1空值查询

6.2.2利用比较运算符查询

6.2.3利用字符串运算符查询

6.2.4利用逻辑运算符查询

6.2.5检索一定范围内的值

6.2.6利用列表值检索数据

6.3设置结果集格式

6.3.1改变列名

6.3.2利用ORDER BY子句排序

6.3.3消除重复行

6.3.4利用TOP n输出前n行

6.4GROUP BY子句和HAVING子句

6.4.1GROUP BY子句的使用

6.4.2GROUP BY子句和HAVING子句的联合使用

6.5WITH ROLLUP子句和聚合函数的使用

6.5.1利用GROUP BY子句与WITH ROLLUP进行统计

6.5.2聚合函数的应用

6.6小结

习题

第7章TransactSQL语句的高级应用

7.1多表连接

7.1.1连接概述

7.1.2内连接

7.1.3外连接

7.1.4交叉连接

7.1.5连接多个表

7.1.6合并多个结果集

7.2使用子查询

7.2.1子查询介绍

7.2.2利用子查询作表达式

7.2.3利用子查询关联数据

7.2.4利用子查询生成派生表

7.2.5使用子查询修改表数据

7.2.6EXISTS和NOT EXISTS子句

7.3利用游标处理结果集

7.3.1游标的概念

7.3.2游标的运用

7.3.3游标的嵌套

7.3.4查看游标的信息

7.4管理大对象类型数据

7.5小结

习题

第8章索引和视图

8.1规划索引

8.1.1索引的用途

8.1.2索引的类型

8.1.3设计索引的基本原则

8.2创建索引

8.2.1利用SQL Server Management Studio创建索引

8.2.2利用CREATE INDEX命令创建索引

8.3维护索引

8.3.1在SQL Server Management Studio中修改索引

8.3.2利用ALTER INDEX命令修改索引

8.3.3索引碎片检测

8.3.4索引重组

8.3.5索引重建

8.3.6索引分析

8.3.7删除索引

8.4统计信息及应用

8.4.1统计信息的收集

8.4.2统计信息的创建

8.4.3查看统计信息

8.4.4统计信息的更新

8.5视图的定义

8.5.1视图概念

8.5.2创建视图

8.5.3查看视图信息

8.6视图的修改

8.6.1在SQL Server Management Studio中修改视图

8.6.2利用ALTER VIEW命令修改视图

8.6.3视图重命名

8.6.4删除视图

8.7通过视图修改数据

8.8小结

习题

第9章存储过程与触发器

9.1认识存储过程

9.1.1存储过程的类型

9.1.2存储过程的设计原则

9.1.3常用系统存储过程的使用

9.2创建和管理存储过程

9.2.1创建存储过程

9.2.2修改存储过程

9.2.3执行存储过程

9.3认识触发器

9.3.1触发器的分类

9.3.2触发器的工作原理

9.3.3创建触发器前应注意的问题

9.4创建和管理触发器

9.4.1创建触发器

9.4.2修改触发器

9.4.3触发器的常见应用

9.4.4查看触发器

9.4.5删除触发器

9.4.6禁用触发器

9.4.7启用触发器

9.5小结

习题

第10章事务和锁

10.1事务概述

10.1.1事务的特点

10.1.2事务的分类

10.2管理事务

10.2.1启动事务

10.2.2保存事务

10.2.3提交事务

10.2.4回滚事务

10.2.5自动提交事务

10.2.6事务嵌套

10.3管理并发数据

10.3.1并发的影响

10.3.2并发控制的类型

10.3.3事务的隔离级别

10.4管理锁

10.4.1锁的类型

10.4.2可以锁定的资源

10.4.3锁的兼容性

10.4.4死锁

10.4.5显示锁定信息

10.5小结

习题

第11章SQL Server的安全管理

11.1SQL Server的安全性机制

11.1.1基本概念

11.1.2权限层次结构

11.1.3查询权限

11.2管理服务器范围的安全性

11.2.1SQL Server 2016的验证模式

11.2.2服务器角色

11.2.3管理登录名

11.2.4管理凭据

11.3管理数据库范围的安全性

11.3.1数据库角色

11.3.2管理数据库用户

11.3.3特殊用户

11.4管理密钥与证书

11.4.1SQL Server 2016的密码系统架构

11.4.2创建密钥

11.4.3创建证书

11.4.4加密实例

11.5权限管理

11.5.1语句权限

11.5.2对象权限

11.5.3解决权限冲突

11.6小结

习题

第12章备份和恢复

12.1备份和还原概述

12.1.1备份的时机

12.1.2备份和恢复的类型

12.1.3备份策略的选择

12.1.4恢复模式的设置

12.2备份数据库

12.2.1创建备份设备

12.2.2执行完整数据库备份

12.2.3执行差异数据库备份

12.2.4执行事务日志备份

12.2.5执行文件或文件组的备份

12.3还原数据库

12.3.1从完整数据库备份还原

12.3.2从差异数据库备份还原

12.3.3从事务日志备份还原

12.4还原受损的系统数据库

12.5小结

习题

第13章系统自动化任务管理

13.1SQL Server代理

13.1.1配置SQL Server代理

13.1.2启动和停止SQL Server代理

13.1.3SQL Server代理的安全性

13.2作业

13.2.1创建作业

13.2.2管理作业

13.2.3查看作业历史

13.3警报

13.3.1创建警报响应SQL Server错误

13.3.2删除警报

13.4操作员

13.4.1创建操作员

13.4.2为操作员分配警报

13.5维护计划

13.6小结

习题

第14章复制与性能监视

14.1复制概述

14.1.1复制的发布模型

14.1.2复制类型

14.1.3SQL Server 2016引入的新功能

14.2创建复制

14.2.1创建发布

14.2.2创建订阅

14.2.3配置分发

14.3管理复制

14.3.1查看和修改发布属性

14.3.2查看和修改项目属性

14.3.3设置历史记录保持期

14.3.4查看发布服务器信息及执行任务

14.4系统性能监视器的使用

14.4.1系统性能监视器的运行

14.4.2SQL Server的性能对象

14.4.3监视SQL Server的任务

14.4.4利用SQL Server Profiler工具进行监视

14.5小结

习题

第15章SQL Server数据库应用系统开发

15.1常用软件开发的一般过程

15.2社区诊所就医管理系统的数据库设计

15.2.1数据库的需求分析

15.2.2设计数据库的概念结构

15.2.3数据库的表设计

15.3数据库应用系统的开发

15.3.1软件开发环境的搭建

15.3.2系统总体设计

15.3.3系统的功能与实现

15.3.4系统的运行与使用

15.4小结

习题

前言

人类所能够收集的数据随着大数据时代的来临急剧增加，已经达到拍字节级别甚至艾字节级别的大数据量，使得大数据分析应运而生。依托大数据获取隐含知识或决策依据的系统和技术的基础就是数据库开发，大数据更是将数据库的应用平台推上一个新的高度。
Microsoft公司的SQL Server 2016是一个功能完备的数据库管理系统，SQL Server 2016在确保原有功能的基础上，增加了许多新功能。其对云计算和大数据的支持实现了与Microsoft Azure云平台的交互和全面支持。
本书从教学实际需求出发，结合初学者的认知规律，由浅入深、循序渐进地讲解SQL Server 2016数据库管理与开发过程的知识。全书体系完整、可操作性强，以大量的例题对常用知识点操作进行示范，所有例题全部通过调试，内容涵盖了设计一个数据库应用系统要用到的主要知识。同时，对SQL Server 2016数据库的主要操作单元录制了微课视频，以此为读者学习数据库的基本操作提供了新的方法和途径。
全书共分15章，现将本书的主要内容简单介绍如下。
第1章有关数据库系统的基础知识和关系数据库理论。
第2章SQL Server 2016基础知识和运行环境的基本操作。
第3章SQL Server 2016数据库的创建与管理、数据库文件和文件组、数据库快照等。
第4章数据类型、表的基本操作、数据完整性和数据转换等。
第5章TransactSQL的语法规则及使用。
第6章利用SELECT语句进行数据检索。
第7章多表连接、子查询、游标和管理大对象类型数据的操作。
第8章索引与视图的创建、管理、删除方法及统计信息的操作与应用等。
第9章存储过程与触发器的基本特点、创建、修改、删除等操作。
第10章事务和并发控制的基本特点、创建、管理和应用等基本操作。
第11章SQL Server 2016的安全架构，包括服务器、数据库和权限的安全架构设计，以及登录名、架构、用户、角色、权限方面安全性管理等。
第12章数据库的备份和还原，主要包括备份和还原的类型、还原前的准备与备份、还原的操作过程与策略等。
第13章系统自动化任务管理的基本工作原理，作业、操作员、警报创建和使用等。
第14章复制与性能监视。主要内容包括：复制的创建、管理与应用，系统监视和调整的目标、系统性能因素、监视策略和主要监视工具的使用等。
第15章数据库应用程序的开发过程，通过案例讲解如何使用Java访问SQL Server数据库，开发数据库应用程序等。
本书由孙福振编写第12章，苏晶编写第15章，其他章节由姜桂洪编写，全书由姜桂洪统稿。
张冬梅、王德亮、吕兵等老师在本书的编写过程中给予了很多帮助，在此深表感谢。
由于编者水平有限，对于书中存在的不足之处，恳请读者批评指正。
编者
2018年12月

第3章
创建与管理数据库

SQL Server 2016将数据保存于数据库中，并为用户提供了访问这些数据的接口。数据库所存储的信息能否正确地反映现实世界，能否在系统运行过程中及时、准确地为各个应用程序提供所需的数据，关系到以此数据库为基础的应用系统的性能。
本章主要介绍数据库的基本概念及数据库的创建、修改、附加、分离和删除等基本操作，以及数据库快照的创建和数据库的分区管理等。
3.1数据库对象和数据库文件
3.1.1数据库的基本概念

数据库的
基本概念

SQL Server 2016将数据库映射为一组磁盘文件，并将数据与日志信息分别保存于不同的磁盘文件中，每个文件仅在与之相关的数据库中使用。因此，从物理角度看，数据库包括数据文件和日志文件。从逻辑角度看，数据库中的表、索引、触发器、视图、键、约束、默认值、规则、用户定义数据类型或存储过程及数据库本身，都可以理解为数据库对象。
1. 数据库的结构层次
SQL Server的数据库基本结构分为3个层次，反映了观察数据库的3种不同角度。以内模式为框架所组成的数据库叫做物理数据库，以概念模式为框架所组成的数据库叫做概念数据库，以外模式为框架所组成的数据库叫做用户数据库。
(1) 物理数据库。这是数据库的最内层，是物理存储设备上实际存储数据的集合。这些数据是原始数据，是用户加工的对象，由内部模式描述的指令操作处理的位串、字符和字组成。在SQL Server中就是存储文件，即由操作系统管理的数据文件和日志文件。
(2) 概念数据库。这是数据库的中间一层，是数据库的整体逻辑表示。概念数据库指出了每个数据的逻辑定义及数据间的逻辑联系，是存储记录的集合，涉及的是数据库所有对象的逻辑关系，而不是它们的物理情况，是数据库管理员概念下的数据库。在SQL Server中表现为由数据行和列组成的基本表。
(3) 用户数据库。这是用户所看到和使用的数据库，表示了一个或一些特定用户使用的数据集合，即逻辑记录的集合。数据库不同层次之间的联系是通过映射进行转换的。在SQL Server中表现为视图、报表和查询结果集等。
2. 数据库的逻辑结构
SQL Server的数据库逻辑结构可以理解为在运行SQL Server软件中观察到的数据库组成。例如，展开SQL Server 2016的示例数据库teaching，如图31所示，可以看到teaching数据库组成部分，如数据库关系图、表、视图等数据库对象。每个数据库对象完全限定的对象名称包含4部分：

server.database.schema.object即服务器.数据库.架构.数据库对象

以访问teaching数据库的表student为例，即使在当前服务器中也需要写成teaching.dbo.student的形式。如果从其他服务器中访问，前面还需加上服务器名。其中，dbo表示架构； schema在SQL Server中为架构的统称。

图31数据库的逻辑结构

3. 架构
架构是形成单个命名空间的数据库实体的集合。命名空间是一个集合，其内部的每个元素的名称都是唯一的。在SQL Server 2016中的默认架构是DBO。如果用户创建数据库时没有指定架构，系统将使用默认架构。展开一个数据库中的“安全性”→“架构”文件夹，就可以观察到系统架构列表。
4. 数据库所有者
数据库所有者(DBO)就是有权限访问数据库的用户，即登录数据库的网络用户。数据库所有者是唯一的，拥有该数据库中的全部权限，并能够提供给其他用户访问权限和功能。
5. 数据库的物理文件
每个SQL Server 2016数据库至少具有两个操作系统文件，即一个主数据文件和一个日志文件。主数据文件包含数据和数据库对象，日志文件包含恢复数据库中的所有事务所需的信息。
SQL Server 2016数据库具有以下3种类型的文件。
(1) 主数据文件包含数据库的启动信息，并指向数据库中的其他文件。用户数据和对象可存储在此文件中，也可以存储在次要数据文件中。每个数据库有一个主要数据文件，建议文件扩展名是.mdf。
(2) 次要数据文件是可选的，由用户定义并存储用户数据。通过将每个文件放在不同的磁盘驱动器上，次要文件可用于将数据分散到多个磁盘上，建议文件扩展名是.ndf。
(3) 事务日志文件保存用于恢复数据库的日志信息。每个数据库必须至少有一个日志文件，建议文件扩展名是.ldf。
默认情况下，数据和事务日志被放在同一个驱动器的同一个路径下，这是为处理单磁盘系统而采用的方法。但是，在生产环境中建议将数据和日志文件放在不同的磁盘上。

数据库的
常用对象

3.1.2数据库的常用对象
数据库对象是数据库的组成部分，除了数据库本身外，常见的对象有表、索引、视图、数据库关系图、默认值、规则、触发器、用户、存储过程、序列等，本节简要介绍这些对象的概念，为后续学习打下基础。
(1) 表(Table)。数据库中的表与日常生活中使用的表格类似，由行(Row)和列(Column)组成。其中，列由同类的信息组成，每列又称为一个字段，每列的标题称为字段名。行包括若干列的信息项。一行数据称为一个或一条记录，是有一定意义的信息组合。一个数据库表由一条或多条记录组成，没有记录的表称为空表。每个表中通常都有一个主关键字，用于唯一地确定一条记录。
(2) 索引(Index)。索引是根据指定的数据库表列建立起来的顺序。它提供了快速查询大量数据的方法。有的索引还可以限制表，使其指定的列数据不重复。
(3) 视图(View)。视图是一个虚拟的表，在数据库中实际并不存在。视图是由查询数据表产生的，可以用来控制用户对数据的访问，并能简化数据的显示，提高数据的安全性管理水平。
(4) 数据库关系图(Database diagram)。这是本数据库中的表之间的关系示意图，也称图表，利用图表可以编辑表与表之间的关系以及表的行列属性。
(5) 默认值(Default)。默认值是当在表中创建列或插入数据时，对没有指定其具体值的列或列数据项赋予事先设定好的值。
(6) 规则(Rule)。规则是对数据库表中数据信息的限制，其限定的是表的列。
(7) 存储过程(Stored Procedure)。存储过程是为完成特定功能而汇集在一起的一组SQL程序语句，经编译后存储在数据库中的SQL程序。
(8) 触发器(Trigger)。触发器是一个用户定义的SQL事务命令的集合。当对一个表进行插入、更改、删除时，这组命令就会自动执行。
(9) 用户(User)。用户是有权限访问数据库的使用者，同时需要自己输入登录账号和密码。一般来说，数据库用户分为管理员用户和普通用户，前者可对数据库进行修改删除，后者只能进行阅读、查看等操作。
除了以上列出的数据库对象之外，不同的数据库管理系统也有部分自定义的对象，将在具体学习中分别介绍，此处不再赘述。