数据库第二次实验

<实验要求>

1. 每次实验前学生必须根据实验内容认真准备。
2. 在指导教师的帮助下能够完成实验内容。
3. 实验结束后总结实验内容、书写实验报告。
4. 遵守实验室规章制度、不缺席。
5. 实验学时内必须做数据库的有关内容，不允许上网聊天或玩游戏。

<实验报告的书写要求>

1. 明确实验的目的及要求;
2. 记录实验的输入数据和输出结果;
3. 说明实验中出现的问题和解决过程;
4. 写出实验的体会和实验过程中没能解决的问题;

实验 2 数据查询

（单表查询、连接查询、嵌套查询、集合查询）

一．实验目的

理解 SELECT 语句的操作和基本使用方法，掌握简单查询；理解 JOIN 语句的操作和基本使用方法，掌握内连接、外连接、表的自身连接的概念和使用；掌握 SELECT 语句的嵌套查询语法，实现表的复杂查询，进一步理解 SELECT 语句的高级使用方法；了解集合查询的使用方法。二．实验要求

1. 掌握 SQL 语言中常用查询语句的功能和使用方法
2. 掌握 SQL 语言中分组查询命令的功能及其使用方法
3. 掌握 SQL 语言中连接查询命令的功能及其使用方法
4. 掌握 SQL 语言中子查询命令的功能及其使用方法
5. 掌握查询命令中的函数的使用方法
6. 了解集合查询的使用方法 三．设计的内容或知识点

SELECT [ ALL | DISTINCT [ ( expression [, ...] ) ] ]

* | expression [ AS output_name ] [, ...]

[ FROM from_item [, ...] ]

[ WHERE condition ]

[ GROUP BY expression [, ...] ]

[ HAVING condition [, ...] ]

[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]

[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ] 四、实验环境

一种情况：普通 PC 机，装有 Oracle 的客户端，通过客户端配置和移植工具中的 net manager 配置远程数据库服务器，实现连接。

另一种情况：下载简单的客户端配置程序，配置 tnsnames 文件，通过 toad 或 pl/sql developer 开发工具连接远程数据库服务器，实现连接。

本实验环境现在选用第一种情况进行实验。

五、实验步骤

1. 分组查询命令的使用
   1. 不带条件的分组查询：每组只返回一条记录。

­­计算每门课程的总分

select cno,sum(grade) from sc group by cno

 

 

 

 

 

­­查每个院系每个性别的学生数。

select sdept,ssex,count(*) from student group by sdept,ssex;

 

1. 1. 带条件的分组查询

­­成绩平均分超过 85 的课程,并按平均分升序排列。

select cno, avg(grade) from sc group by cno having avg(grade)>85 order by avg(grade) asc;

 

1. 连接查询命令的使用
   1. 无条件连接，即笛卡尔积(没有意义)

select student.*,sc.* from student, sc;

 

 

 

 

 

1. 1. 等值连接 ：参与连接的表在公共列上有相同值 。 ­­查看成绩高于 85 分的学生信息及成绩情况

select student.*,sc.* from student, sc where student.sno=sc.sno and grade >85;

 

 

 

 

 

内连接实现查询要求：

select student.*,sc.* from student inner join sc on student.sno=sc.sno where grade >85;

 

 

 

1. 1. 外连接：可避免连接时丢失信息（等值连接只显示连接条件成立的信息，不能显示没有选课的学生信息，有些时候个别学生确实没有选修课程，但也希望看到该学生信息，这时需要用到外连接查询）可以使用两种方式实现外连接：

select student.*,cno,grade from student,sc where student.sno=sc.sno(+);

­­外连接

select student.*,cno,grade from student left outer join sc on student.sno=sc.sno （4）表的自身连接：一个表与其自己进行连接，需要给表起别名以示区别；由于所有属性名都是同名属性，因此必须使用别名前缀。

 

­­查询每一门课的间接先修课（即先修课的先修课）

select first.cno，second.cpno from course first, course second

where first.cpno=second.cno;

 

1. 子查询（嵌套查询)命令的使用
   1. 单行值子查询：子查询返回一条纪录

­­查询与“2008001”在同一个院系学习的学生信息

select * from student where sdept =( select sdept from student where sno= ‘1111’ )

 

1. 1. 多行值子查询：子查询返回一组纪录

­­­­查询平均成绩高于或等于' 1'号课程最高成绩的学生情况 。

select * from student where sno in ( select sno from sc group by sno having avg(grade)>=(select max(grade) from sc where cno= '1 ') );

 

­­查询其他学院中比计算机学院某一学生年龄小的学生姓名和年龄

select sname,sage from student where sage<any ( select sage from student where sdept=’IS’ ) and sdept<>’IS’;

 

­­可以用聚集函数实现上述查询要求

select sname,sage from student where sage<=( select max(sage) from student where sdept= ' 计算机学院' )

and sdept<>'计算机学院 ';

 

1. 查询命令中函数的使用

­­将所有学生的名字用大小写形式显示

select sname, upper(sname),initcap(sname) from student;

 

­­显示当前日期，dual 是系统提供的一张空表

select sysdate from dual;

 

­­上述日期格式显示为日­月­年格式，不符合中文日期规范，因此可以使用转换函数转换为好看的格式

select to_char(sysdate, 'yyyy­mm­dd hh24:mi:ss') from dual;

 

­­根据输入值将学生信息排序：若输入 1，则按 sname 排序；若输入 2，则按年龄排序；缺省时，按 sname 排序。

select * from student order by decode(&ob,1, sname, 2, sage, sname);

 

5、集合查询的使用

­­查询计算机学院的学生及年龄不大于 19 岁的学生

select * from student where sdept= '计算机学院' union select * from student where sage<=19;

 

­­查询计算机科学系的学生与年龄不大于 19 岁的学生的交集

select * from student where sdept= '计算机学院' intersect select * from student where sage<=19;