oracle---高级查询（二）

一 子查询

子查询可以出现在任意的位置

包括：SELECT子句、FROM子句、WHERE子句、GROUP BY、HAVING、ORDER BY等。


示例：

查询出月薪比“SCOTT”高的员工信息

Select * from emp where sal>(select sal from emp where ename=‘SCOTT’)

查询出月薪最高的员工姓名和月薪

Select ename,sal from emp where sal=(select max(sal) from emp)

select * from emp where sal>2000 and job='MANAGER'

根据子查询的位置 可以分为 ：

表子查询 

列的子查询 

条件的子查询  

根据数据返回的行和列 分为 单行单列 多行多列 多行单列

--1表字查询（虚表 内存表）   比条件和连接 更容易理解（多行多列子查询）

select * from (select * from emp where sal>200) where job='MANAGER'

--2 列子查询 每个子查询只能返回一行记录（单行单列子查询）

--查询部门名称

select e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno;

select ename,(select dname from dept where rownum=e.empno) as dname from  emp e;

--3 条件子查询

--查询所有雇员为 ACCOUNTING的雇员

select deptno from  dept where dname='ACCOUNTING'

select * from  emp where deptno=10

--=只能等于一行  单行单列子查询

select * from  emp where deptno=(select deptno from  dept where dname='ACCOUNTING');

--查询所有雇员为 ACCOUNTING RESEARCH的雇员 (多行单列子查询)

select * from  emp where deptno=any(select deptno from  dept where dname='ACCOUNTING' or  dname='RESEARCH');

select * from  emp where deptno in(select deptno from  dept where dname='ACCOUNTING' or  dname='RESEARCH');

注意：
一般来说子查询的效率低于连接查询。
表连接查询都可以用子查询替换，但反过来说却不一定。

二 集合

集合的操作

{1，2，4}^{2,3,4}={2,4}

{1，2，4}U{2,3,4}={1，2，3,4}

{1，2，3}-{2，3，4}={1}

集合操作符：合并多个查询结果

UNION ALL：将多个查询结果合并到一个结果中，有重复行【重点】

UNION：将多个查询结果合并到一个结果中，没有重复行（并集）【重点】

INTERSECT：返回两个查询结果中共有的行 (交集)

MINUS：返回从第一个查询结果中减去第二个查询结果中相同的行之后剩余的行(差集)

--复制 表
 create table dept1 as select * from dept
select * from  dept;

select * from dept1;

--交集

select * from dept intersect select * from  dept1;

--并集 union 去掉重复行

select * from dept union select * from  dept1;

--并集 uinon all 不会去重复行

select * from dept union all select * from  dept1;

--差集

select * from dept1 minus select * from  dept;

三rownum分页

分页查找：
select t.* from (select g.*,rownum rn from 表名 g) t where t.cn>=5 and t.cn<10


rownum大于1的记录永远不可能成立
rownum<=任何值都成立 ，>=1成立