数据库实验指导—SQL - Server - 2008

目录

目录 ................................................................................................................................... 2 实验1 SQL Server的安装及管理工具的使用 ............................................................ 3 实验2 交互式SQL ....................................................................................................... 4 实验3 数据完整性和安全性管理 .............................................................................. 16 实验4 触发器和存储过程实验 .................................................................................... 26 实验5 数据库备份与恢复 ............................................................................................ 30 实验6 数据库设计综合应用 ........................................................................................ 31

实验1 SQL Server的安装及管理工具的使用

一、实验目的

1、通过对某个商用数据库管理系统的使用，了解DBMS的工作原理和系统构架。 在此推荐数据库管理系统SQL Server 2005，该系统已经在机房FTP上。你也可以从微软官方网站上下载该系统。 2、熟悉对DBMS的操作

二、实验内容

1、根据安装文件的说明安装数据库管理系统。在安装过程中记录安装的选择，并且对所作的选择进行思考，为何要进行这样的配置，对今后运行数据库管理系统会有什么影响。 2、学会启动和停止数据库服务，思考可以用哪些方式来完成启动和停止。 3、掌握管理工具的使用

(1)SQL Server Management Studio的使用

? 连接服务器的方法 ? 查询编辑器的使用 (2)配置管理器的使用

4、了解数据库系统的逻辑组件：

它们主要是数据库对象，包括基本表、视图、触发器、存储过程、约束等。今后将学习如何操作这些数据库对象。

三、实验报告要求

１、给出安装过程中出现的错误及解决方法。

２、根据你所了解的SQL Server工具，写出部分主要组件的功能（以SQL Server2005为例，写出SQL Server Management Studio、配置管理器）。

实验2 交互式SQL

2.1创建数据库及表

一、实验目的

本次实验了解SQL Server 2005的启动，熟悉如何使用SSMS和SQL建立数据库和表，并加深对于完整性的理解。

二、背景知识

在使用数据库的过程中，接触最多的就是数据库中的表。表是数据存储的地方，是数据库中最重要的部分，管理好表也就管理好了数据库。

表是由行和列组成的。创建表的过程主要就是定义表的列的过程。表的列名在同一个表中具有唯一性，同一列的数据属于同一种数据类型。除了用列名和数据类型来指定列的属性外，还可以定义其它属性：是否为空、默认值、标识符列、全局唯一标识符列等。

约束是SQL Server提供的自动保持数据库完整性的一种方法，定义了可输入表或表的单个列中的数据的限制条件。在SQL Server中有5种约束：主关键字约束（Primary Key Constraint）、外关键字约束（Foreign Key Constraint）、惟一性约束（Unique Constraint）、检查约束（Check Constraint）和默认约束(Default Constraint)。

三、实验内容

方法1：在SSMS中创建数据库和表

在本练习中，将使用SQL Server2005数据库管理系统创建Student数据库，并在Student数据库中创建表Student，使你能够掌握使用SQL Server2005数据库管理系统创建数据库和表的方法。

1) 启动SQL Server2005：

依次单击开始－>所有程序－>SQL Server 2005－>SQL Server Management Studio 启动SQL Server 2005数据库管理系统 2) 登录数据库服务器：

点击“连接到服务器“对话框中的连接按钮连接到SQL Server 2005数据库服务器。

3) 创建数据库Student

在SQL Server 2005数据库管理系统的左边栏“对象资源管理器”中右击数据库对象，在弹出的快捷菜单中单击“新建数据库”命令。

在弹出的“新建数据库”对话框右侧的数据库名称中输入数据库名称“Student”，然后单击确定。

4） 在Student数据库中新建表”Student”

单击SQL Server 2005数据库管理系统的左侧的“对象资源管理器”栏中的”刷新”

按

钮，以显示出新建的数据库“Student”。 依次展开左侧栏对象资源管理器中的“数据库”－>“Student”，并右击Student数据库中的表项目，在弹出的快捷菜单中单击“新建表”命令。

在右侧在工作区中输入“Student”表的信息，该表具有如下列： 列名 Sno Sname Ssex Sage Sdept 数据类型 CHAR(5) CHAR(10) BIT INT CHAR(15) 完整性约束 主码 非空 无 无 无

单击文件菜单中的保存命令保存该表，并取名为“Student”。

方法二、使用SQL语句创建数据库和表

1) 单击工具栏中的“新建查询”按钮，并在弹出的“连接到服务器”对话框中单击“连

接”按钮，新建一个SQL脚本。

2) 在右侧的SQL脚本输入框中输入如下SQL代码：

CREATE DATABASE STUDENT -- 创建STUDENT数据库 GO

USE STUDENT GO

-- 转到STUDENT数据库

CREATE TABLE STUDENT -- 在STUDENT数据库中创建表STUDENT (

Sno CHAR(5) PRIMARY KEY, Sname CHAR(10) NOT NULL, Ssex BIT, Sage INT,

Sdept CHAR(15) )

单击工具栏中的

