MySql（十三）子查询

子查询

定义:出现在其他语句中内部的select语句，称为子查询或内查询

外面的语句可以是insert、update、delete、select等；一般select作为外面语句较多。

外面的语句如果是select语句，则此语句称为外查询或主查询

分类:

1. 按照子查询出现的位置：

   select后面：仅仅支持标量子查询

   from后面：支持表子查询

   where或having后面：标量子查询、列子查询、行子查询、表子查询

   exists后面：标量子查询、列子查询、行子查询、表子查询

2. 按照结果集行列数分类：

   标量子查询（单行子查询）：结果集只有一行一列

   列子查询（多行子查询）：结果集只有一列多行

   行子查询：结果一行多列或者多行多列

   表子查询（嵌套子查询）：一般为多行多列（一行一列、一列多行、一行多列都可以）

where或having后面的子查询

支持查询：标量子查询、列子查询、行子查询

特点：①子查询放在小括号内

②子查询一般放在条件的右侧

③标量子查询一般搭配着单行操作符使用（单行操作符：>、<、>=、<=、<>）

④列子查询一般搭配着多行操作符使用(多行操作符：in、any、some、all)

⑤子查询的执行都是优先于主查询的执行

1.标量子查询:一行一列

标量子查询一般搭配着单行操作符使用（单行操作符：>、<、>=、<=、<>）

/**案例1：查询谁的工资比Abel工资高
	分析：①查询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;②查询143号员工的salary；③在①和②的基础上查询姓名、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
	分析：①查找公司工资最少的salary；②在①的基础上查询last_name,job_id和salary
**/
SELECT last_name,job_id,salary
FROM employees
WHERE salary = (
	SELECT MIN(salary)FROM employees
);
/**查询最低工资大于50号部门的最低工资的部门id和其最低工资
	分析：①查询50号部门的最低工资；②查询所有部门的最低工资；③在②的基础上筛选出salary>①的部门id和其最低工资**/
SELECT department_id, MIN(salary)
FROM emplooyees
GROUP BY department_id
HAVING MIN(salary) > (
	SELECT salary FROM employees
    WHERE department_id = 50
);

2.列子查询（多行子查询）：一列多行

列子查询一般搭配着多行操作符使用(多行操作符：in、any、some、all)

操作符	含义
in、not in	（不）等于列表中的任意一个
any、some	和子查询返回的某一个值比较
all	和子查询返回的所有值比较

/**案例1：返回location_id是1400或1700的部门中的所有员工的姓名
	分析：①：查询location_id是1400或1700的部门编号；②：有①的部门编号查出所有员工的姓名
**/
SELECT last_name FROM employees
WHERE department_id IN (
	SELECT department_id 
    FROM departments
    WHERE location_id IN(1400,1700)
);
/**案例2：方法一：返回其他工种中比job_id为'IT_PROG'部门任意工资低的员工的：工号、姓名、job_id以及salary
	分析：①查询job_id为'IT_PROG'的部门中的任意工资
**/
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ANY(
	SELECT salary
    FROM employees
    WHERE job_id = 'IT_PROG'
)AND job_id <> 'IT_PROG';
/**案例2的方法二：**/
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < (
	SELECT MIN(salary)
    FROM employees
    WHERE job_id = 'IT_PROG'
)AND job_id <> 'IT_PROG';
/**案例3：方法一：返回其他工种中比job_id为'IT_PROG'部门所有工资低的员工的：工号、姓名、job_id以及salary
	分析：①查询job_id为'IT_PROG'的部门中的任意工资
**/
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ALL(
	SELECT salary
    FROM employees
    WHERE job_id = 'IT_PROG'
)AND job_id <> 'IT_PROG';

3.行子查询：一行多列或多行多列

/**案例1：方法一：查询员工编号最小并且工资最高的员工信息**/
SELECT * FROM employees
WHERE employee_ie = (
	SELECT MIN(employee_id)
    FROM employees
)AND salary = (
	SELECT MAX(salary)
    FROM employees
);
/**案例1：方法二：**/
SELECT * FROM employees
WHERE （employee_id,salary) = (
	SELECT MIN(employee_id),MAX(salary)
    FROM employees
);

select后面的子查询

仅仅支持标量子查询(单行单列)

/**案例1：查询每个部门的员工个数
	分析：涉及到表departments和表employees:count(*) --> department_id
**/
SELECT d.*, (
	SELECT COUNT(*) FROM employees e
    WHERE d.'department_id' = e.'department_id'
)员工个数
FROM departments d;
/**查询员工号=102的部门名
	分析：涉及到表departments和表employees:employee_id --> department_id --> department_name
**/
SELECT department_name
FROM departments d
WHERE d.department_id = (
	SELECT e.department_id 
    FROM employees e
    WHERE employee_id = 102
);

from后面的子查询

支持表子查询

/**案例1：查询每个部门的平均工资的工资等级
	分析：①查询每个部门的平均工资（查询结果为一个结果集）；
	②将①的结果集起别名作为一张新表（avg），将表avg和表grade_level进行连接查询
**/
SELECT avg.*,grade_level
FROM (
	SELECT AVG(salary) ag
	FROM employees
	GROUP BY department_id
) avg
INNER JOIN job_level 
ON avg.ag BETWEEN lowest_sal AND highest_sal;

exists后面的子查询

exists后面的子查询又称为相关子查询

语法：

exists(完整的查询语句);

结果为：0或1

/**案例1：查询有员工的部门名**/
SELECT department_name 
FROM departments d
WHERE EXISTS (
    	SELECT * FROM employees e
       	WHERE d.department_id  = e.department_id
       );
/**方法二：**/
SELECT department_name
FROM departments d
WHERE d.department_id IN (
    SELECT e.department_id 
    FROM employees e
)
/**案例2：查询没有女朋友的男神信息**/
SELECT * FROM boys b
WHERE b.id NOT IN(
	SELECT boyfriend_id 
    FROM beauty
);
/**案例2方法二：查询没有女朋友的男神信息**/
SELECT * FROM boys b
WHERE NOT EXISTS(
	SELECT *
    FROM beauty bea
    WHERE b.id = bea.boyfriend_id
);