数据库实验指导手册新版

DATABASE

Database System Concepts 数据库系统概论  实验指导书 隆承志编著

计算机科学与工程学院

 数据库实验指导书 I

DATABASE

目录 

目录 .................................................................................................................................  概述 ....................................................................................................................................... 1 实验1：数据库、表建立与数据插入 ............................................................................. 2 

实验名称和性质.......................................................................................................... 2 实验目的 ...................................................................................................................... 2 实验的软硬件环境要求 ............................................................................................. 2 知识准备 ...................................................................................................................... 2 实验内容 ...................................................................................................................... 3 验证性实验 .................................................................................................................. 3 设计性实验 .................................................................................................................. 7 思考题 .......................................................................................................................... 8 实验2：单表查询 .............................................................................................................. 9 

实验名称和性质.......................................................................................................... 9 实验目的 ...................................................................................................................... 9 实验的软硬件环境要求 ............................................................................................. 9 知识准备 ...................................................................................................................... 9 实验内容 ................................................................................................................... 10 验证性实验 ............................................................................................................... 10 设计性实验 ............................................................................................................... 12 思考题 ....................................................................................................................... 13 实验3：多表查询 ........................................................................................................... 14 

实验名称和性质....................................................................................................... 14 实验目的 ................................................................................................................... 14 实验的软硬件环境要求 .......................................................................................... 14 知识准备 ................................................................................................................... 14 实验内容 ................................................................................................................... 15 验证性实验 ............................................................................................................... 15 设计性实验 ............................................................................................................... 18 思考题 ....................................................................................................................... 18 实验4：数据操作与索引 ............................................................................................... 19 

实验名称和性质....................................................................................................... 19 实验目的 ................................................................................................................... 19 实验的软硬件环境要求 .......................................................................................... 19 知识准备 ................................................................................................................... 19 实验内容 ................................................................................................................... 20 验证性实验 ............................................................................................................... 20 

DATABASE

思考题 ....................................................................................................................... 23 实验5：数据完整性约束 ............................................................................................... 24 

实验名称和性质....................................................................................................... 24 实验目的 ................................................................................................................... 24 实验的软硬件环境要求 .......................................................................................... 24 知识准备 ................................................................................................................... 24 实验内容 ................................................................................................................... 25 验证性实验 ............................................................................................................... 25 设计性实验 ............................................................................................................... 28 思考题 ....................................................................................................................... 28 实验6：SQL编程及存储过程 ...................................................................................... 29 

实验名称和性质....................................................................................................... 29 实验目的 ................................................................................................................... 29 实验的软硬件环境要求 .......................................................................................... 29 知识准备 ................................................................................................................... 29 实验内容 ................................................................................................................... 31 验证性实验 ............................................................................................................... 31 设计性实验 ............................................................................................................... 33 思考题 ....................................................................................................................... 33 实验7：事务处理和触发器 ........................................................................................... 34 

实验名称和性质....................................................................................................... 34 实验目的 ................................................................................................................... 34 实验的软硬件环境要求 .......................................................................................... 34 知识准备 ................................................................................................................... 34 实验内容 ................................................................................................................... 35 验证性实验 ............................................................................................................... 35 设计性实验 ............................................................................................................... 38 思考题 ....................................................................................................................... 38 实验8：数据库安全与数据库恢复 ............................................................................ 39 

实验名称和性质....................................................................................................... 39 实验目的 ................................................................................................................... 39 实验的软硬件环境要求 .......................................................................................... 39 知识准备 ................................................................................................................... 39 实验内容 ................................................................................................................... 40 验证性实验 ............................................................................................................... 40 思考题 ....................................................................................................................... 45 实验9：数据库设计 ..................................................................................................... 46 

DATABASE

实验目的 ................................................................................................................... 46 实验的软硬件环境要求 .......................................................................................... 46 知识准备 ................................................................................................................... 46 实验内容 ................................................................................................................... 47 综合性实验 ............................................................................................................... 47 

