sql数据库第七课-CSDN博客

--全局变量，由系统定义（两个@@符）
select @@version

--局部变量
declare @name varchar(8)
declare @seat int
--变量赋值可以使用关键字：set,select
set @name='张三'
set @seat=15
--查看变量值
select @name as 姓名,@seat as 座位号

insert into stuInfo(StuName,Age,Seat)
values ('张xx',25,10)
insert into stuInfo(StuName,Age,Seat)
values ('李xx',23,5)
--使用查询语句给变量赋值
--将表中id为的姓名赋值给@stuName
declare @stuName varchar(50)
select @stuName=StuName from StuInfo
where id=4
--打印变量值
print @stuName

select* from stuInfo

insert into stuInfo(StuName,Seat)
values ('李文才',3)
insert into stuInfo(StuName,Seat)
values ('张秋丽',4)
insert into stuInfo(StuName,Seat)
values ('李斯文',2)

declare @name varchar(50)
set @name='李文才'
declare @seat int
select @seat=seat from stuInfo where stuName=@name
select stuName,seat from stuInfo where seat=(@seat+1)
or seat=(@seat-1)

select @@language
print 'SQLServer版本：'+@@version
print '服务器名称：'+@@servername
print '当前SQLServer语言：'+@@language

--@@error:配合sql语句返回当前发生的错误号（返回值：int）
insert into stuInfo (stuname) values ('李文才')
print '当前错误号：'+convert (varchar(5),@@error)

--@@identity:返回当前插入语句的标识列
insert into stuInfo (stuname) values ('李四')
print '返回的标识列的值为：'+convert(varchar(5),@@identity)

select* from stuInfo

--if ...else
--查询出平均分
declare @average int
select @average=avg(writtenExam) from stuMark
-- 判断
if (@average>70)
begin
print '本班学生考试成绩优秀'
select top 3 * from stuMark
order by WrittenExam desc
end
else
begin
print '本班学生考试成绩较差'
select top 3 * from stuMark
order by WrittenExam
end

--循环加分
declare @n int
while(1=1)
begin
select @n=count(*)from stumark where writtenExam<60
if(@n>0)
update stumark set writtenexam=writtenexam+2
where (writtenexam+2)<=100
else
break
end

select * from stumark

--case...end
--ABCDE显示成绩
select 学号=stuID,成绩=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 stumark

select 学号=stuID,笔试成绩=writtenexam,
机试成绩=labexam,平均分=(writtenexam+labexam)/2,
成绩=case
when (writtenexam+labexam)/2<60 then '不及格'
when (writtenexam+labexam)/2 between 60 and 69 then '差'
when (writtenexam+labexam)/2 between 70 and 79 then '中'
when (writtenexam+labexam)/2 between 80 and 89 then '良'
else'优'
end
from stumark

select * from stumark
declare @labAvg INT
while(1=1)
BEGIN
update stumark
set labexam =
case
when labexam<60 then labexam+5
when labexam between 60 and 69 then labexam+3
when labexam between 70 and 79 then labexam+2
when labexam between 80 and 89 then labexam +1
else labexam
end
select @labAvg=AVG(labexam) from stumark
if @labAvg>=85
break
end
select * from stumark