21、MySQL基础之子查询

1、何为子查询

嵌套在其它语句内部的select语句，称为子查询或内查询，外面的语句可以是insert、update、delete、select等，一般select作为外面语句较多，外面如果为select语句，则此语句称为外查询或主查询

2、子查询的分类

按结果集的行列数不同分类

标量子查询

子查询的结果只有一行一列
注：标量子查询，一般搭配着单行操作符使用，常见的单行操作符有：>、<、<=、>=、<>、=

列子查询

子查询的结果只有一列多行
注：列子查询，一般搭配着多行操作符使用，常见的多行操作符有：in、any/some、all

行子查询

子查询的结果只有一行多列

表子查询

子查询的结果一般为多行多列

按子查询出现的位置分类

select后面

仅仅支持标量子查询

from后面

有查询结果就可以
注意事项：将子查询结果充当一张表，必须起别名

where或having后面：

支持标量子查询（重点）
支持列子查询（重点）
支持行子查询（很少用）

exists后面

有查询结果就可以

3、子查询的特点

1、子查询需要放在小括号内
2、子查询一般放在条件的右侧
3、子查询的执行优先于主查询

4、案例

放在where或者having后面

标量子查询案例

案例1：谁的工资比Abel高？

SELECT last_name,salary
FROM employees
WHERE salary>
	   (SELECT salary 
	    FROM employees
	    WHERE last_name ='ABel'
			);

案例2：返回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
                  )

案例3：返回公司工资最少的员工last_name，job_id和salary

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

案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资

SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >(SELECT MIN(salary)
                     FROM employees
                     WHERE department_id = 50
					 )

列（多行）子查询案例

列子查询的特点：返回一列多行

多行比较符：

1、in|not in：判断是否属于in列表（常用）
2、any|some：用于和any列表中的任意一个值进行比较
3、all：用于和all列表中的所有值进行比较

案例1：返回location_id是1400或1700的部门中的所有员工姓名

SELECT last_name
FROM employees
WHERE department_id in(SELECT DISTINCT department_id
                       FROM departments
                       WHERE location_id in(1400,1700)
                       )
-- 注意对in列表中的值进行去重处理

案例2：查询出其它工种中，比员工的job_id为‘IT_PROG’的任意工资低的员工工号、姓名、job_id以及salary

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ANY(SELECT  DISTINCT salary
                   FROM employees
                   WHERE job_id = 'IT_PROG'
				   ) 
				AND job_id <> 'IT_PROG';
-- 注意对any列表中的值进行去重处理
-- 或者
SELECT employee_id,last_name,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 employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ALL(SELECT  DISTINCT salary
                   FROM employees
                   WHERE job_id = 'IT_PROG'
				   ) 
				AND job_id <> 'IT_PROG';
-- 注意对any列表中的值进行去重处理
-- 或者
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';

行子查询案例（不常用）

行子查询特点：返回一行多列

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

SELECT *
FROM employees
WHERE (employee_id,salary) = (SELECT MIN(employee_id),MAX(salary)
                              FROM employees															)

放在select后面

案例1：查询每个部门的员工数

SELECT d.*,(SELECT COUNT(*)
		    FROM employees e
			WHERE d.department_id = e.department_id
           ) 个数
FROM departments d
-- select后面的子查询只能是 标量子查询,查询结果有n行,该标量子查询会被执行n次.
-- 此处标量子查询的功能:针对d表当前行的department_id从e表中筛选出和该department_id相等的记录,并统计这些记录的个数.
-- 或者
SELECT d.*,COUNT(e.department_id)
FROM departments d
LEFT JOIN employees e
on d.department_id = e.department_id
GROUP BY d.department_id 

案例2：查询员工号为102的 部门名

-- 方法一:根据e表当前行的department_id在d表中寻找与该department_id相等的记录,并返回该记录的 department_name   等价于在e表的右侧新增部门名列
SELECT employee_id,(SELECT department_name
                    FROM departments d
                    WHERE e.department_id = d.department_id) 部门名
FROM employees e
WHERE employee_id = 102
-- 方法二
SELECT d.department_name
FROM employees e
LEFT JOIN departments d
on e.department_id = d.department_id
WHERE e.employee_id = 102

放在from后面

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

SELECT g.grade_level,ag_dep.*
FROM job_grades g
INNER JOIN  (SELECT department_id,AVG(salary) ag
            FROM employees
            GROUP BY department_id
			) ag_dep
on ag_dep.ag BETWEEN g.lowest_sal and g.highest_sal

放在exists后面（相关子查询）

语法：exists(完整的查询语句)
返回：若查询语句有查询结果,则返回 1,否则返回 0

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

SELECT department_id,department_name
FROM departments d
WHERE EXISTS (SELECT *
			  FROM employees e
			  WHERE d.department_id = e.department_id
)
-- 执行顺序
-- 1.首先执行一次外部查询
-- 2.对于外部查询中的每一行分别执行一次子查询，根据d表当前行的department_id在e表中寻找与该department_id相等的记录,若存在这样的记录,子查询返回1,否则返回0
-- 3.通过where筛选出子查询结果为 1 的行.
-- 或
SELECT department_id,department_name
FROM departments
WHERE department_id in (SELECT DISTINCT department_id
                        FROM employees
						)

5、子查询练习题

案例1：查询和zlotkey相同部门的员工姓名和工资

SELECT CONCAT(last_name,first_name),salary
FROM employees
WHERE department_id = (SELECT department_id
                       FROM employees
                       WHERE last_name = 'zlotkey'
					  );

案例2：查询工资比公司平均工资 高的员工的员工号，姓名和工资。

SELECT CONCAT(last_name,first_name),employee_id,salary
FROM employees
WHERE salary > (SELECT AVG(salary)
                FROM employees
				);

案例3：查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资

SELECT
	last_name,
	salary 
FROM
	employees e 
WHERE
	salary > ( SELECT AVG( salary ) FROM employees s GROUP BY department_id HAVING e.department_id = s.department_id );
-- 方法二
SELECT
	last_name,
	salary 
FROM
	employees e
	INNER JOIN ( SELECT AVG( salary ) ag, department_id d FROM employees GROUP BY department_id ) ag_dep ON e.department_id = ag_dep.d 
WHERE
	salary > ag_dep.ag

案例4：查询姓名包含u的部门里所有员工的员工名和员工号

SELECT
	last_name,
	employee_id 
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 
WHERE
	department_id IN ( SELECT department_id FROM departments WHERE location_id = 1700 );

案例6：查询管理者是K_ing的员工姓名和工资

SELECT
	last_name,
	salary 
FROM
	employees 
WHERE
	manager_id IN ( SELECT employee_id FROM employees WHERE last_name = 'k_ing' );

案例7：查询工资最高的员工的姓名，要求first_name和1ast_name显示为一-列，列名为姓.名

SELECT
	CONCAT( first_name, '.', last_name ) '姓.名' 
FROM
	employees 
WHERE
	salary = ( SELECT MAX( salary ) FROM employees );