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> --子查询解决上面的问题
SQL> select *
2 from emp
3 where sal > ( select sal
4 from emp
5 where ename='SCOTT');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7839 KING PRESIDENT 17-11月-81 5000 10
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行。
SQL> select *
2 from emp
3 where deptno= (select deptno
4 from dept
5 where dname ='SALES');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
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
已选择6行。
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
ENAME SAL
---------- -----
JAMES 950
FORD 3000
MILLER 1300
已选择14行。
SQL> select *
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)
3 ;
ENAME SAL
---------- -----
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
ENAME SAL
---------- -----
JAMES 950
FORD 3000
MILLER 1300
已选择14行。
SQL> --select后面跟子查询
SQL> select (select dname from deptno where deptno=10),ename
2 from emp;
select (select dname from deptno where deptno=10),ename
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
SQL> select (select dname from dept where deptno=10),ename
2 from emp;
(SELECTDNAMEFR ENAME
-------------- ----------
ACCOUNTING SMITH
ACCOUNTING ALLEN
ACCOUNTING WARD
ACCOUNTING JONES
ACCOUNTING MARTIN
ACCOUNTING BLAKE
ACCOUNTING CLARK
ACCOUNTING SCOTT
ACCOUNTING KING
ACCOUNTING TURNER
ACCOUNTING ADAMS
(SELECTDNAMEFR ENAME
-------------- ----------
ACCOUNTING JAMES
ACCOUNTING FORD
ACCOUNTING MILLER
已选择14行。
SQL> --可以将子查询视为新表
SQL> select e.ename,e.sal
2 from (select ename,sal from emp) e;
ENAME SAL
---------- -----
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
ENAME SAL
---------- -----
JAMES 950
FORD 3000
MILLER 1300
已选择14行。
SQL> --8.可以有多个子查询
SQL> --多行子查询
SQL> --查询部门名称为SALES和ACCOUNTING的员工信息
SQL> select *
2 from emp
3 where deptno=(select deptno
4 from dept
5 where dname='SALES' or dname='ACCOUTING');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
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
已选择6行。
SQL> ed
已写入 file afiedt.buf
1 select *
2 from emp
3 where deptno in (select deptno
4 from dept
5* where dname='SALES' or dname='ACCOUTING')
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
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
已选择6行。
SQL> --查询工资最低的员工信息
SQL> select *
2 from emp
3 where sal = (select min(sal)
4 from emp);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
SQL> select *
2 from emp
3 where sal = ( select min(sal)
4 from emp
5 group by deptno);
where sal = ( select min(sal)
*
第 3 行出现错误:
ORA-01427: 单行子查询返回多个行
SQL> -- 关于null
SQL> host cls
SQL> --查询不是经理的员工的信息
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 13-7月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 13-7月 -87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择14行。
SQL> select *
2 from emp
3 where empno not in (select mgr from emp)
4 ;
未选定行
SQL> ed
已写入 file afiedt.buf
1 select *
2 from emp
3* where empno not in (select mgr from emp where mgr is not null)
4 ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
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
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 13-7月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择8行。
SQL> --如果子查询中，包含null，类似表达式，会导致整个集合为null
SQL> host cls
SQL> -any的使用
SP2-0042: 未知命令 "-any的使用" - 其余行忽略。
SQL> --any的使用
SQL> --问题: 查询工资比10号部门员工中任意一个低的员工信息
SQL> select *
2 from emp
3 where sal < any (select sal from emp where deptno=10);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7876 ADAMS CLERK 7788 13-7月 -87 1100 20
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7788 SCOTT ANALYST 7566 13-7月 -87 3000 20
7902 FORD ANALYST 7566 03-12月-81 3000 20
已选择13行。
SQL> --等同于
SQL> select *
2 from emp
3 where sal < (select max(sal) from emp where deptno=10);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 13-7月 -87 3000 20
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 13-7月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择13行。
SQL> --all的使用
SQL> --查询工资比10号部门都要低的员工信息
SQL> select *
2 from emp
3 where sal < all (select sal from emp where deptno=10);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7876 ADAMS CLERK 7788 13-7月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
SQL> --等同于
SQL> ed
已写入 file afiedt.buf
1 select *
2 from emp
3* where sal < (select min(sal) from emp where deptno=10)
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7876 ADAMS CLERK 7788 13-7月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
SQL> spool off
SQL> --课堂练习
SQL> --第一题: 伪列
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 13-7月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 13-7月 -87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择14行。
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
ROWNUM EMPNO ENAME
---------- ---------- ----------
12 7900 JAMES
13 7902 FORD
14 7934 MILLER
已选择14行。
SQL> --关于rownum的注意事项：
SQL> --1. rownum一旦生成就不变
SQL> --2. rownum只能使用<=,不能使用>=
SQL> select rownum,ename
2 from emp
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
ROWNUM ENAME
---------- ----------
8 SCOTT
13 FORD
9 KING
已选择14行。
SQL> select *
2 from emp
3 where rownum<=3
4 orber by sal desc;
orber by sal desc
*
第 4 行出现错误:
ORA-00933: SQL 命令未正确结束
SQL> ed
已写入 file afiedt.buf
1 select *
2 from emp
3 where rownum<=3
4* order by sal desc
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7369 SMITH CLERK 7902 17-12月-80 800 20
SQL> host cls
SQL> select rownum,ename,sal
2 from emp
3 where rownum<3;
ROWNUM ENAME SAL
---------- ---------- -----
1 SMITH 800
2 ALLEN 1600
SQL> ed
已写入 file afiedt.buf
1 select rownum,ename,sal
2 from emp
3* where rownum>3
SQL> /
未选定行
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
13-7月 -87
HIREDATE
--------------
03-12月-81
03-12月-81
23-1月 -82
已选择14行。
SQL> --假设：我们已知道入职的年份有： 80 81 82 87
SQL> -- 第一题
SQL> select rownum,ename,sal
2 from (select * from emp order by sal desc)
3 where rownum<=3;
ROWNUM ENAME SAL
---------- ---------- -----
1 KING 5000
2 SCOTT 3000
3 FORD 3000
SQL> select *
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
3 where e.deptno=d.deptno
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
已选择6行。
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
13-7月 -87
HIREDATE
--------------
03-12月-81
03-12月-81
23-1月 -82
已选择14行。
SQL> select to_char(hiredate,'yyyy') from emp;
TO_C
----
1980
1981
1981
1981
1981
1981
1981
1987
1981
1981
1987
TO_C
----
1981
1981
1982
已选择14行。
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"
*
第 3 行出现错误:
ORA-00923: 未找到要求的 FROM 关键字
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;
TOTAL 1980 1981 1982 1987
---------- ---------- ---------- ---------- ----------
14 1 10 1 2
SQL> spool off
