北京理工大学计算机学院数据库应用系统开发实验指导

数据库应用系统开发

实验指导

(第2版)

北京理工大学计算机学院

2013年8月

目录

(第2版) .................................................................................................................................................... I 第1章SQL SERVER基本操作 (1)

1.1.本书的实验环境 (1)

1.2.SQL S ERVER组成 (1)

1.3.SQL S ERVER安装 (2)

1.4.SQL S ERVER 数据库引擎实例 (3)

1.5.SQL S ERVER M ANAGEMENT S TUDIO工具 (4)

1.6.登录管理 (7)

1.7.设置SQL S ERVER身份验证模式 (9)

1.8.修改SA登录的口令及状态 (10)

1.9.使用SQL S ERVER验证模式连接数据库服务器 (12)

1.10.小结 (14)

第2章安全管理 (15)

2.1.权限管理的基本方法 (15)

2.2.使用服务器角色给登录授予权限 (15)

2.3.使用数据库角色授权 (18)

2.4.指定特定对象的权限 (19)

2.5.实验目的、内容与要求 (24)

第3章表 (27)

3.1.表的概念 (27)

3.2.数据完整性 (28)

3.3.创建和修改表 (30)

3.4.查询表 (31)

3.5.自动编号列和标识符列 (31)

3.6.基本数据定义与查询 (36)

3.7.复杂查询 (37)

3.8.示例数据库 (38)

第4章TRANSACT-SQL编程 (43)

4.1.程序中处理错误 (43)

4.1.1.RAISERROR (43)

4.2.游标 (50)

4.2.2.处理游标中的行 (52)

i

4.3.存储过程 (54)

4.4.触发器 (54)

4.5.INSTEAD OF触发器示例二 (64)

4.6.实验 (66)

第5章数据库备份与恢复 (68)

5.1.实验的目的、内容与要求 (68)

5.2.备份与恢复概述 (68)

5.3.简单恢复模式下的备份与恢复 (73)

5.4.完整恢复模式下的备份与恢复 (75)

第6章数据库设计 (81)

6.1.概述 (81)

6.2.IDEF1X方法 (84)

6.3.ER WIN数据建模 (97)

6.4.合同管理系统数据建模示例 (109)

6.5.实验 (112)

第7章销售合同管理系统的程序开发 (114)

7.1.程序开发示例 (114)

7.2.实验内容与要求 (127)

第8章实验报告 (132)

8.1.实验报告 (132)

第9章合同管理应用需求描述 (133)

9.2.系统功能 (134)

9.3.售后服务管理需求 (136)

9.4.附件一合同示例 (138)

ii

第1章SQL Server基本操作

1.1. 本书的实验环境

本书的示例与Windows和SQL Server环境有关，具体的环境如下：

Windows环境：

Windows 7 旗舰版32位

主机名：GUO-PC

用户：guo，属于Administrators组

SQL Server环境：

SQL Server 2012 Express（SQL Server 11.0.2100）

1.2. SQL Server组成

SQL Server提供包括数据库引擎、Analysis Services、Reporting Services、Integration Services和Master Data Serivce服务等，这些服务由表1-1中的服务器组件提供。其中，数据库引擎是SQL Server的数据库服务器，Analysis Services是OLAP分析服务器，Reporting Services是报表服务器，Integration Services是数据集成服务器。本书的内容只涉及数据库引擎服务器，它可以完成数据库的管理、安全管理（用户与权限）、数据库基本对象（表、索引、视图）和T-SQL对象的管理等。

表1-1 SQL Server主要服务器组件

1

表1-2 SQL Server 主要管理工具

1.3. SQL Server安装

下载SQL Server Express 2012

SQL Server 2012 包括Enterprise、Business Intelligence、Standard三个主要版本，另外，微软还提供Web（Professional）、Developer和Express版本。SQL Server Express 2012是微软提供的一个免费版本。其下载的页面上提供了多种下载选项，其中，下载文件SQEXPRWT_x86_CHS.exe包括数据库引擎和工具SQL Server Management Studio Express。

