10.20
create database students
create table Student(
Sno char(7),
Sname char(10) not null,
Ssex char(2),
Sage tinyint,
Sdept char(20),
primary key(Sno)
)
create table Course(
Cno char(10)not null,
Cname char(20) not null,
Ccredit tinyint,
Semester tinyint
primary key(Cno)
)
create table SC(
Sno char(7) not null,
Cno char(10) not null,
Grade tinyint ,
XKLB char(4),
Primary key(Sno,Cno)
)
SELECT name FROM sys.server_principals WHERE name = '20224025';
USE students;
CREATE USER [20224025] FOR LOGIN [20224025];
-- 或者如果用户已经存在，则可能是需要修改其权限
ALTER ROLE db_datareader ADD MEMBER [20224025]; -- 赋予读取权限
ALTER ROLE db_datawriter ADD MEMBER [20224025]; -- 赋予写入权限
-- 或者根据你的需要赋予其他权限
drop user [20224025] ;
--1）在 SQL Server 管理器中，创建一个新的 windows 登录名。
USE [master];
GO
CREATE LOGIN [DOMAIN\Username] FROM WINDOWS;
GO
--（2）在 SQL Server 管理器中为自己建立一个 SQL SERVER 服务器登录名（自己学号）。
USE [master];
GO
CREATE LOGIn [20224025] WITH PASSWORD = '123456';
GO
--（3）为所属的 SQL 服务器设置混合认证安全认证模式。
USE master;
GO
-- 显示当前的配置设置
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
-- 修改身份验证模式为混合模式
EXEC sp_configure 'sql server authentication mode', 2;
RECONFIGURE;
GO
--（4）用自己创建的登录名登录服务器。
--（5）在 SQL Server 管理器中为自己建立一个 students 数据库用户（自己学号-user）和角色（自己学号-role）。
create user [20224025] for login [20224025];
use students;
create role [20224025];
SELECT o.name, o.type_desc
FROM sys.objects o
WHERE o.name = '20224025';
SELECT name, type_desc
FROM sys.database_principals
WHERE name = '20224025' AND type_desc = 'DATABASE_ROLE';
--（6）用管理器实现如下权限的分配：
--① 将 student 表中“sname”、“ssex”、“sage”的修改权限赋予自己创建的数据库用户。
USE students; -- 替换为你的数据库名
-- 授予 myUser 对 student 表中 sname、ssex、sage 列的 UPDATE 权限
GRANT UPDATE (sname, ssex, sage) ON dbo.student TO [20224025];
--② 将 student 表、course 表、sc 表的查询权限授予自己创建的角色。
USE students;
-- 授予myRole对student表、course表、sc表的查询权限
GRANT SELECT ON dbo.student TO [20224025];
GRANT SELECT ON dbo.course TO [20224025];
GRANT SELECT ON dbo.sc TO [20224025];
--③ 拒绝自己创建的角色拥有 sc 表中 grade 的修改权限。
USE students;
-- 拒绝myRole对sc表中grade列的修改权限
DENY UPDATE(grade) ON dbo.sc TO [20224025];
--④ 将自己创建的数据库用户添加为自己创建的角色成员。
-- 假设你已经有一个名为 'db_datareader' 的数据库角色
USE students;
GO
ALTER ROLE [20224025] ADD MEMBER [20224025];
GO
--（7）用管理器创建触发器实现如下安全性和完整性：
--①定义 course 表中 credit 属性为“3”的缺省。
ALTER TABLE course
ADD CONSTRAINT DF_course_credit DEFAULT 3 FOR Ccredit;
--②建立 course 与 sc 表间来维护参照完整性而使用的一个级联删除触发器、一个级
联修改触发器和一个受限插入触发器。
CREATE TRIGGER trg_course_delete
ON course
AFTER DELETE
AS
BEGIN
DELETE FROM sc WHERE cno IN (SELECT cno FROM deleted);
END;
CREATE TRIGGER trg_course_update
ON course
AFTER UPDATE
AS
BEGIN
IF UPDATE(cno)
BEGIN
UPDATE sc
SET sc.cno = deleted.cno
FROM sc
INNER JOIN deleted ON sc.cno = deleted.old_cno; -- 假设有old_cno列来存储旧值，实际中需要其他方法获取
END
END;
CREATE TRIGGER trg_sc_insert_check
ON sc
AFTER INSERT
AS
BEGIN
IF EXISTS (
SELECT 1
FROM inserted i
LEFT JOIN course c ON i.cno = c.cno
WHERE c.cno IS NULL
)
BEGIN
-- 如果有任何新插入的cno在course表中不存在，则抛出错误
RAISERROR('Cannot insert sc record with non-existent cno.', 16, 1);
ROLLBACK TRANSACTION; -- 回滚事务以撤销插入操作
END;
END;
（8）对“students 数据库”编写存储过程，完成下面功能：
① 统计成绩 60 分以下的人数；
CREATE PROCEDURE sp_CountStudentsBelow60
AS
BEGIN
SELECT COUNT(*) AS Below60Count FROM sc WHERE grade < 60;
END;
EXEC sp_CountStudentsBelow60;
② 统计给定 cno 的课程的平均成绩，并返回平均成绩；
CREATE PROCEDURE sp_AverageGradeByCNO
@CNO INT
AS
BEGIN
SELECT AVG(grade) AS AverageGrade FROM sc WHERE cno = @CNO;
END;
EXEC sp_AverageGradeByCNO @CNO = 1001;
③将 sc 表中 grade 从百分制改为等级制（5、4、3、2、1）。即 0-20 分为 1，21-40
为 2，4 1-60 为 3，61-80 为 4,81-100 为 5。
CREATE PROCEDURE sp_ChangeGradeToLevels
AS
BEGIN
UPDATE sc
SET grade = CASE
WHEN grade BETWEEN 0 AND 20 THEN 1
WHEN grade BETWEEN 21 AND 40 THEN 2
WHEN grade BETWEEN 41 AND 60 THEN 3
WHEN grade BETWEEN 61 AND 80 THEN 4
WHEN grade BETWEEN 81 AND 100 THEN 5
ELSE grade -- 保持原值或其他处理
END;
END;
