优化MySchool第三章SQL编程

2017-01-09 19:15:08来源:作者:人点击

优化MySchool第三章SQL编程

/****** Script for SelectTopNRows command from SSMS  ******/SELECT TOP 1000 [StudentNo]      ,[LoginPwd]      ,[StudentName]      ,[Gender]      ,[GradeId]      ,[Phone]      ,[Address]      ,[Birthday]      ,[Email]      ,[MyTT]  FROM [MySchool].[dbo].[Student]    --<<局部变量>>   --字符串类型  declare @name nvarchar(32)  set @name='小小'  print @name    --数字类型  --float  numberic(18,2)  decimal(18,7)  --(1)float  declare @num  float  set @num=20.121  print @num    --(2)decimal(18,7)  declare @numm decimal(18,5)  set @numm=20.232323232  print @numm    --日期类型  declare @birthday datetime  set @birthday=2017-01-08  print @birthday    --查询(习近平)的信息  select * from Student where StudentName='习近平'    declare @xm nvarchar(32)  set @xm='习近平'  select * from Student where StudentName=@xm     --查询与之相邻的信息(按编号查询)  select * from Student where StudentNo=23316-5 or StudentNo=23316+1     declare @stuno int  set @stuno=23316  select * from Student where (StudentNo=@stuno-5) or (StudentNo=@stuno+1)         --<<全局变量>>  --<<输出语句>>  --最后一个T-SQL错误的错误号  update Student set Address='汉中' where StudentNo=23317  print '当前错误号:'+convert(varchar(5),@@error)  print @@error   --最后一次插入的标志列   print @@identity      --计算机名称   print @@servername   select @@SERVERNAME as '服务器名称'      --SQL Server版本信息   print 'SQL Server的版本信息'+@@version   select @@VERSION as 'SQL Server的版本信息'            --<<数据类型转换>>   --cast()和convert()函数   --cast(表达式 as 数据类型)   declare @cun nvarchar(32)   set @cun='小小'   print 'her is '+cast(@cun as nvarchar(32))         --convert(数据类型[(长度)],表达式[,样式])   declare @names nvarchar(32)   set @names='小轩'   print 'he is '+convert(nvarchar(32) , @names)         --<<逻辑控制语句>>   --begin - end   --语法: --begin           -- 语句或语句块           -- end   --if else(条件语句)   declare @age int   set @age=10   if(@age>=20)   begin     --满足条件     print 'yes'   end   else   begin     print 'NO'   end   select * from dbo.Subject      --课程为oop的编号  declare @stuject  int  select @stuject=SubjectId from dbo.Subject where SubjectName='oop' --平均分  declare @gge int  select @gge=AVG(StudentResult) from dbo.Result where ExamDate>=2013-08-09 and ExamDate<2013-08-10 and SubjectId=@stuject  if(@gge>80)   begin   print '优秀'   --前三名 ,从高到低排序,降序排列   select top 3 * from dbo.Result where ExamDate>=2013-08-09 and ExamDate<2013-08-10 and SubjectId=@stuject    order by StudentResult  desc   end         --上机练习一   declare @xin nvarchar(32)   set @xin='*'   print @xin   print @xin+@xin   print @xin+@xin+@xin   print @xin+@xin+@xin+@xin    print @xin+@xin+@xin+@xin+@xin        select * from Student  --上机练习二  select * from dbo.Student where StudentNo=23323     --上机练习三   --select * from dbo.Result  declare @fr int   select @fr=SubjectId from dbo.Subject where SubjectName='java'   declare @suo int  select @suo=StudentResult from dbo.Result where SubjectId=@fr  if(@suo>85) begin print '优秀' end else if(@suo>70) begin print '良好' end if(@suo>60) begin print '中等' end else begin print '差' end      --上机练习四 declare @date datetime declare @id int select @id=SubjectId from dbo.Subject where SubjectName='winfrom' select @date=MAX(ExamDate) from dbo.Result where SubjectId=@id declare @n int while(1=1) --条件永远成立 begin select @n=COUNT(*) from dbo.Result where SubjectId=@id and ExamDate=@date and StudentResult<60 if(@n>0)  --每人加两分 update Result set StudentResult=StudentResult+2 from dbo.Result where SubjectId=@id and ExamDate=@date and StudentResult<95 else break  --退出循环 end

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台