数据库建立及查询

数据库建立及查询一、实验目的1.熟练掌握用SQL语句实现基本表的创建。熟练掌握用SQL语句实现数据插入、数据更新和数据查询。熟练掌握用SQL语句实现数据的简单查询和连接查询。二、实验环境SQLServer2000三、预习要求SQLServer2000的查询分析器和企业管理器的使用，SQL数据定义和数据操纵语言。四、实验内容（一）用SQL语句建立如下mySPJ数据库，包括S,P,J,和SPJ四个基本表（四张表的数据见附录部分，要求实现关系的三类完整性。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）＞X程项目代码（JNO）、供应数量（QTY）组成，表示某供应商供应某种零件给某工程项目的数量为QTY。（二）分别使用插入、删除、修改的方式更新基本表中的数据。利用Insert语句将习题中给出的示例记录插入各表。InsertintoSPJvalues（'s1','p1','j1',200）利用Update更新表中的记录：将p表中的所有红色零件的重量增加5。updatePsetWEIGHT=WEIGHT+5whereCOLOR=红'将spj表中所有天津供应商的QTY属性值减少10。用子查询。updateSPJsetQTY=QTY-10whereSNO=any（selectSNOfromSwhereCITY=天津'）3.利用Delete语句删除p表中的所有红色零件的记录。deletefromPwhereCOLOR=红'（一）完成下面的简单查询：查询所有“天津”的供应商明细；select*fromSwhereCITY='天津’查询所有“红色”的14公斤以上的零件。select*fromPwhereCOLOR=红'andWEIGHT>=14查询工程名称中含有“厂”字的工程明细。select*fromJwhereJNAMElike'%厂%'（二）完成下面的连接查询：等值连接：求s表和j表的相同城市的等值连接。select*fromJ,SwhereJ.CITY=S.CITY;自然连接：查询所有的供应明细，要求显示供应商、零件和工程的名称，并按照供应、工程、零件排序。selectS.SNAME,J.JNAME,P.PNAMEfromS,J,P,SPJwhereS.SNO=SPJ.SNOandJ.JNO=SPJ.JNOandP.PNO=SPJ.PNO;笛卡尔积：求s和p表的笛卡尔积select*fromP,S左连接：求j表和spj表的左连接。select*fromJ,SPJwhereJ.JNO*=SPJ.JNO;右连接：求spj表和j表的右连接。select*fromJ,SPJwhereSPJ.JNO=*J.JNO;（三）分组查询：求各种颜色零件的平均重量。selectCOLOR,AVG（WEIGHT）fromPgroupbyP.COLOR求北京供应商和天津供应商的总个数。selectS.CITY,COUNT（*）fromSgroupbyS.CITY;求各供应商供应的零件总数。selectSUM(QTY)fromSPJ;求各供应商供应给各工程的零件总数。selectSNO,SUM(QTY)fromSPJgroupbySPJ.SNO;求使用了100个以上P1零件的工程名称。selectJ.JNAMEfromJwhereJ.JNOin(selectSPJ.JNOfromSPJwhereSPJ.PNO='p1'andSPJ.QTY>100);求各工程使用的各城市供应的零件总数。selectS.CITY,sum(SPJ.QTY)fromSPJ,SwhereS.SNO=SPJ.SNOgroupbyS.CITY;嵌套查询：in连接谓词查询：查询没有使用天津供应商供应的红色零件的工程名称。selectJ.JNAMEfromJwhereJNOin(selectJNOfromSPJ,PwhereJ.CITY!=天津'andP.COLOR=红')；查询供应了1000个以上零件的供应商名称。(having)selectJ.JNAMEfromJwhereJ.JNOin(selectSPJ.JNOfromSPJgroupbySPJ.JNOhavingSUM(SPJ.QTY)>1000);比较运算符：求重量大于所有零件平均重量的零件名称。selectP.PNAMEfromPwhereP.WEIGHT>=any(selectavg(WEIGHT)fromP);五、实验方法和步骤(一)使用MicrosoftSQLServer企业管理器和查询分析器建立数据库mySPJ:1.打开"开始一>程序一>MicrosoftSQLServer—>企业管理器"；在企业管理器左边的树标签中依次打开“MicrosoftSQLServer—>SQLServer组一>(local)(WindowsNT)—>数据库"，(local)(WindowsNT)前的红色标记转化为绿色标记表明NT服务已启动；从企业管理器的“工具”菜单中选择“SQL查询分析器”，打开查询分析器后，在其窗口书写创建数据库mySPJ的SQL语句，点击执行按钮(或F5键)执行该SQL语句；在企业管理器左边的树标签中查看数据库是否建立成功。(二)在数据库mySPJ中建立S，P，J，和SPJ四个基本表：按照实验内容给出的基本表结构在查询分析器窗口中书写SQL语句分别建立各表，并设置主键和外键约束；createtables(pnochar10notnull,(snochar10notnull,pnamechar10,snamechar10,colorchar10,stattusint4,weightint4)citychar10)createtablejcreatetablep(jnochar10notnull,

