sql 局部变量声明赋值 全局变量的应用 查询 判断语句

--声明局部变量，并为之赋值
declare @name varchar(8)
declare @no varchar(8)
declare @sex  char(2)
declare @age int
declare @address varchar(20)
set @name='feiniao'
set @no='s253002'
set @sex='男'
set @age=26
set @address='zdfgsadgbfxbvxcb cxv'

insert into stuInfo_1(stuName,stuNo,stuSex,stuAge,stuAddress)
values(@name,@no,@sex,@age,@address)

declare @aa varchar(8)
declare @bb varchar(8)
set @aa='feiniao'
select @bb=stuSeat from stuInfo_1 where stuName=@aa
print '@bb='+@bb


--查询前2条记录或前2条记录的某些列
select * from stuInfo_1
select top 2 * from stuInfo_1
select top 2 stuName,stuAddress from stuInfo_1
go
--全局变量

print @@ERROR
print '当前错误号'+convert(varchar(5),@@ERROR)--全局变量@@ERROR返回int值需要转换
select @@SERVICENAME AS '服务器吗名称'
print @@VERSION 
--if else语句
declare @avgage float
select @avgage=avg(stuAge)from stuInfo_1
print '平均年龄'+convert(varchar(4),@avgage)
if(@avgage>=26)
begin
print '年龄适中'
print 'hh'
end
else
begin
print '年龄不合适'
end
--while循环语句
while (1=1)
begin
print 'hh'
break
end
--case多分支语句
declare @a int
set @a=3
select case
when @a<0 then  '非整数'
when @a=0 then  '零'
else  '非负非零数'
end

print @@ERROR
--给已存在的表中添加新列
alter table stuInfo_1
add score numeric(4,2)

select * from stuInfo_1
update stuInfo_1 set score=60.23 where stuSeat=7
--查询
declare @age1 int
select @age1=stuAge from stuInfo_1 where stuName='娟娟'
select * from stuInfo_1 where stuAge>@age1
go

select * from stuInfo_1 where stuAge>(select stuAge from stuInfo_1 where stuName='娟娟')

select * from  stuInfo_1 where score between 50.00 and 55.20
--分数在60分以上的再加2分
declare @avgscore numeric(4,2)
select  @avgscore=avg(score) from stuInfo_1 
print @avgscore
if (@avgscore>60)
begin 
update stuInfo_1 set score =score+2
select * from stuInfo_1
end
else
begin
update stuInfo_1 set score=score-2
end
go

select count(*) as '应到人数' from stuInfo_1