oracle----多表查询

多表查询
涉及到一个概念：笛卡尔集。先讲下笛卡尔集
如果做2表查询 select a1.name ,a1.sex from stu a1 ,addr a2;
如果stu表有4个学生，addr表有5条地址，没加限定的条件下，则会有20条结果，这就是笛卡尔集。
如果是2张表，最少有1个条件可以排除笛卡尔集，如果是3张表，则最少是2个条件排除笛卡尔集
多表查询的条件，最少不能少于表的个数减一
select a1.name ,a2.name ,a2.salary from 部门 a1,emp a2 where a1.deptno=a2.deptno and a1.deptno='10';
如果是想查询 学生表的学生成绩，属于定级表中优良中差哪一档
select a1.name,a2.grade from stu a1, scoregrade a2 where a1.score between a2.lowscore and a2.highscore;


多表查询之自连接
select  a1.name,a2.name from emp a1 ,emp a2 where a1.mgr=a2.empno and a1.name='ford';


子查询
单行子查询
select * from emp where deptno=(select deptno from emp where name='smith');
数据库执行sql语句，是从右到左执行，所以把数据筛选多的条件放在最右，先执行后面的语句就快了


多行子查询 
查询和第3组成绩相同的同学的名字和年龄
比如第三组有3个人，分别是60、60、82分
select distinct grade from stu where team='3';结果应该是60和82
实际应把语句写成：
select name ,age from stu where grade in(select distinct grade from stu where team='3');


select name ,salary ,dept from emp where salary> all (select salary from emp where dept='30');
意思是查询 工资大于30号部门所有人的这些人，其实就是找工资比30号部门最高工资还高的人，这里all的意思是 比括号里所有的值


select name ,salary ,dept from emp where salary> any (select salary from emp where dept='30');
意思是查询 工资大于30号部门所有人中的一个即可，其实就是找工资比30号部门最低工资高的人，这里any的意思是 比括号里任意值




多列查询
select dept ,job from emp where name='smith';
select * from emp where (dept,job)= (select dept ,job from emp where name='smith');
意思查 和smith 同一部门从事相同工作的


select dept ,avg(salary) mysal from emp group by dept; 可以把这个看做一张自己制作的子表，称作内嵌视图，必须给子查询指定别名
select a2.name ,a2.salary,a2.dept ,a1.mysal from (select dept ,avg(salary) mysal from emp group by dept) a1 ,emp a2 where a1.dept=a2.dept and a1.salary>a2.mysal;
搜索每个部门中，工资要大于部门平均工资的人






oracle分页有3种方式 在效率上，rowid 优于 rownum，rownum 优于 分析函数法
1.rownum 分页
首先
select * from emp;把这个作为一个内嵌视图
其次，获取rownum ,oracle分配的
select a1.*,rownum rn from (select * from emp) a1;
第三，分页控制
select a1.*,rownum rn from (select * from emp) a1 where rn<10 ;rownum在oracle中每次查询只能使用一次，所以不能写rownum>5 and rownum<10
select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum<10) where rn>5 ;
指定查询的列，修改首先步骤中的内容
排序，在其次步骤中添加
控制显示的范围，可以使用函数，在第三步进行传输控制。



合并查询  union ，union all ,intersect ,minus
union 从查询结果中取并集,会自动去掉结果中重复的行
select name,salary from emp where salary>2500 union 
select name ,salary from emp where job='MANAGER';
union all ，结果集是或的关系，不会去掉重复行
intersect，结果取交集，返回2个共有的数据
minus，结果取差集，返回存在第一个集合中，而不存在第二个集合中的数据。其实就是用第一个集合减去第二个集合