declare @todayDate char(10),@dispStr varchar(20)
set @todayDate=getdate()
set @dispStr='今天的日期为'
select @dispStr+@todayDate
declare @学号 varchar(10),@姓名 varchar(50),@班级 varchar(50)
declare @所在系 varchar(80),@msgstr varchar(50)
--变量赋值
select @学号=Student.Student_No,@姓名=Student_Name,@班级=Class_Name,@所在系=Department_Name
from Student,Class,Department
where Student.Student_ClassNo=Class.Class_No
and Class.Class_DepartmentNo=Department.Department_No
set @msgstr='学号：'+@学号+ ' 姓名：'+@姓名+' 班级：'+@班级+' 所在系：'+@所在系
select @msgstr
go
select Student.Student_No,Student_Name,Class_Name,Department_Name
from Student,Class,Department
where Student.Student_ClassNo=Class.Class_No
and Class.Class_DepartmentNo=Department.Department_No
declare @dispstr varchar(20)
set @dispstr='这是一个局部变量引用出错的演示'
go
--批处理在这里结束,局部变量被清除
select @dispstr
go
print 'SQLserver的版本信息'
print @@version
print ''
print '服务器名称为： '+@@servername
print '所用的语言为： '+@@language
print '所用的服务为： '+@@servicename
go
begin
select * from Student
select * from Course
end
go
begin
declare @num int
set @num=3
if exists(select COUNT(selectcourse_courseno)
from SelectCourse
group by SelectCourse_StudentNo having COUNT(selectcourse_courseno)>=@num)
begin
select '选课' + cast(@num as char(2))+'门以上的学生名单'
select 姓名=Student_Name,COUNT(SelectCourse_CourseNo) 选课门数
from SelectCourse,Student
where Student.Student_No=SelectCourse.SelectCourse_StudentNo
group by Student_Name having COUNT(SelectCourse_CourseNo)>=@num
order by COUNT(SelectCourse_CourseNo)desc
end
else
print '没有选课'+cast(@num as char(2))+'门以上的学生'
end
go
declare @score numeric(4,1),@step varchar(6)
begin
select @score=SelectCourse_Score from Student,SelectCourse
where Student.Student_No=SelectCourse.SelectCourse_StudentNo and Student_Name='王大锤'
if @score>=90 and @score<=100 set @step='优'
else
if @score>=80 set @step='良'
else
if @step>=70 set @step='中'
else
if @score>=60 set @step='及格'
else set @step='不及格'
print @step
end
go
select 学号=Student_No,姓名=Student_Name,性别=case Student_Sex
when '男' then 'Man'
when '女' then 'Woman'
end
from Student
begin
declare @C_name char(20),@C_no char(5)
set @C_no='20191'
if exists(select count(*)from SelectCourse where SelectCourse_CourseNo=@C_no)
begin
set @C_name=(select distinct Course_Name from SelectCourse,Course
where SelectCourse.SelectCourse_CourseNo=Course.Course_No
and SelectCourse.SelectCourse_CourseNo=@C_no)
select'选修课程：'+@C_name+'的学生成绩单'
select 学号=Student.Student_No,姓名=Student_Name,成绩=Case
when SelectCourse_Score is null then '未考'
when SelectCourse_Score >=90 and SelectCourse_Score<=100 then '优秀'
when SelectCourse_Score>=80 then '良好'
when SelectCourse_Score>=70 then '中'
when SelectCourse_Score>=60 then '及格'
when SelectCourse_Score <60 then ' 不及格'
end
from student,selectcourse
where Student.Student_No=SelectCourse.SelectCourse_StudentNo
and SelectCourse_CourseNo=@C_no
end
else
print '没有重修'+@c_name+'课程的学生'
end
go
declare @Result INT,@i int
select @Result=1,@i=5
while @i>0
begin
set @Result=@Result*@i
set @i=@i-1
if @i>1
continue
else
begin
print '5的阶乘为：'
print @Result
break
end
end
go
declare @num int
set @num=0
while(select Class_Amount from Class where Class_No='201801')<90
begin
update Class set Class_Amount = Class_Amount+10 where Class_No='201801'
set @num=@num+1
end
select @num as 循环次数
go
declare @sum int,@count int
select @sum=0,@count=1
label_1:
select @sum=@sum+@count
select @count=@count+1
if @count<=5
goto label_1
select @sum as 总和
go
if exists(select*from Class where Class_No='201801')
return
else
insert into Class values('200803','01','02','软件工程181',90)
go
select * from Class
go