执行按钮，运行SQL语句，完成数据库与表的创建。

四、实验任务

1．用两种方法建立一个新数据库HRM（也可任意取名）。 2．在数据库HRM中，建立如下所示的三个表： Employee表 列名 EmployeeID 数据类型 Char 长度 6 是否允许为空 否 说明 员工编号，主键

Name Birthday Sex Address Zip PhoneNumber EmailAddress DepartmentID Departments表 列名 DepartmentID DepartmentName Note Salary表 列名 EmployeeID Income OutCome Char Datetime Bit Char Char Char Char Char 数据类型 Char Char Text 数据类型 Char Float Float 10 8 1 20 6 12 30 3 长度 3 20 16 长度 6 8 8 否 否 否 是 是 是 是 否 是否允许为空 否 否 是 是否允许为空 否 否 否 姓名 出生日期 性别 地址 邮编 电话号码 电子邮件地址 员工部门号，外键 说明 员工部门号，主键 部门名 备注 说明 员工编号，外键 收入 支出 3.对三张表输入数据（可任意输入，但注意要符合完整性要求，否则会出错，在本实验中，外键设置可先不做要求）

五、实验报告要求

1、要求给出三个表创建的SQL语句，要求建表时给出主外键约束、唯一约束、取空值约束、用户自定义的约束等。（注：在实验报告上只需写出其中一个表的SQL语句即可）

2、举例说明如何操作会违背完整性（实体完整性、参照完整性）。

2.2 SQL数据查询语句

一、实验目的

? 了解SQL Server 2005查询编辑器的启动，熟悉如何在SQL Server 2005查询编辑器

查询记录。

? 掌握SELECT语句的基本语法和查询条件表示方法

? 掌握GROUP BY 和ORDER BY 子句的作用和使用方法 ? 掌握连接查询和子查询的使用方法

二、实验内容

1、单击工具栏中的“新建查询”按钮，并在弹出的“连接到服务器”对话框中单击“连接”按钮，新建一个SQL脚本。

2、在SQL脚本中写数据查询语句执行； 从表中查询数据：

在查询分析器的查询窗口中输入SQL语句，如图所示。

点击按钮，执行该SQL语句，在查询窗口下部出现一个输出窗口，如图所示。

三、实验任务

1．对上节建立的表输入数据： Departments表： DepartmentID 1 2 3 Employee表 EmployeeID Name Birthday Sex Address Zip 1001 1002 1003 2001 2002 2003 3001 3002 Salary表 EmployeeID 1001 1002 1003 2001 2002 Income 3600 3300 3700 4000 3800 OutCome 1500 1000 1200 1600 1800 李勇 王敏 刘晨 张立 刘毅 张玫 徐静 赵军 78-3-12 80-11-2 78-6-22 78-8-1 82-1-23 81-3-15 76-8-12 79-2-19 0 1 0 0 0 1 1 0 河南 河南 河南 河南 河南 河南 河南 河南 PhoneNumber EmailAddress ly@henu.edu.cn lc@henu.edu.cn zl@henu.edu.cn ly@henu.edu.cn zm@henu.edu.cn xj@henu.edu.cn zj@henu.edu.cn DepartmentID 1 1 2 2 2 3 3 475001 3880378 475002 0378311 475003 0378322 475004 0378333 475005 0378344 475006 0378355 475007 0378366 475008 0378377 DepartmentName 财务部 研发部 人力资源部 Note 财务部 研发部 人力资源部 wm@henu.edu.cn 1

2003 3001 3002 3800 4200 4100 1500 2000 1800 2、练习下面简单的查询语句：

a) 查询每个雇员的所有信息 b) 查询每个雇员的地址和电话

c) 查询EmployeeID为000001的雇员的地址和电话。 d) 查询女雇员地址和电话，并用AS子句将结果中各列的标题分别指定为“地址”和“电话”。

e) 计算每个雇员的实际收入。 f) 找出所有姓王的雇员的部门号。

思考：找出所有地址中含有“中山”的雇员的号码和部门号。 （Where Address like ‘%中山%’）

3、练习多表连接查询和嵌套查询：

a) 查询每个雇员的情况及工资情况（工资=Income - Outcome） b) 查询财务部工资在2200元以上的雇员姓名及工资情况 c) 查询研发部在1966年以前出生的雇员姓名及其工资详情 d) 查询人力资源部雇员的最高和最低工资 e) 将各雇员的情况按工资由低到高排列 f) 求各部门的雇员数

g) 找出所有在财务部和人力资源部工作的雇员的编号 h) 统计人力资源部工资在2500以上雇员的人数 i) 求财务部雇员的总人数 j) 求财务部雇员的平均工资

k) 查找比所有财务部的雇员工资都高的雇员的姓名

l) 查找财务部年龄不低于研发部所有雇员年龄的雇员的姓名 m) 查找在财务部工作的雇员的情况

四、实验报告要求

1、写出与上述查询任务相对应的SQL查询语句（注：在实验报告上写出2-d、2-e、2-f及3中所有练习的SQL语句）

