Oracle数据库学习笔记（五）

多表查询

查询学生和对应的系的信息，输出学号、姓名和系名

SELECT sno,sname,dname

FROM student,dep

WHERE student.dno=dep.dno;

多表查询必须将两个表有关联的列连起来

例：

查询大学物理不及格的学生名单，输出学生的学号、姓名及成绩

SELECT student.sno,sname,grade

FROM student,sc,course

WHERE student.sno=sc.sno AND sc.cno=course.cno And cname='大学物理' AND grade<60;

元组变量

查询教师王莹直接领导的信息，输出工号和姓名

SELECT t2.tno,t2.tname

FROM teacher t1, teacher t2 --给表创建两个别名

WHERE t1.mgr=t2.tno AND t1.tname='王莹'; --通过表的别名，相当于把一张表变成两张表，变成了多表查询

层次查询

查询教师王莹及其各级领导信息，输出层次关系、工号和姓名

select level,tno,tname   

from teacher                   

connect by tno=prior mgr--查询各级领导必须在领导工号加上prior以表上一级的信息 

start with tname='王莹'--从王莹开始查起

;

查询教师上下级关系，最高领导为第一层，输出层次关系、工号和姓名

select level, tno ,tname

from teacher

connect by prior tno=mgr

start with mgr is nul--查询至无上级领导人员

;

集合运算

查询同时选修了c01和c02两门课的学生，输出学号

(select sno from sc where cno='c01')

intersect --交

(select sno from sc where cno='c02');

查询选修了c01但没有选修c02的学生的学号

(SELECT sno FROM sc WHERE cno='c01')

MINUS  --差

(SELECT sno FROM sc WHERE cno='c02');

查询选修了c01或者c02的学生的学号

select distinct sno

from sc

where cno='c01' or cno='c02';

或者：

(SELECT sno FROM sc WHERE cno='c01')

UNION  --并

(SELECT sno FROM sc WHERE cno='c02');

聚合操作

常用5种聚合的操作符：

SUM — 对某列的值求和。

AVG — 求某列值的平均值。

MIN — 求某列值的最小值。

MAX — 求某列值的最大值。

COUNT — 统计某列查询结果的数目（计数）。

求全体教师月工资总额

select sum(sal) from teacher;

查询教师的最高工资

select max(sal) from teacher;

查询数据库技术的最低分

select min(grade) from sc,course where sc.cno=course.cno and cname='数据库技术';

查询数据库技术成绩最低的学生的信息，输出学号、姓名和成绩

select sc.sno,sname,grade

from sc,student

where sc.sno=student.sno and grade=(select min(grade) from sc,course where sc.cno=course.cno and cname='数据库技术') and cno in (select cno from course where cname='数据库技术');

查询物理系学生大学英语的平均成绩

select avg(grade)

from sc,dep,course,student

where sc.cno=course.cno and student.sno=sc.sno and student.dno=dep.dno and dname='物理系' and cname='大学英语';

查询有多少名同学数据库技术不及格

select count(*)--查询不确定的人数可用*代替

from sc,course

where sc.cno=course.cno and cname='数据库技术' and grade<60;

查询计算机系的同学正在选修的课程数

SELECT COUNT(DISTINCT cno) --去重复值

FROM sc,student,dep

WHERE dep.dno=student.dno and student.sno=sc.sno and grade IS NULL and dname='计算机系';

查询化学系岁数最小的学生的年龄

select min(extract(year from sysdate)-extract(year from birth))

from student,dep

where student.dno=dep.dno and dname='化学系';

分组统计

各个系的同学正在选修的课程数，输出系名和课程数

SELECT dname,COUNT(DISTINCT cno)

FROM sc,student,dep

WHERE dep.dno=student.dno and student.sno=sc.sno and grade IS NULL

Group by dname;

--分组依据的字段必须是输出的字段，比如这个例子如果改成group by dep.dno则出错。

查询各个系数据库技术最高学和最低分，男女生分开统计，输出系名、性别、最高分和最低分

SELECT dname,sex,MAX(grade),MIN(grade)

FROM student,sc,course,dep

WHERE sc.cno＝course.cno and student.dno=dep.dno and student.sno=sc.sno and cname='数据库技术'

GROUP BY dname,sex;

--如果写成group by 1,2;是错误的，group by与order by不同，不能用输出项顺序号代替输出项名称。

查询数据库技术最高分超过85分的系，男女生分开统计，输出系名、性别、最高分

select dname,sex,max(grade)

from student,sc,course,dep

where student.sno=sc.sno and sc.cno=course.cno and student.dno=dep.dno and cname='数据库技术'

group by dname,sex

having max(grade)>85;  --附加条件

查询数据库技术最高分超过85分的系，男女生分开统计，输出系名、性别、最高分

select dname,sex,max(grade)

from dep,student,sc,course

where dep.dno=student.dno and student.sno=sc.sno and sc.cno=course.cno and cname='数据库技术' and grade>85

group by dname,sex;--因为只要把成绩没超过85分的记录先排除掉，那么最高分一定超过85分。

容易犯错误的写法：

查询各个系的同学正在选修的课程数，输出课程数超过3的系名和课程数

select dname,count(distinct cno)

from dep,sc,student

where dep.dno=student.dno and student.sno=sc.sno and grade is null and count(distinct cno)>3

group by dname;

--错误，因为聚合函数不能出现在where条件句中。

正确写法：

查询各个系的同学正在选修的课程数，输出课程数超过3的系名和课程数

select dname,count(distinct cno)

from dep,sc,student

where dep.dno=student.dno and student.sno=sc.sno and grade is null

group by dname

having count(distinct cno)>3;

子查询

查询工资最高的老师的信息，输出工号、姓名和工资

SELECT tno, tname, sal

FROM teacher

WHERE sal=(SELECT max(sal) FROM teacher);

查询物理系的学生名单，输出姓名

SELECT sname

FROM student

WHERE dno=(SELECT dno FROM dep WHERE dname='物理系');

查询选课超过3门的男生人数

select count(*)

from student

where sex='男' and sno in(select sno from sc group by sno having count(cno)>3);

student表中没有cno，所以利用sno跳板过去sc表查找cno

查询数据库技术不及格的男生名单，输出学号和姓名

SELECT sno,sname FROM student

WHERE sex='男' AND sno IN

(SELECT sno FROM sc

WHERE grade<60 AND cno IN

(SELECT cno FROM course

WHERE cname='数据库技术') );

查询数据库技术成绩最高的学生的信息，输出学号、姓名和成绩

select sc.sno,sname,grade

from sc,student

where sc.sno=student.sno and grade=(select max(grade) from sc,course where sc.cno=course.cno and cname='数据库技术') and cno in (select cno from course where cname='数据库技术');

查询不及格学生信息，输出学号和姓名

select sno,sname from student

where sno in(select sno from sc where grade<60);

或者：

select sno,sname from student

where sno=some(select sno from sc where grade<60);

或者：

select sno,sname from student

where sno=any(select sno from sc where grade<60);