Oracle学习笔记 -- day05 多表查询、连接查询、子查询、分页、行转列、集合运算

本文链接：https://blog.youkuaiyun.com/wingzhezhe/article/details/77574952

一、普通多表查询

1、练习

范例：查询员工表和部门表

select * from emp e,dept d --56 --笛卡尔积
select * from emp e;  --14
select * from dept d; -- 4
select * from emp e,dept d  where e.deptno=d.deptno

范例：查询出雇员的编号，姓名，部门的编号和名称，地址

select e.empno,e.ename,d.deptno,d.dname,d.loc from emp e,dept d  where e.deptno=d.deptno

范例：查询出每个员工的上级领导 --自关联自连接

--(员工编号、员工姓名、员工部门编号、员工工资、领导编号、领导姓名、领导工资)

select e1.empno,e1.ename,e1.deptno,e1.sal, e2.empno,e2.ename,e2.sal from
 emp e1, --员工表
 emp e2 --领导表
 where e1.mgr=e2.empno

范例: 在上一个例子的基础上查询该员工的部门名称

select e1.empno,e1.ename,d.dname,e1.sal, e2.empno,e2.ename,e2.sal from
 emp e1, --员工表
 emp e2, --领导表
 dept d --部门表
 where e1.mgr=e2.empno and e1.deptno =d.deptno

范例：查询出每个员工编号，姓名，部门名称，工资等级和他的上级领导的姓名，工资等级

select e1.empno,e1.ename,d.dname,e1.sal,s1.grade, e2.empno,e2.ename,e2.sal,s2.grade from
 emp e1, --员工表
 emp e2, --领导表
 dept d, --部门表
 salgrade s1, --工资等级表
 salgrade s2
 where e1.mgr=e2.empno and e1.deptno =d.deptno
 and e1.sal between s1.losal and s1.hisal
 and e2.sal between s2.losal and s2.hisal

二、连接查询

1、连接查询的语法

right join on 右外连接，以右边的表为全量表

left join on 重点，左外连接，以左边的表为全量表

(+)：oracle中独有的关键词，(+)在=号左边为右外连接，在=号右边为左外连接

--关键词：right join    on
--范例：查询出所有员工的上级领导
select e1.empno,e1.ename,e1.deptno,e1.sal, e2.empno,e2.ename,e2.sal from
 emp e1,  emp e2
 where e1.mgr=e2.empno(+) -- (+)在右边，当前的查询叫做左外连接
 
 其中：emp e1  --全量表
	   emp e2  --非全量表
 
--关键词：left join  on     重点

select e1.empno,e1.ename,e1.deptno,e1.sal, e2.empno,e2.ename,e2.sal 
 from emp e1 left join emp e2  on e1.mgr=e2.empno
 

--范例：查询出所有的部门下的员工，要求把没有员工的部门也展示出来
select * from emp e;  --14
select * from dept d; -- 4

--正确SQL
	--(+)形式
select * from emp e,dept d   --56  
	where e.deptno(+) -- 非全量表
			=
		  d.deptno  --全量表
	--left join on 形式
select * from dept d left join emp e on e.deptno=d.deptno

三、子查询

1、子查询优点

（1）、子查询可以返回单行单列值

（2）、子查询可以返回单行多列值

（3）、子查询可以返回多行多列值（通常把子查询的结果当成表来使用）

（4）、使用子查询可以提高查询效率

（5）、子查询可以出现在sql语句的任何位置

2、各种范例

范例：查询比SCOTT工资高的员工

select * from emp where sal>(select sal from emp where ename='SCOTT')

范例：查询职位是经理并且工资比7782号员工高的员工

select sal from emp where empno=7782

select * from emp where job='MANAGER' and sal>(select sal from emp where empno=7782)

范例：查询工资最低的员工

select min(sal) from emp

select * from emp where sal=(select min(sal) from emp)

范例：查询部门最低工资大于 30号部门最低工资的结果

select deptno,min(sal) from emp group by deptno
 having min(sal)>(select min(sal) from emp where deptno=30)

范例：查询出和SCOTT同部门并且同职位的员工

select deptno,job from emp where ename='SCOTT'

select * from emp where deptno=(select deptno from emp where ename='SCOTT')
 and job=(select job from emp where ename='SCOTT');

select * from emp where (deptno,job,sal)=(select deptno,job,sal from emp where ename='SCOTT');

范例：查询每个部门的最低工资对应的雇员信息(包括部门名称)

select deptno,min(sal) from emp group by deptno

select e.*,d.dname from emp e,
(select deptno,min(sal) minsal from emp group by deptno) t,
dept d
where e.sal=t.minsal and e.deptno=t.deptno and e.deptno=d.deptno

范例：查询出不是领导的员工

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

四、分页查询

1、分页关键词

rowid ：每行数据的物理地址

rownum：每行数据的序号序号是随着查询产生的，rownum不能使用>号

select  e.*,rownum from (select * from emp order by sal desc) e where rownum<4

2、oracle分页思想

--把emp分页显示   每页显示3条
--第一页
select  e.*,rownum from (select * from emp order by sal desc) e where rownum<4 
--第二页  7566  7698  7782   rownum 不支持大于号
select  * from (select  e.*,rownum r from (select * from emp order by sal desc)
 e where rownum<7 ) t where t.r>3
 -- 第三页
 select  * from (select  e.*,rownum r from (select * from emp order by sal desc)
 e where rownum<10 ) t where t.r>6

3、其他练习题

范例：找到员工表中薪水大于本部门平均薪水的员工

select e.* ,t.avgsal from emp e,
(select deptno,avg(sal) avgsal from emp group by deptno) t
where e.deptno=t.deptno and e.sal>t.avgsal

五、行转列

1、别名

select 2 "1980" from dual

2、行转列语法

sum(decode(列,列的值,显示的值))

3、范例：统计每年入职的员工个数

select sum(t.counts) "Total" ,
  sum(decode(years,'1980',counts)) "1980",
  sum(decode(years,'1981',counts)) "1981",
  sum(decode(years,'1982',counts)) "1982",
  sum(decode(years,'1987',counts)) "1987"
  from (select to_char(hiredate,'yyyy') years,count(*) counts
  from scott.emp group by to_char(hiredate,'yyyy')) t

六、集合运算

1、范例

范例：工资大于1500，或者是20号部门下的员工

select * from emp where sal>1500
union --all--并集
select * from emp where deptno=20;

select * from emp where sal>1500 or deptno=20;

范例：工资大于1500，并且是20号部门下的员工

select * from emp where sal>1500
intersect  --交集
select * from emp where deptno=20;

select * from emp where sal>1500 and deptno=20;

范例：1981年入职的普通员工（不包括总裁和经理）

select * from emp where to_char(hiredate,'yyyy')='1981'
minus  --减  差集  补集
select * from emp where job='MANAGER' or job='PRESIDENT'

2、注意：

集合运算时两个结果集只要是列的数量相等并且对应的列数据类型一致就可以做集合运算

select empno,ename from emp
union
select deptno,dname from dept