12.高级DQL-子查询

子查询

• 在SELECT查询中，WHERE查询条件中的限制条件不是一个确定的值，而是来自于另外一个查询的结果

• 为了给查询提供数据而首先执行的查询语句叫做子查询，子查询嵌入的语句称作主查询或父查询

• 子查询是嵌入在其他SQL语句中的SELECT语句，大部分时候出现在WHERE子句中

• 主查询可以是SELECT语句，也可以是其他类型的语句比如DML或DDL语句

主查询是DQL语句

• 常用于过滤条件，大部分时候出现在DQL语句中的WHERE子句

• FROM子句用来指定要查询的表

  • 如果要在一个子查询的结果中继续查询，则子查询出现在FROM子句中，这个子查询也称作行内视图或者匿名视图

  • 把子查询当作视图对待，但视图没有名字，只能在当前的SQL语句中有效

• 把子查询放在SELECT子句部分，可以将查询的结果当作主查询记录中的一个字段值显示

  • 当子查询所代表的字段中没有数据时，就被设置为NULL，可以认为是外连接的另一种表现形式

• 子查询可以存在于HAVING子句中

主查询是DDL语句

• 以子查询的结果作为创建一个表的依据，语法：CREATE TABLE 表名(字段信息) AS 子查询语句

• 当创建表时使用子查询，查询中的字段有别名则该表对应的字段就使用该别名作为其字段名，当子查询中一个字段含有函数或表达式，那么该字段必须给别名

主查询是DML语句

• 子查询语句在DML语句中一般用于在WHERE子句的限定条件上，然后再进行增删改操作

子查询的分类

• 根据返回结果的不同，子查询可分为：

  • 单行单列子查询：常用于过滤条件，可以配合单行比较符 >、>=、<、<= 使用

  • 多行单列子查询：常用于过滤条件，由于查询出多个值，在主查询中要使用多行比较符IN、ALL、ANY其中ALL和ANY不能单独使用，需要配合单行比较操作符

  • 多行多列子查询：常当作一张表看待

EXITST 关键字

• 在主查询中需要引入到子查询的字段数据，使用 EXISTS 关键字

  • EXISTS 后边的子查询至少返回一行数据，则整个条件返回TRUE

  • NOT EXTIST，则是后面的子查询没有返回数据时，则整个条件返回TRUE

演示：子查询

• 在DQL中使用子查询：WHERE子句中使用子查询

  • 查看比CLARK工资高的员工

    SELECT ename,sal FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename=‘CLARK’);

  • 查看和CLARK相同职位的员工

    SELECT ename,job FROM emp WHERE job=(SELECT job FROM emp WHERE ename=‘CLARK’);

  • 查看与CLARK同部门的员工

    SELECT ename,deptno FROM emp WHERE deptno=(SELECT deptno FROM emp WHERE ename=‘CLARK’);

  • 查找薪水比整个机构平均薪水高的员工

    SELECT ename,sal FROM emp WHERE sal>(SELECT AVG(sal) FROM emp);

• 在DQL中使用子查询：FROM子句中使用子查询

  • 查看比自己所在部门平均工资高的员工
    SELECT e.ename,e.sal,e.deptno
    FROM emp e,(SELECT deptno,AVG(sal) sal FROM emp GROUP BY deptno) s
    WHERE e.deptno=s.deptno AND e.sal>s.sal;

• 在DQL中使用子查询：SELECT子句中使用子查询

  SELECT e.ename,e.sal,(SELECT d.dname FROM dept d WHERE d.deptno=e.deptno) dname FROM emp e;

• 在DQL中使用子查询：HAVING子句中使用子查询

  • 查看每个部门的最低薪水是多少，前提时该部门的最低薪水要高于30号部门的最低薪水
    SELECT deptno,MIN(sal) FROM emp
    GROUP BY deptno HAVING MIN(sal)>(
    SELECT MIN(sal) FROM emp WHERE deptno=30
    );

• 在DDL中使用子查询

  • 创建表employee，数据为现有表中emp和dept对应的数据
    CREATE TABLE employee
    AS
    SELECT e.empno id,e.ename name,e.job,e.sal*12 salary,e.deptno,d.dname,d.loc
    FROM emp e,dept d
    WHERE e.deptno=d.deptno(+);

  SELECT * FROM employee;

• 在DML中使用子查询

  – 将CLARK所在部门的所有员工删除
  DELETE FROM employee WHERE deptno=(SELECT deptno FROM employee WHERE name=‘CLARK’);

• 多行多列子查询

  • 查询与职位是SALESMAN的同部门其他职位的员工信息
    SELECT ename,job,deptno FROM emp
    WHERE deptno IN(SELECT deptno FROM emp WHERE job=‘SALESMAN’) AND job<>‘SALESMAN’;

  • 查看比职位是CLERK和SALESMAN工资都高的员工
    SELECT ename,sal FROM emp
    WHERE sal >ALL(
    SELECT sal FROM emp WHERE job IN(‘CLERK’,‘SALESMAN’)
    );

• EXISTS

  • 查询emp表中已有的部门的部门信息
    SELECT d.deptno,d.dname FROM dept d
    WHERE EXISTS(SELECT * FROM emp e WHERE d.deptno=e.deptno);

  • 查询emp表中没有的部门的部门信息
    SELECT d.deptno,d.dname FROM dept d
    WHERE NOT EXISTS(SELECT * FROM emp e WHERE d.deptno=e.deptno);