(9)子查询&DML操作【Oracle】

一、Note

1、子查询

1.1、概念
  • 被嵌套在SELECT/UPDATE/DELETE/INSERT语句中用()括起来的SELECT语句就是子查询
  • having/from/where等子句可以写子查询,即子查询外可以不是查询
1.2、用法
  • 从形式上看,所有的子查询都是被 小括号 括起来的

    • 没有被 小括号 括起来就无法确定子查询的边界
    • INSERT INTO … SELECT … 中的SELECT不是子查询
  • 子查询中通常不使用 ORDER BY 子句

    • 因为这样可能导致sql语句无法执行
    • FROM子句中使用的子查询可以使用 ORDER BY
  • 子查询可以嵌套多层使用,但最多不超过255层

  • 重点关注的是 子查询返回 的数据(比如有没有数据返回、返回的数据是 多少行、多少列)

1.3、运算符
  • 单行运算符

    • =、>、>=、<、<=、<>、或 !=
    • 只能比较单个值
  • 多行运算符

    • IN、NOT IN、ANY、ALL、EXISTS、NOT EXISTS
    • 可比较多个值
    • EXISTS:关注的是子查询是否有数据返回,有则返回 true,此时外部语句(主查询)中的where就可以根据exists的返回值来确定当前行数据是否保留在查询结果中,并不会关注返回的数据是什么
    • NOT EXISTS:不存在数据则返回 false,当前行数据不保留在主查询的查询结果中
1.4、分类
  • 单行子查询

    • 返回 单行、单列(最多返回一行数据)
  • 多行子查询

    • 返回 多行、单列
  • 多列子查询

    • 返回 单行、多列

    • 返回 多行、多列

      • 暂时处理不了
      • 在数据库基础阶段先不看
  • 关联子查询

    • 子查询引用了外部语句中的某些表或某些列(子查询依赖外部语句)
    • 子查询与外部语句是相互关联的(通过子查询返回的数据会影响外部语句)
    • 注意:外部语句不可以引用子查询中的表或列
  • 嵌套子查询

    • 即在子查询中继续嵌套子查询
    • 但最多不超过255层

一般先考虑连接查询,再考虑子查询

  • 子查询比表连接效率低

2、DML操作

  • 借助子查询可实现DML操作

    • update,delete也可使用子查询/关联子查询
  • 查询时可指定表的别名、列的别名,当update、delete时就可使用表的别名;列的别名用不了,因为没机会指定

二、Code

1、认识子查询

  • 当一个SELECT语句被嵌套在另外一个SELECT/UPDATE/DELETE语句中时,被嵌套的SELECT语句就是子查询;子查询一定是通过 ( ) 括起来的;子查询中尽量不要使用 ORDER BY 语句(可能导致SQL无法执行)

  • 使用子查询时,通常重点关注的是 子查询返回 多少行、多少列

  • 查询与 Simth 从事同一个岗位工作的雇员信息

    • SELECT ename , job FROM emp WHERE lower(ename)='smith';
  • 查询岗位为CLERK的雇员工号、姓名、岗位

    • SELECT empno , ename , job FROM emp WHERE job = 'CLERK' ;
  • 单行子查询:

  • 查询与Smith从事同一个岗位的雇员工号、姓名、岗位

    • SELECT empno , ename , job FROM emp WHERE job = ( SELECT job FROM emp WHERE lower(ename) = 'smith' );
    • WHERE 子句中 等号 之后的 ( ) 中就是一个 返回 单行单列 数据的 子查询 ( 通常称作 单行子查询)
  • 查询 emp 表中 哪些雇员的月薪比整个公司的平均月薪高

    • SELECT e.empno , e.ename , e.sal 
      FROM emp e 
      WHERE e.sal > ( SELECT avg(sal) FROM emp ) ;
      
  • 查询 emp 表中所有雇员的平均月薪,并列出月薪比整个公司的平均月薪高的雇员信息

    • SELECT a.avgsal , e.empno , e.ename , e.sal , e.job
      FROM (SELECT avg(sal) avgsal FROM emp) a
      INNER JOIN emp e
      ON e.sal > a.avgsal ;
      