注解：如果安装的不是Express版本，则需要选择安装的组件。本书的内容中需要安装数据库引擎服务器组件和客户端工具组件。因为有些组件需要额外的安装条件，这会明显降低安装的速度，也可能导致安装失败。

SQL Server Express 2012下载页面：

2

bd927ed719e8b8f67c1cb954/zh-cn/download/details.aspx?id=29062

SQL Server Express 2012 SQLEXPRWT_x86_CHS.exe下载地址：

bd927ed719e8b8f67c1cb954/download/3/6/E/36E9CA26-CC2C-4600-8D25-A152F949

8FA1/CHS/x86/SQLEXPRWT_x86_CHS.exe）

安装示例数据库AdventureWorks

后面内容中所提供的示例均使用SQL Server示例数据库AdventureWorks。SQL Server 2012的示例数据库AdventureWorks需要单独安装，其安装文件AdventureWorks2012_Data.mdf是AdventureWorks2012数据库的数据文件，其下载地址：bd927ed719e8b8f67c1cb954/Download/Release?ProjectName=msftdbprodsa

mples&DownloadId=165399&FileTime=129762331847030000&Build=20717

可以使用下面两种方法之一安装示例数据库AdventureWorks。

方法一：

（1）在SQL Server Management Studio中从数据库的弹出菜单中选择“附加”数据库。

（2）在附加数据库对话框中添加数据库，选择文件AdventureWorks2012_Data.mdf，注意要删除自动添加的日志文件，即完成AdventureWorks2012的安装。

（3）重命名AdventureWorks2012为AdventureWorks

方法二：

（1）在SQL Server Management Studio的查询分析器中执行下面的命令：

exec sp_attach_db@dbname=N'AdventureWorks2012',

@filename1=N'D:\SQL Server 2000 Sample Databases\AdventureWorks2012_Data.mdf'

GO

USE master

GO

ALTER DATABASE AdventureWorks2012MODIFY name=AdventureWorks

GO

1.4. SQL Server 数据库引擎实例

数据库引擎的实例（简称“实例”或“数据库实例”，你也可以把它称做“SQL Server 实例”）是作为操作系统服务运行的sqlservr.exe 进程。每个实例管理多个系统数据库（master、model、msdv和tempdb）以及一个或多个用户数据库（例如SQL Server示例数据库AdventureWorks）。

每台计算机都可以运行数据库引擎的多个实例。如果是第一次安装SQL Server，可以安装一个默认实例，一台计算机上只能安装一个默认实例。默认实例没有名称，连接默认认实例只需要提供计算机名，而连接有名实例则需要提供计算机名和实例名，如GUO-PC\SQLEXPRESS，其中，GUO-PC是运行实例的计算机名，SQLEXPRESS是实例

3

4 名。

应用程序必须连接到某一个实例，只有通过实例才能访问SQL Server 数据库。

数据库实例与其它的计算机进程没有本质上的区别，都是可执行程序的运行副本，所不同的是访问的数据不同。例如，运行Word 的可执行程序文件WINWORD.EXE 便会产生一个该程序的副本，它可以称为Word 实例，重复运行就会产生多个Word 实例，每个Word 文档都必须通过一个Word 实例才能够访问。Word 文档就相当于Word 数据库。 注解：严格意义上讲，不通过Word 实例也可以访问Word 文档。但从安全性的角度考虑，数据库软件厂商不会公开其数据库的内部结构（除了像Aceess 个人数据库外），也不会提供访问数据库的其它方式，只能通过实例访问数据库。

实例处理所有应用程序请求的数据库服务操作。在完成某一连接后，应用程序通过该连接将 Transact-SQL 语句发送给该实例。该实例将这些 Transact-SQL 语句解析为针对数据库中的数据和对象的操作并将操作的结果或错误消息返回给应用程序。

