@ArrowLLL 2017-05-09T09:33:20.000000Z 字数 18561 阅读 3339

数据库实验汇总 4.0

T-SQL 数据库

Elon Lin

实验一：存储过程

参考: SQL Server 存储过程

a. 创建一个存储过程:查询某一系的学生的选课信息(这里查询信息安全系）

CREATE PROC SCinfo_InfoSec
AS
    SELECT Student.Sno, Cno, Grade
        FROM Student,Sc WHERE
            Sdept = 'InfomationSecurity'
        AND Student.Sno = Sc.Sno;

b. 创建带输入参数的存储过程:输入系别及性别后,查询该系相应性别学生选课情况

CREATE PROC SCinfo_dept_sex (
    @deptName varchar(20),
    @sex char(1)
) AS
    SELECT Student.Sno, Cno, Grade
        FROM Student,Sc WHERE
            Sdept = @deptName
        AND Ssex = @sex
        AND Student.Sno = SC.Sno;

c. 创建带输入参数带默认值的存储过程:输入系别及性别后,查询该系相应别学生的选课情况。如果不输入学生的性别,则默认查询该系男生的选课情况

CREATE PROC SCinfo_dept_sexDefMale (
    @deptName varchar(20),
    @sex char(1) = 'M'
) AS
    SELECT Student.Sno, Cno, Grade
        FROM Student, Sc WHERE
            Sdept = @deptName
        AND Ssex = @sex
;

d) 创建带输入参数的存储过程:完成输入一个学号,如果该学号存在,则显示:该学号存在,同时显示该学生的学号、该学生所在系的学生人数。否则显示:该学号不存在(显示出学号)

CREATE PROC Sdeptinfo_Sno (
    @stuNo char(10)
) AS
BEGIN
    DECLARE @numStu int;
    SELECT @numStu = COUNT(*) FROM Student
        WHERE Sno = @stuNo;
    IF @numStu > 0
        SELECT @stuNo AS '学号',
                '存在' AS '该学号是否存在',
                COUNT(*) AS '所在系共有学生人数'
            FROM Student
            WHERE Sdept in (
                SELECT Sdept FROM Student
                    WHERE Sno = @stuNo
            );
    ELSE
        SELECT @stuNo AS '学号',
                '不存在' AS '该学号是否存在';
END

e. 创建带输入参数的存储过程:完成输入一个学号,如果该学号存在,则显示:该学号存在,并显示该学生的学号、所选修课程的课程数、所选课程的平均成绩。否则显示:该学号不存在(显示出学号)。

CREATE PROC SCinfo_Sno (
    @stuNo char(10)
) AS
    BEGIN
        DECLARE @numStu int;
        SELECT @numStu = COUNT(*) FROM Student
            WHERE Sno = @stuNo;
        IF @numStu > 0
            SELECT @stuNo AS '学号',
                    '存在' AS '该学号是否存在',
                    COUNT(*) AS '选择的课程总数',
                    AVG(Grade) AS '所有课程平均成绩'
                FROM Sc WHERE  Sno = @stuNo;
        ELSE
            SELECT @stuNo AS '学号',
                    '不存在' AS '该学号是否存在';
    END

实验二 : 存储过程进阶

参考: SQL Server 存储过程
 存储过程：数据的插入和更新

a. 创建一个加密存储过程,且该存储过程带输入参数:完成:输入一个学号,果该学号存在,则显示:该学号存在,并显示该学生的学号、所选修课程的课程数、所选课程的平均成绩。否则显示:该学号不存在(显示出学号)。

CREATE PROC SCinfo_Sno_enc(
    @stuNo char(10)
) WITH ENCRYPTION AS
    BEGIN
        DECLARE @numStu int;
        SELECT @numStu = COUNT(*) FROM Student
            WHERE Sno = @stuNo;
        IF @numStu > 0
            SELECT @stuNo as '学号',
                    '存在' as '学号是否存在',
                    COUNT(*) as '课程数',
                    AVG(Grade) as '平均成绩'
                FROM Sc
                WHERE Sno = @stuNo;
        ELSE
            SELECT @stuNo as '学号',
                '不存在' as '学号是否存在';
    END

b. 创建带输入参数的存储过程:输入系别及性别后,查询该系相应性别学生的选课情况。如果性别输入不对,提示用户正确的输入方法(当用户非法输入时,提示用户。如:性别的正确输入方法是“M”或“F”)。

CREATE PROC SCinfo_dept_sex(
    @deptName varchar(20),
    @stuSex char(1)
) AS
    BEGIN
        IF (@stuSex IN ('F', 'M'))
            SELECT Sdept, Student.Sno, Cno, Grade
                FROM Student, Sc WHERE
                    Sdept = @deptName
                AND Sex = @stuSex
                AND Student.Sno = Sc.Sno;
        ELSE PRINT '性别输入方式不正确。输入 F 表示女生， 输入 M 表示男生';
    END

