资源描述
2013级数据库原理实验报告专业:_计算机_班级: 学号:姓名: 2015年5月实验一 SQL Server 2005 基本操作一、实验目的了解 SQL Server 2005 组件;了解 SQL Server 数据库组成; 掌握 SQL Server 2005 界面基本操作。二、实验内容(1 )开始-程序-Microsoft SQL Server 2005-SQL Server Management Studio,打开后进入到SQL Server 2005主体界面(2) 点击“数据库”前面的“+”,可以展开查看数据库,并且可以继续展开下 级目录,查看数据库中的表、视图等。(3) 了解SQL Server 2005菜单栏的一些主要工具的使用。( 4)学生动手操作 SQL Server 2005,打开数据库,打开表,打开查询界面;查 看数据库的属性、表的属性等。三、实验总结进行这次实验有遇到什么问题?怎么解决的? 答:此次实验我们一步一步按照实验内容操作的,基本上没有遇到问题。实验二 数据定义一、实验目的掌握 SQL Server 2005 的数据库创建;掌握 SQL Server 数据定义语言;掌握 SQL Server 2005 数据定义的 SQL 语言定义与管理器定义两种方式。二、实验内容(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-1 person 表结构字段名数据类型字段长度允许空否字段说明P noChar6Not Null工号,主键SexChar2Not Null性别BirthDateDatetimeNull出生日期ProfVarchar10Null职称DeptnoChar4Not Null部门代码,外键(参照dept表)表 2-2Salary 表结构字段名数据类型字段长度允许空否字段说明P noChar6Not Null工号,主键,外键(参照person表)BaseDec5Null基本工资BonusDec5Null奖金,要求50Fact实发工资=基本工资+奖金MonthIntNot Null月份表 2-3dept 表结构字段名数据类型字段长度允许空否字段说明DeptnoChar4Not Null部门代码,主键DnameVarchar10Not Null部门名称create table dept(Deptno Char(4) Not Null,primary key(Deptno),Dname Varchar(10) Not Null,)create table person(P_no Char(6) Not Null,primary key(P_no),P_name Varchar(10) Not Null,Sex Char(2) Not Null,BirthDate Datetime Null,Prof Varchar(10) Null,Deptno Char(4) Not Null,foreign key(Deptno) references dept(Deptno), )create table Salary (P_no Char(6) Not Null,foreign key(P_no) references person(P_no),Base Dec(5) Null,Bonus Dec(5) Null,Fact as Base+Bonus,Month Int Not Null, check (Bonus50),)(3) 利用 SQL 语句创建视图。要求:在基表person上创建员工视图PersonView,其中包含工号、姓名、性别、 职称和部门代码等字段。create view PersonView (P_no,P_name,Sex,Prof,Deptno) asselect P_n,P_name,Sex,Prof,Deptno from person(4) 创建索引。要求: 在人员表的姓名列上创建一个单列索引 name_sortcreate index name_sorton person(P_name); 在人员表的出生日期列和姓名列上创建一个组合索引 birth_namecreate clustered index birth_nameon person(BirthDate,P_name);在人员表的姓名列上创建一个唯一索引 u_name_sortcreate unique index u_name_sorton person(P_name);创建月薪表上基于 fact 的索引 fact_idxcreate index fact_idxon salary(fact);(5) 删除索引。 要求:删除月薪表上的索引 fact_idxdrop index salary.fact_idx;三、实验总结1在表的创建的时候要注意什么问题? 答:完整性约束语的应用语法, 表间关系,和建立顺序。2自己在这次实验中遇到什么问题?怎么解决的? 答:视图和索引的标点要不要加的问题。多尝试找到正确的。实验三 数据操作一、实验目的掌握 SQL Server 的数据操作语言;掌握 SQL Server 数据操作的管理器处理方式; 掌握 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高级0003000008胡溧阳男1979-6-15中级0001000009肖丽丽女1968-8-19中级0002000010黄剑祥男1965-9-19高级0003表 3-2 表 salary 中的数据P noBaseBonusFactMonth00000121003001000002180030010000032800280100000425002501000005230027510000061750130100000724002101000008290020010000092700210100001023002101表 2-3 表 dept 中的数据DeptnoDname0001人事部0002财务部0003市场部insertinto dept (Deptno,Dnamevalues (0001,人事部)insertinto person (P_no,P_name,Sex,BirthDate,Prof,Deptno) values (000001,王云,男,1973-4-7,中级,0001)insertinto salary (P_no,Base,Bonus,Month) values (000001,2100,300,1)(2) 用 SQL 语句修改表中的数据。 要求:将 salary 表中工号为 000006的员工工资增加为 1800,奖金增加为 160。update salaryset Base=1800,Bonus=160where P_no=000006(3) 用SQL语句删除表中的数据。要求:删除 person 表中工号为 000010 的员工数据deletefrom salarywhere P_no=0000010deletefrom personwhere P_no=0000010( 4 )更新视图。要求:将员工视图 PersonView 中姓名为王云的员工职称改为高级。update PersonViewset Prof =高级where P_name= 王云( 5)向视图插入数据。要求:向视图 PersonView 中插入一行数据(000011,刘美萍,女, 中级,0002)。insertinto PersonView (P_no,P_name,Sex,Prof,Deptno) values (000011, 刘美萍, 女, 中级,0002)( 6)删除视图。要求:将视图 PersonView 删除。drop View PersonView;三、实验总结1.在删除员工工号为“000010”的员工数据时遇到了什么问题?必须怎么处理? 答:Person表被salary表引用,所以不能执行该命令。必须先将salary表中的 员工工号为“000010”的员工数据删除,才能删除Person表中员工工号为“000010” 的员工数据。2对于三个表来说,插入数据的顺序应该是怎样的?答:先插入表dept,再插入Person表,最后插入salary表。实验四 数据查询一、实验目的掌握 SQL Server 的数据查询语言; 掌握 SQL 语言在不同情况下的灵活应用。二、实验内容(1) 利用 SQL 语句查询 person 表中的所有数据select *from person;二I结果| |j消息P_FWP_nDmeScsBirth DotePrufDeptra1;000001 i主云1 加旳 OdflftttkttW高级00012000002197M2-1JOO:HB(MO中無0013000005車皓然137IHJ&-25 00WOOO00024000004文1979-OSOGOO:flftM.WOVB00025000005染玉邀阳7MS25 OttOttWHMO中銭003卡00Q0W罗向东1J7M5-11 WWW0K初级QWJ7000007肖舷男1963-07-1J 00:-M.M DOOOODSBJJOOOQB胡廉阳1979)6-15 0a:flttfl0_0(0巾簸0013OQOOD9肖丽丽女196&如 9 00 (Km OO0申级002TOOOODn划塞?女NUU.中级0D02(2) 条件查询。要求:查询person表中所有不重复的职称select distinct Prof from person二结果消息Prof1祈聂”2 “誨3 中级查询person表中职称为中级的所有员工数据select *from personwhere Prof=中级二结果消息PjioP_nameSexBirthDateProfDeptno1DDDDD2谢志文卑19754)2-14 DD:DD:DD.DDD中级DDD12DDDDD5梁玉琼女197MS-25DD:DD:DD.DDD中级DOTS3DOTOTB胡漂阳卑1979-06-15 DD:DD:DD.DDD中级DDD14(HHKH肖丽丽女196MS-19DD:DD:DD.DDD中级DOT2 查询person表中具有高级职称的男员工信息select *from personwhere prof=高级and Sex=男;J结果消息P_noP_nameSexBirthDateProfDeptna1| DDDD03李浩炳男197&-25Cu:ut:uG.CuD昌级DDD22DDDD07肖家庆男19G347-14C1:IC.LID咼级D脑3DDDD1D堇劃祥H19654圧19覺:吧血常3高级DDDi 查询 person 表中姓名为王云、谢志文或罗向东的员工数据select *from personwhere P_name= 王云 or P_name= 谢志文 or P_name= 罗向东结果一消息卩_口0P_nameSexBirth DateProfDeptno1DDDDD1i王云男1905417DD:DD:DD.DD)高级DDD12WHMHJ2谢志文男197542-14DD:DD:DD.DD)中级DDD13DDDDDE罗向东男197M511 DD:DD:DD.DDD初级W3(3) 使用 ORDER BY 排序要求:利用 SQL 语句将工号在 000003 和 000008之间的员工的月收入按实发工资升 序排列。select Factfrom Salarywhere P_no between 000003and000008order by Fact asc(4) 查询工号为 000002 的员工的基本工资改为原来的 2 倍,奖金改为原来的 1.5 倍后的实际收入。/先执行修改 update 再查询/直接在 select 中用表达式 fact= Base*2+ Bonus*1.5select Fact=Base*2+1.5*Bonus from Salarywhere P_no=000002(5) 利用 SQL 语句查询各部门的实发工资总数select Dname, sum (Fact)实发工资总数 from person,salary,deptwhere person.P_no=salary.P_noand person.Deptno=dept.Deptno group by Dnamen结聚二消息1game实岌工资总毅1册冨割丨374D2人華部76003市览部7W5(6) 利用 SQL 语句查询 1月份发放奖金平均数大于 200元的部门,并从低到高排 序。select Dname,avg (Bonus) sslfrom dept,salary,personwhere Salary.P_no=person.P_noand dept.Deptno=person.Deptnoand Month=1group by Dname having avg (Bonus)200order by ssl /*avg (Bonus)*/ asc務结果 切消息name5Sl1市场部213.75WDD2财劳部24S.6666663人事部331.666666(7) 查询人事部所有员工信息。select *from person,Salary,deptwhere Salary.P_no=person.P_noand dept.Deptno=person.Deptno and Dname=人事部J结果一消息P_noP_nameSexBirth DateProfDept.PoBaseBonusFactMonthDept.name1| DDDDD1王云男19D5417DD:DD:W.DDD高级DDD1DDDDD1210030D24OT1DDD1人事部2D0DDD2谢志文男197542-14DD:DD:D0.D0D中级DDD10DDDD2720067573751DDD1人事部3WHHHJS胡漂阳男197M6-15DD:DD:DD.DDD中级DDD1ODDDDS29M20D31001DDD1人事部(8) 查询 person 表中职称为中级的员工信息select *from personwhere Prof=中级J结果一消息P_noP_nameSexBirth DateProfDeptno1DDDD02谢志文卑197542-14DD:DD:DD.0DD中级DDD12DDDDD5梁玉琼女1970425 DD:DD:DD.ODD中级0D033DOOMS胡漂阳卑19794-15DD:DD:DD.DDD中级DDD14(HKHH肖丽丽女1968419 DD:DD:DmD中级0OT2(9) 查询每个员工 1月份的工资和奖金。(10) 查询比工号为 000005的员工实发工资高的所有员工信息select *from person,dept,Salarywhere Salary.P_no=person.P_noand dept.Deptno=person.Deptnoand Fact(select Factfrom Salary,person,deptwhere Salary.P_no=person.P_noand dept.Deptno=person.Deptnoand person.P_no=000005P*oP_nameSexBirth DateProfDept.Dept.name卩_门0BaseBonusFactMonthDDDW2谢志文里19754)2-14 OO:DD:OD.DOO中级DDD1DDD1人事部DDDDD272DD6757B751DDD0D3李浩然197MS-25D0:DD:0D.DDQ高级DDD2DDD2财勞部DDDDD32SDD2SD3DSD1DDD004廖小玲女1979-DS-D6D0:DD:0D.DD初级DDD2DDD2财箸部(HKKM425DD25D275D1DDD0D7肖家庆男196347-14D0:DD:0D.DD0高级DDD3DDD3市场部DDDDD724DD21026101DDOODB胡漂阳里1979-D6-15 00:0D:DD.DDD中级DDD1DDD1人事部DDDDDS29DD2DD31001DDfHKB肖丽丽女196S4S-19D0:DD:0D.DD中级DDD2DDD2财勞部DDDDD927DD21029101(11) 查询比部门 0003所有员工实发工资都高的员工。select P_name from person,dept,Salarywhere Salary.P_no=person.P_noand dept.Deptno=person.Deptnoand Fact (select max(Fact)from Salary,person,deptwhere Salary.P_no=person.P_noand dept.Deptno=person.Deptnoand dept.Deptno=0003(12) 查询实发工资比平均实发工资高的员工工号及实发select person.P_no,Factfrom Salary,person,deptwhere Salary.P_no=person.P_noand dept.Deptno=person.Deptnoand Fact(select avg (Fact) maxlfrom Salary,person,deptwhere Salary.P_no=person.P_noand dept.Deptno=person.Deptno/*group by Dname/* having avg (Fact)*/*/*结果消息卩_仃0Fact10DDD027B75(13)使用UNION查询。要求:利用 SQL 语句分别查询具有高、中级职称的职工姓名,合并输出 select P_namefrom personwhere Prof=中级unionselect P_namefrom personwhere Prof= 高级三、实验总结1在查询的语句中,如果涉及到的属性列多个表都有,那应该怎么处理? 答:用表名.属性列名来指定用哪个列的数据来完成操作。2如果查询的数据列没有列名,为了明白查询的数据是什么含义,可以怎样处 理?答:可以用数据列数据 数据列名的方式给数据列附上列名。3.写出 SQL 语言完整的查询语句,并标出 DBMS 执行的先后顺序。答:先执行 from 语句,再执行 where 语句的执行条件,有 group by 的执行分 组操作和 having 语句里的表达式,最后输出 select 里有的数据列里的数据。实验五 数据完整性与管理一、实验目的理解数据完整性的意义; 掌握数据备份方法; 掌握数据库恢复方法。二、实验内容(1)学生实践创建规则的语言使用;CREATE RULE sex_rule AS sex N(男,女卜游离规则。Sp_bindrule sex_rule,person.sex-绑定规则-已将规则绑定到表的列。Sp_unbindrule person.sex-解除绑定。DROP RULE sex_rule-删除规则。(2)对数据库中的数据进行更新、删除操作,测试是否会成功;如果不成功,分析 数据库操作失败的原因;A. 测试对主表进行插入、更新及删除操作时的影响。要求: 表 dept 中插入一行数据 (0004,研发部),测试是否影响从表。insertinto dept (Deptno,Dname)values (0004,研发部)/不影响,插入成功,因为是新插入的一行数据,所以对从表不受影响 表 dept 中的部门号 0003 改为 0006 ,测试是否影响从表。update deptset Deptno=0006where Deptno=0003/影响,不成功person表中有部门号这个属性,所以会受到牵制,需先改变 dept 表中的数据。显示:UPDATE语句与REFERENCE约束FK_person_Deptno_7F60ED59冲突。该冲突发生于数据库Employee,表dbo.person, column Deptno。语句已终止。删除表dept中部门号为0001的员工数据,测试是否影响从表。deletefrom deptwhere Deptno=0001/影响,不成功person表中有部门号这个属性,所以会受到牵制,需先改变 dept 表中的数据。显示: DELETE 语句与 REFERENCE 约束FK_person_Deptno_7F60ED59冲突。该冲突发生于数据库Employee,表dbo.person, column Deptno。语句已终止。B. 测试对从表进行插入、更新及删除操作时的影响。要求: 表 person 中插入一行数据 (000012,宋全礼,男,1980-7- 17,初级,0005),测试是否违背参照完整性。insertinto person (P_no,P_name,Sex,BirthDate,Prof,Deptno)values (000012,宋全礼,男,1980-7-17,初级,0005)/影响,不成功,dept表中有部门号这个属性,为外码,所以会受到牵制,需 先处理 dept 表显示: INSERT 语句与 FOREIGN KEY 约束FK_person_Deptno_7F60ED59冲突。该冲突发生于数据库Employee,表dbo.dept, column Deptno。语句已终止。 将表person中工号为000002的员工所在部门更新为0005,测试是否违背参照完 整性。update personset Deptno=0005where P_no=000002/影响,不成功, dept 表中有部门号这个属性,为外码,所以会受到牵制,需先处理 dept 表显示: UPDATE 语句与 FOREIGN KEY 约束FK_person_Deptno_7F60ED59冲突。该冲突发生于数据库Employee,表dbo.dept, column Deptno。语句已终止。 删除表person中工号为000005的员工数据,测试是否违背参照完整性。deletefrom personwhere P_no=000005/影响,不成功, dept 表中有部门号这个属性,为外码,所以会受到牵制,需 先处理 dept 表显示:DELETE语句与REFERENCE约束FK_Salary_P_no_014935CB冲突。该冲突发生于数据库Employee,表dbo.Salary, column P_no。语句已终止。(3)数据备份和恢复操作。*卜计算机卜文件(E:)卜詢 卜M0241392227查看M 工具E 帮助fH1令副直|宰T点|宗名称iV Empdatl.mdf.呈面V Empdat2.ndf.最近访问的电J Emplog.ldf.Employee, b日k.三、实验总结1数据库的完整性包含哪些完整性约束?在 SQL 语言里面分别是用哪些关键词实 现的?答:域完整性:实现域完整性可以通过:核查约束“check约束”、外键约束 “foreign key约束”、默认值约束“default约束”、非空约束“not null约束”等 来实施。实体完整性:列的Identity属性、主键约束“primany key约束”、惟一性约束 “unique约束”等来实现。参照完整性:可以通过“外键约束”、“触发器”、“存储过程”等来实施。 2数据库备份有什么好处? 答:如操作失误或数据丢失,可回到之前的数据状态。实验心得(字体:宋体小四;段落:首行缩进2 字符、行距:固定值20 磅)通过这次的数据库原理实验,让我了解了 SQL Server 2005组件、SQL Server数 据库组成,数据库的属性,表的属性;掌握SQL Server 2005界面的基本操作,掌握 了解SQL Server数据定义语言;学会了 SQL Server 2005的数据库创建、修改、删 除, SQL Server 的数据操作语言, SQL Server 数据操作的管理器处理方式,以及 SQL Server 2005 定义视图语言、数据查询语言、数据备份和恢复方法,理解了数据完整性 的意义。在之前的十几周上的是数据库系统概论课,我们学的不仅是数据库的理论知识, 还有写数据操作语言。之后是四周的上机实验课。通过理论和实验的操作,让我们加 深对数据库的知识的了解和认识。有了上机实验课的操作,更加巩固了我们课上所学 的知识,做到学以致用。由于,对数据库知识掌握得不牢固,在实验课中我会不记得 要编写的语句,还要现翻课本查找,但在查找的过程也让我又复习了一遍知识,加深 了印象。数据库是数据管理的有效技术,是计算机科学的重要分支。而信息资源在当今社 会是很重要的,已成为各个部门的重要财富和资源。作为信息系统核心和基础的数据 库技术应用越来越广泛。人们可以直接访问并使用数据库,例如通过网上订购火车 票、机票,等等。数据库已经成为每个人生活中不可缺少的部分。所以我们要更加努 力和认真地学好数据库,用以丰富我们的知识。
展开阅读全文