注解：与SQL Server 不同，Oracle 的实例定义为由后台进程和系统全局区SGA （用于访问数据库的内存）两部分组成，Oracle 实例并不负责解析SQL 语句，也不负责管理连接和传送数据，这些任务由Oracle 服务器进程完成。

1.5. SQL Server Management Studio 工具

1.5.1. 连接SQL Server 数据库引擎

运行SQL

Server Management

Studio （简称

SSMS ）。第一个出

现的界面是连接

SQL Server 服务器

的对话框，如图

1-1。

在图 1-1中，

在服务器类型中选

择“数据库引擎”

（注：如果只安装

了数据库引擎组件，则服务器类型列表框不能手动选择，它自动选择“数据库引擎”）。

服务器名称“GUO-PC\SQLPRESS ”中的GUO-PC 是所连接的服务器的主机名，而SQLEXPRESS 则是数据库实例名。如果连接默认实例，则只显示主机名。

图 1-1 SSMS 连接数据库服务器

5 身份验证列表框列出了“Windows 身份验证”和“SQL Server 身份验证”两种方式。身份验证在后面有专门的实验进行练习，因此在这里选择“Windows 身份验证”。

当成功连接到SQL 数据库服务器后，则进入SSMS 的基本操作界面，如图 1-2所示。

检查SQL Server 服务器的基本信息

对象资源管理器（图 1-2）的根节点是一个连接的数据库引擎节点，显示了SQL Server 数据库引擎的基本信息GUO-PC\SQLEXPRESS(SQL Server 11.0.2100-guo-PC/guo)，包括运行数据库引擎服务器的主机名GUO-PC 、数据库实例名SQLEXPRESS （如果是默认实例则没有实例名）、SQL Server 数据库引擎的版本11.0.2100、连接数据库引擎的登录名GUO-PC\guo 。

上面的示例是数据库引擎在本地主机运行，所

以，服务器名称和登录名中的主机都是GUO-PC 。如

果使用GUO-PC 上的用户连接的服务器运行的主机

名是Server1、实例名为EEXPRESS ，则显示的信息

应为：Server1\SQLEXPRESS(SQL Server

11.0.2100-guo-PC/guo)。

浏览对象资源管理器工具栏

其中，SSMS 左边的窗口是对象资源管理器，右边的窗口是摘要窗口。在对象资源管理器窗口的工具栏中有五个按钮，分别是连接、断开连接、停止、刷新和筛选。如果需要使用新的用户建立与数据库服务器的连接，则可以使用工具栏的“连接”按钮并选择“数据库引擎”（如图 1-3所示），这样就会出现与错误

!

图 1-2 SSMS 的基本操作界面

图 1-3 对象资源按理器的按钮

6 未找到引用源。完全相同的界面。

使用查询编辑器

可以使用SSMS （图 1-8中）的的“新建查询”或者“数据库引擎查询”打开一个查询编辑器（如图 1-4所示）。查询编辑器中可以执行T-SQL 语句，它还可以将其中的T-SQL 语句保存到相应的文件中。需要注意的是，查询编辑器标题栏中的连接信息，它决定了执行T-SQL 语句的环境与权限，图 1-4中的guo-PC\guo 表示使用登录guo-PC\guo 连接了数据库引擎。

图 1-4 SSMS 查询编辑器

检查数据库

展开SQL Server 服务器下面的节点

“数据库”后会显示该数据库服务器上所

有的数据库。其中，有系统数据库和示例

数据库“AdventureWorks ”。进一步展开

“系统数据库”节点后会显示所有的系统

数据库，这些数据库的具体信息会在后续

的内容中进一步学习。

可以选择其中的一个数据库节点如

master ，展开后显示该数据库的更详细的

信息，如图 1-5。

检查数据库服务器的安全性 展开对象资源管理器的“安全性”节点，可以查看数据库服务器的安全性，包括登录名、服务器角色和凭据。

