苍海之南

Menu

Administrator
Published on 2021-08-08 / 138 Visits
0
0

MySQL核心之子查询

#MySQL

子查询

分类

  • 位于 select 后面：仅仅支持标量子查询
  • 位于 from 后面：支持表子查询
  • 位于 where 或 having 后面：支持标量子查询，列子查询，行子查询
  • 位于 exists后面：表子查询

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

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

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

表子查询（结果集一般为多行多列）

特点

  • 子查询放在小括号内
  • 子查询一般放在条件的右侧
  • 标量子查询，一般搭配着单行操作符使用
  • 列子查询，一般搭配着多行操作符使用 in、any/some、all

位于 where或 having 后面

标量子查询

#案例1.谁的工资比 Abel 高?
SELECT *
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 MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT  MIN(salary)
	FROM employees
	WHERE department_id = 50
);

列子查询

#案例1.返回location_id是1400或1700的部门中的所有员工姓名
SELECT last_name
FROM employees
WHERE department_id  <>ALL(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400,1700)
);

#案例2.返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id以及salary
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ANY(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
#或
SELECT last_name,employee_id,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 last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ALL(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
#或
SELECT last_name,employee_id,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 *
FROM employees
WHERE employee_id=(
	SELECT MIN(employee_id)
	FROM employees
)AND salary=(
	SELECT MAX(salary)
	FROM employees
);

位于 select 后面

仅仅支持标量子查询

#案例1.查询每个部门的员工个数
SELECT d.*,(
	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id = d.`department_id`
 ) 个数
FROM departments d;
  
#案例2.查询员工号=102的部门名
SELECT (
	SELECT department_name,e.department_id
	FROM departments d
	INNER JOIN employees e
	ON d.department_id=e.department_id
	WHERE e.employee_id=102
) 部门名;

位于 from后面

将子查询结果充当一张表，要求必须起别名

#案例1.查询每个部门的平均工资的工资等级
SELECT  ag_dep.*,g.`grade_level`
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

位于 exists 后面

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

#in
SELECT department_name
FROM departments d
WHERE d.`department_id` IN(
	SELECT department_id
	FROM employees
)

#exists
SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE d.`department_id`=e.`department_id`
);

#案例2.查询没有女朋友的男神信息

#in
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN(
	SELECT boyfriend_id
	FROM beauty
)

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

练习

#1.查询和Zlotkey相同部门的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE department_id = (
	SELECT department_id
	FROM employees
	WHERE last_name = 'Zlotkey'
)

#2.查询工资比公司平均工资高的员工的员工号，姓名和工资
SELECT last_name,employee_id,salary
FROM employees
WHERE salary>(
	SELECT AVG(salary)
	FROM employees
);

#3.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
SELECT employee_id,last_name,salary,e.department_id
FROM employees e
INNER JOIN (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) ag_dep
ON e.department_id = ag_dep.department_id
WHERE salary>ag_dep.ag ;

#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT last_name,employee_id
FROM employees
WHERE department_id IN(
	SELECT  DISTINCT department_id
	FROM employees
	WHERE last_name LIKE '%u%'
);

#5.查询在部门的location_id为1700的部门工作的员工的员工号
SELECT employee_id
FROM employees
WHERE department_id =ANY(
	SELECT DISTINCT department_id
	FROM departments 
	WHERE location_id  = 1700
);

#6.查询管理者是King的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE manager_id IN(
	SELECT employee_id
	FROM employees
	WHERE last_name  = 'K_ing'
);

#7.查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为 姓.名
SELECT CONCAT(first_name,last_name) "姓.名"
FROM employees
WHERE salary=(
	SELECT MAX(salary)
	FROM employees
);

经典案例

查询工资最低的员工信息: last_name, salary

#1.查询最低的工资
SELECT MIN(salary)
FROM employees

#2.查询last_name,salary，要求salary=1
SELECT last_name,salary
FROM employees
WHERE salary=(
	SELECT MIN(salary)
	FROM employees
);

查询平均工资最低的部门信息

#方式一
#1.各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#2.查询①结果上的最低平均工资
SELECT MIN(ag)
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) ag_dep
#3.查询哪个部门的平均工资=2
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
	SELECT MIN(ag)
	FROM (
		SELECT AVG(salary) ag,department_id
		FROM employees
		GROUP BY department_id
	) ag_dep
);

#4.查询部门信息
SELECT d.*
FROM departments d
WHERE d.`department_id`=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	HAVING AVG(salary)=(
		SELECT MIN(ag)
		FROM (
			SELECT AVG(salary) ag,department_id
			FROM employees
			GROUP BY department_id
		) ag_dep
	)
);

#方式二
#1.各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#2.求出最低平均工资的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) 
LIMIT 1;
#3.查询部门信息
SELECT *
FROM departments
WHERE department_id=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary) 
	LIMIT 1
);

查询平均工资最低的部门信息和该部门的平均工资

#1.各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

#2.求出最低平均工资的部门编号
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) 
LIMIT 1;

#3.查询部门信息
SELECT d.*,ag
FROM departments d
JOIN (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary) 
	LIMIT 1
) ag_dep
ON d.`department_id`=ag_dep.department_id;

查询平均工资最高的 job 信息

#1.查询最高的job的平均工资
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1

#2.查询job信息
SELECT * 
FROM jobs
WHERE job_id=(
	SELECT job_id
	FROM employees
	GROUP BY job_id
	ORDER BY AVG(salary) DESC
	LIMIT 1
);

查询平均工资高于公司平均工资的部门有哪些?

#1.查询平均工资
SELECT AVG(salary)
FROM employees

#2.查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

#3.筛选②结果集，满足平均工资>1
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)>(
	SELECT AVG(salary)
	FROM employees
);

查询出公司中所有 manager 的详细信息

#1.查询所有manager的员工编号
SELECT DISTINCT manager_id
FROM employees

#2.查询详细信息，满足employee_id=1
SELECT *
FROM employees
WHERE employee_id =ANY(
	SELECT DISTINCT manager_id
	FROM employees
);

各个部门中最高工资中最低的那个部门的最低工资是多少

#1.查询各部门的最高工资中最低的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1

#2.查询1结果的那个部门的最低工资
SELECT MIN(salary) ,department_id
FROM employees
WHERE department_id=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY MAX(salary)
	LIMIT 1
);

查询平均工资最高的部门的manager的详细信息: last_name, department_id, email, salary

#1.查询平均工资最高的部门编号
SELECT 
    department_id 
FROM
    employees 
GROUP BY department_id 
ORDER BY AVG(salary) DESC 
LIMIT 1 

#2.将employees和departments连接查询，筛选条件是1
SELECT
	last_name,
	d.department_id,
	email,
	salary 
FROM
	employees e
	INNER JOIN departments d ON d.manager_id = e.employee_id 
WHERE
	d.department_id = ( SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG( salary ) DESC LIMIT 1 );
MySQL核心之分页查询
MySQL核心之连接查询

Comment