2、并记录在实验过程中遇到的问题、解决办法及心得体会。

2.3 视图的定义和操作

一、实验目的：

理解视图的概念，掌握视图的使用方法。

二、预计实验时间：上机一次

三、实验过程

1． 对应HRM数据库，参照前面实验中完成的查询，按如下要求自行设计视图：

1）基于单个表按投影操作定义视图。

举例：定义一个视图用以查看所有员工的编号、姓名和出生日期。 2）基于单个表按选择操作定义视图。

举例：定义一个满足sex=’true’的员工的所有信息的视图。 3）基于单个表按选择和投影操作定义视图。

举例：定义一个视图用以查看部门号码为‘2’的所有员工的姓名、电话和邮件地址。 4）基于多个表根据连接操作定义视图。

举例：定义一个视图用以查看所有员工的姓名、部门名及工资。 5）基于多个表根据嵌套查询定义视图。

举例：定义一个比所有财务部的雇员工资都高的雇员的信息的视图 6）定义含有虚字段（即基本表中原本不存在的字段）的视图。 举例：定义一个视图用以查看所有雇员的编号、姓名、年龄。

2． 分别在定义的视图上进行查询、插入、更新和删除操作，分情况（查询、更新）讨论哪些操作可以成功完成，哪些不能成功完成，并分析原因。

四、实验报告要求：

1、在实验报告中要给出上述任务的视图定义语句；（注：实验报告上写出4、5、6的SQL语句）

2、分情况讨论哪些操作可以成功完成，哪些不能成功完成。

查询操作：举出一个进行查询操作的例子。更新操作：更新操作包含插入、更新、删除，针对不能成功完成更新操作的情况举出两个例子，并分析原因。

2.4 SQL数据更新语句

一、实验目的

? ? ? ?

加深对SQL数据更新（插入、修改及删除）语句的基本语法格式的掌握 掌握单个元组及多个元组的插入、修改及删除操作的实现过程 加深对更新操作时数据库中数据一致性问题的了解

加深对约束条件在数据更新操作执行中的作用问题的了解

二、实验内容

方法一：在SSMS中插入、删除及修改数据，

方法二：在查询编辑器中写SQL插入、删除及修改数据

三、实验任务

设有一个SPJ数据库，包括S，P，J，SPJ四个关系模式： S（SNO，SNAME，STATUS，CITY）

P（PNO，PNAME，COLOR，WEIGHT）

J（JNO，JNAME，CITY）

SPJ（SNO，PNO，JNO，QTY）

供应商表S由供应商代码（SNO）、供应商姓名（SNAME）、供应商(STATUS)、供应商所在城市（CITY）组成；

零件表P由零件代码（PNO）、零件名（PNAME）、颜色（COLOR）、重量（WEIGHT） 组成；

工程项目表J由工程项目代码（JNO）、工程项目名（JNAME）、工程项目所在城市（CITY）组成；

供应情况表SPJ由供应商代码（SNO）、零件代码（PNO）、工程项目代码（JNO）、供应数量（QTY）组成，表示某供应商供应某种零件给某工程项目的数量为QTY。试完成以下操作：

1．在S表中插入元组“s6，华誉，40，广州” 2．在J表中插入元组“j8，传感器厂”

3．对每一个供应商，求他为各种工程供应零件的总数量，并将此结果存入数据库 4．将P表中PNO值为p6的元组的color属性值改为绿，weight属性值改为60 5．将SPJ表中前4个元组的qty属性值统一修改为300

6．将S表中city属性名含有“京”或“津”的相应status属性值增加100 7．将供应商s2为“一汽”工程项目所供应的零件数量修改为2000 8．将全部红色零件的颜色修改为浅红色 9．由s5供给j4的零件p6改为由s3供应

10．在SPJ表中新增一名为SDATE的属性列，对该表中的每一元组在SDATE属性列上填

上实验当时的日期和时间

11．删除所在城市为“广州”的供应商记录

12．删除所有零件名称中第一个字为“螺”字的零件记录，并在供应情况表中删除相应的记录

13．删除s3和s4两供应商为“三建”工程供应“螺母”或“螺丝刀”零件的相应供应情况数据信息

四、实验报告要求

1、写出与上述任务相对应的SQL更新语句（实验报告上写出3、5、10的SQL语句） 2、并记录在实验过程中遇到的问题、解决办法及心得体会。

实验3 数据完整性和安全性管理

3.1 数据完整性管理

一、实验目的

? 掌握域完整性的实现方法。 ? 掌握实体完整性的实现方法。 ? 掌握参照完整性的方法。

二、实验内容

数据库的完整性设置。

三、实验步骤

? 可视化界面的操作方法： ? 实体完整性

1．将student表的“sno”字段设为主键：在表设计界面中，单击左边的行选定块，选定“sno”字段，单击工具按钮

设置主键。如图1所示：

图1

1． 将“sc”表的“sno”和“cno”设置为主键：在表设计界面中，单击并按住Ctrl键

