实验四
1.实验题目：数据表的更新
2.实验目和要求：
1)掌握SQL语句的数据操纵功能
2)掌握SQL语言的数据插入，修改，删除操作
3)掌握与嵌套查询相结合的插入，删除，修改数据的SQL语句
3.实验步骤：
1) 按实验内容要求完成各项操作
2) 根据题目要求给出解决方案
3) 提交实验报告
4.实验内容
- 在学生表中插入一新生信息(‘200213808’,’HUJING’,’女’,22,’计算机’)
INSERT
INTO Student(SNO,SNAME,SEX,AGE,DEPT)
VALUES('200213808','胡静','女',22,'计算机系');
- 新建一个学生平均成绩表spjcj,将学生的平均成绩插入到spjcj表中
CREATE TABLE SPJCJ
(SNO CHAR(10) PRIMARY KEY,
AVG_GRADE SMALLINT);
INSERT
INTO SPJCJ(SNO,AVG_GRADE)
SELECT SNO,AVG(GRADE)
FROM SC
GROUP BY SNO;
- 删除数据库中学号为’200213801’的退学学生有关信息。
DELETE
FROM Student
WHERE SNO='200213808';
- 将计算机系学生2号课成绩全部提高5%。
UPDATE SC
SET GRADE=GRADE*1.05
WHERE CNO='2'AND SNO IN(SELECT SNO
FROM Student
WHERE DEPT='计算机系');
- 删除没有成绩的学生选课记录
DELETE
FROM SC
WHERE SNO IN(SELECT SNO
FROM SC
WHERE GRADE=NULL);
5.实验总结:如上实验内容
6.思考题
请分析数据库模式修改语句和数据更新语句的区别。
Alter用来修改基本表，是对表的结构进行操作，比如对字段增加，删除，修改类型。
Update用来修改表中的数据，修改某一行某一列的值。
实验六
1.实验题目：数据库的完整性
2.实验目的和要求：
- 掌握数据库的实体完整性约束定义，完整性检查及违约处理方式。
- 掌握数据库的参照完整性约束定义，完整性检查及违约处理方式。
- 掌握数据库的用户定义完整性约束定义，完整性检查及违约处理方式。
- 掌握触发器的定义及使用。
3.实验步骤：
- 按实验内容要求完成各项操作
- 根据题目要求给出解决方案
- 提交实验报告
4.实验内容：
- 定义S， C表，定义实体完整性约束，定义学生的年龄在19到22岁之间，课程名唯一的约束
CREATE TABLE S
(
SNO CHAR(10),
SNAME CHAR(10),
SEX CHAR(10),
AGE SMALLINT CONSTRAINT C4 CHECK(AGE<22 AND AGE>19),
DEPT CHAR(10),
PRIMARY KEY NONCLUSTERED(SNO)
)
CREATE TABLE C
(
CNO CHAR(10),
CNAME CHAR(10) UNIQUE,
CCREDIT CHAR(10),
CT CHAR(10),
PRIMARY KEY NONCLUSTERED(CNO)
)
- 在C表中增加cpno字段，cpno字段为先修课号，定义cpno为外码，参照C表的cno，并定义当删除被参照表中相关记录时，要求违约处理方式为置空，当发生修改操作时，违约处理方式为级联。
CREATE TABLE C
(
CNO CHAR(10),
CNAME CHAR(10) UNIQUE,
CCREDIT CHAR(10),
CT CHAR(10),
CPNO CHAR(10),
PRIMARY KEY NONCLUSTERED(CNO)
FOREIGN KEY(CPNO)REFERENCES C(CNO)/*表级完整性约束条件，CPNO是外码，被参照表是C，被参照列是CNO*/
ON DELETE NO ACTION
ON UPDATE CASCADE
)
- 定义SC表的实体集参照完整性约束，要求当其被参照表发生删除操作时，S表违约处理的方式为级联，当其被参照表发生修改操作时，违约处理的方式为拒绝。
CREATE TABLE SC
(CNO CHAR(10)
SNO CHAR(10)
GRADE SMALLINT
PRIMARY KEY(CNO,SNO)
FOREIGN KEY(SNO)REFERENCE S(SNO)
ON DELETE NO ACTION
ON UPDATE NO ACTION
FOREIGN KEY(CNO)REFERENCE C(CNO)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
- 触发器
- 建立一DML触发器，每当学生的成绩发生更新时，将更新的学号，成绩存入g-log表内
CREATE TABLE g-log
(SNO CHAR(10)
GRADE SMALLINT)
CREATE TRIGGER RTI_UP
ON SC
FOR UPDATE
AS
IF UPDATE(GRADE)
BEGIN
INSERT INTO g-log
SELECT SNO,GRADE
FROM INSERTED
WHERE GRADE BETWEEN 0 AND 100
END
RETURN
- 建立一个INSTEAD OF触发器，每当删除课程表中记录时，先检查此课程是否已被选修，如选修则不允许删除，且给出提示信息“此课程已有学生已选修，无法删除”
CREATE TRIGGER C_CHECK1
ON C
INSTEAD OF DELETE
AS
IF EXISTS(SELECT CNO
FROM SC
WHERE CNO=SC.CNO)
BEGIN
PRINT’此课程已有学生已选修，无法删除’
ROLLBACK TRANSACTION
END
- 实验总结：内容如上实验内容
- .思考题
- 试分析参照完整性违约检查及违约处理的方式。
拒绝（NO ACTION）执行
级联（CASCADE）操作：当删除或修改参照表的一个元组造成了与参照表的不一致。
设置为空值（SET-NULL）当删除或修改参照表的一个元组造成了与参照表的不一致，则将参照表中的所有造成不一致的元组的对应属性设置为空值。
- 试比较AFTER触发器和INSTEAD OF触发器的区别。
AFTER表示在触发事件的操作执行之后激活触发器，只能在表上指定，且动作晚于约束处理。
INSTEAD OF
- 用来代替通常的触发动作
- 动作早于表的约束处理
- 不仅可以在表上定义，还可以在带有一个或多个基表的视图上定义
区别：每个表上只能创建一个INSTEAD OF触发器，但可以创建多个AFTER触发器。
实验七
1.实验题目：数据库的安全性
2.实验目的和要求：
- 掌握SQL Server 2005的安全控制机制
- 掌握SQL Server2005的身份验证模式
- 理解数据库用户帐户的基本概念
- 理解角色的概念
3.实验步骤：
- 按实验内容要求完成各项操作
- 根据题目要求给出解决方案
- 提交实验报告
4.实验内容：
- 定义登录log1，log2，log3
- 定义用户user1，user2，user3
CREATE USER user1
CREATE USER user2
CREATE USER user3
- 掌握SQL SERVER 2005架构和用户分离的概念
- 数据库的授权、授权语句
- 将查询SC表和修改GRADE属性的权限授予用户user1。
GRANT SELECT,UPDATE(GRADE)
ON SC
TO user1;
- 将对表S的插入权限授予用户user2，并允许他将此权限授予其他用户。
GRANT INSERT
ON S
TO user2;
- 允许用户user3拥有对cs系学生的查询权限
GRANT SELECT(DEPT='CS')
ON S
TO user3;
- 创建角色R1，授予R1 课程表查询和插入的权限，并授予用户user3
CREATE ROLE R1
GRANT SELECT,INSERT
ON C
TO R1;
GRANT R1
TO user3
- 删除角色R1课程表的插入权限
REVOKE INSERT
ON C
FROM R1
- 收回所有用户对表S的插入权限
REVOKE INSERT
ON TABLE S
FROM user1，user2，user3
5.实验总结
6.思考题
1.请分析with grant option、with check option、with admin option有何联系和区别
如果指定了with grant option子句，则获得某种权限的用户还可以把这种权限在授予其他的用户。如果没有指定，则获得某种权限的用户只能使用该权限，不能传播该权限。
如果指定了with admin option子句，则获得这种权限的角色或用户还可以把这种权限在授予其他的角色。
with check option表示对视图进行ＵＰＤＡＴＥ，ＩＮＳＥＲＴ，ＤＥＬＥＴＥ操作时要保证更新，插入或删除的行满足视图定义中的谓词条件。
- 根据实验内容分析角色在权限分配上有何优点。
一个角色包含的权限包括直接授予这个角色的全部权限加上其他角色授予这个角色的全部权限。