(3X)《数据库系统SSD7》实验指导书

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

谭长庚编写

课程编号 320182X1

总学时 64

实验学时 20

课外学时 20

中南大学软件学院

2018年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的“年龄”属性列。

<10）删除表SC，利用磁盘上保存的.SQL文件重新创建表SC。

<11）备份数据库，再还原。

三、实验要求：

1．预习数据库与表的建立方法；

2．预习备份数据库，还原数据库的方法；

3. 预习表结构的修改方法。

四、实验步骤

１．熟悉有关SQL SERVER的图形界面工作环境<参见后附录2 SQL Server 2005的使用）。

２．创建数据库或连接已建立的数据库。

３．在当前数据库上建立新表。

４．定义表的结构。

<1）用ＳＱＬ命令形式

<2）用SQL SERVER提供的对象资源管理器<企业管理器）以交互方式进行

5．将以上表的定义以.ＳＱＬ文件的形式保存在磁盘上。

6．修改表的结构。

7．删除表的定义。

五、实验报告

1．完成本工程实验后，学生应提交实验报告。

2．实验报告格式与要求见附录1。

附录1：实验报告格式与要求

正文要求

实验1《XXXXX<工程名称）》

实验学时：实验地点：实验日期：

一、实验目的

指出此次实验应该达到的学习目标。

二、实验内容

指出此次实验应完成的任务。

三、实验方法

包括实验方法、原理、技术、方案等。

四、实验步骤

指出完成该实验的操作步骤。

五、实验结果

记录实验输出数据和结果。

六、实验结论

对实验数据和结果进行分析描述，给出实验取得的成果和结论。

注：有程序的要求附上程序源代码，有图表的要有截图并有相应的文字说明和分析七、实验小结

给出本次实验的体会，如学会了什么，遇到哪些问题，如何解决这些问题，存在哪些有待改进的地方。

附录2 SQL Server 2005的使用

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

点右键启动

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

点击启动

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

3.新建数据库，修改相关参数

注意：服务器可能是soft-c23\sqlexpress(下拉菜单去选>,c23为机位号； 或者用.\sql2005

鼠标指向“数据库”，点右键

输入数据库名

修改数据库文件保存路径<

在e盘先建好文件夹）

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

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

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

T-SQL建立数据库：CREATEDATABASE csu_tcg

ONPRIMARY

(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上练习数据的插入、修改、删除操作。<比较在表上定义/未定义主码

4．将表S、C、SC中的数据全部删除，再利用磁盘上备份的数据来恢复数据。5．如果要在表SC中插入某个学生的选课信息<如：学号为“2007001005”，课程号为“c123”，成绩待定），应如何进行？

6．求各系学生的平均成绩，并把结果存入数据库；

7．将“CS”系全体学生的成绩置零；

8．删除“CS”系全体学生的选课记录；

9．删除学号为“S1”的相关信息；

10．将学号为“S1”的学生的学号修改为“S001”；

11．把平均成绩大于80分的男同学的学号和平均成绩存入另一个表S——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’的，并且入学年份为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

DELETEFROM student

WHERE(2008-birthday><20 and spno='003'

3．对于student表，插入一条新记录，它的具体信息为，学号：200700302、姓名：李伟龙、性别：男、出生日期：19880808、院系编号：‘001’、专业编号：

‘01’、班级号：‘003’、入学时间：20070901。

步骤：新建查询-输入代码:

INSERTINTO student

(sno,sname,sex,birthday,dno,spno,classno,entime>

values('200700302','李伟龙','男

','880808','001','001','003','20070901'>

<注意：要将学号的类型改为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之间。

创建课程关系表C ：

创建学生-课程表SC ：

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 */

<2）/*客户表customer */

<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 的操作数据库。

四、实验步骤

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表中员工的平均薪水，输出低于平均薪水的员工的人数以及这些员工