拖动左边的行选定块，选定sno和cno字段，单击工具按钮所示：

设置主键。如图2

图2

? 域完整性 3. 将“ssex”字段设置为只能取“男”，“女”两值：在表设计界面，点击鼠标右键——CHECK约束,——添加约束，添加CK_student_sex名称，然后在约束表达式框中输入“ ssex in ('男','女') ”。如图3所示。

图3

? 参照完整性

4. 将“student”表和“sc”表中的“sno”字段设为参照: 打开“sc”表的设计界面, 点击工具栏按钮

, 在弹出的属性(properties)对话框中点击“新建”按钮，在“主键表(Primary key

table)”下拉框中选择“student”表，在其下的字段选择框中选择“sno”，在“外键表(Foreign

key table)”下拉框中选择“sc”表，在其下的字段选择框中选择“sno”，单击关闭即可。见图4。

图4

? 命令方式操作方法：

? 实体完整性

1．将“student”表的“sno”字段设为主键： 当“student”表已存在则执行：

alter table student add constraint pk_sno primary key (sno) 当“student”表不存在则执行:

Create table student(sno CHAR(5) primary key ,

sname CHAR(10) NOT NULL, ssex CHAR(2), sage int,

sdept CHAR(4))

注:可用命令“drop table student”删除“student”表

2. 添加一身份证号字段,设置其惟一性.(注: 操作前应删除表中的所有记录) Alter table student add id char(18) unique (id) 3. 将“sc”表的“sno”和“cno”设置为主键： 当“sc”表已存在则执行：

alter table sc add constraint PK_SnoCno primary key (sno,cno) 当“sc”表不存在则执行：

Create table sc(sno CHAR(5),

cno CHAR(2),

grade INT NULL,

constraint PK_SnoCno primary key (sno,cno))

? 域完整性

4. 将“ssex”字段设置为只能取“男”，“女”两值： 当“student”表已存在则执行：

alter table student add constraint CK_Sex check (ssex in ('男' ,'女')) 当“student”表不存在则执行：

Create table student(sno CHAR(5) primary key ,

sname CHAR(10),

ssex CHAR(2) check (ssex in ('男' ,'女')) , sage int, sdept CHAR(4))

5. 设置学号字段只能输入数字:

alter table student add constraint CK_Sno_Format check (sno like '[0-9][0-9][0-9][0-9][0-9]')

6. 设置身份证号的输入格式:

alter table student add constraint CK_ID_Format check ((id like

'[0-9][0-9][0-9][0-9][0-9][0-9][1-2][0-9][0-9][0-9][0-1][0-9][0-3][0-9][0-9][0-9][0-9]_') OR (id like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-1][0-9][0-3][0-9][0-9][0-9][0-9]'))

7. 设置18位身份证号的第7位到第10位为合法的年份(1900-2050) alter table student add constraint CK_ID_Format2 check (len(id)=18 and

( (convert(smallint,substring(id,7,4) )>=1900) and(convert(smallint,substring(id,7,4) )<=2050)) ) 6. 设置男生的年龄必须大于22, 女生的年龄必须大于20.

Alter table student add constraint CK_age check (ssex='男' and sage>=22 or ssex='女' and sage>=20 )

? 参照完整性

9. 将“student”表和“sc”表中的“sno”字段设为参照: 当“sc”表已存在则执行：

alter table sc add constraint FP_sno foreign key (sno) references student(sno )

当“sc”表不存在则执行：

Create table sc(sno CHAR(5) constraint FP_sno

foreign key references student(sno), cno CHAR(2),grade INT NULL,

constraint PK_SnoCno primary key (sno,cno) )

? 完整性验证

1． 实体完整性： 在“student”表数据浏览可视化界面中输入学号相同的两条记录将会出现错误如图5所示：

图5

或者在命令窗口输入下面两条命令也会出现错误提示：

insert into student values('95003','张三','男',24,'CS','42222919901012903X')

insert into student values('95001','李四','女',21,'CS','422229199510129031')

图6

下面的语句用来验证“sc”表中的实体完整性： insert into sc values('95002', '10',65) insert into sc values('95002', '10',90)

图7

2. 域完整性:

使用下面的语句验证“ssex”字段的域完整性：

insert into student values('95009','张匀','大',20,'CS', ‘42222919901012904X’)

3．参照完整性： 使用下面的语句“验证”sc表中的“sno”字段的域完整性(假设student表中没有学号为“95998”的学生记录)：

insert into sc values('98998', '10',98)

四、实验任务:

1、建立课程的实体完整性, 和课程号cno的参照完整性;

2、对HRM数据库，练习建立三个表的主外键约束、唯一约束、取空值约束、用户自定义的约束（参考HRM数据库表定义图中说明）；

3、建立salary表的Income字段限定在0-9999之间。

五、实验报告要求

1、写出与上述任务相对应的SQL语句

2、并记录在实验过程中遇到的问题、解决办法及心得体会。

3.2 数据库安全性管理

一、实验目的

? ? ? ?

掌握SQL Server身份验证模式。

掌握创建登录账户、数据库用户的方法。 掌握使用角色实现数据库安全性的方法。 掌握权限的分配。

二、实验内容

1、设置身份验证模式：Windows身份验证模式和混合模验证模式。 2、设置登录账户 3、设置数据库用户 4、设置数据库角色。 5、设置管理对象的权限

三、实验步骤

1.设置身份验证模式

? 依次单击开始－>所有程序－>SQL Server 2005－>SQL Server Management Studio

启动SQL Server 2005数据库管理系统 ? 连接成功后，右键你的实例，选择\属性\

? 在\属性\窗口中， 转到\安全性)项，在\服务器身份验证\中设置为\

