高级子查询【weber出品必属精品】
2014-08-19 13:59 yaoweber 阅读(196) 评论(0) 编辑 收藏 举报-
多列子查询
where条件中出现多列与子查询进行比较
多列子查询分为：成对比较和非成对比较
成对比较：
SQL> select ename,sal,job from emp where (deptno,job) in(select deptno,job from emp where ename='SCOTT'); ENAME SAL JOB ------ ----- --------- FORD 3000 ANALYST SCOTT 4000 ANALYST
非成对比较：
select ename,sal,job from emp where deptno=(select deptno from emp where ename='SCOTT') and job = (select job from emp where ename='SCOTT') ENAME SAL JOB ------ ----- --------- SCOTT 4000 ANALYST FORD 3000 ANALYST
- 标量子查询表达式
标量子查询是从一行中返回一列值的子查询
标量子查询可用于:
1. DECODE 和CASE的条件和表达式部分
2. 除了GROUP BY之外的所有SELECT语句
CASE表达式中使用标量子查询：CASE表达式可以实现if --then-- else的功能
SQL> select employee_id,last_name,(
case
when department_id=(select department_id from departments where location_id=1800)
then 'CANDA' else'USA' end ) location
from employees; EMPLOYEE_ID LAST_NAME LOCAT ----------- ------------------------- ----- 198 OConnell USA 199 Grant USA 200 Whalen USA 201 Hartstein CANDA 202 Fay CANDA 203 Mavris USA 204 Baer USA 205 Higgins USA 206 Gietz USA 100 King USA 101 Kochhar USA .......................
107 rows selected. - ORDER BY子句中使用标量子查询
SQL> select ename,sal,deptno from emp order by (select dname from dept where emp.deptno=dept.deptno); ENAME SAL DEPTNO ---------- ---------- ---------- CLARK 2450 10 KING 5000 10 MILLER 1300 10 JONES 2975 20 FORD 3000 20 ADAMS 1100 20 SMITH 800 20 SCOTT 3000 20 WARD 1250 30 TURNER 1500 30 ALLEN 1600 30 JAMES 950 30 BLAKE 2850 30 MARTIN 1250 30 14 rows selected.
- 相互关联的子查询
相互关联子查询用于行-执行-行的处理主查询的每一行子查询都要执行一次：通俗的说就是，主查询--->子查询--->主查询。就是这个执行顺序。
---从emp表中查询 薪水大于 部门平均薪水 的用户名，部门编号和薪水，没有大于的则不会被显示出来。
select ename,deptno,sal from emp outer where sal>(select avg(sal) from emp inner where outer.deptno=inner.deptno ); ENAME DEPTNO SAL ------ ------ ----- ALLEN 30 1600 JONES 20 2975 BLAKE 30 2850 SCOTT 20 4000 KING 10 5000 FORD 20 3000 - 再举一个相互关联的例子
---查询至少换了两次工作的员工---
SQL> select employee_id,last_name,job_id from employees e where 2<=(select count(*) from job_history j where e.employee_id=j.employee_id); EMPLOYEE_ID LAST_NAME JOB_ID ----------- ------------------------- ---------- 200 Whalen AD_ASST 101 Kochhar AD_VP 176 Taylor SA_REP - 使用EXISTS
EXISTS 检测子查询中行的存在性
1.如果值存在于子查询中:
终止在子查询中的查找
条件返回TRUE2.如果值不存在于子查询中:
条件返回FALSE
继续在子查询中的查找---示例：查找是领导的员工
SQL> select empno,ename from emp outer where exists(select mgr from emp inner where outer.empno=inner.mgr); EMPNO ENAME ---------- ---------- 7566 JONES 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7902 FORD 6 rows selected.另一种写法：
SQL> select empno ,ename from emp where empno in(select mgr from emp); EMPNO ENAME ---------- ---------- 7566 JONES 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7902 FORD 6 rows selected.
- 我们来做另一个实验：查找不是领导的员工，也就是最基层的员工了。
SQL> select empno,ename from emp outer where not exists (select 1 from emp inner where outer.empno=inner.mgr); EMPNO ENAME ---------- ---------- 7369 SMITH 7499 ALLEN 7521 WARD 7654 MARTIN 7844 TURNER 7876 ADAMS 7900 JAMES 7934 MILLER 8 rows selected.
---这里解释一下：not exists里的整数1的作用，只要是个整数即可，‘abc’字符串也可以。 - 关联UPDATE
使用相互关联的子查询更新一个表的行基于另外一个表的行
SQL> create table e as select * from emp; 表已创建 SQL> alter table e add dname varchar2(10); 表已更改。 SQL> desc e 名称 是否为空? 类型 ----------------------------------------- -------- ---------------------------- EMPNO NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) DNAME VARCHAR2(10) SQL> select * from e; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME ----- ------ --------- ----- -------------- ----- ---------- ------ ---------- 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 19-4月 -87 4000 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 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 * from dept; DEPTNO DNAME LOC ------ -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> update e set dname=(select dname from dept d where e.deptno=d.deptno); 已更新14行。 SQL> commit; 提交完成。 SQL> select * from e; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME ----- ------ --------- ----- -------------- ----- ---------- ------ ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20 RESEARCH 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 SALES 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 SALES 7566 JONES MANAGER 7839 02-4月 -81 2975 20 RESEARCH 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 SALES 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 SALES 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 ACCOUNTING 7788 SCOTT ANALYST 7566 19-4月 -87 4000 20 RESEARCH 7839 KING PRESIDENT 17-11月-81 5000 10 ACCOUNTING 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 SALES 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 RESEARCH 7900 JAMES CLERK 7698 03-12月-81 950 30 SALES 7902 FORD ANALYST 7566 03-12月-81 3000 20 RESEARCH 7934 MILLER CLERK 7782 23-1月 -82 1300 10 ACCOUNTING
- 相互关联的DELETE
使用相互关联的DELETE删除一个表的行基于另外一个表的行
SQL> conn hr/hr 已连接。 SQL> create table emp as select * from employees; 表已创建。 SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- REGIONS TABLE COUNTRIES TABLE LOCATIONS TABLE DEPARTMENTS TABLE JOBS TABLE EMPLOYEES TABLE JOB_HISTORY TABLE EMP TABLE EMP_DETAILS_VIEW VIEW 已选择9行。 SQL> delete emp where exists(select employee_id from job_history where emp.employee_id=employee_id); 已删除7行。 SQL> roll
- WITH子句
使用WITH子句, 在复杂查询中如果相同的查询块多次出现，可以重复使用
WITH子句将结果存放到用户的临时表空间中
WITH子句提高了性能
select dname,sum(sal) from emp e,dept d where e.deptno=d.deptno group by dname having sum(sal) >(select sum(total_sal)/count(*)from (select dname,sum(sal) total_sal from emp e,dept d where e.deptno=d.deptno group by dname))
- 使用with子句
with dept_costs as(select dname,sum(sal) total_sal from emp e,dept d where e.deptno=d.deptno group by dname), avg_costs as (select sum(total_sal)/count(*) avg_sal from dept_costs) select * from dept_costs where total_sal>(select avg_sal from avg_costs) order by dname
