数据库系统管理和开发技术实验指导书06

1、.wd.wd.wd.?数据库系统管理与开发技术?课程实验指导书王贱珍 主编青岛大学国际商学院管理科学与工程系二六年二月二十日目 录 TOC o 1-3 h z u HYPERLINK l _Toc128925177前言 PAGEREF _Toc128925177 h 1HYPERLINK l _Toc128925178实验1 SQL Server 2000安装 PAGEREF _Toc128925178 h 2HYPERLINK l _Toc128925179实验2 SQL Server 2000服务器管理 PAGEREF _Toc128925179 h 4HYPERLINK l _Toc12

2、8925180实验3 数据库管理与规化 PAGEREF _Toc128925180 h 6HYPERLINK l _Toc128925181实验4 数据完整性 PAGEREF _Toc128925181 h 10HYPERLINK l _Toc128925182实验5 数据查询与更新 PAGEREF _Toc128925182 h 14HYPERLINK l _Toc128925183实验6 Transact-SQL 语言 PAGEREF _Toc128925183 h 16HYPERLINK l _Toc128925184实验7 存储过程和用户自定义函数 PAGEREF _Toc128925

3、184 h 19HYPERLINK l _Toc128925185实验8 触发器 PAGEREF _Toc128925185 h 20HYPERLINK l _Toc128925186实验9 游标 PAGEREF _Toc128925186 h 24HYPERLINK l _Toc128925187实验10 SQL Server安全性管理 PAGEREF _Toc128925187 h 25HYPERLINK l _Toc128925188实验11 备份和复原数据库 PAGEREF _Toc128925188 h 28HYPERLINK l _Toc128925189实验12 综合实验 PAG

4、EREF _Toc128925189 h 31前 言一、实验的地位、作用数据库系统管理与开发技术实验作为课程实践性环节之一，是教学过程中必不可少的重要内容。通过上机实验，使学生加深理解、验证稳固课堂教学内容，让学生在消化课堂中的内容根基上提高课堂和书本中无法学到的调试和测试程序的能力，培养学生理论与实践相结合的能力。作为?数据库系统管理与开发技术?课程的先修课、理论课，?数据库原理?与本课程有局部重叠，本课程并未将这局部重叠的内容完全忽略，而是进展了稳固提高。例如，在管理数据库表局部，用企业管理器和SQL语言管理数据库表的 基本方法，同学们应当在?数据库原理?课程中已掌握，而?数据库系统管理与

5、开发技术?实验在稳固前期所学知识的根基上，侧重于重点掌握Transact-SQL提供的为保证数据库完整性而提供的表的各种约束constraint机制的用法。只有结实掌握了一些 基本的Transact-SQL语言，才能掌握数据库系统开发技术中重要的存储过程、触发器、游标等技术，才能较好的进展数据库的管理工作。二、实验开设对象本实验开设对象为?数据库系统管理与开发技术?课程的学习者。三、课程简介在信息管理与信息系统专业和电子商务专业的培养方案中，?数据库系统管理与开发技术?是考察课程。?数据库系统管理与开发技术?课程以SQL Server 2000为背景介绍大型数据库系统的管理和应用系统的开发技术

6、知识，稳固学生关于数据库理论方面的根基知识，并加强他们在数据库系统管理和开发、设计、应用方面的实践能力，为他们将来从事信息管理或相关专业的实际工作打下良好的根基。 四、考核与报告实验后，学生将实验结果等内容写出实验报告，应符合实验教学的要求，并得到指导教师认可。 指导教师对每份实验报告进展审阅、评分。 该实验课程内容是对理论教学内容的应用与验证，实验课的成绩记入课程平时成绩，占总成绩的20。实验1SQL Server 2000安装【实验目的】了解Windows操作系统与SQL Server之间的关系；了解SQL Server 2000各安装选项的作用；会正确安装SQL Server 2000。

7、【实验内容和要求】在VMWare WorkStation软件中练习安装SQL Server 2000。步骤如下：翻开VMWare WorkStation软件，启动虚拟机。进入虚拟机操作系统后，翻开资源管理器，找到SQL Server 2000安装盘，双击安装程序，进入SQL Server 2000安装界面。选择安装SQL Server 2000企业版。根据安装向导的提示，安装SQL Server 2000服务器和客户端。实验2SQL Server 2000服务器管理【实验目的】掌握可用什么工具管理SQL Server 2000服务器。【实验内容和要求】用SQL Server服务管理器启动、暂停

