SQL Server 编程基础

课上写的乱七八糟的东西

全局变量：是由SQL Server预先定义并负责维护的一类变量，主要用于保存SQL Server系统的某些参数值和性能统计数据，使用范围覆盖整个程序，用户对其只能引用而不能定义。

局部变量：是由用户根据需要定义的，使用范围只限于某一个批处理语句或者过程体内的一类变量。局部变量主要用于储存临时数据。局部变量包括标量变量、表变量等等。

• SET一般用于给变量指定常量值，而SELECT一般用于把从表中查询的数据赋值给
  变量。
• SELECT支持同时对多个变量赋值，SET只能给一个变量赋值。
• 当表达式返回多个值时，SET会出错，SELECT会将返回的最后一个值赋给变量
• 当表达式未返回值时，使用SET赋值的变量被赋NULL值，而SELECT赋值的变量保
  持原值。
• 当使用SELECT从表中查询数据赋值给变量时，如果查询结果有多条记录，它将把
  最后一条记录的值赋值给变量。
  当给一个变量复制时使用SET效率更高。

1、一条PRINT语句只能输出一个值，而一条SELECT语句可以输出多个值。
2、PRINT语句以文本形式输出，SELECT语句以表格方式输出。

week12_2

sql编程

select @@error

--第一种
declare @avgscore int
set @avgscore = (select avg(score) from score where studentid = (select studentid from student where name = '白燕') )
--第二种
select @avgscore = (select avg(score) from score where studentid = (select studentid from student where name = '白燕') )
set @avgscore = 50

if(@avgscore >= 90)
    print '优秀'
else if(@avgscore >= 80)
    print '良好'
else if(@avgscore >= 70) 
    print '中等'
else if(@avgscore >= 60) 
    print '及格'
else 
    begin
        print '不及格'
        print '!!!!!!!!'
    end

week_13_1

--select count(studentid) from score where score < 60
declare @count int
set @count = (select count(studentid) from score where score < 60)
select * from score
while(@count > 0) 
begin 
    update score set score = score + 1 where score < 100
    select @count =  count(studentid) from score where score < 60
end

while((select count(studentid) from score where score < 60) > 0) 
begin 
    update score set score = score + 1 where score < 100
end


--select * from student

select name "姓名", avg(score) "平均分", 
"评价" = 
case
    when(avg(score) > 90) then '优秀'
    when(avg(score) > 80) then '良好'
    when(avg(score) > 70) then '中等'
    when(avg(score) > 60) then '及格'
    else '不及格'
end
from score
    join student 
    on score.studentid = student.studentid
    group by name

create function comment_avgScore(@name nvarchar(50))
returns varchar(20)
as 
begin
    declare @comment varchar(20)
    select @comment = 
    case
        when(avg(score) > 90) then '优秀'
        when(avg(score) > 80) then '良好'
        when(avg(score) > 70) then '中等'
        when(avg(score) > 60) then '及格'
        else '不及格'
    end
    from score
        join student 
        on score.studentid = student.studentid
        where name = @name
return @comment
end

select dbo.comment_avgScore('金蝶') 
select dbo.comment_avgScore('凌辉') 

作业

8_1

--查询学号为G11310000000002的学生的姓名和年龄，并进一步查询比他出生年份晚一年的学生的学号和年龄。
declare @year nvarchar(4), @birthday datetime
select name, 2021-datepart(YYYY,birthday) from student where studentid = 'G11310000000002'
set @birthday = (
        select birthday from student	
            where studentid = 'G11310000000002'
    )
set @year = datepart(YYYY, @birthday)
select studentid, birthday from student
    where datepart(YY, birthday) = @year + 1
GO

8_4

--上机练习8_4
--查询课程编号为2的课程最后一次考试是否有不及格的学生，如果有，所有学生成绩加2分，
--直到所有学生都及格。因为成绩列有检查约束，成绩在0到100之间，所以要求成绩达到99分的同学不加分。
use StudentInfo
declare @num int, @testTime datetime;																		
select @testtime = max(testtime) from score where subjectid = 2
while(1 > 0)
begin
    set @num = (select count(studentid) from score where score < 60 and testtime = @testtime)
    if(@num > 0) 
    begin
        update score set score = score + 2
            where score <= 98 and subjectid = 2 and testtime = @testtime
    end
    else 
        break;
end

上机练习8_5

--上机练习8_5
--查询参加最近一次课程编号为3的课程考试的学生姓名、成绩和成绩等级，按成绩降序排列。
--如果成绩不低于90分，成绩等级为“优秀”，如果成绩在不低于80分且小于90分，成绩等级为“良好”，
--如果成绩不低于70分且小于80分，成绩等级为“中等”，
--如果成绩不低于60分且小于70分，成绩等级为“及格”，如果成绩低于60分，成绩等级为“不及格”。
use StudentInfo
declare @lasttime datetime;
set @lasttime = (select max(testtime) from score where subjectid = 3)
select name, score, 等级 = 
case
    when score >= 90 then '优秀'
    when score >= 80 and score < 90 then '良好'
    when score >= 70 and score < 80 then '中等'
    when score >= 60 and score < 70 then '及格'
    else '不及格'
end
from score s
    inner join student sd
    on s.studentid = sd.studentid
    where testtime = @lasttime and subjectid = 3
    order by score desc

上机练习8-6

上机练习8-7

1、定义一个求最近一次考试的某门课程的平均成绩的函数，要求参数是课程号。函数名称为getavgScore_bySubjectID。如图8-17所示。
2、定义一个多语句表值函数返回参加某门课程考试的学生的姓名、课程名和成绩，并按成绩升序排序。要求参数是课程名称。（这样以后查询学生考试结果只需调用函数就可以了）。

--上机练习8-7
--1、定义一个求最近一次考试的某门课程的平均成绩的函数，
--要求参数是课程号。函数名称为getavgScore_bySubjectID。如图8-17所示。
--2、定义一个多语句表值函数返回参加某门课程考试的学生的姓名、课程名和成绩，并按成绩升序排序。
--要求参数是课程名称。（这样以后查询学生考试结果只需调用函数就可以了）。
create function getavgScore_bySubjectID(@subjectid int)
returns numeric
as
begin
    declare @testtime datetime;
    declare @avg_score numeric;
    set @testtime = (select max(testtime) from score where subjectid = @subjectid)
    set @avg_score = (select avg(score) from score where subjectid = @subjectid and testtime = @testtime)
return @avg_score
end