关联表、连接和视图

本文讨论MySQL数据库中关联表、连接（join）和视图（view）的具体应用。

关联表

关系型数据库中，在表中通常使用外键（foreign key）字段引用另一个表的某个字段数据，从而完成表的关联。下面的语句用于创建t3、t3a表和测试数据，可以复制到HeidiSQL中执行。

MySQL

use cdb_demo;

create table t3(
recid bigint not null auto_increment primary key,
f1 varchar(30) not null unique,
f2 varchar(30) not null,
f3 int not null default 0,
f4 date
)engine=innodb default charset=utf8mb4;

insert into t3(f1,f2,f3,f4)
values('E0001','王一军',1,'2005-10-9'),
('E0002','张三',2,'2005-10-9'),
('E0003','李四',3,'2005-10-15'),
('E0004','刘彬',1,'2006-3-6');

create table t3a(
recid bigint not null auto_increment primary key,
f1 varchar(30) references t3(f1),
f2 int,
f3 int,
f101 decimal(15,2),
f102 decimal(15,2)
)engine=innodb default charset=utf8mb4;

insert into t3a(f1,f2,f3,f101,f102)
values('E0001',2025,1,8166,0),
('E0001',2025,2,8166,0),
('E0001',2025,3,8166,0),
('E0002',2025,1,5158,100),
('E0002',2025,2,5516,0);

代码中，t3表保存简化的人员信息，其中，f1为员工代码，f2为姓名，f3为性别，f4为加入公司日期。t3a表保存简化的工资信息，其中，f1为员工代码，这里使用references关键字定义为外键（foreign key），并引用t3表的f1字段数据，也就是说t3a表的f1字段数据应该是t3表f1字段中已经存在的数据；f2为工资发放年份，f3为工资发放月份，f101为工资进项，f102为工资扣除项。

表中的外键也可以在字段定义后创建，如下面的代码创建的t3a表与上述代码创建的t3a表相同。

MySQL

create table t3a(
recid bigint not null auto_increment primary key,
f1 varchar(30),
f2 int,
f3 int,
f101 decimal(15,2),
f102 decimal(15,2),
foreign key(f1) references t3(f1)
)engine=innodb default charset=utf8mb4;

单独创建外键时，需要使用如下语法：

MySQL

foreign key(<外键字段>) references <引用表>(<引用字段>)

一般来讲，外键字段引用的另一表中的字段常常定义为主键（primary key）或唯一（unique），此时，引用表的字段数据和外键数据则可以形成一对一（1*1）或一对多（1*n）的对应关系，从而完成两个表的数据关联。

示例中，t3a表中的f1字段需要引用t3表中的f1字段数据，一般称t3为主表，t3a为子表。操作表的数据时（添加、更新、删除）应注意，一般情况下，添加数据时应首先添加主表记录，然后再添加子表数据；而删除数据时，则应先删除子表数据，然后再删除主表数据。

在MySQL中可以通过设置环境参数foreign_key_checks改变这一规则；首先，可以通过如下代码查看此参数的值。

MySQL

show variables like 'foreign_key_checks';

如果显示ON表示打开，显示OFF则表示关闭。修改foreign_key_checks参数值时，1表示打开，0表示关闭，工作中可以参考如下代码。

MySQL

set foreign_key_checks=0;
-- 操作主、子表数据
set foreign_key_checks=1;

实际工作中，不建议取消外键检查，如果强制删除关联数据，会破坏数据的完整性和逻辑性，这并不是数据处理工作的好习惯。

连接（jion）

连接可以看作数据的横向扩展，结构上可以是一对一、一对多。如员工信息在一个表中，而每月的工资数据在另一个表中，此时就可以通过连接获取员工信息和某个月的工资数据；再比如，在超市的购物单，单号等信息在一个表中，而某单购买的商品信息保存在另一个表中，通过连接就可以获取完整的购物信息。

连接操作有几个关键点，首先，一次连接需要两个数据源，可称为左表和右表，一般左表为主表（如t3表），右表为子表（如t3a表）；连接操作时需要一个关联字段，即左表和右表中都包含的字段，关联字段在左表和右表中的名称可能相同，也可能不同。

下面看一个简单的示例，通过连接查询t3和t3a表关联的数据。

MySQL

use cdb_demo;

select * from 
t3 join t3a on t3.f1=t3a.f1;

查询结果如下图所示。

查询结果

先来看连接操作的语法：

MySQL

<左表> join <右表> on <关联字段>

其中，join关键字指定数源源的左表和右表，一般来讲，会将主表作为<左表>，如示例中的t3表，将数据子表作为<右表>，如示例中的t3a表；on关键字后指定两个表的关联字段，如示例中的t3.f1=t3a.f1。