jnomechar10,(snochar10notnull,citychar10)pnochar10notnull,jnochar10notnull,createtablespjqtyint4)（三）更新表数据：1.利用Insert语句将习题中给出的示例记录插入各表。insertintosvalues（s1,精益，20，天津）;values（s2,盛锡，10，北京）；values（s3,东方红，30，北京）;values（s4,丰泰盛，20，天津）;values（s5,为民，30，上海）；30，洛阳）;values(s6,一拖，rLS津京京谨海阳天北北天上洛n-n-_u_u_u-U213233红盛

益锡方泰民拖

精盛东丰为一234565L_1EESinsertintopvalues（p1,螺母，红，12）；values（p2,螺栓，绿，17）；values（p3,螺丝刀，蓝，14）；values（p4,螺丝刀，红，14）；values（p5,凸轮，蓝，40）；values（p6,齿轮，红，30）；得到如下图表：prL：=ifTiecolor30insertintojvalues（J1,三建，北京）；values（J2,一汽，长春）；values（J4，弹簧厂，天津）；values（J5,造船厂，天津）；values（J6，机车厂，唐山）；values（J7，无线电厂，常州）;values（J8,半导体厂，南京）;得到如下图表：irL：dirien三建J2一汽J3弹簧厂J4造船厂J5机车厂J6无戏电厂J7半导体厂京春津津山州京北长天天唐常南1insertintospjvalues（s1,p1，j1，200）；values（s1,p1，j3，100）；values（s1,p1，j4，700）；values（s1,p2，j2，100）；values（s2,p3，j1，400）；values（s2,p3，j2，200）；values（s2,p3，j4，500）；values（s2,p3，j5，400）；values（s2,p5，j1，400）；values（s2,p5，j2，100）；values（s3,p1，j1，200）；values（s3,p3，j1，200）；values(s4,p5,jl,100);values(s4,p6,j3,300);values(s4,p6,j4,200);values(s5,p2,j4,100);values(s5,p3,jl,200);values(s5,p6,j2,200);values(s5,p6,j4,500)；得到如下图表：snoIpnoIjnoIqtyhiplJl200slplJ3100siplJ4700slp2J2100s2p3Jl400s2p3J2200e2p3J4500e2p3j5400e2p5jl400e2p5j2100s3pljl200s3p3jl200s4p5jl100s4p6J3300s4p6J4200p2J4100p3Jl200s5pBJ2200s5pBJ45002.利用Update更新表中的记录。将p表中的所有红色零件的重量增加5。updatepsetweight=weight+5wherecolor='红'；将spj表中所有天津供应商的QTY属性值减少10。updatespjsetqty=qty-10wheresnoin(selectsnofromswherecity='天津');3.利用Delete语句删除表中满足条件的记录。deletefromspjwherepnoin(selectpnofrompwherecolor='红')；deletefrompwherecolor='红'；六、实验中的