1 / 17

实验二 交互式 SQL

实验二 交互式 SQL. 邓云. 实验内容概况. 完成情况基本良好 大多数同学态度都很端正 实验之星：徐晨灿，陈佳威，丁迪童，史少晨，仇浩波. 39. 实验 2.1. 67 人提交了报告， 12 人未提交 未提交报告的同学： 王干，揭宇如，王庆一，李广耀，王嘉良，余岸轩，丁海涛，安东，杜紫薇，彭芃，王灏. 39. 实验 2.2. 64 人提交了报告， 15 人未提交， 1 人文件损坏 未提交报告的同学： 王干，揭宇如，卓炜，王庆一，李广耀，王嘉良，余岸轩，丁海涛，刘一鸣，邓捷，张微，梁雨诗，许玉珏，王灏，汪洲 提交作业文件损坏同学：陈晓钟. 18.

dwight
Download Presentation

实验二 交互式 SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 实验二 交互式SQL 邓云

  2. 实验内容概况 • 完成情况基本良好 • 大多数同学态度都很端正 • 实验之星：徐晨灿，陈佳威，丁迪童，史少晨，仇浩波 39

  3. 实验2.1 • 67人提交了报告，12人未提交 • 未提交报告的同学： 王干，揭宇如，王庆一，李广耀，王嘉良，余岸轩，丁海涛，安东，杜紫薇，彭芃，王灏 39

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

  5. 建表及数据插入 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中，如果列名和关键字同名，则须用“[]”括起来 注意添加外键约束

  6. SQL查询 • 求供应工程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 SNO ORDER BY SUM(QTY)DESC; 结果：(S1,S3)

  7. SQL查询 • 求没有使用天津供应商生产的红色零件的工程号 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,SPJ WHERE S.CITY != '天津' AND COLOR!='红' AND P.PNO=SPJ.PNO AND S.SNO=SPJ.SNO 没有考虑用了天津供应商提供的非红色的零件的工程和用了红色但非天津供应商提供的零件的工程

  8. SQL查询 • 求被供应零件P1的平均数量大于供应给工程J1的任意零件的最大数量的工程号 嵌套查询和被嵌套查询的关联一定要记得描述 SELECT JNO FROM J T1 WHERE (SELECT AVG(QTY) FROM SPJ WHERE PNO='P1' AND JNO=T1.JNO) > (SELECT MAX(T2.QTY) FROM SPJ T2 WHERE JNO='J1') 结果：(J4)

  9. SQL查询 • 求被供应零件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‘ ) 结果：空

  10. SQL查询 • 求至少有一个供应商、零件或工程所在的城市 SELECT CITY FROM S UNION SELECT CITY FROM J 结果：(北京, 长春, 常州, 南京, 上海, 唐山, 天津)

  11. SQL查询——除法 语义描述： 在一个集合A中寻在满足如下条件的元组，该元组在关系C中跟集合B的所有元组都有关系 C B result A AID BID a2 a1 b1 a2 b2 a1.id b1.id a2.id b1.id … … a2.id b2.id a2.id bm.id an bm an.id b2.id an.id bm.id … … 普通的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 ） ）

  12. SQL查询——除法 文艺的SQL表示形式： SELECT a FROM A WHERE NOT EXISTS （SELECT * FROM B WHERE B.IDNOT IN ( SELECT C.BIDFROM 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。 如果没有则需要再添加什么条件呢？

  13. SQL查询 • 求至少用了供应商S1所供应的全部零件的工程号 JNO FROM J T1 SELECT WHERE NOT EXISTS (SELECT NOT EXISTS( ) ) * FROM SPJ T2 WHERE T2.SNO=‘S1’ AND SELECT * FROM SPJ WHERE T2.PNO = SPJ.PNO AND T1.JNO = SPJ.JNO 结果：(J4)

  14. SQL查询 • 求对所有工程都提供了同一零件的供应商号码 (零件，供应商，工程) / (工程) 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) ) 结果：空

  15. SQL查询 • 求供应商号码对，其中Sx和Sy供应的零件都相同 (SX.id，工程) / (SY供应零件的工程工程) (SY.id，工程) / (SX供应零件的工程工程) 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) ) AND NOT 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) ) 第一列供应商提供了了第二列供应商提供的所有零件 第二列供应商提供了了第一列供应商提供的所有零件 结果：空

  16. SQL更新 • 将所有工程中红色零件的使用数量加100 UPDATE SPJ SET QTY=QTY+100 WHERE PNO in( SELECT PNO FROM P WHERE COLOR='红‘ )

  17. SQL删除与临时表 • 删除工程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 WHERE PNO IN (SELECT * FROM #TMP); DELETE FROM P WHERE PNO IN (SELECT * FROM #TMP); DROP TABLE #TMP; 第一步和第二步也可以用一句select into语句完成。

More Related