c. 创建一个加密存储过程,且该存储过程带输入参数:完成输入一个学号,如果该学号存在,则显示:该学号存在,并显示该学生所在系的所有学生的学号、所选修课程的课程数、所选课程的平均成绩,并按学号排序。否则显示:该学号不存在(显示出学号)。

CREATE PROC Deptinfo_Sno (
    @StuNo char(10)
) WITH ENCRYPTION AS
    BEGIN
        DECLARE @numSno int;
        SELECT @numSno = COUNT(*) FROM Student
            WHERE Sno = @stuNo;
        IF (@numSno > 0)
            BEGIN
                PRINT '学号' + @stuNo + '存在';
                SELECT Sno,
                        COUNT(*) AS '课程总数',
                        AVG(Grade) AS '平均成绩'
                    FROM SC
                    WHERE Sno in (
                        SELECT Sno FROM Student
                            WHERE Sdept in (
                                SELECT Sdept FROM Student
                                    WHERE Sno = @stuNo
                            )
                    )
                    GROUP BY Sno
                    ORDER BY Sno;
            END
        ELSE
            PRINT '学号 ' + @stuNo + ' 不存在';
    END

d. 创建一存储过程,加入一条学生记录到学生表中。插入过程中,如果输入有误,提示用户正确的输入方法与规则。如:学号为 2014XXXXXX(每个 X 代表 0-9),用户插入 2015XXXXXX 或者位数不对等,则提示用户“学号总共有 10 位数字,前四位请输入2014”;如性别应为“M”或“F”,如果用户输入不对(即用户只要不是输入 M 或 F),则提示用户......

CREATE PROC InsInfo_To_Student(
    @stuNo char(10),
    @stuName varchar(10),
    @stuSex char(1),
    @stuAge int,
    @stuDept varchar(20),
    @stuBirthPlace varchar(20)
) AS
    BEGIN
        IF (LEN(@stuNo) != 10 OR @stuNo NOT LIKE '2014[0-9][0-9][0-9][0-9][0-9][0-9]')
            PRINT '学号总共有 10 位数字,前四位请输入2014';
        ELSE IF (@stuSex != 'F' AND @stuSex != 'M')
            PRINT '性别输入不正确，应输入 M 表示女生，F 表示男生'
        ELSE
            INSERT INTO Student
                (Sno, Sname, Ssex, Sage, Sdept, BirthPlace)
            VALUES
                (@stuNo, @stuName, @stuSex, @stuAge, @stuDept, @stuBirthPlace);
    END

e. 创建一存储过程,输入姓名,删除该学生的选课信息及学生表中的记录信息。如果姓名不存在,则提示用户“该姓名不存在,请重新输入存在的姓名”。

CREATE PROC DeleteInfo_stu(
    @stuName varchar(10)
) AS
    BEGIN
        DECLARE @numStu int;
        SELECT @numStu = COUNT(*) FROM Student
            WHERE Sname = @stuName;
        IF @numStu > 0
            BEGIN
                DELETE FROM Sc WHERE Sno
                    IN (
                        SELECT Sno FROM Student
                            WHERE Sname = @stuName
                    );
                DELETE FROM Student WHERE Sname = @stuName;
            END
        ELSE
            PRINT '该姓名 ' + @stuName + ' 不存在,请重新输入存在的姓名';
    END

f. 创建一存储过程,输入姓名,将该学生所选的所有课程的分数增加 1 分。如果姓名不存在,则提示用户“该姓名不存在,请重新输入存在的姓名”。

CREATE PROC AddGradeInSc_stu (
    @stuName varchar(10)
) AS
    BEGIN
        DECLARE @numStu int;
        SELECT @numStu = COUNT(*) FROM Student
            WHERE Sname = @stuName;
        IF @numStu > 0
            UPDATE Sc SET Grade = Grade + 1
                WHERE Sno in (
                    SELECT Sno FROM Student
                        WHERE Sname = @stuName
                );
        ELSE
            PRINT '该姓名 ' + @stuName + ' 不存在,请重新输入存在的姓名'
    END

g. 掌握一下存储过程

Sp_help

查看表结构 : EXEC Sp_help Student;
Sp_renamedb

更改数据库名称 : EXEC Sp_renamedb 'OldDatabase', 'NewDatabase';
Sp_rename

更改表名称 : EXEC Sp_rename 'OldTable', 'NewTable'
Sp_who

报告当前用户或进程的信息，也可以不跟参数返回所有活动用户信息 : EXEC Sp_who 'loginName'; 或 EXEC Sp_who 'SPID' 或 EXEC Sp_who
Sp_depends

查看一个数据库对象引用了哪些其它的数据库对象（例如，依赖表或视图的视图和过程，以及视图或过程所依赖的表和视图）: EXEC Sp_depends 'view';
Sp_helptext

显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的文本 : EXEC Sp_helptext 'AddGradeInSc_stu';

实验三 : 数据约束

参考: SQL 约束 (Constraints)

(1). 创建学生关系表 Student,学号 Sno 为主键,同时要求为姓名 Sname 字1段建立唯一性约束。

