MySQL进阶知识2——子查询

定义

子查询指在sql语句中的select语句，也叫嵌套查询。

单行子查询

返回一条记录的子查询语句。

例子：

显示tim同一部门的所有员工

select * from where 部门编号 = worker
(select 部门编号 from worker  where name = 'tim');

多行子查询

返回多条记录的子查询，使用关键字in。

例子：

1.查询岗位与和部门10拥有的岗位相同的雇员信息

select * from worker where job in 
(select job from worker where 部门编号 = 10)


2.查看各个类别中，价格最高的商品名称和价格

思路：
1）先把原来的表按照分类、价格排序；
2）然后把排序过的结果当作一张表，进行分组

select * from
(select name,category,price 
from goods 
order by category,price desc) as 商品表
group by 商品表.category;

使用all关键字

例子：

显示，工资比部门20的所有员工的工资高的员工信息

思路：
1）查询部门20所有员工的工资
2）查询工资大于该值的员工

select * from worker
where sal > 
all(select sal from worker where 部门编号 = 20);

使用any关键字

例子：

显示，工资比部门20的任意一个员工的工资高的员工信息

select * from worker
where sal > 
any(select sal from worker where 部门编号 = 20);    

---

from子句中使用子查询

子查询出现在from子句中。

技巧：
把子查询当做临时表使用。

例子一：

1.显示高于自己部门平均工资的员工信息

思路一：
1）先获取各部门平均工资
select avg(sal) from worker group by 部门;

2）然后上面查询出来的信息当做一张临时表
select * from worker,
(select avg(sal) as avg_sal from worker group by 部门) as 新表
where  worker.部门编号 = 新表.部门编号
and worker.sal > 新表.avg_sal;

思路二：
select e1.* from worker as e1
where e1.sal > 
(select avg(sal) as mysal from worker as e2  
where e2.部门编号 = e1.部门编号);

例子二：

2.部门最高工资的人

思路一：
1）获取部门最高工资
select max(sal),部门 from worker group by 部门;

2）搜索
select * from worker,
(select max(sal) as max_sal,部门from worker 
group by部门) as 新表
where worker.部门编号 = 新表.部门编号
and worker.sal = 新表.max_sal;

思路二：
select * from worker as e
where sal = 
(select max(sal) from worker  
where 部门编号 = e.部门编号);