DATABASE

课程名称：数据库系统概论试验 英文名称：Database System Concepts

总学时： 64 学 分：3 实验学时：16 上机学时：16 课程类别：试验 课程性质：必修 适用专业：计算机各类专业 授课实验室： 

实验（上机）教学目的与基本要求： 

《数据库系统概论》是计算机学科各专业的一门重要专业基础课程，是计算机科学与技术专业、网络工程四年制本科的必修课程。本课程主要学习关系数据库系统，并以大型关系数据库管理系统sql server 为实例。通过本课程的学习，了解数据库系统的基本概念、基本理论，掌握关系数据库的相关知识和技术，初步掌握数据库设计方法，并能用数据库系统建立数据库及简单的应用；通过实际的上机操作，熟悉sql server操作环境，掌握关系数据库标准语言SQL、规范化理论。能采用高级语言进行简单应用系统的实现，能进行用户的授权与管理。 实验（上机）教学方式与考核方式 

教学方式：上机操作

考核方式：上机操作＋实验报告 

实验（上机）指导书和参考书：参见自编的《数据库系统概论实验指导书》 主要仪器设备：安装数据库的个人电脑或者实验室电脑。

DATABASE

实验名称和性质 

实验名称 实验学时 实验性质 必做/选做

数据库、数据表的建立与数据的插入 2

□验证 □综合 □设计 □必做 □选做

实验目的 

1) 了解和掌握SQL Server系统； 2) 掌握数据库建立的几种方法；

3) 掌握数据表的建立方法和数据表中数据的插入方法。

实验的软硬件环境要求 

硬件环境要求： PC机（单机）

使用的软件名称、版本号：SQL Server 2000（或2005、2008）

知识准备 

1) SQL Server 2000主要的管理工具和实用程序有以下6个，其中企业管理器和查

询分析器是本课程实验中用到的工具。

服务管理器：主要作用是用来启动数据库服务器的实时服务、暂停和停止正

在运行的服务；

企业管理器：用户和系统管理员可以使用它来管理网络、计算机、服务和其

它系统组件；

查询分析器：可以使用户交互式地输入和执行各种Transact-SQL语句，并且

迅速地查看这些语句的执行结果；

服务器网络实用工具：配置本计算机作为服务器时允许使用的连接协议； 客户端网络实用工具：配置客户端的网络连接，管理和测定客户端的网络库

等；

DATABASE

联机帮助文档；

实验内容 

1) 创建数据库和查看数据库属性；

2) 在企业管理器中和查询分析器中创建数据表； 3) 企业管理器中和查询分析器中数据表中数据的插入。

验证性实验 

（1）企业管理器使用

1）创建数据库

使用企业管理器建立数据库的步骤：

① 单击“开始”菜单下“所有程序”→“Microsoft SQL Server” →“企业管理器”。 ② 选中需要在其上创建数据库的服务器，点击前面的“＋”号，使其展示为树形目录。 ③ 选中“数据库”文件夹，单击鼠标右键，在弹出菜单上选择“新建数据库”。

随后在数据库属性对话框的常规页面中，输入数据库名“学生选课”，如下图所示：

DATABASE

数据库属性对话框有3 个页面：常规页码、数据文件页面和事务日志页码。分别用来义数据库名、数据文件属性和日志文件属性。请结合帮助文档，了解这些页面中选项的含义。

4）单击“确定”按钮，关闭对话框。在企业管理器窗口中出现“学生选课”数据库标志，表明已经成功创建数据库。

5） SQL Server 企业管理器中，右键单击“学生选课”数据库，在弹出菜单中选择“属性”，将弹出一个对话框，显示“学生选课”数据库的属性。

（二）．创建数据库表 

1） 打开刚才创建的“ 学生选课” 数据库， 并在“SQL Server Enterprise Mananger”窗口的右边窗口中用鼠标右键点击“表”对象，选择“新建表”命令，打开SQL Server

