数据库系统原理-实验指导书.doc

阳光学院计算机工程系硬件（软件）实验系列指导用书数据库系统原理实验指导书福州大学阳光学院计算机工程系2011年 8月前 言 随着计算机技术的不断向前发展，数据库技术也随之形成了一套较为完整的理论体系，数据库系统原理是计算机领域的重要和基础学科，数据是数据库系统的核心和基础。因此，在学习数据库的理论知识的同时，数据库管理也是目前计算机领域学习和研究的主要内容。本实验指导书就是以数据库管理知识为基础，结合SQL Server 2005的操作与应用编写而成的实验指导，从而让学生能了解SQL Server 2005这一数据库管理系统的操作以及SQL语言的功能和运用。本实验指导书由5个部分组成：SQL Server 2005基本操作、数据定义、数据操作、数据查询以及数据控制。采用教师课堂讲授与指导、学生动手实验相结合的形式，让学生能更好的吸收理论课上学习的知识，并且能接收新的上机操作知识，提高动手能力。编者：杨曦 欧阳林艳 2011年 8月 10 日 目 录实验一 SQL Server 2005基本操作1实验二 数据定义4实验三 数据操作7实验四 数据查询10实验五 数据管理13实验一 SQL Server 2005基本操作一、实验目的了解SQL Server 2005组件；了解SQL Server数据库组成；掌握SQL Server 2005界面基本操作。二、原理说明SQL Server 2005中的数据库由包含数据的表集合以及其他对象（如视图、索引、存储过程等）组成，它的数据库分为系统数据库与用户数据库两大类。系统数据库包含如下数据库：master：记录SQL Server 中的所有系统级信息；msdb：供SQL Server 代理服务调度报警和作业以及记录操作员时使用；model：是在SQL Server 中创建用户数据库的模板；tempdb：临时数据库，为数据的排序操作等提供临时空间。用户数据库是用户自己根据需要创建的。SQL Server 2005包含两种数据文件：（1）数据文件：存放数据库中数据。分为主数据文件（.mdf）与辅助数据文件（.ndf）；（2）事务日志文件：存放恢复数据库的所有日志信息（.ldf）。三、实验环境安装了Microsoft SQL Server 2005的计算机四、实验内容（1）开始-程序-Microsoft SQL Server 2005- SQL Server Management Studio，打开后进入到SQL Server 2005主体界面图 1-1 SQL Server 2005主体界面（2）点击“数据库”前面的“+”，可以展开查看数据库，并且可以继续展开下级目录，查看数据库中的表、视图等。（3）了解SQL Server 2005菜单栏的一些主要工具的使用。 选择SQL语言操作的数据库 检查语法错误打开SQL语言查询界面 执行SQL语言 图 1-2 SQL Server 2005主要工具（4）学生动手操作SQL Server 2005，打开数据库，打开表，打开查询界面；查看数据库的属性、表的属性等。五、实验注意事项1注意在进行数据库SQL查询界面后，如果以后要对哪个数据库进行操作，就要在上方属性栏选择相应的数据库（如图1-2中的master处）；2数据库的主数据文件只有1个，辅助数据文件可以有多个。实验二 数据定义一、实验目的掌握SQL Server 2005的数据库创建；掌握SQL Server数据定义语言；掌握SQL Server 2005数据定义的SQL语言定义与管理器定义两种方式。二、原理说明（1）数据库创建代码：Create Database EmployeeOn Primary(Name=Empdat1,Filename=d:你的学号dataEmpdat1.mdf ,Size=10MB,MaxSize=50MB,FileGrowth=5MB),(Name=Empdat2,Filename=d:你的学号dataEmpdat2.ndf ,Size=5MB,MaxSize=25MB,FileGrowth=2MB)Log On(Name=Emplog,Filename=d:你的学号dataEmplog.ldf ,Size=5MB,MaxSize=25MB,FileGrowth=5%)（2）定义表代码：Create Table ( 列级完整性约束条件 ， 列级完整性约束条件. ,); 三、实验环境安装了Microsoft SQL Server 2005的计算机四、实验内容（1）创建、修改、删除数据库。创建要求：数据库Employee中包含一个数据库文件Empdat1.mdf和一个日志文件Emplog.ldf。其中，数据文件大小为10MB，最大为50MB，以5MB速度增长；日志文件大小为5MB，最大为25MB，以5%速度增长。修改要求：增加第二个数据库文件Empdat2.ndf，其中，数据文件大小为5MB，最大为25MB，以2MB速度增长。（2）利用SQL创建人员表person、月薪表salary及部门表dept。要求：按表2-1、表2-2及表2-3中的字段说明创建。表2-1person表结构字段名数据类型字段长度允许空否字段说明P_noChar6Not Null工号，主键P_nameVarchar10Not Null姓名SecChar2Not Null性别BirthDateDatetime8Null出生日期ProfVarchar10Null职称DeptnoChar4Not Null部门代码，外键(参照dept表)create table person(P_no char(6) primary key Not Null, P_name Varchar(10) Not Null, Sec char(2)Not Null, BirthDate Datetime, Prof Varchar(10), Deptno char(4) foreign key(Deptno)references dept Not Null )表2-2 Salary表结构字段名数据类型字段长度允许空否字段说明P_noChar6Not Null工号，主键，外键(参照person表)BaseDec5Null基本工资BonusDec5Null奖金，要求50Fact实发工资=基本工资+奖金MonthIntNot Null月份create table Salary (P_no Char(6) primary key foreign key(P_no)references person Not Null , Base Dec(5), Bonus Dec(5) check(Bonus50), Month Int Not Null , Fact as Base+Bonus)表2-3 dept表结构字段名数据类型字段长度允许空否字段说明DeptnoChar4Not Null部门代码，主键DnameVarchar10Not Null部门名称create table dept(Deptno Char (4) primary key Not Null, Dname Varchar(10) Not Null)（3）利用SQL语句创建视图。要求：在基表person上创建员工视图Person View，其中包含工号、姓名、性别、职称和部门代码等字段。create view PersonViewas select P_no,P_name,Sec,Prof,Deptno from person （4）创建索引。要求： 在人员表的姓名列上创建一个单列索引name_sort create index name_sorton person(P_name)在人员表的出生日期列和姓名列上创建一个组合索引birth_name create index birth_nameon person(BirthDate,P_name)在人员表的姓名列上创建一个唯一索引u_name_sort create unique index u_name_sort on person(P_name)（5）删除索引。要求：删除月薪表上的索引fact_idx create index Factidxon Salary(Fact)Drop Index Salary.Factidx五、实验注意事项1注意合理使用完整性约束短语；2注意创建表的顺序，因为表与表之间存在参照关系，所以在创建表时应该先建主表，再建从表；3注意索引创建的条件，如唯一索引。实验三 数据操作一、实验目的掌握SQL Server的数据操作语言；掌握SQL Server数据操作的管理器处理方式；掌握SQL Server 2005定义视图语言。二、原理说明（1）数据库插入语言：INSERT INTO (,.) VALUES ( ,.) （2）视图定义语言：CREATE VIEW (,.) AS WITH CHECK OPTION三、实验环境安装了Microsoft SQL Server 2005的计算机四、实验内容（1）利用SQL语句向表person、salary和dept中插入数据。要求：按表3-1、表3-2及表3-3中的数据插入。表3-1表person中的数据P_noP_nameSexBirthDateProfDeptno000001王云男1973-4-7中级0001000002谢志文男1975-2-14中级0001000003李浩然男1970-8-25高级0002000004廖小玲女1979-8-6初级0002000005梁玉琼女1970-8-25中级0003000006罗向东男1979-5-11初级0003000007肖家庆男1963-7-14高级0003表3-2表salary中的数据P_noBaseBonusFactMonth00000121003001000002180030010000032800280100000425002501000005230027510000061750130100000724002101表2-3表dept中的数据DeptnoDname0001人事部0002财务部0003市场部（2）用SQL语句修改表中的数据。要求：将salary表中工号为000006的员工工资增加为1800，奖金增加为160。 update Salaryset Base=1800 ,Bonus=160 where P_no=000006（3）用SQL语句删除表中的数据。要求：删除person表中工号为000010的员工数据 DELETE FROM person where P_no=000001（4）更新视图。要求：将员工视图PersonView中姓名为王云的员工职称改为高级。 update PersonViewset Prof=高级where P_name=王云（5）向视图插入数据。要求：向视图PersonView中插入一行数据(000008，刘美萍，女，中级，0002)。 insert into PersonViewVALUES(000008,刘美萍,女,中级,0002)（6）删除视图。要求：将视图PersonView删除。 drop VIEW PersonView五、实验注意事项1注意插入数据时的先后顺序；2注意创建视图的语言。实验四 数据查询一、实验目的掌握SQL Server的数据查询语言；掌握SQL语言在不同情况下的灵活应用。二、原理说明（1）查询的一般格式：SELECT All|Distinct ,. FROM , . WHERE GROUP BY HAVING ORDER BY ASC | DESC （2）嵌套查询；SELECT FROM WHERE (SELECT FROM)（3）连接查询。(连接条件：WHERE =)三、实验环境安装了Microsoft SQL Server 2005的计算机四、实验内容(1) 利用SQL语句查询person表中的所有数据。 select *from person(2) 条件查询。要求 ：查询person表中所有不重复的职称 select distinct Prof from Person 查询person表中职称为中级的所有员工数据 select *from Personwhere Prof=中级 查询person表中具有高级职称的男员工信息 select *from Personwhere Prof=高级and Sec=男 查询person表中姓名为王云、谢志文或罗向东的员工数据 select *from Personwhere P_name=王云 or P_name=谢志文or P_name=罗向东(3) 使用ORDER BY排序要求：利用SQL语句将工号在000003和000008之间的员工的月收入按实发工资升序排列。 select factfrom Salarywhere P_no=000003and P_no200ORDER by Avg(Bonus) (7) 查询人事部所有员工信息。 Select * From salary ,Person , dept where salary.p_no=Person.p_no and dept.deptno=Person.deptno and Dname=人事部(8) 查询person表中职称为中级的员工信息。 select *from Personwhere Prof=中级(9) 查询每个员工1月份的工资和奖金。 select P_no,Base,Bonusfrom Salarywhere Month=1 (10) 查询比工号为000005的员工实发工资高的所有员工信息。 Select * From salary ,Person , dept where salary.p_no=Person.p_no and dept.deptno=Person.deptno and fact(select fact from salarywhere p_no=000005)(11) 查询比部门0003所有员工实发工资都高的员工。 Select P_no from salaryWhere fact( Select max(fact) from salary, person Where salary.p_no= person.p_no and Deptno=0003 group by Deptno )(12) 查询实发工资比平均实发工资高的员工代码及实发。Select p_no,factfrom salarywhere fact(Select avg(fact) from salary)(13) 使用UNION查询。要求：利用SQL语句分别查询具有高、中级职称的职工姓名，合并输出。Select p_name from personwhere prof=高级unionSelect p_name from personwhere prof=中级五、实验注意事项1注意嵌套查询的父子层嵌套关系；2注意连接查询的连接条件、相关表。实验五 数据完整性与管理一、实验目的理解数据完整性的意义；掌握数据备份方法；掌握数据库恢复方法。二、原理说明（1）创建、删除规则语言: CREATE RULE sex_rule AS sex IN(男， 女)Sp_bindrule sex_rule, person.sex Sp_unbindrule person.sexDROP RULE sex_rule（2）测试完整性约束： 主表、从表的概念；主码、外码约束的意义。三、实验环境安装了Microsoft SQL Server 2005的计算机四、实验内容（1）学生实践创建规则的语言使用；（2）对数据库中的数据进行更新、删除操作，测试是否会成功；如果不成功，分析数据库操作失败的原因；A. 测试对主表进行插入、更新及删除操作时的影响。要求： 向表dept中插入一行数据(0004，研发部)，测试是否影响从表。insert into dept values(0004,研发部) (1 行受影响)注：不影响从表。 将表dept中的部门号0003改为0006，测试是否影响从表。update dept set deptno=0006where deptno=0003 (1 行受影响) 注：影响从表。 删除表dept中部门号为0001的员工数据，测试是否影响从表。delete from deptwhere deptno=0001 注：影响从表。B. 测试对从表进行插入、更新及删除操作时的影响。要求： 向表person中插入一行数据(000012，宋全礼，男，1980-7-17，初级，0005)，测试是否违背参照完整性。insert into person values(000012,宋全礼,男,1980-7-17,初级,0005)注：违背了参照完整性 将表person中工号为000002的员工所在部门更新为0005，测试是否违背参照完整性。 update person set deptno=0005where p_no=000002 注：违背了参照完整性删除表person中工号为000005的员工数据，测试是否违背参照完整性。delete from person where p_no=000005 注：违背了参照完整性（3）数据备份和恢复操作。 创建备份设备PubsBac在企业管理器下，展开服务器组，然后展开服务器。展开“管理”文件夹，右击“备份”，然后单击“新建备份设备”命令。在出现的对话框“名称”框中输入该命名设备的名称PubsBac，然后单击“文件名”按纽，输入磁盘备份设备所使用的文件