8、或停顿SQL Server服务、SQL Server Agent服务。用操作系统中的服务管理程序启动、暂停或停顿SQL Server服务、SQL Server Agent服务。用命令行方式启动或停顿SQL Server服务sqlservr 命令，写出命令。用Net命令启动或停顿服务SQL Server服务，写出命令。在企业管理器中建设服务器组glx，然后在其中注册几个远程服务器。看看你是否可以在自己的机器上管理和使用远程计算机上的SQL Server服务，从而理解注册服务器的含义。练习在企业管理器中获取数据库对象的SQL 脚本的操作：如何获取pubs数据库中创立sales表的脚本将脚本保存为“

9、getscript.sql。记录操作步骤。提示：操作完毕后，在查询分析器中翻开保存的脚本文件，看看脚本内容。实验3数据库管理与规化【实验目的】掌握用企业管理器来管理和操作数据库及其相应的SQL语句。【实验内容和要求】用企业管理器和SQL语句2种方法建设如下内容的数据库：注意：要先在资源管理器中建设存放数据文件和事物日志文件的目录，否那么，会报错。如何用企业管理器删除数据库myDB记录操作步骤。SQL语句如何写练习如何用企业管理器移动数据库。先创立数据库move，该数据库的数据文件move_data.mdf和日志文件move_log.ldf都存放在目录D:下。在企业管理器中把拆分move数据库。

10、把目录D:下的数据文件move_data.mdf和日志文件move_log.ldf剪切、粘贴到E:目录下。然后在企业管理器中用E:目录下的数据文件move_data.mdf和日志文件move_log.ldf附加数据库。记录操作步骤。提示：用存储过程完成第3题。分别用企业管理器和SQL语句两种方式完成下面两题：为表products 创立一个以p_name为索引键、填充因子为10的簇索引index_product，索引中间级中每个页节点上保持开放的空间，索引存放在主文件组。products 创立一个p_id, p_name, sumvalue三个字段构成的复合索引，填充因子为50，索引中间级中每个

11、页节点上保持开放的空间，索引存放在主文件组。【思考题】看看在企业管理器中，有哪几个系统数据库、哪几个实例数据库各数据库的作用分别是什么一台有数据库Student的SQL Server服务器，因意外，SQL Server服务系统遭到破坏，但数据库文件是完好的。现还有另外一台SQL Server服务器B，但其上没有Student数据库。如何尽快让服务器B上提供数据库Student的数据服务实验4数据完整性【实验目的】SQL Server 提供了一些工具来帮助用户实现数据完整性,其中最主要的是约束constraint、规那么、缺省值、约束和触发器。本次实验目的是掌握约束、规那么和缺省值的用法。【实验

12、内容和要求】用企业管理器在上次实验中创立的数据库myDB中添加四个表：学生表S、课程表C、教师表T和选课表SC。S(S#, Sname, Sbirth, Ssex, Sdept, Sage)， 各字段含义分别为学号、姓名、生日、性别、院系、年龄;C(C#, Cname, T#)，各字段含义分别为课号、课名、教授该课程的教师编号;SC(S#, C#, grade) ，各字段含义分别为课号、学号、成绩;T(T#, Tname,Tposition) ，各字段含义分别为教师编号、教师名、职称。其中：S#, C#为字符型，长度都为5；grade为整型；Sbirth为日期时间型； 看看各表的主码是什么，为

13、各表设置主码； Sage是一个计算字段，等于当前时间与生日Sbirth之间相差的年数；C表的T#是外键，参照T表；C表的Cname不可为空，且值是唯一的，不可重复；SC表的S#字段是参照S表的外键，C#字段是参照C表的外键；C表的Grade小于等于100；T表的T#是IDENTITY属性的字段，初始值为10，增量为1；Tposition字段是字符型，长度为6，缺省值为“助教；未说明的字段自己根据情况定义其类型。写出创立上面4个表的SQL语句。创立如以以下列图所示两表：p和c，二表的主键如以下列图，且c表有对更新和删除操作级连的外键a参照p表的主键a。用下面的SQL语句在p和c两表中插入数据并显

14、示两表的内容：insert into p values(a1,b1)insert into p values(a2,b2)insert into p values(a3,b3)insert into c values(c1,a1)insert into c values(c2,a2)insert into c values(c3,a1)insert into c values(c4,a3)insert into c values(c5,a4)select * from pselect * from c然后，用下面的SQL语句修改p表中的数据，并显示两表的内容：update p set a =a

15、0 whereb= b1select * from pselect * from c然后，用下面的SQL语句删除p表中的一行数据，并显示两表的内容：delete from p where b=b1select * from pselect * from c为什么p表中的一行数据，c表的数据也被删除了通过数据库文件EXP06_Data.MDF和EXP06_Log.LDF附加数据库Exp06，在该数据库中做下面实验。创立雇佣日期规那么hire_date_rule，hire_date在1980-01-01 以后、当前日期之前。创立工作级别规那么job_level_rule，job_level 只能在

16、1,2,3,4,5之间。创立字符规那么my_character_rule，字符串必须以a 到f 的字母开头以0到9的数字结尾。绑定规那么hire_date_rule 到用户自定义数据类型hire_date 上。绑定规那么hire_date_rule 到用户自定义数据类型hire_date 上带futureonly选项，写出相应SQL语句并执行之。绑定规那么my_character_rule 到orders 表的字段order_id。创立生日缺省值birthday_defa为1978-1-1。创立姓名缺省值name_defa为user。把姓名缺省值name_defa绑定到Employees表的e

17、mp_name字段。解除规那么hire_date_rule 与用户自定义数据类型hire_date 的绑定，带futureonly选项。解除姓名缺省值name_defa与Employees表的emp_name字段的绑定。【思考题】T表的T#字段的数据类型可以为字符型吗为什么SQL Server怎样保证数据库的实体完整性、参照完整性和用户自定义完整性如果用下面的语句创立p、c两表，然后如实验题3那样删除p表的一行数据，会出现什么情况create table p(a char(2) primary key, b char(2)create table c(c char(2) primary key

18、, a char(2), foreign key(a) references p(a) ON DELETE NO ACTION ON UPDATENO ACTION )绑定规那么hire_date_rule 到用户自定义数据类型hire_date 上带futureonly选项，写出相应SQL语句并执行之。现在，Employees表的hiredate和birthday两字段的数据类型分别是datetime和用户自定义数据类型hire_date。我们在企业管理器里把hiredate字段的数据类型改为用户自定义数据类型hire_date。然后，插入两条数据：insert employees valu