的表编辑器窗口，如下图所示：

DATABASE

表1 学生表

列名 学号 姓名 性别 出生日期 专业名 所在系 联系电话

数据类型 Char Char Bit

smalldatetime Char Char char

长度 6 8 1 4 10 10 11

是否允许为空 N N N N N N Y

3）点击工具栏上的“保存”按钮，在弹出的“选择名称”对话框中输入表名“学生-表”，然后单击“确定”按钮，关闭表编辑器窗口，完成新表的创建。

4）同理，根据表2 建课程表，根据表3 建选课表

表2 课程表

列名 课程号 课程名 学时 学分

数据类型 Char Char Tinyint Tinyint

表3 选课表

列名 学号 课程号 成绩

（三）增删改数据 

1）打开SQL Server 企业管理器，选中服务器，展开数据库文件夹，进一步展开“学生选课”数据库。单击表文件夹，找到“学生表”。

2）用鼠标右键单击“学生表”，选择“打开表”—“返回全部行，就会出现表更新对话框，如下图所示：

数据类型 Char Char Tinyint

长度 6 3 1

是否允许为空 N N Y

长度 6 20 1 1

是否允许为空 N N N N

DATABASE

3）参考表4 的内容，为学生表输入数据。

（2）查询分析器使用

1）在企业管理器中，点击菜单“工具”—“SQL 查询分析器”，打开查询分析器的

窗口。如下图所示：

2）在查询分析器的右窗口中输入T-SQL语句： CREATE DATABASE  LMS 

选中该语句并执行（单击工具栏的“执行”按钮），出现

CREATE DATABASE进程正在磁盘“LMS_DATA”上分配空间 CREATE DATABASE进程正在磁盘“LMS_LOG”上分配空间

表示数据库成功建立。可以在“对象浏览器”（左边窗格）中的服务器名称上右击鼠标，并单击“刷新”，在服务器中可以看到LMS数据库。

3）用T-SQL语句创建数据表Student： USE LMS

CREATE TABLE Student( SNO VARCHAR(10) NOT NULL,

DATABASE

SEX CHAR(2), BIRTHDAY DATETIME)

选中上述语句并执行，出现命令已成功完成

表示数据表已经建立好。可以在“对象浏览器”中的LMS数据库上右击鼠标，并单击“刷新”，然后展开“用户表”，可以看到Student数据表。

4）向Student数据表中插入数据记录。 INSERT INTO Student VALUES

（’200512’,’Josephine’,’F’,’1980-12-20’）

选中上述语句并执行，出现“所影响的行数为1行”，表示数据已经插入到数据表中。

设计性实验 

1） 删除验证性试验中创建的LMS数据库， 2） 再创建LMS数据库。

3） 在LMS数据库中创建三个数据表：

S (SNO VARCHAR (10) NOT NULL ,SNAME VARCHAR(20),DEPA VARCHAR(20), AGE INT, SEX CHAR(4) )

C (CNO VARCHAR(10) NOT NULL, CNAME VARCHAR(20),cpno varchar(10),credit int)

SC(SNO VARCHAR(10) NOT NULL, CNAME VARCHAR(10) NOT NULL, GRADE DECIMAL)

3）在3个表中输入如下数据。

表4 学生表-S

学号SNO 200512 200518 200018 200511 200510 200513 200514 200012 200515

姓名SName 李勇 刘晨 王敏 杨扬 张立 张立 王点点 欧阳雨 刘依依

系部Depa 计算机系 计算机系 数学系 物理系 信息系 物理系 信息系 数学系 数学系

年龄Age 20 19 18 20 19 19 19 20 23

性别Sex 男 女 女 男 男 男 女 男 女

DATABASE

表5课程表-C

课程号CNo c01 c02 c03 c04

课程名CName 数据库原理 信息系统 数据结构 DB_设计

前修课程Cpno 学分Credit c03 C01 null co1

表6 成绩表-SC

