数据库函数-存储过程-触发器-数据库安全的实验

1、 实验十 函数一、实验目的与要求理解什么是标量函数、内嵌表值函数及多语句表值函数。熟练掌握标量函数、内嵌表值函数、多语句表值函数的定义和调用。基表如下所示：二、实验内容和步骤1、函数的定义对于CPXS数据库，定义完成如下功能的函数：（1）据产品名称，查询该产品的相关信息；（函数名为FU_CP）use cpxs1gocreate function FU_CP(productname char(30)returns tableasreturn(select * from CPXSB right join XSS on CPXSB.客户编号=XSS.客户编号 right join CP on CPX

2、SB.产品编号=CP.产品编号where 产品名称=productname)Gouse cpxs1goselect * from FU_CP(彩色电视机)go（2）按某年某季度统计给定产品名称的销售数量及销售金额；分别用名为FU1_CPXS内嵌表值函数和名为FU2_CPXS的多语句表值函数。use cpxs1gocreate function FU12_CPXS(year int ,quarter int, cpmc char(10)returns tableasreturn (select 产品名称, sum(数量) as 销售数量, sum(销售额) as 销售总额 from cpxsb,

3、cpwhere cpxsb.产品编号=cp.产品编号 and 产品名称=cpmc and datepart(year ,销售日期)=year and datepart(quarter,销售日期)=quarter) Gouse cpxs1gocreate function FU2_SPXS(year int ,quarter int ,cpmc char(10)returns xsqk table (产品名称 char (10), 销售数量 int , 销售金额 int)asbegin insert into xsqkselect 产品名称,sum(销售额)as 销售总额,sum(数量)as 销

4、售数量from cpxsb,cpwhere cpxsb.产品编号=cp.产品编号 and 产品名称=cpmc and datepart (year ,销售日期)=year and datepart (quarter, 销售日期)=quartergroup by 产品名称return endgo（3）根据销售商名称，统计其在某年某季度内销售商品名称、数量及金额。（函数名为FU3_CPXS）use cpxs1gocreate function FU3_CPXS(xssmc char(10),year int,quarter int) returns tableasreturn(select 产品名

5、称, sum(数量) as 销售数量, sum(销售额) as 销售总额 from CPXSB,CP,XSS where CPXSB.产品编号=CP.产品编号 and CPXSB.客户编号=XSS.客户编号 and 客户名称=xssmc and datepart(year,销售日期)=year and datepart(quarter,销售日期)=quarter group by 产品名称)Go函数的调用（1）对函数FU_CP，查询产品名称为“mp3”的产品情况；use cpxs1goselect * from FU_CP(mp3)g对函数FU1_CPXS，查询2004年第1季度彩色电视机的销

6、售数量和销售金额；use cpxs1goselect * from FU321_CPXS (2004,1,彩电电视机)（3）对函数FU2_CPXS，查询2004年第1季度洗衣机的销售数量和销售金额；select * from FU1_CPXS(2004,1,洗衣机)（4）对函数FU3_CPXS，查询广电公司2004年第1季度销售的产品名称、销售数量和销售金额。三、分析与回答试说明内嵌表值函数、多语句表值函数的联系与区别答：内嵌表值函数返回的是一个表；而多语句表值函数的功能是试图与存储过程的组合，可以利用多语句表值函数返回一个表，表中的内容可由复杂的逻辑和多条SQL语句构建，可以在select语

7、句中句的from子句中使用多语句表值函数。实验十一 索引、默认值约束和默认值对象实验内容和步骤索引的创建和删除对CP表，在产品名称上定义一个唯一非聚簇的索引ind_cp。use cpxs1gocreate unique index ind_cp6 on cp (产品名称)Go（2）先创建各客户购买产品的情况VIEW1视图，包括客户编号、客户名称、产品编号、产品名称、价格，购买日期、购买数量，然后在客户编号+产品编号+购买日期定义一个唯一聚簇索引ind_view1。（请注意7个SET选项设置）。创建视图如下：create view cp_gmqkselect xss.客户编号,客户名称 , cp

8、.产品编号,产品名称,价格 , cpxsb.销售日期,数量from cp join cpxsb on cp.产品编号=cpxsb.产品编号 join xss on xss.客户编号=cpxsb.客户编号定义一个唯一聚簇索引ind_view1：use cpxs1gocreate unique clustered index ind_viewon cpxsb(客户编号,产品编号,销售日期)go2、索引的删除删除ind_view1索引use cpxs1godrop index cpxsb.ind_viewgo3、默认值约束的定义对库存量字段添加默认值为0的约束def_kcluse cpxs1goal

9、ter table cpadd constraint def_kcldefault 0 for 库存量go4、默认值约束的删除删除def_kcl默认值约束use cpxs1goalter table cpdrop constraint def_kclgo5、默认值对象的定义、使用和删除（1）定义一值为0的默认值对象kcl_def，并将其绑定到CP表的库存量字段；然后再删除此默认值对象，请叙述该过程，并写出相关SQL语句。create default kcl_defas 0goexec sp_bindefault kcl_def ,cp.库存量go drop default kcl_defgo

10、sp_unbindefault cp.库存量go （2） 用SQL命令定义一名为city的用户自定义数据类型，要求char(6)，NULL，再定义一值为“北京”的默认值对象city_def，然后将city_def默认值对象绑定到city自定义数据类型，最后删除city_def默认值对象，请叙述该过程，并写出相关SQL语句。自定义数据类型：use cpxs1gosp_addtype city,char(6),nullgo绑定到自定义数据类型：use cpxs1gocreate default city_defas 北京goexec sp_bindefault city_def,citygo删除c

11、ity_def默认对象：use cpxs1godrop default city_defgosp_unbindefault citygo三、分析与回答说明索引的概念和作用。索引的概念:数据库中的索引是一个表中包含列表的列值，其中注明了表中包含值的行数据所在的存储位置；其作用是加快数据的查询。说明聚簇索引和非聚簇索引的含义和区别。聚集索引的概念：聚集索引定义了数据在表中存储的物理顺序，它是指表中数据行的的物理存储顺序与索引顺序完全相同。聚集索引有上下两层构成，上层为索引页，包含表中的索引页面，下层为数据页。 非聚集索引的概念：非聚集索引不改变表中的物理存储顺序，数据与索引分开存储。非聚集索引中仅

12、包含索引值和指向数据存储位置的指针。索引中的项目按索引键值的顺序存储，而表中的信息按另一种顺序存储。区别在于：1：数据不按非聚集索引中的关键字值的顺序排列和存储；2：非聚集索引的叶级节点不是存放数据的数据页实验十二 数据完整性的实现1、实验内容和步骤1、实体完整性的实现（1）对CP表、CPXSB表、XSS表，定义主键约束（用企业管理器和SQL语句分别实现，写出相应过程和语句）。对cp表：use cpxs1goalter table CPadd constraint pk_cpprimary key (产品编号)go对xss表：use cpxs1goalter table XSSadd cons

13、traint pk_xssprimary key (客户编号)go对cpxsb表: use cpxs1goalter table cpxsbadd constraint pk_cpxsbprimary key (产品编号)go（2）在CP表的产品名称列定义一个唯一约束（用企业管理器和SQL语句分别实现，写出相应过程和语句）。use cpxs1goalter table cpadd constraint uk_cpunique (产品名称)go2、参照完整性的实现（1）利用企业管理器建立CP表与CPXSB之间的参照关系，当对主表CP表进行更新和删除操作时，从表CPXSB采用NO ACTION方式

14、，写出其过程。（2）利用SQL语句建立CPXSB与XSS表之间的参照关系，当对主表XSS表进行更新和删除操作时，从表CPXSB采用CASCADE（级联）方式，写出其过程。3、域完整性的实现（1）在CP表的价格列上定义大于等于0的检查（CHE约束。use cpxs1goalter table cpadd constraint ck_cpcheck(价格=0)go（2）定义一个大于等于0的规则check_kcl，并将其绑定到CP表的库存量列，然后解除绑定，删除该规则，请写出相关SQL语句。use cpxs1gocreate rule check_kclas range like k=0 gosp_

15、bindrule check_kcl,cp.库存量go先解除绑定use cpxs1gosp_unbindrule cp.库存量go删除该规则：use cpxs1godrop rule check_kclgo4、综合训练创建一个“学生档案”数据库，并用CREATE TABLE在该数据库中创建“人事”表和“成绩”两个表，其中“人事”表包括学号、姓名、性别、电话号码、家庭住址字段组成。“成绩”表包括学号、选修课程名称、成绩三个字段。分别在“人事”表的学号字段设置主键约束，在“成绩”表的学号字段设置外键约束，在成绩字段设置大于等于0小于等于100的检查约束，在家庭住址字段设置惟一约束，在性别字段设置默

16、认值为“男”。use cpxs1gocreate database 学生档案go use 学生档案create table 人事(学号 char(6) not null,姓名 varchar(10),性别 char(2),电话号码 char(20),家庭住址 char(30),)use 学生档案create table 成绩(学号 char(6) not null,选修课程名称 varchar(20),成绩 int,)在“人事”表的学号字段设置主键约束：alter table 人事add constraint pk_rsprimary key (学号)在“成绩”表的学号字段设置外键约束：use

17、 学生档案alter table 成绩add constraint fk_cjforeign key (学号) references 成绩(学号)在成绩字段设置大于等于0小于等于100的检查约束use 学生档案alter table 成绩add constraint ck_rscheck (成绩=0)在家庭住址字段设置惟一约束：use 学生档案use 学生档案alter table 人事add constraint uq_rsunique (家庭住址)go在性别字段设置默认值为“男”：use 学生档案alter table 人事add constraint dt_rsdefault 男for

18、性别三、分析与回答在SQL Server中讨论参照完整性，请体会如下含义：插入从表中记录时：有限制和忽略两种方式；删除主表中的记录时：有限制、忽略和级联三种方式；修改主表中的主键时：有限制、忽略和级联三种方式。试根据自身学习，分别说明其含义。实验十三 存储过程一、实验目的与要求熟练掌握存储过程的创建、调用和删除。二、实验内容和步骤对于CPXS数据库，创建如下存储过程： 1、无参存储过程编写一无参存储过程用于查询每个客户购买产品的情况（包括客户编号、产品编号、客户名称、产品名称、价格、购买日期、购买数量），然后调用该存储过程。use cpxs1gocreate procedure custome

19、r123 asselect CPXSB.客户编号,CPXSB.产品编号,客户名称,产品名称,价格,销售日期,数量 from CPXSB join CP on CPXSB.产品编号=CP.产品编号 join XSS on CPXSB.客户编号=XSS.客户编号 gouse cpxs1goexecute customer123 go2、带有参数的存储过程编写一加密存储过程，查询指定客户购买产品的情况。并调用该存储过程查询客户编号为“000002”的客户购买情况。create procedure specialcus123456 cusnum char(20)asselect CPXSB.客户编号,

20、CPXSB.产品编号,客户名称,产品名称,价格,销售日期,数量 from CPXSB left join CP on CPXSB.产品编号=CP.产品编号 left join XSS on CPXSB.客户编号=XSS.客户编号 where CPXSB.客户编号=cusnumGouse cpxs1exec specialcus123456 000002go3、带有通配符参数的存储过程编写一存储过程，查询指定产品的销售情况。如果没有提供参数，则查询产品名称中包含有“冰箱”的产品销售情况。use cpxs1gocreate procedure cp_xsqk123产品名称 char(10)=冰箱a

21、sselect * from CPXSBgo4、带有OUTPUT参数的存储过程编写一存储过程，查询指定客户在指定时间段内购买指定产品的数量，存储过程中使用了输入和输出参数。并调用该存储过程查询名称为“家电市场”的客户在2004年购买“洗衣机”的数量。use cpxs1gocreate procedure special cusnum char(20), time datetime, produce char(16), num int outputAsselect num=数量 from CPXSB left join XSS on CPXSB.客户编号=XSS.客户编号 left join C

22、P on CPXSB.产品编号=CP.产品编号 where CPXSB.产品编号=time and 销售日期=time and 产品名称=produce Go查询指定客户在指定时间段内购买指定产品的数量，存储过程中使用了输入和输出参数。并调用该存储过程查询名称为“家电市场”的客户在2004年购买“洗衣机”的数量。use cpxs1goExecute special 100001, 2004-03-18 00:00:00.000, 彩色电视机Go三、分析与回答试说明存储过程的分类和特点。SQLsever支持5种形式的存储过程： 1系统存储过程 2本地存储过程 3临时存储过程 4远程存储过程 5扩

23、展存储过程存储过程的特点是： 1：存储过程在服务器端运行，实行速度快； 2：存储过程执行一次后，其执行规划就驻留在高速缓冲存储器中，在以后的操作中，只需要从高速缓冲存储器中调用已经编好的二进制代码执行即可，提高了系统的性能； 3：确保数据库的安全，使用存储过程可以完成所有数据库的操作，并通过编程方式控制上述操作对数据库信息的访问权限； 4：自动完成需要与执行的任务。存储过程可以在系统启动时自动执行，完成一些需要与执行的任务，而不必在系统启动后在进行手工操作，大大方便了用户的使用实验十四 触发器一、实验目的与要求熟练掌握后触发器和替代触发器的区别；熟练掌握后触发器和替代触发器的创建。二、实验内容

24、和步骤对于CPXS数据库，完成如下各项： 1、编写存储过程，对产品销售表进行插入操作，并通过触发器保证插入时，产品编号与CP表中的对应字段一致，销售商编号与销售商表中对应字段一致。其中触发器分别用后触发器和替代触发器实现。2、在CPXSB上创建一后触发器，若对产品编号列和客户编号列修改，则给出提示信息，并取消修改操作，用两种方法实现。use cpxs1gocreate trigger tri_remindon CPXSBfor updateasif(columns_updated()&3)0 begin raiserror(正在对产品编号列和客户编号列修改,19,1) rollback tra

25、nsaction endgo三、分析与回答试说明后触发器和替代触发器的区别。Aafter型触发器和instead of型触发器的区别： 对于after型触发器在一种操作上可以建立多个触发器；而instead of 型的触发器在一种操作上只能创建一个触发器；实验十五 系统安全管理一、实验目的与要求理解SQL Server中的安全管理机制。理解帐户、用户、服务器角色和数据库角色的含义。熟练掌握Windows NT登录帐户和SQL Server登录帐户的建立与删除。熟练掌握添加与删除服务器角色成员。熟练掌握添加和删除一个登录帐户为某个数据库的用户。熟练掌握添加和删除数据库角色。熟练掌握添加和删除数据

26、库角色成员。熟练掌握用户、角色的授权、收回权限和拒绝权限。二、实验内容和步骤1、创建Windows NT登录帐户在SQL Server中添加一个帐户名为“WANG”的Windows NT登录帐户。第一步：创建windows2003的用户”WANG”,截图如下所示：第二步：将windowsNT用户添加到sqlsever中：use cpxs1goexecute sp_grantlogin MAWEIFEIWANGgo2、创建SQL Server登录帐户在SQL Server中添加一个帐户名为“LIU”，密码为“123456”的SQL Server登录帐户。第一步:首先应将SQLsever的认证模式设置为混合模式，其截图如下所示：第二步：通过命令创建SQLsever的登录账户：use cpxs1goexecute sp_addlogin LIU,123456go 运行后进行更新，且结果如下所示：3、添加服务器角色成员将“WAN