如何在MySQL中创建关系

Question

在课堂上，我们都在“学习”数据库，而且每个人都在使用Access。我对此感到厌倦，所以我尝试使用MySQL的原始SQL命令来做与班级里其他人一样的事情，而不是使用Access。

我已经成功创建了数据库和表，但是如何在两个表之间建立关系呢？

如果我的两个表像这样：

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 )
)

和

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 )
)

我如何在这两个表间建立“关系”？我想让每个账户被“分配”给一个客户ID（表示谁拥有它）。

- joshhunt

---

我拒绝学习Access，我会学习一个真正的数据库引擎：MySQL。好精神！恭喜 =D - Metafaniel

请注意，外键约束不实现关系，而是实现完整性。账户表中的account_id和customer_id之间的关联实现了各自实体之间的关系。 - reaanb

只要使用InnoDB而不是MyISAM，就很好！此外，PostgreSQL比MySQL有一些值得关注的有趣功能。加油！ - jgmjgm

Answer 1

如果表是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;

你必须明确指定表是InnoDB引擎，因为MyISAM引擎不支持外键。点击 这里 了解更多信息。

- ehogue

---

我在一篇文章中看到，如果InnoDB被定义为默认存储引擎，则无需指定ENGINE=InnoDB子句，可以使用命令（mysql> SELECT @@default_storage_engine;)来检查。 - Arun

Answer 2

正如ehogue所说，将此放入您的CREATE TABLE中

FOREIGN KEY (customer_id) REFERENCES customers(customer_id) 

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

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

学习这些命令的好方法之一是使用MySQL GUI工具，这为您提供了一个更加“可视化”的界面来处理数据库。与Access的方法相比，真正的优势在于，在通过GUI设计表后，它会显示即将运行的SQL语句，因此您可以从中学习。

- nickf

---

您的答案是最佳解决方案。 - Omar

Answer 3

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).

你需要问自己的是这是一对一关系还是一对多关系。也就是说，每个账户是否都有一个客户，每个客户是否都有一个账户。或者会有没有账户的客户存在。你的问题暗示了后者。

如果你想要一个严格的一对一关系，只需合并这两个表格即可。

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,
)

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

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
}

如果你有一个客户ID，并且想要账户信息，那么你需要在“customersaccounts”和“accounts”上进行连接操作：

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

由于建立了索引，这将变得极其迅速。

您也可以创建一个视图，它为您提供了合并的"customersaccounts"表的效果，同时保持它们的独立性。

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;

- user3842431

Answer 4

延续ehogue的评论，你应该使两张表的键大小匹配。与其

customer_id INT( 4 ) NOT NULL ,

做到它

customer_id INT( 10 ) NOT NULL ,

确保你的customers表中的整数列为int（10）。

- Zak

Answer 5

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

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

例如，在查询中，您可以使用联接在选择语句中链接两个表：

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

- Gary Richardson

Answer 6

以下是一些资源，可以帮助您入门：http://www.anchor.com.au/hosting/support/CreatingAQuickMySQLRelationalDatabase 和 http://code.tutsplus.com/articles/sql-for-beginners-part-3-database-relationships--net-8561 此外，像其他人建议的那样，请使用图形用户界面(GUI) - 尝试下载并安装Xampp（或Wamp），这些软件可以在您的计算机上运行服务器软件(Apache和mySQL)。然后，在浏览器中导航到//localhost时，选择PHPMyAdmin以开始使用可视化的mySQL数据库。如上所述，使用innoDB允许您创建所需的关系。这使得查看数据库表格变得更加容易。只需记住在完成后停止Apache和mySQL服务 - 这些服务可能会打开端口，从而让您暴露于黑客/恶意威胁之下。

- user3659515

---

如果您将Apache和MySQL设置为仅提供本地请求，则无需停止它们。此外，如果安装这些服务，用户至少应安装软件防火墙。话虽如此，许多家用路由器都内置了防火墙，因此端口不应该开放，除非有人可以访问路由器并打开它们。 - CodingInTheUK

Answer 7

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

以下是代码，请尝试，虽然第一个回答这个问题的人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; 

- Musa

Answer 8

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)
);

- Anayat

---

请对您提供的模式进行一些解释。 - samabcde