t-sql 笔记

DECLARE @name nvarchar(32)   --声明
set @name ='AA'       --赋值
print @name          --打印

--数字类型       
--SQL 数据类型  float numberic(18,2)   decimal(18,7)
declare @shuzi decimal(18,7) 
set @shuzi =12.21
print @shuzi
--日期类型

declare @riqi datetime
set @riqi='1998-04-27'
print @riqi


select *from  Grade --查询学生信息

insert into Grade 
values ('高级')
select @@IDENTITY as 新插入的行 --查询新插入的最后一行

select @@SERVERNAME --查询计算机的版本名称

数据类型转换
--cast 和 convert 
declare @i int 
set @i=5
print '数据类型为'+cast(@i as nvarchar(32) )


declare @q int 
set @q =10
print '数据类型为'+convert(nvarchar(32), @q)
---- if else 语句
declare @wd int
set @wd=18
	if(@wd>=18)
		begin
		print '成年'
		end
	else
		 begin
		 print '不成年'
		 end
 --练习：
--统计并显示2013-08-09 的oop考试平均分
--如果平均分在70分以上，显示“考试成绩优秀”，并显示前三名学生的考试信息
--如果在以下，显示“考试成绩较差”，并显示后三名学生的考试信息


select * from Result
--求出OOP的科目编号
declare @subid int
select @subid=Subjectid from Subject
where SubjectName='oop'

--查询平均分
declare @pjf int 
select @pjf=AVG(Studentresult) from Result
where ExamDate >='2013-08-09' and ExamDate<'2013-08-10'
and @subid=Subjectid 

if(@pjf>=70)
begin
 print '成绩优秀'
 select top 3* from Result
 where ExamDate >='2013-08-09' and ExamDate<'2013-08-10'
and @subid=Subjectid 
order by StudentResult desc
end
else
begin
  print '成绩较差'
  select top 3* from Result
  where ExamDate>='2013-08-09' and ExamDate<'2013-08-10'
  and @subid=SubjectId
  order by StudentResult
end

---上机练习1
declare @qq nvarchar(32)
set @qq='★'
print @qq
print @qq+@qq
print @qq+@qq+@qq
print @qq+@qq+@qq+@qq
print @qq+@qq+@qq+@qq+@qq

declare @iq int=1
  declare @j int=1
  declare @outstring nvarchar(19)=''
 while(@iq<=10)--改变循环次数
   begin
    set @j=1
   while(@j<=@iq)
     begin
      set @outstring+='★'
     set @j+=1
    end
   set @outstring+=Char(10)
  set @iq+=1
  end
 print @outstring
 
  declare @chusheng datetime 
   select @chusheng = birthday from student WHERE studentno=23311
   declare @xingming nvarchar(32)
   select @xingming=studentname from student WHERE studentno=23311
   declare @nianling int
   SET @nianling = FLOOR(DATEDIFF(Dy,@chusheng,GETDATE())/365)
   PRINT '姓名是:'+cast(@xingming as nvarchar(32))+ '  年龄是:'+cast(@nianling as nvarchar(32))
   
   
   
   -- 上机三
   -- 1 查询课程id
   declare @id2 int 
   select @id2=subjectid from Subject WHERE subjectname='java'
   -- 2 查询该学生的成绩 学号为 23219
   declare @chengji int
   select @chengji=studentresult from result where subjectid=@id2 and studentno=23219
   print @chengji
   if(@chengji>=85)
   begin
   Print '优秀!'
   end
   else if(@chengji>=70)
   begin
   Print '良好!'
   end
   else if(@chengji>=60)
   begin
   Print '中等!'
   end
   else
   begin
   Print '差!'
   end

   
--上机2
DECLARE @NO int 

SET @NO = 23270


SELECT StudentName,FLOOR(DATEDIFF(DY, Birthday, GETDATE())/365) 

FROM student WHERE StudentNo=@NO 
DECLARE @date datetime,@year int 

SELECT @date=Birthday FROM Student WHERE StudentNo=@NO 

print @date 

SET @year = DATEPART(YY,@date) 

SELECT * FROM Student WHERE DATEPART(YY,Birthday)=@year+1 or DATEPART(YY,Birthday)=@year-1 




--****************** 7.经典while循环加分题目***************************************
--检查学生参加“oop”课最近一次考试是否有不及格（分及格）的学生。
--如有，每人加分，高于分的学生不再加分，直至所有学生这次考试成绩均及格

--code a little ,debug a little 编写一点点，调试一点点
--***********************************************************************

--01.找一个变量存储oop科目编号
declare @id int
select @id=SubjectID from Subject
where   SubjectName='oop'




--02.找到一个变量，存储最近一次考试时间
 declare @maxdate datetime
 select @maxdate= MAX(examdate) from Result
 where SubjectId= @id 




---在查询一遍结果
select * from Result
where Subjectid=@id
and  ExamDate=@maxdate

--03.投影出符合条件的人数
declare @sum int
select @sum=COUNT(StudentNo) from Result
where SubjectId=@id 
and ExamDate=@maxdate
and StudentResult<70

while(@sum>0)
begin
--每人+2分
  update Result set StudentResult+=2
  where SubjectId=@id
  and ExamDate=@maxdate
  and StudentResult<95
  
  select @sum =COUNT(studentno) from Result
  where SubjectId=@id
  and ExamDate=@maxdate
  and StudentResult<70
end

--直角三角形 两重循环
 declare @i int
 declare @j int
 declare @str nvarchar(100)
 set @i=1
 set @j=1
 set @str=''
 while(@i<=5)
 begin
   while(@j<=@i)
   begin
     set @str+='☆'  
     set @j+=1
   end
   print @str
   set @i+=1
 end


 --三角形一重循环
declare @name1 nvarchar(32)
set @name1='☆'
declare @int int
set @int=1
while(@int<=5)
begin
print @name1
set @name1+='☆'
set @int+=1
end
go

---上机练习3
--查询学号是2012 的学生 JAVA课程最近一次成绩
---1  找一个课程位 java 
declare @bui int 
select @bui=Subjectid from Subject
where SubjectName='java'

---2 找到一个变量 来存储最近一次考试时间
declare @date datetime
select @date =MAX(examdate) from Result
where  SubjectId=@bui


select a.StudentName,等级=
case 
 when b.StudentResult>90 then '☆☆☆☆'
 when b.StudentResult between 80 and 89 then '☆☆☆'
 when b.StudentResult between 70 and 79 then '☆☆'
 when b.StudentResult between 60 and 69 then '☆'
 else
  '你要努力了!!!' 
end
 from Student a,Result b
where a.StudentNo=b.StudentNo


select a.StudentName,b.StudentResult from Student a,Result b
where a.StudentNo=b.StudentNo