mysql-sql-第八周

学习目标：

sql

学习内容：

19.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
分析：有几种方法，分组、连接、联查等

Select s.stunm,name,avg(tscore) from students s,score sc group by s.stunm,name having avg(tscore)>60;

Select s.stunm,name,avg(tscore) from students s left join score sc on s.stunm=sc.stunm group by s.stunm,name having avg(tscore)>60;

Select stunm,avg(tscore) as ag from score group by stunm having avg(tscore)>60;

select s.stunm,s.name,ag from students s,(select stunm, avg(tscore) as ag
from score group by stunm having avg(tscore)> 60) r where s.stunm = r.stunm;

select s.stunm, s.name, r.ag
from students s right join( select stunm, avg(tscore) as ag from score group by stunm having avg(tscore)> 60) r on s.stunm = r.stunm;

select s.stunm,name,ag
from (select stunm, avg(tscore) as ag from score group by stunm having avg(tscore)> 60)r
left join (select s.stunm, s.name from
Students s)s on s.stunm = r.stunm;

20.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
分析：floor（）、datediff(）,DateDiff ()函数可用来决定两个日期之间所指定的时间间隔数目
Dloor()向下取整

select name, floor(datediff(current_date, birthday)/365) as age
from students;

21.查询各学生的年龄，只按年份来算
分析：year（）
select *, year(now())-year(birthday) as 年龄 from Students;

22.查询" 2003 “课程比” 2002 "课程成绩高的学生的信息及课程分数

先求两表交集
(selectfrom score sc where sc.counm =‘2003’)as t1 inner join (select * from score sc1 where sc1.counm=‘2002’)as t2 on t1.stunm=t2.stunm
再查询" 2003 “课程比” 2002 "课程成绩高的学生的信息及课程分数
Select t1.stunm ,t1.counm,t1.tscore from (selectfrom score sc where sc.counm =‘2003’)as t1 inner join (select * from score sc1 where sc1.counm=‘2002’)as t2 on t1.stunm=t2.stunm where t1.tscore>t2.tscore;

23.查询同时存在" 2003 “课程和” 2002 "课程的情况
Select students.stunm,name,counm,tscore from students left join score on students.stunm=score.stunm where students.stunm= any(select score.stunm from score where counm in(‘2002’,‘2003’));

24.查询存在" 2001 “课程但可能不存在” 2002 "课程的情况

先连接两表
(selectfrom score where counm=’2001’)as t1 left join (selectfrom score where counm=’2002’)as t2 on t1.stunm=t2.stunm
再查询
Selectfrom(selectfrom score where counm=‘2001’)as t1 left join (select*from score where counm=‘2002’)as t2 on t1.stunm=t2.stunm

学习时间：

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

学习产出：

一周一发