CREATE TABLE Student(
    Sno char(10) primary key,
    Sname varchar(15) NOT NULL CONSTRAINT Uc_StuName UNIQUE,
    Ssex char(1) NOT NULL,
    Sage int NOT NULL,
    Sdept varchar(20) NOT NULL,
    BirthPlace varchar(20)
);

(2). 创建好 Student 后,修改 Student 表,取消姓名 Sname的唯一性约束。

ALTER TABLE Student
    DROP CONSTRAINT Uc_StuName;

(3). 再次修改 Student 表,增加姓名 Sname 的唯一性约束。

ALTER TABLE Student
    ADD CONSTRAINT Uc_StuName UNIQUE(Sname);

(4). 创建课程关系表 Course,课程号 Cno 为主键,同时要求为学分 Ccredit 字段创建 CHECK 约束,使 10>=Ccredit>0。


CREATE TABLE Course(
    Cno char(5) primary key,
    Cname varchar(20),
    Cpno char(5) references Course(Cno),
    Ccredit int NOT NULL,
    CONSTRAINT Chk_Credit ChECK(10 >= Ccredit AND Ccredit > 0)
);

(5). 创建好 Course 后,修改 Course 表,禁止学分 Ccredit 的 CHECK 约束。

ALTER TABLE Course NOCHECK CONSTRAINT Chk_Credit;

(6). 再次修改 Student 表,启用学分 Ccredit 的 CHECK 约束。

ALTER TABLE Course CHECK CONSTRAINT Chk_Credit;

(7). 创建选修关系表 SC ,课程号 Cno 和学分号 Cno 共同构成主键,同时要求为成绩 Grade 字段创建 DEFAULT 约束,使成绩Grade 的缺省默认值为60 分。

CREATE TABLE Sc(
    Sno char(10),
    Cno char(5),
    Grade numeric(5, 2) CONSTRAINT Default_Grade DEFAULT 60.00,
    PRIMARY KEY(Sno, Cno),
);

(8). 创建好 SC 后,修改 SC 表,取消成绩 Grade 的 DEFAULT 约束。

ALTER TABLE Sc DROP CONSTRAINT Default_Grade;

(9). 再次修改 SC 表,增加成绩 Grade 的 DEFAULT 约束,使成绩 Grade 的缺省默认值为 0 分。

ALTER TABLE Sc ADD CONSTRAINT Default_Grade DEFAULT(0) FOR Grade;

(10). 创建性别默认值 SexDefault,其取值为“M”

CREATE DEFAULT sexDefault as 'M';

(11). 将上述默认值绑定到 Student 表的 Ssex列。通过插入数据,检验绑定的有效性。

EXEC sp_bindefault SexDefault, 'Student.Ssex';

(12). 将上述绑定 SexDefault 松绑。

EXEC sp_unbindefault 'Student.Ssex';

(13). 删除上述 SexDefault。

DROP DEFAULT SexDefault;

(14). 创建学号规则 SnoRule,限制学号的取值只能是 2014-XX-YYYY,且:XX 两位中,左边的 X 只能输入 0~2 的数字,右边的 X 可以任意数字;YYYY 四位中,只能输入数字,不能输入字母。

CREATE RULE SnoRule AS
    @Sno like '2014[0-2][0-2][0-9][0-9][0-9][0-9][0-9]';

(15). 将上述规则 SnoRule 绑定到 Student 表 Sno 列。通过插入数据,检验绑定的有效性。

EXEC sp_bindrule SnoRule, 'Student.Sno';

(16). 将上述绑定松绑。

EXEC sp_unbindrule 'Student.Sno';

(17). 删除上述 SnoRule。

DROP RULE SnoRule;

实验四 : 触发器

1、为“Student”表建立一个名为 tri_updSno_student 创建一个 Update 触发器,当修改学生表中的学号时,同时修改选课表中的学号。

CREATE TRIGGER up_dSno_student
    ON Student
    AFTER UPDATE
AS
    IF UPDATE(Sno)
    BEGIN
        UPDATE Sc SET Sno = inserted.Sno
            FROM Sc, deleted, instered
            WHERE Sc.Sno = deleted.Sno;
    END

2、为“Course”表建立一个名为 tri_updCno_Course 创建一个 Update 触发器,当修改课程表中的课号时,同时修改选课表中的课号。

CREATE TRIGGER tri_updCno_Course
    ON Course
    AFTER UPDATE
AS
    IF UPDATE(Cno)
    BEGIN
        UPDATE Sc SET Sc.Sno = inserted.Sno
            FROM Sc, instered, deleted
            WHERE Sc.Sno = deleted.Sno;
    END

3、为“Student”表建立一个名为 tri_no_updSname_student 的 UPDATE触发器,其作用是当修改“Student”表中的“Sname”字段时,提示不能修改,并取消修改操作。

CREATE TRIGGER tri_no_updSname_student
    ON Student
    AFTER UPDATE
AS
    IF UPDATE(Sname)
    BEGIN
        PRINT '不能修改学生姓名';
        ROLLBACK TRANSACTION
    END

