SQL数据库实验五答案

数据库原理

实验指导书

实验五 SQL Server 2000中数据库安全性设置

1. 实验的目的

1) 熟悉通过SQL对数据进行安全性控制。

2) 完成作业的上机练习。

2. 实验内容

要求：在SQLSERVER2000中利用SQL语句完成以下操作。

1) 通过SQL对登入账号，用户的创建修改与删除。

2) 通过SQL对角色管理，以及角色中添加用户。

3) 对角色和用户数据的授权和权力回收

3. 实验步骤

1） 复制生成xscj数据库的代码并运行。

2） 创建登入账号和用户名s1，s2，t1,t2，l1，l2密码与账号相同默认登入访问xscj数据库。

3） 创建角色student，teacher，leader。

4） 为角色student授权student表、course表和SC表的select 权力,拒绝修改和删除这三个表的权力

5） 为角色teacher授权student表、course表和SC表的select 权力,以及对SC表的修改，插入更新的权力, 拒绝修改student表和course表的权力

6） 为角色leader授权student表、course表和SC表的select 权力,以及对student表、course表修改，插入更新的权力，拒绝修改和删除表SC的权力

7） 将s1,s2加入student角色

8） 将t1,t2加入teacher角色

9） 将l1,l2加入leader角色

10） 分别以s1,t1,l1登入服务器验证对student表、course表和SC表相应权限的验证

文档从互联网中收集，已重新修正排版，word格式支持编辑，如有帮助欢迎下载支持。

1word格式支持编辑，如有帮助欢迎下载支持。 实验二

4.create database studb

Sp_helpdb

5. alter database studb

modify file

(name=studb,

size=5MB,

maxsize=20MB,

filegrowth=1MB

)

7. ALTER DATABASE studb

MODIFY NAME=student_db

8. DROP DATABASE student_db

实验三

5. USE students_db

GO

CREATE TABLE grade

(学号 char(4),

课程编号 char(4),

分数 decimal(5))

E studentdb

GO

INSERT grade

VALUES(‘0004’,’0001’,’80’)

9. USE studentdb

GO

ALTER TABLE curriculum

ALTER COLUMN 课程编号 char(4) NOT NULL

10. USE studentdb

GO

ALTER TABLE grade

ALTER COLUMN 分数 real

11. USE studentdb

GO

ALTER TABLE student_info

ALTER COLUMN 姓名 。。。。。。不会

12. USE studentdb

GO

DELETE FROM grade

WHERE学号='0004'

13. USE studentdb

GO

UPDATE grade SET 分数=90

WHERE 学号='0003' and 课程编号='0005' 文档从互联网中收集，已重新修正排版，word格式支持编辑，如有帮助欢迎下载支持。

2word格式支持编辑，如有帮助欢迎下载支持。 14. USE studentdb

实验五：多表查询及视图

一、实验目的

1. 掌握SELECT语句的基本语法和查询条件表示方法；

2. 掌握数据表的连接查询、嵌套查询、集合查询的使用方法。

3. 掌握创建及管理视图的方法；

二、实验学时

2学时

三、实验要求

1. 了解SELECT语句的基本语法格式和执行方法；

2. 掌握连接查询、嵌套查询和集合查询的语法规则；

3. 掌握使用界面方式和命令方式创建及管理视图；

4. 完成实验报告；

四、实验内容

1.以实验3数据库为基础，请使用T-SQL 语句实现进行以下操作：

1) 查询选修了‘数学’或者‘大学英语’的学生学号、姓名、所在院系、选修课程号及成绩；

2) 查询与‘张力’(假设姓名唯一)年龄不同的所有学生的信息；

3) 按照“学号，姓名，所在院系，已修学分”的顺序列出学生学分的获得情况。其中已修学分为考试已经及格的课程学分之和；

4) 查找选修了至少一门和张力选修课程一样的学生的学号、姓名及课程号；

5) 查询只被一名学生选修的课程的课程号、课程名；

6) 使用嵌套查询出选修了“数据结构”课程的学生学号和姓名；

7) 使用嵌套查询查询其它系中年龄小于CS系的某个学生的学生姓名、年龄和院系；

8) 使用ANY、ALL 查询，列出其他院系中比WM系所有学生年龄小的学生的姓名；

9) 使用集合查询查询选修1号课程同时选修2号课程的同学的学号与姓名；

2.以实验数据库为基础数据，利用对象资源管理器创建以下视图：

1）创建所有学生学号、姓名及年龄的信息视图v_stu_info

2）创建CS系学生基本信息视图v_stu_cs

3）创建选修课成绩在80分以上的学生的信息视图v_stu_80,包括学生基本情况及成绩。

3. 以实验数据库为基础数据，请使用T-SQL语句完成以下内容，并将SQL语句写在实验报告册中： 1) 创建v_CS视图，包括CS系各学生的学号、姓名及年龄，要求进行修改和插入操作时仍需保证该视图只有CS系的学生;

《数据库原理及应用》上机实验报告表

题目 数据安全性控制 姓名 丁建东 日期 2010-4-22

实验环境 操作系统为Microsoft Windows2000、数据库管理系统为Microsoft SQL

Server2000标准版或企业版

实验内容与完成情况（备注：若纸张不够可以另外加页） 1．创建教辅人员角色，授予学生成绩表的查询权限

（1）创建登录用户U1

EXEC sp_addlogin 'u1', '123', 'school'

（2）创建角色

EXEC sp_addrole 'fudy'

（3）为角色授权

Grant select on student to fudy

（4）将用户添加到角色

EXEC sp_adduser 'u1','u1','fudy'

2．创建专业教师角色，授予所教课程的成绩读写权限

exec sp_addlogin 'u2','123','school'

create role r2;

grant update

on table score,student

to r2

3．创建学生角色，授予学生信息表读写权限，其余表的只读权限

exec sp_addlogin 'u3','123','school'

Create role r3

Grant select,update,insert

On table student

To r3

4．建立视图，写语句实现如下功能：辅导员不能修改学生成绩，专业教师不能修改除成绩之处的学生信息。

CREATE VIEW update1

AS

SELECT update1 u1, u2, u3

FROM sno AS a

JOIN update1sno AS ta ON (u1_update >< u3_score)

JOIN update1 AS sno ON (u2_update = u3_score)

出现的问题： 1. 服务器: 消息 170，级别 15，状态 1，行 1