sql子查询

子查询

一个select语句中包含另一个完整的select语句。

子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。

l  子查询出现的位置：

Ø  where后，作为条为被查询的一条件的一部分；

Ø  from后，作表；

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

Ø  any

Ø  all

l  子查询结果集的形式：

Ø  单行单列（用于条件）

Ø  单行多列（用于条件）

Ø  多行单列（用于条件）

Ø  多行多列（用于表）

练习：

1.      工资高于JONES的员工。

分析：

查询条件：工资>JONES工资，其中JONES工资需要一条子查询。

 

第一步：查询JONES的工资

SELECT sal FROM emp WHERE ename='JONES'

 

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

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

 

结果：

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

 

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

 

l  子查询作为条件

l  子查询形式为单行单列

 

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

分析：

SELECT * FROMemp 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 [q1] (SELECT sal FROM emp  WHERE deptno=30)

 

l  子查询作为条件

l  子查询形式为多行单列（当子查询结果集形式为多行单列时可以使用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个以上直接下属的员工信息

 

SELECT * FROMemp WHERE empno IN(

SELECT mgr FROMemp GROUP BY mgr HAVING COUNT(mgr)>=2);

 

l  子查询作为条件

l  子查询形式为单行多列

 

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

 

l  子查询作为表

l  子查询形式为多行多列

---

大于所有