再看连接查询结果，其中显示了t3表和t3a表中的所有字段，请注意，两个表的同名字段含义未必相同，如f2和f3字段，在t3表中为姓名和性别，在t3a表中为工资年份和月份；此时，阅读数据就会产生混乱。实际工作中，可以对表和字段使用别名，如下面的代码。

MySQL

use cdb_demo;

select E.recid as e_id,E.f1 as e_num,E.f2 as fullname,E.f3 as sex,
S.recid as s_id,S.f2 as s_year,S.f3 as s_month,S.f101 as s_in,S.f102 as s_de
from 
t3 as E join t3a as S on E.f1=S.f1;

代码查询结果如下图所示。

查询结果

本例，将左表t3命名为E（员工），将右表t3a命名为S（工资），然后，在on子句中指定关联字段为两个表的f1字段。select关键字后指定显示的字段，使用左表和右表的别名分别选择两个表中需要显示的字段，并定义了别名。如果需要更直观，也可以将字段别名定义为中文，如下面的代码。

MySQL

use cdb_demo;

select E.recid as 员工ID,E.f1 as 员工编号,E.f2 as 姓名,E.f3 as 性别,
S.recid as 工资ID,S.f2 as 工资年份,S.f3 as 工资月份,S.f101 as 进项,S.f102 as 扣除项,
(S.f101-S.f102) as 实发工资
from 
t3 as E join t3a as S on E.f1=S.f1;

请注意，代码中还添加了一个计算列，即“(S.f101-S.f102) as 实发工资”，代码查询结果如下图所示。

查询结果

也许大家也发现了，t3a表中只有员工编号为E0001和E0002的工资信息，连接查询中，没有工资数据的员工信息没有显示出来，这是连接操作的默认规则，即只显示左表和右表有关联的数据。如果需要显示左表，即主表中的所有记录，可以在join关键字前添加left，即执行左连接，如下面的代码。

MySQL

use cdb_demo;

select E.f1 as 员工编号,E.f2 as 姓名,
S.f2 as 工资年份,S.f3 as 工资月份,S.f101 as 进项,S.f102 as 扣除项,
(S.f101-S.f102) as 实发工资
from 
t3 as E left join t3a as S on E.f1=S.f1;

代码查询结果如下图所示。

查询结果

本例，对于没有工资记录的员工，其工资数据显示为空值（NULL）。

视图（view）

视图可以看作是查询模板，对于包含复杂结构的查询语句，可以将其定义为视图，使用时只需通过视图名称调用即可。创建视图的语法比较简单，如下面的代码。

MySQL

create view <视图名>
as
<查询语句>

下面的代码可以将员工和工资信息的连接查询定义为视图。

MySQL

use cdb_demo;

create view v_es
as
select E.f1 as 员工编号,E.f2 as 姓名,
S.f2 as 工资年份,S.f3 as 工资月份,S.f101 as 进项,S.f102 as 扣除项,
(S.f101-S.f102) as 实发工资
from 
t3 as E left join t3a as S on E.f1=S.f1;

本例会创建v_es视图。

下面的代码，将v_es作为数据源进行查询。

MySQL

use cdb_demo;

select * from v_es;

查询结果如下图所示。

视图查询结果

下面的代码，在t3a表中添加一些2026年的工资数据。

MySQL

use cdb_demo;

insert into t3a(f1,f2,f3,f101,f102)
values('E0001',2026,1,8366,0),
('E0001',2026,2,8900,0),
('E0002',2026,3,5558,200),
('E0002',2026,1,5558,0),
('E0002',2026,2,5660,0);

再次通过v_es视图查询结果如下图所示。

视图查询结果

和数据表一样，查询视图同样可以使用条件、分组、统计等功能，如下面的代码，可以分别计算员工的各年度的实发工资总额。

MySQL

use cdb_demo;

select 姓名,工资年份,sum(实发工资) as 实发工资总额 from v_es
group by 姓名,工资年份
having 工资年份 is not null;

代码查询结果如下图所示。

视图查询结果

请注意，如果有姓名相同的员工，应使用员工编号进行分组，然后通过t3表连接显示姓名，可以参考如下代码。

MySQL

use cdb_demo;

select E.f2 as 姓名,X.* from 
t3 as E
join
(select 员工编号,工资年份,sum(实发工资) as 实发工资总额 from v_es
group by 员工编号,工资年份
having 工资年份 is not null) as X
on E.f1=X.员工编号;

本例将v_es视图的统计结果作为右表，别名为X；然后通过员工编号与t3表的f1字段进行连接，并显示员工编号对应的姓名（f2字段）。代码执行结果如下图所示。

连接查询结果

请注意，视图只是命名的查询模板，所以，表中的数据变化也会体现到视图查询结果中；如果需要将视图的查询结果持久化，可以将数据保存到表中，下一篇文章会介绍相关操作。