Day 3

1. 查询各学生的年龄，通过年份来计算

**
练习时间函数之前，我们先看一下当前的时间

select s_name, s_birth, (year(now()) - year(s_birth)) as age from Student;

结果截图

1. 查询各学生的年龄，通过出⽣⽇期来计算，当前⽉⽇ < 出⽣年的⽉⽇，年龄减⼀

**

select s_name, s_birth, timestampdiff(year, s_birth, now()) as age from Student;

结果截图

1. 查询本周过生日的学生

**

select * from Student 
where week(s_birth) = week(now());

结果截图（Empty Set）

1. 查询下周过生日的学⽣

**

select * from Student
where week(s_birth) = week(now()) + 1;

结果截图

1. 查询本⽉过⽣⽇的学⽣

**

select * from Student 
where month(s_birth) = month(now());

结果截图

1. 按各科成绩进行排序，并显示排名，Score重复时保留名次空缺（难度等级：三颗星）

**

select a.s_id, a.c_id, a.s_score, count(b.s_score) + 1 as rank 
from Score a left join Score b
on a.c_id = b.c_id and a.s_score < b.s_score
group by a.s_id, a.c_id, a.s_score 
order by a.c_id, rank asc;

结果截图

这里为我们显示排名信息提供了一种新的思路，之前我们都是通过定义变量的方式来显示

1. 查询存在” 01 “课程但可能不存在” 02 “课程的情况(不存在时显示为null) （难度等级：三颗星）

**

select a.s_id, a.s_score as score1, b.s_score as score2 from 
(select s_id, s_score from Score where c_id = '01') as a
left join 
(select s_id, s_score from Score where c_id = '02') as b 
on a.s_id = b.s_id;

结果截图

1. 查询不存在” 01 “课程但存在” 02 “课程的情况

**

select * from Score 
where s_id not in (select distinct s_id from Score a where c_id = '01') 
and c_id = '02';

结果截图

1. 查询大于每门课课程平均分的学生名单（难度等级：三颗星）

**
方案一

select s.*, tmp.avg_score from Score s 
left join
(select c_id, avg(s_score) as avg_score from Score group by c_id) as tmp
on s.c_id = tmp.c_id 
where s.s_score > tmp.avg_score
order by c_id, s_id;

结果截图

方案二

select a.s_id, a.c_id, a.s_score, avg(b.s_score) as avg_score from Score a 
left join Score b on a.c_id = b.c_id 
group by a.s_id, a.c_id, a.s_score
having a.s_score > avg_score
order by c_id, s_id;

结果截图

方案一相对来说逻辑上好理解一些，方案二如果理解了前面的第16题，这种写法也就好理解了；面试的时候曾被问到过这道题目，结果没答上来，见鬼；如果使用了group by操作，那having就扮演了where的角色
**

1. 查询在同一个月份过生日的人数

**

select month(s_birth) as birthday, count(*) as num from Student
group by birthday;

结果截图