SQL 基础--> 子查询

时间:2016-08-23
简介:数据库|一、子查询子查询就是位于SELECT、UPDATE、或DELETE语句中内部的查询 二、子查询的分类单行子查询返回零行或一行多行子查询返回一行或多行多列子
一、子查询
子查询就是位于SELECT、UPDATE、或DELETE语句中内部的查询
    
二、子查询的分类
单行子查询
返回零行或一行
多行子查询
返回一行或多行
多列子查询
返回多列
相关子查询
引用外部SQL语句中的一列或多列
嵌套子查询
位于其它子查询中的查询
 
三、子查询语法
 
SQL>
SELECT select_list
 FROM table
WHERE expr operator (SELECT select_list FROM table);
 
子查询(内部查询)在执行主查询之前执行一次,然后主查询(外部查询)会使用该子查询的结果
 
四、子查询的规则
将子查询括在括号中
将子查询放置在比较条件的右侧
只有在执行排序Top-N分析时,子查询中才需要使用ORDER BY 子句
单行运算符用于单行子查询,多行运算符用于多行子查询
 
五、单行子查询
仅返回一行
使用单行的表较运算符:= ,>, >= ,< , <= ,<>
 
--在WHERE 子句中使用子查询
SQL>
select ename, job
 from emp
where empno = (select empno from emp where mgr = 7902);
 
--使用分组函数的子查询
SQL>
select ename, job, sal from emp where sal > (select avg(sal) from emp);
 
--在HAVING子句中使用子查询
SQL>
select deptno, min(sal)
 from emp
group by deptno
having min(sal) > (select min(sal) from emp where deptno = 20);
 
--在FROM 子句中使用子查询
SQL>
    select empno, ename from (select empno, ename from emp where deptno = 20);
    
--单行子查询中的常见错误
--子查询的结果返回多于一行
SQL>
select empno, ename
 from emp
where sal = (select sal from emp where deptno = 20);
 
(select sal 返回多行
ORA-01427: single-row subquery returns more than one row
 
--子查询中不能包含ORDER BY子句
SQL>
        select empno, ename
  from emp
 where sal > (select avg(sal) from emp order by empno);
order by empno)
 
ORA-00907: missing right parenthesis
       
--子查询内部没有返回行,如下语句可以正确执行,但没有数据返回
SQL>
select ename, job
 from emp
where empno = (select empno from emp where mgr = 8000);
 
返回:no rows selected
 
六、多行子查询
返回多个行
使用多行比较运算符IN ,ANY ,ALL
 
--在多行子查询中使用IN 操作符
SQL>
select empno, ename, job
 from emp
where sal in (select max(sal) from emp group by deptno);
 
--在多行子查询中使用ANY 操作符
备注:
{
比如集合(1,2,3,4)
>any表示只要大于其中任意一个就行
>all表示要比其中的任何一个都大
}
SQL>
select empno, ename, job
 from emp
where sal < any (select avg(sal) from emp group by deptno);
 
--在多行子查询中使用ALL 操作符
SQL>
select empno, ename, job
 from emp
where sal > all (select avg(sal) from emp group by deptno);
 
七、相关子查询
子查询中使用了主查询中的某些字段,主查询每扫描一行都要执行一次子查询
 
--查询工资高于同一部门的员工的平均工资的 部门号,姓名,工资
SQL>
select deptno, ename, sal
 from emp
outer where sal > (select avg(sal)
                     from emp
                    inner where inner.deptno = outer.deptno);
 
--查询负责管理其它员工的员工记录(使用exists)
注:EXISTS 是判断是否存在,和in类似,但效率要比in高
SQL>
select empno, ename
 from emp
outer where exists
(select empno from emp inner where inner.mgr = outer.empno);
 
--查询不管理其它员工的职员(not exists)
SQL>
select empno, ename
 from emp
outer where not exists
(select empno from emp inner where inner.mgr = outer.empno)
 
备注:EXISTS 和NOT EXISTS 与IN 和NOT IN 的比较
EXISTS与IN的不同:
EXISTS只检查行的存在性,IN 要检查实际值的存在性(一般情况下EXISTS的性能高于IN)
NOT EXISTS 和NOT IN:
当值列表中包含空值的情况下,NOT EXISTS 则返回true,而NOT IN 则返回false.
 
--看下面的查询,查询部门号不在emp表中出现的部门名称及位置
SQL>
select deptno, dname, loc
 from dept d
where not exists (select 1 from emp e where e.deptno = d.deptno);
 
--IN与空值
SQL>
SELECT *
 FROM emp e
WHERE e.empno NOT IN (SELECT 7369
                        FROM dual
                      UNION ALL
                      SELECT NULL FROM dual);
SQL>
SELECT * FROM emp e WHERE e.empno IN ('7369', NULL);
 
注:子查询要包含在括号内
子查询一般放在比较条件的右侧
除非进行TOP-N 分析,否则不要在子查询中使用ORDER BY。
 
八、多列子查询
1、成对比较
 
--查询工资为部门最高的记录
SQL>
select *
 from scott.emp
where (sal, job) in (select max(sal), job from scott.emp group by job);
 
--非成对比较,实现了与上述类似的功能
SQL>
select *
 from scott.emp
where sal in (select max(sal) from scott.emp group by job)
  and job in (select distinct job from scott.emp);
 
九、嵌套子查询
即位于子查询内部的子查询,嵌套层数最多可达层。然而应尽量避免使用嵌套子查询,使用表连接的查询性能会更高
 
SQL>
select deptno, Num_emp
 from (select deptno, count(empno) as Num_emp from emp group by deptno) d
where Num_emp > 3;
 
注意:子查询对空值的处理除了count(*)外,都会忽略掉空值

TAGS:SQ子查询

上一编:sql搜索like通配符的用法详解

下一编:SQL语句 不足位数补0

New Hot