Oracle之子查询

子查询

• 子查询是指一个查询语句嵌套在另一个查询语句内部的查询(select …)

1、from 后面的子查询

• 将子查询的结果看作一张表，再对这张表进行查询

-- 查询员工的姓名和他上级的姓名
select e.ename, d.ename mname 
from emp e left join emp d on e.mgr = d.empno;

select e.ename, d.ename mname
from emp e left join (select empno, ename from emp) d 
			on e.mgr = d.empno;
			
-- 查询“c001”课程比“c002”课程成绩高的所有学生的学号；
select s1.sno
from (select * from sc where cno = 'c001') s1
         join (select * from sc where cno = 'c002') s2
              on s1.sno = s2.sno
where s1.score > s2.score;

-- 查询所有同学的学号、姓名、选课数、总成绩；
select s1.sno, s1.sname, s2.num 选课数, s2.sum 总成绩
from student s1 left join 
(select sno, count(cno) num, sum(score) sum from sc group by sno) s2
	on s1.sno = s2.sno;

2、where 后面的子查询

• 将子查询的结果当作条件表达式的条件来进行比较

单行子查询

• 单行子查询：子查询的sql语句只查出一条记录

-- 查询工资大于10号部门平均工资的员工信息
-- 查询10号部门的员工平均工资
select avg(sal) from emp where deptno = 10;

select * from emp
where sal>(select avg(sal) from emp where deptno = 10);

多行子查询

• 多行子查询：子查询的sql 语句查出的是一个数据集合

-- 查询工资比20号部门所有人工资都高的员工信息

-- 单行子查询
select * from emp where sal>(select max(sal) from emp where deptno=20);
-- 多行子查询
select * from emp where sal>all(select sal from emp where deptno=20);

-- 查询所有课程成绩小于60 分的同学的学号、姓名；
select sno, sname from student
where sno in (select sno from sc where score < 60);

-- 查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；
select *
from student
where sno in (select s1.sno
              from (select * from sc where cno = 'c001') s1
                       join (select * from sc where cno = 'c002') s2
                            on s1.sno = s2.sno);

-- 查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名；
select sno, sname
from student
where sno in (select s1.sno
             from (select * from sc where cno = 'c001') s1
                      join (select * from sc where cno = 'c002') s2
                           on s1.sno = s2.sno
             where s1.score > s2.score);

-- 查询没有学全所有课的同学的学号、姓名；
select sno, sname from student
where sno not in (select sno from sc group by sno having count(cno) = (select count(*) from course)); 

3、exists、not exists

• exists：后面跟一个子查询，如果子查询有结果，那么这个条件就是成立的，如果子查询无结果条件不成立
• not exists：与exists相反

-- 查询无结果
select * from emp where exists(select * from emp where 1=0);
-- 查询emp表所有数据
select * from emp where exists(select * from emp);

-- 查询有员工的部门信息
select * from dept where deptno in (select deptno from emp group by deptno);

select * from dept d where (select count(*) from emp where deptno=d.deptno)!=0;

select * from dept d where exists(select deptno from emp where deptno=d.deptno);

-- 查询没有有员工的部门信息
select * from dept where deptno not in (select deptno from emp group by deptno);

select * from dept d where (select count(*) from emp where deptno=d.deptno)=0;

select * from dept d where not exists(select deptno from emp where deptno=d.deptno);

4、having 后面的子查询

• 将子查询的结果当作条件表达式的条件来进行比较

-- 查询各个部门的部门编号和员工人数，要求部门的平均工资大于30号部门的平均工资

-- 查询30号部门的平均工资
select avg(sal) from emp where deptno=30;

select deptno,count(*) from emp group by deptno having avg(sal)>(select avg(sal) from emp where deptno=30);

-- 查询全部学生都选修的课程的课程号和课程名
select cno, cname from course
where cno in (select cno from sc group by cno having count(sno) = (select count(*) from student));

-- 查询没有学全所有课的同学的学号、姓名；
select sno, sname from student
where sno not in (select sno from sc group by sno having count(cno) = (select count(*) from course));

select s1.sno, s1.sname
from student s1 left join (select sno from sc group by sno having count(cno) < (select count(*) from course)) s2
on s1.sno=s2.sno;

5、select 和 from 之间的子查询

select 和 from 之间的子查询，子查询的结果只能是单行列

• 将子查询的结果当作一个字段来进行展示

select empno, ename, (select 1 from dual) from emp;
-- 查询员工的信息及部门名称
select emp.*, (select dname from dept where deptno=emp.deptno) dname from emp;