MYSQL多表联合查询、删除、建立连接

1. inner join ：交集 | WHERE

SELECT (字段们) FROM (表们) WHERE [条件](表A.字段A = 表B.字段B)

DELETE (表们) FROM (表们) WHERE [条件](表A.字段A = 表B.字段B)
或 DELETE FROM (表们) USING (表们) WHERE [条件](表A.字段A = 表B.字段B)

2. left join ：以指定的左表列进行交集

SELECT (字段们) FROM (表A) LEFT JOIN (表B) ON [条件](表A.字段A = 表B.字段B)

// 找出2个表中的相同记录，并同时删除2个表的数据
DELETE (表们) FROM (表A) LEFT JOIN (表B) ON [条件](表A.字段A = 表B.字段B) WHERE [条件](表X.字段=值)

right join ：以指定的右表列进行交集 full outer join / union ：并集 cross join ：笛卡尔积 ／ A x B 的映射

附：如何创建关联表 // CONSTRAINT 外键名称(fk_xxx) FOREIGN KEY (关联字段) REFERENCES 被关联表(被关联表的关联字段)

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;

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

参考列表