初学oracle 连表查询

-- SQL92连表查询

-- 等值查询
-- 查询员工信息和部门信息
select * from emp e,dept d
where e.deptno = d.deptno
-- 查询员工姓名，部门名称
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno
-- 查询员工所有信息和部门名称
select e.*,d.dname from emp e,dept d where e.deptno = d.deptno
-- 查询工资大于3000的员工姓名，工资和部门名称 
select e.ename,e.sal,d.dname from emp e,dept d where e.deptno = d.deptno
and e.sal>3000

-- 非等值查询
-- 查询员工的工资等级
select * from emp e,salgrade s
where e.sal<=s.hisal and e.sal>s.losal
update emp set deptno=null where empno=1
-- 外连接
-- 左外连接 
select * from emp e,dept d
where e.deptno = d.deptno(+);
-- 右外连接
select * from emp e,dept d
where e.deptno(+) = d.deptno;
-- 自连接 自己连自己
-- 查询员工姓名和员工的经理姓名
select e1.ename,e2.ename from emp e1 ,emp e2
where e1.mgr = e2.empno;
-- 查询员工姓名和员工的经理姓名和其经理的姓名
select e1.ename,e2.ename,e3.ename from emp e1 ,emp e2,emp e3
where e1.mgr = e2.empno and e2.mgr = e3.empno;
-- SQL99连表查询
-- cross join交叉连接(笛卡尔积)
select * from emp e cross join dept d;
-- natural join 自然连接
select * from emp natural join dept; 
-- using
select e.ename,deptno from emp e join dept d 
using(deptno);
-- on 自定义连接条件
select * from emp e join dept d 
on e.deptno = d.deptno ;
select * from emp e1 join emp e2 on e1.mgr=e2.empno;
-- 查询员工的姓名和经理的姓名和其经理的姓名
select e1.ename, e2.ename, e3.ename
  from emp e1
  join emp e2
    on e1.mgr = e2.empno
  join emp e3
    on e2.mgr = e3.empno
-- inner join 两边都合法的数据
select * from emp e inner join dept d on e.deptno = d.deptno;
-- left join 以左表为主
select * from emp e left join dept d on e.deptno = d.deptno;
-- right join 以右表为主
select * from emp e right join dept d on e.deptno = d.deptno;
-- full join 全连 取两个表的所有数据
select * from emp e full join dept d on e.deptno = d.deptno;
-- 查询员工姓名和部门姓名和其经理的名称和经理的部门名称
select e1.ename, d1.dname, e2.ename, d2.dname
  from emp e1
  left join dept d1
    on e1.deptno = d1.deptno
  left join emp e2
    on e1.mgr = e2.empno
  left join dept d2
    on e2.deptno = d2.deptno;




-- 子查询
select * from emp where sal>(select SAL from emp where ename='CLARK');
-- 查询工资高于平均工资的雇员名字和工资。
select ename,sal from emp where sal>(select avg(sal) from emp);
-- 查询和SCOTT同一部门且比他工资低的雇员名字和工资。
select ename,sal from emp where 
deptno=(select deptno from emp where ename='SCOTT')
and sal<(select sal from emp where ename='SCOTT');
-- 多行子查询 
-- 查询和'SCOTT','SMITH','CLARK'在同一个部门的员工信息并不包含'SCOTT','SMITH','CLARK';
select * from emp where
deptno in(select distinct deptno from emp where ename in('SCOTT','SMITH','CLARK'))
and ename not in('SCOTT','SMITH','CLARK');
-- 查询工资最高的雇员名字和工资。
select ename,sal from emp where sal=(select max(sal) from emp);
-- 查询职务和SCOTT相同，比SCOTT雇佣时间早的雇员信息
select * from emp where job = (select job from emp where ename='SCOTT')
and hiredate<(select hiredate from emp where ename='SCOTT');
-- 查询工资比SCOTT高或者雇佣时间比SCOTT早的雇员的编号和名字
select empno,ename from emp where job = (select job from emp where ename='SCOTT')
or hiredate<(select hiredate from emp where ename='SCOTT');

-- 多行查询
-- 查询工资低于如何一个"CLERK"的工资的雇员信息
select * from emp where sal < any(select sal from emp where job = 'CLERK');
-- 查询工资比所有的"SALESMAN"都高的雇员的编号、名字和工资
select empno,ename,sal from emp where sal > all(select sal from emp where job = 'SALESMAN');
-- 查询部门20中职务同部门10的雇员一样的雇员信息
select * from emp where job in (select job from emp where deptno = 10)and deptno = 20;
-- 查询在雇员中有哪些人是经理人
select * from emp where empno in (select distinct mgr from emp where mgr is not null);
-- 找出部门编号为20的所有员工中收入最高的职员
select * from emp where sal = (select max(sal) from emp where deptno = 20)and deptno = 20;
-- 查找每个部门平均薪水的等级
select deptno, grade
  from salgrade, (select deptno, avg(sal) b from emp group by deptno) a
 where losal < a.b
   and hisal >= a.b;