如何在 MySQL 中创建关系

Question

提问by Josh Hunt

In class, we are all 'studying' databases, and everyone is using Access. Bored with this, I am trying to do what the rest of the class is doing, but with raw SQL commands with MySQL instead of using Access.

在课堂上，我们都在“学习”数据库，每个人都在使用 Access。对此感到厌烦，我正在尝试做其他课程正在做的事情，但是使用 MySQL 的原始 SQL 命令而不是使用 Access。

I have managed to create databases and tables, but now how do I make a relationship between two tables?

我已经设法创建了数据库和表，但现在如何在两个表之间建立关系？

If I have my two tables like this:

如果我有两个这样的表：

CREATE TABLE accounts(
    account_id INT NOT NULL AUTO_INCREMENT,
    customer_id INT( 4 ) NOT NULL ,
    account_type ENUM( 'savings', 'credit' ) NOT NULL,
    balance FLOAT( 9 ) NOT NULL,
    PRIMARY KEY ( account_id )
)

and

和

CREATE TABLE customers(
    customer_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    address VARCHAR(20) NOT NULL,
    city VARCHAR(20) NOT NULL,
    state VARCHAR(20) NOT NULL,
    PRIMARY KEY ( customer_id )
)

How do I create a 'relationship' between the two tables? I want each account to be 'assigned' one customer_id (to indicate who owns it).

如何在两个表之间创建“关系”？我希望每个帐户都被“分配”一个 customer_id（以表明谁拥有它）。

Answer 1

回答by Eric Hogue

If the tables are innodb you can create it like this:

如果表是 innodb，您可以像这样创建它：

CREATE TABLE accounts(
    account_id INT NOT NULL AUTO_INCREMENT,
    customer_id INT( 4 ) NOT NULL ,
    account_type ENUM( 'savings', 'credit' ) NOT NULL,
    balance FLOAT( 9 ) NOT NULL,
    PRIMARY KEY ( account_id ), 
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) 
) ENGINE=INNODB;

You have to specify that the tables are innodb because myisam engine doesn't support foreign key. Look herefor more info.

您必须指定表为 innodb，因为 myisam 引擎不支持外键。看看这里获取更多信息。

Answer 2

回答by nickf

as ehogue said, put this in your CREATE TABLE

正如 ehogue 所说，把它放在你的 CREATE TABLE 中

FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

alternatively, if you already have the table created, use an ALTER TABLE command:

或者，如果您已经创建了表，请使用 ALTER TABLE 命令：

