34.子查询简介

目录

 

• 子查询简介
• 带From关键字的子查询
• 带IN关键词的子查询
• 带比较运算符的子查询
• with…as

简介

 

• 定义：子查询指一个查询语句嵌套在另一个查询语句内部，在SELECT子句中先计算子查询，子查询的结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。 这个特性从MySQL 4.1开始引入。

• 子查询作为过滤条件时需要用（） 包裹

子查询的常见分类

 

• From型子查询：将子查询的结果作为父查询的表来使用
• in/not in 型子查询：子查询的结果是单列多行，作为where的过滤条件
• where型子查询：查询结果作为过滤条件出现在比较运算符的一端

带From关键词的子查询

 

• 子查询是一张多行多列的表，将子查询作为父查询的表来嵌套查询
• 子查询语句必须用（）包裹且需要有别名
• 计算出各部门性别为男性的员工人数

select dept_name,count(emp_id) from 
(select dept_name, emp_id,ename,gender from 
dept inner join emp_part
 where id=dept_id and gender='男')b group by dept_name;

带IN关键词的子查询

 

• 将子查询作为where语句后的过滤条件，常用于子查询结果是单列多行的情况
• 子查询语句必须用（）包裹
• in/not in
• 查询出北京地区所有的员工信息

SELECT *
FROM emp_part
WHERE  dept_id IN (SELECT id FROM dept WHERE dept_location = '北京')

带比较运算符的子查询

 

• 将子查询的结果作为过滤条件，放在比较运算符的一端
• 常用于子查询结果为单个结果的情况
• 子查询语句必须用（）包裹

#查询出薪资大于公司平均薪资的员工id,姓名及薪资
SELECT emp_id,ename,salary FROM emp_part 
WHERE salary > (SELECT AVG(salary) FROM emp_part);

with as

 

• 如果一整句查询语句中，某个子查询的结果会被多个父查询引用，通常建议将共用的子查询用简写表示出来

• 语法： with [表名] as （select…)

# 查询出部门平均薪资大于公司平均薪资的部门名称，部门主管，所在地及部门平均薪资

# 不使用 with ...as
select dept_id,dept_name,dept_manager,dept_location,avg_salary from dept inner join 
(select dept_id,avg(salary) avg_salary from emp_part group by dept_id)b on id=dept_id 
and avg_salary > (select avg(avg_salary) from 
(select dept_id,avg(salary) avg_salary from emp_part group by dept_id)b);

# 使用 with ...as
with dept_avg as
(select dept_id,avg(salary) avg_salary from emp_part group by dept_id)select
dept_id,dept_name,dept_manager,dept_location,avg_salary from dept inner join dept_avg 
on id=dept_id 
and avg_salary > (select avg(avg_salary) from dept_avg);