《数据库系统SSD7》试验参考指导书.doc

《数据库系统SSD7》
课程实验指引书

谭长庚 编写

课程编号 32X1
总 学 时 64
实验学时 20
课外学时 20

中南大学软件学院
11月

实验1《数据库与表基本操作》
实验学时： 2
每组人数： 1
实验类型： 1 （1：基本性 2：综合性 3：设计性 4：研究性）
实验规定： 1 （1：必修 2：选修 3：其他）
实验类别： 3 （1：基本 2：专业基本 3：专业 4：其他）

一、实验目
1．纯熟掌握SQL Server用法。
2. 纯熟掌握使用SQL Server 200X对象资源管理器以及SQL DDL完毕数据库创立、删除和连接办法；数据表建立、删除；表构造修改。
3. 加深对表实体完整性、参照完整性和顾客自定义完整性理解。
二、实验内容
在如下实验中，使用学生-课程数据库，它描述了学生基本信息、课程基本信息及学生选修课程信息。用对象资源管理器（公司管理器）与SQL语句分别完毕。
（1）  创立学生-课程数据库，数据文献名为student_data、大小10M，日记文献名为student_log、大小5M新数据库，该数据库名为student_xxxx。xxxx表达班级学号。1班12号，数据库名为student_0112。
（2）  创立学生关系表S ：
学号
姓名
性别
年龄
所在系
Sno
Sname
Ssex
Sage
sdept
（3）  创立课程关系表C ：
课程号
课程名
先行课
学分
Cno
Cname
Cpno
ccredit
（4）  创立学生-课程表SC ：
学号
课程号
成绩
Sno
Cno
grade
（5）  将以上创立表S、C、SCSQL命令以 .SQL文献形式保存在磁盘上。在表中加入至少4个元组，第一种为本人信息。
（6）  在表S上增长“出生日期”属性列。
（7）  删除表S“年龄” 属性列。
（10） 删除表SC，运用磁盘上保存.SQL文献重新创立表SC。
（11）备份数据库，再还原。
三、实验规定：
1．预习数据库与表建立办法；
2．预习备份数据库，还原数据库办法；
3. 预习表构造修改办法。
四、实验环节
１．熟悉关于SQL SERVER图形界面工作环境（参见后附录2 SQL Server 使用）。
２．创立数据库或连接已建立数据库。
３．在当前数据库上建立新表。
４．定义表构造。
（1）用ＳＱＬ命令形式
（2）用SQL SERVER提供对象资源管理器（公司管理器）以交互方式进行
5． 将以上表定义以　.ＳＱＬ文献形式保存在磁盘上。
6． 修改表构造。
7． 删除表定义。
五、实验报告
1．完毕本项目实验后，学生应提交实验报告。
2．实验报告格式与规定见附录1。

附录1：实验报告格式与规定

《XXXXX（课程名称）》

实验报告

项目名称
专业班级
学 号
姓 名

实验成绩：

批阅教师：

年 月 日

正文规定
实验1《XXXXX（项目名称）》
实验学时： 实验地点： 实验日期：

一、实验目
指出本次实验应当达到学习目的。
二、实验内容
指出本次实验应完毕任务。
三、实验办法
涉及实验办法、原理、技术、方案等。
四、实验环节
指出完毕该实验操作环节。
五、实验成果
记录实验输出数据和成果。
六、实验结论
对实验数据和成果进行分析描述，给出实验获得成果和结论。
注：有程序规定附上程序源代码，有图表要有截图并有相应文字阐明和分析
七、实验小结
给出本次实验体会，如学会了什么，遇到哪些问题，如何解决这些问题，存在哪些有待改进地方。

附录2 SQL Server 使用

0.启动SQL server 服务：在程序菜单上选Sql server 配备管理

点右键启动

1.在程序菜单中启动SQL sever express
点击启动

2.点击“连接对象资源管理器”按钮，连接服务器（如设立开机就连接无需做此步）
连接对象资源管理器按钮

注意：服务器也许是soft-c23\sqlexpress(下拉菜单去选),c23为机位号；
或者用.\sql

3.新建数据库，修改有关参数
鼠标指向“数据库”，点右键
选“新建数据库”

输入数据库名
修改数据库文献保存途径（在e盘先建好文献夹）

4.新建表，选定新建数据库，指向“表”，点右键，“新建表”，输入各列名及类型，在右属性框修改表名。

