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'
--(2) 根据输入的课程编号，统计该课程的选课人数和平均分，并将统计结果返回给调用者。
CREATE PROCEDURE PROC2(@cNo char(3), @count tinyint OUTPUT, @avg numeric(5,1) OUTPUT)
AS
SELECT @count=COUNT(*), @avg=AVG(score)
FROM Score
WHERE courseNo=@cNo
GO
--DECLARE @count tinyint, @avg numeric(5,1)
--EXEC PROC2 '006', @count OUTPUT, @avg OUTPUT
--SELECT @count, @avg
--(3) 不允许使用聚集函数，统计每个学院的选课学生人数和课程平均分，并将学院的名称、选课人数
--和平均分按学院的名称顺序，以集合的方式返回给调用者。
--1) 书上使用临时表的方法：
CREATE PROCEDURE PROC3
AS
BEGIN
--定义一个临时表,存放每个学院的名称、课程名称、课程选课人数和课程平均分
CREATE TABLE #myTemp(
institute varchar(30),
courseName varchar(30),
count tinyint,
avg numeric(5,1),
PRIMARY KEY(institute,courseName)
)
DECLARE @institute varchar(30)
--定义游标curInstitute，查找学院名称
DECLARE curInstitute CURSOR
FOR
SELECT DISTINCT institute
FROM Class
ORDER BY institute
OPEN curInstitute
FETCH curInstitute INTO @institute
WHILE (@@FETCH_STATUS=0)
BEGIN
DECLARE @courseName varchar(30),@count tinyint=0,@avg numeric(5,1)=0
--定义游标curCourse，查询学院名称为@institute的课程名称、课程选课人数和平均分
DECLARE curCourse CURSOR
FOR
SELECT courseName,count(*),avg(score)
FROM Score,Course
WHERE studentNo IN(
SELECT studentNo
FROM Student
WHERE classNo IN(
SELECT classNo
FROM Class
WHERE institute=@institute
)
) AND Score.courseNo=Course.courseNo
GROUP BY courseName
OPEN curCourse
FETCH curCourse INTO @courseName,@count,@avg
WHILE(@@FETCH_STATUS=0)
BEGIN
--将学院名称、课程名称、课程选课人数和课程平均分插入到临时表#myTemp中
INSERT INTO #myTemp VALUES(@institute,@courseName,@count,@avg)
--获取下一个游标值，取该学院下一门课程的课程名、选课人数和平均分
FETCH curCourse INTO @courseName,@count,@avg
END
CLOSE curCourse
DEALLOCATE curCourse
--获取游标curInstitute的下一个值，即取下一个学院
FETCH curInstitute INTO @institute
END
CLOSE curInstitute
DEALLOCATE curInstitute
--显示临时表的内容，同时将临时表的内容返回给调用者
SELECT * FROM #myTemp Order BY institute
END
GO
--EXEC PROC3
--DROP PROCEDURE PROC3
--2) 使用游标作为OUTPUT参数的方法：
--将游标使用成O
UTPUT参数。游标可以使用OUTPUT（输出）参数，但不能使用成输入参数。
--也就是说，游标可以作为结果返回，但却不能传输到过程中去。当游标被用作参数时，
--需要限定其为OUTPUT和VARYING。VARYING关键字指出该结果集要用来支持输出参数。
--这样就提供了将结果集返回到调用过程的能力。
--例如：
CREATE PROCEDURE PROC3(@institute_cursor CURSOR VARYING OUTPUT)
AS
SET @institute_cursor = CURSOR
FOR
SELECT institute, courseName, COUNT(*), AVG(score)
FROM Class,Student,Score,Course
WHERE Class.classNo=Student.classNo
AND Student.studentNo=Score.studentNo
AND Score.courseNo=Course.courseNo
GROUP BY institute,courseName
ORDER BY institute
OPEN @institute_cursor
GO
--DECLARE @MyCursor CURSOR;
--EXEC PROC3 @institute_cursor = @MyCursor OUTPUT;
--WHILE (@@FETCH_STATUS = 0)
--BEGIN
-- FETCH NEXT FROM @MyCursor;
--END;
--CLOSE @MyCursor;
--DEALLOCATE @MyCursor;
--GO
--(4) 不允许使用聚集函数，统计每门课程的选课人数和平均分，按如下格式输出。
--课程名 1：xxxxxx
--学号 姓名 成绩
--xxxxxx xxxxxx xxxx
--... ... ...
--选课人数：xxx
--平均分：xxx.xx
--------------------------------------------------
--课程名 2：xxxxxx
--学号 姓名 成绩
--xxxxxx xxxxxx xxxx
--... ... ...
--选课人数：xxx
--平均分：xxx.xx
--------------------------------------------------
CREATE PROCEDURE PROC4
AS
BEGIN
SET NOCOUNT ON
DECLARE @courseNo char(3),@courseName varchar(30)
DECLARE @n tinyint=0
DECLARE curCourse CURSOR
FOR
SELECT courseNo,courseName
FROM Course
OPEN curCourse
FETCH curCourse INTO @courseNo,@courseName
WHILE(@@FETCH_STATUS=0)
BEGIN
SET @n=@n+1
PRINT '课程名' + STR(@n,2) + '：' + @courseName
PRINT '学号 姓名 成绩'
DECLARE numeric(5,1)
@studentNo char(7),@studentName varchar(20),@score
DECLARE @count tinyint=0,@sum numeric(5,1)=0,@avg numeric(5,1)=0
DECLARE curScore CURSOR
FOR
SELECT Student.studentNo,studentName,score
FROM Student,Score
WHERE courseNo=@courseNo
AND Student.studentNo=Score.studentNo
OPEN curScore
FETCH curScore INTO @studentNo,@studentName,@score
WHILE(@@FETCH_STATUS=0)
BEGIN
PRINT CONVERT(char(10),@studentNo) + CONVERT(char(10),@studentName) + CONVERT(char(10),@score)
SET @sum=@sum+@score
SET @count=@count+1
FETCH curScore INTO @studentNo,@studentName,@score
END
CLOSE curScore
DEALLOCATE curScore
IF @count!=0 SET @avg=@sum/@count
PRINT '选课人数：' + CONVERT(char(3), @count)
PRINT '平均分：' + CONVERT(char(5), @avg)
PRINT '-------------------------------------------'
FETCH curCourse INTO @courseNo,@courseName
END
CLOSE curCourse
DEALLOCATE curCourse
END
GO
--EXEC PROC4
因篇幅问题不能全部显示，请点此查看更多更全内容