MySQL初级-9-子查询

本文最后更新于:2020年7月24日 下午

mysql Ver 14.14 Distrib 5.5.62

文中案例所用数据库下载

9.1 含义

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

外部的查询语句,称为主查询或外查询。

9.2 分类

  1. 按子查询出现的位置。

    SELECT后:标量子查询。

    FROM后:表子查询。

    WHERE或HAVING后:标量子查询、列子查询、行子查询。

    EXISTS后(相关子查询):表子查询。

  2. 按结果集的行列数。

    标量子查询:结果集只有一行一列。

    列子查询:结果集有多行一列。

    行子查询:结果集有一行多列。

    表子查询:结果集一般为多行多列。

9.3 示例

9.3.1 WHERE或HAVING后

特点:

​ 1、子查询放在小括号内。

​ 2、子查询一般放在条件的右侧。

​ 3、标量子查询一般搭配单行操作符使用。如:>、<、>=、<=、<>。

​ 4、列子查询一般搭配多行操作符使用。如:IN、ANY/SOME、ALL。

​ 5、子查询的执行优先于主查询执行,主查询的条件用到了子查询的结果。

9.3.1.1 标量子查询(单行子查询)

  1. 谁的工资比Abel高?

    SELECT last_name 员工名
    FROM employees 
    WHERE salary>(
    	SELECT salary
    	FROM employees
    	WHERE last_name='Abel'
    );
  1. 查询job_id与141号员工相同,且salary比143号员工高的员工名、职位编号和工资。

    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
    );
  1. 查询公司工资最少的员工的员工名、职位编号和工资。

    SELECT last_name 员工名,job_id 职位编号,salary 工资
    FROM employees
    WHERE salary=(
    	SELECT MIN(salary)
    	FROM employees
    );
  1. 查询最低工资大于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
    );

9.3.1.2 列子查询(多行子查询)

  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)
    );
  1. 查询其他工种中比job_id为IT_PROG工种任意工资低的员工号、员工名、工种编号和工资。

    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';
  1. 查询其他工种中比job_id为IT_PROG工种所有工资都低的员工号、员工名、工种编号和工资。

    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';

9.3.1.3 行子查询

  1. 查询员工编号最小且工资最高的员工信息。

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

9.3.2 SELECT后

  1. 查询每个部门的员工个数。

    SELECT d.department_name 部门名,(
    	SELECT COUNT(*)
    	FROM employees e
    	WHERE e.department_id=d.department_id
    ) 员工个数
    FROM departments d;
  1. 查询员工号为102的员工所在的部门名。

    SELECT (
    	SELECT department_name
    	FROM departments d
    	INNER JOIN employees e
    	ON d.department_id=e.department_id
    	WHERE e.employee_id=102
    	
    ) 部门名;

9.3.3 FROM后

将子查询结果作为一张表,必须起别名。

  1. 查询每个部门的部门名、平均工资的工资等级

    SELECT avg_sal.*,j.grade_level 工资等级
    FROM (
    	SELECT e.department_id 部门编号,d.department_name 部门名,AVG(e.salary) 平均工资
    	FROM employees e
    	INNER JOIN departments d
    	ON d.`department_id`=e.`department_id`
    	GROUP BY e.department_id
    ) avg_sal
    INNER JOIN job_grades j
    ON 平均工资 BETWEEN j.`lowest_sal` AND j.`highest_sal`;

9.3.4 EXISTS后(相关子查询)

语法:EXISTS(完整的查询语句);

  1. 查询有员工的部门。

    SELECT department_name 部门名
    FROM departments d
    WHERE EXISTS(
    	SELECT *
    	FROM employees e
    	WHERE d.`department_id`=e.`department_id`
    );
  1. 查询没有男友的女神的信息。
SELECT b.name 女神
FROM beauty b
WHERE NOT EXISTS(
	SELECT *
	FROM boys bo
	WHERE b.`boyfriend_id`=bo.`id`
);

版权声明:自由转载-非商用-非衍生-保持署名 Creative Commons BY-NC-ND 3.0 协议,转载请注明出处!

 目录