MySQL 之多张表的操作

零
Mysql教程评论98字数 6544阅读21分48秒阅读模式

在 《下载安装及单张表的操作》中介绍的都是处理如下所示的单张表 members 的情况：

image.png文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/16241.html

如果想进行扩展学习，我们可以将保存地区信息的 region 字段删除（顺便也将比较占位置的 updateTime 列删除），新增仅仅保存地区 id 信息 region_id，避免单张表中有过多重复冗余的数据：文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/16241.html

sql

复制代码
ALTER TABLE members DROP region;
ALTER TABLE members ADD region_id INT;
ALTER TABLE members DROP updateTime;

改动后表 members 数据如下：文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/16241.html

image.png文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/16241.html

另建一张表 regions 专门保存地区的相关信息：文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/16241.html

sql

复制代码
CREATE TABLE IF NOT EXISTS regions(
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(10) UNIQUE NOT NULL,
	country VARCHAR(10)
);

往表 regions 中添加一些数据：文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/16241.html

sql

复制代码
INSERT INTO regions (`name`, country) VALUES ('台湾', '中国');
INSERT INTO regions (`name`, country) VALUES ('香港', '中国');
INSERT INTO regions (`name`, country) VALUES ('北京', '中国');

现在表 regions 的数据如下：文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/16241.html

image.png文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/16241.html

外键约束

新增外键

现在我们准备给表 members 的 region_id 填入数据，此时遇到一个问题：region_id 的值应该是取自于表 regions 的 id 字段，而目前却是可以任意输入一个 int 类型的数字。我们应该对其可输入数据进行限制，此时可以对表 members 创建外键约束，因为表 members 已经创建好了，所以我们使用如下语句：文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/16241.html

sql

复制代码
ALTER TABLE members ADD FOREIGN KEY (region_id) REFERENCES regions (id);

如果是在创建表时添加外键，可以使用如下方式：文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/16241.html

sql

复制代码
CREATE TABLE IF NOT EXISTS members(
	-- 省略,
  region_id INT,
	FOREIGN KEY (region_id) REFERENCES regions(id)
);

有了外键约束，当我们往表 members 的 region_id 添加的数据，如果不存在于表 regions 的 id 字段：

sql

复制代码
UPDATE members SET region_id = 10 WHERE id = 1;

就会报错：

image.png

创建好外键后可以通过打开 members 的设计表，查看 "外键" tab 信息：

image.png

可以看到外键在 "删除时" 和 "更新时" 的值为 RESTRICT，它代表着当父表（也就是 regions） 更新或删除某条记录（也就是 id）时，如果该记录已经有关联的外键记录（也就是已经记录到 members 的 region_id 了），就不允许删除或更新。比如我们已经在 members 的 Jay 这条数据添加了 region_id：

image.png

如果想再去将表 regions 中台湾的 id 由为 1 改为 6，就会报错：

image.png

如果想成功修改，可以将 "更新时" 的值改为 CASCADE 后保存，表示当更新时，会将关联的外键记录一起更新：

image.png

如此，我们就能成功将表 regions 中台湾的 id 改为 6 了，并且再去看表 members，会发现原本 Jay 这条记录的 region_id 也自动更新为了 6：

image.png

注意，如果将 "删除时" 的值也改为 CASCADE，那么当我们删除表 regions 的台湾这条记录后，表 members 的 Jay 这条记录会被整个地删除。

另外还有两个可选值：
NO ACTION 为 SQL 标准中的关键字，在 MySQL 中与 RESTRICT 的效果一致；
SET NULL 表示在删除或更新父表中的数据时，将子表中的外键列设置为 NULL。

更新外键

前面是使用 navicat 图形操作工具直接做的相关修改，如果是使用 SQL 语句，可以先将原本的外键删除：

sql

复制代码
ALTER TABLE members DROP FOREIGN KEY members_ibfk_1;

再在添加外键时，指定删除时（ON DELETE）和更新时（ON UPDATE）的值：

sql

复制代码
ALTER TABLE members ADD FOREIGN KEY (region_id) REFERENCES regions (id) 
	ON DELETE CASCADE 
	ON UPDATE CASCADE;

注：members_ibfk_1 为外键名，可以使用 navicat 直接通过 members 的设计表的 "外键" tab 查看到，也可以通过：

sql

复制代码
SHOW CREATE TABLE members;

查看到结果：

image.png

全文如下，可以在第 12 行看到外键名信息：

sql

