mysql中join各种形式大全及操作和图片

小鱼儿 2024-02-19 15:33 24阅读 0赞

### 一：创建2张表 ###

*join 建表语句*/
    drop database if exists test;
    create database test;
    use test;
     
    /* 左表t1*/
    drop table if exists t1;
    create table t1 (id int not null,name varchar(20));
    insert into t1 values (1,'t1a');
    insert into t1 values (2,'t1b');
    insert into t1 values (3,'t1c');
    insert into t1 values (4,'t1d');
    insert into t1 values (5,'t1f');
     
    /* 右表 t2*/
    drop table if exists t1;
    create table t2 (id int not null,name varchar(20));
    insert into t2 values (2,'t2b');
    insert into t2 values (3,'t2c');
    insert into t2 values (4,'t2d');
    insert into t2 values (5,'t2f');
    insert into t2 values (6,'t2a');

**1.笛卡尔积**

mysql> select * from t1 join t2;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjg4NDcy_size_16_color_FFFFFF_t_70]  
**2.左连接**  
两表关联，左表全部保留，右表关联不上用null表示。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjg4NDcy_size_16_color_FFFFFF_t_70 1]  
**3.右连接**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjg4NDcy_size_16_color_FFFFFF_t_70 2]  
**4、内连接**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjg4NDcy_size_16_color_FFFFFF_t_70 3]  
**5、左表独有**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjg4NDcy_size_16_color_FFFFFF_t_70 4]  
**6、右表独有**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjg4NDcy_size_16_color_FFFFFF_t_70 5]  
**7、全连接**  
oracle里面有full join,但是在mysql中没有full join。我们可以使用union来达到目的。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjg4NDcy_size_16_color_FFFFFF_t_70 6]  
**8、并集去交集**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjg4NDcy_size_16_color_FFFFFF_t_70 7]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjg4NDcy_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/3368aa2e5c624e61a8deaaf60013109b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjg4NDcy_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/aae06f3629d940848409faa24802694b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjg4NDcy_size_16_color_FFFFFF_t_70 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/5573d2da2aab487f8282315f7de3d85f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjg4NDcy_size_16_color_FFFFFF_t_70 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/acc4ac1a52ba420dad692f66632994df.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjg4NDcy_size_16_color_FFFFFF_t_70 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/3e5e8d06ad7f41dd92860a2dbf88614d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjg4NDcy_size_16_color_FFFFFF_t_70 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/58f2a0b9b980453293e96315acd4f0ff.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjg4NDcy_size_16_color_FFFFFF_t_70 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/562d9b8d77b249e38d315b171a75c0e1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNjg4NDcy_size_16_color_FFFFFF_t_70 7]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/68108323774842b4bb409eeb3d19aedc.png