08.多表查询下篇

四、子查询

一个select语句中包含另一个完整的select语句。
子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。
子查询出现的位置：

• where后，作为条为被查询的一条件的一部分；
• from后，作表；

当子查询出现在where后作为条件时，还可以使用如下关键字：

• any
• all

子查询结果集的形式：

• 单行单列（用于条件）
• 单行多列（用于条件）
• 多行单列（用于条件）
• 多行多列（用于表）

练习：
1.工资高于JONES的员工。
分析：
查询条件：工资>JONES工资，其中JONES工资需要一条子查询。

第一步：查询JONES的工资

SELECT sal FROM emp WHERE ename='JONES'

第二步：查询高于JONES工资的员工

SELECT * FROM emp WHERE sal > (${第一步})

结果：

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

2、查询与SCOTT同一个部门的员工。

第一步：查询scott的部门

SELECT deptno FROM emp WHERE ename="scott";

第二步：查询与SCOTT同一个部门的员工

SELECT * FROM emp WHERE deptno=(${第一步});

结果：

SELECT * FROM emp WHERE deptno=(SELECT deptno FROM emp WHERE ename="scott");

- 子查询作为条件
- 子查询形式为单行单列

3、工资高于30号部门所有人的员工信息
分析：

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

查询条件：工资高于30部门所有人工资，其中30部门所有人工资是子查询。高于所有需要使用all关键字。

第一步：查询30部门所有人工资

SELECT sal FROM emp WHERE deptno=30;

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

SELECT * FROM emp WHERE sal > ALL (${第一步})

结果：

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

- 子查询作为条件
- 子查询形式为多行单列（当子查询结果集形式为多行单列时可以使用ALL或ANY关键字）

4、查询工作和工资与MARTIN（马丁）完全相同的员工信息
分析：
查询条件：工作和工资与MARTIN完全相同，这是子查询

第一步：查询出MARTIN的工作和工资

SELECT job,sal FROM emp WHERE ename='MARTIN'

第二步：查询出与MARTIN工作和工资相同的人

SELECT * FROM emp WHERE (job,sal) IN (${第一步})

结果：

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

5、有2个以上直接下属的员工信息

第一步：查询出有2个以上直接下属的员工编号

SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(mgr)>=2

第二步：根据员工编号查询出其他信息

SELECT * FROM emp WHERE empno IN (${第一步});

结果：

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

- 子查询作为条件
- 子查询形式为单行多列

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

分析：（无需子查询）
查询列：员工名称、员工工资、部门名称、部门地址
查询表：emp和dept，分析得出，不需要外连接（外连接的特性：某一行（或某些行）记录上会出现一半有值，一半为NULL值）
条件：员工编号为7788

第一步：去除多表，只查一张表，这里去除部门表，只查员工表

SELECT ename, sal FROM emp e WHERE empno=7788

第二步：让第一步与dept做内连接查询，添加主外键条件去除无用笛卡尔积

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

第二步中的dept表表示所有行所有列的一张完整的表，这里可以把dept替换成所有行，但只有dname和loc列的表，这需要子查询。

第三步：查询dept表中dname和loc两列，因为deptno会被作为条件，用来去除无用笛卡尔积，所以需要查询它。

SELECT dname,loc,deptno FROM dept;

第四步：替换第二步中的dept

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

• 子查询作为表
• 子查询形式为多行多列
  

6、自连接:自己连接自己，起别名，把自己看成多张表
求7369员工编号、姓名、经理编号和经理姓名

SELECT e1.empno , e1.ename,e2.empno AS mgrno,e2.ename FROM emp e1, emp e2 WHERE e1.mgr = e2.empno AND e1.empno = 7369;

练习：
求各个部门薪水最高的员工所有信息

    select e.* from emp e,(select max(sal) maxsal,deptno from empgroup by deptno) awhere e.deptno = a.deptnoand e.sal =a.maxsal