SQL学习自我总结（六）

子查询：一个查询被嵌套在另一个查询中，就是子查询
使用子查询相当于执行两个连续查询并且用第一个查询的结果作为第二个查询的搜索值

可以将子查询放在许多的SQL字句中，包括：
where字句、having字句、from字句，create view语句中、create table语句、update语句、insert语句的into字句中和update语句的set字句中

范例：
select last_name,e.employee_id
from employees e
where salary > (select salary from employees where last_name='Abel');
使用子查询的原则：
一个子查询必须放在括号里
将子查询放在比较条件的右边
在子查询中可以使用两种比较条件：单行运算符和多行运算符
oracle服务器没有强制限制子查询的数目，限制子查询的数量只与查询所需要的缓冲区的大小有关。

子查询的分类
子查询可分为：
单行子查询：即子查询只返回一行
多行子查询：即子查询将返回多行
多列子查询：即子查询返回的不止一列(多列子查询的返回行数可以是单行也可以是多行)

单行子查询
单行子查询中使用having子句
单行子查询仅返回一行数据，需要使用单行比较符来进行比较。单行比较符：
=
>
>=
<
<=
<>
!=
^=

范例：
select last_name,job_id
from employees
where job_id = (select job_id from employees where employee_id = 141);

select last_name,job_id,salary
from employees
where job_id = (select job_id from employees where employee_id = 141)
and salary > (select salary from employees where employee_id = 143);

select last_name,job_id,salary
from employees
where salary = (select min(salary) from employees);

select department_id,min(salary)
from employees
group by department_id
having min(salary) > (select min(salary) from employees where department_id = 50);
上面的SQL语句显示所有其最低薪水小于部门50的最低薪水的部门号和最低薪水

select job_id,avg(salary)
from employees
group by job_id
having avg(salary) = (select min(avg(salary)) from employees group by job_id);
上面的SQL语句找出平均薪水为最低平均薪水的工作岗位。

多行子查询
多行子查询需要使用多行比较符：
in 等于列表中的任何成员
any 比较子查询返回的每个值
all 比较子查询返回的全部值

范例：
select last_name,salary,department_id
from employees
where salary in (select min(salary) from employees group by department_id);

在多行子查询中使用any、all运算符示例：
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_prog';

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_prog';

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_prog';

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_prog';