连接(join)、视图(view)与联合(union)

连接和联合都对两个数据集(如两个select查询结果)进行操作,不同的是,联合是两个数据集进行合并,比如,当A数据集有m条记录,B数据集有n条记录,那么,联合后的记录集是m+n条记录;连接一般是对两个记录集进行笛卡尔乘积,理论上讲,假如A表是m条记录,每条记录对应B表n条记录,则连接结果为m×n条记录,但在实际应用中还会有一些特殊情况,下面分别说明。

连接

下面的代码,我们在cdb_demo数据库中创建一个名为user_priv的表,用来模拟保存用户权限。

MySQL
CREATE TABLE if NOT EXISTS cdb_demo.user_priv( 
recid BIGINT AUTO_INCREMENT NOT NULL PRIMARY KEY, 
username VARCHAR(15) REFERENCES cdb_demo.user_main(username), 
priv1 INT DEFAULT 0, 
priv2 INT DEFAULT 0, 
priv3 INT DEFAULT 0 
)ENGINE=INNODB, DEFAULT CHARSET='utf8';
 

代码中,user_priv表中的username字段定义为外键(foreign key),并引用了user_main表中的username字段;这样,就使用username字段将user_main和user_priv表建立了一个关系,从而让user_priv表成为user_main表的扩展表,或者称为子表。

实际应用中,可以通过这种主键(唯一键)与外键的关系创建“一对一”或“一对多”的关系,比如,一个超市小票中的销售单号(主键或唯一键)、柜员、顾客、时间等信息就可以作为销售主表数据,而购买的商品编号、数量、单价、销售单号(外键)等数据作为子表数据,然后,通过“销售单号”关联为一个完整的销售数据。

下面的代码,在user_priv表中添加user01和user03用户的权限数据。

MySQL
INSERT INTO cdb_demo.user_priv(username,priv1,priv2,priv3) 
VALUES('user01',1,1,0), ('user03',1,2,8);
 

语句执行后,user_priv表的数据如下图。

下面的代码,我们进行user_main和user_priv表的连接操作。

MySQL
USE cdb_demo;
SELECT M.*,P.* 
FROM user_main AS M JOIN user_priv AS P 
ON M.username=P.username;

首先注意from关键字后面的内容,user_main表的别名定义为M,user_priv表的别名定义为P;on关键字指定两个表的连接字段,这里使用的是两个表中唯一键字段(user_main表)和外键字段(user_priv表),它们有相同的字段名username。请注意,即使两个表中的字段名不同,也可以进行连接操作。

默认情况下,会使用内连接,即只连接左表(join左边的表)和右表(join右边的表)都存在的记录。代码执行结果如下图。

如果需要包含左表(user_main)中的所有记录,需要在join关键字前使用left关键字,如下面的代码。

MySQL
USE cdb_demo;
SELECT M.*,P.* 
FROM user_main AS M LEFT JOIN user_priv AS P 
ON M.username=P.username;

此时,在user_priv表中不存在的数据会显示为空值(null),代码执行结果如下图。

如果想查询user_priv表中不存在的用户信息,可以将查询条件设置为连接字段为空,如下面的代码。

MySQL
USE cdb_demo;
SELECT M.*,P.* 
FROM user_main AS M LEFT JOIN user_priv AS P 
ON M.username=P.username
WHERE P.username IS NULL;

代码查询结果如下图。

如果只需要查看部分字段,可以分别指定,如下面的代码。

MySQL
USE cdb_demo;
SELECT M.userid,M.username,M.userpwd,M.fullname,M.sex,P.priv1,P.priv2,P.priv3 
FROM user_main AS M JOIN user_priv AS P 
ON M.username=P.username;

查询结果如下图。

视图

前面的连接查询,代码可能有些多,如果需要多次使用这些查询,一方面可以保存SQL语句备用;另一方面,也可以创建视图。

视图就好像查询模板,或者虚拟表,但它不是真的保存数据。下面的代码,我们使用create view语句创建一个名为v_user_priv的视图。

MySQL
USE cdb_demo;
CREATE VIEW v_user_priv 
AS 
SELECT M.userid,M.username,M.userpwd,M.fullname,M.sex,P.priv1,P.priv2,P.priv3 
FROM user_main AS M JOIN user_priv AS P 
ON M.username=P.username;

接下来,可以直接使用v_user_priv作为查询的数据源,如下面的代码。

MySQL
USE cdb_demo;
SELECT * FROM v_user_priv WHERE username='user03';

代码执行结果如下图。

联合

如果某个表的数据量非常大,则可以考虑分表保存,比如,每一年的数据保存在一个表中,如果需要跨年度查询数据,就可以使用数据的联合操作。下面的代码,分别创建两个表t2020和t2021,并添加一些记录,请注意,这两个表的字段结构是相同的。

MySQL
USE cdb_demo;
CREATE TABLE t2020( 
recid BIGINT AUTO_INCREMENT NOT NULL PRIMARY KEY, 
fullname VARCHAR(15), 
sex INT DEFAULT 0 
)ENGINE=INNODB,DEFAULT CHARSET='utf8';
 
CREATE TABLE t2021( 
recid BIGINT AUTO_INCREMENT NOT NULL PRIMARY KEY, 
fullname VARCHAR(15), 
sex INT DEFAULT 0 
)ENGINE=INNODB,DEFAULT CHARSET='utf8';

INSERT INTO t2020(fullname,sex)  
VALUES('user01',1), 
('user02',2);

INSERT INTO t2021(fullname,sex) 
VALUES('张三',1), 
('李四',2);

下面的代码,通过联合操作,显示两个表中的fullname和sex字段数据。

MySQL
USE cdb_demo;
SELECT fullname,sex FROM t2020
UNION 
SELECT fullname,sex FROM t2021;

代码执行结果如下图。

如果两个记录集的字段名不同,会使用第一个记录集的字段名,如下面的代码。

MySQL
USE cdb_demo;
SELECT fullname AS `name` ,sex FROM t2020
UNION 
SELECT fullname,sex FROM t2021;

代码执行结果如下图。

本站内容均为原创作品,转载请注明出处,本页面网址为:http://caohuayu.com/chy/article/Article.aspx?code=cc004005