Oracle之子查询
来源：互联网 发布：php接口开发教程 编辑：程序博客网 时间：2024/05/17 06:07
1、为什么要有子查询bg：查询工资比scott高的员工信息<1>查询scott的工资SQL> select sal from emp where ename = 'SCOTT'; SAL---------- 3000<2>查询比工资比3000大的员工SQL> select * from emp where sal > 3000; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7839 KING PRESIDENT 17-11月-81 5000 10<3>分两步太麻烦，于是二者结合，子查询诞生SQL> select * from emp where sal > (select sal from emp where ename = 'SCOTT'); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7839 KING PRESIDENT 17-11月-81 5000 10<4>透过现象看本质：子查询就是多个select语句的嵌套2、子查询的游戏规则（1）良好的书写风格与习惯（这点在哪都很重要）；（2）子查询外面的()不要忘记；（3）子查询和主查询查询的可以是同一张表,也可以不是同一张表，只要子查询返回的结果,主查询可用即可；eg：使用2种方法查询部门名称是SALES的员工信息<1>方法一SQL> select * from emp 2 where deptno = (select deptno 3 from dept 4 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行。<2>方法二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---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 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已选择6行。（4）子查询放置的位置select a , b, c ---可以，但只能存放置单行子查询,不能放置多行子查询from tab1 ---可以where ... ---可以group by ... ---不可以having ...---可以order by ...---不可以<1>多行子查询（错误）SQL> select ename,empno,(select deptno from emp) deptno 2 from emp;select ename,empno,(select deptno from emp) deptno *第 1 行出现错误:ORA-01427: 单行子查询返回多个行<2>单行子查询（正确）SQL> select ename,empno,(select deptno from emp where empno = 7369) 2 from emp;ENAME EMPNO (SELECTDEPTNOFROMEMPWHEREEMPNO=7369)---------- ---------- ------------------------------------SMITH 7369 20ALLEN 7499 20WARD 7521 20JONES 7566 20MARTIN 7654 20BLAKE 7698 20CLARK 7782 20SCOTT 7788 20KING 7839 20TURNER 7844 20ADAMS 7876 20JAMES 7900 20FORD 7902 20MILLER 7934 20已选择14行。<3>查询员工的姓名和薪水SQL> select * from (select ename,sal from emp);ENAME SAL---------- ----------SMITH 800ALLEN 1600WARD 1250JONES 2975MARTIN 1250BLAKE 2850CLARK 2450SCOTT 3000KING 5000TURNER 1500ADAMS 1100JAMES 950FORD 3000MILLER 1300已选择14行。<4>查询部门名称是SALES和ACCOUNTING的员工信息SQL> select * from emp 2 where deptno in(select deptno from dept 3 where dname = 'SALES' or 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行。（5）子查询分类<1>按照子查询返回的条目数，分为单行子查询和多行子查询说明：1. 单行操作符对应单行子查询，多行操作符对应多行子查询2. 单行子查询只能使用单行比较操作符( = > >= < <= <>)3. 多行子查询只能使用多行比较操作符(in any all)eg：1>查询薪水比30号部门任意一个员工薪高的员工信息（大于集合中的最小值）SQL> select * from emp 2 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 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行。2>查询薪水比30号部门所有员工高的员工信息（大于集合中的最大值）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 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<2>按照子查询执行的顺序，分为一般子查询和相关子查询1.一般子查询:子查询的结果被主查询使用eg：找到员工表中薪水大于本部门平均薪水的员工SQL> select e.empno,e.ename,e.sal,d.avgsal,e.deptno from emp e, 2 (select deptno,avg(sal) avgsal from emp group by deptno) d 3 where e.deptno = d.deptno and e.sal > d.avgsal 4 order by e.deptno; EMPNO ENAME SAL AVGSAL DEPTNO---------- ---------- ---------- ---------- ---------- 7839 KING 5000 2916.66667 10 7566 JONES 2975 2175 20 7788 SCOTT 3000 2175 20 7902 FORD 3000 2175 20 7499 ALLEN 1600 1566.66667 30 7698 BLAKE 2850 1566.66667 30已选择6行。2.相关子查询:主查询的参数让子查询用（一般是通过别名技术）eg：找到员工表中薪水大于本部门平均薪水的员工SQL> select e.empno,e.ename,e.sal,(select avg(sal) from emp where deptno = e.deptno) avgsal,e.deptno 2 from emp e 3 where e.sal > (select avg(sal) from emp where deptno = e.deptno) 4 order by e.deptno; EMPNO ENAME SAL AVGSAL DEPTNO---------- ---------- ---------- ---------- ---------- 7839 KING 5000 2916.66667 10 7566 JONES 2975 2175 20 7902 FORD 3000 2175 20 7788 SCOTT 3000 2175 20 7499 ALLEN 1600 1566.66667 30 7698 BLAKE 2850 1566.66667 30已选择6行。（6）当子查询遇见nulleg：查询不是经理的员工信息<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已选择6行。<2>查询不是经理的员工信息SQL> select * from emp 2 where empno not in(select mgr from emp);未选定行<3>没有任何数据返回，why？因为not in后面的集合中出现了空值（KING的MGR为空），还记得之前提过的当not in集合中包含了null会使查询结果受到影响的事吗？<4>解决办法SQL> select * from emp 2 where empno not in (select mgr from emp 3 where mgr is not null); 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行。
阅读全文
0 0
- Oracle之子查询
- Oracle之子查询
- oracle数据库之子查询
- Oracle之子查询
- Oracle笔记之子查询
- oracle之子查询常用
- Oracle数据库之子查询
- Oracle之子查询
- oracle强化练习之子查询
- oracle SQL语句之子查询
- 10-Oracle入门之子查询
- Oracle数据库笔记之子查询
- ORACLE多表查询之子查询
- Oracle数据库的查询之子查询(六)
- oracle 基本语句学习 (三)之子查询
- Oracle之子查询（认证考试指南）
- Oracle常用sql语句(三）之子查询
- SQL数据库之子查询
- AXI4 STREAM DATA FIFO
- hadoop 2.0安装的详细过程
- HDU 6153 A Secret（KMP）
- 在瑞安的普及比赛上翻车了……
- 关于数组套数组的看法
- Oracle之子查询
- QGC之qml
- Javascript—循环
- java八大排序算法之冒泡排序
- MFC添加背景图片
- HDU 6153 A Secret （扩展KMP）
- leetcode 650. 2 Keys Keyboard(dp)
- Oracle之DML
- kmp与exkmp算法讲解
