MySQL高级---索引优化分析(二、join查询练习)

矫情吗；* 2021-09-23 19:20 324阅读 0赞

## 1、建表Sql ##

CREATE TABLE`tbl_dept`(
    `id`INT(11) NOT NULL AUTO_INCREMENT,
    `deptName`VARCHAR(30) DEFAULT NULL,
    `locAdd`VARCHAR(40) DEFAULT NULL,
    PRIMARY KEY (`id`)
    )ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8;
    
    CREATE TABLE`tbl_emp`(
    `id`INT(11) NOT NULL AUTO_INCREMENT,
    `name`VARCHAR(20) DEFAULT NULL,
    `deptId`INT(11)DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY`idx_dept_id`(`deptId`)
    )ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
    
    INSERT INTO tbl_dept(deptName,locAdd)VALUES('RD',11);
    INSERT INTO tbl_dept(deptName,locAdd)VALUES('HR',12);
    INSERT INTO tbl_dept(deptName,locAdd)VALUES('MK',13);
    INSERT INTO tbl_dept(deptName,locAdd)VALUES('MIS',14);
    INSERT INTO tbl_dept(deptName,locAdd)VALUES('FD',15);
    
    INSERT INTO tbl_emp(NAME,deptId)VALUES('z3',1);
    INSERT INTO tbl_emp(NAME,deptId)VALUES('z4',1);
    INSERT INTO tbl_emp(NAME,deptId)VALUES('z5',1);
    INSERT INTO tbl_emp(NAME,deptId)VALUES('w5',2);
    INSERT INTO tbl_emp(NAME,deptId)VALUES('w6',2);
    INSERT INTO tbl_emp(NAME,deptId)VALUES('s7',3);
    INSERT INTO tbl_emp(NAME,deptId)VALUES('s8',4);
    INSERT INTO tbl_emp(NAME,deptId)VALUES('s9',51);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70]![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 1]

## 2、以上面的建表为例，测试七种JOIN ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 2]  
1、`select * from tbl_emp a inner join tbl_dept b on a.deptId =b.id;`  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 3]  
2、`select * from tbl_emp a left join tbl_dept b on a.deptId =b.id;`  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 4]  
3、`select * from tbl_emp a right join tbl_dept b on a.deptId =b.id;`  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 5]

4、`select * from tbl_emp a left join tbl_dept b on a.deptId =b.id where b.id is null;`  
![在这里插入图片描述][20200121102732737.png]

5、`select * from tbl_emp a right join tbl_dept b on a.deptId =b.id where a.deptId is null;`  
![在这里插入图片描述][20200121102917432.png]  
6、MySQL不支持full outer join 这样的语法 ，但是在oracle可以适用。  
`select * from tbl_emp a full outer join tbl_dept b on a.deptId =b.id;`  
此时我们可以使用`union`去重操作将两张表的左连接和右连接相加

select * from tbl_emp a left join tbl_dept b on a.deptId =b.id  
    union 
    select * from tbl_emp a right join tbl_dept b on a.deptId =b.id;

**a，b表的公有+a表的独有+b表的独有**![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 6]

7、查询a,b表各自的独有

select * from tbl_emp a left join tbl_dept b on a.deptId =b.id  where b.id is null
    union 
    select * from tbl_emp a right join tbl_dept b on a.deptId =b.id where a.deptId is null;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 7]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200121095922697.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 1]: /images/20210923/2aa860bad695472daf0a602a200ef973.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 2]: /images/20210923/cf78ad0a4a154695950d7602a4ca01b8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 3]: /images/20210923/b1ea87fe5f1e4fecb6e8219cc29f3b48.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 4]: /images/20210923/7fd6cabd1cd24184a24c2cd2c2aa69d9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 5]: /images/20210923/32e5018749334525a058bf2521919fed.png
[20200121102732737.png]: /images/20210923/08a9c104a4194b5a94b9d08cc533debe.png
[20200121102917432.png]: /images/20210923/ee360d48b012440698dd119b3558ea97.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 6]: /images/20210923/929e924a3da1407a8359f1c1b4930a0b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 7]: /images/20210923/ab4226e915f5427293fffcf171771d77.png