数据库原理实验报告.docx

1、数据库原理实验报告数据库原理实验报告学号:08093556 姓名:张雪涛 班级: 信科09-2班 指导教师： 谢红侠 中国矿业大学计算机科学与技术学院 2011年 4 月数据库原理教师成绩评定表评定成绩的依据：基础理论及基本技能的掌握独立解决实际问题的能力；研究内容的理论依据和技术方法；取得的主要成果工作态度及工作量；成绩采用优良中差四个等级评定成绩表实验序号实验成绩指导教师日期实验一实验二实验一：SQL数据定义功能一、实验内容及要求1. 使用SQL语句建立学生管理系统相关的表，同时完善各表的相关完整性约束。其中带有下划线的为主码 学院（学院代码，学院名称） 学生（学号，姓名，性别，学院代码）

2、 ，学院代码为外码 教师（教师号，教师姓名，学院代码），学院代码为外码 课程（课程号，课程名，学时） 学习（学号，课程号，成绩），学号为外码，课程号为外码 开课（教师号，课程号），教师号为外码，课程号为外码2. 对各表进行增加、删除、修改属性操作添加操作：对学生表添加出生日期字段，和家庭地址字段，教师表增加性别字段，出生日期字段，对课程表增加先修课程字段，并且先修课程为本关系外码。删除操作： 删除学生表家庭地址字段，删除教师表出生日期字段修改操作： 修改先课程表中先修课程字段名称为先修课程号，修改学生姓名字段，该字段值不允许取空值3. 建立索引为学生表在学生姓名上建立名为sname-index