5.点“新建查询”，在编辑框中输入sql语句，完毕建表、查询语句编辑；点“执行按钮”执行，观测成果。点“存盘”按钮可以对sql脚本存盘。

也可通过配备管理器来启动数据库服务器。

T-SQL建立数据库：
CREATE DATABASE csu_tcg
ON PRIMARY
( NAME = 'csu-tcg'，
FILENAME ='e:\MSSQL\DATA\csu-tcg.mdf' ，--文献夹要先建好
SIZE = 3072KB ，--单位可以M，K
FILEGROWTH = 1024KB )
LOG ON
( NAME = N'csu-tcg_log'，
FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\csu-tcg_log.ldf' ,
SIZE = 1MB ，
FILEGROWTH = 10%)
GO /*go为事务提交一段SQL脚本（是隐形事务）结束标志符。SQL   Server   应用程序可将多条   Transact-SQL   语句作为一种批解决发给   SQL   Server去执行。在此批解决中语句编译成一种执行筹划。程序员在SQL   Server 实用工具中执行特定语句，或生成Transact-SQL 语句脚本在   SQL   Server   实用工具中运营，用   GO   来标记批解决结束*/

实验2《数据表查询与更新》
实验学时： 4
每组人数： 1
实验类型： 2 （1：基本性 2：综合性 3：设计性 4：研究性）
实验规定： 1 （1：必修 2：选修 3：其他）
实验类别： 3 （1：基本 2：专业基本 3：专业 4：其他）

一、实验目
1. 熟悉和掌握对数据表中数据查询操作和SQL命令使用，学会灵活纯熟使用SQL 语句各种形式，加深理解关系运算各种操作（特别是关系选取，投影，连接和除运算）；
2. 熟悉和掌握数据表中数据插入、修改、删除操作和命令使用（熟悉使用UPDATE/INSERT/DELETE语句进行表操作）；加深理解表定义对数据更新作用。

二、实验内容
（一）在表S，C，SC上完毕如下查询：
1． 查询学生基本信息；
2． 查询“CS”系学生基本信息；
3． 查询“CS”系学生年龄不在19到21之间学生学号、姓名；
4． 找出“CS”系年龄最大学生，显示其学号、姓名；
5． 找出各系年龄最大学生，显示其学号、姓名；
6． 记录“CS”系学生人数；
7． 记录各系学生人数，成果按升序排列；
8． 按系记录各系学生平均年龄，成果按降序排列；
9． 查询无先修课课程课程名和学时数；
10．记录每位学生选修课程门数、学分及其平均成绩；
11．记录选修每门课程学生人数及各门课程平均成绩；
12．找出平均成绩在85分以上学生，成果按系分组，并按平均成绩升序排列；
13．查询选修了“1”或“2”号课程学生学号和姓名；
14．查询选修了课程名为“数据库系统”且成绩在60分如下学生学号、姓名和成绩；
15．查询每位学生选修了课程学生信息（显示：学号，姓名，课程号，课程名，成绩）；
16．查询没有选修课程学生基本信息；
17．查询选修了3门以上课程学生学号；
18．查询选修课程成绩至少有一门在80分以上学生学号；
19．查询选修课程成绩均在80分以上学生学号；

（二）在表S、C、SC中完毕下列更新：
1．  将数据分别插入表S、C、SC；
2．  将表S、C、SC中数据保存在磁盘上。
3．  在表S、C、SC上练习数据插入、修改、删除操作。（比较在表上定义/未定义主码（Primary Key）或外码（Foreign Key）时状况）
4．  将表S、C、SC中数据所有删除，再运用磁盘上备份数据来恢复数据。
5．  如果要在表SC中插入某个学生选课信息（如：学号为“001005”，课程号为“c123”，成绩待定），应如何进行？
6．  求各系学生平均成绩，并把成果存入数据库；
7．  将“CS”系全体学生成绩置零；
8．  删除“CS”系全体学生选课记录；
9．  删除学号为“S1”有关信息；
10．将学号为“S1”学生学号修改为“S001”；
11．把平均成绩不不大于80分男同窗学号和平均成绩存入另一种表S——GRADE（SNO，AVG_GRADE）；
12． 把选修了课程名为“数据构造”学生成绩提高10%；
13． 把选修了“C2”号课程，且成绩低于该门课程平均成绩学生成绩删除掉。
三、实验规定：
1． 熟悉SQL SERVER 工作环境；
2．连接到学生-课程数据库
3．复习对表中数据查询SQL语言命令；复习对表中数据插入、修改和删除SQL语言命令，理解这些更新语句基本语法和用法。

