第四章子查询 ① 笔记

1. 内容回顾

1.1. 课前测试
1.2. 上节内容

2. 本章重点

2.1. 子查询类型
2.2. 比较运算符中使用字查询
2.3. all字查询
2.4. any/some字查询
2.5. exists和not exists字查询

3. 具体内容

#union / union all 联合查询:需要查询多表数据垂直拼接到一起
#union :去重
#union all: 不去重
#实例:请查询所有学生的姓名性别和老师的姓名性别
create table teacher
(
	teacherId int primary key auto_increment,
	teacherName varchar(20),
	sex char(1)
)

insert into teacher
(teacherName,sex)
VALUES
('张三','男'),
('李四','女'),
('贾宝玉','男'),
('薛宝钗','女')

3.1. 联合查询

union 合并去重复两个的集合并集；并且会排重
如果是一个字段，重复直接去掉，如果是多个字段，都相同才会去重。

#查询所有老师和学生的姓名和性别
#实例:请查询所有学生的姓名性别和老师的姓名性别
#一条查询返回一个结果包含学生和老师的信息
select name,sex from studentinfo
UNION #联合查询,会自动去重复
select teacherName,sex from teacher

union all：联合但是不会去重

#查询所有老师和学生的姓名和性别:不去重
select name,sex from studentinfo
UNION all#联合查询,不会自动去重复
select teacherName,sex from teacher

union all 效率比union 高。

3.2 子查询

3.2.1 什么是子查询

子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句中的查询。数据库引擎将子查询作为虚表执行查询操作。
子查询可作为联接语句中的一个表，也可作为选择语句中的一个值。
子查询的执行依赖于嵌套查询。顺序从最内层开始，一层一层向外执行，外层的嵌套查询可以访问内层嵌套查询的结果，相比变量方式执行效率更高，子查询还可以将多表的数据组合在一起。

#子查询: 在sql中再嵌套另外一个sql。分为：简单子查询，相关子查询

#简单子查询: 子级查询能够独立运行
#查询班级AAA01的所有学生
#1.关联查询
SELECT
	*
from classinfo c
join studentInfo s #inner可以省掉
on c.classId = s.classId
where c.className = 'AAA01'
#2.简单子查询实现: 学生的班级编号等于AAA01班级编号
#子查询实现:查询AAA01的所有学生
# =用于返回值是一个
select * from studentinfo
where classId =
(
	select classId from classInfo where className = 'AAA01'
)

#in 用于返回值有多个
select * from studentinfo
where classId in
(
	select classId from classInfo where className = 'AAA01'
)

#相关子查询: 子级查询不能独立运行，要依赖主查询的数据
#查询学生成绩信息: 学生姓名,科目名称,分数
#1.三表关联
SELECT
	s.name,c.courseName,e.score
from studentInfo s
join examInfo e
on s.studentId = e.studentId
join courseInfo c
on e.courseId = c.courseId

#2.相关子查询：不能独立运行的，需要依赖外部查询的输入，查询结果返回到外部
select studentId,courseId,score from examInfo;

SELECT
	(select name from studentInfo where studentId =e.studentId ) as 姓名,
	(select courseName from courseInfo where courseId = e.courseId) as 科目,
	score
from examInfo e

3.2.2 子查询的类型