注意登录名中的“guo-PC\guo ”，它的存在使得以Windows 的用户guo 登录到

Windows

图 1-5 master 系统数据库

后可以通过Windows身份验证模式直接连接到数据库服务器。具体的机制在后面的内容中会作进一步的解释。

1.6. 登录管理

为了测试SQL Server登录的管理，首先需要新创建一个Windows用户。下面的实验中新创建的Windows用户的名称为david。

为了测试如何操作才能使一个Windows的用户以Windows的身份验证模式登录到SQL Server，我们在实验的过程中分成多个步骤，这样的步骤被分成创建Windows用户、在SQL Server中创建相应的登录和给SQL Server的登录分配相应的权限三个步骤。每个步骤完成后进行测试以检查不正确的配置会发生什么样的问题，这样以加深对整个配置过程的理解。

具体的操作步骤如下：

（1）使用管理员用户guo创建Windows用户david。

（2）使用新的用户david登录Windows并连接SQL Server。如果成功则进行第5步，否则，进行第3步。

（3）使用用户guo为david用户创建SQL Server登录GUO-PC\david。

（4）第二次使用新的用户david登录Windows并连接SQL Server。

（5）使用管理员用户guo连接数据库并设置SQL Server登录GUO-PC\david的默认数据库和权限。

1.6.1.第一次连接SQL Server

使用新创建的Windows用户重新登录Windows，注意检查SSMS连接对话框中的连接信息是否是如图1-6所示内容，特别是用户名是否是本地主机的新用户david。然后使用SSMS连接SQL Server，连接成功。

7

图1-6 使用david用户连接数据库服务器

8 注解：在有些环境下，可能连接失败。Windows 用户使用Windows 身份验证连接数据库服务器必须在数据库服务器中有对应的登录。对应的登录有两种：一是为Windows 用户创建的登录，例如，Windows 主机GUO-PC 上的用户david 创建的数据库登录名为GUO-PC\david ，david 用户使用该登录连接数据库服务器；二是为Windows 用户组创建的登录，例如，在数据库服务器中有一个名为BUILTIN\Users 的登录，它是一个内置的登录，与所有属于Users 组的Windows 用户对应，每个属于Windows 用户组Users 的用户都使用该登录连接数据库（在没有对应用户登录的情况下）。如果在数据库服务器中没有对应的登录，则连接失败。

1.6.

2. 创建SQL Server 登录

新建登录需要使用具有相应权限的Windows 用户重新登录Windows 并连接SQL Server 。在SQL Server 对象资源管理器中选择“安全性”，点击右键并选择“新建登录名...”进入到SQL Server 新建登录对话框（如图 1-7所示）。在“登录名”文本框中输入所要创

建的登录名GUO-PC\david ，其中，GUO-PC 表示主机名，david 表示主机GUO-PC 上的Windows 用户名。

图 1-7 创建登录GUO-PC\david

注解：可以使用图 1-7中右边窗口中的“脚本”将要执行的操作转换为T-SQL语句并显示在一个查询编辑器窗口中，或者保存到一个文件中。

完成新建登录后，可以通过SSMS查看新建的登录是否确实存在。展开SSMS的“对象资源管理器”的“安全性”节点，检查前面操作所创建的SQL Server登录GUO-PC\david 是否存在。

1.7. 设置SQL Server身份验证模式

SQL Server提供了两种身份验证模式，一是Windows身份验证模式，二是SQL Server 和Windows身份验证模式（简称混合验证模式）。混合验证模式可以使用Windows身份验证模式，也可以使用SQL Server身份验证模式。Windows身份验证模式是假设登录到Windows的用户是安全的，在连接到SQL Server时不需要提供用户密码。SQL Server身份验证模式在连接数据库服务器时必须提供SQL Server的登录名和相应的密码。两种身份验证模式都使用登录连接数据库服务器。