四、实验环节
查询：
1．在表Ｓ、Ｃ、ＳＣ上进行简朴查询、连接查询、嵌套查询；
2．使用聚合函数查询、对数据分组查询、对数据排序查询。
插入：
1．将数据插入当前数据库表Ｓ、Ｃ、ＳＣ中；
（1）用ＳＱＬ命令形式；
（2）使用SQL Server Management Studio（简称SSMS）（或公司管理器）以交互方式进行；
2．    将以上插入数据分别以.SQL文献和.txt文献形式保存在磁盘上；
3．    修改表Ｓ、Ｃ、ＳＣ中数据；
（1）用ＳＱＬ命令形式
（2）用SSMS以交互方式进行
4．    删除表Ｓ、Ｃ、ＳＣ中数据。
（1）用ＳＱＬ命令形式
（2）用SSMS以交互方式进行

五、实验报告
1．完毕本项目实验后，学生应提交实验报告。
2．实验报告格式与规定见附件。

附录 实例
1． 对于student表，将所有专业号为‘001’，并且入年份为学生，或是专业号为‘003’，并且年龄不大于20岁学生班级号改为‘001’。
环节：新建查询-输入代码:
use edudb—-假设数据库为edudb
go
UPDATE student—student是 edudb中学生信息表
SET classno='001'
WHERE spno='001' AND entime='' or spno='003' and (-birthday)<20
2． 对于student表，删掉所有年龄不大于20岁，并且专业号为‘003’学生记录。
环节：新建查询-输入代码:
use edudb
go
DELETE FROM student
WHERE (-birthday)<20 and spno='003'

3． 对于student表，插入一条新记录，它详细信息为，学号：00302、姓名：李伟龙、性别：男、出生日期：19880808、院系编号：‘001’、专业编号：
‘01’、班级号：‘003’、入学时间：0901。
环节：新建查询-输入代码:
INSERT INTO student (sno,sname,sex,birthday,dno,spno,classno,entime)
values ('00302','李伟龙','男','880808','001','001','003','0901')
（注意：要将学号类型改为char（10），否则长度不够无法执行）

实验3《视图与完整性约束》
实验学时： 4
每组人数： 1
实验类型： 2 （1：基本性 2：综合性 3：设计性 4：研究性）
实验规定： 1 （1：必修 2：选修 3：其他）
实验类别： 3 （1：基本 2：专业基本 3：专业 4：其他）

一、实验目
学习灵活纯熟进行视图操作，结识视图作用；掌握完整性控制办法。
1.熟悉和掌握对数据表中视图定义操作和SQL命令使用；
2.熟悉和掌握对数据表中视图查询操作和SQL命令使用；
3.熟悉和掌握对数据表中视图更新操作和SQL命令使用，并注意视图更新与基本表更新区别与联系；
4．熟悉SQL语言对数据库进行完整性控制办法。

二、实验内容
（一）以S ，C ，SC表为基本完毕如下视图定义及使用
1．定义“SSCH”院学生基本状况视图V_SSCH；
2．将S，C，SC表中学生学号，姓名，课程号，课程名，成绩定义为视图V_S_C_G;
3．将各院学生人数，平均年龄定义为视图V_NUM_AVG;
4． 将各位学生选修课程门数及平均成绩定义为视图V_AVG_S_G并查询成果;
5．  查询平均成绩为90分以上学生学号、姓名和成绩；
6． 通过视图V_SSCH，新增长一种学生记录 ('S12','YAN XI',19，'SSCH')，并查询成果；
7．通过视图V_SSCH，删除学号为“S12”学生信息，并查询成果；
8．将视图V_SSCH中学号为“S12”学生改名“中南人”。
（二）使用SQL进行数据完整性控制：涉及三类完整性、check短语、constrain子句。
1. 在创立下列关系表时完毕如下约束：定义实体完整性;参照完整性（外码、在删除S中元组时级联删除SC中相应元组、当更新S中Sno时同步更新SC中Sno）;顾客定义完整性：学生年龄<30。
2. 修改S中约束条件，学号在100－1000之间。
学生关系表S ：
学号
姓名
性别
年龄
所在系
Sno
Sname
Ssex
Sage
sdept
  创立课程关系表C ：