Server和Windows身份验证模式\， 确定，根据提示，应该重新启动sql服务 ? 然后执行下面的语句启用sa用户, 同时清除sa的密码(能成功登陆后再根据你的需

要设置)

EXEC sp_password null,null,'sa' ALTER LOGIN sa ENABLE

? 语句执行完成后, 再用sa连接你的实例, 应该就没有问题了. 2.设置登录账户

? 创建使用 Windows 身份验证 (SQL Server Management Studio) 的 SQL Server 登

录名

? ?在 SQL Server Management Studio 中，打开对象资源管理器并展开要在其中

创建新登录名的服务器实例的文件夹。

? ?右键单击“安全性”文件夹，指向“新建”，然后单击“登录名”。 ? ?在“常规”页上的“登录名”框中输入一个 Windows 用户名。 ? ?选择“Windows 身份验证”。 ? ?单击“确定”。

? 创建使用 SQL Server 身份验证 (SQL Server Management Studio) 的 SQL Server

登录名

? ?在 SQL Server Management Studio 中，打开对象资源管理器并展开要在其中

创建新登录名的服务器实例的文件夹。

? ?右键单击“安全性”文件夹，指向“新建”，然后单击“登录名”。

?在“常规”页上的“登录名”框中输入一个新登录名的名称。 ?选择“SQL Server 身份验证”。 ?输入登录名的密码。

?选择应当应用于新登录名的密码策略选项。通常，强制密码策略是更安全的选择。

? ?单击“确定”。

? 通过 Transact-SQL 创建使用 Windows 身份验证的 SQL Server 登录名

? ?在查询编辑器中，输入以下 Transact-SQL 命令：CREATE LOGIN

Windows User> FROM WINDOWS; GO

? 通过 Transact-SQL 创建使用 SQL Server 身份验证的 SQL Server 登录名

? ?在查询编辑器中，输入以下 Transact-SQL 命令：CREATE LOGIN

name> WITH PASSWORD = '' ; GO

3.设置数据库用户

? 创建数据库用户

? 在 SQL Server Management Studio 中，打开对象资源管理器并展开要在其中

创建新登录名的服务器实例的文件夹。 ? 依次展开数据库，某个具体数据库，“用户” ? 右键单击用户，选择“新建用户” ? 输入用户名，选择登录名，确定 ? 通过 Transact-SQL 创建数据库用户（create user）： create user dba for login dba with

default_schema=dbo

? 并指定数据库用户“dba” 的默认 schema 是“dbo”。这意味着 用户“dba”

在执行“select * from t”，实际上执行的是 “select * from dbo.t”。

4设置数据库角色

? 新建角色

? 在 SQL Server Management Studio 中，打开对象资源管理器并展开要在其中

创建新登录名的服务器实例的文件夹。 ? 依次展开数据库，某个具体数据库，“角色” ? 右击选中新建角色

? 通过 Transact-SQL新建角色：CREATE ROLE role_name ? 在对象资源管理器中设置数据库角色

? 在 SQL Server Management Studio 中，打开对象资源管理器并展开要在其中

创建新登录名的服务器实例的文件夹。 ? 依次展开数据库，某个具体数据库，“用户” ? 选中某用户，右击“属性”，在展开的数据库用户属性对话框中进行设置 ? 通过 Transact-SQL设置数据库角色

? 通过加入数据库角色，赋予数据库用户“dba”权限： exec sp_addrolemember

'db_owner', 'dba'

? 此时，dba 就可以全权管理数据库 mydb 中的对象了。

? 如果想让 SQL Server 登陆帐户“dba”访问多个数据库，比如 mydb2。可以

让 sa 执行下面的语句：

? use mydb2 go create user dba for login dba with default_schema=dbo go exec

sp_addrolemember 'db_owner', 'dba' go

? 此时，dba 就可以有两个数据库 mydb, mydb2 的管理权限了！

? ? ? ?

5.设置管理对象的权限

? 在对象资源管理器中授权：

? 选中需要授权的登录名、用户名或角色名 ? 右键单击选择“属性”，在弹出的该对象的属性窗口中选择“安全对象”，进

行权限分配

? 通过 Transact-SQL分配权限：通过GRANT和REVOKE语句

四、实验任务

1、创建角色ProgramerRole，拥有创建表，存储过程，视图权限，拥有对Salary表的查询、修改、插入权限

