表的高级查询.doc

子查询通过把一个查询的结果作为另一个查询的一部分。具体是第一个查询可以作为第二个查询的一部分出现在第二个查询的条件中，这就是子查询。第一个查询是子查询，第二个查询是主查询。 在select 语句中嵌套了另一个select语句1) where 子句中嵌套子查询2) 用子查询的结果 作为字段来出现3) 子查询出现在from子句中 -1) where子句中嵌套子查询，执行顺序是： 先执行子查询 再执行主查询例1：找出工资高于公司平均工资的所有员工？select * from empwhere sal+nvl(comm,0)(select avg(sal+nvl(comm,0) from emp);例2：高于部门30中员工最高工资的其他员工？select * from empwhere sal+nvl(comm,0) all(select sal+nvl(comm,0) from emp where deptno=30);例3：列出受雇日期早于其直接上司的所有员工。select * from emp ewhere hiredateall(select sal from emp where deptno=30);例5：查询薪水比MANAGER的还高的员工信息。select * from emp where salany(select sal from emp where job=MANAGER);-2)用子查询的结果作为字段来实现 先执行主查询 再执行子查询例1：找员工姓名和直接上级的名字select ename as 员工姓名,(select ename from emp where empno=a.mgr) as 经理名字 from emp a;例2：显示部门名称和人数 select dname,(select count(*) from emp where deptno=a.deptno)as 人数 from dept a;例3：列出所有部门的详细信息和部门人数。select a.*,(select count(*) from emp where deptno=a.deptno) as 人数from dept a; 3)from 中加子查询通常的查询中，FROM子句后面都是具体的表名。然而，我们也可以在FROM后面跟上一个子查询作为中间数据结果集top n 问题找到工资最低的前两位员工select * from (select * from emp order by sal asc) where rownum1的自然数)。2) rownum对于大于某值的查询条件 如果想找到从第二行记录以后的记录，当使用rownum2是查不出记录的，原因是由于rownum总是从1开始的伪列，Oracle认为rownumn(n1的自然数)这种条件依旧不成立，所以查不到记录。 可以使用以下的子查询方法来解决。注意子查询中的rownum必须要有别名，否则查不出记录来，这是因为rownum不是某个表的列。3) rownum对于小于某值的查询条件 rownum对于rownum1的自然数)的条件认为是成立的，所以可以找到记录。实现分页查询 -希望每次页面显示的时候 都只查询回需要的记录查询雇员表中排在第69位置上的雇员。select ename,sal from (select rownum as num,ename,sal from emp)where num between 6 and 9; 高级查询 多表联合查询: 通过连接可以建立多表查询，多表查询的数据可以来自多个表，但是表之间必须有适当的连接条件。为了从多张表中查询，必须识别连接多张表的公共列。一般在where子句中用比较运算符指明连接的条件。若有N个表，则关联条件一定有N-1个。两个表的连接有如下几种方式：内连接：1)相等连接 两个表具有相同意义的列，可以建立相等连接条件 2)不等连接 外连接：1)左外连接 2)右外连接 3)全外连接自连接：相等连接： 通过两个表具有相同意义的列，可以建立相等连接条件。 使用相等连接进行两个表的查询时，只有连接列上在两个表中都出现且值相等的行才会出现在查询结果中。在雇员表emp中是没有雇员的部门名称信息的，只有雇员所在部门的编号，部门的信息在另外的部门表dept中，两个表具有相同的部门列deptno 可以通过该列建立相等连接。【训练1】 显示雇员的名称和所在的部门的编号和名称。执行以下查询：SELECT e.ename,e.deptno,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno;说明：在where条件中要指明进行相等连接的列. 为表起别名 不在两个表中同时出现的列，前面的表明前缀（或别名）可以省略。 简化后：SELECT ename,d.deptno,dname FROM emp e,dept d WHERE e.deptno=d.deptno;【训练2】 显示工资大于3000的雇员的名称、工资和所在的部门名称。SELECT ename,sal,dname FROM emp e,dept d WHERE e.deptno=d.deptno AND sal3000;不等连接：两个表中的相关的两列进行不等连接，比较符号一般为： ,=,=,betweenand显示雇员名称，工资和所属工资等级。select ename,sal,grade from emp e,salgrade s where e.sal between s.losal and s.hisal; 显示雇员的编号，姓名，工资，工资级别，所在部门的名称 Select empno,ename,sal,grade,dname from emp e,dept d,salgrade s where e.deptno=d.deptno and e.sal between losal and hisal;外连接： 相等连接有一个问题：如果某个雇员的部门还没有填写，即保留为空，那么该雇员在查询中就不会出现； 或者某个部门还没有雇员，该部门也不会出现。为了解决这个问题可以用外连，即除了满足相等条件的记录外，还显示那些不满足连接条件的行，不满足连接条件的行将显示在最后。左外连接：(左边表中的信息全部显示出来)显示雇员的名称，工资和所在的部门名称及没有属于任何部门的雇员。select ename,sal,dname from emp left outer join dept on emp.deptno=dept.deptno; 右外连接：(右边表中的信息全部显示出来)显示雇员的名称，工资和所在的部门名称及没有任何雇员的部门。select ename,sal,dname from emp right outer join dept on emp.deptno=dept.deptno; 说明：部门OPERATION没有任何雇员。全外连接：(左右表中的内容都显示出来) select dname,ename,b.deptno from emp a full outer join dept d on a.deptno=b.deptno;自连接：自连接就是一个表，同本身进行连接。对于自连接可以想像存在两个相同的表，可以通过不同的别名区别两个相同的表。select worker.ename|的经理是|manager.ename as 雇员经理from emp worker,emp managerwhere worker.mgr=manager.empno;说明：为