8.mysql 基础之子查询

1、子查询：查询语句中嵌套了查询语句，也称为嵌套查询

2、按查询结果分类：单行子查询和多行子查询

3、按关联性分类：关联子查询、非关联子查询

4、单行子查询操作符：=、>、 < 、>=、<=、<>

5、多行子查询操作符：in 、any（和单行操作符一块使用，表示任一个）、all（和单行操作符一块使用，表示所有）

6、exist 和not exist 使用关联子查询 一般使用in的查询，也可以使用该关键字

注： 写子查询方法 ：

1）先写外查询（语句简单）

2）先写内查询（语句复杂）

题目：

1.查询和zlothkey相同部门的员工姓名和工资

select last_name,salary from emploees

where department_id in (select department_id from emploees where last_name='zlothkey')

2.查询工资比公司平均工资高的员工的员工号，姓名和工资

select emploee_id,last_name,salary

from emploees

where salary >(select avg(salary) from emploees )

3.选择工资大于所有job_id='SA_MAN'的员工的工资的员工的last_name,job_id,salary

select last_name,job_id,salary

from emploees

where salary >all

(select salary from emploees where job_id='SA_MAN')

4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名

select  emploee_id,last_name 

from emploees

where department_id in (select distinct department_id from emploees  where last_name like '%u%')
5.查询在部门的location_id为1700的部门工作的员工的员工号

select  emploee_id,last_name 

from emploees

where department_id in (select department_id from departments where location_id=1700)
6.查询管理者是king的员工姓名和工资

select  last_name,salary 

from emploees

where manager_id in (select emploee_id from emploees where last_name='king')

7.查询工资最低的员工信息姓名和工资

select  last_name,salary 

from emploees

where salary = (select min(salary) from emploees)

8.查询平均工资最低的部门信息

方式一：

select department_id  

from emploees

group by department_id 

having  avg(salary)=(select min(avg_salary)
                                 from(
                                           select avg(salary)   avg_salary
                                           from emploees
                                          group by department_id) avg_department_id

                                           )

方式二：

select department_id  

from emploees

group by department_id 

having  avg(salary)<= all(
                                           select avg(salary) 
                                           from emploees
                                          group by department_id

                                           )

方式三：

select department_id  

from emploees

group by department_id 

having  avg(salary)= (
                                           select avg(salary)   avg_salary
                                           from emploees
                                          group by department_id
                                          order by avg_salary asc
                                           limit 0,1

                                           )

方式四：(临时表多表查询时，必须起个别名，且要用括号括起来)

select  e.department_id from
emploees e ,( select avg(salary)   avg_salary
                                           from emploees
                                          group by department_id) avg_department_id
                                          order by avg_salary asc
                                           limit 0,1) t_depart_avg 
where e.department_id =t_depart_avg.department_id

9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）

select d.*, (select avg(salary) from emploees where department_id=d.department_id）avg_sal
from deparments d,(
 select avg(salary)   avg_salary
                                           from emploees
                                          group by department_id) avg_department_id
                                          order by avg_salary asc
                                           limit 0,1) t_depart_avg 
where d.department_id =t_depart_avg.department_id

10.查询公司中所有的manger信息

方式一：自联接

select mgr.emploees,mgr.last_name,mgr.department_id from emploees e join

emploeesmgr

on e.manager_id=mgr.emploees_id

方式二：子查询

select emploees_id, last_name,department_id from emploees 

where emploee_id in (select distinct manger_id from emploees )

方式三：使用exists

select emploees_id, last_name,department_id from emploees e1

where exists (select * from emploees e2 where

e1.emploees_id =e2.manger_id)