MySQL子查询

MySQL子查询

一、子查询含义

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

二、分类

• 标量子查询（单行子查询）：一般搭配<,>,=

• 列子查询（多行子查询）：一般搭配IN,ANY,ALL，其中IN比较常用，ANY和ALL类似MAX和MIN的用法

• 行子查询（多行多列或一行多列）：使用频率较少

  【注意】：标量子查询和列子查询比较常用

三、示例

①WHERE和HAVING后面(重点)

示例一：谁的工资比Abel高（标量子查询）
#①查询Abel的工资

SELECT `salary`
FROM `employees`
WHERE`last_name` = 'Abel';

#②查询员工的信息，满足salary>①中的结果

SELECT *
FROM `employees`
WHERE `salary` >(
SELECT `salary`
FROM `employees`
WHERE`last_name` = 'Abel'
);

示例二：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资（标量子查询）

#①查询141号员工的job_id

SELECT `job_id`
FROM `employees`
WHERE `employee_id` = 141;

#②查询143号的salary

SELECT `salary`
FROM `employees`
WHERE `employee_id` = 143;

#③查询员工姓名，job_id和工资，满足job_id=①，salary>②

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

示例三：返回location_id是1400或者1700的部门中的所有员工姓名（列子查询）
#①查询location_id为1400或1700的部门

SELECT d.`department_id`
FROM `departments` d
WHERE `location_id` IN('1400','1700');

#②查询部门=①时，所有员工的姓名

SELECT `last_name`
FROM `employees`
WHERE `department_id` IN(
	SELECT d.`department_id`
	FROM `departments` d
	WHERE `location_id` IN('1400','1700')
);

示例四：查询员工编号最小并且工资最高的员工（行子查询）

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`
) 员工个数
FROM `departments` d;

③FROM后面

示例一：查询每个部门的平均工资的工资等级
#①查询每个部门的平均工资

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

#②连接①中的结果集和job_grades表，筛选条件为平均工资between lowest_sal and highest_sal

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

【注意】：FROM后面将子查询的结果充当一张表，必须对它起别名

④EXISTS后面（较少使用）

示例一：查询没有女朋友的男神信息

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