sql中的局部变量和判断

--sql中的局部变量
declare @name nvarchar(10)
declare @id int

set @name='张三'
set @id = 1

select @name
select @id


declare @name nvarchar(10),@id int
--set只能对一个变量赋值
--set @name='张三'
--set @id=1
--select 可以同时对多个变量赋值
select @name='张三',@id=1
--在查询语句中对变量赋值
select @name=sName,@id=sId from student
--select @name,@id
print @name
print @id

--全局变量  系统变量
select @@version

insert into class values(1,'123','234')

select @@error

select @@identity

select @@LANGUAGE

select @@servername

select @@rowcount



--if else
--计算平均分数并输出，如果平均分数超过60分输出成绩最高的三个学生的成绩，否则输出后三名的学生
declare @avg float
select @avg=avg(english) from score 
if(@avg >= 70)
	print '平均分数超过70'
else 
	if(@avg >= 60)
		begin	
			print '成绩最高的三个学生'
			select top 3 * from score order by english desc
		end
	else
		begin
			print '成绩最低的三个学生'
			select top 3 * from score order by english asc
		end 

--while
declare @i int
set @i = 0
while(@i < 1000000)
	begin
	    set	@i = @i + 1
		insert into score values(1,@i,@i)
	end

select * from score


--truncate table score

insert into Score (studentId,english,math) values(1,50,30)
insert into Score (studentId,english,math) values(2,40,60)
insert into Score (studentId,english,math) values(3,59,40)
insert into Score (studentId,english,math) values(4,20,25)
insert into Score (studentId,english,math) values(5,90,10)
insert into Score (studentId,english,math) values(6,20,30)
insert into Score (studentId,english,math) values(7,10,20)

--如果不及格的人超过半数(考试题出难了)，则给每个人增加2分

declare @count int,@failCount int

select @count = count(*) from score
select @failCount = count(*) from score where english < 60 

while(@failCount > @count/2)
	begin
		update score set english = english + 2
		select @failCount=count(*) from score where english < 60
	end 
update score set english = 100 where english > 100


select * from score


delete from student where sId in
(select sId from 
(select row_number() over (order by sid) as num,* from student where num between 2 and 3) 
as t)



--把所有未及格的人的成绩都加及格
declare @count int,@i int
select @count=count(*) from score where english < 60
set @i = 0
while(@count > 0)
	begin
		set @i = @i + 1
		update score set english = english + 2
		select @count=count(*) from score where english < 60
	end
update score set english = 100 where english  > 100
select * from score
select @i