19、es (e1,张三,1990-1-1,1970-1-1 )insert employees values (e1,李四,1970-1-1,1990-1-1 )为什么一条SQL语句执行成功，另一条执行失败实验5数据查询与更新【实验目的】稳固在数据库原理课程中学过的Ansi-SQL查询语句的 基本用法，并进一步掌握Transact-SQL扩展的查询语句用法及复杂查询语句的用法。关于数据更新的三种类型的SQL语句Insert、Delete 、Update的 基本用法，同学们在数据库原理课程中应当已经掌握。本实验主要掌握Transact-SQL对ansi-SQL的一些重要的扩展语法的使用如select

20、 .into、insertinto，另外，还要求掌握事务的使用。【实验内容和要求】先把pangu数据库附加到SQL Server服务器。pangu中有如下表：然后用查询分析器在pangu中做以下各题：1查找公司中所有姓张且全名为两个字的员工的姓名、所在部门编号；2查询在编号为P1、P2、F3、S1的部门中工作的员工的姓名和部门名；3查询firms表中公司介绍字段以j% 或“j_开头的纪录；提示：LIKE 模式串 ESCAPE 逃逸字符 4查询工作级别为2 的员工姓名,查询结果按工资排序；5查询工资最高的4名员工的姓名和工资；6查询公司编号、公司所订购的产品编号和应支付的金额。查询结果按公司编号

21、和产品编号分组；7查询有多个员工的工资不低于1500 的部门编号；8查询各部门中的最高工资数额；9查询在公司工作的工龄一样的员工；10查询平均工资低于公司平均工资的部门编号、名称及其平均工资；11查询单笔订货量超过10 单位的产品名称；12查询order_id为3的订货纪录，将其产品ID、产品名称、订货公司的名称保存到新建的表order3中。13删除department_info中所有数据，然后往department_info表插入各个部门的员工总数e_num、工资总额d_wage；提示： insertinto 14做第13题，不同之处在于：把第13题的操作作为一个事务，假设往departme

22、nt_info表插入各个部门的员工总数、工资总额的操作失败那么回滚整个事务。(练习事务的使用)实验6Transact-SQL 语言【实验目的】Transact-SQL 是ANSI SQL 的加强版语言，它提供了标准的SQL命令另外还对SQL 命令做了许多扩大提供类似Basic、Pascal、C 等第三代语言的 基本功能，如变量说明、程序流程控制语言功能、函数等。本实验的目的是掌握Transact-SQL 语言中变量声明、流程控制命令使用、注释符运算符与通配符使用、其它命令使用、常用函数使用。【实验类型】设计型实验【实验内容和要求】翻开查询分析器，步骤为：【开场】按钮【程序】【Microsoft

