mysql - 从具有外键的亲戚中选择？

Question

我有这些表:

create table CLIENTE (
idCliente int primary key auto_increment,
name varchar(255)


create table ANIMAL (
idAnimal int primary key,
name varchar(255),
foreign key (idCliente) references CLIENTE (idClinete) on delete set null on update cascade

如何列出(使用 Select * from methood)客户 John 提供的动物名称？它是一种可执行文件，所以我知道如果我运行代码，它实际上不会显示名称，但除此之外，我怎样才能编写代码？

Answer 1

您需要在 ANIMAL 表中有一个 idCliente 列:

create table ANIMAL (
    idAnimal INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    idCliente INT,
    FOREIGN KEY (idCliente) REFERENCES CLIENTE (idCliente) ON DELETE SET NULL ON UPDATE CASCADE
);

然后在JOIN中使用它:

SELECT a.*
FROM ANIMAL AS a
JOIN CLIENTE AS c ON a.idCliente = c.idCliente
WHERE c.name = 'John';