2、创建一个登录账号Testlogin

3、创建对应于这个登录账号的数据库用户TestUser 4、将用户TestUser添加到TestRole角色中

五、实验报告要求

1、写出与上述任务相对应的SQL语句

2、思考数据库服务器上的角色有哪些？权限类型有哪些？ 3、并记录在实验过程中遇到的问题、解决办法及心得体会。

实验4 触发器和存储过程实验

4.1 触发器实验

一、实验目的

? 掌握触发器的创建、修改和删除操作。 ? 掌握触发器的触发执行。 ? 掌握触发器与约束的不同。

二、实验要求

1.创建触发器。

2.触发器执行触发器。

3.验证约束与触发器的不同作用期。 4.删除新创建的触发器。

三、实验内容

（一）示例

1.创建触发器

①启动SQL Server 查询编辑器，选择要操作数据库，如“sc（学生选课）”数据库。 ②在查询命令窗口中输入以下CREATE TRIGGER语句，创建触发器。

为sc(学生选课)表创建一个基于UPDATE操作和DELETE操作的复合型触发器，当修改了该表中的成绩信息或者删除了成绩记录时，触发器被激活生效，显示相关的操作信息。

--创建触发器

CREATE TRIGGER tri_UPDATE_DELETE_sc ON sc

FOR UPDATE，DELETE AS

--检测成绩列表是否被更新 IF UPDATE(成绩) BEGIN

--显示学号、课程号、原成绩和新成绩信息 SELECT INSERTED.课程号，DELETED.成绩AS原成绩， INSERTED.成绩AS新成绩 FROM DELETED ，INSERTED

WHERE DELETED.学号=INSERTED.学号 END

--检测是更新还是删除操作 ELSE IF COLUMNS_UPDATED( )=0 BEGIN

--显示被删除的学号、课程号和成绩信号

SELECT 被删除的学号=DELETED.学号，DELETED.课程号， DELETED.成绩AS原成绩 FROM DELETED END ELSE

--返回提示信息 PRINT ‘ 更新了非成绩列！’

③点击快捷工具栏上的快捷按钮，完成触发器的创建。 2.触发触发器

①在查询命令窗口中输入以下UPDATE sc语句，修改成绩列，激发触发器。

UPDATE sc

SET成绩=成绩+5

WHERE 课程号=’101’

②在查询命令窗口中输入以下UPDATE sc语句修改非成绩列，激发触发器。

UPDATE sc

SET 课程号=’113’ WHERE 课程号=’103’

③在查询命令窗口中输入以下DELETE sc 语句，删除成绩记录，激发触发器。

DELETE sc

WHERE 课程号=’102’

3. 比较约束与触发器的不同作用期

①在查询命令窗口中输入并执行以下ALTER TABLE 语句，为sc表添加一个约束，使得成绩只能大于等于0且小于等于100。

ALTER TABLE sc

ADD CONSTRAINT CK_成绩

CHECK(成绩>=0 AND成绩<=100)

②在查询命令窗口中输入并执行以下UPDATE sc语句，查看执行结果。

UPDATE sc SET成绩=120

WHERE 课程号=’108’

③在查询命令窗口中输入执行以下UPDATE sc语句，查看执行结果。

UPDATE sc SET成绩=90

WHERE 课程号=’108’

从这部分实验中，我们可以看到，约束优先于触发器起作用，它在更新前就生效，以对要更新的值进行规则检查。当检查到与现有规则冲突时，系统给出错误消息，并取消更新操作。如果检查没有问题，更新被执行，当执行完毕后，再激活触发器。

4. 删除新创建的触发器

①在查询命令窗口中输入DROP TRIGGER 语句，删除新创建的触发器。

DROP TRIGGER tri_UPDATE_DELETE_sc

②点击快捷工具栏上的快捷按钮，删除触发器。 （二）练习

1.在Student表中编写insert的触发器，假如每个班的学生不能超过30个，如果低于

此数，添加可以完成；如果超过此数，则插入将不能实现。

2.在SC表上编写update触发器，当修改SC表中的grade字段时将其修改前后的信息保存在SC_log表中。

四、实验报告要求

1、写出与上述任务相对应的SQL语句

2、并记录在实验过程中遇到的问题、解决办法及心得体会。

4.2存储过程实验

一、实验目的

? 掌握用户存储过程的创建操作。 ? 掌握用户存储过程的执行操作。 ? 掌握用户存储过程的删除操作。

二、实验内容

1、创建带输入参数的存储过程的存储过程。 2、执行所创建的存储过程。 3、删除所有新创建的存储过程。

三、实验步骤

对应于student数据库

(1) 创建一个无参存储过程StuScoreInfo,查询以下信息:学号,姓名,性别,课程名称,考试成绩. (2) 创建一个带参数的存储过程stu_info,该存储过程根据传入的学生编号在student表中查询此学生的信息.

(3) 创建一个带参数的存储过程StuScoreInfo2,该存储过程根据传入的学生编号和课程名称查询以下信息:姓名,课程名称,考试成绩.

