1、数据库原理实验报告学号: 姓名:luya 班级: 指导教师： 中国矿业大学计算机科学与技术学院2011 年 5 月数据库原理教师成绩评定表评定成绩的依据： 基础理论及基本技能的掌握 独立解决实际问题的能力； 研究内容的理论依据和技术方法； 取得的主要成果 工作态度及工作量； 成绩采用优良中差四个等级评定成绩表实验序号 实验成绩 指导教师 日期实验一实验二实验一： SQL 数据定义功能一、实验内容及要求1. 使用 SQL 语句建立学生管理系统相关的表，同时完善各表的相关完整性约束。其中带有下划线的为主码学院（学院代码，学院名称） 学生（学号，姓名，性别，学院代码） ，学院代码为外码教师（教师号，
2、教师姓名，学院代码） ，学院代码为外码课程（课程号，课程名，学时）学习（学号，课程号，成绩） ，学号为外码，课程号为外码开课（教师号，课程号） ，教师号为外码，课程号为外码2. 对各表进行增加、删除、修改属性操作添加操作：对学生表添加出生日期字段，和家庭地址字段，教师表增加性别字段，出生日期字段，对课程表增加先修课程字段，并且先修课程为本关系外码。删除操作：删除学生表家庭地址字段，删除教师表出生日期字段修改操作：修改先课程表中先修课程字段名称为先修课程号，修改学生姓名字段，该字段值不允许取空值3. 建立索引为学生表在学生姓名上建立名为 sname-index 的索引，在学院代码字段上建立名为
3、dept-index，降序。4. 删除表操作删除上述表的定义，并重新执行定义表的查询再次建立各表5. 利用 ACCESS 2003 /2007 完善各表的数据内容二、实验目的熟练掌握 SQL 的各种数据定义功能，包括1.定义表的功能，包括主码和外码的定义2.修改表的定义功能，包括增加属性，删除属性，修改属性类型4.建立和删除索引操作，理解索引的作用5.删除表功能三、实验步骤及运行结果1. 使用 SQL 语句建立学生管理系统相关的表（1）创建学院表实验步骤：查询在设计视图中创建查询 选择 SQL 视图，输入如下 SQL 语句：CREATE TABLE 学院(学院代码 CHAR(8) ,学院名称
4、CHAR(15),PRIMARY KEY (学院代码);运行结果：（2）创建学生表实验步骤：查询在设计视图中创建查询 选择 SQL 视图，输入如下 SQL 语句：CREATE TABLE 学生( 学号 CHAR(8) ,姓名 CHAR(4),性别 CHAR(1),学院代码 CHAR(8)REFERENCES 学院(学院代码),PRIMARY KEY (学号);运行结果：（3）创建教师表实验步骤：查询在设计视图中创建查询 选择 SQL 视图，输入如下 SQL 语句：CREATE TABLE 教师( 教师号 CHAR(8) ,教师姓名 CHAR(4),学院代码 CHAR(8) REFERENCES
5、 学院(学院代码),PRIMARY KEY (教师号);运行结果：（4）创建课程表实验步骤：查询在设计视图中创建查询 选择 SQL 视图，输入如下SQL 语句：CREATE TABLE 课程( 课程号 CHAR(4) ,课程名 CHAR(15),学时 INT,PRIMARY KEY (课程号);运行结果：（5）创建学习表实验步骤：查询在设计视图中创建查询 选择 SQL 视图，输入如下SQL 语句：CREATE TABLE 学习( 学号 CHAR(8),课程号 CHAR(4),成绩 SMALLINT,PRIMARY KEY (学号,课程号),FOREIGN KEY (学号) REFERENCES
6、 学生(学号),FOREIGN KEY (课程号) REFERENCES 课程(课程号);运行结果：（6）创建开课表实验步骤：查询在设计视图中创建查询 选择 SQL 视图，输入如下SQL 语句：CREATE TABLE 开课( 教师号 CHAR(8) ,课程号 CHAR(4),PRIMARY KEY (教师号,课程号),FOREIGN KEY (教师号) REFERENCES 教师(教师号),FOREIGN KEY (课程号) REFERENCES 课程(课程号);运行结果：2. 对各表进行增加、删除、修改属性操作添加操作：对学生表添加出生日期字段，和家庭地址字段：实验步骤：查询在设计视图中创
7、建查询 选择 SQL 视图，分别输入如下SQL 语句：ALTER TABLE 学生 ADD COLUMN 出生日期 DATE;ALTER TABLE 学生 ADD COLUMN 家庭地址 CHAR(20);运行结果：教师表增加性别字段，出生日期字段：实验步骤：查询在设计视图中创建查询 选择 SQL 视图，分别输入如下SQL 语句：ALTER TABLE 教师 ADD COLUMN 性别 CHAR(1);ALTER TABLE 教师 ADD COLUMN 出生日期 DATE;运行结果：对课程表增加先修课程字段：实验步骤：查询在设计视图中创建查询 选择 SQL 视图，输入如下 SQL 语句：ALT
8、ER TABLE 课程 ADD COLUMN 先修课程 CHAR(15);运行结果：删除操作：删除学生表家庭地址字段：实验步骤：查询在设计视图中创建查询 选择 SQL 视图，输入如下 SQL 语句：ALTER TABLE 学生 DROP COLUMN 家庭地址;运行结果：删除教师表出生日期字段：实验步骤：查询在设计视图中创建查询 选择 SQL 视图，输入如下 SQL 语句：ALTER TABLE 教师 DROP COLUMN 出生日期;运行结果：修改操作：修改课程表中先修课程字段名称为先修课程号：实验步骤：查询在设计视图中创建查询 选择 SQL 视图，分别输入如下SQL 语句：ALTER TA
9、BLE 课程 DROP COLUMN 先修课程;ALTER TABLE 课程 ADD COLUMN 先修课程号 CHAR(4);运行结果：修改学生姓名字段，该字段值不允许取空值：实验步骤：查询在设计视图中创建查询 选择 SQL 视图，分别输入如下SQL 语句：ALTER TABLE 学生 ALTER 姓名 CHAR(4) NOT NULL;运行结果：3. 建立索引为学生表在姓名上建立名为 sname-index 的索引在学院代码字段上建立名为 dept-index 的索引，降序。实验步骤：查询在设计视图中创建查询 选择 SQL 视图，分别输入如下SQL 语句：CREATE INDEX SNAM
10、E_INDEX ON 学生( 姓名);CREATE INDEX DEPT_INDEX ON 学生(学院代码 DESC);运行结果：4. 删除表操作实验步骤：查询在设计视图中创建查询 选择 SQL 视图，分别输入如下SQL 语句：DROP TABLE 教师 ;DROP TABLE 开课 ;DROP TABLE 课程 ;DROP TABLE 学生 ;DROP TABLE 学习 ;DROP TABLE 学院 ;运行结果：各表被删除。四、实验体会通过本次实验我了解了 SQL 数据定义功能包括使用 SQL 语句建立各表以及主码和外码的定义等，掌握了 SQL 的各种数据定义功能，掌握了对各表进行增加、删除
11、、修改属性等操作以及对表进行建立和删除删除表等功能。但是，还不是很熟练，还得多家记忆。实验二：练习 SQL 数据操纵功能一、实验内容及要求1求选修了某个老师上课的学生，成绩在 90 分以上的姓名、课程名称和成绩； 2求选修了某个老师所授全部课程的学生姓名和学院名称； 3求没有选修某门课程的学生学号和姓名； 4求至少选修了两门课程的学生学号； 5求某门课程不及格学生姓名和考试成绩 6求至少选修了与某个同学选修的课程相同的学生学号 7求至少选修了某两门课程的学生姓名和学院名称。 8查询某个同学没有选修的课程号和课程名 9查询没有被任何学生选修的课程的课程号 10求选修了全部课程的学生姓名 11查询
12、各学院某门课程的平均分，并按照成绩从高到低的顺序排列。12查询选修数据库原理课程的学生姓名和所在院系，结果按各院系排列，同时成绩从高到低排列。 13求学时在 30-45 之间（含 30 和 45）的课程的课程号和课程名称及授课教师。 14检索选修数据库原理课程的最高分学生的姓名 15查询选课人数超过 50（根据情况自己定人数）人的课程的课程号及课程名。16自行提出查询要求，内容包括并运算、差运算、交运算、笛卡儿积运算、连接运算、自然连接运算以及复杂统计表。二、实验目的使用 SQL 语句进行各种查询1、熟练掌握单表查询，包括 (1) 选择表中的若干列（投影） (2) 选择表中的若干元组（选择）
13、(3) 对查询结果排序 (4) 使用集函数 (5) 对查询结果分组2、熟练掌握复杂查询，包括 1) 等值与非等值连接查询 2) 自身连接:一个表与其自己进行连接。3) 外连接查询 4) 复合条件连接：WHERE 子句中可以有多个连接条件。3、熟练掌握嵌套查询，包括 1) 带有 IN 谓词的子查询 2) 带有比较运算符的子查询（子查询一定要跟在比较符之后）3) 带有 ANY 或ALL 谓词的子查询（使用 ANY 和 ALL 时必须同时使用比较运算符） 4) 带有 EXISTS 谓词的子查询（查询结果不返回任何数据，只产生逻辑“真”或“假” ）4、使用 SQL 和 ACCESS 结合完成一些复杂的
14、统计三、实验步骤及运行结果1求选修了某个老师上课的学生，成绩在 90 分以上的姓名、课程名称和成绩实验步骤：查询在设计视图中创建查询 选择 SQL 视图，输入如下 SQL 语句：SELECT 学生.姓名, 学习.成绩, 课程. 课程号FROM 学生 , 学习, 课程, 教师, 开课WHERE (学习.成绩)90) AND (学生.学号)= 学习.学号) AND (学习.课程号)=课程. 课程号 And (学习.课程号)=开课.课程号) AND (教师.教师号)=开课.教师号 ) AND (教师.教师姓名 )=李梦);运行结果：2求选修了某个老师所授全部课程的学生姓名和学院名称实验步骤：查询在设
15、计视图中创建查询 选择 SQL 视图，输入如下 SQL 语句：SELECT 学生.姓名, 学院.学院名称FROM 学生 , 教师, 课程, 开课, 学院, 学习WHERE 教师 .教师姓名=李梦and 学院.学院代码 =学生 .学院代码and 教师.学院代码 =学生 .学院代码and 学生.学号 =学习.学号and 教师.教师号 =开课 .教师号and 课程.课程号 =学习 .课程号and 课程.课程号 =开课 .课程号;运行结果：3求没有选修某门课程的学生学号和姓名实验步骤：查询在设计视图中创建查询 选择 SQL 视图，输入如下 SQL 语句：SELECT 学号, 学生. 姓名FROM 学生
16、WHERE not exists(select *from 学习where 学生 .学号=学习.学号and 学习.课程号 =经济学);运行结果：4求至少选修了两门课程的学生学号实验步骤：查询在设计视图中创建查询 选择 SQL 视图，输入如下 SQL 语句：SELECT 学生.学号FROM 学生 , 学习WHERE 学生 .学号=学习 .学号GROUP BY 学生.学号HAVING count(课程号)1;运行结果：5求某门课程不及格学生姓名和考试成绩实验步骤：查询在设计视图中创建查询 选择 SQL 视图，输入如下 SQL 语句：SELECT 学生.姓名, 学习.成绩FROM 学生 , 学习,
17、课程WHERE 学习 .成绩 在设计视图中创建查询 选择 SQL 视图，输入如下 SQL 语句：SELECT DISTINCT 学号FROM 学习 AS aWHERE not exists(select *from 学习 as bwhere b.学号=T01and not exists(select *from 学习 as cwhere a.学号=c.学号and b.课程号=c.课程号);运行结果：7求至少选修了某两门课程的学生姓名和学院名称实验步骤：查询在设计视图中创建查询 选择 SQL 视图，输入如下 SQL 语句：SELECT 姓名, 学院名称FROM 学生 , 学习, 学院WHERE
18、学生 .学院代码= 学院. 学院代码AND 学习. 学号=学生.学号AND 课程号=002AND 学生. 学号 IN(SELECT 学号FROM 学习WHERE 课程号 =005);运行结果：8查询某个同学没有选修的课程号和课程名实验步骤：查询在设计视图中创建查询 选择 SQL 视图，输入如下 SQL 语句：SELECT 课程名, 课程号FROM 课程WHERE 课程号 NOT IN(SELECT 课程号FROM 学习 ,学生WHERE 学生 .学号=学习 .学号AND 姓名=王莹);运行结果：9查询没有被任何学生选修的课程的课程号实验步骤：查询在设计视图中创建查询 选择 SQL 视图，输入如
19、下 SQL 语句：SELECT 课程号FROM 课程WHERE 课程号 NOT IN(SELECT 课程号FROM 学习 );运行结果：10求选修了全部课程的学生姓名实验步骤：查询在设计视图中创建查询 选择 SQL 视图，输入如下 SQL 语句：SELECT 姓名FROM 学生 , 学习WHERE 学生 .学号=学习 .学号GROUP BY 姓名HAVING COUNT(课程号)=(SELECT COUNT (*)FROM 课程 );运行结果：11查询各学院某门课程的平均分，并按照成绩从高到低的顺序排列实验步骤：查询在设计视图中创建查询 选择 SQL 视图，输入如下 SQL 语句：SELECT
20、 课程名, AVG(成绩) AS 平均分FROM 学习 , 课程WHERE 课程 .课程号= 学习. 课程号GROUP BY 课程名ORDER BY AVG(成绩) DESC;运行结果：12查询选修数据库原理课程的学生姓名和所在院系，结果按各院系排列，同时成绩从高到低排列实验步骤：查询在设计视图中创建查询 选择 SQL 视图，输入如下 SQL 语句：SELECT 姓名, 学院名称 , 成绩FROM 学生 , 学习, 学院, 课程WHERE 学生 .学号=学习 .学号AND 学生. 学院代码=学院. 学院代码AND 课程. 课程号=学习. 课程号AND 课程名=数据库原理及应用ORDER BY 学院名称, 成绩 DESC;运行结果：
