Day58-Oracle02 - 内连接、外连接、子查询(exists用法，关联非关联子查询)、课堂练习（行列转换）、rownum和rowid

笛卡尔积 和内连接 外连接

实际上是两张表的乘积,查询结果没有实际意义 select * from emp,dept;

内连接-等值内连接(隐式) select * from emp,dept where emp.deptno = dept.deptno; 内连接-不等值内连接 select * from emp,dept where emp.deptno <> dept.deptno; 内连接-显式内连接 select * from emp inner join dept on emp.deptno = dept.deptno; 多表查询时,习惯性的去取别名，数据库在后台进行多表操作的时候也会自动起别名进行查询。 select * from emp e,dept d where e.deptno = d.deptno; 自连接：自己连接自己 select * from emp e1,emp m1 where e1.mgr = m1.empno;

---

内连接查询练习

-- 查询员工编号,员工姓名,经理编号, 经理姓名 select e1.empno,e1.ename,e1.mgr,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno; -- 查询员工编号,员工姓名,员工的部门名称,经理编号, 经理姓名 select e1.empno,e1.ename,d.dname,e1.mgr,e2.ename from emp e1,emp e2,dept d where e1.mgr = e2.empno and e1.deptno = d.deptno; -- 查询员工编号,员工姓名,员工的部门名称,经理编号, 经理姓名,经理的部门名称 select e1.empno,e1.ename,d1.dname,e1.mgr,e2.ename,d2.dname from emp e1,emp e2,dept d1,dept d2 where e1.mgr = e2.empno and e1.deptno = d1.deptno and e2.deptno = d2.deptno; //这里是员工和经理两个对象，分别查询两个对象的部门名称，需要引入两次部门表，因为员工和他的经理可能是在同一个部门，也可能在不同的不能，如果只引用一个部门表的话会丢失掉员工和经理在不同部门的数据 -- 查询员工编号,员工姓名,员工的部门名称,员工工资等级,经理编号, 经理姓名,经理的部门名称 select e1.empno,e1.ename,d1.dname,s.grade,e1.mgr,e2.ename,d2.dname from emp e1,emp e2,dept d1,dept d2,salgrade s where e1.mgr = e2.empno and e1.deptno = d1.deptno and e2.deptno = d2.deptno and e1.sal between s.losal and s.hisal; -- 查询员工编号,员工姓名,员工的部门名称,员工工资等级,经理编号, 经理姓名,经理的部门名称,经理工资等级 select e1.empno,e1.ename,d1.dname,s1.grade,e1.mgr,e2.ename,d2.dname,s2.grade from emp e1,emp e2,dept d1,dept d2,salgrade s1,salgrade s2 where e1.mgr = e2.empno and e1.deptno = d1.deptno and e2.deptno = d2.deptno and e1.sal between s1.losal and s1.hisal and e2.sal between s2.losal and s2.hisal; -- 查询员工编号,员工姓名,员工的部门名称,员工工资等级(一级),经理编号, 经理姓名,经理的部门名称,经理工资等级 select e1.empno,e1.ename,d1.dname, case s1.grade when 1 then '一级' when 2 then '二级' when 3 then '三级' else '四级' end, e1.mgr,e2.ename,d2.dname, case s2.grade when 1 then '一级' when 2 then '二级' when 3 then '三级' when 4 then '四级' else '顶级' end from emp e1,emp e2,dept d1,dept d2,salgrade s1,salgrade s2 where e1.mgr = e2.empno and e1.deptno = d1.deptno and e2.deptno = d2.deptno and e1.sal between s1.losal and s1.hisal and e2.sal between s2.losal and s2.hisal;

---

外连接查询练习

左外连接: 查询左表中所有的记录,如果右表没有对应的记录,则显示null left outer join .. on

select * from emp e left outer join dept d on e.deptno = d.deptno;

右外连接: 查询右表中所有的记录,如果左表没有对应的记录,则显示null right outer join .. on

select * from emp e right join dept d on e.deptno = d.deptno;

Oracle外连接特有写法: (+) : 没有对应的记录, + null 值

左外连接 select * from emp e, dept d where e.deptno = d.deptno(+);

右外连接 select * from emp e,dept d where e.deptno(+) = d.deptno;

–扩展: oracle特有, 全外连接 – full join select * from emp e full join dept d on e.deptno = d.deptno;

内连接和外连接的区别和关系：

---

子查询

