SQL子查询实战：高效查询技巧

/*
子查询（非常重要）
一个select语句中包含另一个完整的select语句。
子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。
子查询出现的位置：
where后，作为条为被查询的一条件的一部分；
from后，作表；
当子查询出现在where后作为条件时，还可以使用如下关键字：
any
all
子查询结果集的形式：
单行单列（用于条件）
单行多列（用于条件）
多行单列（用于条件）
多行多列（用于表）
*/
# 1.工资高于JONES的员工。
# 前面写的SQL语句形式：工资高于1500的员工的信息
SELECT * FROM emp WHERE sal >1500;

# 第一步：查询JONES的工资

SELECT sal FROM emp WHERE ename='JONES';

# 第二步：工资高于JONES的员工-->工资高于2975.00的员工信息

SELECT * FROM emp WHERE sal>2975.00;

# 第三步 合并以上两步

SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='JONES');

# 2、查询与SCOTT同一个部门的员工。
# 以前的查询操作：查询部门编号为20的员工记录
SELECT * FROM emp WHERE deptno = 20;

# 第一步：查询SCOTT员工所在部门编号

SELECT deptno FROM emp WHERE ename = 'SCOTT'; -- 20

# 第二步：查询与SCOTT同一个部门的员工--》查询部门编号为20的员工

SELECT * FROM emp WHERE deptno = 20;

# 合并上面两个步骤

SELECT * FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'SCOTT');

#3.工资高于30号部门所有人的员工信息
# 以前的写法：查询工资高于3000的所有员工信息（假设30号部门最高薪资为3000）
SELECT * FROM emp WHERE sal>3000;

# 第一种方法

# 第一步：获取30号部门的最高薪资

SELECT MAX(sal) FROM emp GROUP BY deptno HAVING deptno=30; -- 2850

# 第二步：工资高于30号部门所有人的员工信息---》工资高于2850的所有员工信息

SELECT * FROM emp WHERE sal>2850;

# 合并上面两个步骤

SELECT * FROM emp WHERE sal>(SELECT MAX(sal) FROM emp GROUP BY deptno HAVING deptno=30);

# 第二种写法：上面的30号部门最高工资查询时不使用分组

# 第二种方法

SELECT MAX(sal) FROM emp WHERE deptno = 30;

SELECT * FROM emp WHERE sal>(SELECT MAX(sal) FROM emp WHERE deptno = 30);

# 第三种写法

# 第三种方法

# 第一步：查询出30号部门所有人的薪资

SELECT sal FROM emp WHERE deptno=30; -- 1600 1250 2850 1500 950

# 第二步：工资高于30号部门所有人的员工信息

SELECT * FROM emp WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=30);

# 4、查询工作和工资与MARTIN（马丁）完全相同的员工信息

# 第一种方法

# 第一步：查询马丁的工作

SELECT job FROM emp WHERE ename='MARTIN'; -- SALESMAN

# 第二步：查询马丁的工资

SELECT sal FROM emp WHERE ename = 'MARTIN'; -- 1250.00

#查询工作和工资与MARTIN（马丁）完全相同的员工信息 -->查询工作为SALESMAN，薪水为1250.00元的员工信息

SELECT * FROM emp WHERE job='SALESMAN' AND sal = 1250.00;

#合并上面两个步骤
SELECT * FROM emp 
WHERE job=(SELECT job FROM emp WHERE ename='MARTIN') 
AND sal = (SELECT sal FROM emp WHERE ename = 'MARTIN');

# 第二种写法

# 第二种方法

# 第一步：先查询出马丁的工作和工资

SELECT job,sal FROM emp WHERE ename = 'MARTIN'; -- SALESMAN 1250.00

# 第二步：查询工作和工资与MARTIN（马丁）完全相同的员工信息

SELECT * FROM emp WHERE (job,sal) IN(SELECT job,sal FROM emp WHERE ename = 'MARTIN');

#有2个以上直接下属的领导（员工，因为领导也是一个员工）信息-- 查询mrg列的数据出现过2次以上

# 第三种方法

# 第一步:先把有两个以上直接下属的领导的编号（员工编号）查询出来

SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(mgr)>2; -- 7698 7839

# 第二步：有2个以上直接下属的领导（员工，因为领导也是一个员工）信息

SELECT * FROM emp WHERE empno IN(SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(mgr)>2);

# 6、查询员工编号为7788的员工名称、员工工资、部门名称、部门地址

# 未用子查询

SELECT e.ename,e.sal,d.dname,d.loc FROM emp e,dept d WHERE e.deptno=d.deptno AND empno=7788;

# 运用子查询
SELECT e.ename, e.sal, d.dname, d.loc 
FROM emp e, (SELECT dname,loc,deptno FROM dept) d 
WHERE e.deptno=d.deptno AND e.empno=7788