ALTER TABLE `accounts`
  ADD CONSTRAINT `FK_myKey` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`) ON DELETE CASCADE ON UPDATE CASCADE;

One good way to start learning these commands is using the MySQL GUI Tools, which give you a more "visual" interface for working with your database. The real benefit to that (over Access's method), is that after designing your table via the GUI, it shows you the SQL it's going to run, and hence you can learn from that.

开始学习这些命令的一个好方法是使用MySQL GUI 工具，它为您提供了一个更“可视化”的界面来处理您的数据库。这样做的真正好处（相对于 Access 的方法）是，在通过 GUI 设计您的表之后，它会向您显示将要运行的 SQL，因此您可以从中学习。

Answer 3

回答by user3842431

CREATE TABLE accounts(
    account_id INT NOT NULL AUTO_INCREMENT,
    customer_id INT( 4 ) NOT NULL ,
    account_type ENUM( 'savings', 'credit' ) NOT NULL,
    balance FLOAT( 9 ) NOT NULL,
    PRIMARY KEY ( account_id )
)

and

CREATE TABLE customers(
    customer_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    address VARCHAR(20) NOT NULL,
    city VARCHAR(20) NOT NULL,
    state VARCHAR(20) NOT NULL,
)

How do I create a 'relationship' between the two tables? I want each account to be 'assigned' one customer_id (to indicate who owns it).

You have to ask yourself is this a 1 to 1 relationship or a 1 out of many relationship. That is, does every account have a customer and every customer have an account. Or will there be customers without accounts. Your question implies the latter.

你必须问自己这是一对一的关系还是多选的关系。也就是说，是否每个帐户都有一个客户，每个客户都有一个帐户。或者会有没有账户的客户。你的问题暗示后者。

If you want to have a strict 1 to 1 relationship, just merge the two tables.

如果您想拥有严格的 1 对 1 关系，只需合并两个表即可。

CREATE TABLE customers(
    customer_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    address VARCHAR(20) NOT NULL,
    city VARCHAR(20) NOT NULL,
    state VARCHAR(20) NOT NULL,
    account_type ENUM( 'savings', 'credit' ) NOT NULL,
    balance FLOAT( 9 ) NOT NULL,
)

In the other case, the correct way to create a relationship between two tables is to create a relationship table.

在另一种情况下，在两个表之间创建关系的正确方法是创建关系表。

CREATE TABLE customersaccounts(
    customer_id INT NOT NULL,
    account_id INT NOT NULL,
    PRIMARY KEY (customer_id, account_id)
    FOREIGN KEY customer_id references customers (customer_id) on delete cascade,
    FOREIGN KEY account_id  references accounts  (account_id) on delete cascade
}

Then if you have a customer_id and want the account info, you join on customersaccounts and accounts:

然后，如果您有 customer_id 并想要帐户信息，您可以加入 customeraccounts 和帐户：

SELECT a.*
    FROM customersaccounts ca
        INNER JOIN accounts a ca.account_id=a.account_id
            AND ca.customer_id=mycustomerid;

Because of indexing this will be blindingly quick.

由于索引，这将非常快。

You could also create a VIEW which gives you the effect of the combined customersaccounts table while keeping them separate

您还可以创建一个 VIEW，它可以为您提供合并 customeraccounts 表的效果，同时将它们分开

CREATE VIEW customeraccounts AS 
    SELECT a.*, c.* FROM customersaccounts ca
        INNER JOIN accounts a ON ca.account_id=a.account_id
        INNER JOIN customers c ON ca.customer_id=c.customer_id;

Answer 4

回答by Zak

Adding onto the comment by ehogue, you should make the size of the keys on both tables match. Rather than

通过 ehogue 添加评论，您应该使两个表上的键的大小匹配。而不是

customer_id INT( 4 ) NOT NULL ,

make it

做了

customer_id INT( 10 ) NOT NULL ,

and make sure your int column in the customers table is int(10) also.

并确保客户表中的 int 列也是 int(10) 。

Answer 5

回答by Gary Richardson

Certain MySQL engines support foreign keys. For example, InnoDB can establish constraints based on foreign keys. If you try to delete an entry in one table that has dependents in another, the delete will fail.

某些 MySQL 引擎支持外键。例如，InnoDB 可以建立基于外键的约束。如果您尝试删除一个表中的条目，而该条目在另一个表中有依赖项，则删除将失败。

If you are using a table type in MySQL, such as MyISAM, that doesn't support foreign keys, you don't link the tables anywhere except your diagrams and queries.

如果您在 MySQL 中使用不支持外键的表类型（例如 MyISAM），则除了图表和查询之外，您不要在任何地方链接表。

For example, in a query you link two tables in a select statement with a join:

例如，在查询中，您使用连接将 select 语句中的两个表链接起来：

SELECT a, b from table1 LEFT JOIN table2 USING (common_field);

Answer 6

回答by user3659515

Here are a couple of resources that will help get started: http://www.anchor.com.au/hosting/support/CreatingAQuickMySQLRelationalDatabaseand http://code.tutsplus.com/articles/sql-for-beginners-part-3-database-relationships--net-8561

以下是一些有助于入门的资源：http: //www.anchor.com.au/hosting/support/CreatingAQuickMySQLRelationalDatabase和http://code.tutsplus.com/articles/sql-for-beginners-part- 3-数据库-关系--net-8561

Also as others said, use a GUI - try downloading and installing Xampp (or Wamp) which run server-software (Apache and mySQL) on your computer. Then when you navigate to //localhost in a browser, select PHPMyAdmin to start working with a mySQL database visually. As mentioned above, used innoDB to allow you to make relationships as you requested. Makes it heaps easier to see what you're doing with the database tables. Just remember to STOP Apache and mySQL services when finished - these can open up ports which can expose you to hacking/malicious threats.

也正如其他人所说，使用 GUI - 尝试下载并安装 Xampp（或 Wamp），它在您的计算机上运行服务器软件（Apache 和 mySQL）。然后，当您在浏览器中导航到 //localhost 时，选择 PHPMyAdmin 以开始可视化地使用 mySQL 数据库。如上所述，使用 innoDB 允许您根据要求建立关系。更容易查看您对数据库表的操作。只需记住在完成后停止 Apache 和 mySQL 服务 - 这些会打开端口，使您面临黑客攻击/恶意威胁。

Answer 7

回答by Musa

One of the rules you have to know is that the table column you want to reference to has to be with the same data type as The referencing table . 2 if you decide to use mysql you have to use InnoDB Engine because according to your question that's the engine which supports what you want to achieve in mysql .

您必须知道的规则之一是您要引用的表列必须与引用表具有相同的数据类型。2 如果你决定使用 mysql 你必须使用 InnoDB Engine 因为根据你的问题，这是支持你想要在 mysql 中实现的引擎。

Bellow is the code try it though the first people to answer this question they 100% provided great answers and please consider them all .

波纹管是代码尝试它虽然第一个回答这个问题的人他们 100% 提供了很好的答案，请考虑他们。

CREATE TABLE accounts(
    account_id INT NOT NULL AUTO_INCREMENT,
    customer_id INT( 4 ) NOT NULL ,
    account_type ENUM( 'savings', 'credit' ) NOT NULL,
    balance FLOAT( 9 ) NOT NULL,
    PRIMARY KEY (account_id)
)ENGINE=InnoDB;

CREATE TABLE customers(
    customer_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    address VARCHAR(20) NOT NULL,
    city VARCHAR(20) NOT NULL,
    state VARCHAR(20) NOT NULL,
     PRIMARY KEY ( account_id ), 
FOREIGN KEY (customer_id) REFERENCES customers(customer_id) 
)ENGINE=InnoDB;

Answer 8

回答by Anayat

create table departement(
    dep_id      int primary key auto_increment,
    dep_name    varchar(100) not null,
    dep_descriptin      text,
    dep_photo       varchar(100) not null,
    dep_video       varchar(300) not null
);

create table newsfeeds(
    news_id         int primary key auto_increment,
    news_title      varchar(200) not null,
    news_description    text,
    news_photo          varchar(300) ,
    news_date           varchar(30) not null,
    news_video          varchar(300),
    news_comment        varchar(200),
    news_departement    int foreign key(dep_id) references departement(dep_id)
);

如何在 MySQL 中创建关系

提问by Josh Hunt

回答by Eric Hogue

回答by nickf

回答by user3842431

回答by Zak

回答by Gary Richardson

回答by user3659515

回答by Musa

回答by Anayat

相关推荐

最近更新

标签

如何在 MySQL 中创建关系

提问by Josh Hunt

回答by Eric Hogue

回答by nickf

回答by user3842431

回答by Zak

回答by Gary Richardson

回答by user3659515

回答by Musa

回答by Anayat

相关推荐

MySQL WHERE LIKE AND OR

MySQL JOIN 的评估顺序是什么？

MySQL 中的 NULL（性能和存储）

MySQL Mysql从两个表中选择

相关推荐

最近更新

标签