SQL> --可以在where select from having后放置子查询
SQL> --在select后面放置子查询
SQL> select ename,sal,(select job from emp where empno=7839)
2 from emp;
ENAME SAL (SELECTJO
---------- ---------- ---------
SMITH 800 PRESIDENT
ALLEN 1600 PRESIDENT
WARD 1250 PRESIDENT
JONES 2975 PRESIDENT
MARTIN 1250 PRESIDENT
BLAKE 2850 PRESIDENT
CLARK 2450 PRESIDENT
SCOTT 3000 PRESIDENT
KING 5000 PRESIDENT
TURNER 1500 PRESIDENT
ADAMS 1100 PRESIDENT
ENAME SAL (SELECTJO
---------- ---------- ---------
JAMES 950 PRESIDENT
FORD 3000 PRESIDENT
MILLER 1300 PRESIDENT
已选择14行。
SQL> set pagesize=120
SP2-0268: pagesize 选项的编号无效
SQL> set pagesize 120
SQL> --在from后面跟子查询
SQL> --查询员工的名字，薪水
SQL> select *
2 from (select ename,sal 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行。
SQL> --查询员工的名字，薪水，年薪
SQL> select *
2 from (select ename,sal,sal*12 annlsal from emp);
ENAME SAL ANNLSAL
---------- ---------- ----------
SMITH 800 9600
ALLEN 1600 19200
WARD 1250 15000
JONES 2975 35700
MARTIN 1250 15000
BLAKE 2850 34200
CLARK 2450 29400
SCOTT 3000 36000
KING 5000 60000
TURNER 1500 18000
ADAMS 1100 13200
JAMES 950 11400
FORD 3000 36000
MILLER 1300 15600
已选择14行。
SQL> --子查询和主查询使用的表可以是不一样的，但是只要保证子查询得到的结果能在主查询中使用
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> set linesize 120;
SQL> --其实上面的例子，也可以使用多表查询的等值连接
SQL> select *
2 from emp e,dept d
3 where e.deptno=d.deptno and d.dname='SALES';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME
---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- ---------- --------------
LOC
-------------
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 30 SALES
CHICAGO
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 30 SALES
CHICAGO
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 30 SALES
CHICAGO
7900 JAMES CLERK 7698 03-12月-81 950 30 30 SALES
CHICAGO
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 30 SALES
CHICAGO
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 30 SALES
CHICAGO
已选择6行。
SQL> set linesize 150
SQL> --在可以使用子查询和多表查询的例子中，我们理论上尽量使用多表查询
SQL> --因为子查询查询两次，但是多表查询查询一次
SQL> --单行子查询只能使用单行操作符，多行子查询只能使用多行操作符
SQL> --单行子查询的返回结果是一条，多行子查询返回的结果是多条
SQL> --多行子查询
SQL> --查询部门名称是Sales和Accounting的员工
SQL> select *
2 from emp e
3 where e.deptno in (select d.deptno
4 from dept d
5 where d.dname='SALES' or d.dname='ACCOUNTING');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7839 KING PRESIDENT 17-11月-81 5000 10
7934 MILLER CLERK 7782 23-1月 -82 1300 10
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7900 JAMES CLERK 7698 03-12月-81 950 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
已选择9行。
SQL> --同样，也可以使用多表查询
SQL> select *
2 from emp e,dept d
3 where e.deptno=d.deptno and (d.dname='SALES' or d.dname='ACCOUNTING');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- ---------- -------------- -------------
7782 CLARK MANAGER 7839 09-6月 -81 2450 10 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 17-11月-81 5000 10 10 ACCOUNTING NEW YORK
7934 MILLER CLERK 7782 23-1月 -82 1300 10 10 ACCOUNTING NEW YORK
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 30 SALES CHICAGO
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 30 SALES CHICAGO
7900 JAMES CLERK 7698 03-12月-81 950 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 30 SALES CHICAGO
已选择9行。
SQL> --any
SQL> --查询大于部门号为30中任意一个员工的员工信息
SQL> select *
2 from emp
3 where sal> any (select sal
4 from emp
5 where deptno=30);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7839 KING PRESIDENT 17-11月-81 5000 10
7902 FORD ANALYST 7566 03-12月-81 3000 20
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
已选择12行。
SQL> --上面的例子说明，使用多行子查询的any，实质跟查询薪水大于部门号为30的最低薪水的员工信息
SQL> --本来上面的where语句没有使用any关键字的话就会报错
SQL> --下面我们来查询大于部门号为30的最低薪水的yuangong
SQL> select *
2 from emp
3 where sal>(select min(sal)
4 from emp
5 where deptno=30);
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
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 19-4月 -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 23-5月 -87 1100 20
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择12行。
SQL> --跟上面例子的查询结果一样
SQL> --all
SQL> --查询大于部门号为30的所有薪水的员工信息
SQL> ed
已写入 file afiedt.buf
1 select *
2 from emp
3 where sal> all (select sal
4 from emp
5* where deptno=30)
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7902 FORD ANALYST 7566 03-12月-81 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
SQL> --相当于：
SQL> ed
已写入 file afiedt.buf
1 select *
2 from emp
3 where sal> (select max(sal)
4 from emp
5* where deptno=30)
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7902 FORD ANALYST 7566 03-12月-81 3000 20
SQL> --关于空值的问题，为什么可以在in里面使用空值，但是not in 里面使用空值会不返回结果
SQL> --查询所有老板的信息
SQL> select *
2 from emp
3 where empno in (select mgr from emp);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7902 FORD ANALYST 7566 03-12月-81 3000 20
已选择6行。
SQL> --如果要查询不是老板的按照传统思维，应该直接在上面的例子中加一个Not
SQL> ed
已写入 file afiedt.buf
1 select *
2 from emp
3* where empno not in (select mgr from emp)
SQL> /
未选定行
SQL> --然后返回结果就是未选定行
SQL> --是因为里面有一个大老板 他的老板编号为nul
SQL> --如果要想查询到结果，可以再增加一个条件
SQL> ed
已写入 file afiedt.buf
1 select *
2 from emp
3* where empno not in (select mgr from emp where mgr is not null)
SQL> /
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 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已选择8行。
SQL> --我们只需要保证里面没有空值就可以了、
SQL> spool off
oracle子查询
最新推荐文章于 2018-10-09 16:07:56 发布
扫一扫
374
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
