mysql:自然连接

Question

所有人！目前，我正在尝试使用满足以下条件的MySQL创建一个简单的表。

用参加过的瓜达尔卡纳尔战役制作了一张船名、位移和numGuns的表格。

​

​

这需要3张表格。

我试过几个密码，但都失败了。

我也想知道我是否可以使用‘自然连接’冗余。

这是我一直在尝试的代码

#1st trial
select distinct ship as name, displacement, numGuns
from Ships natural join Classes
natural join Outcomes
where battle = 'Guadalcanal';

#2nd trial
select distinct ship as Name
from Outcomes natural join Ships
where battle = 'Guadalcanal'
union
select displacement, numGuns
from Classes
where name = Name and ??

我为您可能需要的情况添加了数据输入查询。

事先非常感谢！

create table Classes(
  Class varchar(20), type char(20), country varchar(20), numGuns int, bore int, displacement int,
  primary key(Class));

create table Ships(
 name varchar(20), Class varchar(20), launched int,
 primary key(name),
 foreign key(Class) references Classes (Class));

create table Outcomes(
 ship varchar(20), battle varchar(20), result varchar(10),
 foreign key(battle) references Battles(Name),
 foreign key(ship) references Ships(name));

insert into Classes values 
('Bismark', 'bb', 'Germany', 8, 15, 42000),
('Iowa', 'bb', 'USA', 9, 16, 46000),
('Kongo', 'bc', 'Japan', 8, 14, 32000),
('North Carolina', 'bb', 'USA', 9, 16, 37000),
('Renown', 'bc', 'Gt. Britain', 6, 15, 32000),
('Revenge', 'bb', 'Gt. Britain', 8,15, 29000),
('Tennessee', 'bb', 'USA', 12, 14, 32000),
('Yamato', 'bb', 'Japan', 9, 18, 65000);

insert into Ships values
('Prince of Wales', 'Tennessee', 1921),
('Bismark', 'Bismark', 1915),
('Duke of York', 'Kongo', 1914),
('Iowa', 'Iowa', 1943),
('Kirishima', 'Kongo', 1915),
('Kongo', 'Kongo', 1913),
('Fuso', 'Iowa', 1943),
('Yamashiro', 'Yamato', 1942),
('California', 'Iowa', 1943),
('North Carolina', 'North Carolina', 1941),
('Renown', 'Renown', 1916),
('Hood', 'Renown', 1916),
('Scharnhorst', 'Revenge', 1916),
('Revenge', 'Revenge', 1916),
('King George V', 'Revenge', 1916),
('South Dakota', 'Revenge', 1916),
('Tennessee', 'Tennessee', 1920),
('Washington', 'North Carolina', 1941),
('West Virginia', 'Iowa', 1943),
('Yamato', 'Yamato', 1941);

insert into Outcomes values 
('Bismark', 'North Atlantic', 'sunk'),
('California', 'Surigao Strait', 'ok'),
('Duke of York', 'North Cape', 'ok'),
('Duke of York', 'Surigao Strait', 'ok'),
('Fuso', 'Surigao Strait', 'sunk'),
('Hood', 'North Atlantic', 'sunk'),
('King George V', 'North Atlantic', 'ok'),
('Kirishima', 'Guadalcanal', 'sunk'),
('Prince of Wales', 'North Atlantic', 'damaged'),
('Prince of Wales', 'North Cape', 'ok'),
('Scharnhorst', 'North Cape', 'sunk'),
('South Dakota', 'Guadalcanal', 'damaged'),
('Tennessee', 'Surigao Strait', 'sunk'),
('Washington', 'Guadalcanal', 'ok'),
('West Virginia', 'Surigao Strait', 'ok'),
('Yamashiro', 'Surigao Strait', 'ok');

Answer 1

您希望显示两个表的数据：ships和classes --它们与1:n相关，所以只需加入它们(inner join on / using)即可。那么你就有条件只考虑在某一次战斗中出动的船只。条件在理想情况下属于WHERE子句。我们使用EXISTS或IN在其他表中查找数据。

select s.name, c.numguns, c.displacement
from ships s
join classes c on c.class = s.class
where s.name in (select ship from outcomes where battle = 'Guadalcanal')
order by s.name;

通过在WHERE子句中添加条件，我们避免了任何重复。嗯，在这种情况下(只有一场战斗)，我们无论如何也不会得到重复的，但是考虑看多场战斗。一次加入会使我们的船多倍，就像一次加入

Answer 2

我想这会帮到你：

SELECT Ships.name, Classes.displacement, Classes.numGuns 
FROM Ships 
INNER JOIN Classes ON Ships.Class = Classes.Class
INNER JOIN Outcomes ON Outcomes.Ship = Ships.Name
WHERE Outcomes.battle = 'Guadalcanal';

如果您需要帮助，请告诉我。