mysql字段连接查询（MySQL子查询用法）
mysql字段连接查询（MySQL子查询用法）子查询的分类注意事项：语法：SELECT -- 主查询 select_list FROM table WHERE expr operator > (SELECT -- 子查询 select_list FROM table); 复制代码子查询(内查询)在主查询之前执行完成。子查询的结果被主查询(外查询)使用。
1、子查询概述子查询简介
子查询指一个查询语句嵌套在另一个查询语句内部的查询，内部的查询是外部查询的条件，这个特性从MySQL4.1开始引入。
SQL中子查询的使用大大增强了SELECT查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。
子查询基本使用
语法：
SELECT -- 主查询
select_list
FROM
table
WHERE
expr operator > (SELECT -- 子查询
select_list
FROM
table);
复制代码
子查询(内查询)在主查询之前执行完成。
子查询的结果被主查询(外查询)使用。
注意事项：
- 子查询要包含在括号内
- 将子查询放在比较条件的右侧
- 单行操作符对应单行子查询，多行操作符对应多行子查询
子查询的分类
按子查询的结果返回一条还是多条记录，将子查询分为单行子查询和多行子查询。
按子查询是否被执行多次，将子查询划分为相关(或关联)子查询和不相关(或非关联)子查询。
2、子查询的使用2.1、单行子查询单行比较操作符
=等于，>大于，>=大于等于，<小于，<=小于等于，<>不等于
使用实例
-- 查找薪水大于所有员工平均薪水的员工
SELECT employee_id first_name last_name salary
FROM employees
WHERE salary > (
SELECT AVG(salary) FROM employees);
-- 查询工资大于149号员工工资的员工的信息
select employee_id last_name salary
from employees
where salary > (
select salary from employees where employee_id = 149)
-- 返回job_id与141号员工相同，salary比143号员工多的员工姓名，job _id和工资
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
);
-- 返回公司工资最少的员工的last_name job_id和salary
select last_name job_id salary
from employees
where salary = (
select min(salary) from employees
);
-- 查询与147号员工的manager_id和department_id相同的其他员工的employee_id，manager_id，department_id
select employee_id manager_id department_id
from employees
where manager_id = (
select manager_id from employees where employee_id = 147
)
and department_id = (
select department_id from employees where employee_id = 147
)
and employee_id <> 147;
-- 查询最低工资大于50号部门最低工资的部门id和其最低工资
select department_id min(salary) min_salary
from employees
where department_id is not null
group by department_id
having min_salary > (
select min(salary) from employees where department_id = 50
);
复制代码2.2、多行子查询
多行子查询比较操作符
使用实例
-- 查找属于位置ID为1700的所有员工
SELECT
employee_id first_name last_name
FROM
employees
WHERE
department_id IN (SELECT
department_id
FROM
departments
WHERE
location_id = 1700)
ORDER BY first_name last_name;
-- 返回其它job id中比job id为'IT_PROG’部门任一工资低的员工的员工号姓名、job id以及salary
select last_name job_id salary
from employees
where salary < any (
select salary from employees where job_id = 'IT_PROG'
)
and job_id <> 'IT_PROG';
-- 返回其它job id中比job id为'IT_PROG’部门所有工资低的员工的员工号姓名、job id以及salary
select last_name job_id salary
from employees
where salary < all (
select salary from employees where job_id = 'IT_PROG'
)
and job_id <> 'IT_PROG';
-- 查询平均工资最低的部门ID
-- 方式一：
select department_id avg(salary) from employees group by department_id order by avg(salary) limit 1;
-- 方式二：
select department_id
from employees
group by department_id
having avg(salary) = (
select min(avg_sal)
from (
select avg(salary) avg_sal from employees group by department_id
) t_dept_avg_sal
)
-- 方式三：
select department_id
from employees
group by department_id
having avg(salary) <= all (
select avg(salary) from employees group by department_id
);
-- 查询员工表中是领导的员工信息
select employee_id last_name manager_id
from employees
where employee_id in (
select manager_id from employees
);
-- 查询员工表中不是领导的员工信息
select employee_id last_name manager_id
from employees
where employee_id not in (
select manager_id from employees where manager_id is not null
);
复制代码2.3、相关子查询
如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为关联子查询。
相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。
使用实例
-- 查询员工中工资大于本部门平均工资的员工的last_name salary和其department_id
-- 方式一
select last_name salary department_id
from employees e1
where salary > (
select avg(salary) from employees e2 where e1.department_id = e2.department_id
);
-- 方式二
select last_name salary e.department_id
from employees e (select department_id avg(salary) avg_sal from employees group by department_id) t_dept_avg_sal
where e.department_id = t_dept_avg_sal.department_id
and e.salary > t_dept_avg_sal.avg_sal;
-- 查询员工的id salary 按照department_name排序
select employee_id salary
from employees e
order by (
select department_name from departments d where e.department_id = d.department_id
);
复制代码
exists与NOT EXISTS
EXISTS运算符用于指定子查询中是否存在满足条件的行，
如果子查询包含任何行，则EXISTS运算符返回true。 否则它返回false。
EXISTS运算符在找到行后立即终止查询处理，因此，可以利用EXISTS运算符的此功能来提高查询性能
NOT EXISTS 表示如果不存在某种条件，则返回true，否则返回true。
使用实例：
-- 查找管理者的id，姓名，工作和部门id
select employee_id last_name job_id department_id
from employees e1
where exists(
select * from employees e2 where e1.employee_id = e2.manager_id
)
-- 查询departments表中，不存在于employees表中的部门的department_id和department_name
-- 方式一
select department_id department_name
from departments d
where not exists(
select * from employees e where d.department_id = e.department_id
);
-- 方式二
select d.department_id department_name
from departments d left join employees e on d.department_id = e.department_id
where e.department_id is null;
复制代码子查询练习题
#1.查询和Zlotkey相同部门的员工姓名和工资
select last_name salary
from employees
where department_id in (
select department_id from employees where last_name = 'Zlotkey'
)
and last_name <> 'Zlotkey';
#2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
select employee_id last_name salary
from employees
where salary > (
select avg(salary) from employees
);
#3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name，job_id salary
select last_name job_id salary
from employees
where salary > (
select max(salary) from employees where job_id = 'SA_MAN'
);
#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
select employee_id last_name
from employees
where department_id in (
select department_id from employees where last_name like '%u%'
);
#5.查询在部门的location_id为1700的部门工作的员工的员工号
select employee_id
from employees e
where department_id in (
select department_id from departments where location_id = 1700
);
#6.查询管理者是King的员工姓名和工资
select last_name salary
from employees
where manager_id in (
select employee_id from employees where last_name = 'K_ing'
);
#7.查询工资最低的员工信息: last_name salary
select last_name salary
from employees
order by salary
limit 1;
select last_name salary
from employees
where salary = (
select min(salary) from employees
);
#8.查询平均工资最低的部门信息
-- 方式一：
select * from departments
where department_id = (
select department_id
from employees
group by department_id
order by avg(salary)
limit 1
);
-- 方式二：
select * from departments
where department_id = (
select department_id
from employees
group by department_id
having avg(salary) = (
select min(avg_sal) from (select avg(salary) avg_sal from employees group by department_id) t_sal
)
);
-- 方式三：
select * from departments
where department_id = (
select department_id
from employees
group by department_id
having avg(salary) <= all (select avg(salary) from employees group by department_id)
);
#9.查询平均工资最低的部门信息和该部门的平均工资 子查询可以写在查询字段里面
select d.* (select avg(salary) from employees where department_id = d.department_id) avg_sal
from departments d
where department_id = (
select department_id
from employees
group by department_id
having avg(salary) <= all (select avg(salary) from employees group by department_id)
);
#10.查询平均工资最高的job 信息
-- 方式一：
select * from jobs
where job_id = (
select job_id
from employees
group by job_id
having avg(salary) >= all (
select avg(salary) from employees group by job_id )
);
-- 方式二：
select * from jobs
where job_id = (
select job_id
from employees
group by job_id
order by avg(salary) desc
limit 1
);
#11.查询平均工资高于公司平均工资的部门有哪些?
select distinct department_id
from employees
where department_id is not null
group by department_id
having avg(salary) > (
select avg(salary) from employees
);
#12.查询出公司中所有manager 的详细信息
-- 方式一
select employee_id last_name salary department_id
from employees e1
where exists(
select * from employees e2 where e1.employee_id = e2.manager_id
);
-- 方式二
select distinct man.employee_id man.last_name man.salary man.department_id
from employees emp
join employees man
on emp.manager_id = man.employee_id ;
-- 方式三 子查询
select employee_id last_name salary department_id
from employees
where employee_id in (
select distinct manager_id from employees
);
#13.查各个部门最高的工资中 最低的那个部门的最低工资多少
-- 方式一：
select department_id min(salary) from employees
where department_id = (
select department_id from employees
group by department_id
having max(salary) = (
select min(max_sal)
from employees e ( select max(salary) max_sal
from employees
where department_id is not null
group by department_id ) t_min_sal
));
-- 方式二：
select department_id min(salary) from employees
where department_id = (
select department_id from employees
group by department_id
having max(salary) <= all (
select max(salary) from employees
group by department_id)
);
-- 方式三：
select min(salary) from employees
where department_id = (
select department_id from employees
group by department_id
having max(salary) = (
select max(salary) from employees
group by department_id
order by max(salary)
limit 1 )
);
-- 方式四：
select min(salary) from employees e ( select department_id max(salary) from employees
group by department_id
order by max(salary)
limit 1) t_max_sal
where e.department_id = t_max_sal.department_id;
#14.查询平均工资最高的部门的 manager的详细信息: last_name，department_id email salary
-- 方式一：
select last_name department_id email salary
from employees
where employee_id = (
select manager_id
from departments
where department_id = (
select department_id from employees
group by department_id
order by avg(salary) desc
limit 1)
);
-- 方式二：
select last_name department_id email salary
from employees
where employee_id = (
select manager_id from departments
where department_id = (
select department_id
from employees
group by department_id
having avg(salary) >= all (
select avg(salary)
from employees
where department_id is not null
group by department_id)
)
);
-- 方式三：
select last_name department_id email salary from employees
where employee_id = (
select manager_id from departments
where department_id = (
select department_id
from employees
group by department_id
having avg(salary) = (
select max(avg_sal) from (select avg(salary) avg_sal
from employees
where department_id is not null
group by department_id) t_avg_sql
))
);
#15．查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号
-- 方式一：
select department_id
from departments
where department_id not in (
select distinct department_id
from employees e1
where job_id = 'ST_CLERK'
);
-- 方式二：
select department_id
from departments d
where not exists (
select *
from employees e1
where d.department_id = e1.department_id and job_id = 'ST_CLERK'
);
#16．选择所有没有管理者的员工的last_name
select last_name
from employees e1
where not exists(
select * from employees e2 where e1.manager_id = e2.employee_id
);
#17．查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan '
-- 方式一：自连接
select e1.employee_id e1.last_name e1.hiredate e1.salary
from employees e1 employees e2
where e1.manager_id = e2.employee_id and e2.last_name = 'De Haan';
-- 方式二：子查询
select employee_id last_name hiredate salary
from employees
where manager_id in (
select employee_id from employees where last_name = 'De Haan'
);
#18.查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资（相关子查询)
-- 方式一：
select employee_id salary last_name
from employees e1
where salary > (
select avg(salary) from employees e2 where e1.department_id = e2.department_id
);
-- 方式二：
select employee_id salary last_name
from employees e (select department_id avg(salary) avg_sal from employees group by department_id) t_avg_sal
where e.department_id = t_avg_sal.department_id and e.salary > t_avg_sal.avg_sal;
#19.查询每个部门下的部门人数大于5的部门名称（相关子查询)I
select department_name
from departments d
where 5 < (
select count(*) from employees e where e.department_id = d.department_id
);
#20.查询每个国家下的部门个数大于2的国家编号(相关子查询)
select country_id
from locations l
where 2 < (
select count(*) from departments d where l.location_id = d.location_id
);