4、为“Student”表建立一个名为 tri_no_upd_student 的 UPDATE 触发器,其作用是当修改“Student”表中的任意字段时,提示不能修改,并取消修改操作。

CREATE TRIGGER tri_no_upd_student
    ON Student
    AFTER UPDATE
AS
BEGIN
    PRINT '不能修改学生表中任意字段';
    ROLLBACK TRANSACTION
END

5、在 student 数据库中,为“学生”表建立一个名为 del_xs 的 DELETE触发器,其作用是当删除“学生”表中的记录时,同时删除“选课表”
表中与该“学生”表相关的记录。

CREATE TRIGGER del_xs
    ON Student
    AFTER DELETE
AS
    DELETE FROM Sc WHERE Sno in (
        SELECT Sno FROM deleted
    );

6、修改上述3中建立在“Student ”表上的触发器tri_no_updSname_student,使其不能修改“性别”字段的值。

ALTER TRIGGER  tri_no_updSname_student
    ON Student
    AFTER DELETE
AS
    IF(UPDATE(Sno) OR UPDATE(Ssex))
    BEGIN
        PRINT '不能修改学生姓名或性别';
        ROLLBACK TRANSACTION
    END

7、为“Student”表建立一个名为 ins_SS 的 INSERT 触发器,其作用是当在“Student”表中插入一条新记录时,同时在“SS”表中自动添加相关的任课记录。

CREATE TRIGGER ins_SS
    ON Student
    AFTER INSERT
AS
    INSERT INTO SS (Sno, Sname, Ssex)
        SELECT Sno, Sname, Ssex FROM inserted;

8、在 SchoolManagement 数据库范围内,创建一个触发器,禁止删除或修改数据库中的任何表。

CREATE TRIGGER tri_noDel_noUpd
    ON DATABASE
    FOR alter_table, drop_table
AS
BEGIN
    PRINT '不能修改或删除SchoolManager的任意表';
    ROLLBACK TRANSACTION
END

实验五 : 视图定义、查询和更新

定义视图

1、建立控制系学生的视图 AC_Stud (Sno, Sname, Sage)。

CREATE VIEW AC_stud (Sno, Sname, Sage) AS
    SELECT Sno, Sname, Sage
        FROM Student
        WHERE Sdept = 'AutomationControl';

2、建立控制系学生的视图,并要求透过该视图进行的更新操作只涉及控制
系学生 AC_Stud_2(Sno, Sname, Sage)。

CREATE VIEW AC_Stud_2 (Sno, SName, Sage) AS
    SELECT Sno, Sname, Sage
        FROM Student
        WHERE Sdept = 'AutomationControl'
        WITH CHECK OPTION;

3、建立数学系选修了 02 号课程的学生视图 MA_S02(Sno, Sname, Grade)。

CREATE VIEW MA_S02(Sno, Sname, Grade) AS
    SELECT Student.Sno, Sname, Grade
        FROM Student, Sc
        WHERE Cno = '02'
            AND Student.Sno = Sc.Sno;

4、建立数学系选修了02号课程且成绩在90分以上的学生的视图MA_S02_90(Sno, Sname, Grade)。

CREATE VIEW MA_S02_90(Sno, Sname, Grade) AS
    SELECT Student.Sno, Sname, Grade
        FROM Student, Sc
        WHERE Cno = '02'
            AND Grade > 90
            AND Student.Sno = Sc.Sno;

5、将学生的学号及他的平均成绩定义为一个视图。S_Gavg(Sno, Gavg)

CREATE VIEW S_Gavg(Sno, Gavg) AS
    SELECT Sno, AVG(Grade)
        FROM Sc
        GROUP BY Sno;

6、创建视图 AC_Stud_WCO(如下图 1), 创建视图 AC_Stud_WCO2(如下图 2)

67VIEWS.png-36.4kB

#代码如上图

查询视图

1、在控制系学生的视图中找出:(1)年龄小于 20 岁的学生;(2)选修了 02 号课程的学生学号和姓名

SELECT * FROM AC_stud WHERE Sage < 20;
SELECT AC_Stud.Sno, Sname FROM AC_stud, Sc
    WHERE Sage < 20
        AND Cno = '02'
        AND AC_stud.Sno = Sc.Sno;

2、在 S_Gavg 视图中查询平均成绩在 90 分以上的学生学号和平均成绩。

SELECT Sno, Gavg FROM S_Gavg WHERE Gavg > 50;

3、并利用视图消解法,将上述 7 中基于视图的查询,转化为基于基表的查询。

-- 消解视图的查询
SELECT Sno, Sname, Sage
    FROM Student
    WHERE Sdept = 'AutomationControl'

更新视图

1、将控制系学生视图 AC_Stud (Sno, Sname, Sage)中学号为 XXX 的学
生姓名改为“楼文武”

UPDATE AC_Stud SET Sname  = '楼文武'
    WHERE Sno = '2606618496';