学号SNo 200512 200512 200512 200515 200518 200018 200518 200511 200511 200511 200514 200514 200514 200514

课程号CNo c02 c03 c04 c01 c03 c01 c02 c02 c01 c03 c02 c01 c03 c04

成绩Grade 70 null null 80 95 80 45 78 45 89 78 45 89 78

4 4 6 3

思考题 

1）SQL Server 2000 提供了哪些基本数据类型？

2）数据库创建后怎样修改数据库数据文件和日志文件分配的空间大小？

DATABASE

单表查询 2

□验证 □综合 □设计 □必做 □选做

实验名称和性质 

实验名称 实验学时 实验性质 必做/选做

实验目的 

1) 掌握数据的基本检索方法；

2) 掌握数据查询的Group by和Order by子句的使用； 3) 掌握聚集函数的使用方法。

实验的软硬件环境要求 

硬件环境要求： PC机（单机）

使用的软件名称、版本号：SQL Server 2000（或2005、2008）

知识准备 

数据检索的语句格式：

SELECT [ALL|DISTINCT] <目标列表达式列表> FROM <表名或视图名列表> [ WHERE <条件表达式> ]

[ GROUP BY <列名1> [ HAVING <条件表达式> ] ] [ ORDER BY <列名2> [ ASC|DESC ] ]

其中，SELECT后的目标列表达式可以是列名、表达式或函数。

GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。

DATABASE

ORDER BY子句：对查询结果表按指定列值的升序或降序排序 注意：T-SQL语句中关键字不区分大小写

实验内容 

1) 对数据表进行简单检索； 2) 对数据表进行排序检索； 3) 在检索中使用聚集函数；

验证性实验 

先选择要操作的数据库，用T-SQL命令： USE LMS

或在工具栏的当前数据库中选择LMS， 如下图所示。

以下查询要求在逐个语句执行，执行后将执行结果记录下来： 1) 无条件查询全部数据

USE LMS SELECT * FROM S

2) 在SELECT关键字后指明要检索的列名

DATABASE

SELECT SNO，SNAME FROM S ② 查询S表中的系部名 SELECT DEPA FROM S

③ 查询S表中不重复的系部名 SELECT DISTINCT DEPA FROM S 3) 改变列标题的检索

① 使用 空格 形式： 列名 新标题 SELECT SNO 学号 , SNAME 姓名 FROM S ② 使用“AS”形式，列名 AS 新标题 SELECT SNO AS 学号, SNAME AS 姓名 FROM S 4) 有条件选择的查询

① 在S表检索“信息系”的学生信息 SELECT * FROM S WHERE DEPA=’信息系’ ② 在S表中检索姓“王”的学生信息

SELECT * FROM S WHERE sname like ‘王%’ ③ 在SC表检索’C01’选修课成绩为空的选课记录 SELECT SNO， CNO FROM SC

WHERE cno=’C01’ and grade is null ④ 检索年龄为21，18，22的学生学号、姓名 SELECT SNO，SNAME FROM S WHERE age in （21,18,22）

Age IN {21,18,22}表示某条记录的AGE字段值是否是集合{21,18,22}中的元素，如是，则选择。它等价于下面语句：

SELECT SNO，SNAME FROM S

WHERE age=18 or age=21 or age=22 5) 使用聚集函数

① 查询选课表中最高分、平均分、最低分

SELECT MAX(GRADE), AVG(GRADE),MIN(GRADE) FROM SC ② 查询“C01”课程的最高分、平均分和最小成绩。 SELECT MAX(GRADE), AVG(GRADE),MIN(GRADE) FROM SC WHERE CNO=’C01’ 6) 对检索结果进行排序

SELECT * FROM SC WHERE GRADE IS NULL ORDER BY SNO，CNO DESC 7) 进行分组统计：

① 查询各学生的选课数

DATABASE

SELECT SNO，COUNT（*） FROM SC

GROUP BY SNO

