数据库基本功之复杂查询的子查询
子查询返回的值可以被外部查询使用,这样的复合查询等效与执行两个连续的查询.
1. 单行单列子查询
(>,<,=,<>,>=,<=)内部SELECT子句只返回一行结果
2.多行单列子查询
(all, any, in,not in)
all (>大于最大的,<小于最小的)
SQL> select ename, sal from emp where sal >all (2000,3000,4000);ENAME SAL
------------------------------ ----------
KING 5000SQL> -- 查找高于所有部门的平均工资的员工(>比子查询中返回的列表中最大的大才行)
SQL> select ename, job, sal from emp where sal > all (select avg(sal) from emp group by deptno);ENAME JOB SAL
------------------------------ --------------------------- ----------
JONES MANAGER 2975
FORD ANALYST 3000
SCOTT ANALYST 3000
KING PRESIDENT 5000SQL> -- 子查询结果
SQL> select avg(sal) from emp group by deptno;AVG(SAL)
----------
1566.66667
2916.666672175SQL> 3. 多行子查询中使用any
(>大于最小的,<小于最大的)
any的意思是:比子查询中返回的列表中最小的大就行
注意和all的区别,any强调的是只要有任意一个符合就行了,所以>any只要比最小的大就行了.
SQL>
SQL> select ename, sal from emp where sal>any(2000,3000,4000);ENAME SAL
------------------------------ ----------
JONES 2975
BLAKE 2850
CLARK 2450
SCOTT 3000
FORD 3000
KING 50006 rows selected.SQL>
4. 多行子查询中使用in/not in
(逐个比较是否有匹配的值)
SQL>
SQL> select ename,sal from emp where sal in (800, 3000, 4000);ENAME SAL
------------------------------ ----------
SMITH 800
SCOTT 3000
FORD 3000SQL> -- NOT运算操作符可以使用在IN操作上,但不能使用在ANY,ALL操作.
SQL> select ename, sal from emp where sal not in (800,3000,4000);ENAME SAL
------------------------------ ----------
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
TURNER 1500
ADAMS 1100
JAMES 950
MILLER 1300
KING 500011 rows selected.SQL> 5. 多行多列的子查询返回多列结果
以SELECT主查询的WHERE子句中的多个列合并作为成对比较 条件.
SQL>
SQL> create table emp1 as select * from emp;Table created.SQL>
SQL> -- SMITH是20部门的员工
SQL> update emp1 set sal=1600,comm=300 where ename='SMITH';1 row updated.SQL> -- CLARK是10部门的员工
SQL> update emp1 set sal=1500,comm=300 where ename='CLARK';1 row updated.SQL> select * from emp1;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- --------------- ---------- ---------- ----------7369 SMITH CLERK 7902 17-DEC-80 1600 300 207499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 307521 WARD SALESMAN 7698 22-FEB-81 1250 500 307566 JONES MANAGER 7839 02-APR-81 2975 207654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 307698 BLAKE MANAGER 7839 01-MAY-81 2850 307782 CLARK MANAGER 7839 09-JUN-81 1500 300 107788 SCOTT ANALYST 7566 24-JAN-87 3000 207844 TURNER SALESMAN 7698 08-SEP-81 1500 0 307876 ADAMS CLERK 7788 02-APR-87 1100 207900 JAMES CLERK 7698 03-DEC-81 950 307902 FORD ANALYST 7566 03-DEC-81 3000 207934 MILLER CLERK 7782 23-JAN-82 1300 107839 KING PRESIDENT 8000 17-NOV-81 5000 0 1014 rows selected.SQL> 5.2 要求:查找表中是与30部门的员工工资和奖金相同的其他部门的员工.
(现在20部门的SIMTH与30部门的ALLEN 有相同的工资和奖金)
多列子查询特点是主查询每一行中的列都要与子查询返回列表中的相应列同时进行比较,只有各列完全匹配时才显示主查询中的该数据行.分解一下:
5.3 第一步, 查询30号部门的工资和奖金的结果集
此例没有对comm的空值进行处理
SQL>
SQL> select sal,comm from emp1 where deptno=30;SAL COMM
---------- ----------1600 3001250 5001250 140028501500 09506 rows selected.SQL> 5.4 第二步,列出表中属于这个结果集的所有员工.
SQL> select * from emp1 where (sal,comm) in (select sal,comm from emp1 where deptno=30);EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- --------------- ---------- ---------- ----------7369 SMITH CLERK 7902 17-DEC-80 1600 300 207499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 307521 WARD SALESMAN 7698 22-FEB-81 1250 500 307654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 307844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30SQL> 5.5 第三步,去掉30号部门
就显示出了在emp1表中与30部门中任意一个员工的工资和奖金完全相同的,但该员工不是来自30部门的员工信息.
SQL>
SQL> select * from emp1 where (sal,comm) in (select sal,comm from emp1 where deptno=30) and deptno <>30;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- --------------- ---------- ---------- ----------7369 SMITH CLERK 7902 17-DEC-80 1600 300 20SQL> 5.6 知识点:
1)成对比较是不能使用>any或>all等多行单列比较符的.
2)成对比较时的多列顺序和类型必须一一对应.
6. 与非成对比较(含布尔运算)的区别
SQL> select * from emp12 where sal in (select sal from emp1 where deptno=30)3 and nvl(comm,0) in (select nvl(comm,0) from emp1 where deptno=30)4 and deptno<>30;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- --------------- ---------- ---------- ----------7369 SMITH CLERK 7902 17-DEC-80 1600 300 207782 CLARK MANAGER 7839 09-JUN-81 1500 300 10SQL> 两个子查询返回的值分别与主查询中的sal和comm列比较,如果员工的工资与30部门任意一个员工相同,同时,奖金也与30部门任意一个员工相同,那么得到了两个员工的信息.
可见,成对比较(使用where (列,列))比非成对比较(使用where 列 and 列) 更为严苛
7. 关于布尔运算符not
7.1 not 否定后面的比较符
where empno=7788 where NOT (empno=7788)
where ename LIKE ‘S%’ where ename NOT LIKE ‘S%’
where deptno IN (20,30) where deptno NOT IN (20,30)
where sal BETWEEN 1500 AND 3000 where sal NOT BETWEEN 1500 AND 3000
where comm IS NULL where comm IS NOT NULL
where EXISTS (select子查询) where NOT EXISTS (select子查询)
7.2 not in 在子查询中的空值问题
"in"与"not in"遇到空值时情况不同,"not in"如果子查询的结果集中有空值,那么主查询得到的结果集也是空.
SQL>
SQL> select * from emp;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- --------------- ---------- ---------- ----------7369 SMITH CLERK 7902 17-DEC-80 800 207499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 307521 WARD SALESMAN 7698 22-FEB-81 1250 500 307566 JONES MANAGER 7839 02-APR-81 2975 207654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 307698 BLAKE MANAGER 7839 01-MAY-81 2850 307782 CLARK MANAGER 7839 09-JUN-81 2450 107788 SCOTT ANALYST 7566 24-JAN-87 3000 207844 TURNER SALESMAN 7698 08-SEP-81 1500 0 307876 ADAMS CLERK 7788 02-APR-87 1100 207900 JAMES CLERK 7698 03-DEC-81 950 307902 FORD ANALYST 7566 03-DEC-81 3000 207934 MILLER CLERK 7782 23-JAN-82 1300 107839 KING PRESIDENT 8000 17-NOV-81 5000 0 1014 rows selected.SQL> -- 查询empno的值,在mgr里存在空值
SQL> select * from emp where empno not in (select nvl(mgr,0) from emp);EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- --------------- ---------- ---------- ----------7369 SMITH CLERK 7902 17-DEC-80 800 207499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 307521 WARD SALESMAN 7698 22-FEB-81 1250 500 307654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 307844 TURNER SALESMAN 7698 08-SEP-81 1500 0 307876 ADAMS CLERK 7788 02-APR-87 1100 207900 JAMES CLERK 7698 03-DEC-81 950 307934 MILLER CLERK 7782 23-JAN-82 1300 108 rows selected.SQL> -- 查找出没有下属的员工,即普通员工,(该员工号不在mgr之列的)
SQL> select * from emp where empno not in (select mgr from emp);EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- --------------- ---------- ---------- ----------7369 SMITH CLERK 7902 17-DEC-80 800 207499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 307521 WARD SALESMAN 7698 22-FEB-81 1250 500 307654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 307844 TURNER SALESMAN 7698 08-SEP-81 1500 0 307876 ADAMS CLERK 7788 02-APR-87 1100 207900 JAMES CLERK 7698 03-DEC-81 950 307934 MILLER CLERK 7782 23-JAN-82 1300 108 rows selected.SQL>上面的结果不出所料,主查询没有返回记录.这个原因是在子查询中有一个空值,而对于not in这种形式,一旦子查询出现了空值,则主查询记录结果也就返回空了.
注意:not后不能跟单行比较符,只有not in组合,没有not any 和not all的组合,但 not后可以接表达式 如:
where empno not in(...)与where not empno in(...)两个写法都是同样结果,前者是not in组合,后者是not一个表达式.
SQL> -- 例:排除空值的影响
SQL> select ename from emp where empno not in (select nvl(mgr,0) from emp);ENAME
------------------------------
SMITH
ALLEN
WARD
MARTIN
TURNER
ADAMS
JAMES
MILLER8 rows selected.SQL> 8. from 中使用子查询（也叫内联视图）
例:员工的工资大于他所在的部门的平均工资的话,显示其信息.
分两步来考虑:
第一步,先看看每个部门的平均工资,再把这个结果集作为一个内联视图.
SQL>
SQL> select deptno,avg(sal) salavg from emp group by deptno;DEPTNO SALAVG
---------- ----------30 1566.6666710 2916.6666720 2175SQL> 第二步,把这个内联视图起一个别名b, 然后和emp 的别名e 做连接,满足条件即可.
SQL>
SQL> select e.ename, e.sal, e.deptno, b.salavg2 from emp e, (select deptno, avg(sal) salavg from emp group by deptno) b3 where e.deptno = b.deptno4 and e.sal > b.salavg;ENAME SAL DEPTNO SALAVG
------------------------------ ---------- ---------- ----------
ALLEN 1600 30 1566.66667
JONES 2975 20 2175
BLAKE 2850 30 1566.66667
SCOTT 3000 20 2175
FORD 3000 20 2175
KING 5000 10 2916.666676 rows selected.SQL>
9.关联子查询与非关联子查询
从主查询(外部)调用子查询(内部)来看,可以有关联与非关联子查询之分
9.1 非关联子查询:
子查询部分可以独立执行,Oracle的in子查询一般用于非关联子查询 ,首先执行子查询,并将结果列表存放在加了索引的临时表中.即在执行子查询之前,系统先将主查询挂起,待子查询执行完毕,存放在临时表中以后再执行主查询.
SQL> -- 例:得到30号部门工资高于本部门平均工资的员工的信息
SQL> select ename,sal,deptno from emp where deptno=30 and sal > (select avg(sal) from emp where deptno=30);ENAME SAL DEPTNO
------------------------------ ---------- ----------
ALLEN 1600 30
BLAKE 2850 30SQL> 9.2 关联子查询:
其子查询(内部,inner)会引用主查询(外部,outer)查询中的一列或多列.在执行时,外部查询的每一行都被一次一行地传递给子查询.
子查询依次读取外部查询传递来的每一值,并将其用到子查询上,直到外部查询所有的行都处理完为止.然后返回查询结果
9.2.1 实验准备
SQL> create table emp2 as (select e.empno, e.ename, d.loc, d.deptno from emp e, dept d where e.deptno = d.deptno(+));Table created.SQL> select * from emp2;EMPNO ENAME LOC DEPTNO
---------- ------------------------------ --------------------------------------- ----------7782 CLARK NEW YORK 107934 MILLER NEW YORK 107839 KING NEW YORK 107369 SMITH DALLAS 207566 JONES DALLAS 207788 SCOTT DALLAS 207876 ADAMS DALLAS 207902 FORD DALLAS 207499 ALLEN CHICAGO 307521 WARD CHICAGO 307654 MARTIN CHICAGO 307698 BLAKE CHICAGO 307844 TURNER CHICAGO 307900 JAMES CHICAGO 3014 rows selected.SQL>
9.2.2 设置null
SQL>
SQL> update emp2 set loc=null;14 rows updated.SQL> select * from emp2;EMPNO ENAME LOC DEPTNO
---------- ------------------------------ --------------------------------------- ----------7782 CLARK 107934 MILLER 107839 KING 107369 SMITH 207566 JONES 207788 SCOTT 207876 ADAMS 207902 FORD 207499 ALLEN 307521 WARD 307654 MARTIN 307698 BLAKE 307844 TURNER 307900 JAMES 3014 rows selected.SQL> commit;Commit complete.SQL> 9.2.3 通过关联查询将emp2表更新回原值.
SQL> update emp2 e set loc = (select d.loc from dept d where e.deptno = d.deptno);14 rows updated.SQL> rollback;Rollback complete.SQL>
9.3 关联查询中使用EXISTS和NOT EXISTS
EXISTS是在子查询里能否找到一个行值,如果子查询有行值,则立即停止子查询的搜索,然后返回逻辑标识TRUE, 如果子查询没有返回行值,则返回逻辑标识FALSE, 子查询要么返回T,要么返回F,以此决定了主查询的调用行的去留,然后主查询指针指向下一行,继续调用子查询..
9.3.1 EXISTS的例子
显示出emp表中那些员工不是普通员工(属于大小领导的).
SQL> -- 是否为领导
SQL>
SQL> select empno,ename,job,deptno from emp outer where exists(select 'X' from emp where mgr=outer.empno);EMPNO ENAME JOB DEPTNO
---------- ------------------------------ --------------------------- ----------7566 JONES MANAGER 207698 BLAKE MANAGER 307782 CLARK MANAGER 107788 SCOTT ANALYST 207902 FORD ANALYST 207839 KING PRESIDENT 106 rows selected.SQL>
说明:exists子查询中select 后的’X’只是一个占位,返回什么值无关紧要,它关心的是子查询中否’存在’,即子查询的where条件能否有’结果’.
一旦子查询一条记录满足where条件,则立即返回逻辑’TRUE’(就不往下查了).否则返回’FALSE’.
9.3.2 NOT EXISTS的例子
显示dept表中还没有员工的部门.
SQL> select deptno,dname from dept d where not exists (select 'X' from emp where deptno=d.deptno);DEPTNO DNAME
---------- ------------------------------------------40 OPERATIONSSQL> 对于关联子查询,在某种特定的条件下,比如子查询是个大表,且连接字段建立了索引,那么使用exists比in的效率可能更高.
10. 关于别名的使用
有表别名和列别名, 表别名用于多表连接或子查询中,列别名用于列的命名规范.
如果别名的字面值有特殊字符,需要使用双引号.如:"AB C"
10.1 必须使用别名的地方
10.1.1 两表连接后,select 投影中有相同命名的列,必须使用表别名区别标识
SQL> select ename, d. deptno from emp e, dept d where e. deptno=d. deptno;
-- 命名了别名.正确
SQL> create table emp1 as select deptno, avg(sal) salavg from emp group by deptno;
-- 表名无效
SQL> create table emp1 as select deptno, avg(sal) from emp group by deptno;
-- 或者
-- 表已创建
SQL> create table emp1(depno, abc) as select deptno, avg(sal) from emp group by deptno;
-- 命名了别名.正确
SQL> create view v as select deptno, avg(al) salavg from emp group by deptno;
-- 无效
SQL> create view v as select deptno, avg(sal) from emp group by deptno;10.1.2 使用内联视图时, 若where子句还要引用select中函数的投影, 可使用别名.
SQL>
SQL> select * from (select avg(sal) salavg from emp) where salavg > 2000;SALAVG
----------
2073.21429SQL> 10.1.3 内联视图作为多表连接,主查询投影列在形式上不允许单行字段(或函数)与聚合函数并列
解决这个问题是使在内联视图中为聚合函数加别名,然后主查询的投影中引用其别名.
SQL>
SQL> select e.ename, e.sal, b.deptno,b.salavg from emp e, (select deptno, avg(sal) salavg from emp group by deptno) b where e.deptno = b.deptno;ENAME SAL DEPTNO SALAVG
------------------------------ ---------- ---------- ----------
SMITH 800 20 2175
ALLEN 1600 30 1566.66667
WARD 1250 30 1566.66667
JONES 2975 20 2175
MARTIN 1250 30 1566.66667
BLAKE 2850 30 1566.66667
CLARK 2450 10 2916.66667
SCOTT 3000 20 2175
TURNER 1500 30 1566.66667
ADAMS 1100 20 2175
JAMES 950 30 1566.66667
FORD 3000 20 2175
MILLER 1300 10 2916.66667
KING 5000 10 2916.6666714 rows selected.SQL> 10.1.4 rownum列是Oracle的伪列,加别名可以使它成为一个表列
这样才符合SQL99标准中的连接和选择.
SQL>
SQL> select * from (select ename,rownum rn from emp) where rn > 5;ENAME RN
------------------------------ ----------
BLAKE 6
CLARK 7
SCOTT 8
TURNER 9
ADAMS 10
JAMES 11
FORD 12
MILLER 13
KING 149 rows selected.SQL> 10.2 不能使用别名的地方
10.2.1 在一个独立的select结构的投影中使用了列别名,不能在其后的where 或having中直接引用该列别名
原因： from ——》where ——》group by ——》having ——》order by ——》select
SQL> select deptno,avg(sal) salavg from emp group by deptno having salavg > 2000;
select deptno,avg(sal) salavg from emp group by deptno having salavg > 2000*
ERROR at line 1:
ORA-00904: "SALAVG": invalid identifierSQL> select deptno,avg(sal) salavg from emp group by deptno having salavg>2000;
select deptno,avg(sal) salavg from emp group by deptno having salavg>2000*
ERROR at line 1:
ORA-00904: "SALAVG": invalid identifierSQL> from ——》where ——》group by ——》having ——》order by ——》select10.2. 别名不能出现在表达式里
-- 错
SQL> SELECT cust_name AS "NAME", cust_credit_limit/2 AS MIDPOINT,MIDPOINT+100 AS "MAX LOWER LIMIT" FROM customers;
-- 对
SQL> SELECT cust_name AS "NAME", cust_credit_limit/2 AS MIDPOINT,cust_credit_limit/2+100 AS "MAX LOWER LIMIT" FROM customers;11. 简单查询与复杂查询练习题
11.1 列出emp表中工资最高的前三名员工信息
SQL>
SQL> select * from emp order by sal desc;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- --------------- ---------- ---------- ----------7839 KING PRESIDENT 8000 17-NOV-81 5000 0 107902 FORD ANALYST 7566 03-DEC-81 3000 207788 SCOTT ANALYST 7566 24-JAN-87 3000 207566 JONES MANAGER 7839 02-APR-81 2975 207698 BLAKE MANAGER 7839 01-MAY-81 2850 307782 CLARK MANAGER 7839 09-JUN-81 2450 107499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 307844 TURNER SALESMAN 7698 08-SEP-81 1500 0 307934 MILLER CLERK 7782 23-JAN-82 1300 107521 WARD SALESMAN 7698 22-FEB-81 1250 500 307654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 307876 ADAMS CLERK 7788 02-APR-87 1100 207900 JAMES CLERK 7698 03-DEC-81 950 307369 SMITH CLERK 7902 17-DEC-80 800 2014 rows selected.SQL> select * from (select * from emp order by sal desc) where rownum < 4;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- --------------- ---------- ---------- ----------7839 KING PRESIDENT 8000 17-NOV-81 5000 0 107788 SCOTT ANALYST 7566 24-JAN-87 3000 207902 FORD ANALYST 7566 03-DEC-81 3000 20SQL> -- 关于rownum 使用特别需要注意两点:
-- 1,rownum>时不会返回任何行,子查询后加别名方可.
-- 2,rownum<和and并用时,是在另一个条件基础上的rownum< ,而不是两个独立的条件的并集(intersect)
-- 如:
SQL>
SQL> select ename, sal, deptno from emp where deptno=10;ENAME SAL DEPTNO
------------------------------ ---------- ----------
CLARK 2450 10
MILLER 1300 10
KING 5000 10SQL> select ename,sal, deptno from emp where rownum <=1;ENAME SAL DEPTNO
------------------------------ ---------- ----------
SMITH 800 20SQL> select ename,sal, deptno from emp where rownum <=1 and deptno=10;ENAME SAL DEPTNO
------------------------------ ---------- ----------
CLARK 2450 10SQL> 11.2 列出emp表第5-第10名员工(按sal大--小排序)的信息(结果集的分页查询技术)
SQL>
SQL> select * from emp order by sal desc;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- --------------- ---------- ---------- ----------7839 KING PRESIDENT 8000 17-NOV-81 5000 0 107902 FORD ANALYST 7566 03-DEC-81 3000 207788 SCOTT ANALYST 7566 24-JAN-87 3000 207566 JONES MANAGER 7839 02-APR-81 2975 207698 BLAKE MANAGER 7839 01-MAY-81 2850 307782 CLARK MANAGER 7839 09-JUN-81 2450 107499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 307844 TURNER SALESMAN 7698 08-SEP-81 1500 0 307934 MILLER CLERK 7782 23-JAN-82 1300 107521 WARD SALESMAN 7698 22-FEB-81 1250 500 307654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 307876 ADAMS CLERK 7788 02-APR-87 1100 207900 JAMES CLERK 7698 03-DEC-81 950 307369 SMITH CLERK 7902 17-DEC-80 800 2014 rows selected.SQL> select t1.*, rownum rn from (select * from emp order by sal desc) t1 2 ;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN
---------- ------------------------------ --------------------------- ---------- --------------- ---------- ---------- ---------- ----------7839 KING PRESIDENT 8000 17-NOV-81 5000 0 10 17902 FORD ANALYST 7566 03-DEC-81 3000 20 27788 SCOTT ANALYST 7566 24-JAN-87 3000 20 37566 JONES MANAGER 7839 02-APR-81 2975 20 47698 BLAKE MANAGER 7839 01-MAY-81 2850 30 57782 CLARK MANAGER 7839 09-JUN-81 2450 10 67499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 77844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 87934 MILLER CLERK 7782 23-JAN-82 1300 10 97521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 107654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 117876 ADAMS CLERK 7788 02-APR-87 1100 20 127900 JAMES CLERK 7698 03-DEC-81 950 30 137369 SMITH CLERK 7902 17-DEC-80 800 20 1414 rows selected.SQL> select * from (select t1.*, rownum rn from (select * from emp order by sal desc) t1) where rn between 5 and 10;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN
---------- ------------------------------ --------------------------- ---------- --------------- ---------- ---------- ---------- ----------7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 57782 CLARK MANAGER 7839 09-JUN-81 2450 10 67499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 77844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 87934 MILLER CLERK 7782 23-JAN-82 1300 10 97521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 106 rows selected.SQL> 11.3 从列出emp表中显示员工和经理对应关系表.(emp自连接,利用笛卡尔积)
SQL> select a. empno, a. ename, a.mgr, b. empno, b. ename, b.mgr from emp a, emp b where a.mgr=b. empno;EMPNO ENAME MGR EMPNO ENAME MGR
---------- ------------------------------ ---------- ---------- ------------------------------ ----------7788 SCOTT 7566 7566 JONES 78397902 FORD 7566 7566 JONES 78397499 ALLEN 7698 7698 BLAKE 78397521 WARD 7698 7698 BLAKE 78397654 MARTIN 7698 7698 BLAKE 78397844 TURNER 7698 7698 BLAKE 78397900 JAMES 7698 7698 BLAKE 78397934 MILLER 7782 7782 CLARK 78397876 ADAMS 7788 7788 SCOTT 75667369 SMITH 7902 7902 FORD 75667566 JONES 7839 7839 KING 80007698 BLAKE 7839 7839 KING 80007782 CLARK 7839 7839 KING 800013 rows selected.SQL>
11.4 要求列出emp表中最高工资的员工所在工作地点,(emp+dept左外)
SQL> select max(sal) from emp;MAX(SAL)
----------5000SQL> select * from emp where sal = (select max(sal) from emp)2 ;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- --------------- ---------- ---------- ----------7839 KING PRESIDENT 8000 17-NOV-81 5000 0 10SQL> select a.ename, d.loc from2 (select * from emp where sal = (select max(sal) from emp)) a3 left join dept d on a.deptno = d.deptno;ENAME LOC
------------------------------ ---------------------------------------
KING NEW YORKSQL> 11.5 CTAS方法
建立dept1,在dept1表中增加一列person_count,要求根据emp表填写dept1表的各部门员工合计数(典型的关联查询).
SQL>
SQL> create table dept2 as select * from dept;Table created.SQL> -- 表中增加一列
SQL> alter table dept2 add person_count int;Table altered.SQL> -- 关联查询扫描
SQL> update dept2 d set person_count= (select count (*) from emp e where e. deptno=d. deptno);4 rows updated.SQL>
SQL> select * from dept2;DEPTNO DNAME LOC PERSON_COUNT
---------- ------------------------------------------ --------------------------------------- ------------10 ACCOUNTING NEW YORK 320 RESEARCH DALLAS 530 SALES CHICAGO 640 OPERATIONS BOSTON 0SQL> 11.6 复杂select查询,
以HR用户的几个表为例,显示欧洲地区员工的平均工资及人数(使用多表连接及内联视图)
SQL> select avg(salary), count(salary)2 from3 (select e. first_name, e. salary, d. department_id, l. location_id, c. country_id, r. region_id, r. region_name 4 from employees e, departments d, locations l, countries c, regions r5 where e. department_id=d. department_id and d. location_id=l. location_id6 and l. country_id=c. country_id and c. region_id=r. region_id)7 where region_name='Europe';AVG(SALARY) COUNT(SALARY)
----------- -------------8916.66667 36SQL>11.7 同上题(使用嵌套子查询技术)
select avg(salary), count (*)
from employees
where department_id in(select department_idfrom departmentswhere location_id in(select location_id from locationswhere country_id in(select country_id from countrieswhere region_id=(select region_id from regionswhere region_name= 'Europe')))
);
AVG(SALARY) COUNT(SALARY)
----------- -------------
8916.66667 36-- 分解如下:SQL>
SQL> select region_id from regions where region_name = 'Europe';REGION_ID
----------1SQL> select country_id from countries where region_id = 1;COUNTR
------
BE
CH
DE
DK
FR
IT
NL
UK8 rows selected.SQL> select location_id from locations where country_id in('BE','CH','DE','DK','FR','IT','NL','UK');LOCATION_ID
-----------2900300027001000110031002400250026009 rows selected.SQL> select department_id from departments where location_id in (1000,1100,2400,2500,2600,2700,2900,3000,3100);DEPARTMENT_ID
-------------408070SQL> select avg(salary), count (*) from employees where department_id in (40,70,80);AVG(SALARY) COUNT(*)
----------- ----------8916.66667 36SQL>