2、向控制系学生视图 AC_Stud (Sno, Sname, Sage)中插入一个新的学
生记录,其中学号为 62299,姓名为普京,年龄为 50 岁。

INSERT INTO AC_Stud
        (Sno, Sname, Sage)
    VALUES
        ('62299', '普京', '50');

3、向控制系学生视图 AC_Stud_WCO 中插入一个新的学生记录,其中学号为 62299,姓名为普京,年龄为 50 岁。

INSERT INTO AC_Stud_WCO
        (Sno, Sname, Sage)
    VALUES
        ('62299', '普京', '50');
-- 上述插入是不会成功的，因为该视图有 check option 选项，而且并不能插入Sdept为信息

4、向控制系学生视图 AC_Stud_WCO2 中插入一个新的学生记录,其中学号
为 62299,姓名为普京,年龄为 50 岁。

INSERT INTO AC_Stud_WCO2
        (Sno, Sname, Sage, Sdept)
    VALUES
        ('62299', '普京', '50', 'AC');

5、向控制系学生视图 AC_Stud_WCO2 中插入一个新学生记录,其中学号为
62299,姓名为普京帝,年龄为 50 岁,数学系。

INSERT INTO AC_Stud_WCO2
        (Sno, Sname, Sage, Sdept)
    VALUES
        ('62297', '普金帝', '50', 'Math');
-- 上述插入不会成功，因为AC_Std_WCO2 视图指定with check option 但是Sdept != 'AC'

6、删除控制系学生视图 AC_Stud 中学号为 XXX 的记录

DELETE FROM AC_Stud WHERE Sno = '62298';

7、将学生的学号及他的平均成绩定义为一个视图。S_Gavg(Sno, Gavg)

77.png-20.3kB

通过实验思考:此视图可否更新的,是否可以通过“视图实体法”或“视图消解法”,转换成对相应基本表的更新?

-- 不可以的, 由于视图消解，对视图的更新最终要转化为对今本表的更新，而Gavg的存在使得其并不能转换成对基本表Sc的更新

实验六 : SQL Server 安全管理（1）

创建和修改登录账户

使用 SQL 语句创建两种登录账户,如:

创建一个名为“SqlLg”,使用 SQL Server 身份验证的登录账户,其密码为“SqlLg”,默认数据库为 DB_Security,默认语言不变。
假创建一名为“WLT”的 Windows 账户,然后将该 Windows 账户(WLT)映射为一个使用 Windows 身份验证的 SQL Server 登录账户,默认数据库为 DB_Security,默认语言不变。【此题的完成分两步:(1)打开“计算机管理”对话框,创建一个新的 Windows 用户 WLT;(2)使用系统存储过程 sp_grantlogin 将一个 Windows 系统账户映射为一个使用 Windows 身份验证的 SQL Server 登录账户。】

EXEC sp_addlogin
    @loginame = 'SqlLg',
    @passwd = 'SqlLg',
    @defdb = 'DB_Security';
EXEC sp_grantlogin
    @loginame = 'ACM-PC\WLT';

禁止或删除登录账户

a. 使用 SQL 语句禁止 Windows 身份验证的登录账户,如: 使用 SQL 语句,禁止 Windows 身份验证的登录账户'XXX\WLT'。

EXEC sp_denylogin @loginame = 'ACM-PC\WLT';

b. 使用 SQL 语句删除登录账户,如: 使用 SQL 语句删除 Windows 身份验证的登录账户“'XXX\WLT'”和 SQL Server 身份验证的登录账户“SqlLg”

24.png-369kB

EXEC sp_droplogin @loginame = 'SqlLg';
EXEC sp_revokelogin @loginame = 'ACM-PC\WLT';

指定服务器角色和取消服务器角色

固定的服务器角色是在服务器安全模式中定义的管理员组,它们的管理工作与数据库无关。SQL Server 在安装后给定了几个固定的服务器角色,具有固定的权限。

32.png-201.4kB

如: 使用 SQL 语句,为 Windows 身份验证的登录账户“XXX\WLT”和 SQL Server 身份验证的登录账户“SqlLg”,指定磁盘管理员的服务器角色 diskadmin。完成后再取消该角色。

-- 指定服务器角色
EXEC sp_addsrvrolemember
    @loginame = 'ACM-PC\WLT',
    @rolename = 'diskadmin';
EXEC sp_addsrvrolemember
    @loginame = 'SqlLg',
    @rolename = 'diskadmin';
-- 取消该角色
EXEC sp_dropsrvrolemember
    @loginame = 'ACM\WLT',
    @rolename = 'disklogin';
EXEC sp_dropsrvrolemember
    @loginname = 'SqlLg',
    @rolename = 'disklogin';

添加数据库用户

使用 sp_grantdbaccess 添加数据库用户,如: 使用 SQL 语句,为 Windows 身份验证的登录账户“‘xgc17\WLT’”和 SQL Server 身份验证的登录账户“SqlLg”,在数据库 DB_Security 中分别建立用户名“test” 和“SqlLg”。

