数据库实验报告河南科技大学.doc

数据库实验报告班级名称：计科113班学生学号：111404060313学生姓名：李晓贝实验时间：201305实验一：数据定义及更新语句练习一、 实验目的：熟练掌握用SQL语句实现数据库和基本表的创建、数据的更新。二、 实验内容：（一）用SQL语句建立如下mySPJ数据库，包括S，P，J，和SPJ四个基本表(教材第二章习题5中的四个表)，要求实现关系的三类完整性。S(SNO,SNAME,STATUS,CITY);P(PNO,PNAME,COLOR,WEIGHT);J(JNO,JNAME,CITY);SPJ(SNO,PNO,JNO,QTY)供应商表S由供应商代码（SNO）、供应商姓名（SNAME）、供应商状态（STATUS）、供应商所在城市（CITY）组成。零件表P由零件代码（PNO）、零件名（PNAME）、颜色（COLOR）、重量（WEIGHT）组成。工程项目表J由工程项目代码（JNO）、工程项目名（JNAME）、工程项目所在城市（CITY）组成。供应情况表SPJ由供应商代码（SNO）、零件代码（PNO）、工程项目代码（JNO）、供应数量（QTY）组成，表示某供应商 供应某种零件 给某工程项目的数量为QTY。（2） 分别使用插入、删除、修改的方式更新基本表中的数据。三、完成情况create table s(sno char(9) primary key, /主键约束sname char(20) unique , /唯一值status smallint ,city char(20),);create table p(pno char(9) primary key, /主键约束pname char(20) , color char(9),weight smallint,);create table j(jno char(9) primary key, /主键约束jname char(20) unique, /唯一值city char(20),);create table spj(sno char(9),pno char(9),jno char(9),QTY smallint,primary key(sno,pno,jno), /主键约束foreign key (sno) references s(sno), /外键约束foreign key (pno) references p(pno), /外键约束foreign key (jno) references j(jno), /外键约束);四、实验结果1、利用Insert 语句将习题中给出的示例记录插入各表insert into s(sno,sname,status,city)values (s1,精益,20,天津)insert into svalues (s2,盛锡,10,北京)insert into svalues (s3,东方红,30,北京)insert into svalues (s4,丰泰盛,20,天津)insert into svalues (s5,为民,30,上海)select *from sinsert into pvalues (p1,螺母,红,12)insert into pvalues (p2,螺栓,绿,17)insert into pvalues (p3,螺丝刀,蓝,14)insert into pvalues (p4,螺丝刀,红,14)insert into pvalues (p5,凸轮,蓝,40)insert into pvalues (p6,齿轮,红,30)select *from pinsert into jvalues (j1,三建,北京)insert into jvalues (j2,一汽,长春)insert into jvalues (j3,弹簧厂,天津)insert into jvalues (j4,造船厂,天津)insert into jvalues (j5,机车厂,唐山)insert into jvalues (j6,无线电厂,常州)insert into jvalues (j7,半导体厂,南京)select *from jinsert into spjvalues (s1,p1,j1,200)insert into spjvalues (s1,p1,j3,100)insert into spjvalues (s1,p1,j4,700)insert into spjvalues (s1,p2,j2,100)insert into spjvalues (s2,p3,j1,400)insert into spjvalues (s2,p3,j2,200)insert into spjvalues (s2,p3,j4,500)insert into spjvalues (s2,p3,j5,400)insert into spjvalues (s2,p5,j1,400)insert into spjvalues (s2,p5,j2,100)insert into spjvalues (s3,p1,j1,200)insert into spjvalues (s3,p3,j1,200)insert into spjvalues (s4,p5,j1,100)insert into spjvalues (s4,p6,j3,300)insert into spjvalues (s4,p6,j4,200)insert into spjvalues (s5,p2,j4,100)insert into spjvalues (s5,p3,j1,200)insert into spjvalues (s5,p6,j2,200)insert into spjvalues (s5,p6,j4,500)select *from spj2. 利用Update更新表中的记录：将p表中的所有红色零件的重量增加5。update pset weight=weight-5where color=红将spj表中所有天津供应商的QTY属性值减少10。用子查询。update spj set QTY =QTY -10where sno in(select sno from swhere city =天津)3. 利用Delete语句删除p表中的所有红色零件的记录。delete from p where color =红DELETE 语句与REFERENCE 约束FK_spj_pno_4F7CD00D冲突。该冲突发生于数据库myspj，表dbo.spj, column pno。语句已终止。（p.pno作为spj.pno的外键，两张表有关系）5、 问题及解决 1、数据库如何保存？本以为要用一次输入一次，后来通过老师讲解知道可以分离数据库（只保留.mdf文件就行了，到用时在附加数据库），还可以备份数据库。2、 输入表是出错？有时候建基本表时容易输错，再往下就不然输入了，只能把整个表删了重建了。 六、思考题：（1） 在为各表设定关键字时弹出的页面中有关键字和索引选项，试通过给基本表设定这两个项目并说明二者的区别。答：索引可以加快表的查询速度，经常用来查询的一个或者几个字段设置为索引。（2） 在基本表中输入数据时，注意数据与字段的数据类型和长度以及能否为空的属性是否一致，保存时如有错误分析错误原因，及时改正并将错误报告写在实验报告中。答：在基本表中输入数据时，不为空的地方不可以输入空值，否则将无法继续输入。七、实验总结：答：建数据库的时候容易出错，工作比较繁琐，稍不小心，不管通过代码添加还是手工输入都很可能稍输或输错。最后还需要仔细检查才行，语句方面还都是一些简单的语句，可以完成。对于这次试验的基础还是可以把握的，通过这一次的实验，对数据库有了一个全新的认识，同时也对开发环境有了一定的了解，生动的实验比单调的课本理论知识有意思多了！实验二：简单查询和连接查询1、 实验目的：熟练掌握用SQL语句实现的简单查询和多个数据表连接查询。2、 实验内容：(一) 完成下面的简单查询：查询所有“天津”的供应商明细；查询所有“红色”的14公斤以上的零件。查询工程名称中含有“厂”字的工程明细。(二) 完成下面的连接查询：等值连接：求s表和j表的相同城市的等值连接。自然连接：查询所有的供应明细，要求显示供应商、零件和工程的名称，并按照供应、工程、零件排序。笛卡尔积：求s和p表的笛卡尔积左连接：求j表和spj表的左连接。右连接：求spj表和j表的右连接。3、 完成情况：（1） 完成下面的简单查询：查询所有“天津”的供应商明细；select *from swhere city =天津查询所有“红色”的14公斤以上的零件select *from pwhere color =红 and weight 14查询工程名称中含有“厂”字的工程明细select *from jwhere jname like%厂（2） 完成下面的连接查询：等值连接：求s表和j表的相同城市的等值连接。select s.*,j.*from s ,jwhere s.city =j.city自然连接：查询所有的供应明细，要求显示供应商、零件和工程的名称，并按照供应、工程、零件排序。select sname,jname,pnamefrom s ,p ,j,spjwhere spj.sno = s.sno and spj.pno = p.pno and spj.jno = j.jno笛卡尔积：求s和p表的笛卡尔积select *from s,p左连接：求j表和spj表的左连接select j.jno,jname,city,spj.sno,pno ,QTYfrom j left join spj on(j.jno=spj.jno)右连接：求spj表和j表的右连接select j.jno,jname,city,spj.sno,pno ,QTYfrom spj right join j on(j.jno=spj.jno)4、 实验结果：（一）查询所有“天津”的供应商明细；查询所有“红色”的14公斤以上的零件查询工程名称中含有“厂”字的工程明细（二）等值连接：求s表和j表的相同城市的等值连接自然连接：查询所有的供应明细，要求显示供应商、零件和工程的名称，并按照供应、工程、零件排序。笛卡尔积：求s和p表的笛卡尔积左连接：求j表和spj表的左连接右连接：求spj表和j表的右连接5、 问题及解决：答：对“笛卡尔积”这一概念不了解，因此在实验时遇到了麻烦，仔细翻阅书本知识后，再加上实验的应用，对这一块的知识点理解不少。6、 思考题：查看实验内容2中左连接和右连接的执行结果是否一致，为什么？一样，j表和spj表的左连接和spj表和j表的右连接本质是一样的，j表和spj表的左连接保留了j表的全部，spj表和j表的右连接还是保留了j表的全部，所以最后查询结果一致。7、 实验总结：答：通过实验的实践队一些概念的理解和掌握有很大的帮助，今后的学习应多动手，多做实验，有助于掌握知识点。实验三：分组查询和嵌套查询一、 实验目的：熟练掌握用SQL语句实现多个数据表的分组查询和嵌套查询。二、 预习要求：实验前复习讲授过的有关分组查询和嵌套查询的知识，编写相应的SQL语句。三、 实验内容：(一) 分组查询：1. 求各种颜色零件的平均重量。2. 求北京供应商和天津供应商的总个数。3. 求各供应商供应的零件总数。4. 求各供应商供应给各工程的零件总数。5. 求使用了100个以上P1零件的工程名称。6. 求各工程使用的各城市供应的零件总数。（二） 嵌套查询：1. in连接谓词查询： 查询没有使用天津供应商供应的红色零件的工程名称。 查询供应了1000个以上零件的供应商名称。(having)2. 比较运算符：求重量大于所有零件平均重量的零件名称。3. Exists连接谓词： 查询供应J1的所有的零件都是红色的供应商名称。 至少用了供应商S1所供应的全部零件的工程号JNO。4、 完成情况及实验结果（1） 分组查询：（2） 1.求各种颜色零件的平均重量。 SELECT COLOR ,AVG(WEIGH)平均重量 FROM P group by color;2. 求北京供应商和天津供应商的总个数。select count(city) as 数目,CITYfrom swhere city=北京 or city=天津group by city3. 求各供应商供应的零件总数。select sno, sum(qty)零件总数from spjgroup by sno4. 求各供应商供应给各工程的零件总数。select sno,jno, sum(qty)零件总数from spjgroup by sno,jno5. 求使用了100个以上P1零件的工程名称。 select jno,sum(qty)as qty from spj where pno=p1 group by jno having sum(qty)100 6. 求各工程使用的各城市供应的零件总数。 select spj.jno,s.city,sum(qty)零件总数 from spj,s where s.sno=spj.sno group by spj.jno,s.city （二） 嵌套查询：4. in连接谓词查询： 查询没有使用天津供应商供应的红色零件的工程名称。select jno,jnamefrom jwhere not exists (select * from spj where spj.jno=j.jno and sno in (select sno from s where city=天津 )and pno in (select pno from p where color=红) 查询供应了1000个以上零件的供应商名称。(having)select sno,snamefrom swhere sno in (select sno from spj group by spj.sno having sum(qty)1000)5. 比较运算符：求重量大于所有零件平均重量的零件名称。select pnamefrom pwhere weigh (select avg(weigh) from p)6. Exists连接谓词： 查询供应J1的所有的零件都是红色的供应商名称。select sno,snamefrom swhere sno in(select distinct snofrom spj xwhere not exists (select * from spj y where y.sno=x.sno and y.jno=j1and not exists (select * from p where p.pno=y.pno and color=红) 至少用了供应商S1所供应的全部零件的工程号JNO。select distinct jnofrom spj spjxwhere not exists (select * from spj spjy where sno=s1 and not exists (select * from spj spjz where spjz.pno=spjy.pno and spjz.jno=spjx.jno)四、 思考题：（1） 嵌套查询中的in连接谓词查询，关键字in可以直接用any代替么？什么情况下in和any可以互相代替？答：关键字 in 不可以直接用any 代替。但“=any”的等价于in。（2） 嵌套查询中的内查询为外查询返回的内容是什么？是表达式？视图？还是物理数据集合？答：含 in，any 的嵌套查询返回的结果通常是一个集合，含exists谓语的子查询不返回任何数据，只产生逻辑值“true”或“false”。(一) 问题及解决：首先写出执行语句不成功的时候系统报告的错误信息。然后分析错误原因，并给出解决办法。select pnamefrom pwhere weight (select avg(weight) from p)分析错误原因：表中列名与SQL中列名不一致，因此导致weight 无效。解决方法：将weight该为weigh 。(二) 实验总结：心得体会，建议等。答：本次实验，使我对书本上的知识有了进一步的了解，尤其是分组查询那部分。只有自己亲自去做去实践，才能发现问题，自己解决问题，更感觉到了实践的重要性，学过的知识应该亲自上机练习一下，这样才能对理论的知识掌握更牢固，理解更透彻。实验四：视图操作和数据控制 一、 实验目的：熟练掌握用SQL语句实现视图操作和数据控制。二、 预习要求：实验前复习讲授过的有关视图操作和数据控制的知识，编写相应的SQL语句。三、 实验内容：（一）定义如下视图：查询北京的供应商的编号、名称和城市。查询S1供应商的所有供应明细。查询各工程名称使用的各种颜色零件的个数。查询上面定义的视图。 （二）数据控制： 使用GRANT把对S表查询的权利授予WangLi。 使用GRANT把对P表查询、插入、修改、删除的权利授予LiMing。 使用REVOKE把LiMing对P表插入、删除的权利回收。4、 完成情况及实验结果（一）定义如下视图：查询北京的供应商的编号、名称和城市。 create view s_bj(sno,sname,city) as (select sno,sname,city from s where city=北京)查询S1供应商的所有供应明细。 create view s1_spj as select sno,pno,jno,qty from spj where sno=s1查询各工程名称使用的各种颜色零件的个数。 create view jno_pcolor_qty(jno,color,qty) as select spj.jno,