1声明变量 @age和@name
declare @age int
declare @name nvarchar(50)
--2赋值
----(1)方法1
set @age=20
set @name='祎祎'
print @age
print @name
----(2)方法2
select @age=20
select @name='商商'
select @age,@name
--3分别使用set和select 为行号变量@rcount赋值
use MySchool
declare @rcount int
--set @rcount=(select COUNT(*) from Student)
select @rcount=COUNT(*) from Student
print @rcount
--计算score表中english平均成绩,如果〉=60,查询正数前三名,否则找出倒数前三名。
declare @count int
select @count=COUNT(*) from Score
declare @sum int
select @sum=SUM(english) from Score
declare @avg int
select @avg=floor(@sum/@count)
if @avg>=60
begin
select * from Score order by english desc
end
else
begin
select * from Score order by english asc
end
--4通过while 计算1---100之间奇数的和。
declare @sums int=0
declare @i int=1
while @i<=100
begin
if @i%2<>0
begin
set @sums+=@i
end
set @i+=1
end
print @sums
--5通过while 计算1---100之间偶数的和。
declare @sumss int=0
declare @j int=1
while @j<=100
begin
if @j%2=0
begin
set @sumss+=@j
end
set @j+=1
end
print @sumss
--6使用while实现如果english不及格的人超过半数,则给每个人增加2分,循环加
--直到不及格的人数少于一半。
select * from Score
insert Score (studentId,math) values (3,76)
select * from Score where english<60
declare @countEnglish int
set @countEnglish=(select COUNT(scoreId) from Score)
declare @loster int
set @loster=(select COUNT(*) from Score where english<60)
if @loster<@countEnglish
begin
update Score set english+=2 where english<60
set @loster=(select COUNT(*) from Score where english<60)
end