链接:https://pan.baidu.com/s/1zAhDUNv-yuJiWmaFLvTk4w
提取码:ttst
提取码:ttst
专栏中有mysql的导入导出,里面有教如何导入
一、子查询基础
1.1 例子
谁的工资比Abel的高
第一种方法:分开两次查
SELECT
salary
FROM
employees
WHERE
last_name = 'Abel'
SELECT
last_name,
salary
FROM
employees
WHERE
salary > 11000;
第二种方法:自连接
SELECT
e1.salary,
e1.employee_id
FROM
employees e1
JOIN employees e2 ON e1.salary > e2.salary
WHERE
e2.last_name = 'Abel'
第三种就是我们今天学习的子查询
SELECT
last_name,
salary
FROM
employees
WHERE
salary > ( SELECT salary FROM employees WHERE last_name = 'Abel' );
1.2 子查询分类
1.2.1 单行子查询 vs 多行子查询
返回结果条目只有一行的为单行子查询,返回多行的为多行子查询。
1.2.2 相关子查询 vs 不相关子查询
子查询和主查询相关联的则为相关子查询,否则为不相关子查询。
相关子查询:查询工资大于本部门平均工资的员工信息
不相关子查询:查询工资大于本公司平均工资的员工信息
二、单行子查询
查询工资大于149号员工工资的信息
SELECT
salary,
employee_id
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
ORDER BY
salary
LIMIT 1
# 子查询
SELECT
last_name,
job_id,
salary
FROM
employees
WHERE
salary = (
SELECT
MIN( salary )
FROM
employees)
三、多行子查询
操作符 | 含义 |
---|---|
IN | 列表中任意一个 |
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')
查询平均工资最低的部门id
# 老
SELECT
department_id,
AVG( salary )
FROM
employees
GROUP BY
department_id
ORDER BY
AVG( salary )
LIMIT 1
# 新
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG( salary ) = (
SELECT MIN( avg_salary )
FROM (
SELECT AVG( salary ) avg_salary, department_id
FROM employees
GROUP BY department_id
) dept_avg_sal
)
# 新二
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG( salary ) <= ALL (
SELECT AVG( salary )
FROM employees
GROUP BY department_id
)
四、相关子查询
查询员工中工资大于本部门平均工资的员工的信息
# 一
SELECT last_name,salary,department_id
FROM employees e1
WHERE salary > (
SELECT AVG(salary)
FROM employees e2
WHERE e2.department_id = e1.department_id
)
# 二
SELECT e.last_name,e.salary,e.department_id
FROM employees e JOIN (
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
) t
ON e.department_id = t.department_id
WHERE e.salary > t.avg_sal
若 employees表中employee_id与job_history表中employee_id相同的数目不小于2,输出这些相同id的员工的employee_id,last_name和其job_id
# 一、 join
SELECT
e.employee_id,
e.last_name,
e.job_id
FROM
employees e
JOIN job_history j ON e.employee_id = j.employee_id
GROUP BY
e.employee_id
HAVING
COUNT(e.employee_id) >= 2
# 二、子查询
SELECT e.employee_id,e.last_name,e.job_id
FROM employees e
WHERE 2 <= (
SELECT COUNT(employee_id)
FROM job_history j
WHERE e.employee_id = j.employee_id
)
EXISTS 与 NOT EXISTS 关键字
查询公司管理者的employee_id,last_name,job_id,department_id信息
# 一、join
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 DISTINCT manager_id
FROM employees e2
WHERE e1.employee_id = e2.manager_id
)
查询departments表中,不存在于employess表中的部门的department_id和department_name
# join
SELECT d.department_id,d.department_name ,e.employee_id
FROM departments d
LEFT JOIN employees e
ON d.department_id = e.department_id
WHERE e.employee_id IS NULL
# 子查询
SELECT department_id,department_name
FROM departments d
WHERE NOT EXISTS(
SELECT department_id
FROM employees e
WHERE d.department_id = e.department_id
)
五、小练习
#1.查询和 Zlotkey 相同部门的员工姓名和工资
#2.查询工资比公司平均工资高的员工的员工号,姓名和工资。
#3.选择工资大于所有 JOB _ ID =' SA _ MAN '的员工的工资的员工的 last _ name , job _ id , salary
#4.查询和姓名中包含字母 u 的员工在相同部门的员工的员工号和姓名
#5.査询在部门的 location _ id 为1700的部门工作的员工的员工号
#6.查询管理者是 King 的员工姓名和工资
#7.查询工资最低的员工信息: last _ name , salary
#8.查询平均工资最低的部门信息
#9.查询平均工资最低的部门信息和该部门的平均工资(相关子査询)
#10.查询平均工资最高的 job 信息
#11.查询平均工资高于公司平均工资的部门有哪些?
#12.查询出公司中所有 manager 的详细信息
#13.各个部门中最高工资中最低的那个部门的最低工资是多少?
#14.查询平均工资最高的部门的 manager 的详细信息: last _ name , department _ id , email , salary
#15.查询部门的部门号,其中不包括 job _ id 是" ST _ CLERK "的部门号