使用多个数据库表 [英] working with more than one DB tables

问题描述

我正在处理一些事情，我想出了一个问题..



我创建了一个数据库，其中包含一个表，在此表中我有主键（自动生成的ID），姓名，姓氏，电子邮件等...

以上描述的是客户。但是说如果我想要更多的字段来描述一个顾客（不像重量，身高，形状等那么重要......）但我不想把它们放在上表中，我该怎么办？



我可以创建第二张桌子吗？但是当我从第一个表中用ID修改一个客户时如何从没有ID的第二个表中访问该客户的正确数据？



希望您理解。 。

谢谢。

Hi,

I am working with something and I came up with a question..

I created a database with one table in it, in this table I have the primary key(autogenerated ID), name, surname,email etc...
The above are describing a customer. But say if I want more fields to describe one customer(not so important like weight, height, shape etc..) but I didn't want to put them in the above table, what can I do?

Can I create a second table? but when I modify one customer from the first table with the ID how can I access the correct data for this customer from the second table without ID?

hope you understand..
thanks.

推荐答案

是的，你可以 - 可能没有必要创建一个单独的表（因为你可以指定哪个选择它们时要检索的字段）：

Yes, you can - it probably isn't necessary to create a separate table (since you can specify which fields you want to retrieve when you SELECT them):

SELECT name, surname FROM MyTable WHERE ID = 22

但如果你这样做，那么你设置一个新表（带有它自己的ID列）和所谓的外键列，它包含原始表中的ID值：

but if you do, then you set up a new table (with it's own ID column) and what is called a Foreign Key column which contains the ID value from the original table:

ID      int, identity
CustID  int, foreign key
Weight  float
Height  float

然后你可以像这样一起检索它们：

You can then retrieve them both together like this:

SELECT a.name, a.surname, b.Weight, b.Height FROM MyTable a
JOIN MyOtherTable b ON a.ID=b.CustId
WHERE a.ID = 22

您好，


您需要创建另一个表并将customerid设置为该表中的外键。 MySQL片段下面应该可以帮到你。

Hello,

What you need is to create the another table and set the customerid as the foreign key in that table. Below MySQL snippet should help you.

CREATE TABLE customer_mst (
    customerid MEDIUMINT NOT NULL AUTO_INCREMENT,
    firstname  VARCHAR(64),
    lastname   VARCHAR(64),
    emailed    VARCHAR(255),
    ...,
    PRIMARY KEY (customerid)
);

CREATE TABLE customer_info (
    infoid     MEDIUMINT NOT NULL AUTO_INCREMENT,
    customerid MEDIUMINT NOT NULL,
    weight     MEDIUMINT DEFAULT 0,  
    height     MEDIUMINT DEFAULT 0,
    ...,
    PRIMARY KEY (infoid),
    FOREIGN KEY (customerid) REFERENCES customer_mst(customerid)
)

问候，

Regards,

这篇关于使用多个数据库表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！