@[TOC]
0 写在前面
实际业务中，查询数据库大多都是多表链接查询，所以MYSQL的连接的使用非常重要。
连接有三种:INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
1 语法说明
一般是设置查询字段+设置两张表的连接 + 连接条件。
例如：
SELECT
'需要查询的字段'
FROM
'表一'
'内连接/左连接/右连接' '表二'
ON
'连接条件'2 SQL准备
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user_wages_information
-- ----------------------------
DROP TABLE IF EXISTS `user_wages_information`;
CREATE TABLE `user_wages_information` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`user_wager_information_uuid` char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用户工资表主键',
`user_information_uuid` char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用户信息主键',
`year` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '年份',
`month` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '月份',
`base_salary` double UNSIGNED NULL DEFAULT NULL COMMENT '基本工资',
`bonus` double NULL DEFAULT NULL COMMENT '奖金',
`punish` double NULL DEFAULT NULL COMMENT '扣除',
`remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户工资信息表' ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Records of user_wages_information
-- ----------------------------
INSERT INTO `user_wages_information` VALUES (1, '92b639cf-c2ca-4fff-9fd8-4c8074e8109c', 'f76b7382-8fc5-4197-8d46-f9d6e3729089', '2022', '10', 3000, 1500, 100, '4400');
INSERT INTO `user_wages_information` VALUES (2, '4dcf10bd-2706-49c4-a505-41da5667cd08', 'f76b7382-8fc5-4197-8d46-f9d6e3729089', '2022', '9', 3000, 1500, 0, '4500');
INSERT INTO `user_wages_information` VALUES (3, '2a560bbf-c98e-4bd4-b8ed-4068d1c261ab', '197410ca-9dde-4850-9ff7-5153786db821', '2022', '10', 9000, 4000, 0, '13000');
INSERT INTO `user_wages_information` VALUES (4, '7eb42aa0-dbc3-4d88-a636-d3044ec6efdf', 'f76b7382-8fc5-4197-8d46-f9d6e3729089', '2022', '8', 3000, 1000, 500, '3500');
INSERT INTO `user_wages_information` VALUES (5, '37cb6240-a958-44db-8276-2f2af2156a32', '197410ca-9dde-4850-9ff7-5153786db821', '2022', '9', 8000, 3000, 0, '11000');
INSERT INTO `user_wages_information` VALUES (6, 'f2009698-8152-4045-b7a8-927994993af9', '01198488-a0b6-46ba-9311-9e64ba1d41ef', '2022', '10', 5000, 0, 0, '5000');
INSERT INTO `user_wages_information` VALUES (7, 'e3e45ce4-1db1-40ff-8efe-f7a0685972e2', '01198488-a0b6-46ba-9311-9e64ba1d41ef', '2022', '9', 5000, 0, 0, '5000');
SET FOREIGN_KEY_CHECKS = 1;
-- ----------------------------
-- Table structure for user_information
-- ----------------------------
DROP TABLE IF EXISTS `user_information`;
CREATE TABLE `user_information` (
`user_id` int NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`user_uuid` char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用户主键',
`user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '姓名',
`user_sex` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '性别',
`user_age` int NOT NULL COMMENT '年龄',
`user_email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '邮箱',
PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户基本信息表' ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Records of user_information
-- ----------------------------
INSERT INTO `user_information` VALUES (1, 'f76b7382-8fc5-4197-8d46-f9d6e3729089', '张三', 'MALE', 50, 'xiaoming@xiaoming.com');
INSERT INTO `user_information` VALUES (2, '197410ca-9dde-4850-9ff7-5153786db821', '李四', 'FAMALE', 30, 'lisi@lisi.com');
INSERT INTO `user_information` VALUES (3, '01198488-a0b6-46ba-9311-9e64ba1d41ef', '小明', 'MALE', 21, 'xiaoming@xiaoming.com');
INSERT INTO `user_information` VALUES (4, 'bf68ca01-0b0e-7c82-5f15-d17bd23355d8', '小红', 'FAMALE', 24, 'xiaohong@xiaohong.com');
INSERT INTO `user_information` VALUES (5, '72236b56-8bb4-7f54-44d4-75594005ec54', '大鹅', 'MALE', 21, 'dae@dae.com');
SET FOREIGN_KEY_CHECKS = 1;
如何插入SQL可以查看添加链接Navicat Premium导出数据库中的结构及数据及运行SQL文件
如果sql执行错误或者出现这个错误[Err] 1273 - Unknown collation: ‘utf8mb4_0900_ai_ci‘
如果对于uuid不理解可以查看java中使用uuid（2）-处理数据库逻辑来进行比较学习。
3 举例说明
3.1 内连接
MySQL的INNER JOIN(也可以省略 INNER 使用 JOIN，效果一样)来连接两张表来读取数据。
sql：查询编号，姓名，性别，和十月份的工资
SELECT
t1.user_id,
t1.user_name,
t1.user_sex,
t2.remark
FROM
user_information t1
INNER JOIN user_wages_information t2 ON t1.user_uuid = t2.user_information_uuid
AND t2.YEAR = 2022
AND t2.MONTH = 10;
结果：
3.2 左连接
MySQL left join 与 join 有所不同。 MySQL LEFT JOIN 会读取左边数据表的全部数据，即使右边表无对应数据。
sql:
SELECT
t1.user_id,
t1.user_name,
t1.user_sex,
t2.remark
FROM
user_information t1
LEFT JOIN user_wages_information t2 ON t1.user_uuid = t2.user_information_uuid
AND t2.YEAR = 2022
AND t2.MONTH = 10;
3.3 右连接
与左连接相反，MySQL RIGHT JOIN 会读取右边数据表的全部数据，即使左边边表无对应数据。
sql
SELECT
t1.user_id,
t1.user_name,
t1.user_sex,
t2.remark
FROM
user_information t1
RIGHT JOIN user_wages_information t2 ON t1.user_uuid = t2.user_information_uuid
AND t2.YEAR = 2022
AND t2.MONTH = 10;
4 写在最后
这一部分比较抽象，难以理解，不过经常使用会有很好的突破。
经常使用的是左连接，所以可以重点学习左连接的使用。
