MySQL数据库子查询总结 mysql子查询原理

子查询是MySQL中比较重要的内容，包括在JDBC语法中，也是经常用到的内容，一定要认知学习子查询，在学习完子查询后，再学习学习视图和约束等东西就可以去学JDBC了，然后就是JavaWeb，其实JDBC没什么特点，只是说将SQL语句写在了Java文章中，然后程序可以和MySQL数据交互，所以MySQL语句不会就无法使用JDBC。

子查询

通过嵌套SELECT来实现子查询

#子查询
#谁的工资比 Abel的高？
#方式一
SELECT last_name,salary
FROM employees 
WHERE last_name = 'Abel'

SELECT last_name,salary
FROM employees
WHERE salary > 1100;

#自连接
SELECT t2.last_name,t2.`salary`
FROM employees t1,employees t2
WHERE t2.`salary` > t1.`salary` 
AND t1.`last_name` = 'Abel';

#子查询
#美观+可读性比较强
SELECT last_name,salary
FROM employees
WHERE salary > (
	SELECT salary
	FROM employees
	WHERE last_name = 'Abel'
);
#可读性一般
SELECT last_name,salary
FROM employees
WHERE (
	SELECT salary
	FROM employees
	WHERE last_name = 'Abel'
)< salary;
#由需求引入子查询
#称谓的规范：外查询（主查询）、内查询（子查询）
#注意事项：
#	子查询药包含在括号捏
#	将子查询放在比较条件的右侧
#	单行操作符对应单行子查询，多行操作符对应多行子查询

单行子查询

=号是单行，返回多于一行的内容如果使用=接收则会报错。

#多行子查询
#多行子查询的操作符： IN ANY ALL SOME(同ANY)
#举例
#IN:
SELECT employee_id,last_name
FROM employees
WHERE salary IN (
		SELECT MIN(salary)
		FROM employees
		GROUP BY department_id
)

