Oracle学习(六)----子查询
来源：互联网 发布：linux找到文件夹位置 编辑：程序博客网 时间：2024/05/01 15:31
1 为什么要有子查询 例：查询工资比scott高的员工信息 方法一：两步求解(1):SQL> select * from emp e 2 where e.ename = 'SCOTT'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20(2):SQL> select * from emp 2 where emp.sal > 3000; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 1 tom_abc 8000 10 7839 KING PRESIDENT 17-11月-81 5000 10 方法二：子查询，相当于嵌套SQL> select * from emp 2 where sal > (select sal from emp where emp.ename = 'SCOTT'); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 1 tom_abc 8000 10 7839 KING PRESIDENT 17-11月-81 5000 10 子查询的本质：多个select语言的嵌套2 子查询知识体系搭建 2.1 合理的书写风格 2.2 子查询外面的()不要忘记 2.3 子查询和主查询可以查询的是同一张表,也可以不是同一张表,只要子查询返回的结果,主查询可以用即可... 例：查询部门名称是SALES的员工信息，两种方法。方法一：使用子查询SQL> select * from emp where deptno = (select deptno from dept where dept.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方法二：分开查询SQL> select * from dept; DEPTNO DNAME LOC---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO -----返回部门编号30 40 OPERATIONS BOSTONSQL> select * from emp 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 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> select e.* from emp e, dept d 2 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 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7900 JAMES CLERK 7698 03-12月-81 950 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 重点：sql优化1)：select * / abc......后者效率高2)：where ... and ... and ... and ...从右向左3)：having/where...where效率高4)：子查询和多表查询那个效率高？在网络连接中，最耗时的时间是建立连接的过程！，在子查询中建立了两次连接，所以比较耗时，所以，多表查询效率较高。 2.4 在什么地方放置子查询select a , b, c ---OK, 只能存放单行子查询(返回结果只能是一行),不能是多行子查询from tab1 ---OK 可以放置子查询，重点..where col in (em1, em2) ----OKcol between a1 and a2col > 222col > ()group by ... ---不可以having .....---可以order by ..---不可以 2.4.1 select之后只能放置单行子查询，不能放置多行子查询错误示例：SQL> select ename,job, sal, (select deptno from emp) from emp;select ename,job, sal, (select deptno from emp) from emp *第 1 行出现错误:ORA-01427: 单行子查询返回多个行 总结：select后面检索的内容可以是不同的列、不同的表SQL> select empno, ename,job,sal, (select empno from emp where empno = 7499), sysdate from emp; EMPNO ENAME JOB SAL (SELECTEMPNOFROMEMPWHEREEMPNO=7499) SYSDATE---------- ---------- --------- ---------- ----------------------------------- -------------- 1 tom_abc 8000 7499 29-3月 -16 7369 SMITH CLERK 800 7499 29-3月 -16 7499 ALLEN SALESMAN 1600 7499 29-3月 -16 7521 WARD SALESMAN 1250 7499 29-3月 -16 7566 JONES MANAGER 2975 7499 29-3月 -16 7654 MARTIN SALESMAN 1250 7499 29-3月 -16 7698 BLAKE MANAGER 2850 7499 29-3月 -16 7782 CLARK MANAGER 2450 7499 29-3月 -16 7788 SCOTT ANALYST 3000 7499 29-3月 -16 7839 KING PRESIDENT 5000 7499 29-3月 -16 7844 TURNER SALESMAN 1500 7499 29-3月 -16 7876 ADAMS CLERK 1100 7499 29-3月 -16 7900 JAMES CLERK 950 7499 29-3月 -16 7902 FORD ANALYST 3000 7499 29-3月 -16 7934 MILLER CLERK 1300 7499 29-3月 -16 2.4.2 from后边放置子查询考题：查询员工的姓名和薪水(考试题)select * from _____;分析：from之后的内容无非是一个集合，我们只需要自己构造一个只包含姓名和薪水的集合就好！SQL> select * from (select ename, sal from emp);ENAME SAL---------- ----------tom_abc 8000SMITH 800ALLEN 1600WARD 1250JONES 2975MARTIN 1250BLAKE 2850CLARK 2450SCOTT 3000KING 5000TURNER 1500ADAMS 1100JAMES 950FORD 3000MILLER 1300 2.5 子查询的分类 单行操作符对应单行子查询，多行操作符对应多行子查询。 按照子查询返回的条目数,分为: 单行子查询和多行子查询 单行子查询只能使用单行比较操作符( = > >= < <= <>), --ppt上的例子 多行子查询只能使用多行比较操作符(in any all) --eg 单行例子ppt例子 --eg 查询部门名称是*(不是)SALES 和 ACCOUNTING 的员工信息 2种方法方法一：SQL> select * from emp where deptno = (select deptno from dept where dname = 'SALES') or deptno = (select deptno from dept where dname = 'ACCOUNTING'); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 1 tom_abc 8000 10 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方法二：SQL> select * from emp 2 where deptno in(select deptno from dept where dname = 'SALES' or dname = 'ACCOUNTING'); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 1 tom_abc 8000 10 7934 MILLER CLERK 7782 23-1月 -82 1300 10 7839 KING PRESIDENT 17-11月-81 5000 10 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 7900 JAMES CLERK 7698 03-12月-81 950 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 注：in：等于列表中的任何一个作比较any：和子查询返回的任意一个值作比较all：和子查询返回的所有值比较 --eg 查询薪水 比30号部门 任意一个员工薪高的员工信息， 大于集合中的最小值，anySQL> select * from emp 2 where sal > any(select sal from emp where deptno = 30); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 1 tom_abc 8000 10 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 --eg 查询薪水 比30号部门 所有员工 高的员工信息， 大于集合中的最大值，all错误示例：SQL> select * from emp 2 where sal > (select sal from emp where deptno = 30);where sal > (select sal from emp where deptno = 30) *第 2 行出现错误:ORA-01427: 单行子查询返回多个行注：此时条件出返回多行正确示例：SQL> select * from emp 2 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 7902 FORD ANALYST 7566 03-12月-81 3000 20 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7839 KING PRESIDENT 17-11月-81 5000 10 1 tom_abc 8000 10方法二：SQL> select * from emp 2 where sal > (select max(sal) from emp where deptno = 30); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO--------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 1 tom_abc 8000 10 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 子查询按照执行的顺序 一般性子查询 相关子查询 2.6 子查询遇见NULL例1：查询员工表中是经理的员工信息SQL> select * from emp 2 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例2：查询员工表中不是经理的员工信息SQL> select * from emp 2 where empno not in(select mgr from emp where mgr is not null); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 1 tom_abc 8000 10 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 <span style="font-size:18px;color:#ff0000;"><strong>分析： 1.select a,b,c from table1 where deptno in(10, 20, null); 相当于======================>deptno = 10 or deptno =20 or deptno =null；2. select a,b,c from table1 where deptno not in(10, 20, null); 相当于======================>deptno = !10 and deptno !=20 and deptno != null； 矛盾！！！</strong></span> 2.7 一般情况下,子查询返回的是一个集合..子查询不排序....Top-N问题除外
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学习_练习-子查询 和 连接查询
- bzoj 1037: [ZJOI2008]生日聚会Party
- Oracle SQL 经典查询练手第二篇
- IOS中CocoaPods 从安装到使用全解
- C语言中static的作用
- poi导出excel (大数据)
- Oracle学习(六)----子查询
- JAVA中，把电脑里所有的.jpg文件保存到f盘指定目录下，并且文件不重名
- mysqldump: Got error: 1142
- 每天一篇python:文件下载篇
- 数位DP
- 【学习汇总】DIV水平居中
- QWidget: Must construct a QApplication before a QWidget
- [BZOJ1036][ZJOI2008]树的统计Count
- Android Volley完全解析(四)，带你从源码的角度理解Volley
