ORACLE数据库(四)----子查询

今天我们来学习子查询，新手上路，大家一起来交流~

在一个查询语句中，嵌入一条查询语句，该条嵌入的语句的查询结果成为外层SQL的一部分，即子查询

一、子查询作为条件

查询结果作为一个集合，跟在WHERE或HAVING字句中

1.查询SMITH所在部门的所有员工信息
  -- 查询SMITH所在部门的编号
  -- 查询某部门编号对应的员工信息
SELECT * FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = 'SMITH');

2.查询BLAKE带领的员工有哪些
SELECT * FROM EMP WHERE MGR = (SELECT EMPNO FROM EMP WHERE ENAME = 'BLAKE');

3.查询BLAKE的领导手下有哪些员工
  -- 查询BLAKE领导是谁
  -- 查询领导编号是XXX的员工
SELECT * FROM EMP WHERE MGR = (SELECT MGR FROM EMP WHERE ENAME = 'BLAKE');

4.查询与SMITH同部门且薪资相等的员工
  -- 查询SMITH的部门和薪资
  -- 查询部门是XXX薪资是YYY的员工
SELECT * FROM EMP 
 WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = 'SMITH') 
   AND SAL = (SELECT SAL FROM EMP WHERE ENAME = 'SMITH');

-- 方法2 方便 用这个  √
SELECT * FROM EMP 
 WHERE (DEPTNO,SAL) = (SELECT DEPTNO,SAL FROM EMP WHERE ENAME = 'SMITH');

5.查询与SMITH同部门同薪资或与JAMES同部门同薪资的员工
-- 第一种方法  等号 一行 
SELECT  * 
  FROM EMP
 WHERE (DEPTNO,SAL) = (SELECT DEPTNO,SAL FROM EMP WHERE ENAME = 'SMITH')
    OR (DEPTNO,SAL) = (SELECT DEPTNO,SAL FROM EMP WHERE ENAME = 'JAMES');

-- 第二种方法   第一个IN改为等号 单行子查询返回多行 错误 
SELECT *
  FROM EMP
 WHERE (DEPTNO,SAL) IN (SELECT DEPTNO,SAL FROM EMP WHERE ENAME IN ('SMITH','JAMES'));

-- 第三种方法 错误  DEPTNO 和 SAL 可以随机的组合
SELECT *
  FROM EMP 
 WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE ENAME IN ('SMITH','JAMES'))
   AND SAL IN (SELECT SAL FROM EMP WHERE ENAME IN ('SMITH','JAMES'));


6.查询公司内薪资最高的员工
SELECT EMAME,MAX(SAL) FROM EMP;  -- 报错
SELECT * FROM EMP WHERE SAL = (SELECT MAX(SAL) FROM EMP);

7.查询公司内各部门薪资最高的员工

 -- 查询各部门MAX(SAL) 
 -- 找到员工
SELECT * 
  FROM EMP
 WHERE (DEPTNO,SAL) IN (SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO);

8.查询公司内哪个部门的平均工资高于整个公司的平均工资
 -- 查询每个部门平均工资
 -- 查询公司的平均薪资
SELECT DEPTNO ,AVG(SAL)
  FROM EMP
 GROUP BY DEPTNO
 HAVING AVG(SAL) > (SELECT AVG(SAL) FROM EMP );


9.查询公司内没有员工的部门
SELECT DEPTNO 
  FROM DEPT
  WHERE DEPTNO NOT IN (SELECT DISTINCT DEPTNO FROM EMP);

子查询用在条件中的注意点：
1）可以使用N行N列中的每一种情况
2）主查询与子查询无论在列数还是属性上都要相统一

二、子查询作为数据源

查询结果作为一个数据源，跟在FROM子句中

1.查找平均工资最高的部门
 -- 查询各个部门的平均工资
 -- 查询最高值
SELECT MAX(AVG_SAL) FROM (SELECT DEPTNO,AVG(SAL) AVG_SAL FROM EMP GROUP BY DEPTNO);

-- 查部门
SELECT DEPTNO 
  FROM EMP 
 GROUP BY DEPTNO
HAVING AVG(SAL) = (SELECT MAX(AVG_SAL) FROM (SELECT DEPTNO,AVG(SAL) AVG_SAL FROM EMP GROUP BY DEPTNO));


2.查找平均工资最低的岗位
SELECT MIN(AVG_SAL) 
  FROM (SELECT JOB,AVG(SAL) AVG_SAL FROM EMP GROUP BY JOB);

SELECT JOB
  FROM EMP 
 GROUP BY JOB 
HAVING AVG(SAL) = (SELECT MIN(AVG_SAL) FROM (SELECT JOB,AVG(SAL) AVG_SAL FROM EMP GROUP BY JOB));

子查询作为数据源的注意点：
1）数据源本身没有行列的限制，所以子查询在该处也可使用N行N列的每种情况
2）子查询作为数据源时，若SELECT子句某字段上出现了函数或计算，则该字段只有命别名才可以被主查询引用
3）主查询不关心子查询的结果是如何得到的，只关心子查询是什么结果，子查询中的字段能否被正常引用

三、子查询作为常量

查询结果作为一个常量，跟在SELECT子句中

1.查询10号部门员工的姓名、岗位、薪资及部门名称和工作地点
-- 方法1
SELECT ENAME,JOB,SAL,
       (SELECT DNAME FROM DEPT WHERE DEPTNO = 10) DNAME,
       (SELECT LOC FROM DEPT WHERE DEPTNO = 10) LOC
  FROM EMP
 WHERE DEPTNO = 10;

