（9）子查询&DML操作【Oracle】

最新推荐文章于 2023-06-04 22:47:23 发布

山风fly

最新推荐文章于 2023-06-04 22:47:23 发布

阅读量427

点赞数

分类专栏： oracle 文章标签：数据库 oracle

本文链接：https://blog.csdn.net/qq_43587449/article/details/107735260

版权

oracle 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

文章目录

一、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 ) ;