–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