Oracle基础（十一）：子查询、EXISTS关键字

一、子查询

子查询是一条SELECT查询语句，它是嵌套在其他SQL语句当中的，
目的是为了给该SQL语句提供数据以支持其执行操作。

--查看谁的工资高于CLARK?
select ename,sal from emp where ename='CLARK';--先查询出CLARK的工资作为过滤条件
select ename,sal from emp where sal>(select sal from emp where ename='CLARK');

（1）子查询在WHERE子句中

①在SELECT查询中,在WHERE查询条件中的限制条件不是一个确定的值，而是来自于另外一个查询的结果。
②为了给查询提供数据而首先执行的查询语句叫做子查询。
③子查询是嵌入在其它SQL语句中的SELECT语句，大部分时候出现在WHERE子句中。
④子查询嵌入的语句称作主查询或父查询
⑤主查询可以是SELECT语句,也可以是其它类型的语句，比如DML或DDL语句。

（2）在DDL中使用子查询

可以根据一个查询结果集快速构建一张表
创建一张表employee，表中的字段为empno,ename,job,sal,deptno,dname,loc
数据为现有表中emp和dept对应的数据。

create table employee as
    select e.empno,e.ename,e.job,e.sal,e.deptno,d.dname,d.loc 
    from emp e join dept d on e.deptno=d.deptno(+);

desc employee;
select * from employee;

通过子查询创建表时若子查询中的字段有别名则该表对应的字段就使用该别名作为其字段名。

当子查询中一个字段含有函数或者表达式，那么该字段必须给别名。

create table employee as
    select e.empno id,e.ename name,e.job,e.sal*12 salary,e.deptno,d.dname,d.loc 
    from emp e join dept d on e.deptno=d.deptno(+);
desc employee;
select * from employee;

（3）DML中使用子查询

--将CLARK所在部门的所有员工删除
delete from employee 
where deptno=(select deptno from employee where name='CLARK');

--查看工资比整个公司平均工资高的员工
select ename,sal 
from emp where sal>(select avg(sal) from emp);

（4）子查询在WHERE子句中（续）

子查询根据查询结果集的不同分为:
①单行单列子查询(返回一行数据)：常用于过滤条件，可以配合=,>,>=,<,<=使用。
②多行单列子查询(返回多行数据)：常用于过滤条件，由于查询出多个值，在判断等于"="时要用IN(list),判断>、>=等操作要配合ANY(list),ALL(list)。
③多行多列子查询(返回多行多列子查询)：通常当做一张表看待。

--查询与SALESMAN职位同部门的其他职位的员工信息
--（该子查询返回多行单列结果集，所以使用IN()等其他操作符）
select ename,job,deptno from emp 
    where deptno in(select deptno from emp where job='SALESMAN') and job<>'SALESMAN';
    
--查看比职位是SALESMAN和CLERK工资都高的员工信息?
select ename,sal,job from emp 
    where sal >all(select sal from emp where job in('SALESMAN','CLERK'));--多行单列子查询

二、EXISTS关键字

EXISTS和NOT EXISTS
EXISTS关键字后面跟一个子查询，当该子查询可以查询出至少一条记录时，EXISTS表达式成立并返回true。

--查看有员工的部门
--员工表中没有40号部门的员工，所以40号部门不会被查询出来，EXISTS关注的是根据子查询中是否能查询出数据
select dname,deptno from dept d 
where exists (select * from emp e where d.deptno=e.deptno);

--查看没有员工的部门
select dname,deptno from dept d 
    where not exists(select * from emp e where e.deptno=d.deptno);--返回40号部门


--查看部门的最低薪水是多少？，前提是该部门的最低薪水要高于30号部门的最低薪水
select min(sal),deptno from emp e group by deptno 
    having min(sal)>(select min(sal) from emp where deptno=30);

三、子查询在FROM子句部分

①FROM子句用来指定要查询的表。
②如果要在一个子查询的结果中继续查询,则子查询出现在FROM子句中,这个子查询也称作行内视图或者匿名视图。
③把子查询当作视图对待，但视图没有名字,只能在当前的SQL语句中有效。

当一个子查询是一个多列子查询，通常是将子查询的结果集当做一张表看待，并基于该查询结果进行二次查询使用。
在FROM子句中，这个子查询也称作行内视图或者匿名视图；
把子查询当作试图看待，但视图没有名字，只能在当前SQL语句中有效。

--查看比自己所在部门平均工资高的员工?
--解决思路：子查询先查看每个部门平均工资是多少，然后将子查询当成一张表看待，
--再与主表关联，然后添加连接条件过滤员工的部门号并且过滤工资高于子查询中的部门平均工资。
select e.ename,e.sal,e.deptno,t.deptno,t.avg_sal from emp e 
    join (select avg(sal) as avg_sal,deptno  from emp group by deptno) t 
    on e.deptno=t.deptno where e.sal>t.avg_sal;--内连接的方式

select e.ename,e.sal,e.deptno,e.deptno,t.avg_sal from emp e,
    (select avg(sal)avg_sal,deptno from emp group by deptno) t 
where e.deptno=t.deptno and e.sal>t.avg_sal;

四、子查询在SELECT部分

把子查询放在SELECT子句部分，可以认为是外连接的另种表现形式，使用更灵活。

--在SELECT子句中使用子查询，可以将查询的结果当作主查询记录中的一个字段值显示
--这里相当一个左外连接，SCOTT的没有部门填NULL
select e.ename,e.sal,(select d.dname from dept d where d.deptno=e.deptno) dname 
from emp e;

posted @ 2022-06-09 00:23 禾喵阅读(948) 评论(0) 编辑收藏举报

Coder Cat