(4) 编写带参数的存储过程,根据传入的课程名称统计该课程的平均成绩.

(5) 编写存储过程,根据传入的课程名统计这门课的成绩分布情况,即按照各分数段统计人数.

四、实验报告要求

1、写出与上述任务相对应的SQL语句

2、并记录在实验过程中遇到的问题、解决办法及心得体会。

实验5 数据库备份与恢复

一、实验目的

? 熟悉数据库备份及恢复机制；

? 了解SQL Server的数据备份和恢复机制；

? 掌握SQL-Server中数据库备份和恢复的方法。

二、实验内容

以管理员帐号登录SQL Server Management Studio，以原有数据库student为基础，请使用Management Studio界面方式或T-SQL 语句实现以下操作： 1. 针对数据库stu创建完全数据库备份集stu.bak，目标磁盘为D：\\ user \\ stu.bak； 2. 在数据库stu中新建数据表ceshi，内容自定，然后针对数据库stu创建差异备份； 3. 向数据库stu的数据表ceshi插入部分记录，然后针对数据库stu创建事务日志备

份；

4. 根据需要，将数据库恢复到数据库stu的最初状态； 5. 根据需要，将数据库恢复到创建数据表ceshi后的状态； 6. 根据需要，将数据库恢复到在ceshi表插入记录后的状态； 7. 针对现有数据库stu创建完全文件和文件组备份集stu_file，目标磁盘为D：\\ user

\\ stu_file.bak；

8. 在当前数据库中新建数据表ceshi2，然后针对数据库stu创建差异文件和文件组

备份；

9. 向数据库stu的数据表ceshi2插入部分记录，然后针对数据库stu创建事务日志

文件和文件组备份；

10. 根据需要，将数据库以文件和文件组方式恢复到创建数据表ceshi2后的状态； 11. 根据需要，将数据库以文件和文件组方式恢复到数据表ceshi2插入记录后的状态；

三、思考

? 说明数据库的备份和恢复的基本步骤；

? SQL Server中的完全备份、事务日志备份和增量备份形式的功能特点；

四、实验报告要求

1、写出与上述任务相对应的SQL语句

2、并记录在实验过程中遇到的问题、解决办法及心得体会。

实验6 数据库设计综合应用

一、实验任务与要求

目的与任务：

1. 掌握数据库设计和实现的基本过程

2. 掌握数据库模式设计、分析和实现的方法 3. 了解数据库应用系统软件开发的一般过程。 实验基本要求：

（1）学习相关的预备知识

（2）按照数据库设计与实现过程完成数据库的设计，应用程序的开发，上机调试、运行 （3）写出课程设计报告

二、课程设计基础知识

数据库设计与实现的基本过程包括需求分析、概念结构设计、逻辑结构设计、数据库的物理设计以及数据库的实施和维护等内容。需求分析确定系统的数据需求和处理需求；概念结构设计通过对用户需求的综合、归纳和抽象，形成一个独立的概念模型；逻辑结构设计则将概念模型转换为具体的数据库管理系统所支持的数据模型；数据库的物理设计结合应用确定数据模型的存储结构和存取方式。在这些工作完成之后，就可以建立数据库并开始调试运行了，在数据库的运行过程中必须对其进行维护——包括数据库的备份与恢复、性能分析与改进等等。

1． 概念结构设计——ER图

概念模型是用户需求的抽象，通常用实体关系图（Entity Relationship Diagram，即ER图）来表示。设计ER图的出发点是数据流图层次的选择，一般选择中层的数据流图作为分析设计的依据。在得到各个局部ER图后，需要将它们合并成一张总的ER图。首先是简单的合并，协调各个局部ER之间不一致的地方；然后再对得到总ER图消除冗余，可通过分析数据字典中数据项之间的逻辑关系加以消除。

2． 逻辑结构设计——ER图向关系模型的转换

关系数据库的数据模型（即关系模型）由一组关系模式组成，因而ER图向关系模型的转换实际上就是将实体（包括实体的属性）和联系转换为关系模式，一般遵循以下基本原则： 1. 一个实体型转换为一个关系模式。实体的属性就是关系的属性，实体的码就是关系的码。

2. 一个1：X（X为1或n）联系通常与某一端的关系模式合并，其中1：n的联系需要与n端对应的关系模式合并。需要在合并的关系模式的属性中加入另一个关系模式的码和联系本身的属性。

3. 一个m：n联系转换为一个关系模式。与该关系相连的各实体的码以及联系本身的属性均转换为关系的属性，而关系的码为各实体码的组合。

4. 三个或三个以上实体间的一个多元联系可以转换为一个关系模式。与该多元联系相连的各实体的码以及联系本身的属性均转换为关系的属性，而关系的码为各实体的码的组合。

5. 具有相同码的关系模式可合并。 3． 数据库设计过程