EXEC sp_grantdbaccess
    @loginame = 'ACM-PC\WLT',
    @name_in_db = 'test';
EXEC sp_grantdbaccess
    @login = 'SqlLg',
    @name_in_db = 'SqlLg';

修改数据库用户

固定的服务器角色 :
41.png-504.1kB
如:
使用 SQL 语句,为数据库用户“test”指定固定的数据库角色 db_accessadmin。完成后再取消该角色。

-- 指定数据库角色
EXEC sp_addrolemember
    @rolename = 'db_accessadmin',
    @membername = 'test';
--取消角色
EXEC sp_droprolemember
    @rolename = 'db_accesslogin',
    @membername = 'test';

删除数据库用户

使用 sp_revokedbaccess 删除数据库用户,如: 使用 SQL 语句,删除用户“SqlLg”。

EXEC sp_revokedbaccess @name_in_db = 'SqlLg';

数据库用户角色的创建与删除

a. 使用 sp_addrole 创建数据库角色,如: 使用系统存储过程 sp_addrole,在数据库 DB_Security 中,添加名为“role2”
的数据库角色。
b. 使用 sp_droprole 创建数据库角色,如: 使用系统存储过程 sp_droprole,在数据库 DB_Security 中,删除名为“role2”的数据库角色

use DB_Security;
-- 创建
EXEC sp_addrole @rolename = 'role1';
-- 删除
EXEC sp_droprole @rolename = 'role1';

增加和删除数据库角色成员

用 SQL 语句增加或删除数据库角色成员

-- 增加
exec sp_addrolemember @rolename = 'role1',
    @membername = 'sqlLg';
-- 删除
exec sp_droprolemember @rolename = 'role1',
    @membername = 'SqlLg';

实验七 : SQL 的安全授权功能

授权:GRANT

回收权限:REVOKE

禁止权限:DENY

1、把查询 Student 表权限授给用户 U1。

use schoolManager;
GRANT SELECT ON Student TO U1;
REVOKE SELECT ON Student FROM U1;
DENY SELECT ON Student TO U1;

2、把对 Student 表和 Course 表的全部权限授予用户 U2 和 U3。

GRANT ALL ON Student TO U2, U3;
GRANT ALL ON Course TO U2, U3;
REVOKE ALL ON Student FROM U2, U3;
REVOKE ALL ON Student FROM U2, U3;
DENY ALL ON Student TO U2, U3;
DENY ALL ON Course TO U2, U3;

3、把对表 SC 的查询权限授予所有用户。

GRANT SELECT ON Sc TO public;
REVOKE SELECT ON Sc FROM public;
DENY SELECT ON Sc TO public;

4、把查询 Student 表的权限授给用户 U4

GRANT SELECT ON Student TO U4;
REVOKE SELECT ON Student FROM U4;
DENY SELECT ON Student TO U4;

5、把修改学生姓名的权限授给用户 U5。

GRANT UPDATE(Sname) ON Student TO U5;
GRANT SELECT ON Studnet TO U5;
REVOKE UPDATE(Sname) ON Student FROM U5;
REVOKE SELECT ON Student FROM U5;
DENY UPDATE(Sname) ON Student TO U5;
-- 注意 : 不授予查询权限就没有办法使用 where 选项，故还要授予U5 select权限, 回收的时候也要，但是拒绝的时候不需要拒绝select。

6、把对表 SC 的 INSERT 权限授予 U6 用户,并允许 U6 用户再将此权限授
予其他用户。

GRANT INSERT ON Sc TO U6 WITH GRANT OPTION;
REVOKE INSERT ON Sc FROM U6 FROM U6 CASCADE;
DENY INSERT ON Sc TO U6 CASCADE;

7、把对表 SC 的记录删除权限授予 U7 用户,并允许 U7 用户再将此权限授
予其他用户。

GRANT DELETE ON Sc TO U7 WITH GRANT OPTION;
REVKE DELETE ON Sc FROM U7 CASCADE;
DENY DELETE ON Sc TO U7 CASCADE;

8、把创建、修改和删除表的权限,授予用户 U8。

GRANT CREATE TABLE, ALTER, DELETE TO U8;
REVOKE CREATE TABLE, ALTER, DELETE FROM U8;
DENY CREATE TABLE, ALTER, DELETE TO U8;

1、把上述对上述用户,分别禁止其拥有相应的权限。
a) 授权前用户是否可以查询? --- 不能
b) 如果不可以查询,授权后是否可以查询? --- 可以
c) 如果可以查询,回收权限后是不是不能再查询? --- 是的

实验八 : 数据备份与恢复

数据备份类型:

a81.png-303.1kB

利用 T-SQL 语句,对 SchoolManagement 数据库进行完整备份;

BACKUP DATABASE 'SchoolManagement'
    TO DISK ='E:\mybak.db'
    WITH NOINIT;
-- 因为是第一次备份，不需要覆盖，在之后的备份中改为with init 则在mybak.db中的数据会被覆盖重写

