mysql优化

来源imooc sqlercn老师出的mysql开发技巧系列课程



join语句:

    表结构:

        a表:

create table a(id int primary key AUTO_INCREMENT,username text,over text);
insert into a (username,over) values('唐僧','旃檀功德佛'),('猪八戒','净坛使者'),('孙悟空','斗战圣佛'),('沙和尚','金身罗汉');

            +----+-----------+-----------------+

            | id | username  | over            |

            +----+-----------+-----------------+

            |  1 | 唐僧      | 旃檀功德佛       |

            |  2 | 猪八戒    | 净坛使者         |

            |  3 | 孙悟空    | 斗战圣佛         |

            |  4 | 沙和尚    | 金身罗汉         |

            +----+-----------+-----------------+

        b表:

            create table b(id int primary key AUTO_INCREMENT,username text,over text);
            insert into b(username,over) values('孙悟空','成佛'),('牛魔王','被降服'),('蛟魔王','被降服'),('鹏魔王','被降服'),('狮驼王','被降服');

            +----+-----------+-----------+

            | id | username  | over      |

            +----+-----------+-----------+

            |  1 | 孙悟空    | 成佛       |

            |  2 | 牛魔王    | 被降服     |

            |  3 | 蛟魔王    | 被降服     |

            |  4 | 鹏魔王    | 被降服     |

            |  5 | 狮驼王    | 被降服     |

            +----+-----------+-----------+

    分为五种:

        inner:内连接,full outer:全外连接,left outer左外连接,right outer右外连接,cross交叉链接


    内连接 inner:

图片1.png

select a.username,a.over,b.over FROM a INNER JOIN b ON a.username=b.username;

            +-----------+--------------+--------+

            | username  | over         | over   |

            +-----------+--------------+--------+

            | 孙悟空    | 斗战圣佛      | 成佛   |

            +-----------+--------------+--------+

        把两个表中的公共部分查询出来


    左外连接 left:

        以左表为基础,查询出左表所有记录,也可以查询只在左表不在右表的记录,是not in的优化方法

图片2.png

        查询悟空四人组中不是悟空的兄弟

 select a.username,a.over,b.over from a left join b on a.username=b.username where b.username is null;

        +-----------+-----------------+------+

        | username  | over            | over |

        +-----------+-----------------+------+

        | 唐僧      | 旃檀功德佛      | NULL |

        | 猪八戒    | 净坛使者        | NULL |

        | 沙和尚    | 金身罗汉        | NULL |

        +-----------+-----------------+------+


    右外连接 right:

        以右表为基础,查询出右表所有记录,也可以查询只在右表不在左表的记录,是not in的优化方法

图片3.png

        和悟空的结拜兄弟中,那些人没有去取经

select b.username,b.over,a.over from a right join b on a.username=b.username where a.username is null;

        +-----------+-----------+------+

        | username  | over      | over |

        +-----------+-----------+------+

        | 牛魔王    | 被降服    | NULL |

        | 蛟魔王    | 被降服    | NULL |

        | 鹏魔王    | 被降服    | NULL |

        | 狮驼王    | 被降服    | NULL |

        +-----------+-----------+------+


    全连接 full:

        可以看作是左连接和右连接的合集

图片4.png

        但是mysql不支持full join

        可以用union all方式把左连接和有链接当成一个合集

select a.username,a.over,b.over from a left join b on a.username=b.username union all select b.username,a.over,b.over from a right join b on a.username=b.username;

        +-----------+-----------------+-----------+

        | username  | over            | over      |

        +-----------+-----------------+-----------+

        | 孙悟空    | 斗战圣佛        | 成佛      |

        | 唐僧      | 旃檀功德佛      | NULL      |

        | 猪八戒    | 净坛使者        | NULL      |

        | 沙和尚    | 金身罗汉        | NULL      |

        | 孙悟空    | 斗战圣佛        | 成佛      |

        | 牛魔王    | NULL            | 被降服    |

        | 蛟魔王    | NULL            | 被降服    |

        | 鹏魔王    | NULL            | 被降服    |

        | 狮驼王    | NULL            | 被降服    |

        +-----------+-----------------+-----------+


    交叉链接 cross:

图片5.png

        没有on从句

        其实就是笛卡尔积

select * from a cross join b;

        +----+-----------+-----------------+----+-----------+-----------+

        | id | username  | over            | id | username  | over      |

        +----+-----------+-----------------+----+-----------+-----------+

        |  1 | 唐僧      | 旃檀功德佛      |  1 | 孙悟空    | 成佛      |

        |  2 | 猪八戒    | 净坛使者        |  1 | 孙悟空    | 成佛      |

        |  3 | 孙悟空    | 斗战圣佛        |  1 | 孙悟空    | 成佛      |

        |  4 | 沙和尚    | 金身罗汉        |  1 | 孙悟空    | 成佛      |

        |  1 | 唐僧      | 旃檀功德佛      |  2 | 牛魔王    | 被降服    |

        |  2 | 猪八戒    | 净坛使者        |  2 | 牛魔王    | 被降服    |

        |  3 | 孙悟空    | 斗战圣佛        |  2 | 牛魔王    | 被降服    |

        |  4 | 沙和尚    | 金身罗汉        |  2 | 牛魔王    | 被降服    |

        |  1 | 唐僧      | 旃檀功德佛      |  3 | 蛟魔王    | 被降服    |

        |  2 | 猪八戒    | 净坛使者        |  3 | 蛟魔王    | 被降服    |

        |  3 | 孙悟空    | 斗战圣佛        |  3 | 蛟魔王    | 被降服    |

        |  4 | 沙和尚    | 金身罗汉        |  3 | 蛟魔王    | 被降服    |

        |  1 | 唐僧      | 旃檀功德佛      |  4 | 鹏魔王    | 被降服    |

        |  2 | 猪八戒    | 净坛使者        |  4 | 鹏魔王    | 被降服    |

        |  3 | 孙悟空    | 斗战圣佛        |  4 | 鹏魔王    | 被降服    |

        |  4 | 沙和尚    | 金身罗汉        |  4 | 鹏魔王    | 被降服    |

        |  1 | 唐僧      | 旃檀功德佛      |  5 | 狮驼王    | 被降服    |

        |  2 | 猪八戒    | 净坛使者        |  5 | 狮驼王    | 被降服    |

        |  3 | 孙悟空    | 斗战圣佛        |  5 | 狮驼王    | 被降服    |

        |  4 | 沙和尚    | 金身罗汉        |  5 | 狮驼王    | 被降服    |

        +----+-----------+-----------------+----+-----------+-----------+


xwm

还是一个菜鸟


Comments are closed.