数据库实验指导书.docx
《数据库实验指导书.docx》由会员分享，可在线阅读，更多相关《数据库实验指导书.docx（37页珍藏版）》请在冰豆网上搜索。
数据库实验指导书
数据库原理
实验设计指导书
中南民族大学计算机科学学院
二○一○年七月
第一部分基础实验
实验一创建和修改数据库
[目的和意义]
学习创建和修改用户数据库的方法。
[实验内容]
分别使用SQLServer2000企业管理器和Transact-SQL语句，按下列要求创建和修改用户数据库。
1.创建一个数据库，要求如下:
（1）数据库名"testDB"。
（2）数据库中包含一个数据文件，逻辑文件名为testDB_data，磁盘文件名为testDB_data.mdf，文件初始容量为5MB，最大容量为5MB，文件容量递增值为1MB。
（3）事务日志文件，逻辑文件名为TestDB_log,磁盘文件名为TestDB_log.ldf,文件初始容量为5MB,最大容量为10MB，文件容量递增值为1MB。
2.对该数据库做如下修改:
（1）添加一个数据文件，逻辑文件名为TestDB2_data，实际文件为TestDB2_data.ndf,文件初始容量为1MB，最大容量为6MB，文件容量递增值为1MB。
（2）将日志文件的最大容量增加为15MB，递增值改为2MB。
[实现步骤]
方法一：
使用企业管理器创建和修改数据库TestDB
1.设置常规选项卡
（1）在企业管理器的控制面板目录中选中结点"数据库"，单击鼠标右键，在弹出菜单中选择"新建数据库"命令。
（2）设置新建数据库的常规选项卡，在"名称"文本框中键入数据库名称"TestDB"。
如图1-l所示。
2.设置数据文件选项卡
在“文件名”字段中键入数据文件名“TestDB_data”。
设置该文件初始大小为5MB。
图1-1
选中“文件属性”对话框中的复选框“文件自动增长”，并选中“按兆字节”设置文件容量递增值为1。
在“最大文件大小”对话框中选择“将文件增长限制为（MB）”为15。
如图1-2所示。
图1-2
3.设置事务日志选项卡
（1）在"文件名"字段中键入事务日志文件的逻辑文件名"TestDB_log"。
设置该文件初始大小为5MBo
（2）选中"文件属性"对话框中复选框"文件自动增长"，并选中"按兆字节"设置文件容量递增值为1。
（3）在"最大文件大小"对话框中选择"将文件增长限制为（MB）"为10如图1-3所示。
（4）单击"确定"按钮，完成数据库的创建。
图1-3设置事务日志选项卡
4.修改事务日志文件属性
（1）从树状目录窗口中找到刚刚创建的数据库TestDB，单击鼠标右键，从弹出菜单中选择命令"属性"，打开数据库TestDB的属性窗口。
（2）选择数据文件选项卡。
在该选项卡中添加数据文件TestDB2_data,方法是:
在"文件名"字段中键入数据文件逻辑文件名"TestDB2_data"，设置该文件初始大小为1MB,然后选中"文件属性"对话框中复选框"文件自动增长"，并选中"按兆字节"设置文件容量递增值为1，最后在"最大文件大小"对话框中选择"将文件增长限制为（MB）6。
如图1-4所示。
图1-4
（3）选择事务日志选项卡，在该选项卡中将事务日志文件的最大容量改为15，递增量改为2。
如图1-5所示。
图1-5
方法二：
使用Transact-SQL语句创建和修改数据库TestDB
1.创建数据库TestDB
CREATEDATABASETestDB
ON
（
NAME=TestDB_data,
FILENAME=”D:
\Programfiles\Microsoftsqlserver\mssql\data\TestDB_data.mdf”,
SIZE=5,
MAXSIZE=15,
FILEGROWTH=1,
）
LOGON
（
NAME=TestDB_log,
FILENAME=”D:
\Programfiles\Microsoftsqlserver\mssql\data\TestDB_log.ndf”,
SIZE=5,
MAXSIZE=10,
FILEGROWTH=1
）
2.修改数据库TestDB
ALTERDATABASETestDB
ADDFILE
（
NAME=TestDB2_data,
FILENAME=”D:
\Programfiles\Microsoftsqlserver\mssql\data\TestDB2_data.ndf”,
SIZE=1,
MAXSIZE=6,
FILEGROWTH=1
）
GO
ALTERDATABASETestDB
MODIFYFILE
（
NAME=TestDB_log,
MAXSIZE=15,
）
GO
ALTERDATABASETestDB
MODIFYFILE
（
NAME=TestDB_log,
FILEGROWTH=2,
）
GO
[习题]
建立学生选课的数据库stuDB,逻辑文件名为stuDB_data，磁盘文件名为stuDB_data.mdf，文件初始容量为10MB，最大容量为15MB，文件容量递增值为2MB。
事务日志文件，逻辑文件名为stuDB_log,磁盘文件名为stuDB_log.ldf,文件初始容量为5MB,最大容量为10MB，文件容量递增值为1MB。
[体会]
实验二创建和修改数据表
[目的和意义]
熟悉有关数据表的创建和修改等工作，并了解主键、外键以及约束的创建和应用，熟练掌握使用企业管理器和CREATETABLE、ALTERTABLE等Transact-SQL语句对数据表的操作方法。
[实验内容]
分别使用企业管理器和Transact-SQL语旬按下列要求创建两个数据表:
表B1项目数据表，表B2员工数据表，并按照步骤完成对表的相关修改和约束设置。
项目表（Project）
字段名
数据类型
字段长度
注释
项目编号
int
主键
名称
varchar
长度为50
负责人
int
客户
int
开始日期
datetime
结束日期
datetime
员工数据表（Employee）
字段名
数据类型
字段长度
注释
编号
int
主键
姓名
varchar
长度为50
性别
varchar
长度为50
所属部门
varchar
长度为50
工资
money
长度为8
[实现步骤]
方法一：
使用企业管理器创建数据表并添加约束
1.打开企业管理器，在树状目录窗口中找到数据库节点TestDB，并选中下一级节点"表"。
2.单击鼠标右键，从弹出菜单中选择命令"新建表"，打开表设计窗口，在窗口中按照"上机任务"中所列的"项目数据表"的字段及要求键入列名、数据类型、长度等属性，并将"项目编号"设置为主键。
如图2-l所示。
图2-l创建"项目数据表"
3.单击"保存"按钮，在弹出的对话框中输入表名称"项目数据表"，关闭表设计窗口，完成表"项目数据表"的刨建。
4.重复1-3步，创建另一个数据表"员工数据表"，表设计窗口如图2-2所示。
图2-2创建"员工数据表"
5.添加外键约束:
在"项目数据表"的"负责人"字段上添加外键约束，参照字
段为"员工数据表"中的字段"编号"，约束名为FK_pm。
实现方法为:
首先在企业管理器中选中"项目数据表"并单击鼠标右键，在弹出菜单中选择命令"设计表"打开设计表窗口。
然后在该窗口中单击按钮"表和索引属性"按钮，打开表的"属性"对话框。
最后选择"关系"选项卡，在改选项卡中做如下的操作。
单击"新建"按钮;
从"主键表"中选择"员工数据表"，并选择字段"编号";
从"外键表"中选择"项目数据表"，并选择字段"负责人";
在"关系名"中输入FK_pm。
单击"关闭"按钮，完成外键约束的设置。
6.添加检查约束:
将"员工数据表"中的字段"工资"的值限定在1000-10000,约束名CK_Salary。
操作方法为:
首先在企业管理器中选中"员工数据表"并单击鼠标右键，在弹出菜单中选择命令"设计表"打开设计表窗口。
然后在该窗口中单击按钮"表和索引属性"按钮，打开表的"属性"对话框。
最后选择"CHECK约束"选项卡，在改选项卡中做如下的操作:
单击标签中"新建"按钮;
在"约束表达式"文本框中输入一个条件表达式;
（工资>=1000AND工资<=10000）
在"约束名"对话框中输入所建核查约束名"CK_Salary"。
如图2-3所示。
图2-3
方法二:
使用Transact-SQL语句创建数据表并添加约束
1.创建项目数据表
USETestDB
GO
CREATETABLE项目数据表
（项目编号intPRIMARYKEY,
名称varchar（50）,
负责人int,
客户int,
开始日期datatime,
结束日期datatime
）
2.创建员工数据表
USETestDB
GO
（
编号intPRIMARYKEY,
姓名varchar（50）,
性别varchar（50）,
所属部门varchar（50）
工资money（8）
3.添加外键约束
ALTERTABLE项目数据表
ADDCONSTRAINTPK_pm
FOREIGNKEY（负责人）
REFERENCES员工数据表（编号）
添加检查约束
ALTERTABLE员工数据表
ADDCONSTRAINTCK_pm
CHECK（工资>=1000AND工资<=10000）
[习题]
建立学生的基本信息表student、课程表course和选修课成绩表sc.
[体会]
实验三添加记录并建立查询
[目的和意义]
熟练掌握查询语句。
[实验内容]
向数据库TestDB中的两个数据表"项目数据表"和"员工数据表"中添加记录，如表Cl,C2所示。
然后在查询分析器中书写Transact-SQL语句完成"实现步骤"中所列的任务。
表c1项目数据表
项目编号
名称
负责人
客户
开始日期
结束日期
1
SiS
2
1
03/12/2000
06/12/2000
2
SiS项目2
1
1
04/06/2000
05/01/2000
3
Pet
2
2
06/17/2000
08/17/2000
4
Pet项目2
2
2
09/01/2000
09/18/2000
5
CCH
3
3
03/12/2000
06/12/2000
6
CCH_LXF
4
3
04/06/2000
05/01/2000
7
CCH_ZHS
7
3
06/17/2000
08/17/2000
8
CCH_LY
9
3
09/01/2000
09/18/2000
9
CCH_LYAN
19
3
03/12/2000
06/12/2000
10
PETER
6
4
04/06/2000
05/01/2000
11
REALIDEA
8
5
06/17/2000
08/17/2000
12
REALIDEA1
7
5
09/01/2000
09/18/2000
13
REALIDEA2
19
5
03/12/2000
06/12/2000
14
REALIDEA3
9
5
04/06/2000
05/01/2000
15
PPA
4
6
06/17/2000
08/17/2000
16
NBA
4
7
09/01/2000
09/18/2000
表C2员工数据表
编号
姓名
性别
所属部门
工资
1
陈有朋
男
项目部
2000
2
孙晓晴
女
项目部
3000
3
张晓峰
男
录入部
1000
4
慕容雪
男
检验部
1500
5
陈秋萍
女
检验部
1000
6
王理斌
男
检验部
2000
7
周晴晴
女
办公室
3000
8
杨亭亭
女
项目部
2500
9
马明宇
男
项目部
4000
19
刘燕
女
项目部
3000
[实验要求]
1·书写语句查询项目数据表中客户字段的唯一值，并查看查询结果。
参考答案:
SELECTUNIQUE客户FROM项目数表；
（用“SELECT*FROM项目数表”；可以显示所有的信息）
2·书写语句查询工资高于2000的项目部的人员的姓名。
参考答案:
SELECT姓名
FROM员工数据表
WHERE所属部门=”项目部"AND工资>2000；
3·书写查询语句查询来自CCH公司的项目名称（以CCH开始）和负责人姓名。
参考答案:
SELECTa.名称,b.姓名
FROM项目数据表ASa,员工数据表ASb
WHEREa.负责人=b.编号
ANDa.名称LIKE“CCH%”;
4．书写语句查询每个部门的平均工资，结果按照平均工资的多少排序
SELECT所属部门,AVG（工资）
FROM员工数据表
GROUPBY所属部门
ORDERBYAVG（工资）；
5．使用外向联接输出所有的员工姓名和负责人的项目名称
SELECT员工数据表.姓名，项目数据表.名称
FROM员工数据表LEFTJOIN项目数据表
ON员工数据表.编号=项目数据表.负责人;
6．使用子查询输出所有负责CCH公司项目（以CCH开始）的员工姓名，以及没有
负责REALIDEA公司（以REALIDEA开始）项目的员工姓名。
SELECT姓名
FROM员工数据表
WHERE编号IN
（SELECTDISTINCT负责人
FROM项目数据表
WHERE名称LIKE'CCH%'）
SELECT姓名
FROM员工数据表
WHERE编号NOTIN
（SELECTDISTINCT负责人
FROM项目数据表
WHERE名称LIKE'REALIDEA%'）;
7·将所有REALIDEA公司的项目的结束日期更改为2002年1月8日。
UPDATE项目数据表
SET结束日期='January8,2002'
WHERE名称LIKE'REALIDEA%’;
8·录入部的张晓峰决定辞职，请将员工数据库中有关他的记录删除，并将他负责的项目移交给杨亭亭。
书写语句对数据表做相应的更改。
DELETE员工数据表
WHERE姓名=”张晓峰”
UPDATE项目数据表
SET负责人=8
WHERE负责人=3；
[习题]在student,course,sc中添加元组
1）从student,course,sc表中选择出当年年龄在20岁以上的学生的学号和姓名。
2）统计平均年龄价格。
3）“张三”同学要退学，请删除他的所有信息。
[体会]
实验四创建视图
[目的和意义]
掌握使用T-SQL语句创建视图的方法，包括视图的建立、删除、修改；了解如何应用视图有选择地查看所需数据，并熟悉通过视图更改数据表中数据的方法。
[实验内容]
在数据库Company_Data中，基于表"项目数据表"和"员工数据表"创建视图，要求为:
（1）视图名为"员工项目"。
（2）包含字段"编号"、"姓名"、"名称"和"开始日期"。
（3）字段别名分别是"员工编号"、”员工姓名"、"项目名称"、"项目开始日期"。
[实现步骤]
（1）打开查询分析器。
在查询窗口书写CREATEVIEW语句创建视图，并指定字段别名：
USETestDB
GO
CREATEVIEW员工项目（员工编号，员工姓名，项目名称，项目开始日期）
AS
SELECTa.编号,a.姓名,b.名称,b.开始日期,
FROM员工数据表ASaINNERJOIN项目数据表ASb
ONa.编号=b.负责人
WHEREa.编号=b.负责人
GO
（2）使用INSERT语句通过视图向员工数据表中添加一条记录，要求"姓名"字段值
为"马中兴"。
USETestDB
GO
INSERTINTO员工项目（员工姓名）
VALUES（'马中兴'）
GO
（3）使用UPPDATE语句通过视图将第二步中插入记录的员工姓名改为"马中新"。
USETestDB
GO
UPDATE员工项目
SET项目负责人='马中新’
WHERE项目负责人=’马中兴’
GO
（4）执行系统存储过程sp_rename将视图更名为"employee_project_VIEW"。
USETestDB
GO
EXECsp_rename'员工项目','emp1oyee_project_VIEW';
实验五创建索引
[目的和意义]
掌握创建索引的方法。
[实验内容]
分别使用企业管理器和Transact-SQL语句在实验四的数据表"员工数据表"中基于"姓名"创建索引，要求索引名为"IDX_Name"，索引类型为非聚集索引。
实现步骤
方法一，使用企业管理器
（1）打开企业管理器，在树状目录中展开数据表"员工数据表"所在的数据库节点TestDB，并选择其下一级节点"表"。
（2）选择表"员工数据表"，并单击鼠标右键。
在弹出菜单中选择命令"所有任务〉管理索引"，打开"管理索引"对话框。
（3）在"管理索引"对话框中的"数据库"下拉菜单中选择数据库表。
（4）选择"新建"按钮，弹出"新建索引"对话框。
（5）在"新建索引"对话框的"索引名"文本框中输入索引名称"IDX_Name"，然后选择字段"姓名"并设置索引属性。
如图D1所示。
图4-l创建索引
（6）设置索引选项以后，单击"确定"按钮，返回到"管理索引"对话框。
（7）单击"关闭"按钮，退出"管理索引"对话框，完成索引的创建。
方法二:
使用T-SQL语旬
USETestDB
GO
CREATEINDEXIDX_NameON员工数据表（姓名）
GO
实验六创建触发器
[目的和意义]
理解触发器的触发过程和类型，掌握创建触发器的方法。
[实验内容]
在数据库Company_Data的表"项目数据表"和"员工数据表"中分别创建触发器。
[实现步骤]
（1）打开查询分析器。
（2）在查询窗口书写CREATETRIGGER语句，基于表"员工数据表"创建AFTERINSERT触发器Tigger_NewEmployeeSalary，将插入员工的工资额限制在5000以内。
USETestDB.
GO
CREATETRIGGERTrigger_NewEmployeeSalary
ON员工数据表
AFTERINSERT
AS
IF（SELECT工资FROMinserted）>5000
BEGIN
PRINT'新员工工资不能超过5000'
ROLLBACK
END
GO
（3）在查询窗口书写CREATETRIGGER语句，基于表"员工数据表"创建AFTERUPDATE触发器Trigger_SalaryChange，将员工工资变动额限制在2000以内。
CREATETRIGGERTrigger_SalaryChange
ON员工数据表
AFTERUPDATE
AS
IFUPDATE（工资）
BEGIN
IF（SELECTMAX（ABS（inserted.工资-de1eted.工资））
FROMinsertedJOINdeleted
ONinserted.编号=deleted.编号）>2000
BEGIN
PRINT‘工资变动不能超过2000'
ROLLBACKTRANSACTION
END
END
实验七创建存储过程
[目的和意义]
了解存储过程的概念和作用，掌握创建存储过程的方法。
[实验内容]
在数据库Company_Data中创建存储过程Procedure_SalaryByDept，要求返回某一特定部门所有员工的工资总和，其中特定部门的名称以存储过程的输入参数进行传递。
[实现步骤]
（1）打开查询分析器。
（2）在查询窗口书写CREATEPROCEDURETransact-SQL语句创建存储过程
Procedure_SalaryByDept，并带有一个输入参数@Department用于传递部门名称，一个输出参数@TotalSalary用于传递输出结果。
CREATEPROCEDUREProcedure_SalaryByDept@Departmentvarchar（40）,
@TotalSalarymoneyOUTPUT
AS
（3）选择所属部门为该参数传递的部门名称的所有记录，并对其工资字段求和。
SELECT@Tota1Sa1ary=sum（工资）
FROM员工数据表
WHERE所属部门=@Department
GO
（4）报告该部门的工资总额。
PRINT'该部门的工资总额’+CONVERT（varchar,@TotalSalary）
GO
注释：
（在这里PRINT语句，不能输出，不知何因。
但是，如果去掉上面语句
“PRINT'该部门的工资总额’+CONVERT（varchar,@TotalSalary）”中的“+CONVERT（varchar,@TotalSalary”则可以输出“该部门的工资总额”却不能算出具体的工资总额）
（5）执行这个创建存储过程的语句。
（6）执行系统存储过程sp_help查看该存储过程的一般信息。
（7）执行该存储过程，分别计算项目部、录入部、和检验部的工资总额。
（8）用ALTTERPROCEDURE语句将存储过程加密。
实验八备份和恢复数据库
[目的和意义]
理解数据库备份的过程和属性设置，掌握使用企业管理器备份数据库的方法。
[实验内容]
使用SQLServer2000企业管理器备份和恢复数据库TestDB.
[实现步骤]
（1）打开企业管理器，在控制面板目录中选择数据库TestDB。
（2）单击鼠标右键，从弹出菜单中选择命令"所有任务〉备份数据库"。
（3）在出现的"SQLServer备份"对话框中选择"常规"选项卡。
（4）在该选项卡中的"名称"文本框中键入"TestDB数据库备份"。
（5）在"描述"文本框中键入"备份集1"。
（6）在"备份"对话框中选中"数据库-完全"选项，如图E1所示。
（7）单击"添加"按钮，在弹出的对话框中选择"备份设备"，然后从下拉列表中选择备份使用的备份设备（注意：
如果没有现成的备份设备，应在备份以前创建一个具体方法参见相关章节）。
（8）单击"重写现有媒体"选项按钮。
（9）单击""选项"选项卡，在该选项卡中选中"完成后验证备份"复选框。
（10）单击"确定"按钮，出现"备份进度"对话框，备份完成后，将
升级会员 