#ANY/ALL
#返回其他job_id中比'IT_PROG'部门任一工资低的员工的员工号、
#姓名、job_id以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND 
salary < ANY(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'); 8:55













#查询工资大于149号员工工资的信息
#核心思路：
/*
先在外查询中添加 last_name(名字)和salary(工资) 来自员工表
然后在过滤条件中筛选
子查询为 查询salary，；来自员工表，条件为employee_id(员工编号)149
where语句 所有数据的salary>子查询结果(149号)的salary的数据
*/
SELECT employee_id last_name,salary
FROM employees
WHERE salary>(
	SELECT salary
	FROM employees
	WHERE employee_id = 149
)

#返回job_id与141员工相同(返回工作id和141号员工相同的),salary(工资)比143员工多的员工姓名(last_name)
#job_id(工作id)和工资(salary)
#核心思路：
/*
这个其实也挺简单的
我们先根据题目得知主查询的内容为 last_name(姓名)，job_id(工作id)，salary(工资)
第一个要求是 job_id与141员工的job_id相同 需要查询141员工的job_id
第二个条件是工资比 143员工的工资高 需要查询143员工的工资 
通过WHERE来过滤，先要求job_id等于141员工的job_id job_id>select(job_id(employee_id = 141))
而第二个条件也是相同 第一个过滤完的数据的salary 大于  salary>select(salary(employee_id = 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
)

#返回工司工资最少的last_name，job_id和salary
#这个也不难
SELECT last_name,job_id,salary
FROM employees
WHERE salary = (
	SELECT MIN(salary)
	FROM employees

)
#查找和141号员工的manager_id和department_id相同的其他员工
#这里的意思就是 查找141号员工的manager_id和department_id相同的员工
#而其他意思就是不包含141号
SELECT employee_id,manager_id,department_id
FROM employees
WHERE manager_id = (
			SELECT manager_id
			FROM employees	
			WHERE employee_id =141
)
AND department_id = (
			SELECT department_id
			FROM employees	
			WHERE employee_id =141
)
AND employee_id <> 141;
		     

#子查询的分类
#角度1：内查询返回的结果的条目数
#单行子查 和 多行子查询 
#角度2：内查询是否被执行多次
#相关子查询 和 不相关子查询
#比如相关子查询需求：查询工资大于本部门平均工资的信息
#    不相关子查询的需求：查询工资大于本公司平均的员工信息


#子查询编写技巧：①从里往外写 ②从外往里写
#单行子查询
#单行操作符:： = ！= > >= < <= <>
#查询工资大于149号工资的员工的信息
SELECT employee_id,last_name,salary
FROM employees 
WHERE salary>(
		SELECT salary
		FROM employees
		WHERE employee_id = 149
		);
#返回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	
		);

#返回公司工资最少的员工的last_name,job_id和salary
SELECT last_name,job_id,salary
FROM  employees
WHERE salary = (
		SELECT  MIN(salary)
		FROM employees		
		);

#查询与141号或174号员工的manager_id和department_id相同的其他员工
#的employee_id,manager_id,department_id
 SELECT employee_id,manager_id,department_id
 FROM employees
 WHERE manager_id = (
			SELECT manager_id
			FROM employees
			WHERE employee_id = 141
			)
AND department_id = (
			SELECT department_id
			FROM employees
			WHERE employee_id = 141
			)
AND employee_id <> 141;

#方式二
SELECT employee_id,manager_id,department_id
FROM employees	
WHERE (manager_id,department_id)=(
		SELECT manager_id,department_id
		FROM employees
		WHERE employee_id = 141 
		);
#查询最低工资大于50部门最低工资的部门id和其他最低工资
SELECT department_id,MIN(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary) > (
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
);
#显示员工的employee_id,last_name和Location。
#其中，若员工department_id与location_id为1800的department_id相同
#则localtion为'Canda'，其余为'USA'
SELECT employee_id,last_name,(CASE department_id WHEN (SELECT 
				department_id FROM departments 
				WHERE location_id = 1800) THEN 'Canda' 
				ELSE 'USA' END ) "location"
FROM employees;

#多行子查询
#多行子查询的操作符：IN ANY ALL SOME（同ANY）
#IN
#查询了每个部门工资最低的员工
#IN等同于多个内容的任意一个
SELECT employee_id,last_name
FROM employees
WHERE salary IN (SELECT MIN(salary)
FROM employees
GROUP BY department_id
)


#ANY/ALL
#返回其他job_id中比job_id为 'IT_PROG'部门任一工资低的员工的员工号、
#姓名、Job_id以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND
salary < ANY (SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')
#返回其他job_id中比job_id为 'IT_PROG'部门所有工资低的员工的员工号、
#姓名、Job_id以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND
salary < ALL (SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')


#查询平均工资最低的部门
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
) t_dept_avg_sal

)
#查询平均工资最低的部门
#MySQL中聚合函数中是不能嵌套使用的
SELECT MIN(avg_sal)
FROM (
		SELECT AVG(salary) avg_sal
		FROM employees
		GROUP BY department_id
) t_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
		)
#空值问题
SELECT last_name
FROM employees
WHERE employee_id NOT IN (
			SELECT manager_id
			FROM employees
			#where manager_id is not null
);

相关子查询案列分析

#相关子查询
#查询员工中大于本公司平均工资的员工的last_name，salary和其department_id 
SELECT last_name,salary,department_id
FROM employees
WHERE salary>(
	SELECT AVG(salary)
	FROM employees
	);



#查询员工中工资大于本部门平均工资的员工的last_name，salary和其department_id
SELECT last_name,salary,department_id
FROM employees e1
WHERE salary>(
	SELECT AVG(salary)
	FROM employees e2
	WHERE department_id = e1.`department_id`
	);
#方法2
#思路解析：
#相当于使用了SQL92语法，进行多表查询，这里使用子查询建立了一个虚表
#这里虚表的数据为 department_id(注意是分组的)和分组后的平均工资
#首先使用等值自连接 外表和子查询表的部门id相连接
#加上AND条件 e的工资 大于 子查询表的部门id工资
#注意这里是进行了部门id相匹配 然后 部门员工工资和已经平均好的工资进行比较
SELECT e.last_name,e.salary,e.department_id
FROM employees e,(
		SELECT department_id,AVG(salary) sal
		FROM employees
		GROUP BY department_id) avg_sal
WHERE e.department_id =	avg_sal.department_id
AND e.salary > avg_sal.sal


#查询员工的id，salary，按照department_name排序
SELECT employee_id,salary
FROM employees  e
ORDER BY (
	SELECT department_name
	FROM departments t
	WHERE e.`department_id` = t.`department_id`
	)
	
#子查询的结论：在select结构中除了 group by 和 limit之外，其他都可以声明子查询
/*
select ...,...,...（存在聚合函数）
from ...(left/right(不支持的full))JOIN ... ON 多表的连接条件
(left/right(不支持full))JOIN ... ON ...
WHERE 不包含聚合函数的过滤条件
group by ...,... 这个地方不能写子查询
having 包含聚合函数的过滤条件
order by ...,...(ASC/DESC)默认ASC
LIMIT ...,... 迁移量和保留数 offset 这个地方也不能写子查询
*/

#若employees表中employee_id与job_history表中employee_id相同的数目不小于2
#输出这些相同id员工的employee_id,last_name和其job_id
SELECT employee_id,last_name,job_id
FROM employees e 
WHERE 2<=(
	SELECT COUNT(*)
	FROM job_history j
	WHERE e.`employee_id`=j.`employee_id`
)

#EXISTS 与 NOT EXISTS关键字
#查询公司管理者的employee_id，last_name,job_id,department_id信息
#方式一：
#自连接
SELECT DISTINCT mgr.employee_id,mgr.last_name,mgr.job_id,mgr.department_id
FROM employees emp JOIN  employees mgr
ON emp.manager_id = mgr.employee_id
#方式二:
#子查询
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (SELECT DISTINCT manager_id FROM employees	
)
#方式三：
#使用 EXISTS实现
SELECT employee_id,last_name,job_id,department_id
FROM employees e1
WHERE EXISTS (
	SELECT * FROM employees e2
	WHERE e1.employee_id = e2.`manager_id`
);	
#查询departments表中，不存在于employees表中部门的department_id和department_name
#方式1
SELECT d.department_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;
#方式2
SELECT department_id,department_name
FROM departments d
WHERE NOT EXISTS(
		SELECT * FROM
		employees e
		WHERE d.`department_id` = e.`department_id`
);