3、 的索引，在学院代码字段上建立名为dept-index，降序。4. 删除表操作删除上述表，重新执行查询再次建立各表5. 利用ACCESS 2003 /2007完善各表的数据内容二、实验目的熟练掌握SQL的各种数据定义功能，包括1.定义表的功能，包括主码和外码的定义2.修改表的定义功能，包括增加属性，删除属性，修改属性类型4.建立和删除索引操作，理解索引的作用5.删除表功能三、实验步骤及运行结果（一）1、建立学院表：CREATE TABLE 学院(学院代码 CHAR(8) PRIMARY KEY, 学院名称 CHAR(15);2、建立学生表：CREATE TABLE 学生(学号 CHAR(8)

4、PRIMARY KEY,姓名 CHAR(15),性别 CHAR(2),学院代码 CHAR(8),FOREIGN KEY(学院代码) REFERENCES 学院(学院代码);3、建立教师表：CREATE TABLE 教师(教师号 CHAR (8) PRIMARY KEY,教师姓名 CHAR(20),学院代码 CHAR(8),FOREIGN KEY(学院代码) REFERENCES 学院(学院代码);4、建立课程表：CREATE TABLE 课程(课程号 CHAR(8) PRIMARY KEY,课程名 CHAR(15),学时 SMALLINT);5、建立学习表：CREATE TABLE 学习(学号

5、 CHAR(8) ,课程号 CHAR(8),成绩 SMALLINT,PRIMARY KEY(学号,课程号),FOREIGN KEY(学号) REFERENCES 学生(学号),FOREIGN KEY(课程号) REFERENCES 课程(课程号);6、建立开课表：CREATE TABLE 开课(教师号 CHAR(8),课程号 CHAR(8),PRIMARY KEY(教师号,课程号),FOREIGN KEY(教师号) REFERENCES 教师(教师号),FOREIGN KEY(课程号) REFERENCES 课程(课程号);（二）1、开课表增加属性：ALTER TABLE 开课 ADD 开课时

6、间 DATETIME;2、学习表增加属性：ALTER TABLE 学习 ADD 任课教师号 CHAR(8)3、学生表增加属性：ALTER TABLE 学生 ADD 年龄 SMALLINT;4、学生表删除属性：ALTER TABLE 学生 DROP COLUMN 年龄;5、修改课程表属性：ALTER TABLE 课程 ALTER COLUMN 课程名 CHAR(20);（三）1、课程表建立索引：CREATE UNIQUE INDEX COU_IND_CNO ON 课程(课程号);2、学生表建立索引：CREATE UNIQUE INDEX STU_IND_SNO ON 学生(学号);3、学习表建立

7、索引：CREATE UNIQUE INDEX SC_IND_SNO_CNO ON 学习(学号,课程号);（四）1、建立临时表：CREATE TABLE 临时表(时间 DATETIME,姓名 CHAR(15),地点 CHAR(15),PRIMARY KEY(时间);2、删除临时表：DROP TABLE 临时表 CASCADE;（五）完善表内容之后，运行结果：学院表：学院学院代码学院名称1122 计算机学院 1123 化工学院 1124 文法学院 1125 外文学院 1126 体育学院 1127 信电学院 1128 环侧学院 学生表：学生学号姓名性别学院代码2100 孙策 男 1122 2101

8、赵子玉 男 1122 2102 高夏丽 女 1122 2103 孙晓峰 男 1123 2104 豆顺 男 1123 3100 张静静 女 1123 3101 武梦醒 女 1124 3102 禹贵 女 1124 3103 李明凯 男 1125 4100 杨柳 男 1125 4101 李阳 男 1126 4102 张恒 男 1126 4103 周浩 男 1127 4104 姚梦玲 女 1127 5100 王选 男 1128 5101 赵忠祥 男 1128 5102 陈天宇 男 1122 5103 吴浩天 男 1123 5104 陈圆圆 女 1124 教师表：教师教师号教师姓名学院代码0001 马辉

9、 11220002 邱理想 11230003 杜明新 11240004 罗慧芬 11250005 刘芳 11260006 蒋克福 11270007 鲍福萍 11280008 王敏 11280009 赵柯 1128课程表：课程课程号课程名学时5000 化学 485001 英语 555002 数据库 645003 数学 425004 物理 485005 生物 325006 体育 285007 美术 205008 音乐 24学习表：学习学号课程号成绩任课教师号2100 5000 960001 2100 5001 870002 2100 5002 920002 2100 5003 760004 210

10、0 5004 860005 2100 5005 780006 2100 5006 990007 2100 5007 562100 5008 642101 5003 850003 2102 5000 850001 2102 5003 550004 2102 5004 940004 2103 5000 900007 2103 5001 930001 2104 5005 620005 3100 5000 430001 3100 5001 820002 3101 5002 890003 3101 5004 950004 3101 5005 850006 3102 5002 440003 3103 50

11、02 900002 3103 5006 910007 4100 5001 900002 4100 5002 910003 4100 5003 970003 4101 5000 930007 4101 5003 960003 4102 5002 820002 4102 5003 960004 4103 5004 780005 4103 5005 830005 4104 5004 870004 4104 5006 920007 5100 5002 930003 5100 5003 870003 5101 5003 930004 5101 5005 900005 5101 5006 870006 5

12、102 5000 870007 5102 5001 550001 5103 5000 980001 5103 5001 880001 5103 5002 750002 5103 5003 950004 5103 5004 770005 5103 5005 960006 5103 5006 830006 5103 5007 515103 5008 74开课表：开课教师号课程号0001 5000 0001 5001 0002 5001 0002 5002 0003 5002 0003 5003 0004 5004 0005 5004 0005 5005 0006 5005 0006 5006 00

13、07 5000 0007 5007 0008 5007 0009 5008 四、实验体会在建立查询的时候，要明确各表之间的联系，要正确指明每个表的主码和外码，这样之后，关系表中才会正确显示各表之间的联系。另外，从这次实验中，我学到数据更新中“插入子查询结果”的好处，在修改查询编码后，我们可以用这个操作，来重新建表和保留原表中的有效记录（原表先重命名，在执行改后的查询，在编写一个插入子查询的编码，就可以向新表中插入原有数据了）。实验二：练习SQL数据操纵功能一、实验内容及要求1求选修了某个老师上课的学生，成绩在90分以上的姓名、课程名称和成绩； 2求选修了某个老师所授全部课程的学生姓名和学院名称

14、； 3求没有选修某门课程的学生学号和姓名； 4求至少选修了两门课程的学生学号； 5求某门课程不及格学生姓名和考试成绩 6求至少选修了与某个同学选修的课程相同的学生学号 7求至少选修了某两门课程的学生姓名和学院名称。 8查询某个同学没有选修的课程号和课程名 9查询没有被任何学生选修的课程的课程号 10求选修了全部课程的学生姓名 11查询各学院某门课程的平均分，并按照成绩从高到低的顺序排列。12查询选修数据库原理课程的学生姓名和所在院系，结果按各院系排列，同时成绩从高到低排列。 13求学时在30-45之间（含30和45）的课程的课程号和课程名称及授课教师。 14检索选修数据库原理课程的最高分学生的

15、姓名 15查询选课人数超过50（根据情况自己定人数）人的课程的课程号及课程名。16自行提出查询要求，内容包括并运算、差运算、交运算、笛卡儿积运算、连接运算、自然连接运算以及复杂统计表。二、实验目的使用SQL语句进行各种查询1、熟练掌握单表查询，包括 (1) 选择表中的若干列（投影） (2) 选择表中的若干元组（选择） (3) 对查询结果排序 (4) 使用集函数 (5) 对查询结果分组2、熟练掌握复杂查询，包括1) 等值与非等值连接查询 2) 自身连接:一个表与其自己进行连接。3) 外连接查询4) 复合条件连接：WHERE 子句中可以有多个连接条件。3、熟练掌握嵌套查询，包括1) 带有IN谓词的

16、子查询2) 带有比较运算符的子查询（子查询一定要跟在比较符之后）3) 带有ANY或ALL谓词的子查询（使用ANY和ALL时必须同时使用比较运算符）4) 带有EXISTS谓词的子查询（查询结果不返回任何数据，只产生逻辑“真”或“假”）4、使用SQL和ACCESS结合完成一些复杂的统计三、实验步骤及运行结果1、查询编码：SELECT 姓名, 课程.课程号, 成绩FROM 学生, 课程, 学习WHERE 成绩90 AND 学习.任课教师号=(select 教师号from 教师where 教师姓名=马辉) AND 学生.学号=学习.学号and 课程.课程号=学习.课程号;运行结果：查询1姓名课程号成绩