两种验证模式中，Windows身份验证模式更为常用。特别是通过应用程序访问数据库时，由于不需要在应用程序中硬编码登录密码，所以提高了数据库服务器的安全性。而有些应用，比如用户通过Internet直接访问数据库时，由于用户本身无法登录到数据库服务器或其所在的域，因而就必需使用SQL Server身份验证模式。另外，有一些应用程序本身只支持SQL Server身份验证，例如ERwin Data Modeler 4.0。所以本实验的目的是通过操作掌握数据库服务器的身份验证模式的设置，以根据不同的应用设置不同的验证模式。

9

图1-8 修改SQL Server的验证模式

修改SQL Server身份验证的操作很简单，选择SSMS中对象资源管理器的SQL Server 服务器节点，通过弹出菜单的“属性”项进入到修改属性对话框。选择对话框左侧列表中的“安全性”，右侧的窗口中出现服务器安全属性窗口。在图1-8中选择SQL Server和Windows身份验证模式。当前是Windows身份验证模式，它是安装时设定的，安装时也可以设定为混合验证模式。

1.8. 修改sa登录的口令及状态

sa登录是SQL Server的一个特殊登录，使用它连接数据库服务器后可以完成对服务器所有的管理操作，因此，需要对sa登录进行重点保护。

sa登录的口令在安装过程中可以设定，但如果在安装时选择的是Windows身份验证

模式则无法设定它的密码，且该登录的状态也被设定为“禁用”。

10

修改sa登录的密码与状态与修改其它SQL Server登录的密码与状态具有相同的操作过程。从SSMS的“安全性”节点选择相应的登录（这里为sa），进入到登录属性修改对话框。

图1-9 sa登录属性

输入新的密码，然后选择左边列表中的“状态”，进入到状态属性修改页面，如图1-10

所示。注意，sa初始的状态为“禁用”，把登录状态修改为“启用”并点击确定。

11

12 图 1-10 sa 登录的状态

1.9. 使用SQL Server 验证模式连接数据库服务器

在完成

对数据库服

务器的登录

验证模式的

修改后，可

以使用

Windows 身

份验证也可

以使用SQL

Server 身份

验证模式登

录数据库服

务器。在SQL Server 登录sa 属性的修改后，现在可以使用SQL Server 登录连接数据库服务器了。

图 1-11 使用sa 登录SQL Server

13 使用

SSMS 断开与

服务器的连接，

重新连接时使

用SQL Server

登录模式并使

用sa 用户登录

连接数据库服

务器，如图2-7所示。连接后注意SSMS 中显示的信息的变化。

图 1-12 使用sa 登录SQL Server 的SSMS

图1-13对象资源管理器的安全性

1.10. 小结

通过本实验完成了对SSMS的初步认识，SSMS其它的功能操作分别在以后的各实验进行完成。如果需要系统的了解SSMS的功能，可以第2章或SQL Server的联机文档。

14

第2章安全管理

2.1. 权限管理的基本方法

可以使用下面几种方式授予登录操作数据库的权限：

（1）将登录加入到服务器角色中。例如，将登录加入到sysadmin服务器角色中，登录就具有对所有数据库的全部操作权限。

（2）将登录映射到某一个数据库的某一个用户，并将映射的用户加入到具有相应操作权限的数据库角色中。

（3）将登录映射到某一个数据库的某一个用户，授予该用户对某些特定的数据库对象的操作权限。

其中，方法1和方法2都是一种隐含的授权操作，有时这些方法会将过多的权限授予登录。方法3是一种更为细化的授权操作。

2.2. 使用服务器角色给登录授予权限

2.2.1.数据库服务器角色

SQL Server 2012提供了9个服务器角色（“角色”类似于Windows中的“组”），这些角色从服务器一级定义了对数据库服务器操作的权限。但这些角色的管理只限于给这些角色添加或删除成员，并不能创建新的服务器角色或删除已有的服务器角色，也不能修改它们中的权限，所以它们也被你为固定服务器角色。

