《数据库系统SSD7》课程实验指导书
谭长庚编写
课程编号X1
总学时64
实验学时20
课外学时20
中南大学软件学院
2012年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 :
(3)创建课程关系表C :
(4)创建学生-课程表SC :
(5)将以上创建表S、C、SC的SQL命令以 .SQL文件的形式保存在磁盘上。在表中加入至少4个元组,第一个为本人信息。
(6)在表S上增加“出生日期”属性列。
(7)删除表S的“年龄”属性列。
1
(10)删除表SC,利用磁盘上保存的.SQL文件重新创建表SC。
(11)备份数据库,再还原。
三、实验要求:
1.预习数据库与表的建立方法;
2.预习备份数据库,还原数据库的方法;
3. 预习表结构的修改方法。
四、实验步骤
1.熟悉有关SQL SERVER的图形界面工作环境(参见后附录2 SQL Server 2005的使用)。
2.创建数据库或连接已建立的数据库。
3.在当前数据库上建立新表。
4.定义表的结构。
(1)用SQL命令形式
(2)用SQL SERVER提供的对象资源管理器(企业管理器)以交互方式进行
5.将以上表的定义以.SQL文件的形式保存在磁盘上。
6.修改表的结构。
7.删除表的定义。
五、实验报告
1.完成本项目实验后,学生应提交实验报告。
2.实验报告格式与要求见附录1。
附录1:实验报告格式与要求
2
3
4
正文要求
实验1《XXXXX (项目名称)》
实验学时: 实验地点: 实验日期:
一、实验目的
指出此次实验应该达到的学习目标。 二、实验内容
指出此次实验应完成的任务。 三、实验方法
包括实验方法、原理、技术、方案等。 四、实验步骤
指出完成该实验的操作步骤。 五、实验结果
记录实验输出数据和结果。 六、实验结论
对实验数据和结果进行分析描述,给出实验取得的成果和结论。
注:有程序的要求附上程序源代码,有图表的要有截图并有相应的文字说明和分析 七、实验小结
给出本次实验的体会,如学会了什么,遇到哪些问题,如何解决这些问题,存在哪些有待改进的地方。
附录2 SQL Server 2005的使用
0.启动SQL server 服务:在程序菜单上选Sql server 2005 的配置管理
点右键启动5
6
1.在程序菜单中启动SQL sever 2005 express
2.点击“连接对象资源管理器”按钮,连接服务器(如设置开机就连接的无需做此步)
点击启动
注意:服务器可能是soft-c23\sqlexpress(下拉菜单去选),c23为机位号; 或者用.\sql2005
3.新建数据库,修改相关参数
鼠标指向“数据库”,点右键
选“新建数据库”
输入数据库名
修改数据库文件保存路径
(在e盘先建好文件夹)
7
8
4.新建表,选定新建的数据库,指向“表”,点右键,“新建表”,输入各列名及类型,在右属性框修改表名。
9
5.点“新建查询”,在编辑框中输入sql 语句,完成建表、查询语句的编辑;点“执行按钮”执行,观察结果。点“存盘”按钮可以对sql 脚本存盘。
也可通过配置管理器来启动数据库服务器。
1
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分以下的学生的学号、姓名和成绩;
11
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中插入某个学生的选课信息(如:学号为“”,课程号为“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语言命令,了解这些更新语句的基本语法和用法。
四、实验步骤
查询:
12
1.在表S、C、SC上进行简单查询、连接查询、嵌套查询;
2.使用聚合函数的查询、对数据分组查询、对数据的排序查询。
插入:
1.将数据插入当前数据库的表S、C、SC中;
(1)用SQL命令形式;
(2)使用SQL Server Management Studio(简称SSMS)(或企业管理器)以交
互方式进行;
2.将以上插入的数据分别以.SQL文件和.txt文件的形式保存在磁盘上;
3.修改表S、C、SC中的数据;
(1)用SQL命令形式
(2)用SSMS以交互方式进行
4.删除表S、C、SC中的数据。
(1)用SQL命令形式
(2)用SSMS以交互方式进行
五、实验报告
1.完成本项目实验后,学生应提交实验报告。
2.实验报告格式与要求见附件。
附录实例
1.对于student表,将所有专业号为‘001’的,并且入学年份为2006的学生,或是专业号为‘003’,并且年龄小于20岁的学生的班级号改为‘001’。
步骤:新建查询-输入代码:
use edudb—-假设数据库为edudb
go
UPDATE student—student是 edudb中的学生信息表
SET classno='001'
WHERE spno='001'AND entime='2006'or spno='003'and
(2008-birthday)<20
2.对于student表,删掉所有年龄小于20岁,并且专业号为‘003’的学生的记录。
步骤:新建查询-输入代码:
use edudb
go
DELETE FROM student
WHERE(2008-birthday)<20 and spno='003'
3.对于student表,插入一条新记录,它的具体信息为,学号:、姓名:李伟龙、性别:男、出生日期:、院系编号:‘001’、专业编号:
‘01’、班级号:‘003’、入学时间:。
步骤:新建查询-输入代码:
13
INSERT INTO student
(sno,sname,sex,birthday,dno,spno,classno,entime)
values('','李伟龙','男','','001','001','003','')
(注意:要将学号的类型改为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命令的使用,并注意视图更新与基本表更新的区别与联系;
14
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之间。
创建课程关系表C :
创建学生-课程表SC :
3.用实验验证当操作违反了完整性约束时,系统如何处理?
问题:外键与参照主键是否一定要相同?
三、实验要求:
1.熟悉SQL SERVER 工作环境;
2.连接到学生-课程数据库
3.复习有关视图操作的SQL语言命令;复习有关完整性约束操作的SQL语言命令
15
四、实验步骤
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 */
(2)/*客户表customer */
16
(3)/*销售主表sales */
(4)/*销货明细表sale_item */
(5)/*产品名称表product */
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 的操作数据库。
四、实验步骤
17
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:其它)
18
一、实验目的
进一步熟悉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
19