17、孙策 5000 96孙晓峰 5001 93吴浩天 5000 982、查询编码：SELECT 姓名, 学院名称FROM 学生, 学院WHERE 学生.学院代码=学院.学院代码and not exists(select *from 开课where 教师号=0001and not exists(select *from 学习where 学号=学生.学号and 课程号=开课.课程号);运行结果：查询2姓名学院名称孙策 计算机学院 孙晓峰 化工学院 张静静 化工学院 陈天宇 计算机学院 吴浩天 化工学院 3、查询编码：SELECT 学号, 姓名FROM 学生WHERE not exists(select

18、 *from 学习where 课程号=5000and 学生.学号=学习.学号);运行结果：查询3学号姓名2101 赵子玉 2104 豆顺 3101 武梦醒 3102 禹贵 3103 李明凯 4100 杨柳 4102 张恒 4103 周浩 4104 姚梦玲 5100 王选 5101 赵忠祥 5104 陈圆圆 4、查询编码：SELECT 学号FROM 学习GROUP BY 学号HAVING count(课程号)=2;运行结果：查询4学号2100 2102 2103 3100 3101 3103 4100 4101 4102 4103 4104 5100 5101 5102 5103 5、查询编码：

19、SELECT 学生.姓名, 学习.成绩FROM 学生, 学习WHERE (学生.学号)=学习.学号) And (学习.课程号)=5001) And (学习.成绩)=all(select 成绩from 学习,课程where 课程.课程号=学习.课程号and 课程.课程名=数据库 );运行结果：查询14姓名王选 15、查询编码：SELECT 课程号, 课程名FROM 课程WHERE 课程号 in(SELECT 课程.课程号FROM 学习, 课程WHERE 学习.课程号=课程.课程号GROUP BY 课程.课程号HAVING count(*)5);运行结果：查询15课程号课程名5000 化学 5001 英语 5002 数据库 5003 数学 5004 物理 5005 生物 四、实验体会从这次实验中，我认识到查询编码是需要下功夫掌握的，自己在编码时，在很多问题上要花很长时间思考。要熟练的掌握和运用各类查询语句，并灵活应用是要继续努力。