数据库原理实验指导书（版本3）

数据库原理实验指导书（版本3）
《数据库原理与技术》
实验指导书
⽬录
实验⼀：数据库管理系统软件的使⽤ (2)
实验⼆: 数据库的建⽴和维护 (4)
实验三：数据库的简单查询和连接查询 (14)
实验四：数据库的嵌套查询实验 (16)
实验五：数据库的分组查询和统计查询 (19)
实验六：数据库视图的定义及使⽤ (21)
实验七：数据完整性实验 (23)
实验⼋*：数据库备份和恢复实验 (33)
实验九：数据库课程设计 (33)
说明： 1. 带*号的为选做；
2. 实验环境为Sql Server。

但除实验七有较⼤差别外，其余的所有实验在Access、Oracle、MySql甚⾄VFP中都类似。

除在Sql Server中进⾏实验外，建议也在Access中操作⼀下。

实验⼀：数据库管理系统软件的使⽤
⼀、实验⽬的
(1)认识⼏种常见的数据库管理系统，熟悉它们的使⽤界⾯;
(2)熟练掌握建⽴数据库和表，向数据库输⼊数据、修改数据和删除数据的操作。

⼆、实验内容
分别在Access和SQL SERVER2005中建⽴数据库并设计各表，输⼊多条实际数据，并实现数据的增、删、改操作。

三、实验步骤：
创建⽤于学⽣管理数据库，数据库名为XSGL，包含学⽣的基本信息，课程信息和选课信息。

