mysql之子查询

mysql之子查询

1、#WHERE或HAVING后

1 标量子查询 单行子查询（一行一列）
2 列子查询 多行子查询 （一行多列）
3 行子查询 （多列多行）

单行和多行的特点：
1 子查询放在小括号内
2 子查询一般放在条件的右侧
3 标量子查询，一般搭配着单行操作符使用

< >= <= <>

列子查询的特点，一般搭配着多行操作符使用:in、any/some、all

子查询的执行顺序优先于主查询，因为查询的条件用到子查询的结果

（1）#1 标量子查询 单行子查询

#案例1：谁的工资比abel高？
#第1步 查询Abel的工资

SELECT salary
FROM employees
WHERE last_name=‘Abel’
#第2步 查询员工的信息满足salary>1结果的结果

SELECT *
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name=‘Abel’
);#把第1步称为单行子查询，或标量子查询

#案例2：返回job_id与141号员工相同，salary比143号员工多的员工 员工姓名 job_id和工资

#第1步查询141员工的job id

SELECT job_id
FROM employees
WHERE employee_id=141;
#第2步查询143员工的salary

SELECT salary
FROM employees
WHERE employee_id=143;

#第3步查询员工的姓名、job id、工资,要求jobid=1并且salary>2
SELECT last_name,job_id,salary
FROM employees
WHERE job_id=(
SELECT job_id
FROM employees
WHERE employee_id=141
)
AND(
SELECT salary
FROM employees
WHERE employee_id=143
)

#在子查询中使用分组函数

#案例：返回公司工资最少的员工的last_name、job_id、salary
#第1步查询公司的 最低工资

SELECT MIN(salary)
FROM employees;
#第2步查询last_name、job_id、salary,要求salary=1

SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees
);

#子查询中的having子句

#案例：查询最低工资大于50部门的最低工资，的部门id和最低工资
#第1步查询50部门的最低工资

SELECT MIN(salary)
FROM employees
WHERE department_id=50;
#第2步查询每个部门的最低工资

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id;
#第3步 在2基础上筛选，满足MIN(salary)》1的部门和最低工资

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=50
);

/
单行操作符（操作单个值的）只能搭配，单行子查询（标量子查询）
/

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT salary
FROM employees
WHERE department_id=250
);
/*
子查询的结果不是一行一列

---

#2多行子查询（列子查询）（一列多行）

#案例：返回 location_id是1400或1700的部门中的 所有员工姓名
#1.查询location_id是1400或1700的部门编号

SELECT DISTINCT department_id #防止重复提高效率
FROM departments
WHERE location_id=1400 OR location_id=1700
//WHERE location_id=1400 || location_id=1700
//WHERE location_id IN(1400,1700);

#2.查询员工姓名，要求部门号是1列表中的 某一个

SELECT last_name
FROM employees
WHERE department_id IN(
SELECT DISTINCT department_id #防止重复提高效率
FROM departments
WHERE location_id IN(1400,1700)
);

#还可以

SELECT last_name
FROM employees
WHERE department_id=ANY(#等于里面任意一个
SELECT DISTINCT department_id #防止重复提高效率
FROM departments
WHERE location_id IN(1400,1700)
);

SELECT last_name
FROM employees
WHERE department_id NOT IN(
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)
);

SELECT last_name
FROM employees
WHERE department_id !=ANY(#相当于或者
SELECT DISTINCT department_id #防止重复提高效率
FROM departments
WHERE location_id IN(1400,1700)
);

#案例：返回其他部门中比job_id为 it_prog 部门仁义工资低的员工的：工号姓名、job_id、salary

#1.查询job_id为 it_prog 任意一个工资

SELECT DISTINCT salary
FROM employees
WHERE job_id=‘IT_PROG’;
#2.查询员工号、姓名、job_id、salary salary<1任意一个

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 DISTINCT MAX(salary)
FROM employees
WHERE job_id=‘IT_PROG’
)
AND job_id != ‘IT_PROG’;

#案例：返回其他部门中比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 DISTINCT MIN(salary)
FROM employees
WHERE job_id=‘IT_PROG’
)
AND job_id != ‘IT_PROG’;

---

#3.行子查询（结果集一行多列，多行多列）

#案例：查询出员工编号最小 并且 工资最高的员工信息

#1：查询最小的员工编号
SELECT MIN(employee_id)
FROM employees;

#2:查询最低工资
SELECT MAX(salary)
FROM employees;

#3：查询员工信息
SELECT *
FROM employees
WHERE employee_id=(
SELECT MIN(employee_id)
FROM employees
)
AND salary=(
SELECT MAX(salary)
FROM employees
);

用行子查询代替

SELECT *
FROM employees
WHERE (employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM employees
);
/
用的不多，因为有局限性
要求连接条件的操作符必须一样
/

---

二、SELECT后

#案例:查询每个部门的员工个数

SELECT d.,(
SELECT COUNT()
FROM employees e
WHERE e.department_id=d.department_id
) AS 个数
FROM departments d;

#案例：查询员工工号=102的部门名

SELECT (
SELECT department_name
FROM departments d
INNER JOIN employees e
ON d.department_id =e.department_id
WHERE e.employee_id=102
) 部门名;
/
仅仅支持标量子查询
/

---

三、from后面：

放在from后面一般的就是表，相当于把子查询的结果集充当一个表格来使用，充当数据源
要求：必须要起别名，否则找不到
表子查询

#案例：查询每个部门的平均工资的工资等级
#1 查询每个部门的平均工资

SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;

#2 连接1的结果集和工资等级表 筛选条件between

SELECT ag_dep.*,g.grade_level
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) AS ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

---

四、EXISTS后（相关子查询）
1代表true
2代表false

语法
EXISTS(完整的查询语句)
结果：
1或0

SELECT EXISTS(
SELECT employee_id
FROM employees
WHERE salary =300000
);

#案例：查询有员工名的部门名

SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.department_id=e.department_id
);

和其他子查询不一样，前面的子查询先执行，因为主查询用到子查询的结果
而这个先去执行主查询，根据主查询的结果，再去过滤。这就是相关子查询
子查询涉及到了主查询的字段

#使用in的方式代替：能用 EXISTS就一定能用in代替

SELECT department_name
FROM departments d
WHERE d.department_id IN(
SELECT department_id
FROM employees
);

#案例2：查询没有女朋友的男神信息

UPDATE beauty SET boyfriend_id = NULL WHERE id=10;

#IN 的方式
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN(
SELECT boyfriend_id
FROM beauty b
WHERE bo.id=b.boyfriend_id
);

#EXISTS
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
SELECT boyfriend_id
FROM beauty b
WHERE bo.id=b.boyfriend_id
);