数据库函数存储过程触发器数据库安全的实验文档格式.docx
《数据库函数存储过程触发器数据库安全的实验文档格式.docx》由会员分享，可在线阅读，更多相关《数据库函数存储过程触发器数据库安全的实验文档格式.docx（43页珍藏版）》请在冰豆网上搜索。
）
createfunctionFU2_SPXS（@yearint,@quarterint,@cpmcchar（10））
returns@xsqktable
（产品名称char（10）,
销售数量int,
销售金额int）
begininsertinto@xsqk
select产品名称,sum（销售额）as销售总额,sum（数量）as销售数量
fromcpxsb,cp
anddatepart（year,销售日期）=@year
anddatepart（quarter,销售日期）=@quarter
groupby产品名称
end
（3）根据销售商名称，统计其在某年某季度内销售商品名称、数量及金额。
（函数名为FU3_CPXS）
createfunctionFU3_CPXS（@xssmcchar（10）,@yearint,@quarterint）
returnstable
fromCPXSB,CP,XSS
whereCPXSB.产品编号=CP.产品编号
andCPXSB.客户编号=XSS.客户编号
and客户名称=@xssmc
anddatepart（year,销售日期）=@year
groupby产品名称）
2、函数的调用
（1）对函数FU_CP，查询产品名称为“mp3”的产品情况；
mp3'
g
（2）对函数FU1_CPXS，查询2004年第1季度彩色电视机的销售数量和销售金额；
select*fromFU321_CPXS（2004,1,'
彩电电视机'
（3）对函数FU2_CPXS，查询2004年第1季度洗衣机的销售数量和销售金额；
select*fromFU1_CPXS（2004,1,'
洗衣机'
（4）对函数FU3_CPXS，查询广电公司2004年第1季度销售的产品名称、销售数量和销售金额。
三、分析与回答
试说明内嵌表值函数、多语句表值函数的联系与区别
答：
内嵌表值函数返回的是一个表；
而多语句表值函数的功能是试图与存储过程的组合，可以利用多语句表值函数返回一个表，表中的内容可由复杂的逻辑和多条SQL语句构建，可以在select语句中句的from子句中使用多语句表值函数。
实验十一索引、默认值约束和默认值对象
一、实验内容和步骤
1、索引的创建和删除
（1）对CP表，在产品名称上定义一个唯一非聚簇的索引ind_cp。
createuniqueindexind_cp6oncp（产品名称）
（2）先创建各客户购买产品的情况VIEW1视图，包括客户编号、客户名称、产品编号、产品名称、价格，购买日期、购买数量，然后在客户编号+产品编号+购买日期定义一个唯一聚簇索引ind_view1。
（请注意7个SET选项设置）。
创建视图如下：
createviewcp_gmqk
selectxss.客户编号,客户名称,
cp.产品编号,产品名称,价格,
cpxsb.销售日期,数量
fromcpjoincpxsboncp.产品编号=cpxsb.产品编号
joinxssonxss.客户编号=cpxsb.客户编号
定义一个唯一聚簇索引ind_view1：
createuniqueclusteredindexind_view
oncpxsb（客户编号,产品编号,销售日期）
2、索引的删除
删除ind_view1索引
dropindexcpxsb.ind_view
3、默认值约束的定义
对库存量字段添加默认值为0的约束def_kcl
altertablecp
addconstraintdef_kcl
default0for库存量
4、默认值约束的删除
删除def_kcl默认值约束
dropconstraintdef_kcl
5、默认值对象的定义、使用和删除
（1）定义一值为0的默认值对象kcl_def，并将其绑定到CP表的库存量字段；
然后再删除此默认值对象，请叙述该过程，并写出相关SQL语句。
createdefaultkcl_def
as0
execsp_bindefault'
kcl_def'
'
cp.库存量'
go
dropdefaultkcl_def
sp_unbindefault'
（2）用SQL命令定义一名为city的用户自定义数据类型，要求char（6），NULL，再定义一值为“北京”的默认值对象city_def，然后将city_def默认值对象绑定到city自定义数据类型，最后删除city_def默认值对象，请叙述该过程，并写出相关SQL语句。
自定义数据类型：
sp_addtype'
city'
'
char（6）'
null'
绑定到自定义数据类型：
createdefaultcity_def
as'
北京'
city_def'
删除city_def默认对象：
dropdefaultcity_def
sp_unbindefault'
（1）说明索引的概念和作用。
索引的概念:
数据库中的索引是一个表中包含列表的列值，其中注明了表中包含值的行数据所在的存储位置；
其作用是加快数据的查询。
（2）说明聚簇索引和非聚簇索引的含义和区别。
聚集索引的概念：
聚集索引定义了数据在表中存储的物理顺序，它是指表中数据行的的物理存储顺序与索引顺序完全相同。
聚集索引有上下两层构成，上层为索引页，包含表中的索引页面，下层为数据页。
非聚集索引的概念：
非聚集索引不改变表中的物理存储顺序，数据与索引分开存储。
非聚集索引中仅包含索引值和指向数据存储位置的指针。
索引中的项目按索引键值的顺序存储，而表中的信息按另一种顺序存储。
区别在于：
1：
数据不按非聚集索引中的关键字值的顺序排列和存储；
2：
非聚集索引的叶级节点不是存放数据的数据页
实验十二数据完整性的实现
1、实验内容和步骤
1、实体完整性的实现
（1）对CP表、CPXSB表、XSS表，定义主键约束（用企业管理器和SQL语句分别实现，写出相应过程和语句）。
对cp表：
altertableCP
addconstraintpk_cp
primarykey（产品编号）
对xss表：
altertableXSS
addconstraintpk_xss
primarykey（客户编号）
对cpxsb表:
altertablecpxsb
addconstraintpk_cpxsb
（2）在CP表的产品名称列定义一个唯一约束（用企业管理器和SQL语句分别实现，写出相应过程和语句）。
addconstraintuk_cp
unique（产品名称）
2、参照完整性的实现
（1）利用企业管理器建立CP表与CPXSB之间的参照关系，当对主表CP表进行更新和删除操作时，从表CPXSB采用NOACTION方式，写出其过程。
（2）利用SQL语句建立CPXSB与XSS表之间的参照关系，当对主表XSS表进行更新和删除操作时，从表CPXSB采用CASCADE（级联）方式，写出其过程。
3、域完整性的实现
（1）在CP表的价格列上定义大于等于0的检查（CHE约束。
addconstraintck_cp
check（价格>
=0）
（2）定义一个大于等于0的规则check_kcl，并将其绑定到CP表的库存量列，然后解除绑定，删除该规则，请写出相关SQL语句。
createrulecheck_kcl
as@rangelike'
k[>
=0]'
sp_bindrule'
check_kcl'
先解除绑定
sp_unbindrule'
删除该规则：
droprulecheck_kcl
4、综合训练
创建一个“学生档案”数据库，并用CREATETABLE在该数据库中创建“人事”表和“成绩”两个表，其中“人事”表包括学号、姓名、性别、电话号码、家庭住址字段组成。
“成绩”表包括学号、选修课程名称、成绩三个字段。
分别在“人事”表的学号字段设置主键约束，在“成绩”表的学号字段设置外键约束，在成绩字段设置大于等于0小于等于100的检查约束，在家庭住址字段设置惟一约束，在性别字段设置默认值为“男”。
createdatabase学生档案
use学生档案
createtable人事
（学号char（6）notnull,
姓名varchar（10）,
性别char
（2）,
电话号码char（20）,
家庭住址char（30）,
createtable成绩
学号char（6）notnull,
选修课程名称varchar（20）,
成绩int,
在“人事”表的学号字段设置主键约束：
altertable人事
addconstraintpk_rs
primarykey（学号）
在“成绩”表的学号字段设置外键约束：
altertable成绩
addconstraintfk_cj
foreignkey（学号）references成绩（学号）
在成绩字段设置大于等于0小于等于100的检查约束
addconstraintck_rs
check（成绩<
=100and成绩>
在家庭住址字段设置惟一约束：
addconstraintuq_rs
unique（家庭住址）
在性别字段设置默认值为“男”：
addconstraintdt_rs
default'
男'
for性别
在SQLServer中讨论参照完整性，请体会如下含义：
●插入从表中记录时：
有限制和忽略两种方式；
●删除主表中的记录时：
有限制、忽略和级联三种方式；
●修改主表中的主键时：
有限制、忽略和级联三种方式。
试根据自身学习，分别说明其含义。
实验十三存储过程
⏹熟练掌握存储过程的创建、调用和删除。
对于CPXS数据库，创建如下存储过程：
1、无参存储过程
编写一无参存储过程用于查询每个客户购买产品的情况（包括客户编号、产品编号、客户名称、产品名称、价格、购买日期、购买数量），然后调用该存储过程。
createprocedurecustomer123
selectCPXSB.客户编号,CPXSB.产品编号,客户名称,产品名称,价格,销售日期,数量fromCPXSB
joinCPonCPXSB.产品编号=CP.产品编号
joinXSSonCPXSB.客户编号=XSS.客户编号
executecustomer123
2、带有参数的存储过程
编写一加密存储过程，查询指定客户购买产品的情况。
并调用该存储过程查询客户编号为“000002”的客户购买情况。
createprocedurespecialcus123456@cusnumchar（20）
leftjoinCPonCPXSB.产品编号=CP.产品编号
leftjoinXSSonCPXSB.客户编号=XSS.客户编号
whereCPXSB.客户编号=@cusnum
execspecialcus123456'
000002'
3、带有通配符参数的存储过程
编写一存储过程，查询指定产品的销售情况。
如果没有提供参数，则查询产品名称中包含有“冰箱”的产品销售情况。
createprocedurecp_xsqk123
@产品名称char（10）='
冰箱'
select*fromCPXSB
4、带有OUTPUT参数的存储过程
编写一存储过程，查询指定客户在指定时间段内购买指定产品的数量，存储过程中使用了输入和输出参数。
并调用该存储过程查询名称为“家电市场”的客户在2004年购买“洗衣机”的数量。
createprocedurespecial
@cusnumchar（20）,@timedatetime,
@producechar（16）,@numintoutput
As
select@num=数量fromCPXSB
leftjoinCPonCPXSB.产品编号=CP.产品编号
whereCPXSB.产品编号=@time
and销售日期=@time
and产品名称=@produce
查询指定客户在指定时间段内购买指定产品的数量，存储过程中使用了输入和输出参数。
Executespecial'
100001'
'
2004-03-1800:
00:
00.000'
试说明存储过程的分类和特点。
SQLsever支持5种形式的存储过程：
1系统存储过程
2本地存储过程
3临时存储过程
4远程存储过程
5扩展存储过程
存储过程的特点是：
1：
存储过程在服务器端运行，实行速度快；
2：
存储过程执行一次后，其执行规划就驻留在高速缓冲存储器中，在以后的操作中，只需要从高速缓冲存储器中调用已经编好的二进制代码执行即可，提高了系统的性能；
3：
确保数据库的安全，使用存储过程可以完成所有数据库的操作，并通过编程方式控制上述操作对数据库信息的访问权限；
4：
自动完成需要与执行的任务。
存储过程可以在系统启动时自动执行，完成一些需要与执行的任务，而不必在系统启动后在进行手工操作，大大方便了用户的使用
实验十四触发器
⏹熟练掌握后触发器和替代触发器的区别；
⏹熟练掌握后触发器和替代触发器的创建。
对于CPXS数据库，完成如下各项：
1、编写存储过程，对产品销售表进行插入操作，并通过触发器保证插入时，产品编号与CP表中的对应字段一致，销售商编号与销售商表中对应字段一致。
其中触发器分别用后触发器和替代触发器实现。
2、在CPXSB上创建一后触发器，若对产品编号列和客户编号列修改，则给出提示信息，并取消修改操作，用两种方法实现。
createtriggertri_remind
onCPXSB
forupdate
if（columns_updated（）&
3）>
begin
raiserror（'
正在对产品编号列和客户编号列修改'
19,1）
rollbacktransaction
end
试说明后触发器和替代触发器的区别。
Aafter型触发器和insteadof型触发器的区别：
对于after型触发器在一种操作上可以建立多个触发器；
而insteadof型的触发器在一种操作上只能创建一个触发器；
实验十五系统安全管理
⏹理解SQLServer中的安全管理机制。
⏹理解帐户、用户、服务器角色和数据库角色的含义。
⏹熟练掌握WindowsNT登录帐户和SQLServer登录帐户的建立与删除。
⏹熟练掌握添加与删除服务器角色成员。
⏹熟练掌握添加和删除一个登录帐户为某个数据库的用户。
⏹熟练掌握添加和删除数据库角色。
⏹熟练掌握添加和删除数据库角色成员。
⏹熟练掌握用户、角色的授权、收回权限和拒绝权限。
1、创建WindowsNT登录帐户
在SQLServer中添加一个帐户名为“WANG”的WindowsNT登录帐户。
第一步：
创建windows2003的用户”WANG”,截图如下所示：
第二步：
将windowsNT用户添加到sqlsever中：
executesp_grantlogin'
MAWEIFEI\WANG'
2、创建SQLServer登录帐户
在SQLServer中添加一个帐户名为“LIU”，密码为“123456”的SQLServer登录帐户。
第一步:
首先应将SQLsever的认证模式设置为混合模式，其截图如下所示：
通过命令创建SQLsever的登录账户：
executesp_addlogin'
LIU'
123456'
运行后进行更新，且结果如下所示：
3、添加服务器角色成员
将“WANG”这个WindowsNT登录帐户添加到系统管理员服务器角色中。
executesp_addsrvrolemember'
sysadmin'
4、添加一个登录帐户为某个数据库的用户
将“LIU”这个SQLServer登录帐户添加为CPXS数据库中一个用户，名称也为“LIU”。
executesp_grantdbaccess'
5、添加数据库角色
在CPXS数据库中添加一个名为“ROLE”的角色。
executesp_addrole'
ROLE'
6、添加数据库角色成员
将CPXS数据库中名为“LIU”这个用户添加为“ROLE”角色成员。
executesp_addrolemember'
7、用户、角色的授权
授予“LIU”用户和“ROLE”角色对CPXSB表的查询权限和数量列的修改权限。
grantselect,updateonCPXSBtoLIU,ROLE
8、收回用户、角色的
收回“LIU”用户的所有权限。
并查看是否能打开CPXSB表。
revokeselect,updateonCPXSBfromLIU
9、拒绝用户、角色的权限
重新授予“LIU”用户对CPXSB表的查询权限和数量列的修改权限。
再拒绝该用户的所有权限。
并查看是否能打开CPXSB表，并通过此实例阐述收回权限与拒绝权限的区别。
grantselect,updateonCPXSBtoLIU
授予用户LIU的select和update的权限后，其结果如下所示：
denyselect,updateonCPXSBtoLIU
拒绝后的结果如下所示：
取消用户LIU的权限的结
升级会员 