内连接

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 的别名