笔记系列----sql复杂查询下：子查询

1.单行单列子查询 采用单行比较运算符（>,<,=,<>,>=,<=）

内部select子句只返回一行结果

2.多行单列子查询，多行比较运算符（all,any,in,not in）

all  >大于最大的， <小于最小的

3.在多行子查询中使用any  > 大于最小的，<小于最大的

4.多行子查询中使用in   逐个比较是否有匹配值

5.多行多列子查询，多列子查询返回多列结果

多列子查询，

特点是主查询每一行的列都需要与子查询返回列表中的相应列同时进行比较，只有各列完全匹配才显示数据

注意点：

• 成对比较是不能使用>any或者>all登多行单列比较符的
• 成对比较时多列顺序和类型必须一一对应

6.与非成对比较（含布尔运算）的区别

select ename,deptno,sal,comm 
from emp1
where sal in(
    select sal
    from emp1
    where deptno=30)
and
    nvl(comm,0) in (
    select nvl(comm,0)
    from emp1
    where deptno=30)
and deptno<>30

非成对的，比较没有多行多列子查询的要松一点

7.关于布尔运算not

7.1 not就是否定后面的比较符

where empno=7788	where not (empno=7788)
where ename LIKE 'S%'	where ename not like 'S%'
where deptno IN (20,30)	where deptno not in (20,30)
where sal BETWEEN 1500 AND 3000	ewhere sal not between 1500 and 3000
where comm IS NULL	where comm is not null
where EXISTS (select 子查询  关联子查询）	where not exists (select 子查询）

7.2 not in在子查询中的空值问题

in 与 not in 遇到空值时情况不同，对于 not in ，如果子查询的结果集中有空值，那么主查询得到结果集也是空

查找出没有下属的员工

select ename from emp where empno not in (select mgr from emp);

结果为空

因为在子查询中有一个空值，对于not in这种形式，一旦子查询中出现空值，则主查寻记录结果也返回空

where empno not in（。。。） 与 where not empno in （。。。）写法结果一样，但是前者是not in组合，后者是not的一个表达式

排除空值影响方法如下：

select ename from emp where empno not in (select nvl(mgr,0) from emp);

8.from子句中使用子查询（内联视图）

1) 先看每部门的平均工资，再把结果集作为一个内联视图

select deptno,avg(sal) salavg from emp group by deptno;

2)把内联视图命别名b，然后和emp别名e做连接，满足条件即可
此处需要设置avg（sal）别名，便于select后where子句的比较

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

9.关联子查询与非关联子查询

从主查寻调用子查询，外部调用内部的角度，可以有关联子查询与非关联子查询

9.1 非关联子查询

子查询部分可以独立执行，oracle的in子查询一般用于非关联子查询，执行过程如下：

首先执行子查询，并将获得的结果列表存放在一个加了索引的临时表中。也就是说在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中再执行主查询。

select ename,sal,deptno from scott.emp where deptno=30 and sal>(select avg(sal) from scott.emp where deptno=30);

9.2 关联子查询

子查询（inner）会引用主查询（outer）查询中的一列或多列。执行时，外部查询的每一行都被一次一行地传递给子查询。子查询依次读取外部查询传递来的每一值，并将其用到子查询上，直到外部查询所有的行都处理完为止。然后返回查询结果。

select e1.ename,e1.sal,e1.deptno from emp e1 where sal>(select avg(sal) from emp e2 where e1.deptno=e2.deptno);

在关联子查询中使用exists和not exists,关联查询效率问题

 select empno,ename,job,deptno from emp outer where exists(select 'X' from emp where mgr=outer.empno);  //子查询表大，主查询表小，效果相比于in效果越好。in时获取子表时每条都看，exist是找着一条，马上跳出；exist比较靠前能找到

10.关于别名

若别名中有特殊字符，需要使用双引号。如："AB C"  ”a||a“(不可作为表列明)

10.1必须使用别名的地方

1）两表连接后，select中有相同命名的列，表需要区别标识

2）create table emp1 as select deptno,avg(sal) avgsal from emp group by deptno;

3）内联视图中，where子句需要引用select中函数

select * from (select avg(sal) salavg from emp) where salavg>200;//where先于select执行，后于from执行

10.2不能使用别名的地方

在独立的select结构的投影中使用了列别名，不能在其后的where或having中直接引用该列明

select ename ，sal salary from emp where salary>2000;-------错

select ename,deptno,sal,comm,(sal+nvl(comm,0)) totalsal from emp order by totalsal desc-----对

因为where是先于select执行的，没执行select之前oracle是不认识别名的；但是order by是后于select执行的，所以后面的语句是正确的

11.简单查询与复杂查询练习题

1）列出emp表工资最高的前三位员工信息

select *  from (select ename,deptno,sal,comm,(sal+nvl(comm,0)) totalsal from emp order by totalsal desc ) where rownum <= 3;

使用rownum注意点：

• rownum>时不会返回任何行
• rownum<  和 and 并用时，是在另一个条件基础上的rownum<，而不是两个独立条件的并集（intersect）

体会一下：
SQL> select ename,sal,deptno from emp where deptno=10;
ENAME SAL DEPTNO
---------- ---------- ----------
CLARK 2450 10
KING 5000 10
MILLER 1300 10
SQL> select ename,sal,deptno from emp where rownum <=1;
ENAME SAL DEPTNO
---------- ---------- ----------
SMITH 800 20

//先执行deptno=10的条件，后在deptno=10的基础上执行rownum<=1
SQL> select ename,sal,deptno from emp where rownum <=1 and deptno=10;
ENAME SAL DEPTNO
---------- ---------- ----------
CLARK 2450 10

2) 列出emp表第5-第10名员工（按sal大--小排序）的信息（结果集分页查询技术）
 

select t1.* from (select * from emp order by sal desc) t1 where rownum<=10 
minus
select t2.* from (select * from emp order by sal desc) t2 where rownum<=4;


//参考结果如下
select * from (select t1.*,rownum rn from (select * from emp order by sal desc) t1) where rn between 5 and 10;

正确结果使用rownum 为新列从而避免了rownum不能输出>的问题

3）从列出emp表中显示员工和经理的对应关系表（emp自连，利用笛卡尔积）

select e1.empno,e1.ename,e1.mgr,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;

4)要求列出emp表中最高工资的员工所在工作地点（emp+dept左外）

select * from (select emp.empno,emp.ename,emp.deptno,dept.loc,(sal+nvl(comm,0)) tosal from emp,dept1 dept where emp.deptno=dept.deptno(+)  order by tosal desc)  where rownum<=1;

select a.ename, d.loc from 
(select * from emp where sal=
(select max(sal) from emp)) a left join dept1 d on a.deptno=d.deptno ;

5)ctas方法建立dept1,将dept1表增加一列person_count,要求根据 emp 表填写 dept1 表 的各部门员工合计数（典型个关联查询）
 

create table dept1 as select * from dept;   //此时的select无（）
alter table dept1 add person_count int;
update dept1 set person_count=(select count(*) from emp  where emp.deptno=dept1.deptno) 

select t1.* , t2.person_count  from dept1 t1,(select deptno,count(*) person_count from emp group by deptno) t2 where t1.deptno=t2.deptno;

6)复杂select查询，以及HR用户的几个表为例，显示欧洲地区员工的平均工资及人数（使用多表连接及内联视图）

select avg(salary),count(*) from EMP_DETAILS_VIEW where REGION_NAME='Europe';

7）同上题，使用嵌套子查询语句