一、多表联查
多表联查可以通过连接运算实现,即将多张表通过主外键关系关联在一起进行查询。下图提供了多表联查 时用到的数据库表之间的关系。
1.内联查询
只有完全满足条件(主外键关系)的数据才能出现的结果
1.1 非等值联查
语法:非等值查询:SELECT * FROM 表1,表2
注意:非等值联查:笛卡尔积 逻辑上有错误,表中标红的一行数据是有错误的,班级编号为1,但是显示数据在二班
-- 非等值联查 -- 笛卡尔积 逻辑上有错误
select * from student,class;
1.2 等值联查
语法:等值查询:SELECT * FROM 表1,表2 WHERE 表1.字段1 = 表2.字段2...
-- 等值联查
-- 查询出学生和班级信息 student class
select * from student,class where student.classid=class.classid;
1.3 五张表全部联查出来
-- 5张表全部联查出来
select * from student,class,sc,course,teacher
where student.classid=class.classid and student.Sid=sc.Sid and sc.Cid=course.Cid and course.Tid=teacher.Tid;
1.4 面试题!!!
-- 查询出学生学过张三老师课程的学生信息(面试!!!)
select * from student;
select * from teacher;
select student. * from student,sc,course,teacher
where student.Sid=sc.Sid and sc.Cid=course.Cid and course.Tid=teacher.Tid and teacher.Tname='张三';
-- 查询每个学生的平均成绩 学生姓名 班级名称 平均成绩
select student.Sname ,class.classname ,avg(score) from student,sc,class
where student.Sid=sc.Sid
and student.classid=class.classid
group by student.Sid;
1.5 inner join on
select * from student
inner join class on student.classid=class.classid
inner join sc on student.Sid=sc.Sid
where ssex='男'
1.6 等值联查与inner join on 的区别
等值联查:
- 先拿出所有的结果再筛选
- 适合表的个数多,但是每个表的数据量不大,吃内存但是 IO小
inner join on:
- 通过第一张表的结果集进行on后面的结果匹配,符合条件的放到结果集里面
- 适合表少,但是每张表的数据大,内存占用小,IO高
-- 笛卡尔积
-- 先拿出所有的结果再筛选
-- 适合表的个数多,但是每个表的数据量不大,吃内存但是 IO小
select * from student,class
where student.classid=class.classid andssex='男'; -- 结果一样
-- 通过第一张表的结果集进行on后面的结果匹配,符合条件的放到结果集里面
-- 适合表少,但是每张表的数据大,内存占用小,IO高
select * from student
inner join class on student.classid=class.classid
inner join sc on student.Sid=sc.Sid
where ssex='男'
-- 3表联查
select * from student
inner join class on student.classid=class.classid
inner join sc on student.Sid=sc.Sid
-- 5表联查
select * from student
inner join class on student.classid=class.classid
inner join sc on student.Sid=sc.Sid
inner join course on student.classid=course.Cid
inner join teacher on course.Tid=teacher.Tid
-- 没门课程的平均成绩 课程名称 代课老师姓名 平均成绩
select course.Cname,teacher.Tname,avg(sc.score)from sc
inner join course on sc.Cid=course.Cid
inner join teacher on course.Tid=teacher.Tid
group by course.Cid
2.外联查询
- 找到主查表
- 以小表驱动大表
2.1 left join on 左外联 主查表在join的左边
-- 获取所有学生的数据和对象的班级信息
-- left join on 左外联 主查表在join的左边
select * from student
left join class on student.classid=class.classid
2.2 right join on 右外联 主查表在join的右边
-- right join on 右外联 主查表在join的右边
select * from class
right join student on student.classid=class.classid
查询出所有的学生都学过多少门课程 显示学生姓名 课程数
-- 查询出所有的学生都学过多少门课程 显示学生姓名 课程数
select sname ,COUNT(Cid) from student
left join sc on student.Sid=sc.Cid
group by student.Sid
select sname ,COUNT(DISTINCT Cid) from student
left join sc on student.Sid=sc.Cid
group by student.Sid
查询所有学生中的没有班级的学生信息
-- 查询所有学生中的没有班级的学生信息
select * from student
left join class on student.classid=class.classid
where class.classid is null
查询没有学生的班级
-- 查询没有学生的班级
select * from class
left join student on student.classid=class.classid
where student.Sid is null
select * from student
right join class on student.classid=class.classid
where student.Sid is null
2.3 union
注意:
- union 两个结果集的并集
- union 去除重复与distinct 一样
- 不同类型的字段是可以合并的
- 不同列数量的结果集不允许合并
- 起别名给第一个结果集才有用
查询库中的有名字的人
-- 查询库中的有名字的人
select sname 姓名,ssex,classid from student
union
select tname ,tsex 性别,temail from teacher
获取没有班级的学生和没有学生的班级的数据
-- 获取没有班级的学生和没有学生的班级的数据
select * from student
left join class on student.classid=class.classid
where class.classid is null
union
select * from student
right join class on student.classid=class.classid
where student.Sid is null
获取没有班级的学生和班级和学生都有的还要获取没有学生的班级 (全连接)
-- 获取没有班级的学生和班级和学生都有的还要获取没有学生的班级
-- 全连接
select * from student
left join class on student.classid=class.classid
union
select * from student
right join class on student.classid=class.classid
获取没有班级的学生和班级和学生都有的还要获取没有学生的班级(不去重的并集)
-- 不去重的并集
select * from student
left join class on student.classid=class.classid
union all
select * from student
right join class on student.classid=class.classid
3.子查询
子查询,又叫内部查询
3.1 where 型子查询
查询id最大的一个学生(使用排序+分页实现)
select * from student order by sid desc limit 1
查询id最大的一个学生(子查询)
- 所有的查询必须用小括号括起来
- 效率极低
select * from student
where sid=(select max(sid) from student)
查询每个班下id最大的学生(使用where子查询实现)
select * from student
left join class on student.classid=class.classid
where sid in(
select max(sid) from student group by classid
)
查询学过张三老师课程的学生
-- 查询学过张三老师课程的学生
select * from student where sid in(
select sid from sc where cid=
(
select cid from course where tid=
(select tid from teacher where tname='张三')
)
)
查询没有学过张三老师课程的学生(反向过滤*****)
-- 查询没有学过张三老师课程的学生(反向过滤*****)
select * from student where sid not in(
select sid from sc where cid=
(
select cid from course where tid=
(select tid from teacher where tname='张三')
)
)
3.2 from 子查询 查询结果将作为一张表使用
查询大于5人的班级名称和人数(不使用子查询)
-- 查询大于5人的班级名称和人数(不使用子查询)
select classname,count(*) from class
left join student on class.classid=student.classid
group by class.classid having count(*)>5
查询大于5人的班级名称和人数(使用from型子查询)
-- 查询大于5人的班级名称和人数(使用from型子查询)
select classname ,人数 from class left join
(select classid,count(*) 人数
from student group by classid)t1
on class.classid=t1.classid
where 人数>5
3.3 exists 子查询
子句有结果,父句执行 ,子句没结果 ,父句不执行
-- exists 子查询 子句有结果,父句执行 ,子句没结果 ,父句不执行
select * from teacher
where exists (select * from student where classid=10)
3.4 any(推荐新版本的any) \ some 子查询
题:查询出一班成绩比二班最低成绩高的学生
-- any(推荐新版本的any) \ some all
-- 题:查询出一班成绩比二班最低成绩高的学生
select DISTINCT student.* from sc
left join student on sc.Sid=student.Sid
where student.classid=1 and score>any(
select score from sc
left join student on sc.Sid=student.Sid
where student.classid=2)
-- some
select DISTINCT student.* from sc
left join student on sc.Sid=student.Sid
where student.classid=1 and score>some(
select score from sc
left join student on sc.Sid=student.Sid
where student.classid=2)
3.5 all 子查询
题:查询出一班成绩比二班最高成绩高的学生
-- all
-- 题:查询出一班成绩比二班最高成绩高的学生
select DISTINCT student.* from sc
left join student on sc.Sid=student.Sid
where student.classid=1 and score>all(
select score from sc
left join student on sc.Sid=student.Sid
where student.classid=2)
4.结果集的控制语句
4.1 IF(expr1,expr2,expr3)
IF(expr1,expr2,expr3)
expr1 条件
expr2 条件成立 显示数据
expr3 条件不成立 显示数据
select * from teacher
-- 1 女
-- 0 男
select tid,tname ,if(tsex=1,'女','男')tsex,tbirthday,taddress from teacher
4.2 IFNULL(expr1,expr2)
IFNULL(expr1,expr2)
expr1 字段
expr2 当字段为null写的默认值
IFNULL(expr1,expr2)
-- expr1 字段
-- expr2 当字段为null写的默认值
select sid,sname,IFNULL(birthday,'这个学生没有生日,可怜宝宝!')bir,ssex from student
4.3 case when then end (必须同时出现)
-- case when then end (必须同时出现)
select tid,tname,
case tsex
when 0 then '男'
when 1 then '女'
else '保密'
end tsex,tbirthday from teacher
select tid,tname,
case
when tsex>1 then '男'
when tsex=1 then '女'
when tsex<1 then '未知'
end tsex,tbirthday from teacher
-- 查询学生的成绩,
-- 大于80分的用B显示,
-- 大于70分的用C显示,
-- 大于60分的用D显示,
-- 小于60分的显示不及格
select score,
CASE
when score>=90 then 'A'
when score>=80 then 'B'
when score>=70 then 'C'
when score>=60 then 'D'
when score<60 then '不及格'
end from sc
select score,
CASE
when score>=60 and score<70 then 'D'
when score>=90 then 'A'
when score>=80 and score<90 then'B'
when score>=70 and score<80 then 'C'
when score<60 then '不及格'
end
from sc
(面试题!!!)行专列 列转行
统计各个分数段的人数
分数段 人数
100-90 5
90-70 10
70-60 2
不及格 3
select '100-90' 分数段 ,count(*) 人数 from sc where score <=100 and score >=90
union
select '90-70' ,count(*) from sc where score <=90 and score >=70
union
select '70-60' ,count(*) from sc where score <=70 and score >=60
union
select '不及格' ,count(*) from sc where score <60
分数段 100-90 90-70 70-60 不及格
人数 3 10 2 3 5
select '人数' 分数段,
count(case when score <=100 and score >=90 then score end) '100-90' ,
count(case when score <90 and score >70 then score end) '90-70' ,
count(case when score <70 and score >60 then score end) '70-60' ,
count(case when score <60 then score end) '不及格'
from sc
bioabioa
标签:语句,classid,多表,--,student,join,联查,class,select From: https://blog.csdn.net/m0_68041576/article/details/140613943