完整备份结束后,修改 SchoolManagement 数据库,然后用 T-SQL 语句对该数据库进行差异化备份。

--修改操作，如 update, delete， insert 等
BACKUP DATABASE 'SchoolManagement'
    TO DISK = 'E:\mybak2.db'
    WITH DIFFERENTIAL, NOINIT;

用 T-SQL 语句实现一个备份计划

概论

其实创建作业并不是一个很难的事，只是命令繁多，不太容易记住。所以这里针对要考试的内容尽量精简指令，减去所有不必要参数，只给出必要的参数。

首先要说明的是，创建一个完整的作业需要存储在msdb.dbo数据库中的3个存储过程，如下(均为官方文档链接) ：

msdb.dbo.sp_add_job

msdb.dbo.sp_add_jobstep

msdb.dbo.sp_add_schedule

sp_add_job

这个命令我们能用到的只有两个参数 :

@job_name. 就像创建其他的存储过程、触发器打创建一样，一个名字是必须的。

@job_id. 这个是一个uniqueidentifier 类型的变量，而且是 sp_add_job 的输出参数，是为了获得创建出的作业在系统中打唯一id标识码

sp_add_jobstep

这个命令是为了创建作业中的步骤用的, 我们能用到的参数只有4个:

job_id. 创建出的作业步骤要加入到相应的作业中去，这个job_id 就是标识那个作业用的;

@step_name. 步骤的名字，一个名字是必须的;

@database_name. 要在其中执行 Transact-SQL 步骤的数据库的名称。比如说我们的步骤是向某个数据库中插入信息，那么必须先指定数据库。只是这里要做的是备份。

@command. 这个是sp_add_jobstep存储过程中最重要的一步。表示这个步骤要执行的代码是什么。

sp_add_jobschedule

这个命令就是为了创建一个作业的执行计划。这个存储过程中的参数是最多最繁琐的，但是在我们要创建的三个备份计划其实不需要很多。所以具体问题具体分析，针对每一个备份计划做讲解。

但还是要提前说明一些必要的模块 :

job_id. 标识该计划对应的作业;

name. 名字是必须的;

执行日期(date)模块. 主要由@freq_type, @freq_interval, @ freq_recurrence_factor三个组成。但是在每个计划中并不会全部用到，juice问题具体分析中会有。

开始和结束日期模块. 主要由 @active_start_date， @active_end_date两块组成，分别表示开始时间和结束时间。

执行时间(time)模块. 通俗地讲就是手表上的时间。主要由@freq_subday_type, @freq_subday_interval两个部分组成，同样也不是每个计划中都会用到。具体问题具体分析中会有。
开始和结束时间. 主要由 active_start_time, active_end_time 两个模块组成，分别表示开始时间和结束时间。类似于上班的朝九晚五，表示每天什么时候开始工作，说明时候结束工作。

以上五个模块是一个计划执行时间表的结构，掌握这四个结构使用起来就会很简单。重点是一定要分清 日期(date) 和 时间(time) 的区别。

sp_add_jobserver

指定目标服务器，这个步骤必须有，不然创建出的作业也不会执行。

代码是固定的两个参数

job_id, 同上

serve_name, 指定服务器名字，选择本机则可以写 @server_name = 'local';

备份计划创建举例

每周一次的完整备份

use master;
go
-- //声明@jobId 变量，用于sp_add_job的输出; 声明@jobCommend变量，用于定义sp_add_jobstep中要执行打T-SQL命令。
declare @jobId uniqueidentifier,
    @jobCommand varchar(2000);
--// 给@jobCommand变量赋值，里面是最主要的是备份命令backup，其他的变量也很好理解
--// @backupTime表示备份的时间，用于唯一标识每一个备份文件
--// @backupDisk 表示备份文件磁盘的名字，其实指的是在文件系统中的名字，这里取名的方式是 '位置' + '时间' + '.bak'后缀。
--// @backupName 表示备份的名字，创建一个东西名字是必须的嘛。取名方式很好理解 : 'full backup of SchoolManager_' + '时间'
--// 字符串中的每一个单引号都需要写两次防止认为是字符串结尾，就像转义字符一样。
set @jobCommand = 'declare @backupName varchar(200),
        @backupDisk varchar(200),
        @backupTime varchar(200);
    set @backupTime = CONVERT(varchar(20), GETDATE(), 112);
    set @backupDisk = ''E:\bak\SchoolManagerBackDisk'' + @backupTime + ''.bak'';
    set @backupName = ''full backup of SchoolManager_'' + @backupTime;
    backup database SchoolManager to
        disk = @backupDisk with noinit,
        name = @backupName;';
--// 创建作业，两个参数
exec msdb.dbo.sp_add_job
    @job_name = 'weekBackUp',
    @job_id = @jobId output;
--// 创建作业步骤，四个参数
exec msdb.dbo.sp_add_jobstep
    @job_id = @jobId,
    @step_name = 'weekFullBackup',
    @database_name = 'SchoolManager',
    @command = @jobCommand;
