USE ScoreDB GO
--9.6 在学生成绩管理数据库ScoreDB中完成。 --(1) 在班级表Class中，分别使用列级和元组级约束保证班级人数classNum属性的取值在(0,50)之间。
--为避免误删Class表的数据，另建两表演示，演示后删除 --列级约束
CREATE TABLE Class1(
classNo char(6) PRIMARY KEY, /*班级编号*/ className varchar(30) NOT NULL, /*班级名称*/ institute varchar(30) NOT NULL, /*所属学院*/ grade smallint NOT NULL /*年级*/ DEFAULT 0,
classNum tinyint NULL /*班级人数*/ CHECK(classNum >=0 AND classNum <= 50) )
--元组级约束
CREATE TABLE Class2(
classNo char(6) PRIMARY KEY, /*班级编号*/ className varchar(30) NOT NULL, /*班级名称*/ institute varchar(30) NOT NULL, /*所属学院*/ grade smallint NOT NULL /*年级*/ DEFAULT 0,
classNum tinyint NULL, /*班级人数*/
CONSTRAINT CK_Class_classNum CHECK(classNum >=0 AND classNum <= 50) )
--删除演示表
DROP TABLE Class1 DROP TABLE Class2 GO
--(2) 在学生表Student中，限制籍贯为上海或北京的学生的年龄必须在17岁以上。 --需要使用触发器
CREATE TRIGGER Student_birthday ON Student
FOR INSERT,UPDATE AS
IF EXISTS(SELECT * FROM inserted WHERE YEAR(GETDATE())-YEAR(birthday)<17) ROLLBACK GO
--(3) 对于某门课程，保证如果没有选修其先修课程，则不能选修该课程。
CREATE TRIGGER Score_courseNo ON Score FOR INSERT AS
IF NOT EXISTS(SELECT * FROM Score WHERE courseNo=(
SELECT courseNo FROM Course WHERE priorCourse=(SELECT courseNo FROM inserted))) ROLLBACK GO
--(4) 如果在学生表中修改了学号，则自动修改成绩表中的学号。 CREATE TRIGGER Student_studentNo ON Student FOR UPDATE AS IF NOT EXISTS(SELECT * FROM inserted WHERE studentNo=(SELECT studentNo FROM deleted)) UPDATE Score
SET studentNo=(SELECT studentNo FROM inserted) WHERE studentNo=(SELECT studentNo FROM deleted) GO
--(5) 使用游标编程统计每个班的学生人数，并将统计结果存入班级表的班级人数属性中。 --不使用游标的语句很简单
--UPDATE Class SET classNum=(SELECT COUNT(*) FROM Student WHERE classNo=Class.classNo) --定义变量并赋值
DECLARE @classNo char(6)='' DECLARE @classNum int=0 --定义游标
DECLARE CURSOR_Class CURSOR FOR
SELECT classNo FROM Class --使用游标
OPEN CURSOR_Class --打开游标 FETCH CURSOR_Class INTO @classNo WHILE(@@FETCH_STATUS=0) BEGIN
SELECT @classNum=( SELECT count(*) FROM Student
WHERE classNo=@classNo )
UPDATE Class SET classNum=@classNum WHERE CURRENT OF CURSOR_Class FETCH CURSOR_Class INTO @classNo --获取下一个游标值 END
CLOSE CURSOR_Class --关闭游标
DEALLOCATE CURSOR_Class --释放游标
--9.8 在学生成绩管理数据库ScoreDB中，完成如下操作： --(1) 创建5个用户user01,user02,user03,user04,user05。 --可以采用以下A,B,C,D四种方式创建数据库用户
--A. 创建数据库用户
--下面的示例首先创建名为 user01 且具有密码的服务器登录名，然后在 ScoreDB 中创建对应的数据库用户 user01。
CREATE LOGIN user01 WITH PASSWORD = '123'; USE ScoreDB;
CREATE USER user01 FOR LOGIN user01; GO
--B. 创建具有默认架构的数据库用户
--下面的示例首先创建名为 user02 且具有密码的服务器登录名，然后创建具有默认架构 db_datareader 的对应数据库用户 user02。
CREATE LOGIN user02 WITH PASSWORD = '123'; USE ScoreDB;
CREATE USER user02 FOR LOGIN user02 WITH DEFAULT_SCHEMA = db_datareader; GO
--C. 从证书创建数据库用户
--下面的示例从证书 Teacher50 创建数据库用户 user03。 USE ScoreDB;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '123'; GO
CREATE CERTIFICATE Teacher50
WITH SUBJECT = 'Teachers', EXPIRY_DATE = '11/11/2015'; GO
CREATE USER user03 FOR CERTIFICATE Teacher50; GO
--D. 创建和使用不含登录名的用户
--以下示例创建一个数据库用户 user04，该用户不映射到 SQL Server 登录名。该示例然后向用户 user01 授予相应权限以便模拟 user04 用户。 USE ScoreDB ;
CREATE USER user04 WITHOUT LOGIN ;
GRANT IMPERSONATE ON USER::user04 TO user01 ; GO
--为了使用 user04 凭据，用户 user01 执行以下语句。 EXECUTE AS USER = 'user04' ; GO
--为了恢复到 user01 凭据，该用户执行以下语句。 REVERT ; GO
--创建和使用不含登录名的用户
CREATE USER user05 WITHOUT LOGIN; GO
--(2) 将课程表Course的所有权限授予用户 user01 和 user05，并具有转授权限的权利。 GRANT ALL ON Course TO user01, user05 WITH GRANT OPTION
--(3) 将班级表Class的查询和修改权限授予用户 user01 和 user02，不具有转授的权利，仅能对班级名称、年级和所属学院这3个属性进行操作。
GRANT SELECT,UPDATE ON Class(className,grade,institute) TO user01, user02
--(4) 用户 user01 将课程表Course的查询和删除权限授予用户user03，不具有转授的权利。
GRANT SELECT,DELETE ON Course TO user03
--(5) 用户 user02 将课程表Course的所有权限表授予用户user04，并具有转授的权利。 GRANT ALL ON Course TO user04 WITH GRANT OPTION
--(6) 用户 user04 将课程表Course的查询、删除权限授予用户 user05，并具有转授权限的权利。
GRANT SELECT,DELETE ON Course TO user05 WITH GRANT OPTION --(7) 用户 user05 将课程表Course的查询权限授予用户 user02。 GRANT SELECT ON Course TO user02
--(8) 删除用户 user05 对课程表Course的查询和删除权限。 REVOKE SELECT,DELETE ON Course FROM user05 CASCADE --(9) 删除用户 user02 的所有权限。 REVOKE ALL FROM user02
--(10) 将创建表和存储过程的权限授予用户 user02 和 user03。 GRANT CREATE TABLE,CREATE PROCEDURE TO user02, user03
--9.10 在学生成绩管理数据库ScoreDB中，编写如下的存储过程： --(1) 根据输入的班级编
号，逐行输出该班每个同学的姓名、课程名和相应成绩，并按姓名排序输出。
CREATE PROCEDURE PROC1(@cNo char(6)) AS
SELECT studentName,courseName,Score
FROM Student INNER JOIN Score ON Student.studentNo=Score.studentNo INNER JOIN Course ON Score.courseNo=Course.courseNo WHERE classNo=@cNo ORDER BY studentName GO
--EXEC PROC1 'IS0801'
