SQL server函数查询

create table student(
stuid varchar(20),
stuname varchar(20),
birthday datetime,
age int,
nextbirth datetime
);

insert into student values('200911001','david','1990-08-02',null,null);
insert into student values('200911002','田中一郎','1991-05-25',null,null);
insert into student values('201011001','王小虎','1991-01-18',null,null);
insert into student values('201011002','赵强','1992-02-01',null,null);

1．查询学生表中最长姓名的长度。

 select len(stuname) from student where len(stuname)>=all(select  len(stuname) from student)

2．查询学生表中姓名最长的学生信息。

 select * from student where len(stuname)>=all(select  len(stuname) from student)

3．修改学生信息，将学号中的2009替换成09。

update student
set stuid=replace(stuid,'2009','09')

4．查询姓名为“王小虎”的学号的最末两位。

select right(stuid,2) from student where stuname='王小虎'

5．计算学生的年龄，替换学生表中的age列。

update student
set age=year(getdate())-year(birthday)

6．查询出生年是1991的学生信息。

 select *from student where year(birthday)='1991'

7．查询出生月份是1至6月的学生信息。

select *from student where month(birthday) between 1 and 6

8．查询当月有哪些学生过生日。

select * from student where month(getdate())>= month(birthday)and day(getdate())>=day(birthday)

9．计算下一次过生日的日期，替换学生表中的nextbirth列。

update student
set nextbirth= dateadd(year,ceiling(datediff(day,birthday,getdate())/365.0),birthday)

10．计算下一次过生日距离现在还有多少天。

select datediff(
day,
getdate(),
dateadd(year,ceiling(datediff(day,birthday,getdate())/365.0),
birthday)
) from student