oracle连表查询

SQL92的语法规则的缺点：

• 语句过滤条件和表连接的条件都放到了where子句中 。
• 当条件过多时，联结条件多，过滤条件多时，就容易造成混淆

表连接的种类

内连接：（inner join）

• 返回两个表都对应的数据

外连接：

• 左外连：（left join）以左表为主，右表与其对应
• 右外连：（right join）以右表为主，左表与其对应
• 全外连：（full join）以左右表为主，左右表数据

交叉连接：（cross join）

• 笛卡尔集

自然连接：（Natural join）

• 自己找关系

Using子句

• 如果不希望参照被连接表的所有同名列进行等值连接，自然连接将无法满足要求，可以在连接时使用USING子句来设置用于等值连接的列（参照列）名。
• using子句引用的列在sql任何地方不能使用表名或者别名做前缀

On子句

• 自然连接的条件是基于表中所有同名列的等值连接
• 为了设置任意的连接条件或者指定连接的列，需要使用ON子句
• 连接条件与其它的查询条件分开书写
• 使用ON 子句使查询语句更容易理解

子查询：

• 一个查询的结果作为另一个查询的条件

单行子查询单行子查询只返回一行记录

对单行子查询可使用单行记录比较运算符

< 、  > 、  = 、 >=、  <= 、 <>、!=

多行子查询

• 多行子查询返回多行记录
• 对多行子查询只能使用多行记录比较运算符
• ALL 和子查询返回的所有值比较
• ANY 和子查询返回的任意一个值比较
• IN 等于列表中的任何一个

oracle连表查询 代码

--92连表查询




--查询员工信息和部门信息
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
--外连接
--左外连
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;




--99连表查询




--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 自定义连接条件 join 后紧跟 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, e2.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, e2.ename, d1.dname, 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 sal from emp where ename='CLARK';
select *from emp where sal >2450;
--子查询
--查询工资大于CLARK工资的员工信息
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 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 SAL>(select sal 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 or mgr!='');
--找出部门编号为20的所有员工中收入最高的职员
select * from emp where sal=(select max(sal) from emp where deptno='20');
--查询每个部门平均薪水的等级 
select a.deptno, grade
  from salgrade, (select deptno, avg(sal) b from emp group by deptno) a
 where losal < a.b
   and hisal >= a.b