笔记来自宋红康老师课程
MySQL数据库教程天花板,mysql安装到mysql高级,强!硬!_哔哩哔哩_bilibili
子查询课程
#子查询
#1. 由一个具体的需求,引入子查询
#需求:谁的工资比Abel的高? #自连接比子查询效率高
#方式1:
SELECT salary
FROM employees
WHERE last_name = 'Abel';
SELECT last_name,salary
FROM employees
WHERE salary > 11000;
#方式2:自连接
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e2.`salary` > e1.`salary` #多表的连接条件
AND e1.last_name = 'Abel';
#方式3:子查询 #10行记录
SELECT employee_id,last_name,salary
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name='Abel'
);
#2. 称谓的规范:外查询(或主查询)、内查询(或子查询)
- 子查询的结果被主查询(外查询)使用 。
3. 子查询的分类
角度1:从内查询返回的结果的条目数
单行子查询 vs 多行子查询
角度2:内查询是否被执行多次
相关子查询 vs 不相关子查询
比如:相关子查询的需求:查询工资大于本部门平均工资的员工信息。(不同部门员工会对应不同平均工资进行比较)
不相关子查询的需求:查询工资大于本公司平均工资的员工信息。(不同员工和同一个平均工资进行比较)
#技巧:找到定语部分,即为内查询的内容。1)从内往外写:适合内查询复杂,多层嵌套的情况 2)从外往内写:适合内查询简单的情况
#4. 单行子查询
#4.1 单行操作符: = != > >= < <=
#题目:查询工资大于149号员工工资的员工的信息 #13行记录
SELECT employee_id,salary
FROM employees
WHERE salary >(
SELECT salary
FROM employees
WHERE employee_id=149
);
#题目:返回job_id与141号员工相同,salary比143号员工多的员工姓名,job_id和工资#11条记录
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
#错误操作:为啥返回King、AD_PRES、2100???
SELECT last_name,job_id,MIN(salary)
FROM employees
#正确操作:返回Olson、ST_CLERK、2100.00;此题从外往内写理解起来更舒服
SELECT last_name,job_id,salary
FROM employees
WHERE salary = (
SELECT MIN(salary)
FROM employees
);
#题目:查询与141号员工的manager_id和department_id相同的其他员工 #7行记录
#的employee_id,manager_id,department_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;
#题目:查询最低工资大于110号部门最低工资的部门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 =110
);
#自己的代码
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id=110
GROUP BY department_id
#HAVING department_id=110 #用WHERE还是Having?聚合键所对应的条件用WHERE;聚合函数用HAVING
);
#优化反思:1)内查询不需要用GROUP BY 就可找到110号部门的最低工资,外查询因为要找每个部门的最低工资才需要GROUP BY。如果分组后再设置筛选条件:区别用WHERE还是HAVING
#2)为啥要考虑部门id是否为NULL值?若题目改成50号部门,最终查询结果会出现NULL值的部门,如果不需要则可去NULL值。
#返回结果:110部门及这个部门的最小工资
SELECT department_id,MIN(salary)
FROM employees
WHERE department_id = 110;
#返回结果:12个部门id及各部门的MIN工资
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
#CASE中的子查询
#题目:显式员工的employee_id,last_name和location。
#其中,若员工department_id与location_id为1800的department_id相同,
#则location为’Canada’,其余则为’USA’。
#自己的代码;107条记录;注意 CASE WHEN THEN ELSE END 的两种表达方式
SELECT employee_id,last_name,
(CASE
WHEN department_id=(
SELECT department_id
FROM departments
WHERE location_id=1800
) THEN 'Canada'
ELSE 'USA'
END) AS location
FROM employees;
#参考答案
SELECT department_id, 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;
#4.2 子查询中的空值问题:由于内查询返回结果为空,故外查询查询结果也为空
SELECT last_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE last_name = 'Haas');
#4.3 非法使用子查询
#错误:Subquery returns more than 1 row;内查询返回12个部门各自的最低工资,为多行记录,不能用=
SELECT employee_id, last_name
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);
#5.多行子查询
#5.1 多行子查询的操作符: IN——相当于=,表示或的意思; ANY ALL SOME(同ANY)——需要和单行比较操作符一起使用
# ANY / ALL:
#题目:返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
#76条记录;比IT部门任一工资低,只要满足比IT部门最高工资9000元低即可
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary <ANY (
SELECT salary
FROM employees
WHERE job_id='IT_PROG'
)
AND job_id <>'IT_PROG';
#题目:返回其它job_id中比job_id为‘IT_PROG’部门所有工资低的员工的员工号、姓名、job_id 以及salary
#返回44条记录;比IT部门所有员工工资低,即满足比IT部门最低工资4200还要低
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary <ALL (
SELECT salary
FROM employees
WHERE job_id='IT_PROG'
)
AND job_id <>'IT_PROG';
#题目:查询平均工资最低的部门id
#MySQL中聚合函数是不能嵌套使用的。在Oracle中可以,比如 MIN(AVG(salary))。单行函数可以嵌套。
#方式1:自己的代码
SELECT department_id,AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
ORDER BY avg_salary
LIMIT 1;
#思路:类似高考数学题,根据已知条件推出多个结论,再尝试把它们组合起来解题。从内往外。
#step1.找出各个部门的平均工资AVG(salary); GROUP BY
SELECT AVG(salary)
FROM employees
GROUP BY department_id
step2:把1中的查询结果当成一张表t_dept_avg_sal,再找该表中avg_sal字段的最小值(不能嵌套聚合函数,故通过重命名将其变成字段);
#返回结果为最低平均工资
SELECT MIN(avg_sal)
FROM (
SELECT AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id
) AS t_dept_avg_sal
step3:在employees表中通过筛选条件:AVG(salary)=2中的最低平均工资,找出department_id
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
SELECT MIN(avg_sal)
FROM (
SELECT AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id
) AS t_dept_avg_sal
);
#从外往内更好理解,筛选条件为:平均工资最低:1)AVG(salary)=最低平均工资;2)AVG(salary)<=ALL平均工资
#方式2:一会AVG(salary),一会avg_sal,太复杂了???怎么直接构造包含department_id和avg_sal的新表???
#SQL对该代码的执行顺序???
SELECT department_id, AVG(salary)
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 #此内查询将平均工资重命名为字段avg_sal
) t_dept_avg_sal #把avg_sal这列数据看成一个表
);#此子查询返回结果为最低平均工资
#方式2:代码更容易;从外往内
#step1:外查询语句;AVG(salary)<=ALL平均工资
#step2:查出各部门的平均工资
#step3:2中返回的是多行记录,故用ALL/ANY等关键字。
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)<=ALL(
SELECT AVG(salary)
FROM employees
GROUP BY department_id
);
#5.3 空值问题:要判断子查询中的NULL值是否是自己想要的,不需要则要去空值。
#要查管理层之外的员工:107-18=89条记录;WHERE employee_id IN ()可查出18条记录;
#由于内查询中包含NULL值,当没有去空值的前提下去做外查询,返回结果为空。
SELECT last_name
FROM employees
WHERE employee_id NOT IN (
SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL
);
#6. 相关子查询
#回顾:查询员工中工资大于公司平均工资的员工的last_name,salary和其department_id
#6.1
SELECT last_name,salary,department_id
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
#题目:查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
#方式1:使用相关子查询 #返回38条记录
#思路:本部门平均工资要随外部查询记录变化。
#执行逻辑:
#1、employees表总共107行,比如第一次输入last_name='King',salary=24000,department_id=90的记录。
#2、内查询查出90号部门的平均工资为19333。
#3、24000>19333,故满足条件,输出last_name='King',salary=24000,department_id=90
#4、比如第2次输入last_name='Austin',salary=4800,department_id=60的记录。
#5、内查询查出60号部门的平均工资为5760.
#6、4800<5760,故不满足条件,该结果不输出。
SELECT e1.last_name,e1.salary,e1.department_id
FROM employees e1
WHERE salary>(
SELECT AVG(salary)
FROM employees e2
WHERE department_id=e1.department_id
);
#查出每个部门的平均工资
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id;
#方式2:在FROM中声明子查询
#思路:1、查出每个部门的平均工资,并将其查询结果重命名为表e2。2、原employees表与e2表进行多表内连接
SELECT e1.last_name,e1.salary,e1.department_id
FROM employees AS e1,
(SELECT department_id,AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id) AS e2
WHERE e1.department_id=e2.department_id
AND e1.salary>e2.avg_sal;
#题目:若employees表中employee_id与job_history表中employee_id相同的数目不小于2,
#输出这些相同id的员工的employee_id,last_name和其job_id
#相关子查询。 job_history表为轮岗情况,即轮岗次数大于等于2的结果要输出。 疑问:如何计数——COUNT
SELECT e.employee_id,e.last_name,e.job_id
FROM employees e
WHERE 2<=(
SELECT COUNT(*)
FROM job_history j
WHERE j.employee_id=e.employee_id
);
# 执行逻辑:
#1、输入employee_id=101,内查询返回结果为2次
#2、2次符合条件,故输出101
#3、输入employee_id=102,内查询返回结果为1次
#4、1次不符合条件,故不输出
#6.2 EXISTS 与 NOT EXISTS关键字:相关子查询
#题目:查询公司管理者的employee_id,last_name,job_id,department_id信息
#方式1:自连接
#要判断出是管理者,2张employees表,e1.manager_id=e2.employee_id 自连接 把e2看成管理者表。 返回18行记录
SELECT DISTINCT e2.employee_id,e2.last_name,e2.job_id,e2.department_id
FROM employees e1
JOIN employees e2
ON e1.manager_id=e2.employee_id;
SELECT DISTINCT e2.employee_id,e2.last_name,e2.job_id,e2.department_id
FROM employees e1,employees e2
WHERE e1.manager_id=e2.employee_id;
#返回19条记录,其中1个NULL值
SELECT DISTINCT manager_id
FROM employees;
#方式2:子查询
#正确做法:1、查出manger_id 2、employee_id IN () 子查询返回多行结果,故用IN
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (
SELECT DISTINCT manager_id
FROM employees
);
#错误操作:相关子查询???
SELECT DISTINCT e1.employee_id,e1.last_name,e1.job_id,e1.department_id
FROM employees e1
WHERE e1.employee_id IN (
SELECT employee_id
FROM employees e2
WHERE e2.employee_id=e1.manager_id
AND e1.manager_id IS NOT NULL);
#方式3:使用EXISTS ???
#e1表中的employee_id=100送进去后,与e2表中的107条记录进行比较,若正好等于e2表中的manager_id,则返回TRUE
#e2表第1行manager_id=NULL,此时返回FALSE;e2表第2行manager_id=100,此时返回TRUE
SELECT e1.employee_id,e1.last_name,e1.job_id,e1.department_id
FROM employees e1
WHERE EXISTS (
SELECT * #可以是SELECT任何字段
FROM employees e2
WHERE e2.manager_id=e1.employee_id #注意该筛选条件不同于自连接的筛选条件
);
#题目:查询departments表中,不存在于employees表中的部门的department_id和department_name
#方式1:外连接 只存在departments表中的数据 返回16条记录
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:NOT EXISTS
SELECT department_id,department_name
FROM departments d
WHERE NOT EXISTS (
SELECT *
FROM employees e
WHERE e.department_id=d.department_id
);
#题目:在employees中增加一个department_name字段,数据为员工对应的部门名称
#员工表中增加新字段department_name
# 1) ALTER TABLE employees
ADD(department_name VARCHAR2(14));
# 通过两表部门id相等的关系,把部门表里的department_name更新到员工表。相关子查询。
#2) UPDATE employees e
SET department_name = (
SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id
);
#题目:删除表employees中,其与emp_history表皆有的数据
DELETE FROM employees e1
WHERE employee_id in (
SELECT employee_id
FROM emp_history e2
WHERE e2.employee_id = e1.employee_id
);
子查询课后练习
#子查询课后练习题
#做题技巧总结
#要通过执行内查询的返回结果判断是单行还是多行子查询、相关还是非相关子查询
#1.查询和Zlotkey相同部门的员工姓名和工资
#单行子查询,不相关子查询,返回34条记录
SELECT last_name,salary,department_id
FROM employees
WHERE department_id=(
SELECT department_id
FROM employees
WHERE last_name='Zlotkey'
);
#2.查询工资比公司平均工资高的员工的员工号,姓名和工资。
#单行子查询,不相关子查询,返回51条记录
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
#多行子查询;比所有都高,即比最高值14000高。 不相关子查询。 返回3行记录。
SELECT last_name,job_id,salary
FROM employees
WHERE salary>ALL(
SELECT salary
FROM employees
WHERE job_id='SA_MAN'
);
#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
#多行子查询IN, 返回96条记录
SELECT employee_id,last_name
FROM employees
WHERE department_id IN (
SELECT DISTINCT department_id #加或不加DISTINCT不影响整体查询结果
FROM employees
WHERE last_name LIKE '%u%'
);
#5.查询在部门的location_id为1700的部门工作的员工的员工号
#多行子查询IN,不相关子查询,返回18条记录
SELECT employee_id
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location_id=1700
);
#6.查询管理者是King的员工姓名和工资
#多行子查询,不相关子查询,返回14条记录
SELECT last_name,salary
FROM employees
WHERE manager_id IN (
SELECT employee_id
FROM employees
WHERE last_name='King'
);
#7.查询工资最低的员工信息: last_name, salary
#单行子查询,不相关子查询,返回1条记录
SELECT last_name,salary
FROM employees
WHERE salary=(
SELECT MIN(salary)
FROM employees
);
#8.查询平均工资最低的部门信息(!!!重要)(把部门表里的所有部门信息都找出来)
#高级篇中会有工具分析不同方式的执行快慢
#方式1:从里往外写:把平均工资查询结果当成一个表,查找出MIN(avg_sal)——员工表的department_id——部门表的所有信息
SELECT *
FROM departments
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
SELECT MIN(avg_sal)
FROM (
SELECT AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id
) AS t_dept_avg_sal
)
);
#为何把department_id和avg_sal两个字段作为一张表时,SELECT语句查询结果部门为NULL值???聚合函数的问题吗???
SELECT department_id,AVG(salary)
FROM (
SELECT department_id,AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id
) AS t_dept_avg_sal;
#方式2:AVG(salary)<=ALL() 多行子查询,不相关子查询
SELECT *
FROM departments
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)<=ALL(
SELECT AVG(salary)
FROM employees
GROUP BY department_id
)
);
#方式3:利用 ORDER BY 和 LIMIT 把最低工资3475找出来
SELECT *
FROM departments
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal
LIMIT 1 #即使有两个相等的最低平均工资,也不影响
)
);
#方式4:多表连接:把ORDER BY 和 LIMIT 把最低工资3475、部门id两个字段作为一张表
SELECT d.*
FROM departments d ,(
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1
) t_dept_avg_sal
WHERE d.department_id=t_dept_avg_sal.department_id;
#9.查询平均工资最低的部门信息和该部门的平均工资(相关子查询)
#在8的基础上对方式1、2、3、4改进方法一:SELECT中写子查询,以方式1为例
SELECT d. *,(SELECT AVG(salary) FROM employees WHERE department_id=d.department_id) avg_sal
FROM departments d
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
SELECT MIN(avg_sal)
FROM (
SELECT AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id
) AS t_dept_avg_sal
)
);
#在8的基础上对4改进方法二
SELECT d.*,t_dept_avg_sal.avg_sal
FROM departments d ,(
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY AVG(salary)
LIMIT 1
) t_dept_avg_sal
WHERE d.department_id=t_dept_avg_sal.department_id;
#10.查询平均工资最高的 job 信息
#方式1: 把平均工资查询结果当成一个表,查出MAX(avg_sal)——员工表中的job_id——job表的所有信息
SELECT *
FROM jobs
WHERE job_id=(
SELECT job_id
FROM employees
GROUP BY job_id
HAVING AVG(salary)=(
SELECT MAX(avg_sal)
FROM(
SELECT job_id,AVG(salary) avg_sal
FROM employees
GROUP BY job_id
) t_job_avg_sal
)
);
#方式2:AVG(salary)>=ALL(平均工资)
SELECT *
FROM jobs
WHERE job_id=(
SELECT job_id
FROM employees
GROUP BY job_id
HAVING AVG(salary)>=ALL(
SELECT AVG(salary)
FROM employees
GROUP BY job_id
)
);
#方式3:利用LIMIT和GROUP BY直接找出最高的平均工资
SELECT *
FROM jobs
WHERE job_id=(
SELECT job_id
FROM employees
GROUP BY job_id
HAVING AVG(salary)=(
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY job_id
ORDER BY avg_sal DESC
LIMIT 1
)
);
#方式4:多表连接,把ORDER BY 和 LIMIT 把最高工资、job_id两个字段作为一张表
SELECT j.*
FROM jobs j,(
SELECT job_id,AVG(salary) avg_sal
FROM employees
GROUP BY job_id
ORDER BY avg_sal DESC
LIMIT 1
) t_job_avg_sal
WHERE j.job_id=t_job_avg_sal.job_id;
#11.查询平均工资高于公司平均工资的部门有哪些?
#单行子查询,不相关子查询。 让查找部门的话,NULL值通常不列示。 非聚合函数写在WHERE上执行效率更高。
SELECT department_id,AVG(salary)
FROM
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING AVG(salary)>(
SELECT AVG(salary)
FROM employees
);
#12.查询出公司中所有 manager 的详细信息
#技巧:子查询和自连接一般可以互用。用IN的一般可以改写成EXISTS,NOT IN 可改写成 NOT EXISTS。
#方式1:多行子查询;返回18行记录
SELECT *
FROM employees
WHERE employee_id IN (
SELECT DISTINCT manager_id
FROM employees
);
#方式2:自连接,内连接即交集部分
SELECT DISTINCT *
FROM (
SELECT mgr.*
FROM employees emp,employees mgr
WHERE emp.manager_id=mgr.employee_id
) t_mgr;
#方式3:使用EXISTS:相关子查询;注意与自连接连接条件的区别。 比如e1表中100号员工送进去,与e2表中的107条记录比较。
#e2表中的第1条记录不满足条件,FALSE。第2条记录满足条件,TRUE。
SELECT e1.*
FROM employees e1
WHERE EXISTS (
SELECT *
FROM employees e2
WHERE e2.manager_id=e1.employee_id
);
#13.各个部门中 最高工资中最低的那个部门的 最低工资是多少?
#举3个部门实例进行理解
#方式1:MIN(max_sal)——员工表中的department_id——该部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary)=(
SELECT MIN(max_sal)
FROM (
SELECT MAX(salary) max_sal
FROM employees
GROUP BY department_id
) t_dept_max_sal
)
);
#方式2:MAX(salary)<=ALL(),多行子查询,不相关子查询
SELECT MIN(salary)
FROM employees
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary)<=ALL(
SELECT MAX(salary)
FROM employees
GROUP BY department_id
)
);
#方式3:order by和limit
SELECT MIN(salary)
FROM employees
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary)=(
SELECT MAX(salary) max_sal
FROM employees
GROUP BY department_id
ORDER BY max_sal ASC
LIMIT 1
)
);
#方式4:多表连接,把order by和limit查询结果作为一张表
SELECT MIN(salary)
FROM employees e,(
SELECT department_id,MAX(salary) max_sal
FROM employees
GROUP BY department_id
ORDER BY max_sal ASC
LIMIT 1
) t_dept_max_sal
WHERE e.department_id=t_dept_max_sal.department_id;
#14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
#平均工资——平均工资最高——员工表中的部门——员工表中的manager_id
#方式1:
SELECT last_name,department_id,email,salary
FROM employees
WHERE employee_id=( #如果没有AND manager_id IS NOT NULL,就改成WHERE employee_id IN(),或者=ANY()
SELECT DISTINCT manager_id
FROM employees
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
SELECT MAX(avg_sal)
FROM (
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
) t_dept_avg_sal
)
)
AND manager_id IS NOT NULL
);
#方式2:AVG(salary)>=ALL()
SELECT last_name,department_id,email,salary
FROM employees
WHERE employee_id=(
SELECT DISTINCT manager_id
FROM employees
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)>=ALL(
SELECT AVG(salary)
FROM employees
GROUP BY department_id
)
)
AND manager_id IS NOT NULL
);
#方式3:order by和limit
SELECT last_name,department_id,email,salary
FROM employees
WHERE employee_id=(
SELECT DISTINCT manager_id
FROM employees
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
SELECT AVG(salary)
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1
)
)
AND manager_id IS NOT NULL
);
#方式4:多表连接
SELECT last_name,department_id,email,salary
FROM employees
WHERE employee_id=(
SELECT DISTINCT e.manager_id
FROM employees e,(
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1
) t_dept_avg_sal
WHERE e.department_id=t_dept_avg_sal.department_id
AND e.manager_id IS NOT NULL
);
#15. 查询部门的部门号,其中不包括job_id是"ST_CLERK"的部门号
#方法1:NOT IN ,注意找部门表,而不是员工表
SELECT DISTINCT department_id
FROM departments
WHERE department_id NOT IN ( #job_id="ST_CLERK"的部门只有一个,用!=也可以。
SELECT DISTINCT department_id
FROM employees
WHERE job_id="ST_CLERK"
);
#方式2:NOT IN 通常都可改写成 NOT EXISTS,把部门表里的各部门的详细信息在员工表里找出来,再限定条件job_id="ST_CLERK" 找出其部门
SELECT department_id
FROM departments d
WHERE NOT EXISTS (
SELECT *
FROM employees e
WHERE e.department_id=d.department_id
AND e.job_id="ST_CLERK"
);
#16. 选择所有没有管理者的员工的last_name
#方式1:
SELECT last_name
FROM employees
WHERE manager_id IS NULL;
#方式2:关联子查询:employees表emp进去,如果能在employees表mgr中找到emp.employee_id=mgr.manager_id
SELECT last_name
FROM employees emp
WHERE NOT EXISTS (
SELECT *
FROM employees mgr
WHERE emp.manager_id=mgr.employee_id
);
#17.查询员工号、姓名、雇用时间、工资,其中员工的管理者为 'De Haan'
#方式1:De Haan——ID,manager_id=100的可能有多个员工,用IN
SELECT employee_id, last_name,hire_date,salary
FROM employees
WHERE manager_id IN (
SELECT employee_id
FROM employees
WHERE last_name="De Haan"
);
#方式2:IN可以改写称EXISTS。emp的管理者ID正好是mgr的员工ID。
SELECT employee_id, last_name,hire_date,salary
FROM employees emp
WHERE EXISTS (
SELECT *
FROM employees mgr
WHERE emp.manager_id=mgr.employee_id
AND mgr.last_name='De Haan'
);
#18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资(相关子查询)
#方式1:相关子查询
SELECT employee_id,last_name,salary
FROM employees e1
WHERE salary > (
SELECT AVG(salary)
FROM employees e2
WHERE e2.department_id=e1.department_id
);
#方式2:FROM中声明子查询,再进行多表连接
SELECT employee_id,last_name,salary
FROM employees e1,(
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
)dept_avg_sal
WHERE e1.department_id=dept_avg_sal.department_id
AND e1.salary>dept_avg_sal.avg_sal;
#19.查询每个部门下的部门人数大于 5 的部门名称(相关子查询)
#把部门表送进去,看员工表该部门是否人数大于5
SELECT DISTINCT department_id
FROM departments d1
WHERE 5<(
SELECT COUNT(*)
FROM employees e2
WHERE d1.department_id=e2.department_id
);
#20.查询每个国家下的部门个数大于 2 的国家编号(相关子查询)
SELECT country_id
FROM locations l
WHERE 2<(
SELECT COUNT(*)
FROM departments d
WHERE l.location_id=d.location_id
);
/*
子查询的编写技巧(或步骤):① 从里往外写 ② 从外往里写
如何选择?
① 如果子查询相对较简单,建议从外往里写。一旦子查询结构较复杂,则建议从里往外写
② 如果是相关子查询的话,通常都是从外往里写。