数据库原理实验模板.docx

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

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

3、x 的索引，在学院代码字段上建立名为dept-index，降序。4. 删除表操作删除上述表的定义，并重新执行定义表的查询再次建立各表5. 利用ACCESS 2003 /2007完善各表的数据内容二、实验目的熟练掌握SQL的各种数据定义功能，包括1.定义表的功能，包括主码和外码的定义2.修改表的定义功能，包括增加属性，删除属性，修改属性类型4.建立和删除索引操作，理解索引的作用5.删除表功能三、实验步骤及运行结果1. 使用SQL语句建立学生管理系统相关的表，同时完善各表的相关完整性约束。其中带有下划线的为主码教师（教师号，教师姓名，学院代码），学院代码为外码CREATE TABLE 教师(教师号

4、 CHAR(8),教师姓名 CHAR(8),学院代码 CHAR(8),PRIMARY KEY (教师号);开课（教师号，课程号），教师号为外码，课程号为外码CREATE TABLE 开课(教师号 CHAR(8),课程号 CHAR(8),PRIMARY KEY (教师号,课程号);课程（课程号，课程名，学时）CREATE TABLE 课程(课程号 CHAR(8),课程名 CHAR(8),学时 SMALLINT,PRIMARY KEY (课程号);学生（学号，姓名，性别，学院代码） ，学院代码为外码CREATE TABLE 学生(学号 CHAR(8),姓名 CHAR(8),性别 CHAR(8),学

5、院代码 CHAR(8),PRIMARY KEY (学号);学习（学号，课程号，成绩），学号为外码，课程号为外码CREATE TABLE 学习(学号 CHAR(8),课程号 CHAR(8),成绩 SMALLINT,PRIMARY KEY (学号,课程号);学院（学院代码，学院名称）CREATE TABLE 学院(学院代码 CHAR(8),学院名称 CHAR(8),PRIMARY KEY (学院代码);2. 对各表进行增加、删除、修改属性操作添加操作：ALTER TABLE 学生 ADD 出生日期 CHAR(10);ALTER TABLE 学生 ADD 家庭住址 CHAR(10);ALTER TA

6、BLE 教师 ADD 性别 CHAR(10);ALTER TABLE 教师 ADD 出生日期 CHAR(10);ALTER TABLE 课程 ADD 先行课程 CHAR(8);删除操作： 删除学生表家庭地址字段，删除教师表出生日期字段ALTER TABLE 学生 DROP 家庭住址;ALTER TABLE 教师 DROP 出生日期;修改操作：修改先课程表中先修课程字段名称为先修课程号，修改学生姓名字段，该字段值不允许取空值ALTER TABLE 课程 DROP 先行课程;ALTER TABLE 课程 ADD 先行课程号 CHAR(8);3. 建立索引为学生表在学生姓名上建立名为sname-in

7、dex 的索引，在学院代码字段上建立名为dept-index，降序。CREATE UNIQUE INDEX xname_index ON 学生(姓名); CREATE UNIQUE INDEX dept_index ON 学院(学院代码 DESC);4. 删除表操作删除上述表的定义，并重新执行定义表的查询再次建立各表5. 利用ACCESS 2003 /2007完善各表的数据内容四、实验体会 第一次建表时，由于对ACCESS 2003的应用不熟悉，比较困难，最终在课本的帮助下在下课之前做完了实验。但是第一次做实验，没经验，源代码没有保存下来，因为写实验报告需要源代码，不得不在课后用自己的电脑重新

8、做了一遍。整体感觉不错，比较简单。实验二：练习SQL数据操纵功能一、实验内容及要求1求选修了某个老师上课的学生，成绩在90分以上的姓名、课程名称和成绩； 2求选修了某个老师所授全部课程的学生姓名和学院名称； 3求没有选修某门课程的学生学号和姓名； 4求至少选修了两门课程的学生学号； 5求某门课程不及格学生姓名和考试成绩 6求至少选修了与某个同学选修的课程相同的学生学号 7求至少选修了某两门课程的学生姓名和学院名称。 8查询某个同学没有选修的课程号和课程名 9查询没有被任何学生选修的课程的课程号 10求选修了全部课程的学生姓名 11查询各学院某门课程的平均分，并按照成绩从高到低的顺序排列。12查

9、询选修数据库原理课程的学生姓名和所在院系，结果按各院系排列，同时成绩从高到低排列。 13求学时在30-45之间（含30和45）的课程的课程号和课程名称及授课教师。 14检索选修数据库原理课程的最高分学生的姓名 15查询选课人数超过50（根据情况自己定人数）人的课程的课程号及课程名。16自行提出查询要求，内容包括并运算、差运算、交运算、笛卡儿积运算、连接运算、自然连接运算以及复杂统计表。二、实验目的使用SQL语句进行各种查询1、熟练掌握单表查询，包括 (1) 选择表中的若干列（投影） (2) 选择表中的若干元组（选择） (3) 对查询结果排序 (4) 使用集函数 (5) 对查询结果分组2、熟练掌

10、握复杂查询，包括1) 等值与非等值连接查询 2) 自身连接:一个表与其自己进行连接。3) 外连接查询4) 复合条件连接：WHERE 子句中可以有多个连接条件。3、熟练掌握嵌套查询，包括1) 带有IN谓词的子查询2) 带有比较运算符的子查询（子查询一定要跟在比较符之后）3) 带有ANY或ALL谓词的子查询（使用ANY和ALL时必须同时使用比较运算符）4) 带有EXISTS谓词的子查询（查询结果不返回任何数据，只产生逻辑“真”或“假”）4、使用SQL和ACCESS结合完成一些复杂的统计三、实验步骤及运行结果1求选修了老师“王刚”开课课程且成绩在90分以上的学生姓名、课程名称和成绩；（姓名，课程名，