2、子查询:单行子查询

  • 在 WHERE 子句中使用子查询,以 子查询返回的 结果为查询条件

  • 需要注意,在【单行子查询】前使用了【单行比较运算符 =】,则在 FROM / WHERE子句中使用子查询,将 子查询 返回的结果 当做一张表来使用,该表中只有 单行单列数据

  • 查询与 SMITH 在同一个岗位工作的雇员信息 ( emp )

    • SELECT empno , ename , job , deptno FROM emp WHERE job = ( SELECT job FROM emp WHERE lower(ename) = 'smith' );
  • 查询 emp 表中所有雇员的平均月薪,并列出月薪比整个公司的平均月薪高的雇员信息

    • SELECT a.avgsal , e.empno , e.ename , e.sal FROM ( SELECT avg(sal) avgsal FROM emp ) a JOIN emp e ON e.sal > a.avgsal
  • 验证是否可以在 子查询中使用 ORDER BY 语句

    • SELECT a.avgsal , e.empno , e.ename , e.sal FROM ( SELECT avg(sal) avgsal FROM emp ORDER BY 1 ) a JOIN emp e ON e.sal > a.avgsal ;
  • 哪些部门的平均月薪高于整个公司的平均月薪

    • 先找出整个公司的平均月薪:SELECT avg(sal) FROM emp ;
    • 再根据部门分组,找出月薪高于 平均月薪 的部门的部门编号和该部门的平均月薪:SELECT deptno , avg(sal) FROM emp GROUP BY deptno HAVING avg(sal) > 1982.8125 ;
  • 在 HAVING 子句中使用子查询

    • SELECT deptno , avg(sal) FROM emp
      GROUP BY deptno
      HAVING avg(sal) > ( SELECT avg(sal) FROM emp );
      

3、子查询:多行子查询/多列子查询

  • 多行子查询(单列多行)

  • 显示与20部门中雇员的岗位相同的所有雇员的信息

    • 先找出 20 部门 中所有雇员的岗位:SELECT job FROM emp WHERE deptno = 20 ;

      • 注意返回数据是 多行、单列
    • 再找出 emp 表中从事 CLERK 、MANAGER 、ANALYST 工作的雇员信息:

      SELECT empno , ename , job , sal FROM emp WHERE job IN ( 'CLERK' , 'MANAGER' , 'ANALYST' ) ;

  • 使用多行子查询实现显示与20部门中雇员的岗位相同的所有雇员的信息

    • SELECT empno , ename , job , sal FROM emp 
      WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno = 20 ) ;
      
  • 统计emp表中月薪高于20部门中任意一个员工月薪的雇员信息(统计信息中不要包含20部门的雇员)

    • SELECT empno , ename , job , sal FROM emp 
      WHERE sal > ANY (SELECT DISTINCT sal FROM emo WHERE deptno = 20 ) AND deptno !=20 ;
      
  • 多列子查询(单行多列)

  • 显示与 SMITH 在同一个部门从事同一个工作的雇员信息

    • 第一步: 找出 SMITH 所在的部门和从事的工作:

      SELECT deptno , job FROM emp WHERE ename = 'SMITH';

    • 第二步: 找出 emp 表中 在 20 部门 从事 CLERK 工作的所有雇员

      SELECT empno , ename ,sal , job , deptno FROM emp WHERE deptno = 20 AND job = 'CLERK' ;

    • 使用单个子查询作为查询条件来查询

      SELECT empno , ename ,sal , job , deptno FROM emp
      WHERE ( deptno , job ) = ( SELECT deptno , job FROM emp WHERE ename = 'SMITH' );
      
  • 统计emp表中与SMITH在同一个部门、同一个岗位、且月薪相同的雇员信息

    SELECT empno , ename ,sal , job , deptno FROM emp
    WHERE ( deptno , job , sal ) = ( SELECT deptno , job , sal FROM emp WHERE ename = 'SMITH' ) ;
    
  • 注意 等号 前 ( deptno , job , sal ) 与等号后 SELECT 语句中 诸列的顺序要一致

    前后是 成对比较;不要误理解为()里的列成对,是等号前的列与等号后的列按顺序成对

4、子查询:关联子查询

loop
	my_dept_max_sal
	loop
		-- 找出与自己部门号相同的所有雇员
		this.deptno = x.deptno
		统计这些雇员的最高月薪并将其赋值给my_dept_max_sal
	end loop ;
	-- 判断自己是否是 自己部门 月薪最高的雇员
	if(this.sal = my_dept_max_sal){
		如果自己就是 自己部门 月薪最高的雇员,就将自己的信息显示在查询结果中
	}