课程号
课程名
先行课
学分
Cno
Cname
Cpno
ccredit
创立学生-课程表SC ：
学号
课程号
成绩
Sno
Cno
grade
3．用实验验证当操作违背了完整性约束时，系统如何解决？
问题：外键与参照主键与否一定要相似？

三、实验规定：
1．熟悉SQL SERVER 工作环境；
2．连接到学生-课程数据库
3．复习关于视图操作SQL语言命令；复习关于完整性约束操作SQL语言命令

四、实验环节
1．定义视图、对视图进行查询、修改；
2．进行完整性约束定义、修改；
3. 进行约束违例验证。

五、实验报告
1．完毕本项目实验后，学生应提交实验报告。
2．实验报告格式与规定见附件。

实验4《存储过程》
实验学时： 4
每组人数： 1
实验类型： 2 （1：基本性 2：综合性 3：设计性 4：研究性）
实验规定： 1 （1：必修 2：选修 3：其他）
实验类别： 3 （1：基本 2：专业基本 3：专业 4：其他）

一、实验目
理解存储过程概念、建立和调用办法。进一步熟悉SQL语句对数据库进行完整性控制办法。
二、实验内容
1、建立一种新销售数据库，包括五张表，每张表至少需要10条记录。
（1）/*员工人事表employee */
emp_no
char(5)
Not null
primary key
员工编号
emp_name
char(10)

员工姓名
sex
char(1)

性别
dept
char(4)

所属部门
title
char(6)

职称
date_hired
datetime

到职日
birthday
datetime

生日
salary
int

薪水
addr
char(50)
null

住址

（2）/*客户表customer */
cust_id
char(5)
Not null
primary key
客户号
cust_name
char(20)

客户名称
addr
char(40)

客户住址
tel_no
char(10)

客户电话
zip
char(6)

邮政编码

（3）/*销售主表sales */
order_no
int
Not null
primary key
订单编号
cust_id
char(5)

客户号
sale_id
char(5)

业务员编号
tot_amt
numeric(9,2)

订单金额
order_date
datetime

订货日期
ship_date
datetime

出货日期
invoice_no
char(10)

发票号码

（4）/*销货明细表sale_item */
order_no
int
Not null,
primary key
订单编号
prod_id
char(5)
Not null,
primary key
产品编号
qty
int

销售数量
unit_price
numeric(7,2)

单价
order_date
datetime
null

订单日期

（5）/*产品名称表product */
pro_id
char(5)
Not null
primary key
产品编号
prod_name
char(20)
Not null

产品名称
2、建立表同步创立表约束。
（1） 为每张表建立主键约束。
（2） 通过拖放操作加入外键。
（3） 在表employee加入CHECK约束：输入员工编号必要以E开头5位数编号，性别只能为M/F。
（4） 为销售主表sales中发票编号字段建立UNIQUE约束。
3、运用存储过程，给employee表添加一条业务部门员工信息。
4、运用存储过程从employee、sales、customer表连接中返回所有业务员姓名、客户姓名、销售金额。
5、运用存储过程查找“刘德华”员工编号、订单编号、销售金额。
6、运用存储过程查找姓“李”并且职称为“职工”员工员工编号、订单编号、销售金额。
7、运用存储过程计算出订单编号为10003订单销售金额。

三、实验规定：
1．熟悉SQL SERVER 工作环境；
2．建立销售数据库
3．复习关于约束与存储过程SQL语言命令。
4．备份数据库，作为实验5 操作数据库。

四、实验环节
1．创立销售数据库，并建表、修改，规定将自己信息包括其中；
2、运用存储过程，给employee表添加一条业务部门员工信息。
3、运用存储过程从employee、sales、customer表连接中返回所有业务员姓名、客户姓名、销售金额。
4、运用存储过程查找“刘德华”员工编号、订单编号、销售金额。
5、运用存储过程查找姓“李”并且职称为“职工”员工员工编号、订单编号、销售金。
五、实验报告
1．完毕本项目实验后，学生应提交实验报告。
2．实验报告格式与规定见附件。
附录：实验示例
1、模糊查询
create procedure sp_empname @E_name varchar(10) as
select a.emp_name,a.dept,b.tot_amt
from employee a inner join sales b
on a.emp_no=b.sale_id
where a.emp_name like @E_name
go

exec sp_empname '陈%'
2、 运用存储过程计算出’E0014’业务员销售总金额。
create procedure sp_saletot @E_no char(5),@p_tot int output as
select @p_tot=sum(tot_amt)
from sales
where sale_id=@E_no
go

