Oracle学习(六)：子查询
来源：互联网 发布：洞洞板画线软件 编辑：程序博客网 时间：2024/05/01 20:02
1.知识点：可以对照下面的录屏进行阅读
SQL> --子查询所要解决的问题：问题不能一步求解SQL> --查询工资比SCOTT高的员工信息SQL> --（1）使用普通方法SQL> --1. SCOTT的工资SQL> select sal from emp where ename='SCOTT';SQL> --2. 查询比3000高的员工SQL> select * 2 from emp 3 where sal>3000;SQL> --（2）使用子查询SQL> select * 2 from emp 3 where sal > (select sal 4 from emp 5 where ename='SCOTT');SQL> -- 注意的问题：SQL> --1. 将子查询放入括号中SQL> --2. 采用合理的书写风格SQL> --3. 可以在主查询的where ，select ，from ，having后面，放置子查询SQL> --4. 不可以在group by后面放置子查询SQL> --5. 强调from后面放置子查询SQL> --6. 主查询和子查询可以不是同一张表，只要子查询返回的结果，主查询可以使用，即可SQL> --7. 一般不在子查询中使用order by;但在Top-N分析问题中，必须使用order bySQL> --8. 一般先执行子查询，再执行主查询；但相关子查询除外SQL> --9. 单行子查询只能使用单行操作符；多行子查询只能使用多行操作符SQL> --10. 注意子查询中nullSQL> --部分注意问题举例SQL> --3. 可以在主查询的where select from having后面，放置子查询SQL> --selectSQL> select ename,sal,(select job from emp where empno=7839) myjob 2 from emp;SQL> --5. 强调from后面放置子查询SQL> --查询员工的姓名和薪水SQL> select * 2 from (select ename,sal from emp);SQL> --6. 主查询和子查询可以不是同一张表，只要子查询返回的结果，主查询可以使用，即可SQL> --查询部门名称为 SALES的员工信息SQL> --子查询SQL> select * 2 from emp 3 where deptno=(select deptno 4 from dept 5 where dname='SALES');SQL> --多表查询 SQL> select e.* 2 from emp e,dept d 3 where e.deptno=d.deptno and d.dname='SALES';SQL> --SQL优化： 如果子查询和多表查询都可以，理论上尽量使用多表查询SQL> --多行操作符SQL> --in ：在集合中SQL> --查询部门名称为SALES和ACCOUNTING的员工信息SQL> select * 2 from emp 3 where deptno in (select deptno from dept where dname='SALES' or dname='ACCOUNTING');SQL> --使用多表查询SQL> select e.* 2 from emp e,dept d 3 where e.deptno=d.deptno and (d.dname='SALES' or d.dname='ACCOUNTING');SQL> --any ：和集合的任意一个值比较SQL> --查询工资比30号部门任意一个员工高的员工信息SQL> select * 2 from emp 3 where sal > any (select sal from emp where deptno=30);SQL> --单行子查询的方法也可以解决这个问题SQL>select * 2 from emp 3 where sal > (select min(sal) from emp where deptno=30)SQL> --all ：和集合的所有值比较SQL> --查询工资比30号部门所有员工高的员工信息SQL> select * 2 from emp 3 where sal > all (select sal from emp where deptno=30);SQL> --单行子查询的方法也可以解决这个问题SQL> select * 2 from emp 3 where sal > (select max(sal) from emp where deptno=30)SQL> --多行子查询中null值问题SQL> --集合中有null值不能使用not in，但可以使用inSQL> --查询不是老板的员工信息SQL> select * 2 from emp 3 where empno not in (select mgr from emp);--此查询没有结果，因为含有空值SQL> --查询是老板的员工信息SQL> select * 2 from emp 3 where empno in (select mgr from emp);--此查询结果正常SQL> --原因：所有和空值比较的条件结果是空；SQL> --使用not in方法的正确语句SQL> select * 2 from emp 3 where empno not in (select mgr from emp where mgr is not null);
2.在Sqlplus下实际执行的结果录屏：
SQL> --查询工资比SCOTT高的员工信息SQL> --1. SCOTT的工资SQL> select sal from emp where ename='SCOTT'; SAL ---------- 3000 SQL> --查询比3000高的员工SQL> set linesize 120SQL> col sal for 9999SQL> 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> --子查询所要解决的问题：问题不能一步求解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. 可以在主查询的where select from having后面，放置子查询SQL> 4. 不可以在group by后面放置子查询SQL> 5. 强调from后面放置子查询SQL> 6. 主查询和子查询可以不是同一张表，只要子查询返回的结果，主查询可以使用，即可SQL> 7. 一般不在子查询中使用order by;但在Top-N分析问题中，必须使用order bySQL> 8. 一般先执行子查询，再执行主查询；但相关子查询除外SQL> 9. 单行子查询只能使用单行操作符；多行子查询只能使用多行操作符SQL> 10. 注意子查询中nullSQL> */SQL> --3. 可以在主查询的where select from having后面，放置子查询SQL> --selectSQL> select ename,sal,(select job from emp where empno=7839) myjob 2 from emp;ENAME SAL MYJOB ---------- ----- --------- 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 MYJOB ---------- ----- --------- JAMES 950 PRESIDENT FORD 3000 PRESIDENT MILLER 1300 PRESIDENT 已选择14行。SQL> --5. 强调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 ENAME SAL ---------- ----- JAMES 950 FORD 3000 MILLER 1300 已选择14行。SQL> --6. 主查询和子查询可以不是同一张表，只要子查询返回的结果，主查询可以使用，即可SQL> --查询部门名称为 SALES的员工信息SQL> select * 2 from emp 3 where deptno=(select deptno 4 from emp 5 where dname='SALES'); where dname='SALES') *第 5 行出现错误: ORA-00904: "DNAME": 标识符无效 SQL> ed已写入 file afiedt.buf 1 select * 2 from emp 3 where deptno=(select deptno 4 from dept 5* where dname='SALES')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> select e.* 2 from emp e,dept d 3 where e.deptno=d.deptno and d.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优化： 如果子查询和多表查询都可以，理论上尽量使用多表查询SQL> host clsSQL> --多行操作符SQL> --in :在集合中SQL> --查询部门名称为SALES和ACCOUNTING的员工信息SQL> select * 2 from emp 3 where deptno in (select deptno from dept where dname='SALES' or dname='ACCOUNTING'); 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 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7839 KING PRESIDENT 17-11月-81 5000 10 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7900 JAMES CLERK 7698 03-12月-81 950 30 7934 MILLER CLERK 7782 23-1月 -82 1300 10 已选择9行。SQL> select e.* 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 ---------- ---------- --------- ---------- -------------- ----- ---------- ---------- 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 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7839 KING PRESIDENT 17-11月-81 5000 10 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7900 JAMES CLERK 7698 03-12月-81 950 30 7934 MILLER CLERK 7782 23-1月 -82 1300 10 已选择9行。SQL> host claSQL> host clsSQL> --any 和集合的任意一个值比较SQL> --查询工资比30号部门任意一个员工高的员工信息SQL> select * 2 from emp 3 where sal > any (select sal from emp 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 13-7月 -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 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ----- ---------- ---------- 7876 ADAMS CLERK 7788 13-7月 -87 1100 20 已选择12行。SQL> ed已写入 file afiedt.buf 1 select * 2 from emp 3* where sal > (select min(sal) from emp where deptno=30)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 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 7902 FORD ANALYST 7566 03-12月-81 3000 20 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ----- ---------- ---------- 7934 MILLER CLERK 7782 23-1月 -82 1300 10 已选择12行。SQL> --all 和集合的所有值比较SQL> --查询工资比30号部门所有员工高的员工信息SQL> select * 2 from emp 3 where sal > all (select sal from emp where deptno=30); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ----- ---------- ---------- 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7788 SCOTT ANALYST 7566 13-7月 -87 3000 20 7839 KING PRESIDENT 17-11月-81 5000 10 7902 FORD ANALYST 7566 03-12月-81 3000 20 SQL> ed已写入 file afiedt.buf 1 select * 2 from emp 3* where sal > (select max(sal) from emp 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 13-7月 -87 3000 20 7839 KING PRESIDENT 17-11月-81 5000 10 7902 FORD ANALYST 7566 03-12月-81 3000 20 SQL> host clsSQL> --多行子查询中null值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);未选定行SQL> --查询是老板的员工信息SQL> ed已写入 file afiedt.buf 1 select * 2 from emp 3* where empno in (select mgr from emp)SQL> / EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ----- ---------- ---------- 7902 FORD ANALYST 7566 03-12月-81 3000 20 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7839 KING PRESIDENT 17-11月-81 5000 10 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7788 SCOTT ANALYST 7566 13-7月 -87 3000 20 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 已选择6行。SQL> select * 2 from emp 3 where empno not in (select mgr from emp where mgr is not null); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ----- ---------- ---------- 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7934 MILLER CLERK 7782 23-1月 -82 1300 10 7369 SMITH CLERK 7902 17-12月-80 800 20 7876 ADAMS CLERK 7788 13-7月 -87 1100 20 7900 JAMES CLERK 7698 03-12月-81 950 30 已选择8行。SQL> spool off
0 0
- Oracle学习(六)：子查询
- Oracle学习(六)----子查询
- oracle学习笔记之六 子查询
- Oracle(六)----------------子查询
- Oracle数据库（六）子查询
- oracle学习笔记 ---- 子查询
- 【oracle学习】1.子查询
- oracle学习总结-----子查询
- 【Oracle学习】之 子查询
- Oracle数据库学习---子查询
- oracle学习分组查询子查询
- Oracle备忘录（六）--集合运算和子查询
- Oracle基本操作六：子查询,rownum,rowid
- Oracle学习笔记7 -- 子查询
- Oracle学习（6）：子查询
- Oracle学习笔记摘录4-----子查询和关联查询
- 05-Oracle学习_子查询 和 连接查询
- 06-Oracle学习_练习-子查询 和 连接查询
- Java面试题之Java基础部分 81-83题
- MySQL中DATETIME和TIMESTAMP的区别
- 对于点击登录，弹出登录框，其余的连接都不能点的小案例
- 使用Jenkins搭建持续集成(CI)环境
- optimizer_index_caching和optimizer_index_cost_adj两个参数说明
- Oracle学习(六)：子查询
- Python 计算不重复元素的个数
- Linux使用GDB调试总结
- VI中的多行删除与复制
- 找出界面的第一响应者，让键盘消失
- Gradle 构建 android 应用常见问题解决指南
- 我们为什么需要VPN，使用VPN有什么好处？
- iis http 错误 404.0
- 是国家非国家法规几个