11、成绩） SELECT 姓名, 课程名, 成绩FROM 学生, 学习, 课程WHERE 学生.学号=学习.学号AND 学习.课程号=课程.课程号AND 学习.成绩=90;2求选修了“王刚”老师所授全部课程的学生姓名和学院名称；（姓名，学院名称） SELECT DISTINCT 学生.姓名, 学院.学院名称FROM 学生, 学院WHERE 学生.学院代码=学院.学院代码 AND NOT EXISTS (SELECT *FROM 教师,开课WHERE 教师.教师姓名=王刚 AND 教师.教师号=开课.教师号AND NOT EXISTS (SELECT *FROM 学习WHERE 学生.学号=学习.学

12、号AND 开课.课程号=学习.课程号);3求没有选修课程“软件工程”的学生学号和姓名；（学生学号，姓名） SELECT DISTINCT 学号, 姓名FROM 学生WHERE NOT EXISTS(SELECT *FROM 课程WHERE 课程名=软件工程AND EXISTS(SELECT*FROM 学习WHERE 学习.学号=学生.学号AND 学习.课程号=课程.课程号);4求至少选修了两门课程的学生学号；（学号） SELECT 学号FROM 学生WHERE 学号 IN(SELECT 学号FROM 学习GROUP BY 学号HAVING COUNT(学号)=2);5求课程“经济学”不及格学生

13、姓名和考试成绩；（姓名，成绩） SELECT 姓名, 成绩FROM 学生, 学习, 课程WHERE 学生.学号=学习.学号AND 学习.课程号=课程.课程号AND 课程.课程名=经济学AND 学习.成绩60;6求至少选修了与学号“T06” 同学选修的课程相同的学生学号；（学号） SELECT A.学号FROM 学习 AS A, 学习 AS BWHERE A.课程号=B.课程号AND B.学号=T06AND A.学号T06GROUP BY A.学号HAVING COUNT (A.课程号)=(SELECT COUNT (课程号)FROM 学习WHERE 学号=T06);7求至少选修了“C3，C4”

14、两门课程的学生姓名和学院名称；（姓名，学院名称） SELECT 姓名, 学院名称FROM 学生, 学院WHERE 学生.学院代码=学院.学院代码AND 学号 IN(SELECT 学号FROM 学习GROUP BY 学号HAVING COUNT(课程号)=2);8查询“王石”同学没有选修的课程号和课程名； （课程号，课程名）SELECT 课程号, 课程名FROM 课程WHERE 课程号 NOT IN(SELECT 课程号FROM 学习WHERE 学号 IN(SELECT 学号FROM 学生WHERE 姓名=王石);9查询没有被任何学生选修的课程的课程号；（课程号）SELECT 课程号FROM 课

15、程WHERE NOT EXISTS(SELECT *FROM 学习);10求选修了全部课程的学生姓名；（姓名）SELECT 姓名FROM 学生WHERE NOT EXISTS(SELECT *FROM 课程WHERE NOT EXISTS(SELECT *FROM 学习WHERE 学生.学号=学习.学号AND 学习.课程号=课程.课程号);11查询各学院课程“经济学”的平均分，并按照成绩从高到低的顺序排列；（学院名称，平均分）SELECT 学院名称, AVG(成绩) AS 平均成绩FROM 学习, 学院, 学生, 课程WHERE 学习.学号=学生.学号 And 学院.学院代码=学生.学院代码

16、And 课程.课程号=学习.课程号And 课程.课程名=经济学GROUP BY 学院名称, 课程.课程名ORDER BY AVG(成绩) DESC;12查询选修课程“经济学”的学生姓名和所在院系，结果按各院系排列，同时成绩从高到低排列；（姓名，学院名称，成绩）SELECT 学生.姓名, 学院.学院名称, 学习.成绩FROM 学生, 学院, 学习, 课程WHERE (学生.学院代码)=学院.学院代码) AND (学生.学号)=学习.学号) AND (学习.课程号)=课程.课程号) AND (课程.课程名)=经济学)ORDER BY 学习.成绩 DESC;13求学时在30-45之间（含30和45）

17、的课程的课程号和课程名称及授课教师；（课程号，课程名，教师姓名）SELECT 课程.课程号, 课程.课程名, 教师.教师姓名FROM 教师, 课程, 开课WHERE (课程.课程号)=开课.课程号) AND (开课.教师号)=教师.教师号) AND (课程.学时) Between 30 And 45);14检索选修课程“经济学”的最高分学生的姓名；（姓名）SELECT 姓名FROM 学生, 学习, 课程WHERE 学生.学号=学习.学号 AND 学习.课程号=课程.课程号AND 课程.课程名=经济学AND 学习.成绩 =(SELECT MAX(成绩)FROM 学习,课程WHERE 学习.课程号

18、=课程.课程号AND 课程.课程名=经济学);15查询选课人数超过5人的课程的课程号及课程名；（课程号，课程名）SELECT 课程.课程号, 课程.课程名FROM 课程WHERE (课程.课程号) In (SELECT 课程号FROM 学习GROUP BY 课程号HAVING COUNT(课程号)5);四、实验体会 第二次做实验了，已经能熟练地应用ACCESS了，发现上课听懂和上机操作根本是两码事，总是有些小问题不能处理得很好。而且有个别几个题感觉很难，下课了都没能做完，感觉没能把所学的贯通起来。回到宿舍后，经过查资料，最终把所有的题都做出来了，很有成就感。觉得有些题只要自己认真分析，思考，不断的尝试，最终一定能做出来。收获很多。