华 南 师 范 大 学 实 验 报 告学生姓名学号专业计算机科学与技年级班级课程名称数据库原理与应用实验项目 数据库基本应用和MSSQL 使用实验类型验证设计综合实验时间实验指导老师实验评分一、软件实验报告要求1、上机完成实验内容，主要是编写、运行和分析SQL，请预先自行判断 SQL 的运行结果（例如：查询结果包含哪些数据；更新后关系数据如何变化），然后与实际的运行结果比较，以此发现和修正SQL 编写中的错误。2、提交实验报告。可在上下载实验报告的模板。3、本次实验报告另外作为第四章的作业，上交时间另行通知。二、实验内容一、数据定义用 SQL 创建 SPJ数据库CREATETABLE dbo.S(SNO char(2)NOTNULL,SNAME nchar(20)NULL,STATUS char(2)NULL,CITY nchar(20)NULL,CONSTRAINT PK_S PRIMARYKEY CLUSTERED (SNO ASC);CREATETABLE dbo.P(名师资料总结-精品资料欢迎下载-名师精心整理-第 1 页，共 6 页 -PNO char(2)NOTNULL,PNAME nchar(20)NULL,COLOR nchar(10)NULL,WEIGHT int NULL,CONSTRAINT PK_P PRIMARYKEY CLUSTERED (PNO ASC);CREATETABLE dbo.J(JNO char(2)NOTNULL,JNAME nchar(20)NULL,CITY nchar(20)NULL,CONSTRAINT PK_J PRIMARYKEY CLUSTERED (JNO ASC);CREATETABLE dbo.SPJ(SNO char(2)NOTNULL,PNO char(2)NOTNULL,JNO char(2)NOTNULL,QTY int NULL);用 SQL 创建 SPJ数据库中的各个关系，要求为属性选择合适的数据类型CREATETABLE dbo.S(SNO char(2)NOTNULL,SNAME nchar(20)NULL,STATUS char(2)NULL,CITY nchar(20)NULL,CONSTRAINT PK_S PRIMARYKEY CLUSTERED (SNO ASC);CREATETABLE dbo.P(PNO char(2)NOTNULL,PNAME nchar(20)NULL,COLOR nchar(10)NULL,WEIGHT int NULL,CONSTRAINT PK_P PRIMARYKEY CLUSTERED (PNO ASC);CREATETABLE dbo.J(JNO char(2)NOTNULL,名师资料总结-精品资料欢迎下载-名师精心整理-第 2 页，共 6 页 -JNAME nchar(20)NULL,CITY nchar(20)NULL,CONSTRAINT PK_J PRIMARYKEY CLUSTERED (JNO ASC);CREATETABLE dbo.SPJ(SNO char(2)NOTNULL,PNO char(2)NOTNULL,JNO char(2)NOTNULL,QTY int NULL);根据各个关系的意义和彼此间的联系，自行设计并用 SQL 定义合适的完整性约束，覆盖以下类型：主码约束、外部码约束、非空约束。ALTERTABLEdbo.SPJ WITHNOCHECK ADDCONSTRAINTFK_JNO FOREIGNKEY(JNO)REFERENCES dbo.J(JNO)NOTFOR REPLICATION;ALTERTABLE dbo.SPJ CHECKCONSTRAINT FK_JNO;ALTERTABLEdbo.SPJ WITHCHECKADDCONSTRAINTFK_PNO FOREIGNKEY(PNO)REFERENCES dbo.P(PNO);ALTERTABLE dbo.SPJ CHECKCONSTRAINT FK_PNO;ALTERTABLEdbo.SPJ WITHCHECKADDCONSTRAINTFK_SNO FOREIGNKEY(SNO)REFERENCES dbo.S(SNO);用 SQL 定义一个视图，反映：“三建”工程的供应商号，供应商名，零件号，零件重量，供应数量。SELECT dbo.S.SNOAS供应商号,dbo.S.SNAMEAS供应商名,dbo.P.PNOAS零件号,dbo.P.WEIGHTAS ，零件重量 ,dbo.SPJ.QTYAS供应数量FROMdbo.JINNERJOINdbo.SPJ ONdbo.J.JNO=dbo.SPJ.JNOINNERJOINdbo.PONdbo.SPJ.PNO=dbo.P.PNOINNERJOINdbo.SONdbo.SPJ.SNO=dbo.S.SNO and dbo.J.JNAME=三 建 ;（注：、的 SQL 允许合并，即在同一个SQL 语句中完成关系的定义和相关完整性的定义）二、数据操纵用 SQL 完成：插入各个关系的第一个元组INSERTINTO fmh.dbo.S(SNO,SNAME,STATUS,CITY)名师资料总结-精品资料欢迎下载-名师精心整理-第 3 页，共 6 页 -VALUES(S1,精益,20,天津 );INSERTINTO fmh.dbo.P(PNO,PNAME,COLOR,WEIGHT)VALUES(P1,螺母,红,12);INSERTINTO fmh.dbo.J(JNO,JNAME,CITY)VALUES(J1,三建,北京 );INSERTINTO fmh.dbo.SPJ(SNO,PNO,JNO,QTY)VALUES(S1,P1,J1,200);自行选择方法输入各个关系的剩余元组（不要求编写 SQL）这个是直接编辑前200 行，然后拷贝到数据表中。用 SQL 完成：把 S5供应的所有 P6零件更改为 P4 零件updateSPJ set SPJ.pno=P4where SPJ.sno=S5and SPJ.pno=P6用 SQL 完成：对供应数量总和超过1500 的供应商，供应商状态更改为0。updates set s.status=0where s.sno in(selectspj.sno from spj groupbyspj.sno having sum(spj.qty)1500);用 SQL 完成：删除供应商S2，并且删除 S2的供货信息。deletefromSPJ where sno=S2;deletefromSwhere sno=S2;用 SQL 完成：删除绿色零件的供货信息。deletefromSPJ where pno in(selectpno from pwhere COLOR=绿);三、数据查询用 SQL 完成以下查询至少向北京或天津的工程提供了零件的供应商名，供应的零件名、工程名和供应数量。SELECT dbo.S.SNAMEAS供应商名,dbo.P.pname AS零件名,dbo.P.pname as 工程名,dbo.SPJ.QTYAS供应数量FROMdbo.JINNERJOINdbo.SPJ ONdbo.J.JNO=dbo.SPJ.JNOINNERJOINdbo.PONdbo.SPJ.PNO=dbo.P.PNOINNERJOINdbo.SONdbo.SPJ.SNO=dbo.S.SNO and(dbo.J.city=北京 or dbo.J.city=天津 );名师资料总结-精品资料欢迎下载-名师精心整理-第 4 页，共 6 页 -供应红色零件给工程J1 的供应商名SELECT S.SNAMEAS供应商名FROMJ INNERJOINSPJONJ.JNO=SPJ.JNOINNERJOINPONSPJ.PNO=P.PNOINNERJOINSONSPJ.SNO=S.SNO and p.color=红 and j.jno=J1;没有使用天津产的零件的工程号SELECTdistinctspj.jno FROMspjinnerjoinj on spj.jno=j.jno innerjoins on spj.sno=s.sno innerjoinpon spj.pno=p.pno where s.city 天津 ;没有向位于相同城市的工程供应零件的供应商名SELECTdistincts.sname FROMspjinnerjoinj on spj.jno=j.jno innerjoins on spj.sno=s.sno innerjoinpon spj.pno=p.pno where s.cityj.city;和工程 J3 位于同一城市且使用过至少一种相同零件的其它工程名selectdistinctj.jname from j innerjoinspj on j.jno=spj.jno where j.cityin(selectj.cityfrom j where j.jno=J3)and spj.pno in(selectspj.pno from spj where spj.jno=J3);每种颜色零件的总重量selectp.color,SUM(p.weight)as 总重量 frompgroupbyp.color;红色零件的平均重量selectavg(p.weight)as 平均重量 from pwhere p.color=红;名师资料总结-精品资料欢迎下载-名师精心整理-第 5 页，共 6 页 -供应商最集中的城市（意即，哪个城市的供应商最多？）select city from S groupby CITY havingcount(*)in (selectmax(count(*)from S groupby CITY );对上海和天津，统计该城市的供应商一共供应了多少数量的零件。结果保存到一个新关系 Temp(City,QTYSum)中(至多使用一个 SQL语句用于定义，和一个 SQL语句用于查询)select s.city,sum(spj.qty)from spj innerjoin s on s.sno=spj.sno wheres.city in(上海 ,天津 )groupby s.city 名师资料总结-精品资料欢迎下载-名师精心整理-第 6 页，共 6 页 -