end loop ;
  • 关联子查询:子查询引用了外部语句中的某些表或某些列;子查询与外部语句是相互关联的

  • 统计每个部门中月薪最高的雇员信息

    • 第一步:分组统计每个部门的最高月薪

      SELECT deptno , max(sal) FROM emp GROUP BY deptno ;

    • 第二步:按每个部门的最高月薪查找相应的雇员信息

      SELECT empno , ename , sal , deptno FROM emp WHERE sal IN ( 2850 , 1400 , 3000 , 5000 ) AND deptno IN ( 10 , 20 , 30 );

    • 按部门号升序,工资降序对雇员信息排序

      SELECT empno , ename , sal , deptno FROM emp ORDER BY deptno ASC , sal DESC ;

    • 使用关联子查询

      SELECT this.empno , this.ename , this.sal , this.deptno
      FROM emp this
      WHERE this.sal = ( SELECT max(x.sal) AS my_dept_max_sal FROM emp x WHERE x.deptno = this.deptno );
      
  • 统计每个部门的最高月薪

    SELECT deptno , max(sal) FROM emp WHERE deptno IS NOT NULL GROUP BY deptno ;

    使用 关联子查询实现:

    SELECT DISTINCT my.deptno , ( SELECT max(sal) FROM emp x WHERE x.deptno = my.deptno ) maxsal FROM emp my WHERE my.deptno IS NOT NULL ;
    
  • 在 exists 或 not exists 中使用关联查询

  • 统计 emp 表中在 ‘NEW YORK’ 工作的雇员

    • SELECT e.empno , e.ename , e.job , e.deptno , d.loc FROM emp e JOIN dept d ON e.deptno = d.deptno WHERE d.loc = 'NEW YORK' ;

    • 使用关联子查询实现,主查询中不使用 dept 表

      SELECT e.empno , e.ename , e.job , e.deptno FROM emp e WHERE EXISTS ( SELECT * FROM dept d WHERE d.deptno = e.deptno AND d.loc = 'NEW YORK' ) ;

      或:

      SELECT e.empno , e.ename , e.job , e.deptno FROM emp e WHERE EXISTS ( SELECT '250' FROM dept d WHERE d.deptno = e.deptno AND d.loc = 'NEW YORK' ) ;

  • EXISTS 关注的是 子查询是否有数据返回,如果子查询有数据返回 exists 就返回 true ,否则就返回 false

  • 外部语句(这里是主查询)中的 WHERE 根据 exists 的返回值来确定当前行数据是否保留在查询结果中,如果 exists 返回 true 则 当前行数据 就会保留在 查询结果中

5、借助子查询实现DML操作

  • 使用 CTAS 方式创建表

    CREATE TABLE t_employees
    AS
    SELECT empno AS id , ename AS name , job , sal AS salary , deptno FROM emp ;
    
    CREATE TABLE t_departments 
    AS 
    SELECT deptno , dname , loc AS location FROM dept ;
    
  • 为 t_departments 指定主键约束

     ALTER TABLE t_departments 
     ADD CONSTRAINT t_departments_deptno_pk PRIMARY KEY( deptno ) ;
    
  • 为 t_employees 指定主键约束

    ALTER TABLE t_employees 
    ADD CONSTRAINT t_employees_id_pk PRIMARY KEY( id ) ;
    
  • 为 t_employees 指定外键约束

    ALTER TABLE t_employees 
    ADD CONSTRAINT t_employees_deptno_fk FOREIGN KEY ( deptno ) REFERENCES t_departments( deptno );
    
  • 删除 t_employees 表中不在 ‘NEW YORK’ 工作的雇员信息

    DELETE FROM t_employees e 
    WHERE e.deptno IN ( SELECT deptno FROM t_departments WHERE location != 'NEW YORK' );
    
    -- 回退事务
    rollback ;
    
    • 使用关联子查询实现delete操作
    DELETE FROM t_employees e 
    WHERE EXISTS ( SELECT 'x' FROM t_departments d WHERE d.deptno = e.deptno AND d.location != 'NEW YORK' );
    
  • 修改 20 和 30 部门的雇员的薪水 ( 每个雇员月薪增加 10000 )

    UPDATE t_employees SET salary = salary + 10000 WHERE deptno IN ( 20 , 30 ) ;
    
  • 为在 DALLAS 和 CHICAGO 两个地区工作的雇员月薪增加10000

    UPDATE t_employees e
    SET e.salary = e.salary + 10000
    WHERE e.deptno IN ( SELECT deptno FROM t_departments WHERE location = 'DALLAS' OR location = 'CHICAGO' ) ;
    
    • 使用关联子查询实现update操作
    UPDATE t_employees e
    SET e.salary = e.salary + 10000
    WHERE EXISTS ( SELECT 250 FROM t_departments d WHERE d.location IN ( 'DALLAS', 'CHICAGO' ) AND d.deptno = e.deptno ) ;
    
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值