T—SQL编程
>[1]局部变量
declare @name varchar(8)-----name为变量名,varchar为数据类型
局部变量赋值:
1. set @name = value
2. select @name = value
declare @name varchar(8)
set @name = '李文才'
select * from stuinfo where stuname = @name
declare @seat int
set @seat = stuseat from stuinfo where stuname = @name
select * from stuinfo where (stuseat = @seat+1) or (stuseat = @seat-1)
go
……………………………………………………………………………………………………………………………………
>[2]全局变量
@@error 最后一个T-SQL错误的错误号
@@identity 最后一次插入的标识列
@@language 当前使用的语言的名称
@@max_connections 可以创建的同时连接的最大数目
@@rowcount 受上一个SQL语句影响的行数
@@servername 本地服务器的名称
@@servicename 该计算机上的SQL服务的名称
@@timeticks 当前计算机上每刻度的微妙数
@@transcount 当前连接打开的事物数
@@version SQL Server的版本信息
……………………………………………………………………………………………………………………………………
>[3] if-else 条件语句
if(条件)
begin
语句1
语句2
……
end
else
……
declare @myavg float
set @myavg = avg(writtenexam) from stumarks
print '平均分'+convert(varchar(5),@myavg)
if(@myavg>70)
begin
print '本班笔试成绩优秀,前三名的成绩为:'
select top 3 * form stumarks order by writtenexam desc
end
else
begin
print '本班笔试成绩较差,后三名的成绩为:'
select top 3 * from stumarks order by writtenexam
end
……………………………………………………………………………………………………………………………………
>[4] while 循环语句
declare @n int
while(1=1)-----条件永远成立
begin
set @n = count(*) from stumarks where writtenexam<60
if(@n>0)
update stumarks set writtenexam = writtenexam+2
else
break
end
print '加分后的成绩为:'
select * from stumarks
……………………………………………………………………………………………………………………………………
>[5] case 多分支语句
case
when 条件1 then 结果1
when 条件2 then 结果2
………
else
end
select stuno, 成绩 = case
when writtenexam<60 then 'e'
when writtenexam between 60 and 69 then 'd'
when writtenexam between 70 and 79 then 'c'
when writtenexam between 80 and 89 then 'b'
else 'a'
end
from stumarks