23、 SQL Server】【查询分析器】。在查询分析器中写出完成以下功能的SQL语句。transact-SQL 语言变量声明与使用：声明一个长度为10 个字符的局部变量str1，将其值赋为SQL;声明一个长度为10 个字符的局部变量str2，将其值赋为 Server;声明一个长度为20 个字符的局部变量str，将str1和str2相连接后的值赋给str ;显示局部变量str、str1、str2的值。【实验说明】用declare声明变量；用set或select为变量赋值；两个字符串的连接运算用“运算符；用select或print显示变量的值。注释符运算符与通配符使用将当前数据库切换为pubs数据库

24、，查询employee表中所有姓张的雇员纪录，并做注释：“本语句查询employee表中所有姓张的雇员纪录。【实验说明】用use命令切换数据库；控制命令使用调整员工工资，工作级别为1 的上调8% ，工作级别为2 的上调7%，工作级别为3 的上调6% ，其它上调5%。【实验说明】使用case命令，case命令能根据不同的条件返回不同的值。常用函数使用统计函数: 在pangu数据库中查询：求各部门的平均工资。计算企业的部门数目。求工资最高的员工姓名。求各部门的员工工资总额。【实验说明】使用统计函数avg( )、count( )、max( )、sum( )。注意，统计函数是对组进展统计，如果查询语句

25、中没有用到分组子句group by，那么整个查询结果被看作一个分组。算术函数用CEILING和FLOOR函数返回大于或等于2.6的最小整数值和小于或等于2.6最大整数值。给出一个随机数。提示： rand给出值值。字符串函数把字符串Abc、 ABC全部转换为小写。提示：lower( )把字符串Abc、 abc全部转换为大写。提示：upper ( )select str(123), str(123456,5), str( -124.456,8,3),str(124.456,5), str(124.456,8,2)会得到什么结果把字符串 abc头部的空格去掉。 把字符串abc尾部的空格去掉。获得字符

26、串SQL Server,最左边3个字符的字符串。提示：LEFT( ) 获得字符串SQL Server,最右边5个字符的字符串。提示：Right( ) 获得字符串SQL Server第3个字符开场、长度为4的字符串提示：SUBSTRING( ) 把字符串上海倒序。提示：REVERSE( ) 日期函数查询在本单位工作了8 年以上的员工的姓名和所在的部门。 【实验说明】日期函数是很重要的一类函数，因为在查询、数据库编程中经常要用到这类函数，同学们应该重点掌握。该题必须先求得各员工在本单位的工作时间。employee表中有各员工的雇佣时间hire_date，当前时间可用函数getdate( )得到，求

27、两日期之差的函数是DATEDIFF( )，这样就可求得员工在本单位的工作时间。【思考题】下面SQL语句含义是什么注意cast( )函数和convert( )函数的使用。select cast(hire_date as char) as hire_date, convert(varchar(10), e_wage) as wagefrom employeewhere e_wage=(select min(e_wage) from employee)select getdate(), convert(char(10),getdate(),1)select convert(char(18),getd

28、ate(),100), convert(char(10),getdate(),112)下面SQL语句含义是什么select dateadd(day,21,12/12/1999), dateadd(month,3, 12/12/1999)select dateadd(year,1,1/1/1999), dateadd(week,1, 1999/12/12)实验7存储过程和用户自定义函数【实验目的】掌握存储过程的创立和执行方法。掌握用户自定义函数创立和调用方法。【实验内容和要求】附加数据库Exp10。用Transact-SQL语言完成下面功能：创立存储过程dept_num， 该存储过程有两个参数：

29、dept_id部门编号，作为输入参数, e_num(作为输出参数，获得部门编号为dept_id的部门的雇员总数)。执行存储过程dept_numdept_id为S1，并显示S1的雇员总数。在pubs数据库中创立用户自定义函数内嵌表值函数SalesByStore，该函数有一参数storeid varchar(30)，返回stor_id为storeid的书店所销售的各种书籍的书名和相应的销售数量的信息。调用函数SalesByStore，查看书店号为7131的书店所销售的各种书籍的书名和相应的销售数量的信息。实验8触发器【实验目的】理解触发器的工作原理，掌握假设何创立触发器。【实验内容和要求】附加数据

