如何在MySQL中创建关系

在课堂上,我们都在"学习"数据库,每个人都在使用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 )
)

如何在两个表之间创建"关系"？我希望每个帐户都被"分配"一个customer_id(以表明谁拥有它).

如果表是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所说,把它放在你的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,因此您可以从中学习.

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

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

如果您想要一个严格的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,
)

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

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
}

然后,如果您有customer_id并且想要帐户信息,那么您可以加入customersaccounts和帐户:

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

由于索引,这将是非常快速的.

您还可以创建一个VIEW,它可以为您提供组合的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;

通过ehogue添加注释,您应该使两个表上的键的大小匹配.而不是

customer_id INT( 4 ) NOT NULL ,

做了

customer_id INT( 10 ) NOT NULL ,

并确保customers表中的int列也是int(10).

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

如果您在MySQL中使用的表类型(例如MyISAM)不支持外键,则除了图表和查询之外,您不会将表链接到任何位置.

例如,在查询中,您使用连接链接select语句中的两个表:

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