sqlserver03

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 服务器设置混合认证安全认证模式。
（4）用自己创建的登录名登录服务器。
（5）在 SQL Server 管理器中为自己建立一个 students 数据库用户（自己学号-user）和
角色（自己学号-role）。
（6）用管理器实现如下权限的分配：
① 将 student 表中“sname”、“ssex”、“sage”的修改权限赋予自己创建的数据库用
户。
② 将 student 表、course 表、sc 表的查询权限授予自己创建的角色。
③ 拒绝自己创建的角色拥有 sc 表中 grade 的修改权限。
④ 将自己创建的数据库用户添加为自己创建的角色成员。
（7）用管理器创建触发器实现如下安全性和完整性：
①定义 course 表中 credit 属性为“3”的缺省。
②建立 course 与 sc 表间来维护参照完整性而使用的一个级联删除触发器、一个级
联修改触发器和一个受限插入触发器。
（8）对“students 数据库”编写存储过程，完成下面功能：
① 统计成绩 60 分以下的人数；
② 统计给定 cno 的课程的平均成绩，并返回平均成绩；
③将 sc 表中 grade 从百分制改为等级制（5、4、3、2、1）。即 0-20 分为 1，21-40
为 2，4 1-60 为 3，61-80 为 4,81-100 为 5。
(9) 完成课本 P172 页的上机练习。
要求：仔细阅读每一步要求，分别用 SSMS 工具和 T-SQL 语句实现，请记录结果并分
析原因，在报告中写出 T-SQL 语句

 4.先按照要求建数据库

看一下建完的表：

4.1

 

在 SQL Server 管理器中，创建一个新的 windows 登录名（如果想要创建需要在登录名那里写入域名\用户名的格式，这里我格式就没搞明白，我直接写下一个了）

 

 4.2.在 SQL Server 管理器中为自己建立一个 SQL SERVER 服务器登录名（自己学号）。 

1. 同样在 “Logins” 上右键点击，选择 “New Login…”。
2. 在 “Login – New” 窗口中，选择 “SQL Server authentication”，在 “Login name” 框中输入你的学号20224082。
3. 输入并确认密码。
4. 取消勾选 “Enforce password policy” 以避免复杂性要求（这里三个我都取消勾选）。
5. 在“默认数据库”中选择一个你想要的默认数据库（例如“master”）。
6. 在“服务器角色”页签下，选择“public”角色。
7. 点击“确定”创建登录名。

 

4.3为所属的 SQL 服务器设置混合认证安全认证模式。 

1. 右键点击你的服务器实例，选择“属性”。
2. 在弹出的对话框中，选择“安全性”页签。
3. 在“服务器身份验证”部分，选择“SQL Server 和 Windows 身份验证模式”。
4. 点击“确定”。
5. 重启 SQL Server 服务以应用更改。

 

 4.4.用自己创建的登录名登录服务器。

创建成功后登录：

 

可以发现登录成功，但不能访问自己创建的students数据库。

 4.5.在 SQL Server 管理器中为自己建立一个 students 数据库用户（自己学号-user）和角色（自己学号-role）。

       为E_Market数据库创建用户名，先断开数据库连接，使用windows身份或sa登录数据库；(这里一定要明白用户名和登录名不是一个)

1. 在对象资源管理器中，展开“数据库”文件夹。

2. 找到你想要创建用户的数据库，展开该数据库。

3. 展开“安全性”文件夹。

4. 右键点击“用户”文件夹，选择“新建用户...”。

   在弹出的对话框中：

   • 在“用户名称”字段中输入 学号-user。
   • 在“登录名”字段中选择你创建的学号登录名。
   • 在“默认架构”字段中输入 dbo。
   • 点击“确定”创建用户。

   5.新建角色：

-- 创建数据库角色 
CREATE ROLE [20224082-role]; GO
-- 将用户添加到角色
ALTER ROLE [20224082-role] ADD MEMBER [20224082-user];
GO

 

 

 

 4.6.1修改权限赋予数据库用户

方法1：

1. 打开 SSMS，连接到数据库服务器，展开目标数据库。
2. 展开 “Tables” 文件夹，找到 student 表。
3. 右键点击 student 表，选择 “Properties”。
4. 在 “Table Properties” 窗口中，选择 “Permissions” 页。
5. 点击 “Search” 按钮，添加你创建的数据库用户（例如 123456-user）。
6. 在权限列表中，找到 “sname”、“ssex”、“sage” 列，分别为这三列赋予 “Update” 权限。
7. 点击 “OK” 保存设置。

 方法二

