MySQL子查询

子查询

出现在其他语句中的SELECT语句,称为子查询或内查询

外部的查询语句称为主查询(外查询)

示例

#查询部门地址编号为1700的所有员工的名字
SELECT first_name FROM employee
WHERE department_id in (
    SELECT department_id FROM departments
    WHERE loaction_id=1700
)

按子查询出现位置分类

• SELECT 后面
• FROM 后面
• WHERE 后面
• HAVING 后面
• EXISTS 后面

按子查询结果分类

• 标量子查询 (一行一列)
• 列查询(一列多行)
• 行查询(一行多列)
• 表子查询(多行多列)

标量子查询(WHERE或HAVING后)

#查询比小明工资高的所有员工的信息
SELECT * 
FROM employees
WHERE salary>(
    SELECT salary
    FROM employees
    WHERE neme='小明'
);

列子查询(WHERE或HAVING后)

多行比较操作符

示例

#查询location_id是1400或1700的部门的所有员工名字
SELECT name
FROM employees
WHERE department_id IN(
    SELECT DISTINCT department_id 
    FROM departments
    WHERE loaction_id IN(1400,1700)
);

行子查询

#查询员工中编号最小且工资最高的员工信息
SELECT *
FROM employee
WHERE (employee_id,salary)=(
    SELECT MIN(employee_id),MAX(salary)
    FROM employees
);

SELECT后的子查询

#查询部门信息及每个部门的员工个数
SELECT d.*,(
    SELECT COUNT(*)
    FROM employee e
    WHERE e.department_id=d.department_id
) 员工个数
FROM department d;

FROM后的子查询

#查询每个部门的平均工资的工资水平
SELECT agv_dep.*,g.grade_level
FROM (
    SELECT AVG(salary) ag,department_id
    FROM employees
    GROUP BY department_id
) avg_dep
INNER JOIN job_grades g
ON avg_dep.ag BETWEEN lowest_sal AND highest_sal;

EXISTS后的子查询(相关子查询)

EXISTS(查询语句)

结果:1或0

#查询有员工的部门名
SELECT department_name 
FROM departments d
WHERE EXISTS(
    SELECT *
    FROM employees e
    WHERE d.department_id=e.department
)

MySQL子查询

原文：https://www.cnblogs.com/redo19990701/p/11286713.html