《数据库开发技术实验》实验指导书

浙江树人大学信息科技学院《数据库开发技术实验》实验指导书适合专业：计算机科学与技术本科专业编写部门：电子商务教研室编写日期：2014.02实验一：安全性管理............................................................1实验二：T-SQL高级查询.....................................................7实验三：数据完整性测试..................................................13实验四：T-SQL编程结构...................................................15实验五：游标和事务设计..................................................17实验六：SQLServer函数的使用.....................................19实验七：存储过程的创建和使用......................................21实验八：触发器的创建......................................................23实验九：数据库应用系统分析与设计..............................271实验一：安全性管理一、实验目的通过实验使学生理解SQLServer2000数据库安全性的管理方法。二、原理解析1、SQLServer登录帐号与登录有关的存储过程：sp_addlogin创建SQLServer登录帐号sp_droplogin删除SQLServer登录帐号sp_grantlogin创建windows登录帐号sp_revokelogin删除windows用户或用户组在SQLServer上的登录信息sp_denylogin拒绝某一windows用户或用户组连接到SQLServer2、数据库用户帐号只有数据库用户才具有访问、操作该数据库的权限，某一登录帐号要获得操作数据库的权限，必须与相应的数据库用户相映射。与数据库用户帐号有关的存储过程：sp_grantdbaccess创建一个数据库用户sp_revokedbaccess删除一个数据库用户3、角色管理可以把一些用户归入某一角色，这样只要角色具有了一定的权限，用户相应地从该角色继承了相应的权限，角色的提出有利于权限的管理。角色可分为固定服务器角色和数据库角色。与角色有关的存储过程：sp_addsrvrolemember添加固定服务器角色成员sp_addrole向数据库添加一角色sp_addrolemember添加数据库角色成员sp_dropsrvrolemember删除固定服务器角色成员sp_droprolemember删除数据库角色成员sp_droprole删除角色注意：删除角色前，必须删除角色成员。24、许可管理许可是用来授权用户可以使用数据库中数据和执行数据库操作。许可授予grant语句许可禁止deny语句许可收回revoke语句三、实验内容（1）登录帐号的管理。（2）数据库用户帐号的管理。（3）角色管理。（4）许可管理。四、实验步骤（1）创建SQLServer登录帐号aa，赋予其系统管理员角色。（2）赋予windows登录帐号bb在数据库Student上的db_owner访问权限。（3）创建SQLServer登录帐号LoginT，其在Student数据库上的对应用户为userT。（4）删除userT所对应的登录帐号loginT。（5）删除Student数据库用户帐号userT。（6）在服务器上创建一Windows用户cc，然后将cc授权登录和访问SQLServer系统。依次利用此账号作测试登录。测试成功后运行脚本：sp_revokelogin'cc'然后以cc登录SQLServer系统，能否成功。运行脚本：sp_addlogin'cc','cc'Gosp_denylogin'cc'Go(7)利用系统存储过程为数据库Student创建一数据库角色myrole，并创建一个数据库用户myuser1，使其属于角色myrole。(8)将数据库用户myuser1添加为数据库Student的db_owner角色。(9)在SQLServer查询分析器中，执行下列脚本，并查看数据库myTestDB。3--创建范例数据库myTestDBCreateDatabasemyTestDBGo--创建测试用表stud01和stud02usemyTestDBGoCreateTablestud01(nochar(5),namechar(10),ageint)GoCreateTablestud02(nochar(5),namechar(10),addressvarchar(30))Go--添加测试数据InsertIntostud01(no,name,age)Values('00001','aa',15)GoInsertIntostud01(no,name,age)Values('00002','bb',16)GoInsertIntostud02(no,name,address)Values('00001','aa','zhejiang')GoInsertIntostud02(no,name,address)Values4('00002','bb',',hangzhou')Go1)执行下列脚本，并查看数据库myTestDB登录账号、数据库用户账号和角色。--创建登录账号loginA和loginB，口令分别为a和bsp_addlogin'loginA','a'Gosp_addlogin'loginB','b'Go--创建testDB数据库的userA和userB用户账号usemyTestDBGosp_grantdbaccess'loginA','userA'Gosp_grantdbaccess'loginB','userB'Go--添加testDB数据库的角色userNsp_addrole'roleN'Go--为角色roleN添加两用户userA和userBsp_addrolemember'roleN','userA'Gosp_addrolemember'roleN','userB'Go2)断开SQLServer查询分析器连接，重新以账号loginA登录，执行下列脚本,查看结果（系统提示无Select权限）UsemyTestDBGoSelect*fromstud015Go3)断开SQLServer查询分析器连接，以账号sa登录，执行下列脚本。--将testDB数据库中的表products的Select许可授予角色roleNUsemyTestDBGoGrantSelectOnstud01ToroleNGo--将myTestDB数据库中的表stud01的Insert,Update,Delete许可--授予用户userA和userBGrantInsert,Update,DeleteOnstud01TouserA,userBGo--将myTestD数据库创建表的许可授予用户userAGrantCreateTableTouserAGo4)断开SQLServer查询分析器连接，重新以账号loginA登录，重新执行脚本,查看结果（可检索表stud01，但无检索表stud02权限）UsemyTestDBGoSelect*fromstud01GoSelect*fromstud02Go5)断开SQLServer查询分析器连接，重新以账号sa登录，执行下列脚本。UsemyTestDBGo--将userA对表stud01的Select许可收回RevokeSelectOnstud01TouserAGo66)断开SQLServer查询分析器连接，重新以账号loginA登录，再执行下列脚本,查看结果（还是可以检索表stud01，因为虽然userA的Select许可被收回，但由于角色roleN具有表stud01的Select许可，而userA作为角色roleN的成员继承了roleN的权限，故还能Select表stud01）UsemyTestDBGoSelect*fromstud01Go7)断开SQLServer查询分析器连接，以账号sa登录，执行下列脚本。UsemyTestDBGo--将用户userA对表stud01的Select许可否决DenySelectOnstud01TouserAGo8)断开SQLServer查询分析器连接，重新以账号loginA登录，再执行下列脚本,查看结果（此时，将无法检索表stud01）UsemyTestDBGoSelect*fromstud01Go9)执行下列脚本查看数据库用户信息和许可信息。UsemyTestDBGosp_helprotectGosp_helpuserGo7实验二：T-SQL高级查询一、实验目的通过实验使学生掌握T-SQL高级查询。本次实验让学生学会掌握多表查询的思想，使用UNION子句，以及熟悉统计函数的用法；让学生学会使用groupby子句，compute和computeby子句，以及掌握较为复杂的嵌套查询的思想。二、原理解析1、多表查询在以前的课程中，所使用的查询局限于一张表格中，但在更多的情况下，需要对多张表格中的数据同时进行查询，这是可以把多张表格的名字全部填写在FROM子句中。在使用多表查询时需要注意的是如何避免笛卡尔积的出现。2、使用UNION子句如果有多个不同的查询结果数据集合，但又希望将他们连接在一起组成一组数据。这组数据是这多个结果集合的逻辑联合，在这种情况下，可以使用UNION子句。在UNION子句的使用中，有两条基本原则：1)、每一个结果集的数据类型都必须相同，更确切地说是兼容；2)、每一个结果集中列的数量都必须相等，排列顺序必须相互对应。3、使用统计函数为了有效的处理使用SQL查询得到的数据集合，SQLServer提供了一系列统计函数。这些函数把存储在数据库中的数据描述为一个整体而不是一行行孤立的记录。通过使用这些函数可以实现对数据集合汇总，求平均值等各种运算。4、使用groupby子句在大多数情况下使用统计函数，返回的是所有行数据的统计结果。如果需要按某一列数据的值进行分类，在分类的基础上再进行查询，就需要使用groupby子句了。需要注意的是在groupby子句中不支持对列分配的别名也不支持任何使用了统计函数的集合列。另外，Select后面每一列数据除了出现在统计函数中的列以外都必须在groupby子句中应用。5、使用compute和computeby子句使用compute子句，允许同时观察查询所得的各列数据的细节以及统计各列数据所产生的总和。通过使用compute子句既可以计算数据分类后的和，也可以计算所有数据的总和。需要注意的是，从返回的查询结果来看，compute子句和groupby子句非常类似。但8是两者之间存在着较大的区别，使用groupby子句只能产生一个结果集合；使用compute可以返回多种结果集。6、使用嵌套查询在以前的实验中，我们所使用的查询都是单层查询，但在实际应用中经常要应用到嵌套查询。嵌套查询要求服务器在处理最终查询工作之前先生成一个结果，然后根据当前的查询结果再进一步继续下面的查询工作。但嵌套查询返回的结果作为查询条件等号右边的值存在时之允许嵌套查询返回一行结果，否则系统出错。三、实验内容1、查询每个学生的学号、姓名、邮政编码等基本信息及其所选课程的成绩情况；2、查询学生信息数据表中所有学生的学号、姓名、院系名称和院系编号；3、查询每门课程名称及该门课的任课教师的姓名、编号；4、在stud_info与stud_grade中按学号stud_id进行等值连接，以查询所有参加考试的基本信息和成绩；5、查询所学专业为“计算机控制技术”或年龄为21岁的所有学生的姓名；6、查询计算机工程系全体教师的