学习目标:
sql
学习内容:
35.查有成绩的学生信息
这一题涉及到in和exists的用法,在这种小表中,两种方法的效率都差不多,但是请参考SQL查询中in和exists的区别分析
当表2的记录数量非常大的时候,选用exists比in要高效很多.
EXISTS用于检查子查询是否至少会返回一行数据,该子查询实际上并不返回任何数据,而是返回值True或False.
Select stunm,name from students s where exists(select sc.stunm from score sc where s.stunm=sc.stunm)
Select * from students s where s.stunm in (select sc.stunm from score sc)
Select distinct students.* from students,score where students.stunm=score.stunm and score.tscore is not null
Select distinct students.* from students,score where students.stunm=score.stunm
Select distinct students.* from students,score where students.stunm=score.stunm
36.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
Select s.name,c.name,tscore from students s,course c,score where tscore>70
这样不行,会重复
这样:Select s.name,c.name,tscore from score sc left join course c on sc.counm=c.counm left join students s on s.stunm=sc.stunm where tscore>70
或者这样:Select s.name,c.name,tscore from students s,course c,score sc where tscore>70
and sc.counm=c.counm and s.stunm=sc.stunm;
学习时间:
1月-3月,每天一小时左右
学习产出:
一周一发