MYSQL 中连接的使用

刺骨的言语ヽ痛彻心扉 2024-03-31 14:35 27阅读 0赞

#### 文章目录 ####

*  0 写在前面
 *  1 语法说明
 *  2 SQL准备
 *  3 举例说明
 *   *  3.1 内连接
     *  3.2 左连接
     *  3.3 右连接
 *  4 写在最后

## 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;

![在这里插入图片描述][f35164d81fa84506a055f063dae78a5b.png]  
![在这里插入图片描述][032057a20c8d405fab18a2f7b8e120f7.png]

如何插入SQL可以查看[添加链接Navicat Premium导出数据库中的结构及数据及运行SQL文件][Navicat Premium_SQL]  
如果sql执行错误或者出现这个错误\[Err\] 1273 - Unknown collation: ‘utf8mb4\_0900\_ai\_ci‘可以查看[添加解决：\[Err\] 1273 - Unknown collation: ‘utf8mb4\_0900\_ai\_ci‘][Err_ 1273 - Unknown collation_ _utf8mb4_0900_ai_ci]  
如果对于uuid不理解可以查看[java中使用uuid（2）-处理数据库逻辑][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;

![在这里插入图片描述][eeb8fb590bc14db993362c9fdba33b1d.png]  
结果：  
![在这里插入图片描述][4d87a94fc0ca47c4a5b6f4648d2eb2c4.png]

### 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;

![在这里插入图片描述][4404112be1c44e2bbbb3a39786b2afc7.png]

### 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;

![在这里插入图片描述][081ec9106e3742dfb2d1172352dac46a.png]

## 4 写在最后 ##

这一部分比较抽象，难以理解，不过经常使用会有很好的突破。  
经常使用的是左连接，所以可以重点学习左连接的使用。

[f35164d81fa84506a055f063dae78a5b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/5bff1eef27a44999a96204ce86d94d22.png
[032057a20c8d405fab18a2f7b8e120f7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/6998959ec8b74a53bc6beafc56f18605.png
[Navicat Premium_SQL]: https://blog.csdn.net/weixin_45909221/article/details/127651601
[Err_ 1273 - Unknown collation_ _utf8mb4_0900_ai_ci]: https://blog.csdn.net/weixin_45909221/article/details/127895611
[java_uuid_2_-]: https://blog.csdn.net/weixin_45909221/article/details/127483156
[eeb8fb590bc14db993362c9fdba33b1d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/f11fe151799d4709a3eba9a28f04bfac.png
[4d87a94fc0ca47c4a5b6f4648d2eb2c4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/103b927eb3384bc691f39a6e5aca10f1.png
[4404112be1c44e2bbbb3a39786b2afc7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/38b77241a6564f08804a2b09563e6e32.png
[081ec9106e3742dfb2d1172352dac46a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/7f2e9f30af434088a3ae6fe054b66dc3.png