Oracle子查询

子查询
 单行子查询(single-row subqueries)
    使用的运算符号(=,>,<,>=,<=,<>)
  多行子查询(multiple-row subqueries)
    使用的运算符号(in,not in,exists,not exits,all,any)
   相关子查询(correlated subqueries)
    格式 select 列名,(select 语句) from 表名
   标量子查询(scalar subqueries)
    子查询是返回单行单列,格式同上
   多列子查询(multiple-column subqueries)
   在DDL语句中使用子查询
   在DML语句中使用子查询

--------
单行子查询
 --显示工资最高的雇员信息
 Select ename,deptno,sal from emp
 Where sal=(select max(sal) from emp);

--------
多行子查询
 --显示与部门编号为20的岗位相同的雇员信息
 Select ename,deptno,sal,job from emp
 Where job in (select distinct job from emp where deptno=20);
 --显示不与部门编号为20的岗位相同的雇员信息
 Select ename,deptno,sal,job from emp where job not in (select distinct job from emp where deptno=20);
 --显示高于部门编号为20的所有雇员的工资的雇员信息
 select ename,deptno,sal ,job from emp
 where sal>all(select sal from emp where deptno=20);
 --显示高于部门编号为20的任何雇员的工资的雇员信息
 select ename,deptno,sal ,job from emp
 where sal>any(select sal from emp where deptno=20); 

---------
相关子查询
 --显示每个部门的最高工资的雇员信息
 select deptno,(select max(sal) from emp b where b.deptno=a.deptno) maxsal
 from emp a order by deptno;
 --增加distinct
 select distinct deptno,(select max(sal) from emp b where b.deptno=a.deptno) maxsal
 from emp a order by deptno;
 --显示工作在NEW YORK的雇员信息
 select ename,deptno,sal,job from emp
 where exists (select 'x' from dept where dept.deptno=emp.deptno and dept.loc='NEW YORK');

---------
标量子查询
 --返回单行单列
 Select count(*) from emp;
 Select sum(sal) from emp;
 Select avg(sal) from emp;

---------
多列子查询
 --显示与SMITH部门和岗位完全相同的所有雇员信息，以说明成对比较的意思
 select ename,deptno,sal,job from emp
 where (deptno,job) = (select deptno,job from emp where ename='SMITH');
 --显示岗位或管理员匹配于部门编号为20的所有雇员信息，以说明非成对比较的意思
 select ename,deptno,sal,job,mgr from emp
 where job in (select job from emp where deptno=20) and mgr in (select mgr from emp where deptno=20)
 order by deptno

--------
创建所有的列
 create table dept1(deptno,dname,loc) as
 select deptno,dname,loc from dept;
 --创建所有列
 create table emp1 as select * from emp;

---------
在DML语句中使用子查询
 --先查询于WARD岗位相同的雇员的工资、补助。然后将与WARD岗位相同的雇员的工资、补助更新为与WARD完全相同
 select ename,job,sal,comm from emp1
 where job=(select job from emp1 where ename='WARD');
 update emp1 set (sal,comm)=(select sal,comm from emp1 where ename='WARD')
 where job=(select job from emp1 where ename='WARD');
 select ename,job,sal,comm from emp1
 where job=(select job from emp1 where

 
 --先查询ACCOUNTING部门的雇员信息。然后删除该部门的雇员信息。
 select ename,job,deptno from emp1
 where deptno=(select deptno from dept1 where dname='ACCOUNTING');
 DELETE FROM EMP1
 WHERE deptno=(select deptno from dept1 where dname='ACCOUNTING');
 SELECT ENAME,JOB,deptno from emp1
 where deptno=(select deptno from dept1 where dname='ACCOUNTING');

 --将EMP表中ACCOUNTING部门的雇员信息插入到EMP1表中
 insert into emp1 select * from emp
 where deptno=(select deptno from dept where dname='ACCOUNTING');
 select ename,job,deptno from emp1
 where deptno=(select deptno from dept1 where dname='ACCOUNTING');
 insert into emp1(empno,ename)
 select empno,ename from emp
 where deptno=(select deptno from dept where dname='ACCOUNTING');
 --注意一定包含主键列和非空列，否则会报约束错误。