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
);