Mysql必练50题（第十天）

第四十六天
查询各学生的年龄(周岁)

select s_id,s_name,s_birth,if(
		month(current_date()) < month(s_birth) or
        (month(current_date()) = month(s_birth) and 
        day(current_date()) < day(s_birth)),
		year(current_date()) - year(s_birth) -1,
		year(current_date()) - year(s_birth)
        ) as age
from student;

第四十七题
查询本周过生日的学生

select * from student where 
datediff(concat(year(current_date()),date_format(s_birth,"-%m-%d")),
current_date()) between 0 and 7
or 
datediff(concat(year(current_date())+1,date_format(s_birth,"-%m-%d")),
current_date()) between 0 and 7;

第四十八题
查询下周过生日的学生

select * from student where 
datediff(concat(year(current_date()),date_format(s_birth,"-%m-%d")),
current_date()) between 7 and 14
or 
datediff(concat(year(current_date())+1,date_format(s_birth,"-%m-%d")),
current_date()) between 7 and 14;

第四十九题
查询本月过生日的学生

select * from student where month(current_date()) = month(s_birth);

第五十题
查询12月份过生日的学生

select * from student where month(s_birth)=12;