Prayer

*******************************************************************************
子查询
*******************************************************************************

-----------------------------------------------------------------------------------------------------
子查询是一个在select查询中含有其他的select语句，子查询通常用在where子句中，即将一个查询结果做为查询的条件.
-----------------------------------------------------------------------------------------------------
统计表employees中所有工资小于平均工资的人数
select count(*)工资 from employees
where salary <
(select avg(salary) from employees)
图:
----------------
工资
56
49
36

Select MIN(Salary),Manager_id
from Employee
group by Manager_id
having MIN(Salary) >
(Select MIN(Salary)
From Employee
where Manager_id=004);

注意在多表查询中返回的值应该是一个唯一确定的值，而不应该是多中值。
select Employee_id,Last_name,Salary,Manager_id
from Employee
WHERE Salary>
(select Salary
from Employee
where Manager_id=003)
and
Last_name= (select Last_name
from Employee
where Last_name like 'mary');

注意在any，or，all的区别：
Select Salary,Manager_id,Last_name,Employee_id
from Employee
where Salary < any
(Select Salary
From Employee
where Manager_id=005);

Select Salary
From Employee
where Manager_id=005；返回的是2000，2500
就是在工资中小于2000.2500其中一个就可以了，而ALL则必须是小于2000和2500其中任何一个
。而or则是和and用在一起的.