PPT《邓云》

邓云 完成情况基本良好大多数同学态度都很端正实验之星:徐晨灿,陈佳威,丁迪童,史少晨,仇浩波

39 67人提交了报告,12人未提交未提交报告的同学:王干,揭宇如,王庆一,李广耀,王嘉良,余岸轩,丁海涛,安东,杜紫薇,彭M,王灏

39 64人提交了报告,15人未提交,1人文件损坏未提交报告的同学:王干,揭宇如,卓炜,王庆一,李广耀,王嘉良,余岸轩,丁海涛,刘一鸣,邓捷,张微,梁雨诗,许玉珏,王灏,汪洲提交作业文件损坏同学:陈晓钟

18 CREARE TABLE S(SNO VARCHAR(3) PRIMARY KEY ,SNAME VARCHAR(24),[STATUS] INT,CITY VARCHAR(24))CREARE TABLE SPJ(SNO VARCHAR(3),PNO VARCHAR(3),JNO VARCHAR(3),QTY INT,PRIMARY KEY (SNO,PNO,JNO),FOREIGN KEY (SNO) REFERENCES S(SNO),FOREIGN KEY (PNO) REFERENCES P(PNO),FOREIGN KEY (JNO) REFERENCES J(JNO)) 在SQLSERVER中,如果列名和关键字同名,则须用"[]"括起来 注意添加外键约束 求供应工程J1零件P1的供应商号码 SELECT SNO FROM SPJ WHERE JNO='J1' AND PNO='P1' 结果:(S1,S3) 求供应工程J1零件为红色的供应商号码,并按其供应数量之和降序排列显示 SELECT SNO FROM SPJ, P WHERE SPJ.

PNO = P.PNO AND P.COLOR='红' AND JNO='J1'GROUP BY SNOORDER BY SUM(QTY) DESC;

结果:(S1,S3) 求没有使用天津供应商生产的红色零件的工程号 SELECT JNO FROM J WHERE JNO NOT IN SELECT JNO FROM SPJ, P, S WHERE SPJ.PNO=P.PNO AND SPJ.SNO=S.SNO AND S.CITY='天津' AND P.COLOR='红' 结果:(J2, J5, J6, J7) SELECT JNO FROMS,P,SPJWHERE S.CITY != '天津' AND COLOR!='红' AND P.PNO=SPJ.PNO AND S.SNO=SPJ.SNO 没有考虑用了天津供应商提供的非红色的零件的工程和用了红色但非天津供应商提供的零件的工程 求被供应零件P1的平均数量大于供应给工程J1的任意零件的最大数量的工程号 SELECT JNO FROM J T1WHERE (SELECT AVG(QTY) FROM SPJ WHERE PNO='P1' AND JNO=T1.JNO) > (SELECT MAX(T2.QTY) FROM SPJ T2 WHERE JNO='J1') 结果:(J4) 嵌套查询和被嵌套查询的关联一定要记得描述 求被供应零件P1的平均数量大于供应给工程J1的任意零件的最大数量的工程号 SELECT DISTINCT SNO FROM SPJ T1 WHERE PNO='P1' AND QTY SELECT AVG(QTY) FROM SPJ T2 WHERE T2.JNO=T1.JNO AND PNO='P1' 结果:空 求至少有一个供应商、零件或工程所在的城市 SELECT CITY FROM S UNION SELECT CITY FROM J 结果:(北京, 长春, 常州, 南京, 上海, 唐山, 天津) b1 b2 bm … B a1 a2 an … A 语义描述:在一个集合A中寻在满足如下条件的元组,该元组在关系C中跟集合B的所有元组都有关系 result a2 a1.id b1.id a2.id b1.id a2.id b2.id a2.id bm.id an.id an.id bm.id b2.id … … C 普通的SQL表示形式:SELECT a FROM A WHERE NOT EXISTS(SELECT * FROM B WHERE NOT EXISTS (SELECT * FROM C WHERE C.AID=A.ID AND C.BID=B.ID)) AID BID 文艺的SQL表示形式:SELECT a FROM A WHERE NOT EXISTS(SELECT * FROM B WHERE B.ID NOT IN (SELECT C.BID FROM C WHERE C.AID=A.ID) )SELECT a FROM A WHERE (SELECT COUNT(*) FROM B)=(SELECT COUNT(DISTINCT C.BID) FROM C WHERE C.AID=A.ID)SELECT a FROM A WHERE NOT EXISTS( (SELECT B.ID FROM B) EXCEPT (SELECT C.BID FROM C WHERE C.AID=A.ID)) 此处默认C中C.BID存在外键引用B.ID.如果没有则需要再添加什么条件呢? 求至少用了供应商S1所供应的全部零件的工程号 SELECT WHERE NOT EXISTS(SELECT NOT EXISTS( 结果:(J4) JNO FROM J T1 * FROM SPJ T2 WHERE T2.SNO='S1' AND SELECT * FROM SPJ WHERE T2.PNO = SPJ.PNO AND T1.JNO = SPJ.JNO 求对所有工程都提供了同一零件的供应商号码 结果:空SELECT DISTINCT SNO FROM SPJ T1 WHERE NOT EXISTS (SELECT * FROM J WHERE NOT EXISTS(SELECT * FROM SPJ T3 WHERE T1.SNO = T3.SNO AND T1.PNO = T3.PNO AND J.JNO = T3.JNO) ) (零件,供应商,工程) / (工程) SELECT T1.SNO, T2.SNO FROM J T1, J T2 WHERE T1.SNO > T2.SNO AND NOT EXISTS(SELECT * FROM SPJ T3 WHERE T3.SNO=T2.SNO AND NOT EXISTS(SELECT * FROM SPJ T4 WHERE T4.PNO = T3.PNO AND T4.SNO=T1.SNO) )ANDNOT EXISTS(SELECT * FROM SPJ T5 WHERE T5.SNO=T1.SNO AND NOT EXISTS(SELECT * FROM SPJ T6 WHERE T6.PNO = T5.PNO AND T6.SNO=T2.SNO) ) 求供应商号码对,其中Sx和Sy供应的零件都相同 结果:空(SX.id,工程) / (SY供应零件的工程工程) (SY.id,工程) / (SX供应零件的工程工程) 第一列供应商提供了了第二列供应商提供的所有零件 第二列供应商提供了了第一列供应商提供的所有零件 将所有工程中红色零件的使用数量加100 UPDATE SPJSET QTY=QTY+100WHERE PNO in(SELECT PNO FROM PWHERE COLOR='红') 删除工程J1和J2都使用的零件及相关记录 步骤:创建临时表,将J1和J2都是用的零件插入临时表中删除SPJ表中相关记录删除P表中相关记录删除临时表 CREATE TABLE #TMP(PNO VARCHAR(3));

INSERT INTO #TMP(PNO) SELECT DISTINCT X.PNO FROM SPJ X, SPJ Y WHERE X.PNO = Y.PNO AND X.JNO = 'J1' AND Y.JNO = 'J2';

DELETE FROM SPJ WHEREPNO IN (SELECT * FROM #TMP);

DELETE FROM P WHERE PNO IN (SELECT * FROM #TMP);

DROP TABLE #TMP;

第一步和第二步也可以用一句select into语句完成.