数据库XSGL包含下列3个表：(l）student：学⽣基本信息。

(2）course：课程信息表。

(3）sc：学⽣选课表。

各表的结构分别如表1、表2和表3所⽰。

表1 学⽣信息表：student
1．⽤可视化界⾯在Access中建⽴数据库和表：
（a）在Access中创建xsgl.mdb数据库，使⽤表设计视图创建如表1、表2、表3所⽰结构的3个表。

提⽰：(1)启动Access2000或Access2003，选择⽂件->新建->空数据库，输⼊数据库⽂件名xsgl.mdb，
图1 Access 数据库设计界⾯
进⼊（2）；
（2）启动如图1的设计界⾯后，选择表对象和使⽤使⽤设计器创建表，选择新建，进⼊（3）；（3）⽣成如图2界⾯，选择设计视图,点击确定按钮，进⼊（4）；
（4）在图3表设计界⾯下，分别创建student 表，course 表和sc 表的结构；
图2 表设计器
图3 表结构设计界⾯
（5）输⼊表中的记录：
分别在student 表、course 表和sc 表中输⼊如下表中的记录：
（b ）对表中的记录进⾏浏览、修改、删除操作。

2．在SQLSERVER 中⽤SQL SERVER2005 Management Studio 新建数据库和表：（1）建⽴xsgl 数据库：
①启动
SQL Server2005Management Studio ，界⾯如下：
图4 SQL Server2005企业管理器界⾯
②选择树形菜单数据库，点击⿏标右键，出现如下弹出式菜单，选择新建数据库
③在图中，按图分别设置数据库xsgl的属性，点击确定按钮，完成了数据库的创建。

图5 数据库名称属性设置
图6 数据库数据⽂件和⽇志⽂件属性设置
选择确定按钮，则创建xsgl数据库。

（2）建⽴student、course、sc表：
①选择xsgl数据库树形菜单，选择表，在图7表逻辑对象中点击⿏标的右键，在弹出式菜单中选
择新建，启动表设计器。

图7表逻辑对象界⾯
②选择新建，启动如图8的表设计器，建⽴表结构，保存为student。

图8 student的表结构
③分别按表2、3建⽴course表和sc表。

（3）在表中添加记录：选择student表，在快捷菜单下选择打开表->返回所有⾏，进⼊图9、图10，输⼊学⽣表中的记录。

图10
图10 student表记录录⼊
依次按相同⽅法建⽴course表和sc 表，并输⼊其中的记录。

(4)修改表中的数据:
(5）删除表中的记录
思考：
1.Access数据库主要有哪些对象组成？
2.SQL SERVER2005数据库主要由哪些逻辑对象组成？物理数据库⽂件包括哪些⽂件？
实验⼆: 数据库的建⽴和维护
实验⽬的
熟练掌握建⽴数据库和表，向数据库输⼊数据、修改数据和删除数据的操作。

实验内容
建⽴数据库并设计各表，输⼊多条实际数据，并实现数据的增、删、改操作。

实验步骤：
创建⽤于学⽣管理数据库，数据库名为XSGL，包含学⽣的基本信息，课程信息和选课信息。

数据库XSGL包含下列3个表：(l）student：学⽣基本信息。

(2）course：课程信息表。

(3）sc：学⽣选课表。

各表的结构分别如表1、表2和表3所⽰。

表2 课程信息表：course
⼀.
2．⽤可视化界⾯建⽴：
（a）在SQL SERVER2005中⽤Management Studio-新建数据库；
3．命令⽅式建⽴：
（a）在SQL SERVER2005中，在新建查询中使⽤T-SQL语句：(在做如下操作前，请将第⼀实验建⽴的XSGL数据库删除) CREATE DATABASE XSGL
ON (NAME='XSGL_DA TA',
FILENAME='E:\XSGL.MDF',
SIZE=10MB,
MAXSIZE=50MB,
FILEGROWTH=5%)
LOG ON
(NAME='XSGL_Log',
FILENAME='e:\XSGL_Log.ldf',
SIZE=2MB,
MAXSIZE=5MB,
FILEGROWTH=1MB)
⼆. 表的建⽴：
1．⽤可视化界⾯建⽴：
在SQL SERVER2005中⽤Management Studio—数据库—XSGL—表—右键—新建表;
2．命令⽅式建⽴：
在SQL SERVER2005 中的”新建查询”编辑窗⼝中⽤下列SQL语句:
use XSGL
Create table student(sno varchar(10) NOT NULL, sname varchar (10) NOT NULL, ssex varchar
(2) NOT NULL, sage int NULL, sdept varchar (10) NOT NULL)
go
Create table course(cno varchar (3) NOT NULL , cname varchar (30) NOT NULL, credit int NULL, pcno varchar (3) NULL) go
Create table sc(sno varchar (10) NOT NULL, cno varchar (3) NOT NULL, grade INT NULL) go
三. 表数据的添加:
1. ⽤可视化⽅法:
I: 在SQL SERVER2005中⽤Management Studio—数据库—XSGL—表－表名—右键－打开表－返回所有⾏；
输⼊下列数据：
2. 在SQL SERVER2005”新建查询”的编辑窗⼝中使⽤下列SQL 语句插⼊数据: Select * from student (查看插⼊前的记录信息) insert into student(sno,sname, s sex,sage,sdept) values('95001', '李勇', '男', 20, 'CS') insert into student(sno,sname, s sex,sage,sdept) values('95002', '刘晨', '⼥', 19, 'IS') insert into student(sno,sname, s sex,sage,sdept) values('95003', '王敏', '⼥', 18, 'MA') insert into student(sno,sname, s sex,sage,sdept) values('95004', '张⽴', '男', 19, 'IS') insert into
student(sno,sname, s sex,sage,sdept) values('95005', '刘云', '⼥', 18, 'CS ') Select * from student (查看插⼊后的记录信息) Select * from course (查看插⼊前的记录信息)
insert into course(cno, cname,credit,pcno) values('1', '数据库', 4, '5')
insert into course(cno, cname,credit,pcno) values('2', '数学', 6, null)
insert into course(cno, cname,credit,pcno) values('3', '信息系统', 3, '1')
insert into course(cno, cname,credit,pcno) values('4', '操作系统', 4, '6')
insert into course(cno, cname,credit,pcno) values('5', '数据结构', 4, '7')
insert into course(cno, cname,credit,pcno) values('6', '数据处理', 3, null)
insert into course(cno, cname,credit,pcno) values('7', 'PASCAL语⾔', 4, '6')
Select * from course (查看插⼊前的记录信息)
Select * from sc (查看插⼊前的记录信息)
insert into sc(sno,cno,grade) values('95001', '1' ,92)
insert into sc(sno,cno,grade) values('95001', '2' ,85)
insert into sc(sno,cno,grade) values('95001', '3' ,88)
insert into sc(sno,cno,grade) values('95002', '2' ,90)
insert into sc(sno,cno,grade) values('95002', '3' ,80)
insert into sc(sno,cno,grade) values('95003', '2' ,85)
insert into sc(sno,cno,grade) values('95004', '1' ,58)
insert into sc(sno,cno,grade) values('95004', '2' ,85)
Select * from sc (查看插⼊后的记录信息)
四. 表数据的修改:
1. ⽤可视化⽅法:
在SQL SERVER 2005中⽤Management Studio—数据库—XSGL—表－表名—右键－打开表－返回所有⾏；在表格中将相应的数据修改即可。

2．命令⽅法：在SQL SERVER2005”新建查询”的编辑窗⼝中使⽤下列SQL 语句修改数据。

1)将所有学⽣的年龄增加⼀岁：
Select * from student (查看更新前的记录)
update student set sage=sage+1
Select * from student (查看更新后的记录)
2）将4号课程的学分改为4:
Select * from course (查看更新前的记录)
update course set credit=4 where cno=4
Select * from course (查看更新后的记录)
3）设置7号课程没有先⾏课:
Select * from course (查看更新前的记录)
update course set pcno=null where cno=7
Select * from course (查看更新后的记录)
4）将95001号学⽣的1号课程的成绩增加3分:
Select * from sc (查看更新前的记录)
update sc set grade=grade+3 where sno=’95001’ and cno=’1’
Select * from sc (查看更新后的记录)
五. 表数据的删除:
1. ⽤可视化⽅法:
在SQL SERVER中Management Studio—数据库—XSGL—表－表名—右键－打开表－返回所有⾏；单击左边的⾏标记, 选定某⼀⾏, 或单击后拖动选择相邻的多⾏, 再右击⿏标选择弹出式菜单中的删除。

2．命令⽅法：在SQL SERVER2005”新建查询”的编辑窗⼝中使⽤下列SQL 语句删除数据。

1）删除学号为95005的学⽣的记录：
Select * from student (查看删除前的记录)
delete from student where sno＝’95005’
Select * from student（查看删除后的记录）
2）删除所有的课程记录：
Select * from course (查看删除前的记录)
delete from course
Select * from course (查看删除后的记录)
3) 删除成绩为不及格（少于60分）的学⽣的选课记录：
Select * from sc (查看删除前的记录)
delete from sc where grade<60
Select * from sc (查看删除后的记录)
思考：
⽐较⽤可视化界⾯与命令⽅式在数据的插⼊、修改、删除⽅⾯的优缺点。

实验三：数据库的简单查询和连接查询
实验⽬的：
掌握简单表的数据查询、数据排序和数据联结查询的操作⽅法。

实验内容：
简单查询操作和连接查询操作。

实验步骤：
⼀. 单表查询：
1. 查询全体学⽣的学号和姓名:
select sno, sname from student
2. 查询全体学⽣的所有信息:
select * from student
或者select sno, sname, ssex,sage, sdept from student
3. 查询全体学⽣的姓名, 出⽣年份,和所在系, 并⽤⼩写字母表⽰所有系名:
select sname, '出⽣年份为: ', year(getdate()) - sage, lower(sdept) from student
4. 给上例的结果集指定列名:
select sname, '出⽣年份为: ' 出⽣, year(getdate())- sage 年份, lower(sdept) 系名from student
5. 查询选修了课程的学⽣的学号:
select distinct sno from sc
⽐较: select sno from sc
6. 查询年龄在20岁以下的学⽣的姓名及其年龄:
select sname, sage from student where sage<20
7. 查询考试成绩有不及格的学⽣的学号:
select distinct sno from sc where grade<60
⽐较: select sno from sc where grade<60
8. 查询年龄在20-30岁直接的学⽣的姓名, 姓名, 所在系:
select sname, ssex, sdept from student where sage between 20 and 30
9. 查询IS,CS,MA系的所有学⽣的姓名和性别:
select sname, ssex from student where sdept in ('IS', 'MA','CS')
10. 查找所有姓’李’的学⽣的姓名, 学号和性别:
select sname, sno, ssex from student where sname like '李%'
⽐较: 将学⽣表中的’95001’号学⽣的姓名’李勇’改为’李勇勇’, 再执⾏:
select sname, sno, ssex from student where sname like '李_'
11. 查询没有先⾏课的课程的课程号cno和课程名cname:
select cno, cname from course where pcno is null
⼆. 查询结果排序
12. 查询选修了3号课程的学⽣的学号和成绩, 并按分数降序排列:
select sno, grade from sc where cno='3' order by grade DESC
23. 查询全体学⽣的情况,查询结果按所在系号升序排列, 同⼀系中的学⽣按年龄降序排列:
select * from student order by sdept ASC, sage DESC
三. 连接查询:
14. 查询每个学⽣及其选修课程的情况:
select student.*, sc.* from student, sc where student.sno=sc.sno
⽐较: 笛卡尔集: select student.*, sc.* from student, sc
⾃然连接: select student.sno, sname, ssex, sdept, cno, grade from student, sc where student.sno=sc.sno
15. 查询每⼀门课程的间接先⾏课(只求两层即先⾏课的先⾏课):
select /doc/abdfa69751e79b89680226b2.html o, Second.pcno 间接先⾏课from course First, course Second where First.pcno=/doc/abdfa69751e79b89680226b2.html o
⽐较:
select /doc/abdfa69751e79b89680226b2.html o, Second.pcno 间接先⾏课from course First, course Second where First.pcno=/doc/abdfa69751e79b89680226b2.html o and Second.pcno is not null
16. 列出所有学⽣的基本情况和选课情况, 若没有选课,则只列出基本情况信息:
SQL Server 中: select s.sno, sname, ssex,sdept, cno, grade from student s, sc sc where s.sno*=sc.sno （此处相当于左外连接）select s.sno,sname, ssex,sdept,cno,grade from student s left outer join sc on s.sno=sc.sno
17. 查询每个学⽣的学号, 姓名, 选修的课程名和成绩:
select S.sno, sname, cname, grade from student S, course C, sc SC where S.sno=SC.sno and
/doc/abdfa69751e79b89680226b2.html o=/doc/abdfa69751e79b89680226b2.html o
思考:
如何求出不及格学⽣的学号, 姓名, 不及格的课程名以及成绩。

(select student.sno, sname, cname, grade from student, course, sc where student.sno=sc.sno and
/doc/abdfa69751e79b89680226b2.html o=/doc/abdfa69751e79b89680226b2.html o and grade<60)
实验四：数据库的嵌套查询实验
实验⽬的：
加深对嵌套查询语句的理解。

实验内容：
使⽤IN、⽐较符、ANY或ALL和EXISTS操作符进⾏嵌套查询操作。

实验步骤：
⼀. 使⽤带IN谓词的⼦查询
1.查询与’刘晨’在同⼀个系学习的学⽣的信息:
select * from student where sdept in
(select sdept from student where sname='刘晨')
⽐较: select * from student where sdept =
(select sdept from student where sname='刘晨') 的异同
⽐较: select * from student where sdept =
(select sdept from student where sname='刘晨') andsname<>'刘晨V ⽐较: select S1.* from student S1, student S2 where
S1.sdept=S2.sdept and S2.sname='刘晨'
2.查询选修了课程名为’信息系统’的学⽣的学号和姓名:
SQL Server中: select sno, sname from student where sno in
(select sno from sc where cno in
(select cno from course where cname='信息系统'))
3.查询选修了课程’1’和课程’2’的学⽣的学号(姓名):
select sno from student where sno in (select sno from sc where cno='1')
and sno in (select sno from sc where cno='2')
select x.sno from SC x ,SC y
where x.sno=y.sno and /doc/abdfa69751e79b89680226b2.html o='1' and
/doc/abdfa69751e79b89680226b2.html o='2'
select sno from SC where cno='1' and sno in (select sno from SC where cno='2')
⽐较: 查询选修了课程’1’或课程’2’的学⽣的sno:
select sno from sc where cno='1' or cno='2'
⽐较连接查询:
select A.sno from sc A, sc B where A.sno=B.sno and /doc/abdfa69751e79b89680226b2.html o='1' and /doc/abdfa69751e79b89680226b2.html o='2'
⼆. 使⽤带⽐较运算的⼦查询
4.查询⽐’刘晨’年龄⼩的所有学⽣的信息:
select * from student where sage<
(select sage from student where sname='刘晨')
三. 使⽤带Any, All谓词的⼦查询(对于ALL全称量词，建议改成否定之否定存在量词)
5.查询其他系中⽐信息系(IS)某⼀学⽣年龄⼩的学⽣姓名和年龄;
select sname, sage from student where sage
(select sage from student where sdept='IS')
and sdept<>'IS'
6.查询其他系中⽐信息系(IS)学⽣年龄都⼩的学⽣姓名和年龄:
select sname, sage from student where sage
(select sage from student where sdept='IS')
and sdept<>'IS'
7.查询与计算机系(CS)系所有学⽣的年龄均不同的学⽣学号, 姓名和年龄:
select sno,sname,sage from student where sage<>all
(select sage from student where sdept='CS')
四. 使⽤带Exists谓词的⼦查询和相关⼦查询
8.查询与其他所有学⽣年龄均不同的学⽣学号, 姓名和年龄:
select sno,sname,sage from student A where not exists
(select * from student B where A.sage=B.sage and A.sno<>B.sno)
9.查询所有选修了1号课程的学⽣姓名:
select sname from student where exists
(select * from sc where sno=student.sno and cno='1')
select sname from student where sno in
(select sno from sc where cno='1')
10.查询没有选修了1号课程的学⽣姓名:
select sname from student where not exists
(select * from sc where sno=student.sno and cno='1')
11.查询选修了全部课程的学⽣姓名:
select sname from student where not exists 不存在⼀门课没有选的学⽣.
(select * from course where not exists ⼀门课都没有选的,
( select * from sc where sno=student.sno and cno=/doc/abdfa69751e79b89680226b2.html o)) 选某门课, 11. 查询⾄少选修了学⽣95002选修的全部课程的学⽣的学号:
select distinct sno from sc A where not exists
(select * from sc B where sno='95002' and not exists
(select * from sc C where sno=A.sno and cno=/doc/abdfa69751e79b89680226b2.html o))
12. 求没有⼈选修的课程号cno和cname:
select cno,cname from course C where not exists
(select * from sc where /doc/abdfa69751e79b89680226b2.html
o=/doc/abdfa69751e79b89680226b2.html o )
13*. 查询满⾜条件的(sno,cno)对, 其中该学号的学⽣没有选修该课程号cno的课程select sno,cno from student,course where not exists
(select * from sc where cno=/doc/abdfa69751e79b89680226b2.html o and sno=student.sno)
14*. 查询每个学⽣的课程成绩最⾼的成绩信息(sno,cno,grade):
select * from sc A where grade=
(select max(grade) from sc where sno=A.sno ) order by A.sno ASC
(此处可否⽤GROUP by)
select * from sc where grade in (select max(grade) from sc group by sno) (此答案有点问题: 当在数据库中同时有两个相同的最⾼分的,就出现判断错误.如95001⾥有⼀个最⾼分85, 95002⾥假如也有⼀个学⽣某门课成绩为85,这时问题就出现了.)
select sc.sno, maxgrade from sc , (select sno, max(grade) as maxgrade from sc group by sno) as A where sc.sno=A.sno and grade=maxgrade
思考:
如何查询所有学⽣都选修了的课程的课程号cno?
SELECT CNO,CNAME
FROM Course C
WHERE NOT EXISTS
(SELECT *
FROM Student S
WHERE NOT EXISTS 某个学⽣选课, ⼀个学⽣都没有选,
(SELECT *
FROM SC
WHERE /doc/abdfa69751e79b89680226b2.html
O=/doc/abdfa69751e79b89680226b2.html O AND SC.SNO=S.SNO));
select cno from (select count(sno) as num,cno from sc group by cno) as A
where A.num= (select count(distinct sno) from student)
select /doc/abdfa69751e79b89680226b2.html
o,/doc/abdfa69751e79b89680226b2.html ame from (select count(sno) as num,cno from sc group by cno) as A,course where A.num= (select count(distinct sno) from student) and
/doc/abdfa69751e79b89680226b2.html o=/doc/abdfa69751e79b89680226b2.html o
select /doc/abdfa69751e79b89680226b2.html o,num from (select count(sno) as num,cno from sc group by cno) as temp(num,cno) where temp.num= (select count(distinct sno) from student)
select /doc/abdfa69751e79b89680226b2.html o,num,cname from (select count(sno) as num,cno from sc group by cno) as temp(num,cno),course where temp.num= (select count(distinct sno) from student) and
/doc/abdfa69751e79b89680226b2.html o=/doc/abdfa69751e79b89680226b2.html o (课程名和课程号)
实验五：数据库的分组查询和统计查询
实验⽬的：
熟练掌握数据查询中的分组、统计、计算和集合的操作⽅法。

实验内容：
使⽤聚集函数查询、分组计算查询、集合查询。

实验步骤：
⼀. 使⽤聚集函数：
1．查询学⽣总⼈数：
Select Count(*) as 学⽣总数from student
2. 查询选修了课程的学⽣总数：
select count(distinct sno) as 选课学⽣总数from sc
3. 查询所有课程的总学分数和平均学分数,以及最⾼学分和最低学分：
select sum(credit) as 总credit,avg(credit) as 课程平均学分,max(credit) as 最⾼学分, min(credit) as 最低学分from course
4. 计算1号课程的学⽣的平均成绩, 最⾼分和最低分:
select avg(grade) as 平均成绩,max(grade) as 最⾼分, min(grade) as 最低分
from scwhere cno='1'
5. 查询’信息系’(IS)学⽣”数据结构”课程的平均成绩:
select avg(grade) from student, course, sc where student.sno=sc.sno and
/doc/abdfa69751e79b89680226b2.html o=/doc/abdfa69751e79b89680226b2.html o and sdept='IS' and cname='数据结构' 6*. 查询每个学⽣的课程成绩最⾼的成绩信息(sno,cno,grade):
select * from grade A where grade=
(select max(grade) from sc where sno=A.sno )
7*. 求成绩低于该门课程平均成绩的学⽣的成绩信息(sno,cno,grade)
select * from grade A where grade= （<）
(select avg(grade) from sc where cno=/doc/abdfa69751e79b89680226b2.html o )
select * from sc A where grade<
(select avg(grade) from sc where cno=/doc/abdfa69751e79b89680226b2.html o )
⼆. 分组查询
8. 查询各系的学⽣的⼈数并按⼈数从多到少排序:
selectsdept, Count(*) as ⼈数from student group by sdept order by ⼈数desc
9. 查询各系的男⼥⽣学⽣总数, 并按系别,升序排列, ⼥⽣排在前:
select sdept,ssex,Count(*) as ⼈数from student group by sdept, ssex order by sdept,ssex desc 10. 查询选修了3门课程已上的学⽣的学号和姓名:
select sno, sname from student where sno in
(select sno from sc group by (sno) having count(*)>3)
11. 查询每个学⽣所选课程的平均成绩, 最⾼分, 最低分,和选课门数:
select sno, avg(grade) as 平均成绩,max(grade) as 最⾼分, min(grade) as 最低分,。