② 使用HAVING ：“选课表”中查询选修了3 门以上课程的学生学号。 SELECT SNO ，COUNT（*）FROM SC GROUP BY SNO

HAVING COUNT(*)>=3

③ “选课表”中按学号分组汇总学生的平均分，并按平均分降序排列。 SELECT SNO 学号, AVG(成绩) 平均分 FROM SC GROUP BY SNO

ORDER BY 平均分 DESC

④ 分析下面两个SELECT语句执行结果，说明有什么不同？ SELECT SNO FROM SC ORDER BY SNO GO

SELECT SNO FROM SC

GROUP BY SNO

8) 在ORDER BY字句后带COMPUTE 字句

① 按学号对不及格的成绩记录进行汇总。

SELECT SNO,CNO , GRADE FROM SC WHERE GRADE<60 ORDER BY SNO

COMPUTE COUNT(GRADE)

② 按学号对不及格的成绩记录进行明细汇总（即根据排序结果进行分组汇总） SELECT SNO,CNO , GRADE FROM SC WHERE GRADE<60 ORDER BY SNO

COMPUTE COUNT(GRADE) BY SNO

设计性实验 

（1）实验要求

1) 查询计算机系学生的学号和姓名。 2) 查询选修了课程的学生学号。

3) 查询选修101 课程的学生学号和成绩，并要求对查询结果按成绩的降序排列，

如果成绩相同则按学号的升序排列。

DATABASE

5) 查询学校开设的课程总数。

6) 查询选修两门及两门以上课程的学生学号。

7) 查询年龄在20-22之间的男生的学号、姓名、系部。 8) 查询选修‘C01’课程的学生人数。

（2）实验报告要求

1) 写出验证性实验的执行结果；

2) 用T-SQL完成设计性实验并写出执行结果； 3) 完成以下思考题。

思考题 

1) 使用ORDER BY子句后SELECT后的数据列有什么限制吗？使用GROUP BY

呢？

2) 执行select max(grade) from sc, 写出执行结果；执行 select sno, max(grade)

from sc group by sno,写出执行结构。思考：使用GROUP BY<分组条件>子句后，语句中的统计函数的运行结果有什么不同?

DATABASE

多表查询 2

□验证 □综合 □设计 □必做 □选做

实验名称和性质 

实验名称 实验学时 实验性质 必做/选做

实验目的 

1) 掌握多表连接查询方法； 2) 掌握IN 子查询的嵌套查询； 3) 了解EXISTS嵌套查询方法；

实验的软硬件环境要求 

硬件环境要求： PC机（单机）

使用的软件名称、版本号：SQL Server 2000（或2005、2008）

知识准备 

1) 多表的连接查询（相当于做笛卡儿乘积） SELECT <目标列表达式列表> FROM 表名，表名 2) 多表的等值连接查询

SELECT [ALL|DISTINCT] <表名.列名>… FROM 表1，表2

WHERE 表1.列名=表2.列名

注意：多表连接时要注意当某列名在两张表中同时存在时，在列名前要加表名以示区分。

3) 表自身的连接

DATABASE

FROM 表1 AS 别名1， 表1 AS 别名2 WHERE 别名1.列名=别名2.列名

注意：自身连接时因为是对同一张表操作，为区分开来对该表操作的顺序，需要对表取两个别名，以示区别。

4) IN嵌套子查询

SELECT <目标列表达式列表> FROM 表名 WHERE 列名 IN （SELECT 字句） 5) * EXISTS嵌套子查询

–带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。

●若内层查询结果非空，则返回真值 ●若内层查询结果为空，则返回假值。

由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。

所以，EXISTS子查询中一般是相关自查询，即子查询脱离父查询后不能单独执行。 思考：如果EXISTS子查询中是不相关子查询，会有什么结果？

实验内容 

1) 多表等值连接查询； 2) 外连接查询； 3) IN子查询嵌套； 4) * EXISTS嵌套查询。

验证性实验 

1) 多表的连接查询（相当于做笛卡儿乘积）