SELECT * FROM DEPT;
2.查询10号部门与20号部门在平均薪资上相差了多少
 -- 
SELECT 
       ABS((SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 10) - 
       (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 20) )
  FROM DUAL;

子查询作为常量的注意点：
查询结果限制在单行单列

四、子查询进行增删改

用在增删改中

1.公司今天新来一位员工PEIQI，工号1001，岗位、薪资和奖金与SMITH相同，工作地点在DALLAS
  领导是部门经理

SELECT EMPNO FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE LOC = 'DALLAS') AND JOB = 'MANAGER'

INSERT INTO EMP 
VALUES('1001','PEIQI',
       (SELECT JOB FROM EMP WHERE ENAME = 'SMITH'),
       (SELECT EMPNO FROM EMP WHERE DEPTNO = 
               (SELECT DEPTNO FROM DEPT WHERE LOC = 'DALLAS') AND JOB = 'MANAGER'),
       TRUNC(SYSDATE,'DD'),
       (SELECT SAL FROM EMP WHERE ENAME = 'SMITH'),
       (SELECT COMM FROM EMP WHERE ENAME = 'SMITH'),
       (SELECT DEPTNO FROM DEPT WHERE LOC = 'DALLAS')
);

SELECT * FROM EMP;

2.删掉SALES部门的所有员工
SELECT * FROM DEPT;
DELETE FROM EMP 
 WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME = 'SALES');

3.将30号部门的员工底薪更新为公司的平均薪资
 -- 公司的平均底薪
SELECT * FROM EMP WHERE DEPTNO = 30;
SELECT AVG(SAL) FROM EMP;
UPDATE EMP SET SAL = (SELECT AVG(SAL) FROM EMP) 
 WHERE DEPTNO = 30;

子查询的其他注意点：
1）子查询的结果不要放在GROUP BY和ORDER BY子句中，依照之前所述，根据常量分组，与未进行分组无异，排序同理。
2）一条SQL中可以多次出现子查询，这些子查询可以单独出现，也可以层层嵌套
3）子查询也可以出现在函数和计算中

五、子查询分类

相关子查询：子查询语句不能独自运行，子查询中牵扯主查询中的一部分内容，主查询只在乎与子查询的关系

非相关子查询：子查询语句能够独自运行，子查询中不牵扯主查询中的任何内容，主查询在乎的是子查询的结果

EXISTS : 对应条件查询中的IN
NOT EXISTS : 对应条件查询中的NOT IN
优化用EXISTS代替IN

1 语法

SELECT T1.COL_LIST
  FROM TB_NAME1 T1
 WHERE [NOT] EXISTS (SELECT T2.COL_LIST -- 与查询结果无关 写什么都可以
			                 FROM TB_NAME2 T2
			                WHERE T1.COL_NAME = T2.COL_NAME  --连接两个表的字段
                        AND T2.CONDITION);

2 注意

1.非相关子查询可以用在任何位置，而相关子查询仅用在WHERE或HAVING子句中作为条件，且绝大多数情况下两者可以发生转换。
2.非相关子查询在书写和理解上都更加容易，而相关子查询之所以存在，是因为在很多情况下使用相关子查询能有更高的运行效率。

查询与SMITH同部门且薪资相等的员工
-- 拿出来没有建立关系的 
SELECT * 
  FROM EMP T1
 WHERE NOT EXISTS (SELECT T2.DEPTNO 
                 FROM EMP T2
                WHERE T1.DEPTNO = T2.DEPTNO
                  AND ENAME = 'SMITH');
                  

--  情况2   只剩下10 号部门   *********
SELECT * 
  FROM EMP T1
 WHERE NOT EXISTS (SELECT * 
                 FROM EMP T2
                WHERE T1.DEPTNO = T2.DEPTNO
                  AND ENAME IN ('SMITH','BLAKE'));
       

没有显示和上面相同的结果，因为需要找到和SMITH和BLAKE不同部门的人，与20号部门不同的有 10号和30号， 与30号部门不同的有10号和20号，综上10号20号30号部门的人都有

-- SMITH 20号部门  BLAKE 30号部门
SELECT * 
  FROM EMP T1
 WHERE EXISTS (SELECT * 
                 FROM EMP T2
                WHERE T1.DEPTNO <> T2.DEPTNO
                  AND ENAME IN ('SMITH','BLAKE'));

六、子查询的书写步骤：

1.将需求拆分成不同的步骤
2.按照步骤的先后分别书写SQL
3.将子查询嵌套到主查询中即可
4.若是需要书写相关子查询，可以先写成非相关子查询再转成相关子查询

七、非相关向相关的转化过程

1.主查询的字段及关系 替换成 EXISTS
2.主查询与子查询的关联关系写在子查询中
3.该加表别名的加别名

8.查询公司内哪个部门的平均工资高于整个公司的平均工资
 -- 查询每个部门平均工资
 -- 查询公司的平均薪资
SELECT DEPTNO,AVG(SAL)
  FROM EMP
 GROUP BY DEPTNO
 HAVING AVG(SAL) > (SELECT AVG(SAL) FROM EMP );
 
 -- 改为相关子查询
8.查询公司内哪个部门的平均工资高于整个公司的平均工资
 -- 查询每个部门平均工资
 -- 查询公司的平均薪资
SELECT DEPTNO ,AVG(SAL)
  FROM EMP T1
 GROUP BY DEPTNO
 HAVING EXISTS (SELECT AVG(SAL)
                  FROM EMP T2
                 GROUP BY 1
                HAVING AVG(T1.SAL) > AVG(T2.SAL));