复制代码
CREATE TABLE `members` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `level` int DEFAULT '1',
  `telephone` char(11) DEFAULT NULL,
  `region_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `phone` (`telephone`),
  UNIQUE KEY `telephone` (`telephone`),
  UNIQUE KEY `telephone_2` (`telephone`),
  KEY `members_ibfk_1` (`region_id`),
  CONSTRAINT `members_ibfk_1` FOREIGN KEY (`region_id`) REFERENCES `regions` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

多表查询

如果我们以如下语句同时查询两张表：

sql

复制代码
SELECT * FROM members, regions;

得到的结果会是表 members 和表 regions 的一个笛卡尔乘积，又称直积，表示为 X*Y，也就是表 members 的每条记录都会和表 regions 的每条记录结合一次：

image.png

我们可以添加查询条件，只有当歌手所属的地区与地区的 id 匹配时，才显示相关的地区信息：

sql

复制代码
SELECT * FROM members, regions WHERE members.region_id = regions.id;

结果如下：

image.png

可以看到，表 members 中 region_id 没有值的记录没有显示出来，表 regions 中的关于 "北京" 和 "香港" 的记录也没有显示出来。为了更好地获得想要的查询结果，我们可以使用下面介绍的 JOIN 子句来设置多表之间的连接方式。

JOIN 子句

左连接

如果想让表 members 中的记录全部展示出来，然后根据 region_id 来展示对应的地区信息，可以使用左连接：

sql

复制代码
SELECT * FROM members LEFT JOIN regions ON members.region_id = regions.id;

LEFT JOIN 的完整写法是 LEFT OUTER JOIN，只不过 OUTER 可以省略，使用 ON 来指定连接条件，结果如下，可以看到表 members 中的数据都展示出来了：

image.png

可表示成如下示意图，红色的部分代表展示的记录（下同）：

image.png

对上述查询结果依旧可以使用 WHERE 来过滤，比如添加 WHERE regions.id IS NULL，即查询表 members 内，region_id 的值与表 regions 中的 id 没有交集的那部分记录 ：

sql

复制代码
SELECT * FROM members LEFT OUTER JOIN regions ON members.region_id = regions.id 
	WHERE regions.id IS NULL;

查询结果为：

image.png

示意图如下：

image.png

右连接

与左连接相对应的，就是右连接：

sql

复制代码
SELECT * FROM members RIGHT JOIN regions ON members.region_id = regions.id;

展示的结果会以右表（regions）为主：

image.png

示意图如下：

image.png

内连接

内连接使用 CROSS JOIN 或直接使用 JOIN：

sql

复制代码
SELECT * FROM members JOIN regions ON members.region_id = regions.id;

结果如下：

image.png

示意图：

内连接.png

看起来和之前 SELECT * FROM members, regions WHERE members.region_id = regions.id; 或是 SELECT * FROM members LEFT JOIN regions ON members.region_id = regions.id WHERE regions.id IS NOT NULL; 好像是一样的，但内连接是在两张表连接时即约束了数据之间的关系，然后得到查询结果；而使用 WHERE 则是将已经查询出来的所有结果做了个筛选。

全连接

在 MySQL 中，可以使用 UNION 结合左连接和右连接来实现全连接的效果：

sql

复制代码
(SELECT * FROM members LEFT JOIN regions ON members.region_id = regions.id) 
UNION 
(SELECT * FROM members RIGHT JOIN regions ON members.region_id = regions.id);

结果如下：

image.png

示意图：

image.png

如果想获取两张表彼此没有交集的数据，可以使用如下所示的筛选条件：

sql

复制代码
(SELECT * FROM members LEFT JOIN regions ON members.region_id = regions.id WHERE regions.id IS NULL) 
UNION 
(SELECT * FROM members RIGHT JOIN regions ON members.region_id = regions.id WHERE members.region_id IS NULL);

结果如下：

image.png

示意图：

全连接-去除交集.png

多对多

在上面这些案例里，一个所属地区可以对应多个歌手，也就是表 regions 里的某个 id 可以作为表 members 里多条数据的 region_id 的值，也就是一对多的关系，但一个歌手，终究只会对应一个所属地区。如果我们把所属地区 region_id 认为是开设演唱会的城市，就会出现一个歌手可以对应多个开设城市，而一个城市，也可以对应多个歌手的多对多关系。此时该如何保存某个歌手举办演唱会的城市信息呢？我们可以再创建一张表，专门用来保存歌手和举办演唱会的城市之间的关系：

sql

复制代码
CREATE TABLE IF NOT EXISTS singer_and_city (
	id INT PRIMARY KEY AUTO_INCREMENT,
	singer_id INT NOT NULL,
	city_id INT NOT NULL,
	FOREIGN KEY (singer_id) REFERENCES members (id) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (city_id) REFERENCES regions (id) ON UPDATE CASCADE ON DELETE CASCADE
)

并插入一些数据：

sql

复制代码
-- Jay 举办演唱会的城市有台北（之前的台湾改为台北）、香港和北京
INSERT INTO singer_and_city (singer_id, city_id) VALUES (1, 6);
INSERT INTO singer_and_city (singer_id, city_id) VALUES (1, 2);
INSERT INTO singer_and_city (singer_id, city_id) VALUES (1, 3);

-- Eson 举办演唱会的城市有香港，北京
INSERT INTO singer_and_city (singer_id, city_id) VALUES (2, 2);
INSERT INTO singer_and_city (singer_id, city_id) VALUES (2, 3);

结果如下：

image.png

对表 members 做的修改是删除了 region_id 字段（需要先删除外键）：

image.png

对表 regions 也做了些许调整，结果如下：

image.png

左连接

现在就可以通过左连接查看所有歌手举办演唱会的城市信息：

sql

复制代码
SELECT * FROM members 
	LEFT JOIN singer_and_city ON members.id = singer_and_city.singer_id 
	LEFT JOIN regions ON singer_and_city.city_id = regions.id;

结果如下：

image.png

如果想精简查询结果，可以选择只展示歌手名字（members.name）和城市名字（`regions.`name），并给它们分别取个别名（singer 和 city），另外我们同样可以给表取个别名，比如下面就将 singer_and_city 取别名为 sac，原名和别名之间的 AS 都省略没写：

sql

复制代码
SELECT members.`name` singer,  regions.`name` city FROM members 
	LEFT JOIN singer_and_city sac ON members.id = sac.singer_id 
	LEFT JOIN regions ON sac.city_id = regions.id;

结果如下：

image.png

如果想查看某位歌手的演唱会情况，只需使用 WHERE 添加搜索条件即可：

sql

复制代码
SELECT members.`name` singer,  regions.`name` city FROM members
	LEFT JOIN singer_and_city sac ON members.id = sac.singer_id 
	LEFT JOIN regions ON sac.city_id = regions.id 
	WHERE members.id = 1 || members.id = 2;

结果如下：

image.png

内连接

如果想查询有举办演唱会的歌手都在哪些城市举办的信息，可以使用内连接：

sql

复制代码
SELECT members.`name` singer,  regions.`name` city FROM members 
	JOIN singer_and_city sac ON members.id = sac.singer_id 
	JOIN regions ON sac.city_id = regions.id;

结果如下：

image.png

右连接

可以使用右连接配合 WHERE 来查询哪些城市没有演唱会：

sql

复制代码
SELECT members.`name` singer,  regions.`name` city FROM members
	RIGHT JOIN singer_and_city sac ON members.id = sac.singer_id 
	RIGHT JOIN regions ON sac.city_id = regions.id WHERE members.id IS NULL;

结果如下：

image.png

子查询

SELECT 语句中还可以有 SELECT 语句作为子查询（其中用到的 JSON_OBJECT()，我会在之后的文章中介绍），比如：

sql

复制代码
SELECT 
  m.id id, m.content content, m.createAt createAt, m.updateAt updateAt, 
  JSON_OBJECT('id', u.id, 'name', u.`name`) user,
  (SELECT COUNT(*) FROM `comment` WHERE moment_id = m.id) commentCount
FROM moment m 
LEFT JOIN users u ON u.id = m.user_id 
LIMIT 7 OFFSET 0;

第 4 行就是在查询结果中添加了 commentCount 字段，其数据来自于存储评论的表 comment，用于展示每条动态（m.content）的评论数量。

感谢.gif

历史上的今天
6 月
27
C++一分钟之-C++20新特性：模块化编程
✅MySQL用了函数到底会不会导致索引失效
【Java教程】MultipartFilter 应用场景说明
【Golang教程】Go 项目文件命名规范是什么？
零
  • 转载请务必保留本文链接：https://www.0s52.com/bcjc/mysqljc/16241.html
    本社区资源仅供用于学习和交流，请勿用于商业用途
    未经允许不得进行转载/复制/分享

加载中...

发表评论