数据库原理实验报告1

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

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

3、。4. 删除表操作删除上述表，重新执行查询再次建立各表5. 利用access 2003 /2007完善各表的数据内容二、实验目的熟练掌握sql的各种数据定义功能，包括1.定义表的功能，包括主码和外码的定义2.修改表的定义功能，包括增加属性，删除属性，修改属性类型4.建立和删除索引操作，理解索引的作用5.删除表功能三、实验步骤及运行结果1）创建各表学院表的创建sql语句：create table 学院如图( 学院代码 char(10) not null unique, 学院名称 char(10),primary key(学院代码);学生表创建的sql语句：create table 学生如图( 学

4、号 char(8) not null unique, 姓名 char(8), 性别 char(2), 学院代码 char(10),primary key(学号),foreign key(学院代码) references 学院(学院代码);教师表创建sql语句：create table 教师如图( 教师号 char(8) not null unique,教师姓名 char(8),学院代码 char(10),primary key (教师号),foreign key (学院代码) references 学院(学院代码);课程表创建sql语句：create table 课程如图的( 课程号 char

5、(8) primary key not null unique,课程名 char(8),学时 smallint);学习表创建sql语句：create table 学习如图( 学号 char(8),课程号 char(8),成绩 smallint,primary key (学号,课程号),foreign key(学号) references 学生(学号),foreign key(课程号) references 课程(课程号);开课表创建sql语句：create table 开课如图( 教师号 char(8), 课程号 char(8),primary key (教师号,课程号),foreign ke

6、y (教师号) references 教师(教师号),foreign key (课程号) references 课程(课程号);实验操作图如下：图图图图图图）对各表进行增加，删除，修改属性的操作学生表添加出生日期字段，和家庭地址字段；教师表增加性别字段，出生日期字段；课程表增加先修课程字段；sql语句如下：（）alter table 学生 add 家庭住址 char(20);alter table 学生 add 出生日期 date;alter table 教师 add 性别 char(2);alter table 教师 add 出生日期 date;alter table 课程 add 先修课程

7、 char(8);语句执行之后如图：（学生表添加操作图）（教师表添加操作图）（课程表添加操作图）（）删除学生表家庭地址字段，删除教师表出生日期字段，sql语句如下：alter table 教师 drop column 出生日期;alter table 学生 drop column 家庭住址;（教师表删除属性之后）（学生表删除属性之后）为学生表在学生姓名上建立名为sname-index 的索引，在学院代码字段上建立名为dept-index，降序。sql语句如下：create unique index dept_index on 学院 (学院代码 desc);create unique index

8、 sname_index on 学生 (姓名);）删除表操作sql语句如下：drop table 开课 cascad;drop table 学习 cascad；drop table 课程 cascad;drop table 教师 cascad;drop table 学生 cascad;drop table 学院 cascad;5) 利用access 2003 /2007完善各表的数据内容,如下图;四、实验体会 已删除，嘿嘿实验二：练习sql数据操纵功能一、实验内容及要求1求选修了某个老师上课的学生，成绩在90分以上的姓名、课程名称和成绩； 2求选修了某个老师所授全部课程的学生姓名和学院名称；

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

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

11、询2) 带有比较运算符的子查询（子查询一定要跟在比较符之后）3) 带有any或all谓词的子查询（使用any和all时必须同时使用比较运算符）4) 带有exists谓词的子查询（查询结果不返回任何数据，只产生逻辑“真”或“假”）4、使用sql和access结合完成一些复杂的统计三、实验步骤及运行结果1求选修秦始皇老师的课的学生，成绩在90分以上的姓名、课程名称和成绩； sql语句如下：select 姓名, 课程名, 成绩from 学生, 课程, 学习, 开课, 教师where 成绩>90 and 学生.学号=学习.学号 and 开课.教师号=教师.教师号 and 学习.课程号=课程.课程

12、号 and 教师.教师姓名='秦始皇'结果如图2求选修皇太极老师所授全部课程的学生姓名和学院名称； sql语句如下；select 姓名, 学院名称from 学生, 学院where 学号 in( select 学号from 学习where 课程号 in(select 课程号from 开课where 教师号 in(select 教师号from 教师where 教师姓名 = '皇太极')and 学生.学院代码 = 学院.学院代码;结果如图：3求没有选修英语的学生学号和姓名；sql语句如下： select 学生.学号, 姓名from 学生, 学习, 课程where 课程

13、.课程名='英语' and 学生.学号=学习.学号 and 学习.课程号=课程.课程号;结果如图：4求至少选修了两门课程的学生学号；sql语句如下： select 学号from 学习group by 学号having count(课程号)>=2;结果如图：5求英语课程不及格学生姓名和考试成绩 sql语句如下select 姓名, 成绩from 学生, 学习, 课程where 成绩<60 and 学生.学号=学习.学号 and 学习.课程号=课程.课程号 and 课程名='英语' 结果如图：6求至少选修了与舒畅同学选修的课程相同的学生学号 sql语句如下

14、；select学号from 学习where 课程号 in(select 课程号from 学习where 学号 in(select 学号from 学生where 姓名 = '舒畅');结果如图：7求至少选修了某两门课程的学生姓名和学院名称。 sql语句如下；select 姓名, 学院名称from 学生, 学院, 学习, 课程where课程名 in ('英语','信息技术') and 学生.学院代码=学院.学院代码 and 学生.学号=学习.学号 and 学习.课程号=课程.课程号;结果如图；8查询冷漠同学没有选修的课程号和课程名；sql语句如下：s

15、elect 课程号, 课程名from 课程where 课程号 in(select 课程号from 学习where 学号 not in(select 学号from 学生where 姓名 = '冷漠'); 结果如图：9查询没有被任何学生选修的课程的课程号 sql语句如下：select 课程号from 课程where 课程号 not in (select 课程号from 学习); 结果如图：10求选修了全部课程的学生姓名 ；sql语句如下：select 姓名from 学生where not exists(select * from 课程where not exists(select

16、*from 学习where 学号 = 学生.学号and 课程号 = 课程.课程名); 结果如图：11查询各学院某门课程的平均分，并按照成绩从高到低的顺序排列。sql语句如下：select学院名称, avg(成绩) as 平均成绩from 学院, 学习, 课程, 学生where 课程名='网络技术' and 学习.课程号=课程.课程号 and 学习.学号=学生.学号 and 学生.学院代码=学院.学院代码group by 学院名称order by avg(成绩) desc;结果如图：12查询选修数据库原理课程的学生姓名和所在院系，结果按各院系排列，同时成绩从高到低排列。 sele

17、ct 姓名, 学院名称, 成绩from 学生, 学院, 学习, 课程where 课程名='数据库原理' and 课程.课程号=学习.课程号and 学习.学号=学生.学号and 学生.学院代码=学院.学院代码order by 学院名称, 成绩 desc; 结果如图：13求学时在30-45之间（含30和45）的课程的课程号和课程名称及授课教师。 sql语句如下；select 课程.课程号, 课程名, 教师姓名from 课程, 教师, 开课where 学时 between 30 and 45and 课程.课程号 = 开课.课程号and 开课.教师号 = 教师.教师号; 结果如图;14检索选修数据库原理课程的最高分学生的姓名 sql语句如下：select 姓名fr