数据库系统概论实验指导书.doc

《数据库系统概论实验指导书.doc》由会员分享，可在线阅读，更多相关《数据库系统概论实验指导书.doc（44页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、数据库系统概论实验指导书目 录实验一：数据库管理系统软件的使用1实验二：数据库的建立和维护8实验三：数据库的简单查询和连接查询12实验四：数据库的嵌套查询实验14实验五：数据库的分组查询和统计查询16实验六：数据库视图的定义及使用18实验七：数据完整性实验20实验八*：数据库备份和恢复实验27实验九：使用ASP访问SQL SERVER数据库33实验十：数据库课程设计42说明： 1. 带*号的为选做； 2. 实验环境为Sql Server。 但除实验七有较大差别外，其余的所有实验在Access、Oracle、MySql甚至VFP中都类似。除在Sql Server中进行实验外，建议也在Access

2、中操作一下。 实验一：数据库管理系统软件的使用一、实验目的(1)认识几种常见的数据库管理系统，熟悉它们的使用界面;(2)熟练掌握建立数据库和表，向数据库输入数据、修改数据和删除数据的操作。二、实验内容分别在Access和SQL SERVER2000中建立数据库并设计各表，输入多条实际数据，并实现数据的增、删、改操作。三、实验步骤：创建用于学生管理数据库，数据库名为XSGL，包含学生的基本信息，课程信息和选课信息。数据库XSGL包含下列3个表： (l） student：学生基本信息。 (2）course：课程信息表。 (3）sc：学生选课表。各表的结构分别如表1、表2和表3所示。表1 学生信息表

3、：student列名数据类型长度是否允许为空值sno字符（文本）型10否sname字符（文本）型10否ssex字符（文本）型2否sage整数（数值）型是sdept字符型4否表2 课程信息表：course列名数据类型长度是否允许为空值cno字符（文本）型3否cname字符（文本）型30否credit整数（数值）型是pcno字符（文本）型3是表3 学生选课表：sc列名数据类型长度是否允许为空值sno字符（文本）型10否cno字符（文本）型30否grade整数（数值）型是1 用可视化界面在Access中建立数据库和表：（a）在Access中创建xsgl.mdb数据库，使用表设计视图创建如表1、表2、

4、表3所示结构的3个表。提示：(1)启动Access2000或Access2003，选择文件-新建-空数据库，输入数据库文件名xsgl.mdb，进入（2）；图1 Access数据库设计界面（2）启动如图1的设计界面后，选择表对象和使用使用设计器创建表，选择新建，进入（3）；图2 表设计器（3）生成如图2界面，选择设计视图,点击确定按钮，进入（4）；图3 表结构设计界面（4）在图3表设计界面下，分别创建student表，course表和sc表的结构；（5）输入表中的记录：分别在student表、course表和sc表中输入如下表中的记录：在数据库视图下，选择表对象中的相应表选择打开，在浏览视图下输

5、入如下表记录：snosnamessexsagesdept95001李勇男20CS95002刘晨女19IS95003王敏女18MA95004张立男19IS95005刘云女18CScnocnamecreditpcno1数据库452数学63信息系统314操作系统465数据结构476数据处理37PASCAL语言46snocnograde9500119295001285950013889500229095002380950032859500415895004285（b）对表中的记录进行浏览、修改、删除操作。2在SQLSERVER中用企业管理器新建数据库和表：（1）建立xsgl数据库：启动SQL Serv

6、er2000企业管理器，界面如下：图4 SQL Server2000企业管理器界面选择树形菜单数据库，点击鼠标右键，出现如下弹出式菜单，选择新建数据库在图中，按图分别设置数据库xsgl的属性，点击确定按钮，完成了数据库的创建。图5 数据库名称属性设置 图6 数据库数据文件属性设置图7 数据库日志文件属性设置选择确定按钮，则创建xsgl数据库。（2）建立student、course、sc表：选择xsgl数据库树形菜单，选择表，在图8表逻辑对象中点击鼠标的右键，在弹出式菜单中选择新建，启动表设计器。图8表逻辑对象界面选择新建，启动如图9的表设计器，建立表结构，保存为student。图9 stude

7、nt的表结构分别按表2、3建立course表和sc表。（3）在表中添加记录：选择student表，在快捷菜单下选择打开表-返回所有行，进入图11，输入学生表中的记录。图10图11 student表记录录入依次按相同方法建立course表和sc 表，并输入其中的记录。(4)修改表中的数据:(5）删除表中的记录思考：1. Access数据库主要有哪些对象组成？2. SQL SERVER2000数据库主要由哪些逻辑对象组成？物理数据库文件包括哪些文件？实验二：数据库的建立和维护实验目的熟练掌握建立数据库和表，向数据库输入数据、修改数据和删除数据的操作。实验内容建立数据库并设计各表，输入多条实际数据，

8、并实现数据的增、删、改操作。实验步骤：创建用于学生管理数据库，数据库名为XSGL，包含学生的基本信息，课程信息和选课信息。数据库XSGL包含下列3个表： (l） student：学生基本信息。 (2）course：课程信息表。 (3）sc：学生选课表。各表的结构分别如表1、表2和表3所示。表1 学生信息表：student列名数据类型长度是否允许为空值sno字符型10否sname字符型10否ssex字符型2否sage整数型是sdept字符型4否表2 课程信息表：course列名数据类型长度是否允许为空值cno字符型3否cname字符型30否credit整数型是pcno字符型3是表3 学生选课表：

9、sc列名数据类型长度是否允许为空值sno字符型10否cno字符型30否grade整数型是一. 数据库的建立：2 用可视化界面建立：（a） 在SQLSERVER中用企业管理器-新建数据库；3 命令方式建立：（a） 在SQLSERVER中，在查询分析器中使用T-SQL语句：CREATE DATABASE XSGL ON (NAME=XSGL_DATA, FILENAME=E:XSGL.MDF, SIZE=10MB, MAXSIZE=50MB, FILEGROWTH=5%) LOG ON (NAME=XSGL_Log, FILENAME=e:XSGL_Log.ldf, SIZE=2MB, MAXSI

10、ZE=5MB, FILEGROWTH=1MB)二. 表的建立：1 用可视化界面建立：在SQL SERVER中用企业管理器数据库XSCJ表右键新建表;2 命令方式建立：在SQL SERVER中查询分析器的编辑窗口中用下列SQL语句:use XSGLCreate table student(sno CHAR(5),sname CHAR(10),ssex CHAR(2),sage int,sdept CHAR(4)goCreate table course(cnoCHAR(2),cname CHAR(30),creditINT,pcnoCHAR(2) NULL)goCreate table sc(s

11、no CHAR(5),cno CHAR(2),gradeINT NULL)go三. 表数据的添加:1. 用可视化方法: I: 在SQL SERVER中用企业管理器数据库XSCJ表表名右键打开表返回所有行；输入下列数据：snosnamessexsagesdept95001李勇男20CS95002刘晨女19IS95003王敏女18MA95004张立男19IS95005刘云女18CScnocnamecreditpcno1数据库452数学63信息系统314操作系统465数据结构476数据处理37PASCAL语言46snocnograde950011929500128595001388950022909

12、50023809500328595004158950042852. 在SQL SERVER查询分析器的编辑窗口中使用下列SQL 语句插入数据:insert into student(sno,sname,ssex,sage,sdept) values(95001,李勇,男,20,CS)insert into student(sno,sname,ssex,sage,sdept) values(95002,刘晨,女,19, IS)insert into student(sno,sname,ssex,sage,sdept) values(95003,王敏,女,18,MA)insert into stu

13、dent(sno,sname,ssex,sage,sdept) values(95004,张立,男,19,IS)insert into student(sno,sname,ssex,sage,sdept) values(95005,刘云,女,18,CS )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,

14、信息系统, 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)insert into sc(sno,cno,grade) values(95

15、001, 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

16、,58)insert into sc(sno,cno,grade) values(95004, 2,85)四. 表数据的修改:1. 用可视化方法: 在SQL SERVER中用企业管理器数据库XSCJ表表名右键打开表返回所有行；在表格中将相应的数据修改即可。2命令方法：将所有学生的年龄增加一岁： update student set sage=sage+1 将4号课程的学分改为4: update course set credit=4 where cno=4设置7号课程没有先行课: update course set pcno=null where cno=7将95001号学生的1号课程的成绩增

17、加3分: update sc set grade=grade+3 where sno=95001 and cno=1五. 表数据的删除:1. 用可视化方法: 在SQL SERVER中用企业管理器数据库XSCJ表表名右键打开表返回所有行；单击左边的行标记, 选定某一行, 或单击后拖动选择相邻的多行, 再右击鼠标选择弹出式菜单中的删除。2命令方法：删除学号为95005的学生的记录：delete from student where sno95005删除所有的课程记录：delete from course删除成绩为不及格（少于60分）的学生的选课记录：delete from sc where gra

18、de60思考：比较用可视化界面与命令方式在数据的插入、修改、删除方面的优缺点。实验三：数据库的简单查询和连接查询实验目的：掌握简单表的数据查询、数据排序和数据联结查询的操作方法。实验内容：简单查询操作和连接查询操作。实验步骤：一. 单表查询：1. 查询全体学生的学号和姓名:select sno, sname from student2. 查询全体学生的所有信息:select * from student或者select sno, sname, ssex,sage, sdept from student3. 查询全体学生的姓名, 出生年份,和所在系, 并用小写字母表示所有系名:select sn

19、ame, 出生年份为: , year(getdate() - sage, lower(sdept) from student4. 给上例的结果集指定列名:select sname, 出生年份为: 出生, year(getdate()- sage 年份, lower(sdept) 系名from student5. 查询选修了课程的学生的学号:select distinct sno from sc 比较: select sno from sc 6. 查询年龄在20岁以下的学生的姓名及其年龄:select sname, sage from student where sage207. 查询考试成绩有

20、不及格的学生的学号:select distinct sno from sc where grade60比较: select sno from sc where grade608. 查询年龄在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. 查找所有姓李的学生的姓名, 学号和性别:sel

21、ect 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

22、=3 order by grade DESC23. 查询全体学生的情况,查询结果按所在系号升序排列, 同一系中的学生按年龄降序排列: 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,

23、 sdept, cno, grade from student, sc where student.sno=sc.sno15. 查询每一门课程的间接先行课(只求两层即先行课的先行课):select Fo, Second.pcno 间接先行课from course First, course Second where First.pcno=So比较: select Fo, Second.pcno 间接先行课 from course First, course Second where First.pcno=So and Second.pcno is not null16. 列出所有学生的基本情况

24、和选课情况, 若没有选课,则只列出基本情况信息: SQL Server 中: select s.sno, sname, ssex,sdept, cno, grade from student s, sc sc where s.sno*=sc.sno17. 查询每个学生的学号, 姓名, 选修的课程名和成绩: select S.sno, sname, cname, grade from student S, course C, sc SC where S.sno=SC.sno and C.cno=SC.cno思考:如何求出不及格学生的学号, 姓名, 不及格的课程名以及成绩。实验四：数据库的嵌套查询

25、实验实验目的：加深对嵌套查询语句的理解。实验内容：使用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 sd

26、ept = (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=信息系统)

27、3. 查询选修了课程1和课程2的学生的学号:select sno from student where sno in (selectsnofrom 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 A.cno=1 and B.cno=2二. 使用带比较运算的子查询4. 查询比刘晨年龄小的所

28、有学生的信息:select * from student where sage (select sage from student where sname=刘晨)三. 使用带Any, All谓词的子查询5. 查询其他系中比信息系(IS)某一学生年龄小的学生姓名和年龄;select sname, sage from student where sage Any (select sage from student where sdept=IS) and sdeptIS6. 查询其他系中比信息系(IS)学生年龄都小的学生姓名和年龄:select sname, sage from student wh

29、ere sage ALL(select sage from student where sdept=IS) and sdeptIS7. 查询与计算机系(CS)系所有学生的年龄均不同的学生学号, 姓名和年龄:select sno,sname,sage from student where sageall(select sage from student where sdept=CS)四. 使用带Exists谓词的子查询和相关子查询8. 查询与其他所有学生年龄均不同的学生学号, 姓名和年龄:select sno,sname,sage from student A where not exists(

30、select * from student B where A.sage=B.sage and A.snoB.sno) 9. 查询所有选修了1号课程的学生姓名:select sname from student where exists(select * from sc where sno=student.sno and cno=1)10. 查询没有选修了1号课程的学生姓名:select sname from student where not exists(select * from sc where sno=student.sno and cno=1)11. 查询选修了全部课程的学生姓名:

31、select sname from student where not exists(select * from course where not exists( select * from sc where sno=student.sno and cno=o)11. 查询至少选修了学生95002选修的全部课程的学生的学号:select distinct sno from sc A where not exists (select * from sc B where sno=95002and not exists(select * from sc C where sno=A.sno and c

32、no=B.cno)12. 求没有人选修的课程号cno和cnamecname:select cno,cname from course C where not exists(select * from sc where o=C.cno )13*. 查询满足条件的(sno,cno)对, 其中该学号的学生没有选修该课程号cno的课程select sno,cno from student,course where not exists(select * from sc where cno=o and sno=student.sno)14*. 查询每个学生的课程成绩最高的成绩信息(sno,cno,gra

33、de):select * from sc A where grade=(select max(grade) from sc where sno=A.sno ) 思考: 如何查询所有学生都选修了的课程的课程号cno?实验五：数据库的分组查询和统计查询实验目的：熟练掌握数据查询中的分组、统计、计算和集合的操作方法。实验内容：使用聚集函数查询、分组计算查询、集合查询。实验步骤：一. 使用聚集函数：1 查询学生总人数：Select Count(*) as 学生总数 from student2. 查询选修了课程的学生总数：select count(distinct sno) as 选课学生总数 from

34、 sc3. 查询所有课程的总学分数和平均学分数,以及最高学分和最低学分：select sum(credit) as 总credit,avg(credit) as 课程平均学分,max(credit) as 最高学分,min(credit) as 最低学分 from course4. 计算1号课程的学生的平均成绩, 最高分和最低分:select avg(grade) as 平均成绩,max(grade) as 最高分, min(grade) as 最低分from scwhere cno=15. 查询信息系(IS)学生”数据结构”课程的平均成绩:select avg(grade) from stu

35、dent, course, sc where student.sno=sc.sno and o=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

36、 cno=A.cno ) 二. 分组查询8. 查询各系的学生的人数并按人数从多到少排序 :selectsdept, Count(*) as 人数 from student group by sdept order by 人数 desc9. 查询各系的男女生学生总数, 并按系别,升序排列, 女生排在前:select sdept,ssex,Count(*) as 人数 from student group by sdept, ssex order by sdept,ssex desc 10. 查询选修了3门课程已上的学生的学号和姓名:select sno, sname from student w

37、here sno in (select sno from sc group by (sno) having count(*)3) 11. 查询每个学生所选课程的平均成绩, 最高分, 最低分,和选课门数:select sno, avg(grade) as 平均成绩,max(grade) as 最高分, min(grade) as 最低分, count(*) as 选课门数 from sc group by sno12. 查询至少选修了2门课程的学生的平均成绩:select sno, avg(grade) as 平均成绩, from sc group by sno having count(*)=

38、213. 查询平均分超过80分的学生的学号和平均分:Select sno, avg(grade) as 平均成绩from sc group by sno having avg(*)=80比较: 求各学生的60分以上课程的平均分:select sno, avg(grade) as 平均成绩 from sc where grade=60 group by sno14. 查询”信息系”(IS)中选修了5门课程以上的学生的学号:select sno from sc where sno in (select sno from student where sdept=IS) group by sno ha

39、ving count(*)=2三. 集合查询15. 查询数学系和信息系的学生的信息;select * from student where sdept=MA union select * from student where sdept=IS16. 查询选修了1号课程或2号课程的学生的学号:select sno from sc where cno=1 Union select sno from sc where cno=2比较实验三之3.思考:1. 用两种方法查询平均成绩少于70分的学生的学号。Select sno from sc group by sno having avg(grade)8

40、0Select distinct sno from sc a where (select avg(grade) from sc where sno=a.sno)=904. 创建一个反映学生出生年份的视图: create view BT_S(sno, sname, 出生年份) as select sno, sname, year(date()- sage from student5. 将所有女生的记录定义为一个视图: create view F_student as select * from student where ssex=女6. 将所有学生的学号和他的平均成绩定义为一个视图: create view S_G(sno, avg_grade) as select sno,avg(grade)