文章目录
子查询
子查询概述
出现在某一子句中的select语句称为子查询
子查询功能
- 判断某一元素是否是某一个集合的成员
- 判断某一个集合是否包含另一个集合
- 测试集合是否存在重复元素, 集合是否为空等
子查询分类
根据内查询返回结果的条目数分为: 单行子查询和多行子查询(in,some,all)
根据内查询是否执行多次分为: 非相关子查询和相关子查询
编写子查询的技巧
- 从里往外写select
- 从外往里写select
单行子查询和多行子查询
单行子查询
查询工资大于149号员工工资的员工的信息
select name,salary
from employees
where salary > (
#查询李明的工资,只需要查找一次
select salary
from employees
where id = 149
);
返回job_id与141号员工相同,salary比143号员工高的员工的信息
select name,salary,job_id
from employees goal
where job_id = (
#查询141号员工的job_id
select job_id
from employees
where id = 141
) and
salary > (
#查询143号员工的工资
select salary
from employees
where id = 143
);
返回工资最少的员工的信息
select name,salary
from employees
where salary = (
#最低工资
select MIN(salary)
from employees
);
查询与141号或174号员工的manager_id和department_id相同的其他员工的信息 (成对查询)
select salary,name
from employees
where id not in (141,174) and
#成对查询
(manager_id,department_id) in (
select manager_id,department_id
from employees
where id in (141,174)
);
查询最低工资大于50号部门的最低工资的部门的信息 (查询条件中出现聚合函数)
select department_id,MIN(salary)
from employees
group by department_id #聚合函数出现在查询条件中,必须使用group by和having
having MIN(salary) > (
#50号部门的最低工资
select MIN(salary)
from employees
where department_id = 50
)
查询员工的employee_id,name,location.
其中,若department_id与location_id=1800的员工的department_id相同,则location=‘Canada’,否则为‘USA’
select employee_id,name,(case department_id
when (#查询location_id=1800的department_id
select department_id
from departments
where location_id = 1800
) then 'Canada'
else 'USA' end
) "location" #取别名location
from employees
多行子查询
多行子查询的查询结果有多行,some表示使用or连接,all表示使用and连接.在某些情况下,可以使用聚合函数MAX和MIN将多行子查询转变为单行子查询问题
返回其他job_id中比job_id为‘IT_PROG’部门全部员工工资低的员工的信息
select salary,name
from employees
where job_id != 'IT_PROG'
and salary < all(select salary
from employees
where job_id = 'IT_PROG');
查询平均工资最低的部门id (在MySQL中聚合函数不能嵌套)
#方法一:在MySQL中不能在聚合函数中直接嵌套,在Oracle中可以
select department_id
from employees
group by department_id
having AVG(salary) = (select MIN(AVG(salary))#查询最低平均工资
from employees
group by department_id)
#方法二:通过转换为一张临时表,临时表必须取别名.相当于将MIN和AVG分为两步执行
select department_id
from employees
group by department_id
having AVG(salary) = (select MIN(avg_salary)#查询最低平均工资
from (select AVG(salary) "avg_salary"
from employees
group by department_id) as dept_avg_salry)
#方法三:不需要使用中间表,某种意义上和方法二是相同的,方法二是将多行子查询转变为单行子查询
select department_id
from employees
group by department_id
having AVG(salary) <= all(select AVG(salary)
from employees
group by department_id
)
不相关子查询和相关子查询
不相关子查询
内层查询独立进行, 没有涉及任何外层查询相关信息的子查询
不相关子查询可以用等值连接来进行替换, 而相关子查询不可以
不相关子查询需求: 查询工资高于李明的员工姓名
select name,salary
from employee
where salary > (
#查询李明的工资,只需要查找一次
select salary
from employee
where name = '李明'
);
相关子查询
每执行一次外查询,都要重新计算一次内查询,称为相关子查询
内查询会使用到外查询中的变量,在内查询的查询条件中使用外层变量说明查询和外层变量相关的信息
查询工资大于本部门平均工资的员工姓名
#方式一
select salary,name
from employee goal #goal为目标员工
where salary >(
#查询该员工所在部门的平均工资,根据员工部门的不同,该内查询需要执行多次
select avg(salary)
from employee e #e和goal在同一张表中
where e.department = goal.department #使用连接条件限制内查询的对象和goal员工是同一部门
);
#方式二:很多时候需要查询的数据并没有存在于现成的表中,需要使用子查询得到一张表
select salary,name
from employees,(select department_id,AVG(salary) avg_salary
from employees
group by department_id
) as dept_avg_salary
where employees.department_id = dept_avg_salary.department_id
and employees.salary > avg_salary;
查询员工的id,salary,按照department表中的department_name进行排序
select id,salary
from employees
order by (select department_name
from departments
where employees.department_id = departments.department_id) asc;
若employees表中的employee_id与job_history表中的employee_id相同的数目不小于2,则输出这些相同id的员工的employee_id,last_name和job_id
#方法一:有局限的写法,只适用于>或>=的情况,不适合<或<=的情况
select employee_id,last_name,job_id
from employees
where employee_id in (#job_history表中数目不小于2的employee_id
select employee_id
from job_history
group by employee_id
having count(*) >= 2);#这种写法在count(*) <= 2的时候不适用,job_history中可能有些员工一次都没有出现
#方法二:通用
select employee_id,last_name,job_id
from employees
where 2 <= (select count(*)
from job_history
where employees.employee_id = job_history.employee_id);
EXISTS和NOT EXISTS
EXISTS也是相关子查询的一种
默认情况为特称肯定查询,即存在某个元组符合要求即可
NOT EXISTS和NOT IN在某种程度上可以相互转化
IN子查询
语法: <表达式> [not] in (子查询)
判断表达式的值是否在子查询的结果当中
注意: where <列名> in (select <列名> from …) 两处的列名应该是对应的
#列出张三, 王三同学的所有信息
select *
from student
where sname in ('张三', '王三');
#列出选修了001号课程的学生的学号和姓名
select Sno, Sname
from student
where Sno in (
select Sno
from sc
where Cno = '001'
);
select Sno,Sname
from student
where exists(select * #in子查询转化为exists子查询
from sc
where student.Sno = sc.Sno
and sc.Cno = '001');
#既学过001号课程, 又学过002号课程的学生的学号
select Sno
from sc
where Cno = '001'
and Sno in (
select Sno
from sc
where Cno = '002'
);
#列出没学过李明老师讲授课程的所有学生的姓名
select Sname
from student
where Sno not in (#学过李明老师讲授课程的学生学号集合
select Sno
from sc
where Cno in (#李明老师讲授课程的课程号集合
select Cno
from course
where Tno in (#李明老师集合
select Tno
from teacher
where Tname = '李明'
)
)
);
#替换子查询
select Sname
from student
where Sno not in (
select Sno
from sc,
course,
teacher
where teacher.Tname = '李明'
and sc.Cno = course.Cno
and teacher.Tno = course.Tno
);
some/all子查询
语法
- 表达式 θ \theta θ some(子查询)存在量词some
- 表达式 θ \theta θ all(子查询)全称量词all
-
θ
\theta
θ表示比较运算符,
>,<,=,<> - <表达式>=some(子查询) 等价于 <表达式> in (子查询)
- <表达式> != all(子查询) 等价于 <表达式> not in (子查询)
#找出工资最低的教师姓名
select Tname
from teacher
where Salary <= all(
select Salary
from teacher
);
#找出所有课程都不及格的学生学号(相关子查询)
select Sno
from sc x
where 60 > all (
select Score
from sc
where x.Sno = Sno
);
#找出所有课程都不及格的学生姓名(相关子查询)
select Sname
from student,
sc
where student.Sno = sc.Sno
and 60 > all (
select Score
from sc
where Sno = student.Sno #where子句保证内外层查询中是针对同一个对象
);
select Sname
from student
where Sno in (
select Sno
from sc
where 60 > all (
select Score
from sc
where Sno = student.Sno
)
);
#找出001号课程成绩最高的所有学生的学号
select Sno, Score
from sc x
where Cno = '001'
and Score >= all (
select Score
from sc
where Cno = x.Cno
);
#找出98030101号同学的成绩最低的课程号
select Cno
from sc x
where Sno = '98030101'
and Score <= all (
select Score
from sc
where Sno = x.Sno
);
#找出张三同学成绩最低的课程号
select Cno
from sc,
student
where sc.Sno = student.Sno
and Sname = '张三'
and Score <= all (
select Score
from sc
where Sno = student.Sno #保证查询的是张三, 外层sc不取别名的话会被屏蔽
);
#工资小于任意一个其他教师的教师姓名
select Tname
from teacher
where Salary <= all (
select Salary
from teacher
);
(NOT) EXISTS子查询
语法:[not] Exists(子查询)
一般使用都是not exists子查询, 不加not的exists子查询并没有给现有的功能添砖加瓦
#检索选修了张三老师主讲课程的所有同学的姓名
select distinct Sname
from student,
sc
where student.Sno = sc.Sno
and Cno in (#张三老师的主讲课程号
select course.Cno
from sc,
course,
teacher
where teacher.Tno = course.Tno
and course.Cno = sc.Cno
and teacher.Tname = '张三'
);
select distinct Sname
from student
where exists(
select *
from sc,
course,
teacher
where sc.Cno = course.Cno
and sc.Sno = student.Sno
and course.Tno = teacher.Tno
and Tname = '张三'
);
#不加not的exist实际上没什么卵用, 可以被等价替换掉
select Sname
from student,
sc,
teacher,
course
where student.Sno = sc.Sno
and sc.Cno = course.Cno
and course.Tno = teacher.Tno
and teacher.Tname = '张三';
重点理解: NOT EXISTS
适用场景: 表达全称肯定,全称否定或特称否定的含义
- 学过某[几]人全部课程的学生集合
not exists + not exists
不存在一门课程, 某人教过而学生没学过 - 没学过某[几]人任意一门课程的学生集合
not exist
不存在一门课程, 某人教过且学生学过
#检索学过001号教师主讲的所有课程的所有同学的姓名
# (原本语义) 选出这样的学生: 学过001号教师主讲的所有课程
# (等价语义/双重否定) 选出这样的学生: 不存在一门课程,001号教师主讲,而该同学没有学
select distinct Sname
from student
where not exists(#001教师主讲而该学生并没有学的课程
select *
from course
where Tno = '001'
and not exists(#该学生学过的课程
select *
from sc
where student.Sno = sc.Sno
and sc.Cno = course.Cno
)
);
#列出没学过李明老师讲授任何一门课程的所有同学的姓名
select distinct Sname,Sno#选出满足要求的学生
from student
where not exists(#不存在
select * #一门课程, 既由李明老师主讲, 又被满足要求的学生选择
from sc,
course,
teacher
where course.Cno = sc.Cno
and sc.Sno = student.Sno
and course.Tno = teacher.Tno
and teacher.Tname = '张三'
);
#列出至少学过98030101号同学学过的所有课程的同学学号
select distinct Sno #满足题意的同学x
from sc x
where not exists(#不存在
select * #一门课程, 98030101号同学学过, 而x没学过
from sc y
where y.Sno = '98030101'
and not exists(#不存在 | 没
select *#学过y学的课程
from sc
where sc.Sno = x.Sno #当前sc为x同学学号
and sc.Cno = y.Cno #当前sc学过y学过的课程
)
);
# 已知SPJ(Sno, Pno, Jno, Qty), 其中Sno供应商号, Pno零件号, Jno工程号, Qty数量
#列出至少用了供应商S1供应的全部零件的工程号
select distinct Jno #符合要求的工程
from SPJ x
where not exists(#不存在
select * #S1供应商的零件,且工程x不使用
from SPJ y
where Sno = 'S1'
and not exists(#不存在 | 没
select * #
from SPJ
where Jno = x.Jno #保证该层查询是x工程
and Pno = y.Pno #保证该层查询是y中的零件
)
);
#注:最内层where子句中, 恰好工程和零件是题干中的两个宾语. 其他是否也适用
全称命题与存在命题的转化
全 称 命 题 : : = ∀ x , P ( x ) ⟹ 存 在 命 题 : : = ¬ ( ∃ x , ¬ ( P ( x ) ) 全称命题::=\forall x, P(x) \Longrightarrow 存在命题::=\neg (\exist x, \neg(P(x)) 全称命题::=∀x,P(x)⟹存在命题::=¬(∃x,¬(P(x))
-
对所有的课程, 该同学都学过
等价命题: 不存在有一门课程, 该同学没学过
-
存在一门课程, 该同学没学过
等价命题: 不是所有的课程, 该同学都学过
分组计算和聚集计算
where子句是针对每一个元组进行过滤
having子句针对每一个分组进行过滤, 即分组过滤子句having
#求不及格课程两门及以上的同学的学号
select Sno
from sc
where Score < 60
group by Sno
having count(*) >= 2;
#求有10人及以上不及格的课程号
select Cno
from sc
where Score < 60
group by Cno
having count(*) >= 10;
#求有两门及以上不及格课程的同学的学号和该同学全课程的平均成绩
select Sno, avg(Score)
from sc
where Sno in (
select Sno
from sc
where Score < 60
group by Sno
having count(*) >= 2
);
#错解
select Sno, avg(Score)#这里计算的是该同学这两门不及格课程的平均成绩
from sc
where Score < 60
group by Sno
having count(*) >= 2;
478
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
