oracleday07(三层过滤 自复制 分页查询)

在from子句中使用子查询

显示高于自己部门平均工资的员工

思考：各个部门的平均工资是多少？

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

select e.ename,e.sal,a,myavg,a.deptno from emp e ,(select avg(sal) myavg,deptno from emp group by deptno) a where e.sal>a.myavg and e.deptno=a.deptno;

select放在 where 后面可能有局限性 因为不能加别名 不能看平均工资

查询每个部门最高的人的详细资料

查出各个部门的最高工资

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

select * from emp ,(select max(sal) maxsal,deptno from emp group by deptno) t where emp.deptno=t.deptno and t.maxsal=emp.sal;

显示每个部门的信息(编号 名称) 和人员数量

查出每个部门的人数

select deptno,count(*) from emp group by deptno;

select t1.dname,t2.num from dept t1,(select deptno,count(*) num from emp group by deptno) t2 where t1.deptno=t2.deptno (+); 

当在from 子句中使用子查询时该子查询会被作为一个临时表来对待 当在from中子句使用子查询时 必须给子查询指定别名

 分页查询 

mysql: select * from 表名 where 条件 limit 从第几条取 取几条

sql server

 select top 4 * from 表名 where id not in (select top 4 id from 表名 where 条件);

排除前4条，再取4条 实际上取出5~8

每个表都有个rownum

select * from emp; 先初步显示查询结果

select t1.*,rownum rn from (select * from emp) t1; 底层进行了优化

select t2.* from (select t1.*,rownum rn from (select * from emp) t1 where rownum<=6) t2 where  rn>=4; 3次过滤后速度极快

在百万级别都可以及时响应

oracle 使用三层过滤机制 

第一层 select * from emp ;   可以加个where 条件

第二层     select t1.*,rownum rn from (select * from emp) t1 where rownum<=6 截断 6条后面的数据

第三层 select t2.* from (select t1.*,rownum rn from (select * from emp) t1 where rownum<=6) t2 where  rn>=4;

实际上我们可以把上面的sql 语句当作一个分页模版  

6代表你取到第几条 4代表从第几条开始取

如果需要针对不同的情况分页 在最内层进行处理包括多表

按照入职时间的先后顺序 查询7~10

select t2.* from (select t1.*,rownum rn from (select * from emp order by hiredate) t1 where rownum<=10) t2 where  rn>=7;

看看分页查询的效率:

模拟10w的表->测试

create table mytest as select empno,ename,sal,comm,deptno from emp; 从emp 取出字段作为mytest

自我复制(蠕虫复制)

insert into mytest (empno,ename,sal,comm,deptno) select empno,ename,sal,comm,deptno  from mytest;

还有 rowid  和分析函数

合并查询 

union 

用于取得 两个结果集的并集 当使用该操作符的时候会自动去掉结果集中重复行

union all

与union 相似 但是不会取消重复行 而且不会排序 a1+a2

intersect

该操作符用于取得两个结果集的交集

minus 取差集 a1-a2 取a1减去与a2的并集只会显示存在a1而不存在于 a2的数据

cube 可以对括号内内容进行分类 多次group by

oracle 的内连接和外连接 

内连接实际上就是利用where子句对两张表形成的笛卡尔积进行筛选 我们前面学习的查询都是内连接也是在开发过程中用的最多的连接查询

 基本语法 

select emp.ename,dept.dname from emp,dept where emp.deptno=dept.deptno; <==>

select emp.ename,dept.dname from emp inner join dept on emp.deptno=dept.deptno;

select 列名 ... from 表1 inner join 表二 on 条件

外连接分为三种 左外连 右外连 完全外连

create table a (id number ,name varchar2(32)); 注意不是 String 而是 char 或者varchar2

select stu.name,stu.id ,exam .grade from stu,exam where stu.id=exam.id;

内连接 它的特点是只有两张表同时匹配 才被选择 没有就不被显示

左外连 左侧的表完全显示

select stu.name,stu.id,exam.grade from stu left join exam on stu.id=exam.id;

如果stu(左表)的记录没有和exam任何一条记录匹配也要被选中

另外一种写法

select stu.name,stu.id,exam.grade from stu,exam where stu.id=exam.id(+);

右外连 

显示所有成绩 如果没有名字匹配显示空

select stu.name,stu.id,exam.grade from stu right join exam on stu.id=exam.id;

右外连指的是右边的表如果没有和左表任何一条记录匹配也要被选中

select stu.name,stu.id,exam.grade from stu,exam where stu.id(+);=exam.id

+号在左边就是右外连

实际上左外连和右外连是可以互为转换

完全外连:

即两个表查询，不管有没有匹配上 都显示出来

如果没有相应的匹配值则显示为空