Orcale_03

七、多表联查

（1）定义

多表联查：将多张表根据某种条件连接起来

（2）分类

1.内连接：

• 笛卡尔积连接
• 等值连接
• 不等值连接

2.外连接：

• 左外连接
• 右外连接

3.自连接：

• 自己与自己连接

从语法分：SQL92 SQL99

SQL92

（3）内连接

1.笛卡尔积连接：没有任何条件将两张表直接连接到一起

语法：select 别名.字段

from 表1 别名1,表2 别名2

select * from emp,dept;

笛卡尔积 = A表记录数*B表记录数

问题：数据不准确

2.等值连接

语法：select 别名.字段

from 表1 别名1,表2 别名2

where 表1.公有字段 = 表2.公有字段

--1.2等值连接
--原来：它是在笛卡尔积基础上提供了一个公共的连接条件
SELECT * FROM emp,dept
WHERE emp.deptno = dept.deptno;
​
--查询几个字段
--公有字段需要别名
SELECT ename,job,sal,emp.deptno,dname,loc
FROM emp,dept
WHERE emp.deptno = dept.deptno;
​
--给多个表起别名
SELECT e.ename,e.job,e.sal,e.deptno,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno = d.deptno AND sal > 2000;

3.不等值连接

语法：select 别名.字段

from 表1 别名1,表2 别名2

where 条件1 > <= != 条件2

--1.3不等值连接
--原理：用A表里的一个字段，到B表里的一个范围内左判断，满足范围内的查询出来
--查询员工姓名，工作，工资，工资等级
SELECT e.ename,e.job,e.sal,s.grade
FROM emp e,salgrade s
WHERE e.sal > s.losal AND e.sal < s.hisal
ORDER BY s.grade DESC;
​
--工资等级表
SELECT * FROM salgrade;

（4）自连接

原理：自己和自己连接

语法：select 别名.字段

from 表1 别名1,表2 别名2

where 表1.公有字段 = 表2.公有字段

--1.4自连接
--原理：自己与自己连接
--案例：查询员工的姓名，工作，薪水，以及他的领导是谁
SELECT * FROM emp;
​
SELECT e.ename,e.job,e.sal,e2.ename
FROM emp e,emp e2
WHERE e.mgr = e2.empno;

（5）外连接

左连接：将左表中不满足等值连接条件的数据显示出来，需要在右表上添加(+)

--案例：查询员工姓名，工作，薪水，部门名称以及没有部门的员工信息
SELECT e.ename,e.job,e.sal,d.deptno,d.dname
FROM emp e,dept d
WHERE e.deptno = d.deptno(+);

右连接：将右表中不满足等值连接条件的数据显示出来，需要在左表上添加(+)

--案例：查询员工姓名，工作，薪水，部门名称以及没有员工的部门
SELECT * FROM dept;
​
SELECT e.ename,e.job,e.sal,d.deptno,d.dname
FROM emp e,dept d
WHERE e.deptno(+) = d.deptno;
--左右连不可以一起使用
SELECT e.ename,e.job,e.sal,d.deptno,d.dname
FROM emp e,dept d
WHERE e.deptno(+) = d.deptno(+);

SQL99

--【SQL99语法】
--笛卡尔积/集连接：CROSS JOIN
SELECT * FROM emp CROSS JOIN dept;
​
--等值连接1：使用关键字
SELECT * FROM emp NATURAL JOIN dept;
​
--等值连接2：使用关键字 inner join using(公共字段)
SELECT * FROM emp INNER JOIN dept USING(deptno);
​
--万能写法：等值连接与不等值连接 [inner] join on
SELECT * 
FROM emp e INNER JOIN dept d
ON e.deptno = d.deptno;
​
--不等值连接
SELECT * FROM 
emp e JOIN salgrade s
ON e.sal > s.losal AND e.sal < s.hisal;
​
--外连接
​
--左外连接：left join using
SELECT * FROM emp e LEFT JOIN dept d
USING(deptno);--ON e.deptno = d.deptno;
​
--右外连接:right join on
SELECT *  FROM emp e RIGHT JOIN dept d
ON e.deptno = d.deptno;
​
--全连接:把两个表不满足的条件的数据全部筛选出来
SELECT * FROM
emp FULL JOIN dept
USING(deptno);
​​​​​​​

 （6）子查询

1、子查询：一个查询语句的结果作为另一个查询语句的条件或者一个子表来使用

2、作用：当筛选的条件不确定的时候使用

3、语法：SELECT *,字段,表达式

                 FROM 表 或(子查询)

                 WHERE 字段 > < != (子查询)

4、执行顺序：先执行()里的子查询获得结果，再给主查询使用

注意：子查询必须写在()中

多行子查询