概念: 查询语句中嵌套查询语句 作用: 用于解决复杂的查询需求

分类：

单行子查询 查询出来的结果只有一行 常用操作符: > >= = < <= != <> 多行子查询 查询出来的结果有多行 常用的操作符: in , not in , any , all, exists,

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

1）单行子查询 子查询出来的结果只有一行 常用操作符: > >= = < <= != <>

-- 查询出 比 雇员7654的工资 高,同时 和7788从事相同工作 的员工信息 select * from emp where sal>(select sal from emp where empno = 7654) and job = (select job from emp where empno = 7788) and empno != 7788; -- 查询每个部门最低工资的员工信息 和 他所在的部门信息 select * from emp e,dept d where sal in(select min(sal) from emp group by deptno) and e.deptno = d.deptno 或 select * from emp e1, (select deptno,min(sal) minsal from emp group by deptno) t1, dept d1 where e1.deptno = t1.deptno and e1.sal = t1.minsal and e1.deptno = d1.deptno;

2)多行子查询

常用的操作符: in , not in , any , all, exists, -- 查询出比10号部门任意员工薪资高的员工信息 select * from emp where sal > any(select sal from emp where deptno = 10) -- 查询出比10号部门所有员工薪资高的员工信息 select * from emp where sal > all(select sal from emp where deptno = 10) -- 查询是领导的员工信息 select * from emp where empno in (select mgr from emp where mgr is not null); select * from emp where empno = any(select mgr from emp where mgr is not null); -- 查询不是领导的员工信息 所有都满足 select * from emp where empno not in(select mgr from emp where mgr is not null); select * from emp where empno <>all(select mgr from emp where mgr is not null); -- 注意: 子查询中包含空值

3)exists用法 exists(查询语句): 存在 如果查询语句,有结果,则返回true,否则返回false ; in和exists 是可以替换去使用,如果数据量小,使用in高效, 如果数据量大,用exists效率高一些

例子：下面两条sql语句的查询结果都是为空记录 select * from emp where exists(select * from emp where empno=1234567); select * from emp where 1=2; 下面两条sql语句的查询结果都是查询出所有记录 select * from emp where exists(select * from emp where empno=7369); select * from emp where 1=1; -- 查询有员工的部门信息 select * from dept where deptno in(select distinct deptno from emp); select * from dept d where exists (select * from emp e where d.deptno = e.deptno);

4)关联子查询和非关联子查询 关联子查询: 子查询依赖外层查询，子查询不能够单独执行 先执行外层查询,然后再执行内层查询 非关联子查询: 子查询可以单独执行,不依赖外层查询条件，只查询能够单独进行执行 先执行子查询,再执行外层查询,内层子查询只执行一次 按照行划分: 单行子查询 多行子查询 按照出现的位置划分: select from 当作一张表处理,需要取别名 where having

4.1）select 后面接子查询

– 查询员工姓名和部门名称 select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno; select ename,(select dname from dept d where d.deptno=e.deptno) from emp e;

---

练习

1）练习一: 找到员工表中工资最高的前三名(只要前三条) — rownum

引入rownum的概念，rownum主要用在分页查询、查询前多少名 注意：sql语句的执行顺序如下： from .. where … group by .. having .. select ..rownum.. order by 是先加入rownum，然后再进行order by的，所有这样的话，如果存在排序的话，这样的执行顺序会让rownum失效；所以需要先将把进行排序，然后再加入rownum这个量 另外，rownum值不能够执行>这种条件的，查询不出数据，因为rownum是再查询出数据之后，才进行rownum的编号，默认起始从1开始；

select * from emp order by sal desc; rownum : 伪列 ,代表的是行号 rownum 默认起始值是 1 ,每查询出一条记录, rownum++ rownum :主要运用在分页查询 -- 查询行号小于3的所有记录 select rownum,emp.* from emp where rownum < 3; select rownum,emp.* from emp where rownum >=1; -- rownum没有顺序了 /* from .. where ... group by .. having .. select ..rownum.. order by */ select emp.* from emp order by sal desc; select rownum,tt.* from (select emp.* from emp order by sal desc) tt; -- 查询工资最高的前三名 select rownum,tt.* from (select emp.* from emp order by sal desc) tt where rownum <=3;

2）练习二:找到员工表中薪水大于本部门平均薪水的员工 select * from emp e,(select avg(sal) avgsal,deptno from emp group by deptno) tt where e.deptno = tt.deptno and e.sal > tt.avgsal;

