数据库实验汇总

@ArrowLLL 2017-05-09 17:33 字数 18561 阅读 1064

数据库实验汇总 4.0

T-SQL 数据库


Elon Lin


实验一:存储过程

参考: SQL Server 存储过程

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

   
  1. CREATE PROC SCinfo_InfoSec
  2. AS
  3. SELECT Student.Sno, Cno, Grade
  4. FROM Student,Sc WHERE
  5. Sdept = 'InfomationSecurity'
  6. AND Student.Sno = Sc.Sno;

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

   
  1. CREATE PROC SCinfo_dept_sex (
  2. @deptName varchar(20),
  3. @sex char(1)
  4. ) AS
  5. SELECT Student.Sno, Cno, Grade
  6. FROM Student,Sc WHERE
  7. Sdept = @deptName
  8. AND Ssex = @sex
  9. AND Student.Sno = SC.Sno;

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

   
  1. CREATE PROC SCinfo_dept_sexDefMale (
  2. @deptName varchar(20),
  3. @sex char(1) = 'M'
  4. ) AS
  5. SELECT Student.Sno, Cno, Grade
  6. FROM Student, Sc WHERE
  7. Sdept = @deptName
  8. AND Ssex = @sex
  9. ;

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

   
  1. CREATE PROC Sdeptinfo_Sno (
  2. @stuNo char(10)
  3. ) AS
  4. BEGIN
  5. DECLARE @numStu int;
  6. SELECT @numStu = COUNT(*) FROM Student
  7. WHERE Sno = @stuNo;
  8. IF @numStu > 0
  9. SELECT @stuNo AS '学号',
  10. '存在' AS '该学号是否存在',
  11. COUNT(*) AS '所在系共有学生人数'
  12. FROM Student
  13. WHERE Sdept in (
  14. SELECT Sdept FROM Student
  15. WHERE Sno = @stuNo
  16. );
  17. ELSE
  18. SELECT @stuNo AS '学号',
  19. '不存在' AS '该学号是否存在';
  20. END

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

   
  1. CREATE PROC SCinfo_Sno (
  2. @stuNo char(10)
  3. ) AS
  4. BEGIN
  5. DECLARE @numStu int;
  6. SELECT @numStu = COUNT(*) FROM Student
  7. WHERE Sno = @stuNo;
  8. IF @numStu > 0
  9. SELECT @stuNo AS '学号',
  10. '存在' AS '该学号是否存在',
  11. COUNT(*) AS '选择的课程总数',
  12. AVG(Grade) AS '所有课程平均成绩'
  13. FROM Sc WHERE Sno = @stuNo;
  14. ELSE
  15. SELECT @stuNo AS '学号',
  16. '不存在' AS '该学号是否存在';
  17. END

