--声明局部变量,并为之赋值
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