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

一、普通多表查询

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