Oracle(二)--多表查询&子查询

一、多表查询

1.1 笛卡尔集

select deptno,avg(sal) from emp group by deptno;

1.2 内连接

• 隐式内连接

隐式内连接如下：

--查询员工编号、员工姓名、部门编号、经理编号、经理的姓名
select e1.empno, e1.ename, e1.deptno, m1.empno, m1.ename from emp e1,emp m1 where e1.mgr = m1.empno;

--查询员工编号、员工姓名、部门编号、部门名称、经理编号、经理的姓名
select e1.empno, e1.ename, e1.deptno,d1.dname, m1.empno, m1.ename 
from emp e1,emp m1,dept d1 where e1.mgr = m1.empno and e1.deptno = d1.deptno;

• 显示内连接

显示内连接如下，inner关键字， on连接条件

select e1.ename, d1.loc from emp e1 inner join dept d1 on e1.deptno = d1.deptno;

1.3 外连接

• 左外连接

使用 left outer join关键字，显示所有的左表纪录，右表无数据则为空

select * from emp e1 left outer join dept d1 on e1.deptno = d1.deptno;

• 右外连接

使用right outer join关键字，右表所有的数据都显示，左边如果没有关联数据则显示为空

select * from emp e1 right outer join dept d1 on e1.deptno = d1.deptno;

二、子查询

子查询主要用于解决复杂的查询语句，形式为查询语句中嵌套查询语句

如查询最高工资的员工信息

select * from emp where sal = (select max(sal) from emp);

2.1 单行子查询

使用关系运算符做连接： > / >= / < / <= / != / <> / =

上例即为单行子查询

– 查询每个部门的最低工资和他所在部门的信息

select * from emp e1, (select deptno, min(sal) minisal from emp group by deptno) t1, dept d1 
where e1.deptno = t1.deptno and e1.sal = t1.minisal and d1.deptno = e1.deptno;

2.2 多行子查询

使用逻辑运算符

–查询是不领导的信息

select * from emp where empno not in(select mgr from emp where mgr is not null);

exists查询

数据量较大时，查询十分高效。存在则返回true，不存在则返回false

--查询有员工的部门信息
select * from dept d1 where exists(select * from emp e1 where e1.deptno = d1.deptno);

三、其他

3.1 rownum

伪列：rownum，系统自动生成的一列，表示行号，建议不做大于判断

--工资最高的前三名
select rownum, t1.* from (select e1.* from emp e1 order by sal desc) t1 where rownum < 4;

因为数据库的执行顺序select 在 order by前，因此需使用子查询来达到目的

3.2 查询员工表中薪水大于本部门平均薪水的员工信息

select * from emp e1, (select deptno, avg(sal) avgsal from emp group by deptno) t1 
where e1.deptno = t1.deptno and e1.sal > t1.avgsal;