资源预览内容
第1页 / 共8页
第2页 / 共8页
第3页 / 共8页
第4页 / 共8页
第5页 / 共8页
第6页 / 共8页
第7页 / 共8页
第8页 / 共8页
亲,该文档总共8页全部预览完了,如果喜欢就下载吧!
资源描述
USE ScoreDBGO-9.6 在学生成绩管理数据库ScoreDB中完成。-(1) 在班级表Class中,分别使用列级和元组级约束保证班级人数classNum属性的取值在(0,50)之间。-为避免误删Class表的数据,另建两表演示,演示后删除-列级约束CREATE TABLE Class1(classNochar(6)PRIMARY KEY,/*班级编号*/classNamevarchar(30)NOT NULL,/*班级名称*/institutevarchar(30)NOT NULL,/*所属学院*/gradesmallintNOT NULL/*年级*/DEFAULT 0,classNumtinyintNULL/*班级人数*/CHECK(classNum >=0 AND classNum <= 50)-元组级约束CREATE TABLE Class2(classNochar(6)PRIMARY KEY,/*班级编号*/classNamevarchar(30)NOT NULL,/*班级名称*/institutevarchar(30)NOT NULL,/*所属学院*/gradesmallintNOT NULL/*年级*/DEFAULT 0,classNumtinyintNULL,/*班级人数*/CONSTRAINT CK_Class_classNum CHECK(classNum >=0 AND classNum <= 50)-删除演示表DROP TABLE Class1DROP TABLE Class2GO-(2) 在学生表Student中,限制籍贯为上海或北京的学生的年龄必须在17岁以上。-需要使用触发器CREATE TRIGGER Student_birthdayON StudentFOR INSERT,UPDATEASIF EXISTS(SELECT * FROM inserted WHERE YEAR(GETDATE()-YEAR(birthday)<17)ROLLBACKGO-(3) 对于某门课程,保证如果没有选修其先修课程,则不能选修该课程。CREATE TRIGGER Score_courseNoON ScoreFOR INSERTASIF NOT EXISTS(SELECT * FROM Score WHERE courseNo=(SELECT courseNo FROM Course WHERE priorCourse=(SELECT courseNo FROM inserted)ROLLBACKGO-(4) 如果在学生表中修改了学号,则自动修改成绩表中的学号。CREATE TRIGGER Student_studentNoON StudentFOR UPDATEASIF NOT EXISTS(SELECT * FROM inserted WHERE studentNo=(SELECT studentNo FROM deleted)UPDATE ScoreSET 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 CURSORFORSELECT classNoFROM Class-使用游标OPEN CURSOR_Class-打开游标FETCH CURSOR_Class INTO classNoWHILE(FETCH_STATUS=0)BEGINSELECT classNum=(SELECT count(*)FROM StudentWHERE classNo=classNo)UPDATE Class SET classNum=classNum WHERE CURRENT OF CURSOR_ClassFETCH CURSOR_Class INTO classNo-获取下一个游标值ENDCLOSE 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;GOCREATE CERTIFICATE Teacher50WITH SUBJECT = Teachers,EXPIRY_DATE = 11/11/2015;GOCREATE 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)ASSELECT studentName,courseName,ScoreFROM Student INNER JOIN Score ON Student.studentNo=Score.studentNoINNER JOIN Course ON Score.courseNo=Course.courseNoWHERE classNo=cNoORDER BY studentNameGO-EXEC PROC1 IS0801-(2) 根据输入的课程编号,统计该课程的选课人数和平均分,并将统计结果返回给调用者。CREATE PROCEDURE PROC2(cNo char(3), count tinyint OUTPUT, avg numeric(5,1) OUTPUT)ASSELECT count=COUNT(*), avg=AVG(score)FROM ScoreWHERE courseNo=cNoGO-DECLARE count tinyint, avg numeric(5,1)-EXEC PROC2 006,
网站客服QQ:2055934822
金锄头文库版权所有
经营许可证:蜀ICP备13022795号 | 川公网安备 51140202000112号