mysql日期函数练习

stude(s_id,s_name，s_birth)

查询各学生的年龄(周岁)

按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

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 s_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(s_birth) = month(current_date());

查询12月份过生日的学生

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