-- 1.分组统计每个部门的平均薪水 select deptno , avg(sal) avgsal from emp group by deptno; -- 2. 连接查询结果 select * from emp e,(select deptno , avg(sal) avgsal from emp group by deptno) tt where e.deptno = tt.deptno and e.sal > tt.avgsal;

3）练习三:统计每年入职员工的个数 select count(1),to_char(hiredate,’yyyy’) from emp group by to_char(hiredate,’yyyy’)

select hiredate from emp; -- 只显示年 select to_char(hiredate,'yyyy') from emp; -- 分组统计每年入职个数 select to_char(hiredate,'yyyy') yy,count(1) cc from emp group by to_char(hiredate,'yyyy'); 4）行列转换 - 修改练习三中的行和列的显示，使其反转 -- 1. 先将1987竖起来 select case yy when '1987' then cc end from (select to_char(hiredate,'yyyy') yy,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt; -- 2. 去除1987中空值 select sum(case yy when '1987' then cc end) "1987" from (select to_char(hiredate,'yyyy') yy,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt; -- 3. 计算Total select sum(cc) "TOTAL" from (select to_char(hiredate,'yyyy') yy,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt; -- 4. 合并1987和total select sum(cc) "TOTAL", sum(case yy when '1987' then cc end) "1987" from (select to_char(hiredate,'yyyy') yy,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt; -- 5. 最终结果 select sum(cc) "TOTAL", sum(case yy when '1987' then cc end) "1987", sum(case yy when '1980' then cc end) "1980", sum(case yy when '1981' then cc end) "1981", sum(case yy when '1982' then cc end) "1982" from (select to_char(hiredate,'yyyy') yy,count(1) cc from emp group by to_char(hiredate,'yyyy')) tt;

---

rownum 和rowid

rowid: 伪列,代表的每行记录在磁盘中存放的真实的物理地址 rowid,用在索引查询

select rowid,emp.* from emp where deptno>20 order by sal;

-- 查询第5-10条记录 -- 1.查询前10条记录 select rownum line,emp.* from emp where rownum <=10; -- 2. 查询行号>=5的记录 select * from (select rownum line,emp.* from emp where rownum <=10) tt where line >=5;

扩展: 华为的笔试题

create table pp( pname varchar2(20) ); insert into pp values('aaa'); insert into pp values('bbb'); insert into pp values('ccc'); -- 使用一条语句删除表中重复的记录, 只保留rowid最小的记录 select distinct * from pp; select rowid,pname from pp; -- 1.分组查询出所有最小的rowid select pname,min(rowid) from pp group by pname; -- 2. 删除rowid不在范围内数据(效率比第二种要高) delete from pp where rowid not in(select min(rowid) from pp group by pname); select * from pp; -- 第二种写法: delete from pp p1 where rowid > (select min(rowid) from pp p2 where p2.pname=p1.pname);

---

集合运算

集合运算: 并集 union,交集 intersect,差集minus union会默认去重并进行排序，而union all不去除重复的，不进行排序 union和or的区别：如果用or需要要求在同一张表下面，而union可以在不同的表下面进行。

集合运算的注意事项 1）列 的数量要一致 补齐，需要补相同的类型 如果不足，可以用null补齐 2）列的类型需要保持一致 3）列的顺序需要一致；

/* 员工表 , 经理表 工资,婚否 工资,婚否 所有员工的婚否 */ -- 工资大于1500 或者 20号部门下的员工 select * from emp where sal > 1500; select * from emp where deptno=20; -- 并集 select * from emp where sal > 1500 union select * from emp where deptno=20; select * from emp where sal > 1500 union all select * from emp where deptno=20; -- 工资大于1500 并且 20号部门下的员工 select * from emp where sal > 1500 intersect select * from emp where deptno=20; -- 工资大于1500 并且不是20号部门下的员工 select * from emp where sal > 1500 minus select * from emp where deptno=20; select * from emp where sal > 1500 or deptno=20; -- 集合运算注意事项 /* 1.列的数量要一致 2.列的类型要保持一致 3.列的顺序要一致 如果不足,可以用null补齐 */ select ename,sal from emp where sal > 1500 union select ename,sal from emp where deptno=20; select ename,sal from emp where sal > 1500 union select ename,'a' from emp where deptno=20; select ename,sal from emp where sal > 1500 union select ename,null from emp where deptno=20;