mysql-sql-第九周

学习目标：

sql

学习内容：

25.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
分析：左连接

左连接两张表
Select s.name,c.name,sc.tscore from students s left join score sc on s.stunm=sc.stunm left join course c on sc.counm=c.counm;

显示所有
select *from students s left join score sc on s.stunm = sc.stunm left join course c on sc.counm = c.counm;

只连接一张表score，代码如下
select students.name, counm, tscore from students left join score on students.stunm = score.stunm;

26. 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
分析：左连接

select students.name, tscore from students, score, course where students.stunm = score.stunm
and course.counm = score.counm
and course.name = “数学” and tscore < 60;

参考答案1
select s.name, tscore
from students s left join score sc on s.stunm = sc.stunm
left join course c on sc.counm = c.counm
where c.name = ‘数学’ and tscore < 60

参考答案2
select students.name, score.tscore from students, score, course where students.stunm = score.stunm
and course.counm = score.counm and course.name = “数学”
and score.tscore < 60;

27.查询同时存在"2001 “课程和” 2002 "课程的情况
分析：子查询
Select * from (select stunm ,tscore from score where counm=‘2001’) a ,(select stunm ,tscore from score where counm=‘2002’) b where a.stunm=b.stunm

28.查询存在" 2001 “课程但可能不存在” 2002 "课程的情况(不存在时显示为 null )
分析：左连接或者右连接
Select *from (select * from score where counm=‘2001’) a left join (select stunm,tscore b1 from score where counm=‘2002’) b on a.stunm=b.stunm

Select *from (select * from score where counm=‘2002’) a right join (select stunm,tscore b1 from score where counm=‘2001’) b on a.stunm=b.stunm

29.查询不存在"2001 “课程但存在” 2002 "课程的情况
Select*from score where score.stunm not in(select stunm from score where score.counm=‘2001’) and score.counm=‘2002’
分析：联查

30.查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
联合查询不会显示没选课的学生
分析：联查
Select score.stunm,name,sum(counm),sum(tscore) from score left join students on score.stunm=students.stunm group by score.stunm,name

学习时间：

1月-3月，每天一小时左右

学习产出：

一周一发