30、库Exp10。表构造表数据学生表S中创立触发器stu_upd, 使得当S表中更改学生学号Sno时： 确保一次最多只更改一行，否那么报错，回滚整个事务； 假设一次只更改了一行，那么将选课表SC中被更改学号的学生在选课表中的所有记录也作一样更改，例如：学号为95001的学生学号被改为94010，那么选课表SC中学号95001的选课纪录的学号也都要被改为94010。创立好触发器后，把学生表中学号95001改为94010，看看选课表有什么变化。在学生表S中创立触发器stu_del, 使得当S表中删除学生数据行时： 确保一次最多只删除一行，否那么报错，回滚整个事务； 假设一次只删除了一行，那么将成绩表中

31、被删除的学生在选课表中的所有记录也删除创立好触发器后，把学生表中学号95002的记录删除，看看选课表有什么变化。表构造如上图。表数据如以以下列图。创立触发器，使得：员工换工作即员工的job_id改变,假设该员工的job_lvl他的新工作要求的max_lvl,那么把他的job_lvl置为max_lvl.【思考题】 首先创立三个表salemay、salejune 和salejuly，这三个表分别用来保存五、六、七月的销售量信息。create table salemay(sale_id char(6) not null,sale_name varchar(20),sale_qua smallint)

32、表salejune 和表salejuly 与salemay 具有一样的数据列。 再创立分割视图saleview:create view saleviewasselect * from salemayunion allselect * from salejuneunion allselect * from salejuly这时做操作insert into saleview values(jul001,先科VCD,200)会怎样为什么 然后在视图saleview 上创立INSTEAD OF 触发器saleviewtr:create trigger saleviewtr on saleviewins

33、tead of insertasbegindeclare sale_id char(4)select sale_id=sale_idfrom insertedif substring(sale_id,1,3) =maybegin insert into salemay select sale_id, sale_name, sale_qua from insertedendif substring(sale_id,1,3) =junbegin insert into salejune select sale_id, sale_name, sale_qua from insertedendif s

34、ubstring(sale_id,1,3) =julbegin insert into salejuly select sale_id, sale_name, sale_qua from insertedendend这时做操作insert into saleview values(jul001,先科VCD,200)会怎样为什么实验9游标【实验目的】通过本实验掌握游标 基本用法。【实验内容和要求】有学生选课表SC(S#, C#, grade, ord) ，各字段含义分别为学号、课号、成绩、名次，类型分别是char(4)、char(4)、smallint、smallint。现在，前三个字段都有数据

35、没有为null的。请创立存储过程give_ord，该存储过程有参数C#，该存储过程的功能是将SC表中课程号为C#的记录的ord字段填入学生在该课程的名次。成绩一样的学生的名次也一样。例如，A、B、C、D四人成绩分别是95、93、93、80，那么四人的名次分别是1、2、2、4。创立后，执行该存储过程，为选了课号为DB的课的学生排名次，看看排的结果对不对。【思考题】有学生选课表SC(S#, C#, grade, ord) ，各字段含义分别为学号、课号、成绩、名次，类型分别是char(4)、char(4)、smallint、smallint。现在，前三个字段都有数据没有为null的。请创立存储过程g

36、ive_ord，该存储过程将ord字段填入值即，为每门课的学生排名次，对同一门课，成绩一样的学生的名次也一样。实验10SQL Server安全性管理【实验目的】在SQL Server 的安全模型中包括以下几局部：SQL Server 登录、数据库用户、权限、角色。通过本实验掌握SQL Server 的安全 基本用法。【实验内容和要求】1用SQL Server企业管理器创立SQL Server登录帐户LOGIN1，密码333。【服务器】【安全性】【登录】上右击【新建登录】【SQL Server登录属性新建登录】对话框在【常规】标签中输入：在【常规】标签中单击【确定】按钮。2为test数据库创立新

37、用户LOGIN1该用户以登录帐户LOGIN1登录数据库，对数据表table1有select、insert、update权限。操作：【服务器】【数据库】【test】数据库【用户】上右击【新建数据库用户】【数据库用户属性新建用户】对话框在【常规】标签中如下输入，并点确定：【test】数据库【用户】在LOGIN1上右击【所有任务】【管理权限】对话框在【数据库用户属性test】对话框的【权限】标签中赋权限，并确定：3以LOGIN1登录SQL Server，翻开表table1，做插入、修改数据操作，SQL Server有何反响做delete操作，SQL Server有何反响4为test数据库创立加数据库标准角色role1，给该角色赋予某些权限；将该角色赋给test数据库的LOGIN1用户。5附加题：在windows系统中新建windows用户win1，再将该用户添加为SQL Server登录用户，该用户具有test数据库的角色role1的权限。实验11备份和复原数据库【实验目的】掌握数据库备份及复原的方法。【实验内容和要求