• 小于all()：小于小的
• 大于all()：大于大的
• 大于any()：与子查询的每一个值进行比较，只要大于任意一个子查询的值，就是满足条件的，大于最小的
• 小于any()：与子查询的每一个值进行比较，只要小于任意一个子查询的值，就是满足条件的，小于最大的
• in：在范围内

  --单行子查询：子查询结果是一个单个值
  
  select * from emp where sal > (select sal from emp where ename = 'BLAKE');
  
  --多行子查询
  --查询其他部门比20号部门的任一工资低的员工信息
  --any():比较任何一个 <any :小于最大的  >any ：大于最小的
  select * from emp 
  where deptno <> 20 and sal < any(select sal from emp where deptno = 20);
  
  select * from emp 
  where deptno <> 20 and sal < (select max(sal) from emp where deptno = 20);
  
  --all():比所有都
  --查询其他部门比30号部门的所有人工资都低的员工信息
  select * from emp where deptno <> 30 and sal < all(select sal from emp where deptno = 30);
  
  select * from emp 
  where deptno <> 30 and sal < (select min(sal) from emp where deptno = 30);
  
  --in:在范围内
  --列出某些雇员的姓名和薪水
  select ename,sal from emp 
  where sal in (select sal from emp where deptno = 30);
   
  

--子查询：一个查询语句的结果作为另一个查询语句的条件或者一个子表来使用
--作用：当筛选的调价不确定的时候使用
--语法：select * ,字段，表达式
--      from 表 或（子查询）
--      where 字段 > < !=(子查询)
--执行顺序:先执行()里的子查询获得结果，再给主查询使用
       
--案例：查询工资比BLAke高的员工信息
--第一步：查出BLAKE的工资是多少
select sal from emp where ename ='BLAKE';--2850

--第二步：查询工资大于2850的员工信息
select * from emp where sal > 2850;

--综合写法
select * from emp where sal > (select sal from emp where ename ='BLAKE');

--案例2：查询名为CLARK的管理者信息
select * from emp where empno =(select mgr from emp where ename='CLARK' );

--案例3：查询工资高于平均工资员工的名字和工资
select ename ,sal from emp
where sal > (select avg(sal) from emp);

--案例4：查询和BLAKE属于同一部门且工资比她低的员工信息
select * from emp where sal < (select sal from emp where ename='BLAKE' )
and deptno = (select deptno from emp where ename='BLAKE');


--案例5：查询工资最高的员工信息
select * from emp where sal =(select max(sal) from emp);

--案例6：查询最低工资大于20号部门最低工资的部门编号和最低工资
select deptno,min(sal) from emp
group by deptno 
having min(sal)> (select min(sal) from emp where deptno  =20 );

（7） 分页显示

1.作用：当一个表中的数据量特别大的时候，需要进行分页显示提升用户的体验度

2.rownum关键字

作用：是oracle对外提供的自动给查询结果编号的一个关键字

语法：select rownum r,e.*

from emp e

where rownum <= 数值;

注意：1.使用rownum字段，表需要添加别名

           2.r别名不能用在where关键字后面

           3.rownum不能做大于或大于等于的操作

           4.rownum字段会跟着排序规则位置发生变化

3.分页查询的公式

• m：每页显示的数据多少条
• n：页码

不考虑排序的分页公式

SELECT * FROM (SELECT ROWNUM r,e.* FROM emp e WHERE ROWNUM <= m*n) a
WHERE r > m*n-m;

 考虑排序的分页公式

SELECT * FROM (SELECT ROWNUM r,t.* FROM (SELECT * FROM emp ORDER BY sal DESC) t
WHERE ROWNUM <=m*n) a
WHERE r > m*n-m;

实例：

--分页显示

--案例：查询员工的所有信息
select * from emp;
/*
注意：
1.使用rownum字段，表需要添加别名
2.r别名泵用在where 关键字后面
3.rownum 不能做大于等于或大于的操作
4.rownum 字段会跟着排序给规则变化  
*/

--rownum:编号效果（多了一个R 字段）
select rownum r, e.* from emp e
where rownum<=5;--显示前5条

--查询 6-10 条记录
select rownum r,e.* from emp e
where rownum >=6 and rownum <= 10;
/*对关键字rownum不可以查询大于等于的字段和between and
  
*/

--第一步：查询前10条
select rownum r,e.* from emp  e where rownum <= 10;
--第二步：把第一步的结果当作一个新表重新擦汗寻
select * from (select rownum r,e.* from emp  e where rownum <= 10) a
where r > 5;

--每页5条数据，差第三页信息  5*3
select rownum r,e.* from emp e where rownum <=15

select * from (select rownum r,e.* from emp e where rownum <=15)--5*3
where r>10;--5*3-5

select rownum r,e.* from emp e order by sal desc;

--带有排序的分页
select * from (select rownum r,t.* from (select * from emp order by sal desc) t
where rownum <=15) a
where r > 10;

--查询出king所在部门的工作年限最大的员工名字
select deptno from emp where ename ='KING';
select * from emp where deptno = (select deptno from emp where ename ='KING')
order by hiredate;

select rownum ro,w.ename from (select * from emp where deptno = (select deptno from emp where ename ='KING')
order by hiredate) w
where rownum = 1;


--查询出工资成本最高的部门的部门号和部门名称
select x.deptno,d.dname
from (select deptno ,sum(sal) from emp group by deptno order by sum(sal)desc) x,dept d
where x.deptno = d.deptno and rownum =1;