SELECT * FROM C，SC

2) 表的等值连接查询

① 查询各学生的选课信息（包括学号、课程名、成绩）

DATABASE

因为学号和成绩在SC表中，而课程名在C表中，因此需要多表查询。 SELECT SNO，CNAME，GRADE FROM C，SC

WHERE O=O

② 查询学生的选课记录，显示学生的学号、姓名、课程号、成绩） SELECT S.SNO，SNAME，CNO，GRADE FROM S，SC

WHERE S.SNO=SC.SNO

③ 查询学生的选课记录，显示学生的学号、姓名、课程号、课程名、成绩 SELECT S.SNO，SNAME，O，CNAME，GRADE FROM S，SC，C

WHERE S.SNO=SC.SNO AND O=O

④ 左外连接查询：当希望左表（第一张表）中所有记录全部显示出来时，需要用

左外连接操作。 INSERT INTO S (SNO,SNAME) VALUES（’20000’,’ZXX’）

SELECT S.SNO，SNAME，CNO，GRADE FROM S

LEFT OUTER JOIN SC ON S.SNO=SC.SNO 观察与下面等值连接的执行结果有何不同？

SELECT S.SNO，SNAME，CNO，GRADE FROM S, SC WHERE S.SNO=SC.SNO

3) 表自身的连接

① 查询与‘李勇’同系的学生学号

将学生表S与S本身进行等值连接（系部相等），因为S与S做连接操作时不能区分，所以，对表取一个别名。然后将第二张表S中名字为‘李勇’的记录选择出就可。

SELECT S1.SNO

FROM S AS S1， S AS S2

WHERE S1.DEPA=S2.DEPA AND S2.SNAME=’李勇’

② 显示每个学生的非最高分成绩（学生自己的选课成绩中，不是最高分的选课记

录显示出来）

SELECT SNO, CNO, GRADE FROM SC AS SC1

WHERE GRADE <(SELECT MAX(GRADE) FROM SC AS SC2 WHERE SC2.SNO=SC1.SNO)

4) IN嵌套子查询

① 不相关的IN 子查询：子查询可以单独执行，与被嵌套的查询无关。

如，查询与‘李勇’同系的学生学号

可以先查询出‘李勇’所在的系，然后再到S表中查询与上述结果相同的记录。 SELECT SNO

DATABASE

FROM S

WHERE DEPA IN

（SELECT DEPA FROM S WHERE SNAME=‘李勇’） 查询‘数据库原理’课程的选课人数 SELECT COUNT（*） FROM SC WHERE CNO IN

（ SELECT CNO FROM C WHERE CNAME=‘数据库原理’）

② 相关的子查询：子查询中要用到父查询表的信息，子查询不能独立执行。

如，查询选修课程号为“C01”课程且成绩至少高于选修课程号为“C02”的同学的Cno、Sno和GRADE。

在子查询中，因为要查找该同学‘C02’课程的成绩，所以，需要父查询表中该学生的学号信息。

SELECT CNO，SNO，GRADE FROM SC AS SC1

WHERE CNO=’C01’ AND GRADE >( SELECT GRADE FROM SC AS SC 2

WHERE SC2.SNO=SC1.SNO AND O=’C02’)

5) * EXISTS嵌套子查询

① 执行以下语句，观察显示的两个查询结果

SELECT SNO，SNAME FROM S WHERE EXISTS

（SELECT * FROM SC WHERE CNO=‘C03’） GO

SELECT SNO，SNAME FROM S

② 执行以下语句，观察显示的两个查询结果

INSERT INTO C VALUES（‘C06’，‘数据库安全’，NULL，3） SELECT SNO，SNAME FROM S WHERE EXISTS

（SELECT * FROM SC WHERE CNO=‘C06’） ③ 查询‘数据库原理’课程的选课人数

SELECT COUNT（*） FROM SC WHERE EXISTS

（ SELECT * FROM C

WHERE O=O AND CNAME=’ 数据库原理’)

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