图解Mysql 内连接、左右外连接

﹏ヽ暗。殇╰゛Y 2023-10-02 16:31 11阅读 0赞

展现形式：用两个表（user,fraction），关联字段user.id,fraction.user\_id,一对多

数据库表：user,fraction

#### **建表语句** ####

/*
     Navicat Premium Data Transfer
    
     Source Server         : JCqcby
     Source Server Type    : MySQL
     Source Server Version : 50649
     Source Host           : localhost:3306
     Source Schema         : test
    
     Target Server Type    : MySQL
     Target Server Version : 50649
     File Encoding         : 65001
    
     Date: 24/11/2021 11:53:47
    */
    
    SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    -- Table structure for fraction
    -- ----------------------------
    DROP TABLE IF EXISTS `fraction`;
    CREATE TABLE `fraction`  (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `subject` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
      `score` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
      `user_id` bigint(20) DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = MyISAM AUTO_INCREMENT = 10 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of fraction
    -- ----------------------------
    INSERT INTO `fraction` VALUES (1, 'math', '90', 1);
    INSERT INTO `fraction` VALUES (2, 'english', '89', 1);
    INSERT INTO `fraction` VALUES (3, 'chinese', '99', 1);
    INSERT INTO `fraction` VALUES (4, 'chinese', '98', 2);
    INSERT INTO `fraction` VALUES (5, 'math', '87', 2);
    INSERT INTO `fraction` VALUES (6, 'english', '70', 3);
    INSERT INTO `fraction` VALUES (7, 'math', '100', NULL);
    INSERT INTO `fraction` VALUES (8, 'chinese', '100', NULL);
    INSERT INTO `fraction` VALUES (9, 'english', '100', NULL);
    
    -- ----------------------------
    -- Table structure for user
    -- ----------------------------
    DROP TABLE IF EXISTS `user`;
    CREATE TABLE `user`  (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
      `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = MyISAM AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of user
    -- ----------------------------
    INSERT INTO `user` VALUES (1, 'aaa', 'AAA');
    INSERT INTO `user` VALUES (2, 'bbb', 'BBB');
    INSERT INTO `user` VALUES (3, 'ccc', 'CCC');
    INSERT INTO `user` VALUES (4, 'ddd', 'DDD');
    INSERT INTO `user` VALUES (5, 'eee', 'EEE');
    
    SET FOREIGN_KEY_CHECKS = 1;

#### **表测试数据** ####

user表：

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY3JhenlLLg_size_13_color_FFFFFF_t_70_g_se_x_16][]

fraction表：

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY3JhenlLLg_size_14_color_FFFFFF_t_70_g_se_x_16][]

###  **一、内连接** ###

只查询仅包含符合连接条件的数据

关键字： inner join on

图例:

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY3JhenlLLg_size_20_color_FFFFFF_t_70_g_se_x_16][]

语句： SELECT \* FROM user INNER JOIN fraction ON user.id = fraction.user\_id

查询结果：![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY3JhenlLLg_size_20_color_FFFFFF_t_70_g_se_x_16 1][]

### **二、左外连接** ###

查询左表所有数据和符合连接条件的数据

关键字：left join on

图例：

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY3JhenlLLg_size_20_color_FFFFFF_t_70_g_se_x_16 2][]

语句： SELECT \* FROM user LEFT JOIN fraction ON user.id = fraction.user\_id

查询结果：

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY3JhenlLLg_size_20_color_FFFFFF_t_70_g_se_x_16 3][]

### **三、右外连接**  ###

查询右表所有数据和符合连接条件的数据

关键字：right join on

图例：

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY3JhenlLLg_size_20_color_FFFFFF_t_70_g_se_x_16 4][]

语句： SELECT \* FROM user RIGHT JOIN fraction ON user.id = fraction.user\_id

查询结果：

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY3JhenlLLg_size_20_color_FFFFFF_t_70_g_se_x_16 5][]

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY3JhenlLLg_size_13_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/aee1f85268ba4622a7f818d0b6622a7d.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBAY3JhenlLLg==,size_13,color_FFFFFF,t_70,g_se,x_16
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY3JhenlLLg_size_14_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/3f4da60a95244eaf93d38b532b49efb7.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBAY3JhenlLLg==,size_14,color_FFFFFF,t_70,g_se,x_16
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY3JhenlLLg_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/1a79d883e6c74494a7c96d7e85d51ec0.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBAY3JhenlLLg==,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY3JhenlLLg_size_20_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/61c997ad276e464fb8134ab0b73ee887.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBAY3JhenlLLg==,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY3JhenlLLg_size_20_color_FFFFFF_t_70_g_se_x_16 2]: https://img-blog.csdnimg.cn/7e558c339c2f4b77847c89a1c07cf36f.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBAY3JhenlLLg==,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY3JhenlLLg_size_20_color_FFFFFF_t_70_g_se_x_16 3]: https://img-blog.csdnimg.cn/f66beb332d1940e985abafae99f1fedc.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBAY3JhenlLLg==,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY3JhenlLLg_size_20_color_FFFFFF_t_70_g_se_x_16 4]: https://img-blog.csdnimg.cn/333b4b4a2cee4e48b3f65e09a366b1ba.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBAY3JhenlLLg==,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY3JhenlLLg_size_20_color_FFFFFF_t_70_g_se_x_16 5]: https://img-blog.csdnimg.cn/81422cb893b241d5851722e8e099f6b3.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBAY3JhenlLLg==,size_20,color_FFFFFF,t_70,g_se,x_16