oracle学习之多表查询,子查询
多表查询的基础是采用笛卡尔集:
最终的行数 = 表1的行数 * 表2的行数
最终的列数 = 表1的列数 + 表2的列数
过滤笛卡尔集的关键是写连接条件,N张表至少需要N-1个条件。
多表查询例子1,等值与不等值连接,查询员工信息,员工号,姓名,月薪 和部门名称
注意,有歧义的字段必须指定是哪个表,否则将会报错。
当等值连接不成立的时候,想要保留一边的数据,这时需要使用外连接技术!
左外连接的写法,即想保留等号左边的数据,那么在等号右侧添加(+)
自连接,比如查询员工信息,显示为:xxx's boss is yyy
解题思路:emp看成2张表,一张员工表,一张老板表
连接条件:员工表的老板(mgr)是老板表的员工(empno)
select e.name||'''s boss is '||nvl(b.ename,'his wife!') from emp e,emp b where e.mgr = b.empno(+)
(+)原因在于缺少老板,而使用nvl函数,则是已经是最大的官了
自连接的弊端:即笛卡尔集是平方的增长,大表的时候效率比较低
自连接的使用场景:数据都在同一表;数据不在同一行
子查询,注意事项:
1,合理的书写风格,尤其是较复杂的子查询,要合理的换行和缩进
2,适当的使用()
3,主查询和子查询可以是不同表,只要子查询返回的结果,主查询可以用即可。
//查询部门名称是“SALES”的员工信息 select * from emp where deptno = (select deptno from dept where dname='SALES');
4,可以在主查询为where ,select,having,from后放置子查询
//查询10号部门员工号,员工姓名,部门编号,部门名称 select e.empno,e.ename,e.deptno,d.dname from emp e,dept d where e.deptno = d.deptno and e.deptno = 10; //子查询方法: select empno,ename,deptno,(select dname from dept where deptno=10) from emp where deptno =10;
5,不可以在group by,order by后放置子查询,SQL语法规范
//having后 查询高于30号部门最低薪水的部门及其最低薪水 select deptno,min(sal) from emp group by deptno having min(sal)>(select min(sal) from emp where deptno =30);
6,在from后面放置的子查询(***),要理解为放置的是一个集合
查询员工的性名,薪水和年薪 使用子查询select * from ___________________完成: 即:select * from (select ename,sal,sal*20 from emp);
7,单行子查询只能使用单行操作符 = !=;多行子查询则是使用多行操作符
//查询部门名称为SALES和ACCOUNTING的员工信息 //1,先找到这两的部门编号 select deptno from dept where dname in('SALES' ,'ACCOUNTING'); //2,变成子查询,IN,取里面的信息 select * from emp where deptno in(select deptno from dept where dname in('SALES' ,'ACCOUNTING'));
//多行操作符ANY,取任意一个
select * from emp where sal > ANY (select sal from emp where deptno=30);
//多行操作符ALL,取所有
select * from emp where sal > ALL (select sal from emp where deptno=30);
8,要注意子查询中的NULL值
select * from emp where empno not in ( select distinct mgr from emp where mgr is not null);
9,一般先执行子查询(内查询),然后在执行主查询(外查询)
10,一般不在子查询中使用 order by,但在TOP-N分析问题时,必须使用order by