实验12数据库连接查询 实验目的 1.熟悉多表查询的使用; 2.熟悉连接查询的不同实现方式 3.熟悉集合査询的使用。 实验环境 1.硬件 每位学生配备计算机一台,满足相关软件的安装 2.软件 Windows操作系统,安装 Oracle database11g企业版; 3.网络 需要局域网支持; 4.工具 无 三、实验理论与预备知识 1.连接查询的语法结构; 2.连接查询的使用 集合查询的使用。 四、实验任务 1.连接查询的使用 集合查询的使用 五、实验内容及步骤 1.连接查询的使用 检索数据时,通过各个表之间的共同列的关联性,可以查询存放在多个表中 的不同实体的信息;如果在查询时需要对多个表进行操作,并且指定多个表的连 接关系,则可以使用连接查询 以数据库ORCL、表空间 stu oracle及数据表 student、 course、sc为基础, 以连接査询的方式执行以下语句,并写出详细SQL语句;
实验 12 数据库连接查询 一、实验目的 1. 熟悉多表查询的使用； 2. 熟悉连接查询的不同实现方式； 3. 熟悉集合查询的使用。 二、实验环境 1. 硬件 每位学生配备计算机一台，满足相关软件的安装； 2. 软件 Windows 操作系统，安装 Oracle Database 11g 企业版； 3. 网络 需要局域网支持； 4. 工具 无 三、实验理论与预备知识 1. 连接查询的语法结构； 2. 连接查询的使用 3. 集合查询的使用。 四、实验任务 1. 连接查询的使用； 2. 集合查询的使用。 五、实验内容及步骤 1. 连接查询的使用 检索数据时，通过各个表之间的共同列的关联性，可以查询存放在多个表中 的不同实体的信息；如果在查询时需要对多个表进行操作，并且指定多个表的连 接关系，则可以使用连接查询； 以数据库 ORCL、表空间 stu_oracle 及数据表 student、course、sc 为基础， 以连接查询的方式执行以下语句，并写出详细 SQL 语句；
(1)查询学生的学号、姓名、课程号、成绩; (2)査询学生的学号、姓名、院系、课程名、成绩; (3)查询选修“数据库原理”不及格的学生的学号、姓名、成绩 (4)查询和“崔晓”一个院系的其他学生的学号、姓名、院系 (5)查询信息学院年龄最大的学生的学号、姓名、性别、身高 (6)查询信息学院成绩在60分以下之间的学生的学号、课程号、成绩,其中 査询结果先按照课程号升序排列,再按照成绩降序排列 (7)査询学分高于平均学分的课程编号、名称及学分; (8)査询选修“数据库原理”课程的学生学号、课程号及成绩; (9)査询选修了“医院信息系统”的学生的学号、姓名、成绩,其中査询结 果按学号升序排列; (10)査询信息学院学生选修限选课的学号、课程号及成绩 (11)查询成绩小于平均成绩的学生的学号、课程号及成绩; (12)查询选修了“数据库”的学生的学号、姓名、院系 (13)査询选修“数据库”的学生的最高成绩、最低成绩、平均成绩; (14)査询平均成绩在60分以上的课程及其平均成绩: (15)查询平均成绩大于“高莉”的平均成绩的学生学号、姓名及平均成绩 (16)査询每门课程中成绩最低的学生的学号、姓名、性别、院系; (17)査询只被一名学生选修的课程的课程号、课程名 2.集合查询的使用 以数据库ORCL、表空间 stu oracle及数据表 student、 course、sc为基础, 以集合查询的方式执行以下语句,并写出详细SQL语句; (1)查询信息学院或者软件学院的女生的学号、姓名、院系; (2)查询软件学院的女生的学号、姓名、课程号、成绩 (3)查询成绩在80分90分之间的学生的学号、课程号、成绩,其中查询结 果先按照课程号升序排列,再按照成绩降序排列; 六、实验分析 1.对于多表査询,应该使用子查询还是连接查询,说明理由? 2.对于多表查询,使用表的别名会提高查询效率么,请验证并说明理由? 3.下面SQL语句的执行结果一样么?执行时间一样么?那条语句的执行效率最 高,为什么?
（1） 查询学生的学号、姓名、课程号、成绩； （2） 查询学生的学号、姓名、院系、课程名、成绩； （3） 查询选修“数据库原理”不及格的学生的学号、姓名、成绩； （4） 查询和“崔晓”一个院系的其他学生的学号、姓名、院系； （5） 查询信息学院年龄最大的学生的学号、姓名、性别、身高； （6） 查询信息学院成绩在 60 分以下之间的学生的学号、课程号、成绩，其中 查询结果先按照课程号升序排列，再按照成绩降序排列； （7） 查询学分高于平均学分的课程编号、名称及学分； （8） 查询选修“数据库原理”课程的学生学号、课程号及成绩； （9） 查询选修了“医院信息系统”的学生的学号、姓名、成绩，其中查询结 果按学号升序排列； （10） 查询信息学院学生选修限选课的学号、课程号及成绩； （11） 查询成绩小于平均成绩的学生的学号、课程号及成绩； （12） 查询选修了“数据库”的学生的学号、姓名、院系； （13） 查询选修“数据库”的学生的最高成绩、最低成绩、平均成绩； （14） 查询平均成绩在 60 分以上的课程及其平均成绩； （15） 查询平均成绩大于“高莉”的平均成绩的学生学号、姓名及平均成绩； （16） 查询每门课程中成绩最低的学生的学号、姓名、性别、院系； （17） 查询只被一名学生选修的课程的课程号、课程名； 2. 集合查询的使用 以数据库 ORCL、表空间 stu_oracle 及数据表 student、course、sc 为基础， 以集合查询的方式执行以下语句，并写出详细 SQL 语句； （1） 查询信息学院或者软件学院的女生的学号、姓名、院系； （2） 查询软件学院的女生的学号、姓名、课程号、成绩； （3） 查询成绩在 80 分‐90 分之间的学生的学号、课程号、成绩，其中查询结 果先按照课程号升序排列，再按照成绩降序排列； 六、实验分析 1. 对于多表查询，应该使用子查询还是连接查询，说明理由？ 2. 对于多表查询，使用表的别名会提高查询效率么，请验证并说明理由？ 3. 下面SQL语句的执行结果一样么？执行时间一样么？那条语句的执行效率最 高，为什么？
SELECT empno FROM emp; SELECT empno FROM scott. emp SELECT empno FROM SCOTT.EMP; SELECT EMPNO FROM SCOTT.EMP. 4.在执行连接查询时,FROM子句和 WHERE子句中,对表的连接顺序有哪些要 求 七、课外自主实验 1.数据库查询优化 Oracle可以存储更多更复杂的数据,这就使得数据的查询效率显得更为重 要,低效率的査询给实际应用带来的麻烦事不可估量的,对于 DELETE语句、 SELETE 子句、FROM子句和 WHERE子句等都可以进行优化。 以数据库ORCL、表空间 stu oracle及数据表 student、 course、Sc为基础, 执行以下操作; (1)显示SQL语句执行时间 执行SQL语句: SET TIM|NGON (2)请执行以下sαL语句,指出每组査询效率较高的语句,并总结出SQL语 句查询优化建议 第一组:查询学生的基本信息; 1 Select* from student 2) Select Sno, Sname, Ssex, Sbirth, Sdept from student 3) SELECT SNO CNO GRADE FROM SC 第二组:查询及格的学生成绩 1) Select from sc where grade>59: 2)Select* from sc where Grade>=60: 3) Select sno, Cno, Grade from sc where grade>59 4)Select Sno, Cno grade from sc where grade>=60: 5) SELECT SNO, CNO, GRADE FROM SC WHERE GRADE>=60 第三组:查询学生的学号、姓名、课程号、成绩; 1) Select student. sno, sname, cno, grade from student, sc where student sno=sc sno
SELECT empno FROM emp; SELECT empno FROM scott.emp; SELECT empno FROM SCOTT.EMP; SELECT EMPNO FROM SCOTT.EMP; 4. 在执行连接查询时，FROM 子句和 WHERE 子句中，对表的连接顺序有哪些要 求？ 七、课外自主实验 1. 数据库查询优化 Oracle 可以存储更多更复杂的数据，这就使得数据的查询效率显得更为重 要，低效率的查询给实际应用带来的麻烦事不可估量的，对于DELETE语句、SELETE 子句、FROM 子句和 WHERE 子句等都可以进行优化。 以数据库 ORCL、表空间 stu_oracle 及数据表 student、course、sc 为基础， 执行以下操作； （1） 显示 SQL 语句执行时间 执行 SQL 语句：SET TIMING ON （2） 请执行以下 SQL 语句，指出每组查询效率较高的语句，并总结出 SQL 语 句查询优化建议； 第一组：查询学生的基本信息； 1） Select * from student; 2） Select Sno,Sname,Ssex,Sbirth,Sdept from student; 3） SELECT SNO,CNO,GRADE FROM SC; 第二组：查询及格的学生成绩 1） Select * from sc where Grade>59; 2） Select * from sc where Grade>=60; 3） Select Sno,Cno,Grade from sc where Grade>59; 4） Select Sno,Cno,Grade from sc where Grade>=60; 5） SELECT SNO,CNO,GRADE FROM SC WHERE GRADE>=60; 第三组：查询学生的学号、姓名、课程号、成绩； 1） Select student.sno,sname,cno,grade from student,sc where student.sno=sc.sno;
2)Select student sno, sname, cno, grade from sc, student where student sno=sc. no; 3) Select s sno, sname cno, grade from student s, sc a where s sno=a sno 4)Select s sno, sname, cno, grade from sc a, student s where s sno=a sno 5) SELECT SSNO, SANME, CNO, GRADE FROM SC A, STUDENT S WHERE SSNO=ASNO: 第四组:査询选修“数据库原理”的学生姓名 1) select Sname from student sc course where student sno=scsno and sc cno= course. cno and course cname=数据库原理’; 2)select Sname from student where sno in(select sno from sc where cno=(select cno from course where cname=!数据库原理); 第五组 1) Select sno, Sname, Sbirth from student where ssex=男 and sdept='信息技术学 院 2) Select sno, Sname, Birth from student sdept=信息技术学院 and where ssex= 男; (3)针对以下査询,写出执行效率最高的査询语句,并写出测试过程 1)查询和“崔晓”一个院系的其他学生的学号、姓名、院系; 2)查询信息学院年龄最大的学生的学号、姓名、性别 3)査询选修“数据库原理”课程的学生学号、姓名,课程号及成绩 4)査询信息学院学生选修限选课的学号、课程号及成绩; 5)查询sc表中80分以上学生的基本信息 2.创建及管理索引 索引是表的一个概念部分,用来提高检索数据的效率,但是不必要的索引反 而会影响查询效率。 以数据库ORCL、表空间 stu oracle及数据表 student、 course、sc为基础, 执行以下操作; (1)索引效率的比较 1)重启 oracle服务,记录下面语句的执行时间; Select* from student where sname=张山; 2)为 student的 sname创建索引,请写出具体创建方法;
2） Select student.sno,sname,cno,grade from sc, student where student.sno=sc.sno; 3） Select s.sno,sname,cno,grade from student s,sc a where s.sno=a.sno; 4） Select s.sno,sname,cno,grade from sc a,student s where s.sno=a.sno; 5） SELECT S.SNO,SANME,CNO,GRADE FROM SC A,STUDENT S WHERE S.SNO=A.SNO; 第四组：查询选修“数据库原理”的学生姓名； 1） select Sname from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and course.cname='数据库原理'; 2） select Sname from student where sno in (select sno from sc where cno=(select cno from course where cname='数据库原理')); 第五组： 1） Select Sno,Sname, Sbirth from student where ssex='男' and sdept='信息技术学 院'; 2） Select Sno,Sname, Sbirth from student sdept='信息技术学院' and where ssex=' 男'; （3） 针对以下查询，写出执行效率最高的查询语句，并写出测试过程； 1） 查询和“崔晓”一个院系的其他学生的学号、姓名、院系； 2） 查询信息学院年龄最大的学生的学号、姓名、性别； 3） 查询选修“数据库原理”课程的学生学号、姓名，课程号及成绩； 4） 查询信息学院学生选修限选课的学号、课程号及成绩； 5） 查询 sc 表中 80 分以上学生的基本信息； 2. 创建及管理索引 索引是表的一个概念部分，用来提高检索数据的效率，但是不必要的索引反 而会影响查询效率。 以数据库 ORCL、表空间 stu_oracle 及数据表 student、course、sc 为基础， 执行以下操作； （1） 索引效率的比较 1） 重启 oracle 服务，记录下面语句的执行时间； Select * from student where sname='张山'; 2） 为 student 的 sname 创建索引，请写出具体创建方法；
3)重启 oracle服务,再次执行下面语句,并记录执行时间; Select* from student where sname=张山 (2)请分析针对数据表 student、 course、sc,应该创建哪些索引,并比较索 引建立前后,语句执行速度的变化?如主键索引存在有无区别? (3)请写出索引的具体创建步骤 八、实验扩展资源 1.图书 (1)王彬周士贵rae11g基础与提高[M]电子工业出版社,2008 (2) Oracle database11g数据库管理艺术[M]人民邮电出版社,2010 2.期刊论文 (1)张艳霞,陈丹琪,韩莹. WHERE EXISTS子査询应用研究[.电脑知识与技 术2012(13) (2)赵康李明东.基于成本的 ORACLE子查询性能研究[.西华师范大学学报 自然科学版2013(03) 3.在线学习平台 (1)http://xg.hactcm.edu.cn 4.其他资源 (1)http://www.oracle.com/cn (2)https://academy.oracle.com
3） 重启 oracle 服务，再次执行下面语句，并记录执行时间； Select * from student where sname='张山'; （2） 请分析针对数据表 student、course、sc，应该创建哪些索引，并比较索 引建立前后，语句执行速度的变化？如主键索引存在有无区别？ （3） 请写出索引的具体创建步骤； 八、实验扩展资源 1. 图书 （1）王彬,周士贵.Oracle 11g 基础与提高[M].电子工业出版社,2008. （2）Oracle Database 11g 数据库管理艺术[M].人民邮电出版社,2010. 2. 期刊论文 （1） 张艳霞,陈丹琪,韩莹. WHERE EXISTS 子查询应用研究[J]. 电脑知识与技 术.2012(13). （2） 赵康,李明东. 基于成本的 ORACLE 子查询性能研究[J]. 西华师范大学学报 (自然科学版).2013(03). 3. 在线学习平台 （1）http://xg.hactcm.edu.cn 4. 其他资源 （1）http://www.oracle.com/cn （2）https://academy.oracle.com