sql server 的T-SQL 学习笔记（五）

简单的查询练习，结合着时间函数运用

use studentSys
go
if exists (select * from sys.objects where name = 'studentTest') 
  drop table studentTest
go
create table studentTest(
  studentId  int  primary key identity,
  studentName varchar(20) not null,
  studentBorDay date not null,
  studentSex nchar(1) check(studentSex = '男' or studentSex = '女') not null,
  studentScore int check(studentScore >= 0 and studentScore <= 100) not null,
  studentAddress varchar(50) default ('武汉') not null 
)
select * from studentTest
-- 插入数据
insert into studentTest values('陈聪','1996-03-21','男',88,'上海')
insert into studentTest values('张三','1995-03-01','女',58,'长沙')
insert into studentTest values('李四','1993-04-23','男',48,'上海')
insert into studentTest values('小张','1992-03-12','女',76,'武汉')
insert into studentTest values('小红','1992-06-22','男',78,'北京')
insert into studentTest values('小李','1994-07-08','女',68,'武汉')
insert into studentTest values('小王','1995-08-22','男',78,'上海')
insert into studentTest values('鲁标','1996-06-21','女',88,'武汉')
insert into studentTest values('我不知道','1996-06-11','女',98,'上海')
insert into studentTest values('没名字','1993-03-26','男',86,'武汉')
insert into studentTest values('陈聪','1996-03-21','男',88,'上海')
insert into studentTest values('张三','1995-03-01','女',58,'长沙')
insert into studentTest values('李四','1993-04-23','男',48,'上海')
insert into studentTest values('小张','1992-03-12','女',76,'武汉')
insert into studentTest values('小红','1992-06-22','男',78,'北京')
insert into studentTest values('小李','1994-07-08','女',68,'武汉')
insert into studentTest values('小王','1995-08-22','男',78,'上海')
insert into studentTest values('鲁标','1996-06-21','女',88,'武汉')
insert into studentTest values('我不知道','1996-06-11','女',98,'上海')
insert into studentTest values('没名字','1993-03-26','男',86,'武汉')

-- 查询所有学生的年龄
  select studentName as '姓名' , DATEDIFF(yy,studentBorDay,GETDATE()) as '年龄' from studentTest 
-- 查询年龄最大的三个学生
  select top 3 studentName as '姓名', DATEDIFF(YY,studentBorDay,GETDATE()) as '年龄' from studentTest order by studentBorDay
-- 查出所有学生的月份，不能够出现重复的月份
  select distinct DATENAME(MM,studentBorDay) as '月份' from studentTest 
  select distinct MONTH(studentBorDay) as '月份' from studentTest
-- 查询年龄最小的三个学生
  select top 3 studentName as '姓名' , DATEDIFF(YY,studentBorDay,GETDATE()) as '年龄' from studentTest order by studentBorDay desc
-- 查询有女生出生的月份
  select distinct MONTH(studentBorDay) as '月份'from studentTest where studentSex = '女'
-- 查询女生中年龄最小的一名女生
  select top 1 studentName as '姓名',studentSex as '性别',DATEDIFF(YY,studentBorDay,GETDATE()) as '年龄'  from studentTest where studentSex = '女' order by studentBorDay desc 
-- 查找武汉的男生中年龄最大的两个
  select top 2 studentName as '姓名', studentSex as '性别' ,DATEDIFF(YY,studentBorDay ,GETDATE()) as '年龄' ,studentAddress as '地区' from studentTest where studentSex = '男' and studentAddress = '武汉' 
-- 查出所有学生名字的长度
  select studentName as '姓名' ,LEN(studentName) as '名字长度' from studentTest
--查出所有学生的姓
  select studentName  as '姓名' ,SUBSTRING(studentName,1,1) as '姓' from studentTest
-- 查出所有学生是星期几出生的
  select studentName as '姓名' ,DATENAME(DW,studentBorDay) as '星期' from studentTest 
-- 查询每个学生出生的天数
  select studentName as '姓名' ,datediff(dd,studentBorDay,GETDATE()) as '天数' from studentTest
-- 查询学生姓名和年龄 并将学生年龄和姓名组成字段
  select studentName + CAST(DATEDIFF(yy,studentBorDay,getdate()) as varchar) as '组合字段' from studentTest
  select studentName + CONVERT(varchar(20),datediff(yy,studentBorDay,getdate())) as '组合字段' from studentTest