如何检索链接到某个主键的外键

Question

我有一个简单的MySQL DB，它有一个由两个varchar列组成的PK表。

CREATE TABLE prodotti(
type_prod varchar(10) not null,
model_prod varchar(10) not null,
brand_prod varchar(20) not null,
name_prod varchar(30) not null,
year_prod int not null, 
description_prod varchar(500) not null,
price_prod float not null,
qnt_prod int not null,

PRIMARY KEY(type_prod,model_prod) );

还有其他有FKs链接到此PK的表(在本例中，我只向您展示了其中的两个表)

CREATE TABLE cpu_component(
id_cpu varchar(10) not null,
series_cpu varchar(20) not null,    
num_cores int not null,
frequency_ghz int not null,
socket_cpu int not null,
label_cpu varchar(10),
model_cpu varchar(10),

PRIMARY KEY (id_cpu),

FOREIGN KEY(label_cpu,model_cpu) REFERENCES prodotti(type_prod,model_prod)
ON UPDATE CASCADE
ON DELETE CASCADE );

CREATE TABLE motherboard_component(
id_motherboard varchar(10) not null,
series_motherboard varchar(20) not null,
chipset_motherboard varchar(15) not null,
socket_motherboard int not null,
type_ram_motherboard varchar(15) not null,
ram_frequency_index int not null,
slot_ram int not null,
hdmi_ports_gpu int not null,
usb_ports_motherboard int not null,
bios_motherboard varchar(10) not null,
label_motherboard varchar(10),
model_motherboard varchar(10),

PRIMARY KEY (id_motherboard),

FOREIGN KEY(label_motherboard,model_motherboard) REFERENCES prodotti(type_prod,model_prod)
ON UPDATE CASCADE
ON DELETE CASCADE );

我想检索与用户输入获得的特定PK链接的所有FKs

这是我的第一个问题，请是克莱门特·洛尔

Answer 1

您可以使用SQL的INNER JOIN语句来实现这一点，在这个语句中，连接表prodotti和cpu_component将为您提供cpu_component中也在prodotti中的所有行。

SELECT p.type_prod, p.model_prod, cpu.num_cores
FROM prodotti p
INNER JOIN cpu_component cpu ON(cpu.label_cpu = p.type_prod AND cpu.model_cpu = model_prod)

关于如何将表prodotti与表motherboard_component连接起来

SELECT p.type_prod, p.model_prod, mc.chipset_motherboard
FROM prodotti p
INNER JOIN motherboard_component mc ON(mc.label_motherboard = p.type_prod AND mc.model_motherboard = p.model_prod)