2025年[Mysql] YEAR函数 - MONTH函数 - WEEK函数 - DAY函数

科技前沿 • 2025-02-26 16:01 • 阅读 49

大家好，我是讯享网，很高兴认识大家。

YEAR函数 | MONTH函数 | WEEK函数 | DAY函数用于提取日期片段

1.YEAR函数

YEAR函数用于返回date中的年份值

语法结构

YEAR(date)

-- 2022 SELECT YEAR('2022-05-23'); SELECT YEAR('2022-05-23 15:30:00');

讯享网

2.MONTH函数

MONTH函数用于返回date中的月份值

语法结构

MONTH(date)

讯享网-- 5 SELECT MONTH('2022-05-23'); SELECT MONTH('2022-05-23 15:30:00');

3.WEEK函数

WEEK函数用于返回date中的星期数

语法结构

WEEK(date)

-- 21 SELECT WEEK('2022-05-23'); SELECT WEEK('2022-05-23 15:30:00');

4.DAY函数

DAY函数用于返回date中的日期值

讯享网

语法结构

DAY(date)

讯享网-- 23 SELECT DAY('2022-05-23'); SELECT DAY('2022-05-23 15:30:00');

5.扩展练习案例

数据导入

DROP TABLE IF EXISTS Student; CREATE TABLE Student( SId varchar(10), Sname varchar(10), Sage datetime, Ssex varchar(10) ) ENGINE = InnoDB DEFAULT CHARSET = utf8; insert into Student values('01' , '赵雷' , '1990-01-01' , '男'); insert into Student values('02' , '钱电' , '1990-12-21' , '男'); insert into Student values('03' , '孙风' , '1990-05-20' , '男'); insert into Student values('04' , '李云' , '1990-08-06' , '男'); insert into Student values('05' , '周梅' , '1991-12-01' , '女'); insert into Student values('06' , '吴兰' , '1992-03-01' , '女'); insert into Student values('07' , '郑竹' , '1989-07-01' , '女'); insert into Student values('09' , '张三' , '2017-12-20' , '女'); insert into Student values('10' , '李四' , '2017-12-25' , '女'); insert into Student values('11' , '李四' , '2017-12-30' , '女'); insert into Student values('12' , '赵六' , '2017-01-01' , '女'); insert into Student values('13' , '孙七' , '2018-01-01' , '女');

student表

案例1:查询各学生的年龄，只按年份来算

讯享网SELECT *,(YEAR(NOW()) - YEAR(Sage)) AS age FROM student;

案例2:查询本周过生日的学生

SELECT * FROM student WHERE WEEK(Sage) = WEEK(NOW());

案例3:查询下周过生日的学生

讯享网SELECT * FROM student WHERE WEEK(Sage) = WEEK(NOW())+1;

案例4:查询本月过生日的学生

SELECT * FROM student WHERE MONTH(Sage) = MONTH(NOW());

案例5:查询下月过生日的学生

讯享网SELECT * FROM student WHERE MONTH(Sage) = MONTH(NOW())+1;