实验二 : 存储过程进阶

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

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

   
  1. CREATE PROC SCinfo_Sno_enc(
  2. @stuNo char(10)
  3. ) WITH ENCRYPTION AS
  4. BEGIN
  5. DECLARE @numStu int;
  6. SELECT @numStu = COUNT(*) FROM Student
  7. WHERE Sno = @stuNo;
  8. IF @numStu > 0
  9. SELECT @stuNo as '学号',
  10. '存在' as '学号是否存在',
  11. COUNT(*) as '课程数',
  12. AVG(Grade) as '平均成绩'
  13. FROM Sc
  14. WHERE Sno = @stuNo;
  15. ELSE
  16. SELECT @stuNo as '学号',
  17. '不存在' as '学号是否存在';
  18. END

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

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

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

   
  1. CREATE PROC Deptinfo_Sno (
  2. @StuNo char(10)
  3. ) WITH ENCRYPTION AS
  4. BEGIN
  5. DECLARE @numSno int;
  6. SELECT @numSno = COUNT(*) FROM Student
  7. WHERE Sno = @stuNo;
  8. IF (@numSno > 0)
  9. BEGIN
  10. PRINT '学号' + @stuNo + '存在';
  11. SELECT Sno,
  12. COUNT(*) AS '课程总数',
  13. AVG(Grade) AS '平均成绩'
  14. FROM SC
  15. WHERE Sno in (
  16. SELECT Sno FROM Student
  17. WHERE Sdept in (
  18. SELECT Sdept FROM Student
  19. WHERE Sno = @stuNo
  20. )
  21. )
  22. GROUP BY Sno
  23. ORDER BY Sno;
  24. END
  25. ELSE
  26. PRINT '学号 ' + @stuNo + ' 不存在';
  27. END

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

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

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

   
  1. CREATE PROC DeleteInfo_stu(
  2. @stuName varchar(10)
  3. ) AS
  4. BEGIN
  5. DECLARE @numStu int;
  6. SELECT @numStu = COUNT(*) FROM Student
  7. WHERE Sname = @stuName;
  8. IF @numStu > 0
  9. BEGIN
  10. DELETE FROM Sc WHERE Sno
  11. IN (
  12. SELECT Sno FROM Student
  13. WHERE Sname = @stuName
  14. );
  15. DELETE FROM Student WHERE Sname = @stuName;
  16. END
  17. ELSE
  18. PRINT '该姓名 ' + @stuName + ' 不存在,请重新输入存在的姓名';
  19. END

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

   
  1. CREATE PROC AddGradeInSc_stu (
  2. @stuName varchar(10)
  3. ) AS
  4. BEGIN
  5. DECLARE @numStu int;
  6. SELECT @numStu = COUNT(*) FROM Student
  7. WHERE Sname = @stuName;
  8. IF @numStu > 0
  9. UPDATE Sc SET Grade = Grade + 1
  10. WHERE Sno in (
  11. SELECT Sno FROM Student
  12. WHERE Sname = @stuName
  13. );
  14. ELSE
  15. PRINT '该姓名 ' + @stuName + ' 不存在,请重新输入存在的姓名'
  16. 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段建立唯一性约束。

     
    1. CREATE TABLE Student(
    2. Sno char(10) primary key,
    3. Sname varchar(15) NOT NULL CONSTRAINT Uc_StuName UNIQUE,
    4. Ssex char(1) NOT NULL,
    5. Sage int NOT NULL,
    6. Sdept varchar(20) NOT NULL,
    7. BirthPlace varchar(20)
    8. );

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

     
    1. ALTER TABLE Student
    2. DROP CONSTRAINT Uc_StuName;

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

     
    1. ALTER TABLE Student
    2. ADD CONSTRAINT Uc_StuName UNIQUE(Sname);

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

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

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

     
    1. ALTER TABLE Course NOCHECK CONSTRAINT Chk_Credit;

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

     
    1. ALTER TABLE Course CHECK CONSTRAINT Chk_Credit;

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

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

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

     
    1. ALTER TABLE Sc DROP CONSTRAINT Default_Grade;

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

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

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

     
    1. CREATE DEFAULT sexDefault as 'M';

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

     
    1. EXEC sp_bindefault SexDefault, 'Student.Ssex';

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

     
    1. EXEC sp_unbindefault 'Student.Ssex';

    (13). 删除上述 SexDefault。

     
    1. DROP DEFAULT SexDefault;

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

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

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

     
    1. EXEC sp_bindrule SnoRule, 'Student.Sno';

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

     
    1. EXEC sp_unbindrule 'Student.Sno';

    (17). 删除上述 SnoRule。

     
    1. DROP RULE SnoRule;

    实验四 : 触发器

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

     
    1. CREATE TRIGGER up_dSno_student
    2. ON Student
    3. AFTER UPDATE
    4. AS
    5. IF UPDATE(Sno)
    6. BEGIN
    7. UPDATE Sc SET Sno = inserted.Sno
    8. FROM Sc, deleted, instered
    9. WHERE Sc.Sno = deleted.Sno;
    10. END

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

     
    1. CREATE TRIGGER tri_updCno_Course
    2. ON Course
    3. AFTER UPDATE
    4. AS
    5. IF UPDATE(Cno)
    6. BEGIN
    7. UPDATE Sc SET Sc.Sno = inserted.Sno
    8. FROM Sc, instered, deleted
    9. WHERE Sc.Sno = deleted.Sno;
    10. END

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

     
    1. CREATE TRIGGER tri_no_updSname_student
    2. ON Student
    3. AFTER UPDATE
    4. AS
    5. IF UPDATE(Sname)
    6. BEGIN
    7. PRINT '不能修改学生姓名';
    8. ROLLBACK TRANSACTION
    9. END

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

     
    1. CREATE TRIGGER tri_no_upd_student
    2. ON Student
    3. AFTER UPDATE
    4. AS
    5. BEGIN
    6. PRINT '不能修改学生表中任意字段';
    7. ROLLBACK TRANSACTION
    8. END

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

     
    1. CREATE TRIGGER del_xs
    2. ON Student
    3. AFTER DELETE
    4. AS
    5. DELETE FROM Sc WHERE Sno in (
    6. SELECT Sno FROM deleted
    7. );

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

     
    1. ALTER TRIGGER tri_no_updSname_student
    2. ON Student
    3. AFTER DELETE
    4. AS
    5. IF(UPDATE(Sno) OR UPDATE(Ssex))
    6. BEGIN
    7. PRINT '不能修改学生姓名或性别';
    8. ROLLBACK TRANSACTION
    9. END

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

     
    1. CREATE TRIGGER ins_SS
    2. ON Student
    3. AFTER INSERT
    4. AS
    5. INSERT INTO SS (Sno, Sname, Ssex)
    6. SELECT Sno, Sname, Ssex FROM inserted;

    create trigger delete_1
    on new
    for insert
    as
    begin
    delete new
    from new
    where (new.time)-(select time from inserted)>600 and new.icao=(select icao from inserted)
    end

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

     
    1. CREATE TRIGGER tri_noDel_noUpd
    2. ON DATABASE
    3. FOR alter_table, drop_table
    4. AS
    5. BEGIN
    6. PRINT ‘不能修改或删除SchoolManager的任意表’;
    7. ROLLBACK TRANSACTION
    8. END

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

    定义视图

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

     
    1. CREATE VIEW AC_stud (Sno, Sname, Sage) AS
    2. SELECT Sno, Sname, Sage
    3. FROM Student
    4. WHERE Sdept = ‘AutomationControl’;

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

    系学生 AC_Stud_2(Sno, Sname, Sage)。

     
    1. CREATE VIEW AC_Stud_2 (Sno, SName, Sage) AS
    2. SELECT Sno, Sname, Sage
    3. FROM Student
    4. WHERE Sdept = ‘AutomationControl’
    5. WITH CHECK OPTION;

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

     
    1. CREATE VIEW MA_S02(Sno, Sname, Grade) AS
    2. SELECT Student.Sno, Sname, Grade
    3. FROM Student, Sc
    4. WHERE Cno = ‘02’
    5. AND Student.Sno = Sc.Sno;

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

     
    1. CREATE VIEW MA_S02_90(Sno, Sname, Grade) AS
    2. SELECT Student.Sno, Sname, Grade
    3. FROM Student, Sc
    4. WHERE Cno = ‘02’
    5. AND Grade > 90
    6. AND Student.Sno = Sc.Sno;

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

     
    1. CREATE VIEW S_Gavg(Sno, Gavg) AS
    2. SELECT Sno, AVG(Grade)
    3. FROM Sc
    4. GROUP BY Sno;

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

    67VIEWS.png-36.4kB

     
    1. #代码如上图

    查询视图

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

     
    1. SELECT * FROM AC_stud WHERE Sage < 20;
    2. SELECT AC_Stud.Sno, Sname FROM AC_stud, Sc
    3. WHERE Sage < 20
    4. AND Cno = ‘02’
    5. AND AC_stud.Sno = Sc.Sno;

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

     
    1. SELECT Sno, Gavg FROM S_Gavg WHERE Gavg > 50;

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

     
    1. 消解视图的查询
    2. SELECT Sno, Sname, Sage
    3. FROM Student
    4. WHERE Sdept = 'AutomationControl’

    更新视图

    1、 将控制系学生视图 AC_Stud (Sno, Sname, Sage)中学号为 XXX 的学

    生姓名改为“楼文武”

     
    1. UPDATE AC_Stud SET Sname = ‘楼文武’
    2. WHERE Sno = ‘2606618496’;

    2、 向控制系学生视图 AC_Stud (Sno, Sname, Sage)中插入一个新的学

    生记录,其中学号为 62299,姓名为普京,年龄为 50 岁。

     
    1. INSERT INTO AC_Stud
    2. (Sno, Sname, Sage)
    3. VALUES
    4. (‘62299’, ‘普京’, ‘50’);

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

     
    1. INSERT INTO AC_Stud_WCO
    2. (Sno, Sname, Sage)
    3. VALUES
    4. (‘62299’, ‘普京’, ‘50’);
    5. 上述插入是不会成功的,因为该视图有 check option 选项,而且并不能插入Sdept为信息

    4、 向控制系学生视图 AC_Stud_WCO2 中插入一个新的学生记录,其中学号

    为 62299,姓名为普京,年龄为 50 岁。

     
    1. INSERT INTO AC_Stud_WCO2
    2. (Sno, Sname, Sage, Sdept)
    3. VALUES
    4. (‘62299’, ‘普京’, ‘50’, ‘AC’);

    5、 向控制系学生视图 AC_Stud_WCO2 中插入一个新学生记录,其中学号为

    62299,姓名为普京帝,年龄为 50 岁,数学系。

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

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

     
    1. DELETE FROM AC_Stud WHERE Sno = ‘62298’;

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

    77.png-20.3kB

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

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

    实验六 : SQL Server 安全管理(1)

    创建和修改登录账户

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

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

      禁止或删除登录账户

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

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

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

      24.png-369kB

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

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

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

      32.png-201.4kB

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

       
      1. -- 指定服务器角色
      2. EXEC sp_addsrvrolemember
      3. @loginame = 'ACM-PC\WLT',
      4. @rolename = 'diskadmin';
      5. EXEC sp_addsrvrolemember
      6. @loginame = 'SqlLg',
      7. @rolename = 'diskadmin';
      8. -- 取消该角色
      9. EXEC sp_dropsrvrolemember
      10. @loginame = 'ACM\WLT',
      11. @rolename = 'disklogin';
      12. EXEC sp_dropsrvrolemember
      13. @loginname = 'SqlLg',
      14. @rolename = 'disklogin';

      添加数据库用户

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

       
      1. EXEC sp_grantdbaccess
      2. @loginame = 'ACM-PC\WLT',
      3. @name_in_db = 'test';
      4. EXEC sp_grantdbaccess
      5. @login = 'SqlLg',
      6. @name_in_db = 'SqlLg';

      修改数据库用户

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

       
      1. -- 指定数据库角色
      2. EXEC sp_addrolemember
      3. @rolename = 'db_accessadmin',
      4. @membername = 'test';
      5. --取消角色
      6. EXEC sp_droprolemember
      7. @rolename = 'db_accesslogin',
      8. @membername = 'test';

      删除数据库用户

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

       
      1. EXEC sp_revokedbaccess @name_in_db = 'SqlLg';

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

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

       
      1. use DB_Security;
      2. -- 创建
      3. EXEC sp_addrole @rolename = 'role1';
      4. -- 删除
      5. EXEC sp_droprole @rolename = 'role1';

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

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

       
      1. -- 增加
      2. exec sp_addrolemember @rolename = 'role1',
      3. @membername = 'sqlLg';
      4. -- 删除
      5. exec sp_droprolemember @rolename = 'role1',
      6. @membername = 'SqlLg';

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

      授权:GRANT

      回收权限:REVOKE

      禁止权限:DENY

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

       
      1. use schoolManager;
      2. GRANT SELECT ON Student TO U1;
      3. REVOKE SELECT ON Student FROM U1;
      4. DENY SELECT ON Student TO U1;

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

       
      1. GRANT ALL ON Student TO U2, U3;
      2. GRANT ALL ON Course TO U2, U3;
      3. REVOKE ALL ON Student FROM U2, U3;
      4. REVOKE ALL ON Student FROM U2, U3;
      5. DENY ALL ON Student TO U2, U3;
      6. DENY ALL ON Course TO U2, U3;

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

       
      1. GRANT SELECT ON Sc TO public;
      2. REVOKE SELECT ON Sc FROM public;
      3. DENY SELECT ON Sc TO public;

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

       
      1. GRANT SELECT ON Student TO U4;
      2. REVOKE SELECT ON Student FROM U4;
      3. DENY SELECT ON Student TO U4;

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

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

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

       
      1. GRANT INSERT ON Sc TO U6 WITH GRANT OPTION;
      2. REVOKE INSERT ON Sc FROM U6 FROM U6 CASCADE;
      3. DENY INSERT ON Sc TO U6 CASCADE;

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

       
      1. GRANT DELETE ON Sc TO U7 WITH GRANT OPTION;
      2. REVKE DELETE ON Sc FROM U7 CASCADE;
      3. DENY DELETE ON Sc TO U7 CASCADE;

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

       
      1. GRANT CREATE TABLE, ALTER, DELETE TO U8;
      2. REVOKE CREATE TABLE, ALTER, DELETE FROM U8;
      3. DENY CREATE TABLE, ALTER, DELETE TO U8;

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

      实验八 : 数据备份与恢复

      数据备份类型:

      a81.png-303.1kB

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

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

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

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

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

      概论

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

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

      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';

      备份计划创建举例

      每周一次的完整备份
       
      1. use master;
      2. go
      3. -- //声明@jobId 变量,用于sp_add_job的输出; 声明@jobCommend变量,用于定义sp_add_jobstep中要执行打T-SQL命令。
      4. declare @jobId uniqueidentifier,
      5. @jobCommand varchar(2000);
      6. --// 给@jobCommand变量赋值,里面是最主要的是备份命令backup,其他的变量也很好理解
      7. --// @backupTime表示备份的时间,用于唯一标识每一个备份文件
      8. --// @backupDisk 表示备份文件磁盘的名字,其实指的是在文件系统中的名字,这里取名的方式是 '位置' + '时间' + '.bak'后缀。
      9. --// @backupName 表示备份的名字,创建一个东西名字是必须的嘛。取名方式很好理解 : 'full backup of SchoolManager_' + '时间'
      10. --// 字符串中的每一个单引号都需要写两次防止认为是字符串结尾,就像转义字符一样。
      11. set @jobCommand = 'declare @backupName varchar(200),
      12. @backupDisk varchar(200),
      13. @backupTime varchar(200);
      14. set @backupTime = CONVERT(varchar(20), GETDATE(), 112);
      15. set @backupDisk = ''E:\bak\SchoolManagerBackDisk'' + @backupTime + ''.bak'';
      16. set @backupName = ''full backup of SchoolManager_'' + @backupTime;
      17. backup database SchoolManager to
      18. disk = @backupDisk with noinit,
      19. name = @backupName;';
      20. --// 创建作业,两个参数
      21. exec msdb.dbo.sp_add_job
      22. @job_name = 'weekBackUp',
      23. @job_id = @jobId output;
      24. --// 创建作业步骤,四个参数
      25. exec msdb.dbo.sp_add_jobstep
      26. @job_id = @jobId,
      27. @step_name = 'weekFullBackup',
      28. @database_name = 'SchoolManager',
      29. @command = @jobCommand;
      30. --// 创建作业执行计划时间表,这里除去job_id和name模块;
      31. --// 日期模块三个参数都需要,分别表示 每周, 每周的星期几,每几个星期执行一次;
      32. --// 因为每次只执行一次,所以不需要指定时间模块;
      33. --// 开始和结束日期模块,因为永久执行所以不必指定结束日期
      34. --// 开始和结束时间模块, 因为只执行一次,所以不指定结束时间
      35. @job_id = @jobId,
      36. @name = 'weekBackupSchedule',
      37. @freq_type = 8, --// 8表示每周
      38. @freq_interval = 1, --// 1表示每个星期天,2 ~ 7 表示周一到周六
      39. @freq_recurrence_factor = 1, --// 表示每一周都执行一次
      40. @active_start_date = NULL, --// NULL 表示填充当前时间
      41. @active_start_time = 000000; --// 每次执行时打开始时间,这里表示午夜零点;
      42. --// 最后指定服务器为本机
      43. exec msdb.dbo.sp_add_jobserver
      44. @job_id = @jobId,
      45. @server_name = '(local)';
      每天一次的部分备份
       
      1. use master;
      2. go
      3. --//这一部分和上面是一样的,不做赘述
      4. declare @jobId uniqueidentifier,
      5. @jobCommand varchar(2000);
      6. --// 同上,但是注意备份方式已经改成with differential表示差异备份
      7. set @jobCommand = 'declare @backupDisk varchar(200),
      8. @backupTime varchar(200),
      9. @backupName varchar(200);
      10. set @backupTime = CONVERT(varchar(200), GETDATE(), 112);
      11. set @backupDisk = ''E:\daybak\partBackup'' + @backupTime + ''.bak'';
      12. set @backupName = ''day part backup of schoolManager_'' + @backupTime;
      13. backup database SchoolManager with differential, noinit;';
      14. --// 同上
      15. exec msdb.dbo.sp_add_job
      16. @job_name = 'dayBackup',
      17. @job_id = @jobId output;
      18. --// 同上
      19. exec msdb.dbo.sp_add_jobstep
      20. @job_id = @jobId,
      21. @step_name = 'dayDifBackup',
      22. @database_name = 'SchoolManager',
      23. @command = 'exec dayBackup';
      24. --// @job_id 和 @name 模块不做赘述
      25. --// 执行日期模块,只需要两个参数freq_type 和 freq_interval, 分别表示时间类型是每天,每隔几天执行 (freq_recurrence_factor只用于每周每月不能用于每天)
      26. --// 同样每一次只执行一次所以不用指定时间模块
      27. --// 开始和结束日期模块不指定结束时间表示永久
      28. --// 开始和结束时间米跨不指定结束时间表示只执行一次
      29. exec msdb.dbo.sp_add_jobschedule
      30. @job_id = @jobId,
      31. @name = 'dayBackupSchedule',
      32. @freq_type = 4, --// 为4表示每天
      33. @freq_interval = 1, --// 为1表示每一天都执行
      34. @active_start_date = NULL, --//NULL填充当前时间
      35. @active_start_time = 230000; --// 每一次执行时间是23:00:00
      36. -- // 指定服务器
      37. exec msdb.dbo.sp_add_jobserver
      38. @job_id = @jobId,
      39. @server_name = N'(local)';
      每两小时一次日志备份
       
      1. use master
      2. go
      3. --// 同上
      4. declare @jobid uniqueidentifier,
      5. @sql nvarchar(2000);
      6. --// 意义等同上,但是最后的backup命令要改成日志备份的相应命令
      7. set @sql = 'declare @backupName varchar(200),
      8. @backupDisk varchar(200),
      9. @backupTime varchar(200);
      10. set @backupTime = CONVERT(varchar(20), GETDATE(), 112);
      11. set @backupDisk = ''E:\bak\logDisk'' + @backupTime + ''.bak'';
      12. set @backupName = ''log backup of SchoolManager_'' + @backupTime;
      13. backup log SchoolManager
      14. to Disk = @backupdisk with NO_Truncate;';
      15. exec msdb.dbo.sp_add_job @job_name = 'hourlogbackup',
      16. @job_id = @jobid output
      17. exec msdb.dbo.sp_add_jobstep
      18. @job_id = @jobid,
      19. @step_name = 'hourlogbackup',
      20. @command = @sql;
      21. -- // 日期上选择每天执行,同上
      22. -- // 时间(time)上选择每两小时一次。所以设置@freq_subday_typy 为 8确定为小时,@freq_subday_instrval 为 2 表示间隔两小时。
      23. -- //仍然只需要指定开始日期,不指定结束日期
      24. -- // 因为每隔两小时执行一次要重复一整天,故也不需要指定结束时间
      25. exec msdb.dbo.sp_add_jobschedule
      26. @job_id = @jobid,
      27. @name = 'hourbackupschedule',
      28. @freq_type = 4, --// 指定date单位为天
      29. @freq_interval = 1, --// 指定每一天都执行
      30. @freq_subday_type = 8, --//指定每天执行的时间单位为hour,当该值为4时执行单位为minute
      31. @freq_subday_interval = 2, --// 没两小时执行一次
      32. @active_start_date = NULL, --//指定开始日期
      33. @active_start_time = 000000; --//指定开始时间
      34. --// 指定服务器为本机
      35. exec msdb.dbo.sp_add_jobserver
      36. @job_id = @jobId,
      37. @server_name = N'(local)';

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

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

      以上です~

      +
      +
      +
      +
      +
      +
      +
    ©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页