子查询

一、子查询和视图

    子查询就是一次性视图，与视图不同的是，子查询在select语句执行完毕后会消失。

    子查询在主查询执行前执行一次。

    子查询的结果被用于主查询。

    1、子查询的分类

         单行单列子查询：一定返回一行。单行操作符：>,<,=，>=,<=,<>

         多行单列子查询：返回一列多行

         单行多列子查询：返回零行或多行。

         多行操作符：

            in 等于列表中的任何值。（不能用 NOT IN）

            ANY 与子查询返回的每个值进行比较。(小于是小于最大的，大于是大于最小的)

            ALL（小于是小于最小的，大于是大于最大的）

    2、单行单列子查询

            子查询返回一个值，单行操作符：>,<,=，>=,<=,<>

            例1：查询工资大于Jones的员工姓名及工资

            

            例2：查询和Smith同一部门的员工姓名和工资。

            

    3、多行单列子查询

            子查询返回多行单列，in，not int，any，all

    1)in

            逐个比较是否有匹配值。

            例：查看sales或accounting部门的员工工资

            

    2)not in

            与in不同的是，not in里边如果有空值，则返回空值。

            例：查询没有下属的员工

            

            Not只有not in组合，没有not any和not all组合。

    3)any

            Any强调的是只要符合其中的一个条件就可以了。

            例：查询工资比30号部门任一员工工资高的员工

            

            相当于

            

    4)all

            例:查询比部门30所有员工的工资都要高度员工信息

            

4、多行多列子查询

        子查询中返回多列结果集。

        例1：查询哪些员工的工资是所在职务中工资最高的。

        Select ename,job,sal from emp where(sal,job) in (select max(sal),job from emp group by job);

        

        多列子查询就是子查询中不止一个列名。

5、子查询注意的问题

        1)要有括号

        2)合理的书写分格

        3)可以在主查询where、select、having、from后面使用子查询

            Select后面使用子查询，只能使用单行子查询，即只允许返回一条记录。

            例1：

            

            例2：按部门分组查询最低工资,且最低工资大于20号部门的最低工资才显示

            

        4)不可以在group by 后面使用子查询

        5)强调from后面的子查询。

        6)主查询和子查询可以不是同一张表，只要子查询返回的结果主查询可以使用即可。

            例：查询部门名称是sales的员工.

            

            还可以使用多表连接查询。

            

        7)一般不再子查询中排序，但在top-n分析中，必须对子查询排序。

            例：Top-n分析: 找出员工工资表的前三名

            

        8)一般先执行子查询，在执行主查询。

        9)单行只查询只能使用单行操作符，多行子查询只能使用多行操作符。

            例：查询工资最低的员工的职位和薪水

            

        10)单行子查询空值问题，不能贸然使用=或不等于，因为后面返回null就永远不成立。

            子查询可能返回null ,而判断是否为空,不能使用 = 或者 !=

            

            多行子查询中的null: 集合有空值,不能用not in 可以用in

            例：查询不是领导的员工

            错误的写法：

            

            正确的写法：

            

6、内联视图

        From子句中使用子查询。

        例：员工的工资大于他所在部门的平均工资，显式其信息。

        第一步先看部门的平均工资。

        

        第二步，把这个结果集作为内联视图，起个别名，和emp表连接

        

二、标量子查询

    1、子查询的名称

        在oracle的from子句中，不能使用as(会发生错误)。

        例：找出员工工资表的前三名

        

    2、标量子查询

        标量子查询也就是单行单列子查询，必须而且只能返回1行1列的结果。

    3、标量子查询的书写位置

        能够使用常数或者列名的地方，无论是select子句，group by子句，having子句，还是order by子句，几乎所有的地方都可以使用。

        注意事项：该子查询决不能返回多行结果。

三、关联子查询

        所谓非关联子查询，就是子查询可以脱离主查询独立存在。前边的内容基本都是非关联子查询。

        所谓关联子查询，就是子查询与主查询之间有条件关联，不能独立执行。

        其子查询内部会引用主查询中的一列或多列。在执行时，外部查询的每一行都被一次一行的传递给子查询，子查询以此读取主查询传递来的每一个值，并将其用到子查询上，知道主查询所有的行都处理完毕为止，最后返回查询结果。

        理论上主查询有几行，子查询就被调用几次。

        关联子查询实际只能返回1行结果，这也是关联子查询查询不出错的原因。

        例1：关联查询显式员工的工资大于他所在部门的平均工资。

        

        例2：找出每个部门中工资最高的人。

        用非关联的写法：

        Select deptno,ename from emp where (deptno,sal)in (select deptno,max(sal) from emp group by deptno);

        

        用关联的写法：

        Select outer.deptno,outer.ename,outer.salfrom emp outer where sal=(select max(sal) from emp where deptno=outer.deptno);

        

1、exists

        Exists只要在子查询中找到一个行值就行，如果子查询中有行值，就立即停止子查询的搜索。然后返回true，如果没有没有相应的行值，则返回true。

        例：Exists显式emp表中哪些员工不是普通员工。

        

        子查询中的x只是占位，关联查询只是关心子查询中是否存在。

2、not exists

        显式dept表中还没有员工的部门。

        

        引入exists的目的：在一些情况下，只需要子查询返回一个真值或是假值。如果只考虑是否满足判断条件，而数据本身并不重要，可以使用exists操作符来定义子查询。

        

        以上SQL语句如果子查询(select * from dept where deptno = ‘40’) 有结果，那么 exists 前面的语句会执行，如果 exists 后面的没结果，那么其前面的不会执行。

四、练习题

    1、写出如下结果的select语句，其中sale_price_all列为全部商品的平均销售单价。

    原表如下：

    

    需要得到的结果如下：

    

    解：select product_id,product_name,product_type,sale_price, (selectavg(sale_price) from product) sale_price_all from product;

    

    2、请根据下面条件编写一条SQL语句，创建一幅包含如下数据的视图(AvgPriceByType)。

        条件1：销售单价大于等于1000日元。

        条件2：登记日期是2009年9月20日。

        条件3：包含商品名称、销售单价和登记日期三列。

        执行结果：

        

        解：第一步：求出各个商品类别的平均销售单价，即avg_sale_price.

        

        第二步：按关联查询查找出执行的结果。

        

        第三步：创建视图。

        

        

        3、列出至少有4个员工的所有部门和部门信息。

        解：第一步：先找出每个部门有多少员工。

        

        第二步：找出员工数大于等于4个的部门。

        

        第三步：找出信息。

        

        还可以用多表连接方法：

        

        4、列出各个部门的MANAGER的最低薪金

        

        5、给任职日期超过37年的人加薪30%。

        

        6、列出每个部门工作的员工数量、平均工资和平均服务期限（单位为年）。