《数据库概论》实验指导书
DELETE
FROM Sno_grade
思考：删除一个表中的全部元组后，该表的定义是否被同时删除？ （8）带子查询的删除语句
练习10：删除计算机系所有学生的选课记录。 DELETE FROM SC WHERE 'CS'= (SELECT Sdept FROM Student
WHERE Student.Sno = SC.Sno); 扩展练习
首先执行S_T.sql，重建示例数据库S_T。
然后写出并执行SQL语句完成以下各种操作，记录查询结果。
（1）在Student表中插入计算机系的两个新生元组：（学号：200215180；姓名：王新宇；性别：男；年龄：19岁），（学号：200215181；姓名：丛欣然；性别：女；年龄：18岁）。 （2）对每一门课，求其选课人数，并把结果（课程号，选课人数）存入新表Cno_Number。 （3）将计算机系CS的所有学生的系别改为软件工程系SE。 （4）将软件工程系SE的全体学生的成绩统一减去2分。 （5）删除学号为200215180的学生记录。
（6）将信息系IS所有学生的成绩置为空值（非0值）。 （7）将所有成绩为空值的选课记录删除掉。
实验四SQL的数据视图（2学时）
1、实验目的
(1) 掌握DBMS的数据查询功能 (2) 掌握SQL语言的视图功能
2、实验内容
(1) 创建表的视图
(2) 利用视图完成表的查询 (3) 删除表的视图
3、实验要求
(1) 掌握视图的定义 (2) 掌握对视图的操作 (3) 写出实验报告
4、实验步骤
4.1 执行S_T.sql，建立示例数据库S_T。
4.2 对学生关系Student、课程关系Course和选修关系SC进行视图的定义（创建）、查询、更新、删除等操作。 基本练习
（1）创建视图
练习1：创建信息系学生的视图。 CREATE VIEW IS_Student AS
SELECT Sno,Sname,Ssex,Sage
16
《数据库概论》实验指导书
FROM Student WHERE Sdept='IS' WITH CHECK OPTION;
思考：其中，WITH CHECK OPTION子句的作用是什么？
练习2：建立信息系选修了1号课程且成绩在88分以上的学生的视图。 解法1：
CREATE VIEW IS_S1_88(Sno,Sname,Grade) AS
SELECT Student.Sno, Sname, Grade FROM Student,SC
WHERE Sdept='IS' AND Student.Sno=SC.Sno AND Cno='1' AND Grade>=88;
解法2：
CREATE VIEW IS_S1_88(Sno,Sname,Grade) AS
SELECT IS_Student.Sno, Sname, Grade FROM IS_Student,SC
WHERE IS_Student.Sno=SC.Sno AND Cno='1' AND Grade>=88;
练习3：将学生的学号及平均成绩定义为一个视图。 CREATE VIEW S_G(Sno,Gavg) AS
SELECT Sno, AVG(Grade) FROM SC
GROUP BY Sno; （2）查询视图
练习4：在信息系学生的视图中找出年龄小于19的女生。 SELECT Sno,Sname,Ssex,Sage FROM IS_Student
WHERE Sage<19 and Ssex='女';
思考：试写出通过视图消解法转换后的查询语句。
练习5：利用IS_S1_88视图，查询信息系选修了1号课程且成绩在92分以上的学生的学号、姓名和成绩。 SELECT *
FROM IS_S1_88 WHERE Grade>=92; 思考：如何查询信息系选修了1号课程且成绩在85分以上的学生的学号、姓名、性别、年龄和成绩？
练习6：在S_G视图（参见练习3）中查询平均成绩在88分以上的学生学号和平均成绩。 SELECT * FROM S_G
WHERE Gavg>=88;
17
《数据库概论》实验指导书
思考：对于此视图查询语句，SQL Server2005能否正确完成视图消解？试根据实际执行结果的正确与否加以判断，并写出视图消解转换后的SQL语句。（参考教材p122-123）
（3）更新视图
练习7：向信息系学生视图IS_Student中插入一个新的学生元组，其中学号为200215185，姓名为周捷轮，性别为男，年龄为23岁。 INSERT
INTO IS_Student
VALUES('200215185','周捷轮','男',23);
思考：执行此语句会出现什么问题？如何修改？
试试：
① 首先，使用如下语句新建一个信息系学生视图IS_Student2： CREATE VIEW IS_Student2 AS
SELECT Sno,Sname,Ssex,Sage FROM Student
WHERE isnull(Sdept,'IS')= 'IS ' /*判断Sdept是否为空，若为空则用'IS'代替 */ WITH CHECK OPTION;
思考：与练习1中建立的IS_Student视图有何区别？
② 然后，执行如下SQL语句，对视图进行插入操作，观察视图和基本表中的结果是否正确？ INSERT
INTO IS_Student2
VALUES('200215185','周捷轮','男',23);
练习8：将信息系学生视图IS_Student中学号为200215132的学生姓名修改为樊虹宇。 UPDATE IS_Student SET Sname='樊虹宇'
WHERE Sno='200215132';
观察：视图IS_Student、IS_Student2以及基本表Student中的数据的变化情况。
练习9：删除信息系学生视图IS_Student中学号为200215135的记录。 DELETE
FROM IS_Student
WHERE Sno='200215135';
观察：视图IS_Student、IS_Student2以及基本表Student中的数据的变化情况。
（4）删除视图（即删除视图的定义）
练习8：删除视图S_G和视图IS_Student2。 DROP VIEW S_G;
DROP VIEW IS_Student2;
18
《数据库概论》实验指导书
思考：如何删除视图IS_Student及其导出的其他视图？（提示：SQL Server的DROP VIEW 语句中不支持CASCADE）
扩展练习
首先执行S_T.sql，重建示例数据库S_T。
然后写出并执行SQL语句完成以下各种操作，记录查询结果。
创建计算机系学生的视图CS_Student，包括学号、姓名、年龄、性别、系别等属性列。 CREATE VIEWCS_Student AS
SELECT Sno,Sname,Ssex,Sage,Sdept FROM Student
WHERE Sdept='CS'
WITH CHECK OPTION;
通过视图CS_Student查询计算机系男生的详细信息。
通过视图CS_Student将计算机系的一个新生元组（学号为200215120，姓名为龙语嫣，性别为女，年龄17岁）插入表中。 INSERT
INTO CS_Student
VALUES('200215120','龙语嫣','女',17,'CS');
如果换成如下语句：（系别改为信息系） INSERT
INTO CS_Student
VALUES('200215120','龙语嫣','女',17,'IS'); 会出现什么问题？
通过视图CS_Student将计算机系某个男生（学号为200215121）的年龄修改为19岁。 将信息系所有女生的学号及她的平均成绩定义为一个视图S_F_G。
通过视图S_F_G查询信息系所有女生中平均成绩在85-95之间的学号及平均成绩。 （7）删除视图IS_Student和视图S_F_G。 创建表的视图
例如：建立信息系学生的视图。 CREATE VIEW IS_Student AS
SELECT Sno，Sname，Sage FROM Student WHERE Sdept= 'IS' 利用视图完成表的查询
例如：查询信息系选修了1号课程的学生 SELECT Sno，Sname
FROM IS_Student，SC
WHERE IS_Student.Sno =SC.Sno AND SC.Cno= '1' 删除表的视图
例如：DROP VIEW IS_Student
19
《数据库概论》实验指导书
实验五SQL的数据控制（2学时）
1、实验目的
（1）理解SQL Server 2005数据库的安全性相关的概念。
（2）掌握SQL Server 2005的验证模式、登录管理、用户管理、角色管理、权限管理等操作。
2、实验内容
（1）SQL Server的安全认证模式 （2）管理数据库用户 （3）管理数据库角色 （4）权限管理
3、实验要求
（1）掌握SQL Server的安全认证模式的概念及设置方法
（2）掌握使用SSMS和T-SQL两种方式进行SQL Server登录账号、数据库用户、数据库
角色的添加、修改、删除等操作，以及对数据库用户、角色进行权限授予和回收等操作。
（3）理解架构(Schema)、用户(User)、角色(Role)和登录(Login)等基本概念及其关系。
（4）认真记录实验过程，完成实验报告。
4、相关知识
4.1 数据库安全性控制基础
（1）用户标识与鉴别
通过用户名和口令来鉴别用户。只有在DBMS成功注册了的人员才是该数据库的用户(拥有属于自己的惟一的标识符)，才能访问数据库。任何数据库用户要访问数据库时，都须声明自己的用户标识符。由系统检查该用户标识符的用户是否存在，如果存在还要进一步鉴别该声明者是否确实是具有此用户标识符的那个用户。只有通过鉴别的人才能进入系统。鉴别的方法多种多样，口令是最广泛使用的用户鉴别方法。
（2）存取控制
由DBMS授权给有资格的用户访问数据库的权限。存取控制机制有两部分组成：①定义用户权限，并将用户权限登记到数据字典中； ②合法性检查，当用户提出操作请求时，DBMS查找数据字典，进行合法性检查，如果用户的操作请求超出了所定义的权限，系统将拒绝执行此操作。最常使用的存取控制方法是自主存取控制（DAC），能够通过授权机制有效地控制其他用户对敏感数据的存取，而且用户对数据的存取权限是“自主”的，用户可以自由地将数据的存取权限授予他人、决定是否也将“授权”的权限再授予别人，而系统对此无法控制。自主存取控制主要通过SQL的GRANT和REVOKE语句来实现。另一种存取控制方法是强制存取控制（MAC），主要用于高安全级别的场合，如军事领域。
（3）视图机制
通过视图机制，可以为不同的用户定义不同的视图，使得要保密的数据对无权存取的用户隐藏起来，从而在一定程序上达到对数据的安全保护的目的。
（4）审计
通过审计可以把用户对数据库的所有操作自动记录下来放入审计日记中，DBA可以利用审计跟踪的信息，重现导致数据库现有状况的一系列事件，找出非法存取数据的人、时间和内容。审计通常是很费时间和空间的，一般用于安全性较高的部门，而且可由DBA决定是否采用、何时采用。
（5）数据加密
20