--// 创建作业执行计划时间表,这里除去job_id和name模块;
--// 日期模块三个参数都需要，分别表示 每周， 每周的星期几，每几个星期执行一次;
--// 因为每次只执行一次，所以不需要指定时间模块;
--// 开始和结束日期模块，因为永久执行所以不必指定结束日期
--// 开始和结束时间模块， 因为只执行一次，所以不指定结束时间
    @job_id = @jobId, 
    @name = 'weekBackupSchedule',
    @freq_type = 8, --// 8表示每周
    @freq_interval = 1, --// 1表示每个星期天，2 ~ 7 表示周一到周六
    @freq_recurrence_factor = 1, --// 表示每一周都执行一次
    @active_start_date = NULL, --// NULL 表示填充当前时间
    @active_start_time = 000000; --// 每次执行时打开始时间，这里表示午夜零点;
--// 最后指定服务器为本机
exec msdb.dbo.sp_add_jobserver
    @job_id = @jobId,
    @server_name = '(local)';

每天一次的部分备份

use master;
go
--//这一部分和上面是一样的，不做赘述
declare @jobId uniqueidentifier,
    @jobCommand varchar(2000);
--// 同上，但是注意备份方式已经改成with differential表示差异备份
set @jobCommand = 'declare @backupDisk varchar(200),
        @backupTime varchar(200),
        @backupName varchar(200);
    set @backupTime = CONVERT(varchar(200), GETDATE(), 112);
    set @backupDisk = ''E:\daybak\partBackup'' + @backupTime + ''.bak'';
    set @backupName = ''day part backup of schoolManager_'' + @backupTime;
    backup database SchoolManager with differential, noinit;';
--// 同上
exec msdb.dbo.sp_add_job
    @job_name = 'dayBackup',
    @job_id = @jobId output;
--// 同上
exec msdb.dbo.sp_add_jobstep
    @job_id = @jobId,
    @step_name = 'dayDifBackup',
    @database_name = 'SchoolManager',
    @command = 'exec dayBackup';
--// @job_id 和 @name 模块不做赘述
--// 执行日期模块，只需要两个参数freq_type 和 freq_interval, 分别表示时间类型是每天，每隔几天执行 (freq_recurrence_factor只用于每周每月不能用于每天)
--// 同样每一次只执行一次所以不用指定时间模块
--// 开始和结束日期模块不指定结束时间表示永久
--// 开始和结束时间米跨不指定结束时间表示只执行一次
exec msdb.dbo.sp_add_jobschedule
    @job_id = @jobId,
    @name = 'dayBackupSchedule',
    @freq_type = 4, --// 为4表示每天
    @freq_interval = 1, --// 为1表示每一天都执行
    @active_start_date = NULL, --//NULL填充当前时间
    @active_start_time = 230000; --// 每一次执行时间是23:00:00
-- // 指定服务器
exec msdb.dbo.sp_add_jobserver
    @job_id = @jobId,
    @server_name = N'(local)';

每两小时一次日志备份

use master
go
--// 同上
declare @jobid uniqueidentifier,
    @sql nvarchar(2000);
--// 意义等同上，但是最后的backup命令要改成日志备份的相应命令
set @sql = 'declare @backupName varchar(200),
        @backupDisk varchar(200),
        @backupTime varchar(200);
    set @backupTime = CONVERT(varchar(20), GETDATE(), 112);
    set @backupDisk = ''E:\bak\logDisk'' + @backupTime + ''.bak'';
    set @backupName = ''log backup of SchoolManager_'' + @backupTime;
    backup log SchoolManager
        to Disk = @backupdisk with NO_Truncate;';
exec msdb.dbo.sp_add_job @job_name = 'hourlogbackup',
    @job_id = @jobid output
exec msdb.dbo.sp_add_jobstep
    @job_id = @jobid,
    @step_name = 'hourlogbackup',
    @command = @sql;
-- // 日期上选择每天执行，同上
-- // 时间(time)上选择每两小时一次。所以设置@freq_subday_typy 为 8确定为小时，@freq_subday_instrval 为 2 表示间隔两小时。 
-- //仍然只需要指定开始日期，不指定结束日期
-- // 因为每隔两小时执行一次要重复一整天，故也不需要指定结束时间
exec msdb.dbo.sp_add_jobschedule
    @job_id = @jobid,
    @name = 'hourbackupschedule',
    @freq_type = 4, --// 指定date单位为天
    @freq_interval = 1, --// 指定每一天都执行
    @freq_subday_type = 8, --//指定每天执行的时间单位为hour，当该值为4时执行单位为minute
    @freq_subday_interval = 2,  --// 没两小时执行一次
    @active_start_date = NULL, --//指定开始日期
    @active_start_time = 000000; --//指定开始时间
--// 指定服务器为本机
exec msdb.dbo.sp_add_jobserver
    @job_id = @jobId,
    @server_name = N'(local)';

d) 利用备份,进行数据库的完整恢复。

RESTORE DATABASE 'SchoolManagement' FROM DISK='E:\mybak.bak';

以上です～