óó|?Dè?ó¨ê￡y?Y?￠′|í￡à?×??a??ò?￠DBMS?|?ü?￠??óˉ·?·¨Dè?óê??ˉoí·???éè???????á11éè?????-?á11???-éè???×??êy?Y?￡Díó??ˉ??2úòaéè????àí?á11?à?íéè???×???à??éè??￡?D??ü2aê???àíêμ????2úòaê??éD???DDê1ó??￠???¤êy?Y?ay?êY?a??D?￠???¤×???y?êY?aêμê??×??è?Dó·????×????·?éè???×??óó|?òa?ó￡?DBMS?ê?·ì×??

设计描述数据需求分析数据字典、全系统中数据项、数据流、数据存储概念模型（E-R）图，数据字典设计阶段处理数据流图和判定表、数据字典中处理过程的描述系统说明书。包括：（1）新系统要求、方案和概图（2）翻印新鲜系统信息流的数据流图系统结构图（模块结构图）概念结构设计某种数据模型 如关系模型逻辑结构设计关系物理设计存储安排、存储方法选择、存储路径建立模块设计、IPO图实施阶段编写模块、装入数据、数据库试运行程序编码、编译联接、测试运行、维护性能检测、转储/恢复、数据库重组和重构新旧系统转换、运行、维护（修正性、适应性、改善性维护

三、课程设计题目及进度安排

建议6--8人一组建立开发团队。 开发团体组织架构和职责：

组长： 1人

负责本开发项目的组织与实施，协调团队各成员的开发进度，负责本开发项目的需求分析，负责课程设计报告文档的组织。

数据库分析员： 1人

负责数据库的概念结构设计 、逻辑结构设计、应用系统功能模块设计，编写相关文档。 用户界面设计员： 1人

负责本开发项目的用户交互界面设计，编写相关文档。 程序员： 3-5人

负责本开发项目的程序设计，编写相关文档。 测试员： 1人

负责本开发项目的程序测试，编写相关文档。 课程设计题目：

每个开发团队分别选择一个题目完成课程设计，可以从以下题目中选择，也可以自定题目。 1． 客户订购登记系统

一个公司希望为其客户订购行为建立一个数据库。一个消费者可以有一个或多个订单，每个订单可以有一种或多种商品。每个订单有一个，可以通过多种方式来支付，例如支票、信用卡或者现金。开始运行这个客户订购登记的员工的名字要被记下来。有一个相应的部门工作人员来负责整理订单病把这些订单发给顾客。如果订单上的货物在库中没有，就需写明库中有什么，这样在订单中可能就会用其他货物来填充。

2． 货存控制系统

一个公司希望为控制它的货存建立一个数据库，货存中的产品被分为几类，如服装、食品和文具。当产品需要从供应商那里重新订购时，工作人员需要提出一个购买订单。跟踪记录要提供买进、卖出的货物以及其他的费用。

3． 人力资源管理系统

人力资源部门希望建立一个数据库来管理它的员工。一个公司有几个部门，而一个员工属于一个部门。这个部门指派一个经理来全面负责部门事务和部门员工。为了有助于管理好部门工作，某些工作人员被任命来管理一组人员。当有一个新的员工进入公司时，需要他以前的工作经历和成绩。通常来说，每个员工都要经历一次面试，这通常是由经理来进行的。公司定义了一系列的职位类型，例如经理、业务分析员、销售人员和秘书，而且每个类型都有相关的等级，员工所处的位置决定了员工的工资。职位依据其工作量来分配给一个部门。例如，一个部门可能分配给两个业务分析员的职位。每个岗位会分配给一个员工，随着时间的过去，各个职位都会被分配给工作人员。

4． 工资管理系统

工资管理部门希望建立一个数据库来管理员工的工资。要计算员工的工资，就需要考虑不在休假日期以内的假期、工作期间的病假时间、奖金和扣除的部分。必须指明给每个员工发薪水的方式，随着时间的推移，方式可能会有些改变。大多数的员工是通过银行卡来结算工资的，但是也有一部分人使用现金或支票。如果是通过银行卡，就需要知道账号和卡的类型。付款方式只可能是一种方式。有几种原因可以扣除工资：例如，个人所得税、国家税、医疗保险、退休保险或者预付款。

5． 学生管理系统

一所大学希望建立一个数据库以便对学生进行管理。当一个学生进入学校时，他就会选择一个专业。每个学生也会指定一个指导老师。每个专业每年由一些课程组成。组成专业的课程的最小和最大数目分别是6和8。一般情况下给一个学生三次机会来通过这门课程的考试。有些特殊的课程可以在一个或多个专业中存在。大学可以有几个部门，每个部门都有一个部门负责人，每个课程都会分配一定数量的员工（称为课程合作人）来负责课程的教学。

四、提交课程设计报告，内容包括：

1． 课程设计说明（含设计题目、设计任务与要求、开发环境说明） 2． 功能需求分析 3． 数据库概念设计 4． 数据库逻辑设计 5． 应用系统功能模块

6． 源程序代码及运行结果（含运行界面屏幕截图）。（可选） 7． 总结

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