19实验报告

实验一：数据定义及更新语句练习一、实验目的熟练掌握用SQL语句实现数据库和基本表的创建以及数据的更新。二、实验内容（1）用SQL语句建立如下以自己名字为名的数据库，包括S，P，J，和SPJ四个基本表(教材第二章习题5中的四个表)，要求实现关系的三类完整性。S(SNO,SNAME,STATUS,CITY);P(PNO,PNAME,COLOR,WEIGHT);J(JNO,JNAME,CITY);SPJ(SNO,PNO,JNO,QTY)；（2）分别使用插入、删除、修改的方式更新基本表中的数据。a.利用Insert语句将习题中给出的示例记录插入各表。b.利用Update更新表中的记录：①将p表中的所有红色零件的重量增加5。②将spj表中所有天津供应商的QTY属性值减少10。用子查询。c.利用Delete语句删除p表中的所有红色零件的记录。三、完成情况1.创建四个基本表：CREATETABLES(SNOCHAR(20)PRIMARYKEY,SNAMECHAR(20),STATUSINT,CITYCHAR(20));CREATETABLEP(PNOCHAR(20)PRIMARYKEY,PNAMECHAR(20),COLORCHAR(10),WEIGHTINT);CREATETABLEJ(JNOCHAR(20)PRIMARYKEY,JNAMECHAR(20),CITYCHAR(20));CREATETABLESPJ(SNOCHAR(20),PNOCHAR(20),JNOCHAR(20),QTYINT,PRIMARYKEY(SNO,PNO,JNO),FOREIGNKEY(SNO)REFERENCESS(SNO),FOREIGNKEY(PNO)REFERENCESP(PNO),FOREIGNKEY(JNO)REFERENCESJ(JNO));-1-2.利用INSERT语句为四个基本表插入数据（每个表的插入操作均只以两个数据插入为例，其余省略）：INSERTINTOSVALUES('S1','精益','20','天津');INSERTINTOSVALUES('S2','盛锡','10','北京');INSERTINTOPVALUES('P1','螺母','红','12');INSERTINTOPVALUES('P2','螺栓','绿','17');INSERTINTOJVALUES('J1','三建','北京');INSERTINTOJVALUES('J2','一汽','长春');INSERTINTOSPJVALUES('S1','P1','J1','200');INSERTINTOSPJVALUES('S1','P1','J3','100');3.利用UPDATE语句更新表中的记录：UPDATEPSETWEIGHT=WEIGHT+5WHERECOLOR='红';UPDATESPJSETQTY=QTY-10WHERESNOIN(SELECTSNOFROMSWHERECITY='天津');4.利用Delete语句删除p表中的所有红色零件的记录DELETEFROMSPJWHEREPNOIN(SELECTPNOFROMPWHERECOLOR='红');DELETEFROMP-2-WHERECOLOR='红';四、实验结果1.利用INSERT语句为四个基本表插入数据：2.利用UPDATE语句更新表中的记录：3.利用Delete语句删除p表中的所有红色零件的记录五、问题与解决-3-1.如何保存数据库？原以为需要用一次输入一次，后来通过老师讲解知道可以分离数据库（只保留.mdf文件就行了，到用时在附加数据库），还可以备份数据库。2.为基本表P插入数据出错。在定义基本表P时，为PNAME列定义了UNIQUE约束条件，但插入数据时发现PNAME有两个相同的数据，导致不能正确插入数据。经老师提示后，选择利用DROP语句删除SPJ表和P表，进行修改重新定义，再次插入数据时便可顺利插入。3.用Delete语句删除p表中的所有红色零件的记录时，直接在P表中进行相应操作，导致出现：“DELETE语句与REFERENCE约束FK__spj__pno__4F7CD00D冲突。该冲突发生于数据库myspj，表dbo.spj,column'pno'。语句已终止”的情况。经老师提示后，发现p.pno作为spj.pno的外码，两张表有关系，需先在SPJ表中进行删除操作，然后才能在P表中进行删除操作，这样方可顺利完成p表中的所有红色零件记录的删除操作。六、思考题（1）在为各表设定关键字时弹出的页面中有关键字和索引选项，试通过给基本表设定这两个项目并说明二者的区别。答：关键字会受主码的影响，而索引不受主码影响，可以为任何属性设索引。索引可以加快表的查询速度，经常将用来查询的一个或者几个字段设置为索引。（2）基本表中输入数据时，注意数据与字段的类型和长度以及能否为空的属性是否一致，保存时如有错误分析错误原因，及时改正并将错误报告写在实验报告中。答：在基本表中插入数据时，不为空的地方不可输入空值，否则将无法继续插入。七、实验总结实验刚开始时，因为之前没有接触过SQLServer软件，导致有点不知所措，更不知从何下手，在老师的指导和同学们的帮助下，渐渐学会了如何进行实验操作。感觉数据库还是比较好学的（相对编程来说），但是建数据库的时候容易出错，工作比较繁琐，建表时更要考虑周全并仔细检查，稍不小心，就可能导致前功尽弃，导致进行重新建表、插入数据等一系列操作。-4-实验二：简单查询和连接查询一、实验目的熟练掌握用SQL语句实现的简单查询和多个数据表连接查询。二、实验内容（一）完成下面的简单查询：①查询所有“天津”的供应商明细；②查询所有“红色”的14公斤以上的零件。③查询工程名称中含有“厂”字的工程明细。（二）完成下面的连接查询：①等值连接：求s表和j表的相同城市的等值连接。②自然连接：查询所有的供应明细，要求显示供应商、零件和工程的名称，并按照供应、工程、零件排序。③笛卡尔积：求s和p表的笛卡尔积。④左连接：求j表和spj表的左连接。⑤右连接：求spj表和j表的右连接。三、完成情况（一）简单查询①查询所有“天津”的供应商明细SELECT*FROMSWHERECITY='天津';②查询所有“红色”的14公斤以上的零件SELECT*FROMPWHERECOLOR='红'ANDWEIGHT='14';③查询工程名称中含有“厂”字的工程明细SELECT*FROMJWHEREJNAMELIKE'%厂';（二）连接查询①等值连接SELECTS.*,J.*FROMS,JWHERES.CITY=J.CITY;②自然连接SELECTSNAME,JNAME,PNAME,QTYFROMS,P,J,SPJWHERES.SNO=SPJ.SNOANDP.PNO=SPJ.PNOANDJ.JNO=SPJ.JNO;-5-③笛卡尔积SELECT*FROMS,P;④左连接SELECT*FROMJLEFTOUTERJOINSPJON(J.JNO=SPJ.JNO);⑤右连接SELECT*FROMSPJRIGHTOUTERJOINJON(SPJ.JNO=J.JNO);四、实验结果（一）简单查询①查询所有“天津”的供应商明细②查询所有“红色”的14公斤以上的零件③查询工程名中含“厂”字的工程明细（二）连接查询①等值连接②自然连接-6-③笛卡尔积④左连接⑤右连接-7-五、问题与解决1.由于实验一对表中数据有所更改，进行实验二时不得不对实验数据再次进行更改，添加和删除。解决：对有影响的数据进行更改，而没有影响的数据不做改变。2.对多个表进行连接时，数据有时会被打乱。解决：对每一个数据名称前都加上所在表的前缀，以便于区分数据。3.对于存在量词和全称量词无法正确把握，不能得出正确的查询结果。解决：对书中的数据查询进行逐句逐字的理解，以正确把握存在量词和全称量词的运用。4.在做自然连接时，查询出来的结果有一百多行。解决办法：仔细检查之后发现，少了一个连接SelectSPJ.PNO=P.PNO。六、思考题查看实验内容2中左连接和右连接的执行结果是否一致，为什么？答：不一致，左连接是输出第一个表的全部，二右连接是输出第二个表的全部。原因：左连接列出左边关系中所有的元组，右连接列出右边关系中所有的元组。七、实验总结通过本次实验，我发现自己对课本上的知识点的理解还不够透彻，前面所学的知识也忘了不少，做实验时几乎都是翻着书本做出来的。如求s和p表的笛卡尔积时，就感觉有点陌生了，不知道从哪着手，本以为会很麻烦，询问同学后才发现简单的“SELECT*FROMS,P;”语句就可以解决，由此可见自己对知识点的理解还不够到位。-8-实验三：分组查询和嵌套查询一、实验目的熟练掌握用SQL语句实现多个数据表的分组查询和嵌套查询。二、实验内容（一）分组查询：1、求各种颜色零件的平均重量。2、求北京供应商和天津供应商的总个数。3、求各供应商供应的零件总数。4、求各供应商供应给各工程的零件总数。5、求使用了100个以上P1零件的工程名称。6、求各工程使用的各城市供应的零件总数。（二）嵌套查询：1、in连接谓词查询：①查询没有使用天津供应商供应的红色零件的工程名称。②查询供应了1000个以上零件的供应商名称。(having)2、比较运算符：求重量大于所有零件平均重量的零件名称。3、Exists连接谓词：①查询供应J1的所有的零件都是红色的供应商名称。②至少用了供应商S1所供应的全部零件的工程号JNO。三、完成情况(一)分组查询1.求各种颜色零件的平均重量SELECTCOLOR,AVG(WEIGHT)FROMPGROUPBYCOLOR;2.求北京供应商和天津供应商的总个数SELECTCITY,COUNT(CITY)FROMSGROUPBYCITYHAVINGCITY='北京'ORCITY='天津';3.求各供应商供应的零件总数SELECTSPJ.SNO,SUM(QTY)FROMSPJGROUPBYSPJ.SNO;4.求各供应商供应给各工程的零件总数SELECTSPJ.SNO,JNO,SUM(QTY)FROMSPJGROUPBYSPJ.SNO,JNO;5.求使用了100个以上P1零件的工程名称SELECTJNAMEFROMSPJ,J-9-WHEREJ.JNO=SPJ.JNOANDPNO='P1'GROUPBYJNAME,QTYHAVINGQTY100;6.求各工程使用的各城市供应的零件总数SELECTJNAME,S.CITY,SUM(QTY)FROMSPJ,J,SWHEREJ.JNO=SPJ.JNOANDS.SNO=SPJ.SNOGROUPBYJNAME,S.CITY;（二）嵌套查询1.in连接谓词查询：①查询没有使用天津供应商供应的红色零件的工程名称。SELECTJNO,JNAMEFROMJWHEREJNONOTIN(SELECTJNOFROMSPJWHERESNOIN(SELECTSNOFROMSWHERECITY='天津')ANDPNOIN(SELECTPNOFROMPWHERECOLOR='红'));②查询供应了1000个以上零件的供应商名称SELECTSNO,SNAMEFROMSWHERESNOIN(SELECTSNOFROMSPJGROUPBYSNOHAVINGSUM(QTY)1000);2.比较运算符：求重量大于所有零件平均重量的零件名称SELECTPNAMEFROMPWHEREWEIGHT(SELECTAVG(WEIGHT)FROMP);3.Exists连接谓词：①查询供应J1的所有的零件都是红色的供应商名称SELECTSNO,SNAMEFROMSWHEREEXISTS-10-(SELECTJNOFROMSPJWHEREJNO='J1'ANDPNOIN(SELECTPNOFROMPWHERES.SNO=SPJ.SNOANDCOLOR='红'));②至少用了供应商S1所供应的全部零件的工程号JNOSELECTDISTINCTJNOFROMSPJX