Oracle-多表查询

内连接

select * from 表名 (别名) (inner) join 表名 (别名) on 连接条件 连接emp表和dep表（emp表中与dep表中的did是关联字段，这样查询did会出现2次）： select * from emp,dep where emp.did = dep.did 相当于 select * from emp inner join dep on emp.did=dep.did 相当于 select * from emp join dep on emp.did=dep.did 如果只想出现一次关联字段，需要像下书写： select emp.*,dep.name,dep.daddress from emp join dep on emp.did=dep.did 给表指定别名 select * from 表名 别名 inner join 表名 别名 on 连接条件 -- 需要显示的指明匹配条件，查询结果两个关联列因为名称可能不同，所以全部显示 select * from dept d join emp e on d.deptno = e.deptno 

注意：内连接的 inner 可以省略，直接 ... join ... on ... 默认内连接；查询指定字段时，如果多张表有这个字段，需要指明是那张表的字段

外连接

select * from 表名 (别名) left/right (outer) join 表名 (别名) where 连接条件

左外连接

以左表为参考，左表中数据全部输出，右表与左表数据匹配的数据才显示

select * from emp e left outer join dep d on e.did = d.did 相当于 select * from emp e,dep d where e.did = d.did(+) 以没有（+）的一边为参考（这里是emp），没有（+）的记录全部输出，有（+）的记录，只有左右表相互匹配才输出 

右外连接

以右表为参考，右表数据全部输出，右表与左表数据匹配的数据才显示

select * from emp e right outer join dep d on e.did = d.did 相当于 select * from emp e,dep d where e.did(+) = d.did 以没有（+）的一边表为参考（这里是dep），没有（+）的记录全部输出，有（+）的记录，只有左右表相互匹配后才输出 select * from dep d right outer join emp e on e.did = d.did

注意：外连接的 outer 可以省略，直接 ... left/right join ... on ...

子查询

非相关子查询 -- 单行子查询

子查询结果只有一行，外查询可以直接使用 = > < 运算符进行比较
子查询可以独立执行 先进行子查询再进行外查询 子查询不依赖于外查询

--查得比“CR7”工资高的员工的信息 select * from emp where salary > (select salary from emp where ename = 'CR7') --查询工资高于平均工资的雇员名字和工资 select ename,salary from emp where salary > (select avg(salary) from emp) --查询职务和CR7相同，比CR7雇佣工资高的员工 ---- 查询职务 select ejob from emp where ename = 'CR7' - ---- 查询工资 select esalary from emp where ename = 'CR7' ---- 综合起来 select * from emp where job = (select ejob from emp where ename = 'CR7') and hiredate > (select esalary from emp where ename = 'CR7' )

非相关子查询 -- 多行子查询

子查询结果有多行

外查询进行比较需要使用 all（所有），ang（任意），in，not in

--查询比任意一个‘java开发’工资高的员工（查询比‘java开发’最高工资低的员工） select * from emp where esalary < any(select esalary from emp where ejob='java开发') --相当于 select * from emp where esalary < (select max(esalary) from emp where ejob='java开发') > < ang(...) 查询结果是 “比查出来数据中最大的小”， > ang(...) 查询结果是 “比查出来数据中最小的大”，任意一个用ang() --查询比任何一个‘java开发’工资高的员工（查询比‘java开发’最低工资低的员工） select * from emp where esalary < all(select esalary from emp where ejob='java开发') --相当于 select * from emp where esalary < (select min(esalary) from emp where ejob='java开发') >< all(...) 查询结果是 “比查出来数据中最小的小” ， >all(...) 查询结果是“比查出来数据中最大的大”，所有的用all

相关子查询 -- 子查询依赖于外查询

子查询不能单独运行
先执行外查询再执行内查询

--查询本部门最高工资的员工 ---- 查询 部门 10 最高工资的 员工 select * from emp where deptno = 10 and sal = (select max(sal) from emp where deptno = 10) ---- 查询 部门 20 最高工资的 员工 select * from emp where deptno = 20 and sal = (select max(sal) from emp where deptno = 20) ---- 查询 部门 20 最高工资的 员工 select * from emp where deptno = 30 and sal = (select max(sal) from emp where deptno = 30) ---->> 由上面可以知道查询具体部门的最高工资 depno 是相同的，由此可以得到所有部门最高工资： select * from emp e1 where sal = (select max(sal) from emp e2 where e2.deptno = e1.deptno) select * from emp e1 where sal = (select max(sal) from emp where deptno = e1.deptno) --查询工资高于其所在部门的平均工资的员工 ---- 查询 10 部门员工 高于 10 部门平均工资 的员工 select * from emp where deptno = 10 and sal > (select avg(sal) from emp where deptno = 10) ---- 查询 20 部门员工 高于 30 部门平均工资 的员工 select * from emp where deptno = 20 and sal > (select avg(sal) from emp where deptno = 20) ---- 查询 30 部门员工 高于 30 部门平均工资 的员工 select * from emp where deptno = 30 and sal > (select avg(sal) from emp where deptno = 30) ---->> 由上面可以知道查询具体部门的高于平局工资员工 depno 是相同的，由此可以得到所有部门高于平局工资员工： select * from emp e1 where sal > (select avg(sal) from emp where deptno = e1.deptno) 

注意：看懂了上面两个例子就看懂了相关子查询。

总结

--子查询 : 一个语句包含多个select

-- -- 不相关子查询： 子查询不依赖外查询，可以独立运行 ， 先内后外

-- -- 相关子查询： 子查询依赖外查询，不可以独立运行， 先外后内

-- -- -- 单行子查询： 子查询返回一行结果， 可以直接使用 = > < 等

-- -- -- 多行子查询： 子查询返回多行结果， 需要借助 in ang all

伪列 rowid rownum

----实际表附加的列，叫伪列，像表中的列一样但是表中不存储

----伪劣只用来查询，不能进行增删改

rowid

---- 在表中每一行数据都有一个物理地址，rowid返回该行的物理地址

---- 作用：快速定位某一行，唯一的，自动分配的，不重复

select rowid,ename,ejob from emp

rownum

----查询的结果集，rownum为每一行记录标识一个行号，第一行返回1，第二行返回2 ......

----作用：限制查询的行数，分页

select rownum,ename,ejob,esalary from emp

查询员工表中前五名员工的姓名，工作，工资 select rownum,ename,ejob,esalary from emp where rownum <=5 查询员工表中工资最高的前五名员工的姓名，工作，工资 select rownum,ename,ejob,esalary from emp where rownum <=5 order by esalary desc ----先按工资排序 select rownum,ename,ejob,esalary from emp order by esalary desc ----再进行筛选 select rownum,r.* from (select rownum,ename,ejob,esalary from emp order by esalary desc) r where rownum <=5 --查询员工表中第3条到第6条员工的姓名，工作，工资 select r.r,e.ename,e.ejob,e.esalary from emp e,(select rownum r,eid from emp) r where e.eid = r.eid and r.r between 3 and 6 select * from (select rownum r,eid,ename,ejob,esalary from emp) where r between 3 and 6 --相当于 select * from (select rownum r,eid,ename,ejob,esalary from emp where rownum <=6) <font size=2>注意：where r >= 3 -- 子表中 rownum 必须用别名，因为外部查询语句不能识别内部的 rownum ，但是可以识别内部 rownum 的别名