温故而知新-MySQL 关联查询

默认Inner Join

用户表：

CREATE TABLE `users`
(
    `id`            int          NOT NULL AUTO_INCREMENT,
    `username`      varchar(50)  NOT NULL,
    `password`      varchar(255) NOT NULL,
    `email`         varchar(100)      DEFAULT NULL,
    `register_time` timestamp    NULL DEFAULT CURRENT_TIMESTAMP,
    `score`         bigint            DEFAULT '0',
    `age`           bigint            DEFAULT '0',
    `level`         tinyint           DEFAULT '1',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 14
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci

插入数据

insert into MyDataBase.users (id, username, password, email, register_time, score, age, level)
values  (1, 'user1', 'password1', 'user1@example.com', '2024-12-22 11:22:36', 51, 91, 4),
        (2, 'user2', 'password2', 'user2@example.com', '2024-12-22 11:22:36', 47, 91, 3),
        (3, 'user3', 'password3', 'user3@example.com', '2024-12-22 11:22:36', 80, 83, 2),
        (4, 'user4', 'pass4word', 'user4@example.com', '2024-12-22 12:38:44', 59, 39, 2),
        (5, 'user5', 'password5', 'user5@example.com', '2024-12-22 12:38:44', 56, 50, 3),
        (6, 'user6', 'password6', 'user6@example.com', '2024-12-22 12:38:44', 56, 33, 3),
        (7, 'user7', 'password7', 'user7@example.com', '2024-12-22 12:38:44', 38, 15, 2),
        (8, 'user8', 'password8', 'user8@example.com', '2024-12-22 12:38:44', 90, 76, 1),
        (9, 'user9', 'password9', 'user9@example.com', '2024-12-22 12:38:44', 56, 32, 4),
        (10, 'user10', 'password10', 'user10@example.com', '2024-12-22 12:38:44', 92, 35, 2),
        (11, 'user11', 'password11', 'user11@example.com', '2024-12-22 12:38:44', 60, 78, 1),
        (12, 'user12', 'password12', 'user12@example.com', '2024-12-22 12:38:44', 27, 86, 2),
        (13, 'user13', 'password13', 'user13@example.com', '2024-12-22 12:38:44', 54, 96, 3);

用户详情表

CREATE TABLE IF NOT EXISTS user_details
(
    detail_id    INT AUTO_INCREMENT PRIMARY KEY,
    user_id      INT UNIQUE,
    full_name    VARCHAR(100),
    email        VARCHAR(100),
    phone_number VARCHAR(20),
    address      VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES users (id)
);

插入点数据

INSERT INTO user_details (user_id, full_name, phone_number, address)
VALUES 
    (1, 'User One', '1234567890', '123 Main St'),
    (2, 'User Two', '9876543210', '456 Elm St'),
    (3, 'User Three', '5551234567', '789 Oak St'),
    (4, 'User Four', '1112223333', '321 Pine St'),
    (5, 'User Five', '4445556666', '654 Birch St'),
    (6, 'User Six', '7778889999', '987 Cedar St'),
    (7, 'User Seven', '2223334444', '876 Maple St'),
    (8, 'User Eight', '3334445555', '543 Spruce St'),
    (9, 'User Nine', '6667778888', '210 Walnut St'),
    (10, 'User Ten', '8889990000', '101 Cherry St');

select * from users JOIN user_details ud on users.id = ud.user_id;

id	username	password	email	register_time	score	age	level	detail_id	user_id	full_name	email	phone_number	address
1	user1	password1	user1@example.com	2024-12-22 11:22:36	51	91	4	1	1	User One	null	1234567890	123 Main St
2	user2	password2	user2@example.com	2024-12-22 11:22:36	47	91	3	2	2	User Two	null	9876543210	456 Elm St
3	user3	password3	user3@example.com	2024-12-22 11:22:36	80	83	2	3	3	User Three	null	5551234567	789 Oak St
4	user4	pass4word	user4@example.com	2024-12-22 12:38:44	59	39	2	4	4	User Four	null	1112223333	321 Pine St
5	user5	password5	user5@example.com	2024-12-22 12:38:44	56	50	3	5	5	User Five	null	4445556666	654 Birch St
6	user6	password6	user6@example.com	2024-12-22 12:38:44	56	33	3	6	6	User Six	null	7778889999	987 Cedar St
7	user7	password7	user7@example.com	2024-12-22 12:38:44	38	15	2	7	7	User Seven	null	2223334444	876 Maple St
8	user8	password8	user8@example.com	2024-12-22 12:38:44	90	76	1	8	8	User Eight	null	3334445555	543 Spruce St
9	user9	password9	user9@example.com	2024-12-22 12:38:44	56	32	4	9	9	User Nine	null	6667778888	210 Walnut St
10	user10	password10	user10@example.com	2024-12-22 12:38:44	92	35	2	10	10	User Ten	null	8889990000	101 Cherry St

这就是多表关联查询，语法是 JOIN ON。

如果 user 表找不到对应的user_detail,没有关联的 user_details , JOIN ON 其实默认是 INNER JOIN ON 可以看到上面联查的记录只有 10 条，因为user_details 只能对应10 条

查询的时候需要使用 join on，默认是 inner join 也就是只返回有关联的记录

LEFT JOIN 是额外返回左表中没有关联上的数据
RIGHT JOIN 是额外返回右表中没有关联上的数据。

左右连表

FROM 后的是左表，JOIN 后的表是右表：FROM LTable left join RTable (其实对应的是 Left/Right Join的左右啦！)

SELECT
	r.recipe_name,
	c.comment_text
FROM
	recipes as  r
	LEFT JOIN recipes_comments AS c ON r.recipe_id = c.recipe_id;

recipes 表 left join recipes_comments 表

额外返回 recipes表中没有关联上的数据，意思就是虽然没有对上的数据，但是也会返回左表的！

recipe_name	comment_text
番茄鸡蛋面	什么时候更新一下好图片
番茄鸡蛋面	这张图确实不太好看
番茄鸡蛋面	看后面什么时候做啦
牛肉丸青菜面	厨师来认证一下了
鲜虾煮面	我来组成沙发
鲜虾煮面	我来组成板凳
鲜虾煮面	在你的评论下，那我就是板凳腿了
鲜虾煮面	哈哈哈
鲜虾煮面	哈哈哈
鲜虾煮面	嗯嗯
梅子酒	(NULL)
孜然土豆火腿	(NULL)
黄豆焖猪蹄	(NULL)
秘制叉烧	(NULL)
豆芽牛肉	(NULL)
西洋菜汤	(NULL)
艾叶粑粑(时令)	(NULL)
咸菜蛋炒饭	(NULL)

如果是右连接,则保证右表 recipes_comments 的完整，如果左表没有对应关联数据，则不会出现！

SELECT
	r.recipe_name,
	c.comment_text
FROM
	recipes as  r
	RIGHT JOIN recipes_comments AS c ON r.recipe_id = c.recipe_id;

可以通过一个子查询验证, 从上面左连接查询看得出 西洋菜汤 是没有评论的

所以去查子查询结果应该是查不出的

SELECT
	* 
FROM
	(
	SELECT
		r.recipe_name,
		c.comment_text 
	FROM
		recipes AS r
		RIGHT JOIN recipes_comments AS c ON r.recipe_id = c.recipe_id 
	) AS C 
WHERE
	C.recipe_name = "西洋菜汤"

但是 鲜虾煮蛋面 是有评论的

SELECT
	* 
FROM
	(
	SELECT
		r.recipe_name,
		c.comment_text 
	FROM
		recipes AS r
		RIGHT JOIN recipes_comments AS c ON r.recipe_id = c.recipe_id 
	) AS C 
WHERE
	C.recipe_name like "鲜%"

recipe_name	comment_text
鲜虾煮面	我来组成沙发
鲜虾煮面	我来组成板凳
鲜虾煮面	在你的评论下，那我就是板凳腿了
鲜虾煮面	哈哈哈
鲜虾煮面	哈哈哈
鲜虾煮面	嗯嗯

中间表多表连表

有一张纪念日表一张用户表

例如：一个纪念日可以有多个用户共有，比如婚恋纪念日绑定给两个用户，一个用户也可以有多个纪念日！

中间表 user_id 用户id anniversary_id 对应纪念日id

SELECT
	u.username,
	au.anniversary_id,
	a.title 
FROM
	users AS u
	LEFT JOIN anniversary_user AS au ON u.userid = au.user_id
	LEFT JOIN anniversary AS a ON a.anniversary_id = au.anniversary_id;;

对于我这个 SQL 新手来说有点懵，可以拆开看

首先，通过第一个LEFT JOIN，将anniversary_user表和users表连接起来，得到一个包含用户信息和关联关系的临时结果集。

SELECT
	u.username,
	au.anniversary_id 
FROM
	users AS u
	LEFT JOIN anniversary_user AS au ON u.userid = au.user_id;

username	anniversary_id
村望老弟6	12449846527004672
村望老弟6	12449399246426112
村望老弟	12731695363002368
村望老弟	12724900074622976
村望老弟	12485110234353664
村望老弟	12478846161659084
村望老弟	12452260978102272
用户1680077022441	(NULL)
用户1680077022441	14550531125022720
用户1680077022441	1455024242268792
用户1680077022441	14547995483705344
用户1680077022441	14547883701309440
用户1680082539062	12756884465389568
用户1680082539062	12544698816139264
用户1680082539062	12486741428539392
用户1680082539062	12486478827360256
用户1680082539062	12452260978102272
用户1680085365890	(NULL)
用户1680087057572	(NULL)

因为是 user 左联 anniversary_user 所以会完整查出user 数据，如果这个用户没有创建过纪念日，则为 null！

然后，通过第二个LEFT JOIN，将上一步的临时结果集与anniversary表连接起来，得到一个包含纪念日信息、用户信息和关联关系的新临时结果集。

SELECT
	u.username,
	au.anniversary_id,
	a.title 
FROM
	users AS u
	LEFT JOIN anniversary_user AS au ON u.userid = au.user_id
	LEFT JOIN anniversary AS a ON a.anniversary_id = au.anniversary_id;

username	anniversary_id	title
村望老弟6	12452260978102272	和宝宝在一起的日子
村望老弟6	12449846527004672	周杰伦演唱会
村望老弟6	12449399246426112	(NULL)
村望老弟	12731695363002368	东航为俊森吹喇叭
村望老弟	12724900074622976	牛姐发红包啦
村望老弟	12485110234353664	测试
村望老弟	12478846161659084	冲冲冲
村望老弟	12452260978102272	和宝宝在一起的日子
用户1680077022441	(NULL)	(NULL)
用户1680077022441	14550531125022720	2
用户1680077022441	1455024242268792	1
用户1680077022441	14547995483705344	333
用户1680077022441	14547883701309440	时区2
用户1680082539062	12756884465389568	报销
用户1680082539062	12544698816139264	1234
用户1680082539062	12486741428539392	测试
用户1680082539062	12486478827360256	佳速生日
用户1680082539062	12452260978102272	和宝宝在一起的日子
用户1680085365890	(NULL)	(NULL)
用户1680087057572	(NULL)	(NULL)
神说要有光	1269706806875942	测试玩玩

LEFT JOIN 就是优先查左边的完整结果集匹配不上的则为 null ，上面是用户角度查询

如果只需要查询完整纪念日的数据，可以换成 RIGHT JOIN

SELECT
	u.username,
	au.anniversary_id,
	a.title 
FROM
	users AS u
	LEFT JOIN anniversary_user AS au ON u.userid = au.user_id
	RIGHT JOIN anniversary AS a ON a.anniversary_id = au.anniversary_id;

目录CONTENT

温故而知新-MySQL 关联查询

默认Inner Join

左右连表

中间表多表连表

评论区