sql> --问题：要查询工资比scott高的员工信息
sql> --1. 查询scott的工资
sql> select sal from emp where ename='scott';
sal
----------
3000
sql> --2. 查询比3000 高的
sql> select *
2 from emp
3 where sal >3000;
empno ename job mgr hiredate sal comm
---------- ---------- --------- ---------- -------------- ---------- ----------
deptno
7839 king president 17-11月-81 5000
10
sql> set linesize 120
sql> col sal for 9999
sql> /
empno ename job mgr hiredate sal comm deptno
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7839 king president 17-11月-81 5000 10
sql> --子查询解决上面的问题
3 where sal > ( select sal
4 from emp
5 where ename='scott');
sql> --书写子查询的注意事项:
sql> /*
sql> 1. 将子查询放入括号中
sql> 2. 将子查询往右放
sql> 3. 一般子查询不使用order by 但是在top-n分析和分页中需要
sql> 4. group by后，不能使用子查询
sql> 5. select, from, where后面可以使用
sql> 6. 主查询和子查询可以不是一张表，只要子查询返回的结果主查询可以使用 就ok
sql> 7. 可以将子查询视为一张新表.
sql> */
sql> --在from后面使用子查询
sql> --查询部门名称为sales的员工信息
sql> select e.empno,e.ename
2 from emp e,dept d
3 where e.deptno=d.deptno
4 and d.dname='sales';
empno ename
---------- ----------
7499 allen
7521 ward
7654 martin
7698 blake
7844 turner
7900 james
已选择6行。
3 where deptno= (select deptno
4 from dept
5 where dname ='sales');
7499 allen salesman 7698 20-2月 -81 1600 300 30
7521 ward salesman 7698 22-2月 -81 1250 500 30
7654 martin salesman 7698 28-9月 -81 1250 1400 30
7698 blake manager 7839 01-5月 -81 2850 30
7844 turner salesman 7698 08-9月 -81 1500 0 30
7900 james clerk 7698 03-12月-81 950 30
sql> -- 查询员工的名字和工资
sql> select ename,sal
2 from emp;
ename sal
---------- -----
smith 800
allen 1600
ward 1250
jones 2975
martin 1250
blake 2850
clark 2450
scott 3000
king 5000
turner 1500
adams 1100
james 950
ford 3000
miller 1300
已选择14行。
2 from (select ename,sal)
3 ;
from (select ename,sal)
*
第 2 行出现错误:
ora-00923: 未找到要求的 from 关键字
sql> ed
已写入 file afiedt.buf
1 select *
2* from (select ename,sal from emp)
sql> --select后面跟子查询
sql> select (select dname from deptno where deptno=10),ename
select (select dname from deptno where deptno=10),ename
*
第 1 行出现错误:
ora-00942: 表或视图不存在
sql> select (select dname from dept where deptno=10),ename
(selectdnamefr ename
-------------- ----------
accounting smith
accounting allen
accounting ward
accounting jones
accounting martin
accounting blake
accounting clark
accounting scott
accounting king
accounting turner
accounting adams
accounting james
accounting ford
accounting miller
sql> --可以将子查询视为新表
sql> select e.ename,e.sal
2 from (select ename,sal from emp) e;
sql> --8.可以有多个子查询
sql> --多行子查询
sql> --查询部门名称为sales和accounting的员工信息
3 where deptno=(select deptno
4 from dept
5 where dname='sales' or dname='accouting');
3 where deptno in (select deptno
5* where dname='sales' or dname='accouting')
sql> --查询工资最低的员工信息
3 where sal = (select min(sal)
4 from emp);
7369 smith clerk 7902 17-12月-80 800 20
3 where sal = ( select min(sal)
5 group by deptno);
where sal = ( select min(sal)
*
第 3 行出现错误:
ora-01427: 单行子查询返回多个行
sql> -- 关于null
sql> host cls
sql> --查询不是经理的员工的信息
sql> select * from emp;
7566 jones manager 7839 02-4月 -81 2975 20
7782 clark manager 7839 09-6月 -81 2450 10
7788 scott analyst 7566 13-7月 -87 3000 20
7876 adams clerk 7788 13-7月 -87 1100 20
7902 ford analyst 7566 03-12月-81 3000 20
7934 miller clerk 7782 23-1月 -82 1300 10
3 where empno not in (select mgr from emp)
4 ;
未选定行
3* where empno not in (select mgr from emp where mgr is not null)
已选择8行。
sql> --如果子查询中，包含null，类似表达式，会导致整个集合为null
sql> -any的使用
sp2-0042: 未知命令 "-any的使用" - 其余行忽略。
sql> --any的使用
sql> --问题: 查询工资比10号部门员工中任意一个低的员工信息
3 where sal < any (select sal from emp where deptno=10);
已选择13行。
sql> --等同于
3 where sal < (select max(sal) from emp where deptno=10);
sql> --all的使用
sql> --查询工资比10号部门都要低的员工信息
3 where sal < all (select sal from emp where deptno=10);
3* where sal < (select min(sal) from emp where deptno=10)
sql> spool off
sql> --课堂练习
sql> --第一题: 伪列
sql> -- rownum:行号
sql> select rownum,empno,ename from emp;
rownum empno ename
---------- ---------- ----------
1 7369 smith
2 7499 allen
3 7521 ward
4 7566 jones
5 7654 martin
6 7698 blake
7 7782 clark
8 7788 scott
9 7839 king
10 7844 turner
11 7876 adams
12 7900 james
13 7902 ford
14 7934 miller
sql> --关于rownum的注意事项：
sql> --1. rownum一旦生成就不变
sql> --2. rownum只能使用<=,不能使用>=
sql> select rownum,ename
3 order by sal;
rownum ename
1 smith
12 james
11 adams
3 ward
5 martin
14 miller
10 turner
2 allen
7 clark
6 blake
4 jones
8 scott
13 ford
9 king
3 where rownum<=3
4 orber by sal desc;
orber by sal desc
*
第 4 行出现错误:
ora-00933: sql 命令未正确结束
4* order by sal desc
sql> select rownum,ename,sal
3 where rownum<3;
rownum ename sal
---------- ---------- -----
1 smith 800
2 allen 1600
1 select rownum,ename,sal
3* where rownum>3
sql> --关于rownum的生成机制：必须从依次取出，然后付到记录上
sql> --第二题：将子查询结果集视为新表
sql> --第三题：
sql> select hiredate from emp;
hiredate
--------------
17-12月-80
20-2月 -81
22-2月 -81
02-4月 -81
28-9月 -81
01-5月 -81
09-6月 -81
13-7月 -87
17-11月-81
08-9月 -81
03-12月-81
23-1月 -82
sql> --假设：我们已知道入职的年份有： 80 81 82 87
sql> -- 第一题
2 from (select * from emp order by sal desc)
3 where rownum<=3;
1 king 5000
2 scott 3000
3 ford 3000
2 from (select rownum r,e1.*
3 from (select * from emp order by sal) e1
4 where rownum <=8
5 )
6 where r >=5;
r empno ename job mgr hiredate sal comm deptno
---------- ---------- ---------- --------- ---------- -------------- ----- ---------- ----------
5 7654 martin salesman 7698 28-9月 -81 1250 1400 30
6 7934 miller clerk 7782 23-1月 -82 1300 10
7 7844 turner salesman 7698 08-9月 -81 1500 0 30
8 7499 allen salesman 7698 20-2月 -81 1600 300 30
sql> select e.empno,e.ename,e.sal,d.avgsal
2 from emp e,(select deptno, avg(sal) avgsal from emp group by deptno) d
4 and e.sal>d.avgsal;
empno ename sal avgsal
---------- ---------- ----- ----------
7499 allen 1600 1566.66667
7566 jones 2975 2175
7698 blake 2850 1566.66667
7788 scott 3000 2175
7839 king 5000 2916.66667
7902 ford 3000 2175
sql> --第三题
sql> select to_char(hiredate,'yyyy') from emp;
to_c
----
1980
1981
1987
1982
sql> select count(*) total,
2 sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980"
3 sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) "1981"
4 sum(decode(to_char(hiredate,'yyyy'),'1982',1,0)) "1982"
5 sum(decode(to_char(hiredate,'yyyy'),'1987',1,0)) "1987"
6 from emp;
sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) "1981"
*
2 sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980",
3 sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) "1981",
4 sum(decode(to_char(hiredate,'yyyy'),'1982',1,0)) "1982",
total 1980 1981 1982 1987
---------- ---------- ---------- ---------- ----------
14 1 10 1 2
![龙珠训练营task04[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)