完结篇！MySQL经典50题-第46到50题

MySQL50-12-第46-50题

本文中介绍的是第46-50题，主要的知识点：各种时间和日期函数的使用

• year()：返回年份
• date_format(now(), '%Y%m%d')：返回年月日
• dayofyear() ：一年中的第几天
• weekofyear()：一年中的第几周
• week()：一年中的第几周
• month()：返回月份
• dayofweek()：星期索引，1代表星期1
• weekday()：星期索引，0代表星期1

5个题目是：

• 查询各学生的年龄：按照出生日期来算，当前月日 < 出生年月的月日则，年龄减1
• 查询本周过生日的学生
• 查询下周过生日的学生
• 查询本月过生日的学生
• 查询下月过生日的学生

image

题目46

题目需求

查询各学生的年龄：按照出生日期来算，当前月日 < 出生年月的月日则，年龄减1

分析过程

1、我们以出生年月日中的年份来计算年龄，通过year()来计算当前年份和出生年份的差值

2、比较具体的日期和当前日期的大小，使用dayofyear()来确定每个出生日期是处在每年的哪一天；如果出生日期靠后，则说明最近这年还没有达到一岁，减去1

3、 使用case语句来进行判断

参考资料

SQL实现

自己的方法

-- 自己的方法

select *
    ,case when dayofyear(now()) >= dayofyear(s_birth) then year(now()) - year(s_birth) 
        when dayofyear(now()) < dayofyear(s_birth) then year(now()) - year(s_birth) - 1
    else 'other' end as 'age'
from Student;

image

参考方法

select
    s_name
    ,s_birth
    ,date_format(now(), '%Y') - date_format(s_birth, '%Y') - (case when date_format(now(), '%m%d') > date_format(s_birth, '%m%d') then 0 else 1 end) as age  -- 当前日期大，说明已经过生了，年龄正常；反之说明今年还没有到年龄-1
from Student;

image

如何返回年份/日期

通过date_format函数能够指定返回的数据

-- 两个方法

select year(now());
select date_format(now(), '%Y');

image image

返回具体的日期：

image

题目47

题目需求

查询本周过生日的学生

分析过程

！！！注意：我们通过week函数返回日期在年份中的所属周数

select week(now());   -- 47

1. DAYOFWEEK(date)

返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)，符合国内标准

2. WEEKDAY(date)

返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)，国外标准

SQL实现

自己的方法

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

image

参考方法

select * from Student where week(date_format(now(),'%Y%m%d')) = week(s_birth);  -- 方式1

select * from student where yearweek(s_birth) = yearweek(date_format(now(),'%Y%m%d'));   -- 方式2

题目48

题目需求

查询下周过生日的学生

分析过程

本题和上面的题目是类似的，只是需要我们在现有的日期往前推一周

SQL实现

-- 自己的方法
select *
from Student
where week(s_birth) = week(now()) + 1;   -- 往前推1周

-- 参考方法
select * from Student where week(date_format(now(),'%Y%m%d')) + 1= week(s_birth); 

image

边界问题

如果现在刚好的是今年的最后一个周，那么下周就是明年的第一个周，我们如何解决这个问题呢？？改进后的脚本：

-- 自己的方法

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

当现在刚好是第52周，那么mod函数的结果是0，则说明出生的月份刚好是明年的第一周

题目49

题目需求

查询本月过生的同学

分析过程

我们通过month()来查询每个日期所在的月份

image-20201122131328760

SQL实现

-- 自己的方法
select *
from Student
where month(s_birth) = month(now());

-- 参考方法
select * from Student where month(date_format(now(), '%Y%m%d')) = month(s_birth);

image

返回的是空值，是因为数据本身就没有在11月份出生的同学

image

题目50

题目需求

查询下月过生的同学

分析过程

和上面的题目类似，需要在现有的月份上加1

SQL实现

-- 自己的方法
select * from Student
where month(s_birth) = month(now()) + 1;  -- 推迟一个月

-- 参考方法
select * from Student
where month(date_format(now(), '%Y%m%d')) + 1= month(s_birth);

image

边界问题

假设现在是12月份，那么下个月就是明年的1月份，我们如何解决？？？将上面的代码进行改进：

select * from Student
where mod(month(now()),12)  + 1 =  month(s_birth);

image

如果现在是12月份，则mod函数的结果是0，说明生日刚好是1月份