MySQL 关于年龄、生日的7道查询题目
创建学生信息表
create table Student(s_id varchar(20),s_name varchar(20) not null default'',s_birth varchar(20) not null default'',s_sex varchar(10) not null default'',primary key(s_id));
插入学生测试信息
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('08' , '金星' , '1990-01-20' , '女');
问题
- 查询各学生的年龄
select *,year(now())-year(s_birth) age from student;
- 查询本周过生日的学生
select s_id from student where date_format(s_birth,'%m%d') between date_format('2018-12-03', '%m%d') and date_format('2018-12-09', '%m%d');
- 查询下周过生日的学生
select s_id from student where date_format(s_birth,'%m%d') between date_format('2018-12-10', '%m%d') and date_format('2018-12-16', '%m%d');
- 查询本月过生日的学生
select * from student where month(s_birth)=month(now());
或者
select s_id from student where date_format(s_birth,'%m')=date_format(now(),'%m')；
- 查询下月过生日的学生
select * from student where month(s_birth)=month(now())+1;
或者
select s_id from student where date_format(s_birth,'%m')=date_format(now(),'%m')+1；
- 查询最近30天内过生日的同学
select * from student where dayofyear(now()) - dayofyear(s_birth) between 0 and 30;
- 查询当天过生日的学生
select * from student where month(s_birth)=month(now()) and day(s_birth)=day(now());
或者
select s_id from student where date_format(s_birth,'%m%d')=date_format(now(),'%m%d');
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！