第十一章

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