5.24每日总结

学习了sqlserver

用管理器创建触发器实现如下安全性和完整性
① 定义 course 表中 credit 属性为“3”的缺省
sql
复制代码
ALTER TABLE Course ADD CONSTRAINT DF_Course_Credit DEFAULT 3 FOR Ccredit;
② 建立 course 与 sc 表间来维护参照完整性而使用的一个级联删除触发器、一个级联修改触发器和一个受限插入触发器
级联删除触发器

sql
复制代码
CREATE TRIGGER trg_CascadeDelete ON Course
FOR DELETE
AS
BEGIN
    DELETE FROM SC WHERE Cno IN (SELECT Cno FROM deleted);
END;
级联修改触发器

sql
复制代码
CREATE TRIGGER trg_CascadeUpdate ON Course
FOR UPDATE
AS
BEGIN
    IF UPDATE(Cno)
    BEGIN
        DECLARE @OldCno char(10), @NewCno char(10);
        SELECT @OldCno = Cno FROM deleted;
        SELECT @NewCno = Cno FROM inserted;
        UPDATE SC SET Cno = @NewCno WHERE Cno = @OldCno;
    END
END;
受限插入触发器

sql
复制代码
CREATE TRIGGER trg_RestrictInsert ON SC
FOR INSERT
AS
BEGIN
    IF EXISTS (SELECT * FROM inserted WHERE Cno NOT IN (SELECT Cno FROM Course))
    BEGIN
        RAISERROR('Cannot insert SC record: Course does not exist.', 16, 1);
        ROLLBACK TRANSACTION;
    END
END;
8. 对“students 数据库”编写存储过程
① 统计成绩 60 分以下的人数
sql
复制代码
CREATE PROCEDURE CountLowGrades
AS
BEGIN
    SELECT COUNT(*) AS LowGradeCount FROM SC WHERE Grade < 60;
END;
② 统计给定 cno 的课程的平均成绩，并返回平均成绩
sql
复制代码
CREATE PROCEDURE AvgGradeByCourse (@Cno char(10), @AvgGrade float OUTPUT)
AS
BEGIN
    SELECT @AvgGrade = AVG(Grade) FROM SC WHERE Cno = @Cno;
END;
③ 将 sc 表中 grade 从百分制改为等级制（5、4、3、2、1）
sql
复制代码
CREATE PROCEDURE ConvertGradesToScale
AS
BEGIN
    UPDATE SC
    SET Grade = CASE
        WHEN Grade BETWEEN 81 AND 100 THEN 5
        WHEN Grade BETWEEN 61 AND 80 THEN 4
        WHEN Grade BETWEEN 41 AND 60 THEN 3
        WHEN Grade BETWEEN 21 AND 40 THEN 2
        WHEN Grade BETWEEN 0 AND 20 THEN 1
        ELSE Grade
    END;
END;