1、0宁波工程学院数据库理论与技术课程实验报告学院： 电子与信息工程学院 专业： 计算机科学与技术 年级： 实验时间： 组名： 组长： 组员姓名： 指导教师签字： 成绩： 实验三、SQL-数据查询语言一、实验目的和要求1、实验目的：深入理解连接查询和嵌套查询的语句格式、执行过程，掌握它们的使用方法；理解数据更新语言 INSERT、UPDATE、DELETE 语句的格式和作用，掌握它们的使用方法和应用范围；理解视图的概念，掌握其定义方法和应用方法。2、实验要求：在每次实验前，学生必须根据实验指导书设计出实验方案（程序和实验步骤） ；在实验过程中要求独立进行程序调试和排错，学会使用在线帮助来解决实验中
2、遇到的问题，同时运用理论的知识来分析问题和解决问题，记录下实验过程和结果。上机结束后写出实验报告，并对实验过程进行分析和总结。二、实验内容与步骤1、在 SQL SERVER 2005 上附加数据库 jxgl 和 ReadBook。2、在 jxgl 中完成下列查询jxgl 数据库中表及表中字段说明:Students(sno, sname,class,ssex,bday,bplace,mgrade,photo,sfzh,zxf)该表是学生基本信息表 其中：sno学号，sname姓名，class班级，ssex性别，bday出生日期，bplace 籍贯，mgrade入学成绩，photo照片，sfzh身
3、份证号码，zxf总学分Course(cno,cname,cpno,credit) 1该表是课程情况表 其中：cno课程号，cname课程名称，cpno 先修课程号，credit学分SC(term,sno,cno,grade,point) 该表是学生选课成绩表 其中：term学期，sno学号，cno课程号，grade成绩，point绩点TC(term,class,cno,tno,period)该表是教师上课情况表 其中：list序号，term学期； class班级；cno 课程号；tno教师编号； period学时Teachers (tno,tname,ps,wday,dept,pay,marr
4、y,resume)该表是教师基本情况表：其中 tno教师编号；tname教师姓名；ps职称； wday入校日期；dept系别；pay工资；marry婚否；resume简历（1）查询选修课程学生的学号、姓名、所选课程号、课程名称以及所取得的成绩（2）查询每一门课程及其先修课程，查询结果中显示课程号、课程名和先修课程名（3）查询和“刘涛” 在一个班级的学生的信息思考：该查询可用几种方法实现？（4）查询选修了计算机基础 课的学生的学号、姓名（分别用连接查询和嵌套查询的方法实现）（5）查询没有选修课程的学生的信息（6）查询其他班中比“03 计算应用 1”所有学生入学成绩高的学生的姓名和入学成绩（用两种
5、方法实现）（7）查询每个学生超过该门课程平均成绩的学号（8）查询至少选修了“0311102”学生所选全部课程的学生的学号（9）查询既选修了“计算机基础”又选修了“C 语言基础”的学生的学号3、在 jxgl 中完成下列数据更新（1）向 course 表添加一个新的元组。（2）将“0301102”学生所选的“0000002”课程的成绩改为 100 分，绩点改为 2（3）删除（1）中在 course 表中添加的记录（4）统计每门课程的平均成绩并将统计结果存入到 jxgl 数据库中思考：如何将 students 表中班级名 “03 计算应用 1”改为“03 计算机应用 1”(可调用函数完成)4、查询教
6、师的编号、姓名、上课的课程号、课程名称及班级。将该查询定义为一个视图。5、在第 4 题定义的视图中完成下列查询（1）查询“姚明” 所上课程和班级。（2）统计每一位教师上课的数量26、附加题，做了有加分利用数据库 ReadBook 完成下列查询及视图设计。 （在 SQL SERVER2005 上附加数据库 ReadBook）（1）设计一个查询，要求用连接和嵌套两种方式完成。（2）设计一个查询，要求用外连接的方式完成。（3）设计一个查询，要求用带 exists 或 not exists 相关子查询完成（4）设计一个集合查询（5）设计一个带复合条件连接的查询（6）设计一个视图，并在该视图中完成添加、
7、修改、删除数据的操作。（7）设计一个视图，并在该视图完成两个统计：a.每位读者以及他的借书数量，按借书数量作降序排列 b.每本书以及被借出的次数，按借出次数作降序排列。注意在实验报告中说明查询的目的和对应的语句。三、实验过程及数据记录步骤 2（1）查询选修课程学生的学号、姓名、所选课程号、课程名称以及所取得的成绩查询结果如下：（1）查询每一门课程及其先修课程，查询结果中显示课程号、课程名和先修课程名查询结果如下：3（3）查询和“刘涛” 在一个班级的学生的信息思考：该查询可用几种方法实现？1.嵌套连接 2.多表连接查询结果如下：1.嵌套连接2.多表连接4（4）查询选修了计算机基础 课的学生的学号
8、、姓名（分别用连接查询和嵌套查询的方法 实现）查询结果如下：1.连接查询 2.嵌套查询（5）查询没有选修课程的学生的信息查询结果如下：select * from Studentswhere sno not in(select sno from sc)5（6）查询其他班中比“03 计算应用 1”所有学生入学成绩高的学生的姓名和入学成绩（用两种方法实现）查询结果如下：1.方法一：使用 ALL 关键字 2.方法二：调用内置函数 MAX（）（7）查询每个学生超过该门课程平均成绩的学号查询结果如下：（8）查询至少选修了“0311102”学生所选全部课程的学生的学号查询结果如下：6（9）查询既选修了“计算
9、机基础”又选修了“C 语言基础”的学生的学号查询结果如下：步骤 3（1）向 course 表添加一个新的元组。查询结果如下：（2）将“0301102”学生所选的“0000002”课程的成绩改为 100 分，绩点改为 2查询结果如下： 7（3）删除（1）中在 course 表中添加的记录查询结果如下：（4）统计每门课程的平均成绩并将统计结果存入到 jxgl 数据库中查询结果如下：思考：如何将 students 表中班级名 “03 计算应用 1”改为“03 计算机应用 1”(可调用函数完成)查询结果如下：8步骤 4查询教师的编号、姓名、上课的课程号、课程名称及班级。将该查询定义为一个视图。查询结果
10、如下：create view teacher_c(tno,tname,cno,cname,class)as select TC.tno ,tname,TC.cno,cname,classfrom Teachers,TC,CourseWHERE Teachers.tno=TC.tno and TC.cno=Cogo步骤 5在第 4 题定义的视图中完成下列查询（1）查询“姚明” 所上课程和班级。查询结果如下：select cname,classfrom teacher_cwhere tname=姚明Go9（2）统计每一位教师上课的数量查询结果如下：select tno,tname,count(cn
11、o) as 上课数量from teacher_cgroup by tno,tname附加题（1）设计一个查询，要求用连接和嵌套两种方式完成。查询读了”三重门” 的读者的姓名1. 连接查询：select rnamefrom readers,L_R,BOOKSwhere readers.rno=L_R.rno and l_r.bookid=books.bookidand bookname=三重门2. 嵌套查询：select rname10from readers where rno in(select rno from l_r where bookid in(select bookid from
12、books where bookname=三重门 )两种查询相同，结果如下：（2）设计一个查询，要求用外连接的方式完成。查询借过书的人的信息select *from readers right outer join l_ron readers.rno=l_r.rno（3）设计一个查询，要求用带 exists 或 not exists 相关子查询完成查询王小丫借过的书名查询结果如下：（4）设计一个集合查询：查询性别为男性或者 spet=信管的学生信息查询结果如下：11（5）设计一个带复合条件连接的查询查询每个学生借的书名字和他的姓名查询结果如下：（6）设计一个视图，并在该视图中完成添加、修改、删
13、除数据的操作。将查询学生的学号、姓名、性别、出生日期定义为一个视图。查询结果如下：CREATE VIEW readers_studentsas select rno,rname,rsex,bdayfrom readers12添加一个学号为 042011、姓名为李伟、性别为男、出生日期 1986/7/7 0:00:00 的学生的信息。查询结果如下：insert into readers_students(rno,rname,rsex,bday)values(04211,李伟,男,1986/7/7 0:00:00)将学号为 04201 的学生姓名改为林丽查询结果如下：update readers_
14、studentsset rname=林丽where rno=04201（7）设计一个视图，并在该视图完成两个统计：a.每位读者以及他的借书数量，按借书数量作降序排列 b.每本书以及被借出的次数，按借出次数作降序排列。134、实验结果分析1. 使用选择语句时，选择的属性涉及多个表，要具体指出那个表。2. 使用嵌套查询的方法时，要依次嵌套表，并学会使用 ALL,MAX 查询方法。3. 在进行查询和“刘涛” 在一个班级的学生的信息时，将其中的语言编辑为 where sname=“刘涛”时出错，将其改为 where sname=刘涛 时运行正确。SQL 2005 软件只能识别英文的单引号而不能识别双引号。5、实验贡献情况（注意：每个组员必须分开书写，组长分配每个组员的贡献值）
