06-外键和连接:如何做关联查询？

如何创健外建
连接
- 外连接
关联查询的误区
总结
思考题

把分散在多个不同的表的数据查询出来的操作,就是多表查询。

如何创健外建

外建就是从表中用来引用主表中数据的那个公共字段。

在关联关系R中,公众字段(字段A)是表A的主键,所以表A是主表,表B是从表。表B中的公共字段(字段A)是外键。

在mysql中,外键是通过外键约束来定义的。外键约束的一种,它必须在从表中定义,包括指明哪个是外键字段,以及外键字段所引用的主表中的主键字段是什么。mysql系统会根据外键约束的定义,监控对主表中数据的删除操作。如果发现要删除的主表记录,正在被从表某条记录的外键字段所引用,mysql就会提示错误,从而确保关系数据不会缺失。

外键约束可以在创建表的时候定义,也可以通过修改表来定义。我们先来看看外键约束定义的语法结构


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

连接

在mysql中有两种分别是内连接 (inner join) 和外连接 (outer join)

内连接表示查询结果只返回符合连接条件的记录,这种连接方式比较常用；
外连接则不同,表示查询结果返回某一个表中的所有记录,以及另外一个表中满足连接条件的记录。

在mysql里面,关键字 join inner join cross join的含义是一样的,都表示内连接。

create table demo.membermaster
( cardno int not null primary key,
  membermame text not null,
  memberphone int not null,
  memberpid int not null,
  sex text not null
);
create table demo.trans
(
 transactionnumber int primary key,
 itemnumber int,
 price int,
 quantity int ,
 cardno int,
 transdate datetime,
 constraint fk_trans_membermaster foreign key (cardno) references membermaster (cardno)
);

select a.transactionnumber,a.itemnumber,a.quantity,a.price,a.transdate,b.membermame from demo.trans AS a JOIN demo.membermaster AS b ON (a.cardno = b.cardno);

关键字JOIN 与关键字 ON配对使用,意思是查询满足关联条件 demo.trans 表中 cardno的值与 demo.membermaster表中值相等的两个表的所有记录。

外连接

左连接,一般简写成 left join 返回左边表中的所有记录,以及右边表中符合连接条件的记录。
右连接,一般简写成 right join 返回右边表中所有记录,以及左边表中符合连接条件的记录。
关联查询的误区
其实在 mysql中外键约束不是关联查询的必要条件。很多人往往在设计表的时候,觉得只要连接查询就可以搞定一切了。外键约束太麻烦,没必要。如果你这样想,就进入一个误区。
虽然不用外键约束,可以进行关联查询,但是有了它,mysql系统才会保护你的数据,避免出现误删的情况,从而提高系统整体的可靠性。
总结
外键约束,可以帮助我们确定从表中的外键字段与主表中的主键字段之间的引用关系,还确保从表中的数据所引用的主表数据不会被删除,从而保证了2个表的数据的一致性。 ```

— 定义外键约束： 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.字段名称); ``` 我建议你尽量养成在关联表中定义外键约束的习惯。不过，如果你的业务场景因为高并发等原因，无法承担外键约束的成本，也可以不定义外键约束，但是一定要在应用层面实现外键约束的逻辑功能，这样才能确保系统的正确可靠。

思考题

如果你的业务场景因高并发等原因，不能使用外键约束，在这种情况下，你怎么在应用层面确保数据的一致性呢？
如果不能使用外键约束，你可以在应用层确保数据完整性的功能模块,比如删除主表记录时,增加检查从表中是否应用了这条记录的功能,如果应用了,就不允许删除。

如何创健外建

连接

外连接

关联查询的误区

总结

思考题