06-外键和连接的关联查询

多表查询

将分散在多个不同的表里的数据查询出来的操作成为多表查询

多表查询查询需要建立起多个表之间的关联，才能去查询，同时还需要规避关联表查询中的常见错误

现项目的进货模块有两个数据表，分别是进货单头表（importhead）和进货单明细表（importdetails）

需要查询一次进货的所有相关数据，就需要把两个表关联起来

将两个表关联，就需要2个重要的功能：外键（foreign key）和连接（join）

• 外键需要在创建表的阶段就定义
• 连接可以通过相同意义的字段把两个表连接起来，用在查询阶段

创建外键

外键是从表中引用主表数据的公共字段

公共字段A是表A的主键，A是主表，B是从表，公共字段A是B表的外键

外键是通过外键约束定义的，必须在表中定义，包括哪个是外键字段，以及外键字段所引用的主表的主键字段是什么。

MySQL会根据外键约束的定义，监控对主表中的数据的删除操作，如果发现要删除的主表记录正在表中被某记录当作外键字段使用，就会提示错误，从而确保关联数据不会缺失。

外键约束可以在创建表的时候定义，也可以通过修改表定义。

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

create table 从表名
(
	字段名 类型,
  ...
  -- 定义外键约束，指出外键字段和参照的主表字段
  constraint 外键约束名
  foreign key (字段名) references 主表名 (字段名)
)

通过修改表来定义外键约束

alter table 从表名 add constraint 约束名 foreign key 字段名 references 主表名(字段名)

创建主表demo.importhead

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

创建从表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';

连接

关联关系建立起来之后，需要使用连接查询才能获得我们需要的数据。

MySQL连接有两种，分为内连接(inner join)和外链接(outer join)

• 内连接表示查询结果值返回符合连接条件的记录，比较常用
• 外链接，表示查询结果返回某一个表中的所有记录，以及另一个表中满足连接条件的记录

内连接

join、inner join、cross join含义是一样的，都表示内连接。

项目中有会员销售需求，会员销售的数据记录包括了会员编号，而非会员的会员编号为null。

销售表demo.trans

会员信息表demo.membermaster

两个表存在关联关系，表demo.trans中的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);

关键字join和关键字on配对使用，意思是查询满足关联条件demo.trans中的cardno值和demo.membermaster中的cardno值相等的两个表中的所有记录

外连接

外链接与内连接值返回符合连接条件的记录不同，外链接可以返回表中的所有数据，分为左连接和右连接

• 左连接，一般写作left join，返回左表中的所有记录，以及右表中符合连接条件的记录
• 右连接，一般写作right join，返回右表中的所有记录，以及左表中符合连接条件的记录

select 
	a.transactionno,
	a.itemnumber,
	a.quantity,
	a.price,
	a.transdate,
	b.membername
from demo.trans as a
left join demo.membermaster as b -- left join,以demo.trans为主
on (a.cardno=b.cardno);

使用right join实现同样的效果

select 
	a.transactionno,
	a.itemnumber,
	a.quantity,
	a.price,
	a.transdate,
	b.membername
from demo.trans as a
right join demo.membermaster as b 
on (a.cardno=b.cardno);

关联查询的误区

有了连接就能实现两个表的关联查询，那关联查询是否必须在外键约束的基础之上？

外键约束不是关联查询的必要条件，但是外键约束却不可获取。

超市进货，供货商编号是 1，进货仓库编号是 1。我们进货的商品编号是1234，进货数量是 1，进货价格是 10，进货金额是 10。

插入单头数据：

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

此时如果删除和进货单头表，就会出现明细没有单头的数据缺失情况。

delete from demo.importhead
where listnumber = 1234;

提示错误，因为违反了外键约束。

为什么先插入进货单头表，再插入进货单明细？

因为如果先插入到从表，就会导致无法找到主表参照信息，从而提示错误，因为添加数据违反了外键约束。

删除进货单时，也因该先删除明细，再删除单头

为什么没有外键约束也能进行关联查询？

外键约束是有成本的，需要消耗系统资源。对于大并发的SQL操作，可能会不适合。

大型的中央数据库，就会因为外键约束的系统开销而变得特变慢。

所以MySQL允许不适用系统自带的外键约束，在应用层面完成检查数据一致性的逻辑。也就是说，即使不用外键约束，也要想办法通过应用层面的附加逻辑，来实现外键约束的功能，确保数据的一致性。