第四章 子查询

1. 练习

#多表查询，查询班级，学生，课程，成绩；显示班级名字，学生名字，科目名字和分数
select cs.classname,s.name,c.coursename,e.score
from studentinfo s,examinfo e,courseinfo c,classinfo cs
where s.studentid = e.studentid 
			and s.classid = cs.classid 
			and e.courseid = c.courseid;

select cs.classname,s.name,c.coursename,e.score
from classinfo cs
right join studentinfo s on s.classid = cs.classid
left join examinfo e on e.studentid = s.studentid
left join courseinfo c on c.courseid = e.courseid; 
#多表分组查询，查询成绩表，统计每个学生的成绩总分并从高到低排序(显示学生姓名)
select s.name,sum(score)
from studentinfo s,examinfo e
where s.studentid = e.studentid
group by e.studentid
order by sum(score) desc;
#多表分组查询，查询成绩表，统计没有参加考试的学生(显示学生姓名)
select s.name
from studentinfo s
where s.studentid not in(
	select studentid
	from examinfo
);

select s.name
from studentinfo s
left join examinfo e
on s.studentid = e.studentid 
where e.score is null;
#多表分组查询，查询成绩表，统计每门课程的平均分，并查询平均分最低的课程
select c.coursename,avg(e.score)
from courseinfo c,examinfo e
where c.courseid = e.courseId
group by e.courseId
order by avg(e.score) asc
limit 1;

 2.自关联

自关联(树形菜单，上下级关系)

#西游取经团(公司人事结构:职员，领导)
create table xyj
(
	id int primary key, #人员编号
	name varchar(20), #人员姓名
	pid int           #上级领导编号
);

select * from xyj;

insert into xyj
(id,name,pid)
VALUES
(1,'如来',null),
(2,'菩萨',1),
(3,'唐僧',2),
(4,'孙悟空',3),
(5,'猪八戒',3),
(6,'沙和尚',3);

select * from xyj;

#请查询所有员工自己的名字和上级的名字
select a.name 自己,b.name 上级
from xyj a
left join xyj b on a.pid = b.id;

3. 查询

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。分为：简单子查询，相关子查询

3.2.2 简单子查询

#简单子查询: 子查询可以独立运行,是一个简单的嵌套语句
#查询班级是A01的学生
#关联查询
SELECT
	*
from studentInfo s
join classInfo c
on s.classId = c.classId
where c.className = 'a01';

#简单子查询
SELECT
	*
from studentInfo 
where classId  = (select classId from classInfo where className = 'a01');

3.2.3 相关子查询

#相关子查询: 内外相关，用外部查询的内容作为内部查询的输入条件，
#                    内部查询完了，再将查询结果返回给外部。
#                    相关子查询不能独立运行，需要依赖于外部查询

#查询学生的考试情况，名字、科目、成绩
#内连接查询
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;

#相关子查询实现
select 
	(select name from studentInfo where studentId = e.studentId ) 姓名,
	(select courseName from courseInfo where courseId = e.courseId) 科目,
	score
from examinfo e;

3.3 子查询的类型

        查询语句中的任何条件，值，范围，都可以使用子查询表示，将子查询的结果集再作为一个临时表来使用。

3.3.1 子查询的结果为多行多列(可以当作虚拟表来使用)

#查询每门课考试最高分的学生信息
#1. 查询每门课的最高分
#2. 通过每门课的最高分关联学生信息

#查询科目最高分
select 
	courseId , max(score) 最高分
from examInfo 
group by courseId;

#查询学生信息，关联这个最高分
select
	s.name,e.courseId,e.score
from studentInfo s
join examinfo e
on s.studentId =e.studentId
join (
	select 
	courseId , max(score) max_score
	from examInfo 
	group by courseId
) as c
on e.courseId = c.courseId and e.score = c.max_score;

3.3.2 子查询结果为多行一列(可以当作多个值来用)

#查询参加了某门课程（html）考试的学生信息 ??
select *
from studentinfo
where studentid in(
	select studentid
	from examinfo e
	join (
		select *
		from courseinfo
		where coursename = 'html'
		) as c
	on c.courseid = e.courseid
);

3.3.3 子查询结果为一个值(可以当作字段使用)

#查询与张三在同一个班级的学生信息 ??
select a.*
from studentinfo a
where classid = (
	select classId
	from studentinfo
	where name = '张三'
) and name <> '张三';
#join studentinfo b
#on a.classid = b.classid and b.name = '张三';

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

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

3.4.1 all子查询

        all可以与=，>,>=,<,<=,<>结合来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的所有数据；当所有数据都满足才是true，会返回满足所有条件的数据。

#all 用法
#查询比所有的男生成绩高的女生信息
#相当于是比男生成绩中的最高分高
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.4.2 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.4.3 使用exists和not exists子查询(常用，效率高)

        where exist (子查询) 如果该子查询有结果数据(无论什么数据，只要多于1行)，则就为true，否则就为false；如果内层select返回true，则外层select可以返回值，否则就返回空。

#查询没有参加java考试的学生信息
#方式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
);