系统：

MySQL 8.0.19

题目：

三张表：
S(SNO, SNAME, STATUS, CITY)
P(PNO, PNAME, COLOR, WEIGHT, CITY)
J(JNO, JNAME,CITY)
SPJ(SNO, PNO, JNO, PRICE, QTY)

S表示供应商，各属性依次为供应商号，供应商名，供应商状态值，供应商所在城市；

P表示零件，各属性依次为零件号，零件名，零件颜色，零件重量，零件存放的城市；

J表示工程，各属性依次为工程号，工程名，工程所在城市；

SPJ表示供货关系，各属性依次为供应商号，零件号，工程号，单价, QTY。

基于以上SPJ关系模式用SQL完成下面的查询：

问题1：

求没有供应零件号为P1和P2两种零件的供应商姓名。

1 select SNAME 
2 from S
3 where SNO not in 
4 (
5     select SNO
6     from SPJ
7     where PNO = 'P1' or PNO = 'P2'
8 );

View Code

注意：不是“即没有P1也没有P2零件”，而是“没有P1或没有P2零件”。

问题2：

列出所有供应商的信息，包括供应商姓名、所供应的零件名（没有供应零件的供应商也要列出，最后结果中不要出现重复元组。

1 select distinct S.SNAME, P.PNAME
2 from S left join (SPJ inner join P on SPJ.PNO = P.PNO) on S.SNO = SPJ.SNO

View Code

注意：去重用distinct关键字，外连接用left/right join。

问题3：

求只向与自己位于不同城市的工程供应零件的供应商姓名。

 1 select SNAME
 2 from S
 3 where SNO not in
 4     (
 5     select S.SNO
 6     from S, SPJ, J
 7     where S.SNO = SPJ.SNO 
 8         and SPJ.JNO = J.JNO 
 9         and S.CITY = J.CITY
10     )
11     and SNO in
12     (
13     select S.SNO
14     from S, SPJ
15     where S.SNO = SPJ.SNO
16     );

方法一

 1 select S.SNAME
 2 from S, SPJ
 3 where S.SNO = SPJ.SNO
 4     and S.SNO not in
 5         (
 6         select S.SNO
 7         from S, SPJ, J
 8         where S.SNO = SPJ.SNO 
 9             and SPJ.JNO = J.JNO 
10             and S.CITY = J.CITY
11         );

方法二

注意：(1) 不要输出不向任何工程供应零件的供应商，(2) MySQL不支持集合交intersect和集合差except，只支持集合交union(all)。

问题4：

求只向与自己位于相同城市的工程供应零件的供应商姓名。

 1 select SNAME
 2 from S
 3 where SNO in
 4     (
 5         select S.SNO
 6         from S, SPJ, J
 7         where S.SNO = SPJ.SNO and SPJ.JNO = J.JNO and S.CITY = J.CITY
 8     )
 9     and SNO not in
10     (
11         select S.SNO
12         from S, SPJ, J
13         where S.SNO = SPJ.SNO and SPJ.JNO = J.JNO and S.CITY <> J.CITY
14     );

方法一

1 select S.SNAME
2 from S, SPJ
3 where S.SNO = SPJ.SNO
4     and S.SNO not in
5     (
6         select S.SNO
7         from S, SPJ, J
8         where S.SNO = SPJ.SNO and SPJ.JNO = J.JNO and S.CITY <> J.CITY
9     );

方法二

问题5：

求供应了所有零件的供应商姓名。

 1 select SNAME
 2 from S
 3 where not exists
 4 (  
 5     select *
 6     from P
 7     where not exists
 8     (
 9         select *
10         from SPJ
11         where SPJ.SNO = S.SNO and SPJ.PNO = P.PNO
12     )
13 );

not exists方法

1 select SNAME
2 from S
3 where SNO in
4 (
5     select SNO
6     from SPJ
7     group by SNO
8     having count(JNO) = (select count(*) from J)
9 );

group by + count()方法

要点：(1) not exists ... not exists ... 表示双重否定，可用于实现关系代数的除法，(2) count方法在J表有重复行(即JNO没有主码约束)、或在SPJ表的JNO属性有空值(即没有JNO的外码约束)的情况下不一定能给出正确答案，不过本例比较简单、没有这种风险。

问题6：

求供应了所有红色零件的供应商姓名。

 1 select SNAME
 2 from S
 3 where not exists
 4 (
 5     select *
 6     from (select * from P where P.COLOR = '红色') as P1
 7     where not exists
 8     (
 9         select *
10         from SPJ
11         where S.SNO = SPJ.SNO and P1.PNO = SPJ.PNO
12     )
13 );

P表替换为红色的P表

 1 select SNAME
 2 from S
 3 where not exists
 4 (
 5     select *
 6     from P
 7     where P.COLOR = '红色'
 8     and not exists
 9     (
10         select *
11         from SPJ
12         where S.SNO = SPJ.SNO and P.PNO = SPJ.PNO
13     )
14 );

直接加上‘红色’约束

问题7：

列出每个城市的工程所使用的零件总的数量。

1 select CITY, sum(QTY)
2 from J join SPJ using(JNO)
3 group by CITY;

View Code

问题8：