1. 需求分析与问题解决
1.1 实际问题
#查询工资大于Able的工资
#方式1：内连接
SELECT e1.last_name,e1.salary
FROM employees e1,employees e2
WHERE e1.`salary`>e2.`salary`
AND e2.`last_name`='Abel';
#方式2：子查询
SELECT last_name,salary
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name='Abel'
);1.2 子查询的基本使用
SELECT last_name,salary
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name='Abel'
);#2.称谓的规范：外查询（或者主查询），内查询（子查询）
/*
子查询在主查询之前一次完成
子查询的结果被主查询一次使用
注意：
子查询要放在括号内
将子查询放在条件的右侧
单行函数对应单行的子查询，多行函数对应多行子查询
*/
1.3 子查询的分类
#子查询的分类
#角度1：内容查询返回的结果条目数
#单行子查询VS多行子查询
#角度2:
#相关子查询VS不相关子查询
/*
2. 单行子查询
2.1 单行比较操作符
#单行查询的操作符：> = ！= >= <=
#子查询技巧：1.由外往里写 2.由里往外写
2.2 代码示例
题目：查询工资大于149号员工工资的员工的信息
#练习：查询工资大于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号的manager_id和department_id相同的其他员工的employee_id
#方式1：
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;
#方式2：了解
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
)AND employee_id!=141;2.3 HAVING 中的子查询
#题目：查询最低工资大于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
)
AND department_id!=50;
2.4 CASE中的子查询
#题目：显式员工的employee_id,last_name和location。
# 其中，若员工departhment_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;
2.5 子查询中的空值问题
#4.2子查询的空值问题，
SELECT last_name,job_id
FROM employees
WHERE job_id=(
SELECT job_id
FROM employees
WHERE last_name='bna'
);2.5 非法使用子查询
#2.5 非法使用子查询
SELECT employee_id, last_name
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);3. 多行子查询
#5.1 多行子查询的操作符：IN ANY ALL
3.1 ANY/ALL 的区别
#题目：返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的
# 员工号、姓名、job_id 以及salar
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'
);#题目:查询平均工资最低的部门id
#MSQL中聚合函数不能嵌套，将聚合函数变成一个字段
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
SELECT MIN(avl)
FROM(
SELECT AVG(salary) avl
FROM employees
GROUP BY department_id
) avg_avl
);
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary)<=ALL(
SELECT AVG(salary) avl
FROM employees
GROUP BY department_id
);
3.3 空值问题
# 3.3 空值问
#要考虑有没有空值，特别注意
SELECT last_name
FROM employees
WHERE employee_id NOT IN (
SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL
);4. 相关子查询
4.1 相关子查询执行流程
如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次
4.2 代码示例
#练习：：查询员工中工资大于本部门平均工资的员工
# 的last_name,salary和其department_id
#方式1：使用相关子查询
SELECT e.last_name,e.salary,e.department_id
FROM employees e
WHERE e.salary > (
SELECT AVG(salary)
FROM employees d
WHERE d.`department_id`=e.department_id
) ;
#方式2：在from中声明子查询
SELECT e.last_name,e.salary,e.department_id
FROM employees e ,(
SELECT department_id,AVG(salary) avl
FROM employees
GROUP BY department_id
) d
WHERE e.`salary`> d.avl && e.`department_id`=d.department_id;
#练习：查询员工的id,salary,按照department_name 排序
SELECT employee_id,salary
FROM employees e
ORDER BY (
SELECT department_name
FROM departments
WHERE e.department_id=department_id
);在ORDER BY 中使用子查询
练习：查询员工的id,salary,按照department_name 排序
SELECT employee_id,salary
FROM employees e
ORDER BY (
SELECT department_name
FROM departments
WHERE e.department_id=department_id
);
#
/* 除了GROUP by 之外，其他地方都可以声明子查询
SELECT ..... (存在符合函数)
FROM .... (LEFT/RIGHT)JOIN ... ON 多表的连接条件
(LEFT/RIGHT)JOIN ... ON 多表的连接条件
WHERE 多表的连接条件 AND 过滤条件不包含聚合函数
GROUP BY ....
HAVING 过滤条件包含聚合函数
ORDER BY ....(ASC,DESC)
LIMIT .....;
#题目：若employees表中employee_id与job_history表中employee_id相同的
# 数目不小于2，输出这些相同
# id的员工的employee_id,last_name和其job_id
SELECT e.employee_id,e.last_name,e.job_id
FROM employees e
WHERE 2<=(
SELECT COUNT(*)
FROM job_history d
WHERE d.`employee_id`=e.`employee_id`
);
4.3 EXISTS 与 NOT EXISTS关键字
#题目：查询公司管理者的employee_id，last_name，job_id，department_id信息
#方式1：自连接
SELECT e.employee_id,e.last_name,e.job_id,e.department_id
FROM employees e,employees d
WHERE e.`manager_id`=d.`employee_id`;
#方式2：子查询
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN(
SELECT DISTINCT manager_id
FROM employees
);
#方式3：使用exists
SELECT employee_id,last_name,job_id,department_id
FROM employees e
WHERE EXISTS (
SELECT *
FROM employees d
WHERE e.`employee_id`=d.`manager_id`
);#题目：查询departments表中，
# 不存在于employees表中的部门的department_id和department_name
#方式1：
SELECT d.department_id,d.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 e.department_id=d.`department_id`
);
4.4 相关更新
题目：在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);
4.4 相关删除
题目：删除表employees中，其与emp_history表皆有的数据
DELETE FROM employees e
WHERE employee_id in
(SELECT employee_id
FROM emp_history
WHERE employee_id = e.employee_id);
课后练习题
#第09章 子查询练习题1
【题目】
SELECT * FROM employees;
SELECT * FROM locations;
SELECT * FROM jobs ;
SELECT * FROM countries;
#1.查询和Zlotkey相同部门的员工姓名和工资
SELECT employee_id,salary
FROM employees
WHERE department_id=(
SELECT department_id
FROM employees
WHERE last_name='Zlotkey'
);
#2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
SELECT employee_id,last_name,salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
#3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary > ALL (
SELECT salary
FROM employees
WHERE job_id='SA_MAN'
);
#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT employee_id,last_name,department_id
FROM employees e
WHERE last_name LIKE '%u%' && department_id IN (
SELECT department_id
FROM employees d
WHERE e.`department_id`=d.`department_id`
);
#5.查询在部门的location_id为1700的部门工作的员工的员工号
SELECT employee_id
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location_id=1700
);
#6.查询管理者是King的员工姓名和工资
SELECT e.last_name,e.salary
FROM employees e
WHERE e.`manager_id` =(
SELECT d.`employee_id`
FROM employees d
WHERE e.`manager_id`=d.`employee_id`&& d.`last_name`='King'
) && e.`last_name`!='King';
#7.查询工资最低的员工信息: last_name, salary
SELECT last_name,salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees
);
#8.查询平均工资最低的部门信息
SELECT d.department_id,d.department_name
FROM departments d,employees e
WHERE e.`department_id`=d.`department_id`
GROUP BY department_id
HAVING AVG(e.`salary`) =(
SELECT MIN(val)
FROM (
SELECT department_id ,AVG(salary) val
FROM employees
GROUP BY department_id
) md
) ;
#方法2：
SELECT *
FROM departments
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) =(
SELECT MIN(val)
FROM (
SELECT department_id ,AVG(salary) val
FROM employees
GROUP BY department_id
) md
)
) ;
#方式3：
SELECT d.*
FROM departments d,(
SELECT department_id,AVG(salary) val
FROM employees
GROUP BY department_id
ORDER BY val ASC
LIMIT 0,1
) md
WHERE d.`department_id`=md.department_id;
#9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
SELECT MIN(val)
FROM(
SELECT AVG(salary) val
FROM employees
GROUP BY department_id
) md
);
#方式2：
SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id= d.`department_id`)
FROM departments d,(
SELECT department_id,AVG(salary) val
FROM employees
GROUP BY department_id
ORDER BY val
LIMIT 0,1
)md
WHERE d.`department_id`=md.department_id ;
#10.查询平均工资最高的 job 信息
SELECT j.*
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY job_id
HAVING AVG(salary) =(
SELECT MAX(val)
FROM(
SELECT AVG(salary) val
FROM employees
GROUP BY job_id
) md
);
#11.查询平均工资高于公司平均工资的部门有哪些?
SELECT department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING AVG(salary) > (
SELECT AVG(salary)
FROM employees
);
#12.查询出公司中所有 manager 的详细信息
SELECT e.employee_id,e.last_name
FROM employees e
WHERE EXISTS (
SELECT employee_id
FROM employees d
WHERE e.employee_id=d.manager_id
);
#13.各个部门中 最高工资中最低的那个部门的 最低工资是多少?
SELECT MIN(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)=(
SELECT MIN(val)
FROM (
SELECT MAX(salary) val
FROM employees
GROUP BY department_id
) md
);
#14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
SELECT e.last_name, e.department_id,e.email, e.salary
FROM employees e
WHERE EXISTS (
SELECT d.employee_id
FROM employees d
WHERE e.`manager_id`=d.`employee_id`
)
GROUP BY department_id
HAVING AVG(salary)=(
SELECT MAX(val)
FROM(
SELECT AVG(salary) val
FROM employees
GROUP BY department_id
)md
)
;
SELECT e.last_name, e.department_id,e.email, e.salary
FROM employees e
# where e.`manager_id` is not null
WHERE EXISTS (
SELECT employee_id
FROM employees d
WHERE e.`manager_id`=d.`employee_id`
) ;
#改正：方式3：
SELECT last_name,department_id,email,salary
FROM employees
WHERE employee_id IN (
SELECT DISTINCT e.manager_id
FROM employees e,(
SELECT department_id,AVG(salary) val
FROM employees
GROUP BY department_id
ORDER BY val DESC
LIMIT 0,1
)md
WHERE e.`department_id`=md.department_id
);
#15. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号
SELECT DISTINCT department_id
FROM employees
WHERE department_id NOT IN (
SELECT department_id
FROM employees
WHERE job_id="ST_CLERK"
);
#16. 选择所有没有管理者的员工的last_name
SELECT last_name
FROM employees e
WHERE NOT EXISTS(
SELECT *
FROM employees d
WHERE e.`manager_id`=d.`employee_id`
);
#17．查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan'
SELECT employee_id,last_name,hire_date,salary
FROM employees
WHERE manager_id=(
SELECT employee_id
FROM employees
WHERE last_name='De Haan'
);
#18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询）
SELECT e.employee_id,e.last_name,e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(d.`salary`)
FROM employees d
WHERE e.`department_id`=d.`department_id`
GROUP BY department_id
);
#19.查询每个部门下的部门人数大于 5 的部门名称（相关子查询）
SELECT d.department_name
FROM departments d
WHERE 5 <= (
SELECT COUNT(*)
FROM employees e
WHERE e.`department_id`=d.`department_id`
);
#20.查询每个国家下的部门个数大于 2 的国家编号（相关子查询）
SELECT country_id
FROM locations l
WHERE 2 < (
SELECT COUNT(*)
FROM departments d
WHERE l.`location_id`=d.`location_id`
);