Oracle子查询、连接等知识

–子查询，出现在from中，就是讲select后的结果当作一张表来处理
–查询在纽约工作的人
select *
from (select e1.empno,e1.ename,e1.job,d1.loc from emp e1,dept d1 where e1.deptno=d1.deptno) temp
where temp.loc=’NEW YORK’;

–在where中 select的结果为单行单列可以作为数值使用
–查询公司最早被雇佣的员工
select *,min(hiredate) from emp e;–错误
select * from emp e where e.hiredate=min(hiredate);–错误
select * from emp e where e.hiredate=(select min(hiredate) from emp);–正确

(select deptno,count(*) from emp e group by e.deptno);
–按照单行单列使用
select * from (select deptno,count() c from emp e group by e.deptno) e where e.c=(select max(c) from (select deptno,count() c from emp e group by e.deptno));
–在where中 select的结果为多行可以作为数值使用
select * from emp e where e.sal >=all(select sal from emp);
–最大值 >=all
–最小值 <=all
–不是最小 >any
–不是最大
–in 和 exists
select distinct deptno from emp e where e.sal<2000;
select * from dept d where d.deptno /not/ in (select distinct deptno from emp e where e.sal<2000);
select * from dept d where /not/ exists (select * from emp e where e.sal<2000 and e.deptno=d.deptno);

–显示 scott.dept中符合要求的部门信息
–要求 在scott.emp中员工最多的部门
–写的最短
select * from dept d where d.deptno=
(select deptno from emp e group by e.deptno having count(*)>= all
(select count(*) from emp e group by e.deptno));
select e.deptno,count() from emp e group by e.deptno having count()=max(count(*));

select * from emp;
select * from dept;
–select count(*) from emp,dept;
–内连接
–相等链接
select * from emp e,dept d where e.deptno=d.deptno ;
select * from emp e inner join dept d on e.deptno=d.deptno ;
select * from emp e join dept d on e.deptno=d.deptno ;
–比较内连接方式
select d.dname,count(*) from emp e,dept d where e.deptno=d.deptno and d.deptno>10 group by d.dname order by d.dname;–正确
select d.dname,count(*) from emp e,dept d where d.deptno>10 and e.deptno=d.deptno group by d.dname order by d.dname;–错误
–内联中where条件的排序优化，靠左写连接条件后执行，靠右写筛选条件先执行

select d.dname,count(*) from emp e join dept d on e.deptno=d.deptno where d.deptno>10 group by d.dname order by d.dname;
–不等链接
select * from salgrade;
select * from emp e,salgrade s where e.sal between s.losal and s.hisal;
–自连接
select * from emp e1,emp e2 where e1.mgr=e2.empno order by e1.empno;

select * from emp e1 order by e1.empno;
–外联
–左关联
–通用写法
–每个人的上级是谁
select * from emp e1
left join emp e2 on e1.mgr=e2.empno
order by e1.empno;

–右关联
–每个人有哪些下级
select * from emp e1
right join emp e2 on e1.mgr=e2.empno
order by e1.empno;

–全关联
select * from emp e1
full join emp e2 on e1.mgr=e2.empno
order by e1.empno;

–oracle专业写法
–左关联
select e1.* from emp e1,emp e2 where e1.empno=e2.mgr(+) and e2.empno is null;
select distinct e1.* from emp e1,emp e2 where e1.empno=e2.mgr(+) and e2.empno is not null;
–右关联
select * from emp e1,emp e2 where e1.mgr(+)=e2.empno;
–全关联没有单独的写法