除固定服务器角色外，可以创建删除用户定义的服务器角色，也可以修改用户定义的服务器角色的权限。

需要注意的是服务器角色与数据库角色的区别，后者是在数据库一级定义的操作权限，它是针对某一数据库而不是数据库服务器的。

服务器角色的成员是数据库服务器的登录，既可以是Windows登录也可以是SQL Server登录。通过将某一登录添加到某一个服务器角色中，该登录就继承服务器角色的权限，拥有对整个数据库服务器相应的操作权限。例如，将前面创建的Windows登录GUO\david加入到sysadmin服务器角色中，该登录就拥有对整个数据库服务器的所有操作权限（sysadmin拥有对数据库服务器操作的所有权限）。

如果不需要对整个服务器进行管理，则一般不需要将登录添加到某一个服务器角色中，而是使用数据库角色来完成安全权限的配置。

2.2.2.权限不足的操作

创建一个使用SQL Server验证模式的SQL Server的登录student，首先使用具有相应操作权限的登录（如sa）连接到SQL Server数据库服务器，在“安全性”节点中点击右

15

键并选择“创建新登录”进入到创建新登录的对话框，在对话框中输入新创建的登录的名称student，选择SQL Server验证模式，如果不需要设置登录的密码则不能选择“强制实施密码策略”，否则必须设置由字母和数字混合组成的密码。

现在，使用新创建的登录student 连接到SQL Server数据库服务器，并创建另一个新登录的操作或其它的操作，则会出现如下的错误信息：

图2-1 不具有权限的错误信息

2.2.

3.修改登录的服务器角色集

使用sa或Windows的administrators用户组的用户连接到数据库服务器，选择“安全性/登录”节点中的student登录，修改属性将sysadmin服务器角色加入到student登录的服务器角色集中。

注解：修改登录的服务器角色集类似于Windows中修改用户的组属性，即修改Windows用户隶属于组的集合。

16

图2-2 修改登录的服务器角色集

也可以使用如下的T-SQL命令将登录添加到服务器成员列表中：

--将登录student添加到服务器角色sysdamin中

ALTER SERVER ROLE[sysadmin]ADD MEMBER[student]

GO

注解：在联机文档中提供的命令示例ALTER SERVER ROLE diskadmin ADD [Domain\Juan]有误。

在将新的登录student添加到sysadmin角色中后，登录student就具有了对数据库服务所有的操作权限。

再次进行前面的创建一个新登录的操作，这次发现student登录可以完成这一操作。实际上，student可以完成所有对数据库服务器的操作。

完成操作后使用下面的命令将登录student从sysadmin中删除。

--从服务器角色sysadmin中删除角色成员student

ALTER SERVER ROLE[sysadmin]DROP MEMBER[student]

GO

17

18 2.3. 使用数据库角色授权

使用数据库角色授权是将对某一数据库的所有对象某些操作权限授予某一登录。但这种授权不是直接将操作权限授予登录，而是首先将登录映射到某一个数据库的某一用户，再将用户添加到数据库角色成员列表中。例如，图2-3中的登录student 映射到数据库AdventureWorks 的stu_user 用户，然后将stu_user 添加到db_owner 数据库角色成员列表中，这样stu_user 就继承了db_owner 的所有权限，而student 登录又继承了stu_user 对数据库AdvertureWorks 的操作权限，因为db_owner 包含了对数据库的所有操作权限，所以，student 登录就获得了对数据库AdventureWorks 的所有操作权限。

操作步骤如下：

（1） 使用系统管理员登录（在Windows Administrators 组中的Windows 用户或SQL

Server 的sa 登录）连接到数据库。

（2） 创建一个登录（也可以使用没有经过授权的已有的登录）student 。

CREATE LOGIN student

WITH PASSWORD ='', DEFAULT_DATABASE =master

图 2-3 登录的数据库用户映射

本文来源：https://www.bwwdw.com/article/tzfe.html