1.子查询知识体系，单行子查询，多行子查询



1查询工资比scott高的员工信息

A第一步：查询出scott这个员工的工资

select sal from emp

where ename = 'SCOTT';

B第二步：查询出工资比scott高的员工信息           

select * fromemp

where sal >3000;

总结：

子查询的本质：多个select语句的嵌套

2：子查询的知识体系搭建

A合理的书写风格

B子查询外面（）不要忘记

C子查询和主查询可以查询的是同一张表，也可以不是同一张表

     只要子查询返回的结果，主查询可以用即可。

D在什么地方可以防止子查询

select a,b,c                    ---OK，只能存放单行子查询，不能使多行子查询

from tab1                 ---OK 可以有子查询

where col in(em1,em2)      ---可以有子查询

       col between a1 and a2

       col > 222

       col > ()

group by …                ---不可以有子查询

having ….                 ---可以有子查询

order by …                ---不可以有子查询

E子查询的分类

1.单行操作符对应单行子查询，多行操作符对应多行子查询。

2.按照子查询返回的条目数，分为：单行子查询和多行子查询

3.单行子查询只能使用单行比较操作符（= >   >=  <  <=  <>）

4.多行子查询只能使用多行比较操作符（int any all）

3单行子查询

Eg:查询员工信息和141号工种一样的，薪水比143号员工工资高的员工

SQL> conn hr/123456

已连接。

SQL> select last_name,job_id,salary

 2  FROM employees

 3  WHERE job_id =

 4                (SELECT job_id

 5                 FROM employees

 6                 WHERE employee_id = 141);

               

 

Eg:查询工资最低的员工信息

select last_name,job_id,salary

FROM employees

WHERE salary =

             (SELECT MIN(salary)

              FROM employees);

 

Eg:求各个部门编号和部门的最低工资（这个最低工资要比50号部门的最低工资要高）

思路分析：看子查询看group by和 having条件检索看检索对象

//子查询：求50号部门的最低工资

à检索各部门的最低工资 比50号部门的最低工资大的部门号和部门最小工资

SELECT department_id,MIN(salary)

FROM employees

GROUP By department_id

HAVING MIN(salary) >

                  (SELECT MIN(salary)

                   FROM employees

                   WHERE department_id = 50);

4 查询部门名称是SALES的员工信息（2中方式）

方法1：子查询

SELECT *

FROM emp

WHERE DEPTNO = (SELECT deptno

               FROM dept

               WHERE dname = 'SALES');

方法2：多表查询

SELECT e.*

FROM emp e,dept d

WHERE e.deptno = d.deptno and d.dname = 'SALES';

注意：第二种（多表查询的执行速度比子查询的执行速度快，因为多表查询是一次将数据读到内存中进行读取，消耗内存而加快速度）

     第一种（子查询要进行两次连接数据库的操作，连接数据库的操作是一个耗时操作，减低了速度）。

5 select后面要查询的列中可以是单行子查询，不可以是多行子查询

错误案例：

select ename,empno,(select deptno from emp) AA from emp;

正确案例：

select ename,empno,(select deptno from emp where EMPNO = 7369) AA from emp;

6查询员工的姓名和薪水

select *

from (select ename,sal

    from emp);

7多行子查询

多行子查询只能使用多行比较操作符（in any all）

    --eg查询部门名称是*(不是)SALES和 ACCOUNTING的员工信息 2种方法

         --eg 查询薪水比30号部门任意一个员工薪高的员工信息

         -eg 查询薪水比30号部门所有员工高的员工信息

查询部门名称是*(不是)SALES和 ACCOUNTING的员工信息 2种方法

select *

from emp

where deptno in

             (select deptno

              from dept

              where dname = 'SALES' or dname = 'ACCOUNTING');

 

操作符

含义

IN

等于列表中的任何一个

ANY

和子查询返回的任意一个值比较

ALL

和子查询返回的所有值比较

ANY

SELECT employee_id,last_name,job_id,salary

 FROM employees

 WHERE salary < ANY

                 (SELECT salary

                  FROM employees

                  WHERE job_id = 'IT_PROG')

 AND job_id <> 'IT_PROC';

ALL

SELECT employee_id,last_name,job_id,salary

FROM employees

WHERE salary < ALL

                (SELECT salary

                 FROM employees

                 WHERE job_id = 'IT_PROG')

AND job_id <> 'IT_PROC'

 

Eg:比30号部门任意一个员工薪高的员工信息 大于集合中的最小值 any

select *

from emp

where sal > all(select sal

               from emp

               where deptno = 30);

等价于：

select *

from emp

where sal > (select max(sal)

           from emp

           where deptno = 30);

 

查询是经理的员工信息

分析：

SELECT *

FROM emp

WHERE empno in(经理的集合);

实际：

select *

from emp

where empno in

            (

             select mgr from emp

            );

select *

from emp

where empno not in

(

select mgr

from emp

where mgr is not null

);