declare @tot_amt int
exec sp_saletot E0014，@tot_amt output
select @tot_amt

实验5《触发器与游标》
实验学时： 4
每组人数： 1
实验类型： 2 （1：基本性 2：综合性 3：设计性 4：研究性）
实验规定： 1 （1：必修 2：选修 3：其他）
实验类别： 3 （1：基本 2：专业基本 3：专业 4：其他）

一、实验目
进一步熟悉SQL语句对数据库进行完整性控制办法；理解触发器概念、定义办法和触发条件。理解游标定义、打开、使用、关闭与释放办法。

二、实验内容
针对实验4所建销售数据库：
1、 设立一种触发器，该触发器仅容许“dbo”顾客可以删除employee表内数据，否则出错。
2、 针对employee表写一种DELETE触发器。
3、 针对employee表写一种UPDATE触发器。
4、 记录employee表中员工平均薪水，输出低于平均薪水员工人数以及这些员工姓名与薪水（使用游标）。

三、实验规定：
1．熟悉SQL SERVER 工作环境；
2．恢复实验4所建销售数据库
3．复习关于SQL语句对数据库进行完整性控制办法；复习触发器概念、定义办法和触发条件。复习游标定义、打开、使用、关闭与释放办法约束与存储过程SQL语言命令。
四、实验环节
1.设立一种触发器，该触发器仅容许“dbo”顾客可以删除employee表内数据，否则出错。
2.针对employee表写一种DELETE触发器。
3.针对employee表写一种UPDATE触发器。
4.记录employee表中员工平均薪水，输出低于平均薪水员工人数以及这些员工姓名与薪水（使用游标）。
五、实验报告
1．完毕本项目实验后，学生应提交实验报告。
2．实验报告格式与规定见附件。

附录：实验示例
1、写一种容许顾客一次只删除一条记录触发器。

有员工人事表employee
emp_no
char(5)
Not null
primary key
员工编号
emp_name
char(10)
Not null

员工姓名
sex
char(1)
Not null

性别
dept
char(4)
Not null

所属部门
title
char(6)
Not null

职称
date_hired
datetime
Not null

到职日
birthday
datetime
Null

生日
salary
int
Not null

薪水
addr
char(50)
null

住址
销货明细表sale_item
order_no
int
Not null,
primary key
订单编号
prod_id
char(5)
Not null,
primary key
产品编号
qty
int
Not null

销售数量
unit_price
numeric(7,2)
Not null

单价
order_date
datetime
null

订单日期

create trigger tr_emp
on employee for delete as
declare @row_cnt int
select @row_cnt=count(*) from deleted
if @row_cnt>1
begin
print '此删除操作也许会删除多条人事表数据!!!'
rollback transaction
end

delete from employee
where sex='女' /*成果：所影响行数为0*/

2、显示女职工工号，姓名

(1)声明一种游标
语法：DECLARE <游标名> CURSOR FOR <SELECT语句>；
(2)打开该游标；
语法：open <游标名>
(3)将该游标内容提取到变量中；
Fetch <游标名> Into <主变量>
declare cur_emp_gender scroll cursor for—定义游标
select emp_no ，emp_name
from employee
where sex='F'
order by emp_no

open cur_emp_gender—打开游标

declare @no,@name

fetch next from cur_emp_gender into @no,@name—读数据到局部变量

while(@@FETCH_STATUS=0)
begin
select @no,@name
fetch next from cur_emp_gender into @no,@namer
end

close cur_emp_gender—关闭
deallocate cur_emp_gender—释放

附件：实验报告格式与规定

《XXXXX（课程名称）》

实验报告

项目名称
专业班级
学 号
姓 名

实验成绩：

批阅教师：

年 月 日

正文规定
实验1《XXXXX（项目名称）》
实验学时： 实验地点： 实验日期：

一、实验目
指出本次实验应当达到学习目的。
二、实验内容
指出本次实验应完毕任务。
三、实验办法
涉及实验办法、原理、技术、方案等。
四、实验环节
指出完毕该实验操作环节。
五、实验成果
记录实验输出数据和成果。
六、实验结论
对实验数据和成果进行分析描述，给出实验获得成果和结论。
注：有程序规定附上程序源代码，有图表要有截图并有相应文字阐明和分析
七、实验小结
给出本次实验体会，如学会了什么，遇到哪些问题，如何解决这些问题，存在哪些有待改进地方。