查询语句中的任何条件，值，范围，都可以使用子查询表示。
将子查询的结果集再作为一个临时表来用。
 子查询结果为多行多列(当作虚拟表来使用）
查询每门课考试最高分的学生信息

#查询每门课考试最高分的学生信息
#1. 查询每门课的最高分
#2. 通过每门课的最高分关联学生信息
#1. 查询每门课的最高分
# 对课程分组，求最高分
select
courseId,max(score) as maxScore
from examInfo
group by courseId
#2. 通过每门课的最高分关联学生信息
SELECT
s.name,e.courseId,e.score
from studentInfo s
join examInfo e
on s.studentId = e.studentId
#第一种写法
SELECT
s.name,e.courseId,e.score
from studentInfo s
join examInfo e
on s.studentId = e.studentId
join (
select
courseId,max(score) as maxScore
from examInfo
group by courseId
) as b #关联一个子查询返回的虚拟表
on e.courseId = b.courseId and e.score = b.maxScore
#写法2
SELECT
a.name,a.courseId,a.score
from (
SELECT
s.name,e.courseId,e.score
from studentInfo s
join examInfo e
on s.studentId = e.studentId
) as A
join (
select
courseId,max(score) as maxScore
from examInfo
group by courseId
) as B
on a.courseId = b.courseId and a.score = b.maxScore

 子查询结果为多行一列(可以当作多个值来用(1,2,3,4))
查询参加了某门课程（html）考试的学生信息

#查询参加了某门课程（html）考试的学生信息
select * from studentinfo
where studentId in
(
SELECT
studentId
from examinfo
where courseId = (
select courseId from courseInfo where courseName = 'html'
)
)

 子查询结果为一个值。（可以作为字段使用）
查询与张三峰在同一个班级的学生信息

#查询与张三在同一个班级的学生信息
#1.需要知道张三峰在哪个班级
select classId from studentInfo
where name = '张三峰'
#2.用班级做条件，查询其他学生
select * from studentinfo
where classId = (
select classId from studentInfo
where name = '张三峰'
)

3.2.3 比较运算符中使用子查询

如果子查询的返回值不止一个，而是一个集合时，则不能直接使用比较运算符，可以在比较
运算符和子查询之间插入ANY、SOME或ALL。其中等值关系可以用IN操作符（in关键字用于
where子句中用来判断查询的表达式是否在多个值的列表中。返回满足in列表中的满足
条件的记录）。

#创建表
create table myexam
(
examId int primary key auto_increment,
name varchar(20), #学生姓名
sex char(1), #性别
score int #分数
)
insert into myexam
(name,sex,score)
VALUES
('张三','男',89),
('李四','女',98),
('王五','男',77),
('赵六','男',89),
('孙琪','女',68),
('郭靖','女',60),
('黄蓉','男',89),
('欧阳锋','男',43),
('欧阳克','男',34)

3.2.4 all子查询

all可以与=、>、>=、<、<=、<>结合是来使⽤，分别表示等于、⼤于、⼤于等于、⼩于、⼩于等于、不等于其中的所有数据。当所有数据都满足才是true，会返回满足所有条件的数据。
只要大于其中的所有值才会被显示。

#查询比所有男生分数高的女生信息
#all , any/some 用法
#查询比所有的男生成绩高的女生信息
#其实就是比最高分高
select max(score) from myexam where sex='男'
#方式1: 子查询
select * from myexam
where sex='女'
and score>(select max(score) from myexam where sex='男')
#方式2: ALL
select * from myexam
where sex='女'
and score>all(select score from myexam where sex='男')

3.2.5 any/some子查询

any 可以与=、>、>=、<、<=、<>结合起来使⽤，分别表示等于、⼤于、⼤于等
于、⼩于、⼩于等于、不等于其中的任何⼀个数据。也就是说只要有任意一个满足就是
true。并且显示满足条件的数据。高于最小值。

#查询比某些男生成绩高的女生信息
#其实就是比男生的最低分高
select min(score) from myexam where sex='男'
#方式1:子查询
select * from myexam
where sex='女'
and score>(select min(score) from myexam where sex='男')
#方式2: any/SOME
select * from myexam
where sex='女'
and score>any(select score from myexam where sex='男')

3.2.6 使用exists和not exists子查询(常用,效率高)

where exist （⼦查询）如果该⼦查询有结果数据（⽆论什么数据，只要⼤于等于1⾏），
则就为true，否则就为false
如果内层select返回true则外层select可以返回值，否则就返回空。

#查询没有参加java考试的学生信息
#select * from examInfo
#方式1:
#1.查询哪些学生考了java
select studentId from examinfo
where courseId =
(select courseId from courseInfo where courseName = 'java')
#2.查询不在这些学生中的其他学生
select * from studentinfo
where studentId not in
(
select studentId from examinfo
where courseId =
(select courseId from courseInfo where courseName = 'java')
)
#方式2: EXISTS: 如果后面查询返回了记录则exsists结果为true
# not EXISTS:如果后面查询没有返回记录则not EXISTS 返回true
select * from studentinfo s
where not exists #当前查询的学生在成绩表中不存在java成绩
(
select * from examinfo
where courseId = 2 and studentId = s.studentId
)
#查询参加了html考试的学生信息
select * from studentInfo s
where exists #查询的当前学生在成绩表中存在html的成绩
(
select * from examinfo
where courseId = 1 and studentId = s.studentId
)
#查询参加两门考试的学生
#方式1: 多表关联，分组汇总，过滤
select
s.name,count(e.score) as 成绩个数
from studentInfo s
left join examinfo e
on s.studentId = e.studentId
group by s.name
having count(e.score) = 2
#方式2:相关子查询
select * from studentInfo s
where (
select count(*) from examInfo where studentId = s.studentId
)=2