本文记录了学习MySQL的笔记,其中课件资源全部来源于尚硅谷,详细信息请移步b站
MySQL全部笔记链接
子查询
SQL中的子查询大大增强了SELECT的查询能力,很多时候查询需要从 结果集中获取数据,或者
从同一个表总计算出一个数据结果,然后与这个数据结果进行比较
🌰
谁的工资比Abel的工资高?(不使用子查询)
#方式1:两次查询
SELECT last_name, salary
FROM employees
WHERE last_name = 'Abel'
#我们第一次查询得到了Abel的工资是11000
#那么我们就直接查询工资大于11000的员工信息就行了
SELECT last_name, salary
FROM employees
WHERE salary > 11000
#方式2:自连接
SELECT last_name, salary
FROM employees e1, employees e2
WHERE e2.salary > e1.salary
AND e1.last_name = 'Abel' #连接条件:e1中有个员工姓名为Abel
#方式3:子查询
#如同方式1类似,我们主要的查询是第二次查询,但是先前条件由第一次查询结果得到,
#可以将第二次用到的数值做一次查询
SELECT last_name, salary
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
)
子查询规范/注意事项
规范:
子查询(内查询)在主查询之前一次完成
子查询的结果被主查询使用
注意事项:
- 子查询要包含在括号内
- 将子查询放在比较条件的右侧
- 单行操作符对应子查询,多行操作符对应多行子查询
子查询的分类
角度1: 从内查询返回的结果的条目数
单行子查询 VS 多行子查询
角度2:内查询是否是被查询多次
相关子查询 VS 不相关子查询
不相关子查询需求:
上面样例就是不相关子查询,因为主查询得到了11000后,不管是谁都只是和11000进行比较
相关子查询需求:查询工资大于本部门平均工资的员工信息。
因为主查询中需要子查询的结果作为对比条件,子查询的结果是依赖于主查询的数据的,具有相关性。。
单行子查询
单行操作符: = != > >= < <=
子查询编写技巧:从里往外写、从外向里写
🌰:查询工资 大于149号员工工资的员工信息
SELECT 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 salary = 143
);
🌰:返回公司工资最少的员工的信息
SELECT last_name, job_id, salary
FROM employees
WHERE salary = (
SELECT MIN(salary)
FROM employees
);
🌰:查询与141号员工的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 #记得将141号员工去除掉
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
);
CASE中的子查询
🌰:显示员工的employee_id, last_name, locations,其中,若员工department_id与location_id为1800的部门相同则location为’Canada’,其余为’USA’
SELECT employee_id, last_name,CASE department_id WHEN() THEN 'Canada'
ELSE 'USA' END "location"
FROM employees
#将下面的语句放进上面的括号内,这里为了可读性先不放进去
SELECT department_id
FROM employees
WHERE location_id = 1800
多行子查询
多行比较操作符号
IN、ANY、ALL、SOME
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'
)
🌰:比所有工资都低,也就是比最低的工资还低
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 MIN(avg_sal)
FROM (
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
) new_table
##方式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, salary, department_id
FROM employees e1
WHERE salary > (
SELECT AVG(salary) #当前查询部门的平均工资
FROM employees e2
WHERE department_id = e1.department_id
)
#方式2:
#将各个部门的平均工资构建成为一张表
SELECT e.last_name, e.salary, e.department_id
FROM employees e, (
SELECT department_id, AVG(salary) avg_sal
FROM employees
GROUP BY department_id) table_2
WHERE e.department_id = table_2.department_id
AND e.salary > table_2.avg_sal
🌰:查询员工的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
)
结论:
在SELECT中,除了GROUP BY和LIMIT之外,其他位置都可以声明子查询
🌰:若employees表中的…id和job_history中的。。id相同的数目不小于2
输出这些相同id的员工的employee_id,last_name,job_id
#算了,好饿,吃饭去了。。。。。