先来康康要用到的表
S:供应商表
+-----+--------+--------+------+
| SNO | SNAME | STATUS | CITY |
+-----+--------+--------+------+
| S1 | 精益 | 20 | 天津 |
| S2 | 盛锡 | 10 | 北京 |
| S3 | 东方红 | 30 | 北京 |
| S4 | 丰盛泰 | 20 | 天津 |
| S5 | 为民 | 30 | 上海 |
+-----+--------+--------+------+
P：零件表
+-----+--------+-------+--------+
| PNO | PNAME | COLOR | WEIGHT |
+-----+--------+-------+--------+
| P1 | 螺母 | 红 | 12 |
| P2 | 螺栓 | 绿 | 17 |
| P3 | 螺丝刀 | 蓝 | 14 |
| P4 | 螺丝刀 | 红 | 14 |
| P5 | 凸轮 | 蓝 | 40 |
| P6 | 齿轮 | 红 | 30 |
+-----+--------+-------+--------+
J:工程项目表
+-----+----------+------+
| JNO | JNAME | CITY |
+-----+----------+------+
| J1 | 三建 | 北京 |
| J2 | 一汽 | 长春 |
| J3 | 弹簧厂 | 天津 |
| J4 | 造船厂 | 天津 |
| J5 | 机车厂 | 唐山 |
| J6 | 无线电厂 | 常州 |
| J7 | 半导体厂 | 南京 |
+-----+----------+------+
SPJ:供应情况表
+-----+-----+-----+------+
| SNO | PNO | JNO | QTY |
+-----+-----+-----+------+
| S1 | P1 | J1 | 200 |
| S1 | P1 | J3 | 100 |
| S1 | P1 | J4 | 700 |
| S1 | P2 | J2 | 100 |
| S2 | P3 | J1 | 400 |
| S2 | P3 | J2 | 200 |
| S2 | P3 | J4 | 500 |
| S2 | P3 | J5 | 400 |
| S2 | P5 | J1 | 400 |
| S2 | P5 | J2 | 100 |
| S3 | P1 | J1 | 200 |
| S3 | P3 | J1 | 200 |
| S4 | P5 | J1 | 100 |
| S4 | P6 | J3 | 300 |
| S4 | P6 | J4 | 200 |
| S5 | P2 | J4 | 100 |
| S5 | P3 | J1 | 200 |
| S5 | P6 | J2 | 200 |
| S5 | P6 | J4 | 500 |
+-----+-----+-----+------+
开始做题
4.针对建立的4个表用SQL完成2章习题6中的查询
（1）求供应工程J1零件的供应商号码SNO；
ps:这个就要考虑是否去重了，你细品，供应商可能会供应工程J1不同的零件，所以要去重
SELECT DISTINCT SNO
FROM SPJ
WHERE JNO = 'J1';
（2）求供应工程J1零件P1的供应商号码SNO；
mysql> SELECT DISTINCT SNO
-> FROM SPJ
-> WHERE JNO = 'J1' AND PNO = 'P1';
+-----+
| SNO |
+-----+
| S1 |
| S3 |
+-----+
2 rows in set (0.00 sec)
（3）求供应工程J1零件为红色的供应商号码SNO；
知识点：多表查询
mysql> SELECT SNO
-> FROM SPJ,P
-> WHERE JNO='J1' AND
-> SPJ.PNO = P.PNO AND
-> COLOR = '红';
+-----+
| SNO |
+-----+
| S1 |
| S3 |
+-----+
2 rows in set (0.00 sec)
（4）求没有使用天津供应商生产的红色零件的工程号JNO；
知识点：多表查询，NOT IN 语句
mysql> select distinct jno
-> from spj
-> where jno not in
-> (select jno from
-> spj,s,p
-> where s.city = '天津' AND
-> COLOR = '红' AND
-> spj.sno = s.sno
-> AND spj.pno = p.pno);
+-----+
| jno |
+-----+
| J2 |
| J5 |
+-----+
2 rows in set (0.00 sec)
（5）求至少使用了供应商S1提供的全部零件的工程号JNO；（太难了，先放一放）
这个题目的逻辑其实就是——供应商S1生产了几种商品，找到那些工程，这些工程有啥特点？每个工程都使用了S1生产的全部类型的零件，用没用其他的人的？咱们不用管，就比如第一个工程，使用了不仅使用了供应商S1的，还使用了圣诞老头的，没关系，我们只看是否使用了所有的S1生产的类型的零件。
再专业一点的就是，一个符合要求的工程，一定不存在这种情况（STATUS1)——此工程使用的某个或者某些甚至是全部零件，S1提供了这些零件但是此工程没有从S1那里拿货。
我假设符合要求的工程是SPJ_TARGET，不存在的情况怎么表示呢？
这种情况可以专业一点解释：
SELECT DISTINCT JNO
FROM SPJ
WHERE 不存在STATUS1
#STATUS1
#STATUS1
SELECT *
FROM SPJ SPJ_S1
WHERE SNO = 'S1' AND 不存在使用'S1'提供零件的目标项目（STATUS2)
#STATUS2
使用S1提供零件的目标项目
SELECT *
FROM
SPJ SPJ_FINAL
WHERE
SPJ_FINAL.PNO = SPJ_S1.PNO(零件是S1提供的）
AND SPJ_FINAL.JNO = SPJ_TARGET.JNO（这个项目是目标项目）
5.针对习题四中的4个表完成以下各项操作
（1）找出所有供应商的姓名和所在城市
mysql> select sname, city
-> from s;
+--------+------+
| sname | city |
+--------+------+
| 精益 | 天津 |
| 盛锡 | 北京 |
| 东方红 | 北京 |
| 丰盛泰 | 天津 |
| 为民 | 上海 |
+--------+------+
(2) 找出所有零件的名称、颜色、重量
mysql> select pname, color,weight
-> from p;
+--------+-------+--------+
| pname | color | weight |
+--------+-------+--------+
| 螺母 | 红 | 12 |
| 螺栓 | 绿 | 17 |
| 螺丝刀 | 蓝 | 14 |
| 螺丝刀 | 红 | 14 |
| 凸轮 | 蓝 | 40 |
| 齿轮 | 红 | 30 |
+--------+-------+--------+
(3) 找出使用供应商S1所供应零件的工程号码
mysql> select distinct jno
-> from spj
-> where sno = 'S1';
+-----+
| jno |
+-----+
| J1 |
| J3 |
| J4 |
| J2 |
+-----+
4 rows in set (0.00 sec)
(4) 找出项目J2使用的各种零件的名称及其数量
mysql> select pname, qty
-> from spj, p
-> where jno='J2' and spj.pno = p.pno;
+--------+------+
| pname | qty |
+--------+------+
| 螺栓 | 100 |
| 螺丝刀 | 200 |
| 凸轮 | 100 |
| 齿轮 | 200 |
+--------+------+
（5 ）找出上海厂商供应的所有零件号码
mysql> select pno
-> from spj
-> where sno in
-> (select sno from
-> S where city = '上海');
+-----+
| pno |
+-----+
| P2 |
| P3 |
| P6 |
| P6 |
+-----+
（6）找出使用上海产的零件的工程名称
mysql> select jname
-> from j,spj,s
-> where J.jno=spj.jno
-> and spj.sno = s.sno
-> and s.city = '上海';
+--------+
| jname |
+--------+
| 造船厂 |
| 三建 |
| 一汽 |
| 造船厂 |
+--------+
4 rows in set (0.00 sec)
(7) 找出没有使用天津产的零件的工程号码
mysql> select jno
-> from j
-> where not exists
-> (select * from spj
-> where spj.jno = j.jno
-> and sno in(select sno from s where city = '天津'));
+-----+
| jno |
+-----+
| J5 |
| J6 |
| J7 |
+-----+
8、9、10、11这几个题，涉及到增删改，我先不搞实例了，先转录一点答案
#8 把红色零件改成蓝色
update p set color='蓝' where color='红';
#9 由S5供给J4的零件P6改为由S3供应
update spj set sno='S3'
where sno='S5' and pno='P6' and jno='J4';
#10 从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录
*** 先删有外键的SPJ里的记录 ***
delete from spj where sno='S2';
delete from s where sno='S2';
#11将（S2,J6,P4,200)插入供应情况关系
insert spj(SNO, JNO, PNO, QTY)
values(S2,J6,P4,200);
京公网安备 11010502036488号