5.24
今天实现数据库原理的实验三，
三、实验步骤:
1、 开始→程序→Microsoft SQL Server→SQL Server Management Stdio。
2、 在“连接到服务器”对话框中，选择“Windows 身份验证”，点击“连接”，进入
SQL Server Management Stdio 操作界面。
3、数据库的安全性实验：设置 SQL Server 的安全认证模式，实现对 SQL Server 的用户
和角色管理，设置和管理数据操作权限。
4、数据库的完整性实验：使用 Transact-SQL 设计规则、缺省、约束和触发器， 通过 SQL
Server 管理器定义它们。
创建 students 数据库。利用如下 sql 语句在 students 数据库中创建表（具体含义见课
本 P33 页）
CREATE TABLE Student (
Sno char ( 7 ) PRIMARY KEY,
Sname char ( 10 ) NOT NULL,
Ssex char (2),
Sage tinyint ,
Sdept char (20)
)
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 )
)
完成如下操作：
（1） 在 SQL Server 管理器中， 创建一个新的 windows 登录名。
Ⅰ、
如果存在连接 可以新建一个电脑用户进行连接
（2）在 SQL Server 管理器中为自己建立一个 SQL SERVER 服务器登录名（自己学号）。
输入自己的学号以及设置的密码，同时在用户映射中
选择自己刚刚建立的数据库
分配数据库角色
（3）为所属的 SQL 服务器设置混合认证安全认证模式。
在自己连接的实例下新建查询，输入此语句。
-- 首先，检查当前的身份验证模式
SELECT authentication_mode FROM sys.server_principals WHERE principal_id = 1;
-- 如果需要更改，执行以下命令（需要sysadmin权限）
ALTER LOGIN sa ENABLE; -- 确保sa账户是启用状态
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2;
刷新后，重启服务才能生效。
（4）用自己创建的登录名登录服务器。
配置SQL Server为混合模式身份验证后，你将有两种登录方式可选：
- Windows身份验证：这种方式下，SQL Server会使用你的Windows用户名和密码来验证身份。登录时，在SQL Server Management Studio (SSMS) 或其他客户端工具中，选择“Windows身份验证”或“Windows Authentication”选项，通常不需要手动输入用户名和密码，因为它们是从你的Windows会话中自动获取的。
- SQL Server身份验证：在这种模式下，你需要提供一个在SQL Server中预定义的用户名（例如，SA账户或其他自定义的登录名）及对应的密码。在SSMS或客户端工具的登录界面，选择“SQL Server 身份验证”或“SQL Server Authentication”选项，然后分别输入用户名和密码。
（5）在 SQL Server 管理器中为自己建立一个 students 数据库用户（自己学号-user） 和
角色（自己学号-role）。
USE students;
GO
CREATE USER [20220002-user] FOR LOGIN [20220002];
GO
CREATE ROLE [20220002-role];
GO
-- 允许查询
GRANT SELECT ON dbo.Student TO [20220002-role];
GRANT SELECT ON dbo.Course TO [20220002-role];
GRANT SELECT ON dbo.SC TO [20220002-role];
-- 拒绝对SC表中Grade列的更新权限
DENY UPDATE (Grade) ON dbo.SC TO [20220002-role];
-- 允许修改Student表中的某些列
GRANT UPDATE (Sname, Ssex, Sage) ON dbo.Student TO [20220002-user];
ALTER ROLE [20220002-role] ADD MEMBER [20220002-user];
GO
（6） 用管理器实现如下权限的分配：
① 将 student 表中“sname”、“ssex”、“sage” 的修改权限赋予自己创建的数据库用户。
GRANT UPDATE (Sname, Ssex, Sage) ON dbo.Student TO [20220002-user];
② 将 student 表、 course 表、 sc 表的查询权限授予自己创建的角色。
GRANT SELECT ON dbo.Student TO [20220002-role];
GRANT SELECT ON dbo.Course TO [20220002-role];
GRANT SELECT ON dbo.SC TO [20220002-role];
③ 拒绝自己创建的角色拥有 sc 表中 grade 的修改权限。
DENY UPDATE (Grade) ON dbo.SC TO [20220002-role];
④ 将自己创建的数据库用户添加为自己创建的角色成员。
ALTER ROLE [20220001-role] ADD MEMBER [20220002-user];
（7）用管理器创建触发器实现如下安全性和完整性：
①定义 course 表中 credit 属性为“3”的缺省。
ALTER TABLE Course ADD CONSTRAINT DF_Course_Credit DEFAULT 3 FOR Ccredit;
②建立 course 与 sc 表间来维护参照完整性而使用的一个级联删除触发器、 一个级
联修改触发器和一个受限插入触发器。
CREATE TRIGGER trg_CourseDelete_Cascade ON Course
INSTEAD OF DELETE
AS
BEGIN
DELETE FROM SC WHERE Cno IN (SELECT Cno FROM deleted);
DELETE FROM Course WHERE Cno IN (SELECT Cno FROM deleted);
END;
CREATE TRIGGER trg_SC_Insert_Restrict
ON SC
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO SC (Sno, Cno, Grade, XKLB)
SELECT i.Sno, i.Cno, i.Grade, i.XKLB
FROM inserted i
WHERE EXISTS (SELECT 1 FROM Course c WHERE c.Cno = i.Cno);
END;
CREATE TRIGGER trg_SC_Insert_Restrict
ON SC
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO SC (Sno, Cno, Grade, XKLB)
SELECT i.Sno, i.Cno, i.Grade, i.XKLB
FROM inserted i
WHERE EXISTS (SELECT 1 FROM Course c WHERE c.Cno = i.Cno);
END;
（8） 对“students 数据库”编写存储过程，完成下面功能：
① 统计成绩 60 分以下的人数；
CREATE PROCEDURE Proc_CountBelow60
AS
BEGIN
SELECT COUNT(*) AS 'Below60Count' FROM SC WHERE Grade < 60;
END;
② 统计给定 cno 的课程的平均成绩，并返回平均成绩；
CREATE PROCEDURE Proc_AvgGradeByCno
@Cno CHAR(10)
AS
BEGIN
SELECT AVG(Grade) AS 'AverageGrade' FROM SC WHERE Cno = @Cno;
END;
③将 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 Proc_ConvertGradeToLevel
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
ELSE 5
END;
END;
