MySQL链接外键 mysql建立外键关联

文章目录

• 创建外键
• 连接
• 关联查询的误区 :不要认为有了关联查询 就可以不用麻烦的外键约束
• 总结

把分散在多个不同的表里的数据查询出来的操作，就是多表查询。
把俩个表关联起来： 外键（FOREIGN KEY）和连接（JOIN）。外键需要在创建表的阶段就定义；连接可以通过相同意义的字段 把 2 个表连接起来，用在查询阶段。

创建外键

• 外键就是从表中用来引用主表中数据的那个公共字段。
• 在 MySQL 中，外键是通过外键约束来定义的。
• MySQL 系统会根据外键约束的定义，监控对主表中数据的删除操作。如果删除主表中的正在被从表中某条记录的外键字段所引用，MySQL 就会提示错误，从而确保了关联数据不会缺失。
• 外键约束可以在创建表的时候定义，也可以通过修改表来定义
• CONSTRAINT约束

[constraint<外键约束名称>] foreign key 字段名 
references<主表名> 字段名


-- 创建表的时候定义外键约束
create table 从表名
(
字段名 类型,
...
-- 定义外键约束，指出外键字段和参照的主表字段
[constraint<外键约束名称>] 
foreign key 字段名  references<主表名> 字段名
)
--通过修改表来定义外键约束
ALTER TABLE 从表名 ADD CONSTRAINT 约束名 FOREIGN KEY 字段名 REFERENCES 主表名 （字段名) ;

连接

• 在 MySQL 中，有 2 种类型的连接，分别是内连接（INNER JOIN / JOIN）和外连接（OUTER JOIN）。 JOIN 也是内链接
• 内连接表示查询结果只返回符合连接条件的记录，这种连接方式比较常用；
• 外连接则不同，表示查询结果返回某一个表中的所有记录，以及另一个表中满足连接条 件的记录。
• itemnumber 作为外键约束 插入值的时候 需要先插入主表 才能插入副表
• 外连接还可以返回表中的所有记录，它包括两类，分别是左连接和右连接。
• 左连接，一般简写成 LEFT JOIN，返回左边表中的所有记录，以及右表中符合连接条件 的记录。
• 右连接，一般简写成 RIGHT JOIN，返回右边表中的所有记录，以及左表中符合连接条 件的记录。
• 查询左右连接的时候 ， 如果查询第二次就会倒序输出

关联查询的误区 :不要认为有了关联查询 就可以不用麻烦的外键约束

虽然你不用外键约束，也可以进行关联查询，但是有了它，MySQL 系统才会保护你 的数据，避免出现误删的情况，从而提高系统整体的可靠性。

• 外键约束是有成本的，需要消耗系统资源。对于大并发的 SQL 操作，有可能会不适 合。比如大型网站的中央数据库，可能会因为外键约束的系统开销而变得非常慢。所以， MySQL 允许你不使用系统自带的外键约束，在应用层面完成检查数据一致性的逻辑。
• 通过应用层面的附加逻辑，来实现外键约束的功 能，确保数据的一致性。

总结

-- 定义外键约束：
CREATE TABLE 从表名 (
字段 字段类型 .... 
CONSTRAINT 外键约束名称 FOREIGN KEY (字段名) REFERENCES 主表名 (字段名称) 
);
 
ALTER TABLE 从表名 ADD CONSTRAINT 约束名 FOREIGN KEY 字段名 REFERENCES 主表名 （字段名)

-- 连接查询 SELECT 字段名 FROM 表名 AS a JOIN 表名 AS b ON (a.字段名称=b.字段名称);
SELECT 字段名 
FROM 表名 
AS a 
LEFT JOIN 表名 AS b ON (a.字段名称=b.字段名称);

SELECT 字段名 
FROM 表名 
AS a RIGHT 
JOIN 表名 AS b ON (a 字段名称=b 字段名称);



-- 本文代码

-- 2 个数据表，分别是进货单头表（importhead）和进货单 明细表（importdetails）

CREATE TABLE demo.importhead 
( listnumber INT PRIMARY KEY, 
supplierid INT, 
stocknumber INT,
 importtype INT, 
 importquantity DECIMAL(10 , 3 ), 
 importvalue DECIMAL(10 , 2 ), 
 recorder INT, 
 recordingdate DATETIME
);
select * from demo.importdetails;

CREATE TABLE demo.importdetails (
listnumber INT, 
itemnumber INT, 
quantity DECIMAL(10,3),
 importprice DECIMAL(10,2), 
 importvalue DECIMAL(10,2), 
 -- 定义外键约束，指出外键字段和参照的主表字段 
 CONSTRAINT fk_importdetails_importhead FOREIGN KEY (listnumber) REFERENCES importhead (listnumber)
);


-- 关联成功！ 
select 
	constraint_name, -- 表示外键约束名称
	table_name, -- 表示外键约束所属数据表的名称 
	column_name, -- 表示外键约束的字段名称
	referenced_table_name, -- 表示外键约束所参照的数据表名称 
    referenced_column_name -- 表示外键约束所参照的字段名称
from 	
	information_schema.KEY_COLUMN_USAGE
where
	constraint_name = 'fk_importdetails_importhead';


-- 我们通过公共字段“cardno”把两个表关联到了一起，查询出了会员消费的数 据。
select 
	a.transactionno,
	a.itemnumber, 
	a.quantity, 
	a.price,
	a.transdate,
	b.membername
 from
	demo.trans AS a
 JOIN 
	demo.membermaster AS b ON (a.cardno = b.cardno);
 
 
 select 
	a.transactionno,
	a.itemnumber, 
	a.quantity, 
	a.price,
	a.transdate,
	b.membername
 from
	demo.trans AS a
 right join
  -- left JOIN 
	demo.membermaster AS b ON (a.cardno = b.cardno); -- 把顺序颠倒了一下，意思是一样的
    


# 关联查询的误区
insert into demo.importhead
(listnumber,supplierid,stocknumber)
values
(1234,1,1);

insert into demo.importdetails
(listnumber, itemnumber,quantity,importprice,importvalue)
values
(1234,1,1,10,10);

select * from demo.importdetails;
select * from demo.importhead;

DELETE FROM demo.importhead WHERE listnumbere = 1234;