mysql 子查询总结

单行子查询

单行比较操作符

查询工资大于149号员工工资的员工的信息

返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资

返回公司工资最少的员工的last_name,job_id和salary

查询与141号或174号员工的manager_id和department_id相同的其他员工employee_id，

manager_id，department_id

SELECT employee_id, manager_id, department_id 
FROM employees
 WHERE manager_id IN
 (SELECT manager_id FROM employees WHERE employee_id IN (174,141)) 

AND department_id IN 
(SELECT department_id FROM employees WHERE employee_id IN (174,141)) 

AND employee_id NOT IN(174,141)

SELECT
	employee_id,
	manager_id,
	department_id
FROM
	employees
WHERE
	(manager_id, department_id) IN (
		SELECT
			manager_id,
			department_id
		FROM
			employees
		WHERE
			employee_id IN (141, 174)
	)
AND employee_id NOT IN (141, 174);

查询最低工资大于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
	);

CASE中的子查询

在CASE表达式中使用单列子查询：

显式员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800 的department_id相同，则location为’Canada’，其余则为’USA’

SELECT
	employee_id,
	last_name,
	(
		CASE department_id
		WHEN (
			SELECT
				department_id
			FROM
				departments
			WHERE
				location_id = 1800
		) THEN
			'Canada'
		ELSE
			'USA'
		END
	) location
FROM
	employees;

多行子查询

• 也称为集合比较子查询；
• 内查询返回多行；
• 使用多行比较操作符；

返回其它job_id中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及salary

查询平均工资最低的部门id

方式1：

SELECT
	department_id
FROM
	employees
GROUP BY
	department_id
HAVING
	AVG(salary) = (
		SELECT
			MIN(avg_sal)
		FROM
			(
				SELECT
					AVG(salary) avg_sal
				FROM
					employees
				GROUP BY
					department_id
			) dept_avg_sal
	)

方式2：

SELECT
	department_id
FROM
	employees
GROUP BY
	department_id
HAVING
	AVG(salary) <= ALL (
		SELECT
			AVG(salary) avg_sal
		FROM
			employees
		GROUP BY
			department_id
	)

相关子查询

关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为 关联子查询 。 相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询

 语法结构：

查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id

方式1：相关子查询

方式2：在 FROM 中使用子查询

SELECT
	last_name,
	salary,
	e1.department_id
FROM
	employees e1,
	(
		SELECT
			department_id,
			AVG(salary) dept_avg_sal
		FROM
			employees
		GROUP BY
			department_id
	) e2
WHERE
	e1.`department_id` = e2.department_id
AND e2.dept_avg_sal < e1.`salary`;

查询员工的id,salary,按照department_name 排序

SELECT
	employee_id,
	salary
FROM
	employees e
ORDER BY
	(
		SELECT
			department_name
		FROM
			departments d
		WHERE
			e.`department_id` = d.`department_id`
	);

若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输这些相同 id的员工的employee_id,last_name和其job_id

SELECT
	e.employee_id,
	last_name,
	e.job_id
FROM
	employees e
WHERE
	2 <= (
		SELECT
			COUNT(*)
		FROM
			job_history
		WHERE
			employee_id = e.employee_id
	);

EXISTS 与 NOT EXISTS关键字

查询公司管理者的employee_id，last_name，job_id，department_id信息

方式1：

SELECT
	employee_id,
	last_name,
	job_id,
	department_id
FROM
	employees e1
WHERE
	EXISTS (
		SELECT
			*
		FROM
			employees e2
		WHERE
			e2.manager_id = e1.employee_id
	);

方式2：自连接

SELECT DISTINCT
	e1.employee_id,
	e1.last_name,
	e1.job_id,
	e1.department_id
FROM
	employees e1
JOIN employees e2
WHERE
	e1.employee_id = e2.manager_id;

方式3：

SELECT DISTINCT
	e1.employee_id,
	e1.last_name,
	e1.job_id,
	e1.department_id
FROM
	employees e1
WHERE
	e1.employee_id IN (
		SELECT DISTINCT
			manager_id
		FROM
			employees
	);

查询departments表中，不存在于employees表中的部门的department_iddepartment_name

SELECT
	department_id,
	department_name
FROM
	departments d
WHERE
	NOT EXISTS (
		SELECT
			'X'
		FROM
			employees
		WHERE
			department_id = d.department_id
	);

相关更新

UPDATE table1 alias1
SET COLUMN = (
	SELECT
		expression
	FROM
		table2 alias2
	WHERE
		alias1. COLUMN = alias2. COLUMN
);

在employees中增加一个department_name字段，数据为员工对应的部门名称

方式1：

ALTER TABLE employees ADD (
	department_name VARCHAR2 (14)
);

方式2：

UPDATE employees e
SET department_name = (
	SELECT
		department_name
	FROM
		departments d
	WHERE
		e.department_id = d.department_id
);

相关删除

DELETE
FROM
	table1 alias1
WHERE
	COLUMN operator (
		SELECT
			expression
		FROM
			table2 alias2
		WHERE
			alias1. COLUMN = alias2. COLUMN
	);

删除表employees中，其与emp_history表皆有的数据

DELETE
FROM
	employees e
WHERE
	employee_id IN (
		SELECT
			employee_id
		FROM
			emp_history
		WHERE
			employee_id = e.employee_id
	);

谁的工资比Abel的高？

方式1：自连接

SELECT
	e2.last_name,
	e2.salary
FROM
	employees e1,
	employees e2
WHERE
	e1.last_name = 'Abel'
AND e1.`salary` < e2.`salary`

方式2：子查询

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

以上两种方式有好坏之分吗？解答：自连接方式好！

原因解释：