use students

go

grant update on Student(Sname,Ssex,Sage) to [20224082-user]

go

 

 

 4.6.2查询权限授予角色

方法1：

1. 在目标数据库下，右键点击 student 表，选择 “Properties”。
2. 在 “Table Properties” 窗口中，选择 “Permissions” 页。
3. 点击 “Search” 按钮，添加你创建的角色（例如 123456-role）。
4. 为角色赋予 “Select” 权限，点击 “OK”。
5. 对 course 和 sc 表重复上述步骤，为角色赋予 “Select” 权限。

方法二：

grant select on Student to [20224082-role];

grant select on Course to [20224082-role];

grant select on SC to [20224082-role];

 4.6.3拒绝自己创建的角色拥有 sc 表中 grade 的修改权限。

 

deny update on SC(grade) to [20224082-role]

 4.6.4将用户添加到角色（上面创建角色的时候写了这条语句）

ALTER ROLE [20224082-role] ADD MEMBER [20224082-user];
GO

 4.7.1用管理器创建触发器实现如下安全性和完整性：定义 course 表中 credit 属性为“3”的缺省。

方法一：

create trigger dbo.st1

on [dbo].[Course]

after insert,update

as

begin

set nocount on;

update Course set Ccredit=3 from Course

join SC on SC.Cno=Course.Cno

where Sno in(select Sno from inserted where Cno=null)

end

go

方法二：

1. 在 SSMS 中，展开目标数据库，右键点击 course 表，选择 “Design”。
2. 选择 credit 列，在列属性窗口中，找到 “Default Value or Binding”，输入 3。
3. 保存更改。

 4.7.2建立 course 与 sc 表间来维护参照完整性而使用的一个级联删除触发器、一个级联修改触发器和一个受限插入触发器。

-- 级联删除触发器
CREATE TRIGGER trg_CascadeDelete ON course
AFTER DELETE
AS
BEGIN
    DELETE FROM sc WHERE cno IN (SELECT cno FROM DELETED);
END;
GO

-- 级联修改触发器
CREATE TRIGGER trg_CascadeUpdate ON course
AFTER UPDATE
AS
BEGIN
    IF UPDATE(cno)
    BEGIN
        DECLARE @OldCno INT, @NewCno INT;
        SELECT @OldCno = cno FROM DELETED;
        SELECT @NewCno = cno FROM INSERTED;
        UPDATE sc SET cno = @NewCno WHERE cno = @OldCno;
    END;
END;
GO

-- 受限插入触发器
CREATE TRIGGER trg_RestrictInsert ON sc
INSTEAD OF INSERT
AS
BEGIN
    IF NOT EXISTS (SELECT 1 FROM course WHERE cno IN (SELECT cno FROM INSERTED))
    BEGIN
        RAISERROR('Invalid course number', 16, 1);
        ROLLBACK TRANSACTION;
    END
    ELSE
    BEGIN
        INSERT INTO sc (sno, cno, grade)
        SELECT sno, cno, grade FROM INSERTED;
    END
END;
GO

4.8

对“students 数据库”编写存储过程，完成下面功能：

4.8.1成绩 60 分以下的人数；

4.8.2统计给定 cno 的课程的平均成绩，并返回平均成绩；

4.8.3将 sc 表中 grade 从百分制改为等级制（5、4、3、2、1）。即 0-20 分为 1，21-40

为 2，4 1-60 为 3，61-80 为 4,81-100 为 5。

-- 统计成绩60分以下的人数
CREATE PROCEDURE CountLowGrades
AS
BEGIN
    SELECT COUNT(*) AS LowGradeCount
    FROM sc
    WHERE grade < 60;
END;
GO

-- 统计给定cno课程的平均成绩
CREATE PROCEDURE GetAverageGrade
    @cno INT
AS
BEGIN
    SELECT AVG(grade) AS AverageGrade
    FROM sc
    WHERE cno = @cno;
END;
GO

-- 将百分制成绩改为等级制
CREATE PROCEDURE ConvertGradesToScale
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;
GO