Oracle初级学习

–92多表连接查询

–查询员工信息和部门信息

select * from emp e, dept d where e.deptno = d.deptno;

–查询工资大于3000的员工姓名 工资和部门名称

select * from emp;

select e.ename, e.sal, d.dname

from emp e, dept d

where e.deptno = d.deptno

and e.sal > 3000;

–查询所有员工姓名 和部门名称

select e.*, d.deptno from emp e, dept d where e.deptno = d.deptno;

–非等值查询

–查询员工的工资等级

select *

from emp e, salgrade s

where e.sal <= s.hisal

and e.sal > s.losal select * from emp;

–外连接–

–左外连

update emp set deptno = null where empno = 9468;

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;

–cross jion 交叉连接(笛卡尔积)

select * from emp e cross join dept d;

– natural jion 自然连接

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 jion 全连　去两个表的所有数据

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.empno = d1.deptno

left join emp e2

on e1.mgr = e2.empno

left join dept d2

on e2.deptno = d2.deptno

select sal from emp where ename = ‘CLARK’;

select * from emp where sal>2450;

–子查询

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’);

– 多行子查询 查询和s,s,c在同一个部门的员工信息并不包含s,s,c;

select * from emp where

deptno in(select distinct deptno from emp where ename in(‘SCOTT’,’SMITH’,’CLERCK’))

and ename not in(‘SCOTT’,’SMITH’,’CLERCK’);

–思考：查询工资最高的雇员名字和工资。

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 sal>(select sal from emp where ename = ‘SCOTT’)

or hiredate <(select hiredate from emp where ename = ‘SCOTT’);

– 多行查询

–查询工资低于任何一个“CLERK”的工资的雇员信息。

select * from emp where

sal