一、实验目的
- 掌握视图特点;
- 掌握使用SQL Server创建及管理视图的方法;
- 掌握在对象资源管理器和T-SQL语句中创建和管理索引;
二、实验学时
2学时
三、实验要求
- 掌握使用界面方式和命令方式创建及管理视图;
- 利用对象资源管理器和T-SQL语句创建、修改、删除和使用索引。
四、实验内容
1.以实验数据库为基础数据,利用对象资源管理器创建以下视图:
1)创建所有学生学号、姓名及年龄的信息视图stu_info
select
student表.Sno
student表.Sname
student表.Sage
from
dbo.student表
2)创建CS系学生基本信息视图stu_cs
select
student表.Sno
student表.Ssex
student表.Sname
student表.Sage
student表.Sdept
from
dbo.student表
where
student表.Sdept = 'CS'
3)创建选修课成绩在80分以上的学生的信息视图stu_80,包括学生基本情况及成绩。
select
student表.Sno,
student表.Sname,
student表.Ssex,
student表.Sage,
student表.Sdept,
sc表.Sno,
sc表.Cno,
sc表.Grade
from
dbo.sc表
inner join
dbo.student表
on
sc表.Sno = student表.Sno
where
sc表.Grade > '80'
2. 以实验数据库为基础数据,请使用T-SQL语句完成以下内容:
1)创建CS_age视图,包括CS系各学生的学号、姓名及年龄,要求进行修改和插入操作时仍需保证该视图只有CS系的学生;
create view CS_age
as
select sno,sname,sage
from student表
where sdept = 'CS'
with check option
2)创建CS_age_20视图,包括CS系学生年龄在20岁以上的基本信息;并保证对视图文本的修改都要符合年龄大于20这个条件。
create view CS_age_20
as
select *
from student表
where sdept = 'CS' and sage > 20
with check option
3)向CS系学生视图stu_cs中插入一个新的学生记录,学号为200515026,姓名为“赵红平”,性别为“男”,年龄21岁;
insert
into stu_cs
values('200515026','男','赵红平','21','CS');
4)利用视图stu_cs修改学号为200515001的学生姓名为“赵青青”;
update stu_cs
set sname='赵青青'
where sno='200515001';
5)利用视图stu_cs删除CS系学号为200515003的记录;
delete
from stu_cs
where Sno='200515003'
6)删除视图CS_age_20;
delete view CS_age_20;
3.在对象资源管理器中,利用图形化的方法创建和管理下列索引:
1)对数据表student中字段sname建立非聚集索引idx_name;
2)针对student表的sno和sname列创建复合索引idx_no_name;
3) 删除数据表student的索引idx_name;
4. 利用T-SQL语句创建和管理以下索引:
1)对course表的cname列创建非聚集索引idx_cname;
create
index idx_cname
on course表(cname)
2)对sc表的sno和cno列创建复合索引idx_sno_cno;
create
index idx_sno_cno
on sc表(sno,cno)
3)删除索引idx_cname。
drop index course表.idx_cname;
5.数据库安全性练习(以下内容不需要在实验作业中提交)
- 创建数据库用户
任务:创建数据库用户test1。
因SQL Server中数据库新用户需关联登录名,故建议先创建SQL Server登录名。
操作提示:界面方式。以Windows身份验证连接SSMS→对象资源管理器→安全性→登录名→新建登录名。在“登录名-新建”窗口中进行相关设置,如输入登录名“test1”,SQL Server身份验证,输入密码,选择默认数据库stu,如图1所示。
图1 新建登录名
然后在“用户映射”选项中选择映射数据库stu,同时产生用户test1,如图2所示。单击“确定”即可创建登录名test1和数据库用户test1。
图2 用户映射
SQL命令方式:
CREATE USER user_name [FOR LOGIN <login_name> ]
(2)用户授权
任务:授予用户test1查询、更新数据表student的权限。
用户具有登录名后,可以成功登录数据库服务器,但并不具备访问任何用户数据库的权限。只有成为数据库的合法用户后,才能访问该数据库,故需要赋予其相应权限才可展开工作。
操作提示:界面方式。以Windows身份验证连接SSMS→对象资源管理器→数据库→stu→安全性→用户,双击用户test1即可打开“数据库用户”窗口,依次单击所需权限,如图3所示。单击“确定”即可完成用户授权。
图3 授予数据库用户权限
SQL命令方式:
GRANT <权限>[,<权限>]...
ON <对象类型> [,<对象名>]...
TO <用户>[,<用户>]...
[WITH GRANT OPTION];
(3)课外自主实验(选做)
建议依次登录验证新用户身份和权限。
- 在数据库Stu中创建用户stu1、stu2和stu3,登录名分别为stu1、stu2和stu3;
- 给数据库用户stu1赋予对sc表进行INSERT、UPDATE、DELETE操作权限;
- 给数据库用户stu2和stu3赋予对student表、course表所有操作权限及查询sc的操作权限,并允许再授权给其他用户;
- 收回数据库用户stu2对student表和course表的DELETE操作的权限。
五、实验分析与思考
- 本次实验过程中易出错的地方有哪些,如何解决。
- 试分析并总结数据库视图的优缺点。
- 试分析并总结使用索引的注意事项。
- 本次实验总结。
如有错误望批评指正!谢谢各位!