子查询
什么是子查询?
出现在其它语句内部的select语句称之为子查询语句或内部查询
内部嵌套其它select语句的查询,称之为外查询或者主查询
注意事项
子查询要包含在括号内
将子查询放在比较条件的右侧
单行操作符对应单行子查询,多行操作符对应多行子查询
分类:
按子查询出现的位置:
select后面:
仅仅支持标量子查询
from后面:
支持表子查询
where或having后面:★
标量子查询(单行) √
列子查询 (多行) √
行子查询
exists后面(相关子查询)
表子查询
按结果集的行列数不同:
标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
行子查询(结果集有一行多列)
表子查询(结果集一般为多行多列)
一、where或having后面
1、标量子查询(单行子查询)
2、列子查询(多行子查询)
3、行子查询(多列多行)
特点
1 子查询放在小括号内()
2 子查询一般放在条件的右侧
3:标量子查询,一般搭配着单行操作符使用 >, <, >=, <=,=, <>大于,小于,大于等于,小于等于,等于,不等于
4: 列子查询一般搭配着多行操作符使用 in,and ,all
5: 子查询的执行优先于主查询执行,主查询的条件用到了子查询的结果
标量子查询(单行子查询)
#案例1:谁的工资比 Abel 高?
#①查询Abel的工资
SELECT salary
FROM employees
WHERE last_name = 'Abel'
#②查询员工的信息,满足 salary>①结果
SELECT *
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);
#案例2:返回job_id与141号员工相同,salary比143号员工多的员工 姓名,job_id 和工资
#①查询141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141
#②查询143号员工的salary
SELECT salary
FROM employees
WHERE employee_id = 143
#③查询员工的姓名,job_id 和工资,要求job_id=①并且salary>②
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
);
#案例3:返回公司工资最少的员工的last_name,job_id和salary
#①查询公司的 最低工资
SELECT MIN(salary)
FROM employees
#②查询last_name,job_id和salary,要求salary=①
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees
);
#案例4:查询最低工资大于50号部门最低工资的部门id和其最低工资
#①查询50号部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
#②查询每个部门的最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
#③ 在②基础上筛选,满足min(salary)>①
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
);
#非法使用标量子查询
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT salary
FROM employees
WHERE department_id = 250
);
列子查询(多行子查询)★
返回多行
使用多行比较操作符
**多行比较操作符 **
any 只能用在外部查询不能使用在子查询内部 any是内部任意的值都满足 相当于 min() 如果是 a >any(1,2,3) 就可以写成 a > min(1)
all相当于满足内部的最大的值, 可以理解为:max() 如果a >all (1,2,3) 可以写成 a > max(3)
#案例1:返回location_id是1400或1700的部门中的所有员工姓名
#①查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
#②查询员工姓名,要求部门号是①列表中的某一个
SELECT last_name
FROM employees
WHERE department_id <>ALL(
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
);
#案例2:返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary
案例分析: 因为只要查询其它的部门中的员工薪资要低于 任意一个为”IT_PROG“的员工的薪资要用到any多行查询
#①查询job_id为‘IT_PROG’部门任一工资
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
#②查询员工号、姓名、job_id 以及salary,salary<(①)的任意一个
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ANY(
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
#或
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
SELECT MAX(salary)
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
#案例3:返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工 的员工号、姓名、job_id 以及salary
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ALL(
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
#或
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
SELECT MIN( salary)
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
select后面
案例:查询每个部门的员工个数
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id = d.`department_id`
) 个数
FROM departments d;
#案例2:查询员工号=102的部门名
SELECT (
SELECT department_name,e.department_id
FROM departments d
INNER JOIN employees e
ON d.department_id=e.department_id
WHERE e.employee_id=102
) 部门名;
from后面
#案例:查询每个部门的平均工资的工资等级
#①查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
SELECT * FROM job_grades;
#②连接①的结果集和job_grades表,筛选条件平均工资 between lowest_sal and highest_sal
SELECT ag_dep.*,g.`grade_level`
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal; 内部的子查询使用了函数之后 要起别名不然外部无法调用
使用连接查询查询上述的
select AVG(salary),job_grades.grade_level from
employees e inner join job_grades
on
e.salary between job_grades.lowest_sal
and job_grades.highest_sal
group by e.department_id
exists后面(相关子查询)
语法:
exists(完整的查询语句)
结果:
1或0
exists后面(相关子查询)
#案例1:查询有员工的部门名
-- 连接查询
SELECT
d.department_name
FROM
departments d
JOIN employees e ON d.department_id = e.department_id
WHERE
e.department_id IS NOT NULL
GROUP BY
d.department_id;
-- in
SELECT
d.department_name
FROM
departments d
WHERE
d.department_id IN ( SELECT department_id FROM employees )
-- exists
SELECT
d.department_name
FROM
departments d
WHERE
EXISTS ( SELECT department_id FROM employees e WHERE d.department_id = e.department_id )
#案例2:查询没有女朋友的男神信息
#in
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN(
SELECT boyfriend_id
FROM beauty
)
#exists
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
SELECT boyfriend_id
FROM beauty b
WHERE bo.`id`=b.`boyfriend_id`
);
Practices
#1. 查询和Zlotkey相同部门的员工姓名和工资
#①查询Zlotkey的部门
SELECT department_id
FROM employees
WHERE last_name = 'Zlotkey'
#②查询部门号=①的姓名和工资
SELECT last_name,salary
FROM employees
WHERE department_id = (
SELECT department_id
FROM employees
WHERE last_name = 'Zlotkey'
)
#2.查询工资比公司平均工资高的员工的员工号,姓名和工资。
#①查询平均工资
SELECT AVG(salary)
FROM employees
#②查询工资>①的员工号,姓名和工资。
SELECT last_name,employee_id,salary
FROM employees
WHERE salary>(
SELECT AVG(salary)
FROM employees
);
#3.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
-- 解析:每个部门都有一个平均薪资 所以要先求出每个部门的平均薪资然后放做一个表 然后和emplyees表关联
#①查询各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#②连接①结果集和employees表,进行筛选
SELECT employee_id,last_name,salary,e.department_id
FROM employees e
INNER JOIN (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) ag_dep
ON e.department_id = ag_dep.department_id
WHERE salary>ag_dep.ag ;
#4. 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
#①查询姓名中包含字母u的员工的部门
SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%'
#②查询部门号=①中的任意一个的员工号和姓名
SELECT last_name,employee_id
FROM employees
WHERE department_id IN(
SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%'
);
-- 连表查询
select e.employee_id , e.last_name from employees e join (select department_id from employees where last_name like "%u%" group by department_id) m on e.department_id = m.department_id
-- 因为每个部门会有多个团员工所以查询的员工所在的部门要分组
#5. 查询在部门的location_id为1700的部门工作的员工的员工号
#①查询location_id为1700的部门
SELECT DISTINCT department_id
FROM departments
WHERE location_id = 1700
#②查询部门号=①中的任意一个的员工号
SELECT employee_id
FROM employees
WHERE department_id =ANY(
SELECT DISTINCT department_id
FROM departments
WHERE location_id = 1700
);
-- 连接查询
select employee_id,last_name from employees e join (select department_id from departments where location_id =1700 ) d on e.department_id = d.department_id
#6.查询管理者是King的员工姓名和工资
#①查询姓名为king的员工编号
SELECT employee_id
FROM employees
WHERE last_name = 'K_ing'
#②查询哪个员工的manager_id = ①
SELECT last_name,salary
FROM employees
WHERE manager_id IN(
SELECT employee_id
FROM employees
WHERE last_name = 'K_ing'
);
-- 使用连接查询
-- 连接查询
select e.last_name, e.salary from employees e join employees m
on e.manager_id = m.employee_id where m.last_name= 'K_ing'
#7.查询工资最高的员工的姓名,要求first_name和last_name显示为一列,列名为 姓.名
#①查询最高工资
SELECT MAX(salary)
FROM employees
#②查询工资=①的姓.名
SELECT CONCAT(first_name,last_name) "姓.名"
FROM employees
WHERE salary=(
SELECT MAX(salary)
FROM employees
);