[Mysql] YEAR函数 | MONTH函数 | WEEK函数 | DAY函数

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;