fny5jt9 发表于 2024-8-4 09:50:02

关于MySQL内连接与外连接用法，全都在这儿了


    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">上节课<span style="color: black;">咱们</span>给<span style="color: black;">大众</span>介绍了常用的MySQL多表联合<span style="color: black;">查找</span>用法，<span style="color: black;">晓得</span>了left join /right join /inner join 的基本用法。本节课<span style="color: black;">咱们</span>继续展开讲讲MySQL多表联合<span style="color: black;">查找</span>的其他用法——</span><strong style="color: blue;"><span style="color: black;">全连接与笛卡尔连接。</span></strong></span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;"><strong style="color: blue;"><span style="color: black;">01</span></strong></span></span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;"><strong style="color: blue;"><span style="color: black;">全连接union&amp;union all</span></strong></span></span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;"><span style="color: black;">首要</span>看什么是全连接，<span style="color: black;">详细</span><span style="color: black;">能够</span>看以下韦恩图。</span></span></p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/1b8baea892e04ac4a952a6c70ac28e6e~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723338665&amp;x-signature=AB9nef%2BN5o4Da1pLAgj52y4YBc8%3D" style="width: 50%; margin-bottom: 20px;">
      <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">全连接full outer join</p>
    </div>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><strong style="color: blue;"><span style="color: black;">全连接定义：全连接全<span style="color: black;">叫作</span>为全外连接，它<span style="color: black;">亦</span>是外连接的一种。它将<span style="color: black;">上下</span>两个表<span style="color: black;">经过</span>ON进行<span style="color: black;">要求</span>连接，并且最后列出<span style="color: black;">上下</span>两个表的所有记录。</span></strong></span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">但在MySQL中，它是不支持全连接这种用法的，<span style="color: black;">不外</span><span style="color: black;">能够</span><span style="color: black;">运用</span>union或union all进行弥补。例如，<span style="color: black;">此刻</span><span style="color: black;">咱们</span>想把上节课左连接与右连接的结果合并起来，即<span style="color: black;">咱们</span>想把<span style="color: black;">运用</span></span><strong style="color: blue;"><span style="color: black;">华为手机的乘客编号、姓名、<span style="color: black;">是不是</span>幸存、船舱等级和手机品牌</span></strong><span style="color: black;">与</span><strong style="color: blue;"><span style="color: black;">船舱等级为3且存活的乘客编号、姓名、<span style="color: black;">是不是</span>幸存、船舱等级和手机品牌两种<span style="color: black;">查找</span>结果合并</span></strong><span style="color: black;">，<span style="color: black;">咱们</span>看分别<span style="color: black;">运用</span>union和union all该怎么做。</span></span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><strong style="color: blue;"><span style="color: black;">【union】</span></strong></span></p><span style="color: black;">SELECT</span>a.PassengerId,a.name,b.survived,b.pclass,a.phonebrand<span style="color: black;">from</span> phone a
    <span style="color: black;">left</span> <span style="color: black;">join</span> titanic b
    <span style="color: black;">on</span> a.PassengerId=b.passengerId
    <span style="color: black;">where</span> a.phonebrand=<span style="color: black;">"HUAWEI"</span>
    <span style="color: black;">UNION</span>
    <span style="color: black;">SELECT</span>a.passengerId,a.name,b.survived,b.pclass,a.phonebrand<span style="color: black;">from</span> phone a
    <span style="color: black;">right</span> <span style="color: black;">join</span> titanic b
    <span style="color: black;">on</span> a.PassengerId=b.passengerId
    <span style="color: black;">where</span> b.survived=<span style="color: black;">1</span>
    <span style="color: black;">and</span> b.pclass=<span style="color: black;">3</span>;<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;"><span style="color: black;">运用</span>union<span style="color: black;">查找</span>结果如下</span></span></p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/cee57fcf176d4b39a36d538a4ffd0535~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723338665&amp;x-signature=2Kg6dyA5XgaUoZQq7W1YHMSrPPs%3D" style="width: 50%; margin-bottom: 20px;">
      <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">union <span style="color: black;">查找</span>结果</p>
    </div>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><strong style="color: blue;"><span style="color: black;">【union all】</span></strong></span></p><span style="color: black;">SELECT</span> a.PassengerId,a.name,b.survived,b.pclass,a.phonebrand
    <span style="color: black;">from</span> phone a
    <span style="color: black;">left</span> <span style="color: black;">join</span> titanic b
    <span style="color: black;">on</span> a.PassengerId=b.passengerId
    <span style="color: black;">where</span> a.phonebrand=<span style="color: black;">"HUAWEI"</span>
    <span style="color: black;">UNION</span> <span style="color: black;">all</span>
    <span style="color: black;">SELECT</span> a.passengerId,a.name,b.survived,b.pclass,a.phonebrand
    <span style="color: black;">from</span> phone a
    <span style="color: black;">right</span> <span style="color: black;">join</span> titanic b
    <span style="color: black;">on</span> a.PassengerId=b.passengerId
    <span style="color: black;">where</span> b.survived=<span style="color: black;">1</span>
    <span style="color: black;">and</span> b.pclass=<span style="color: black;">3</span>;<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;"><span style="color: black;">运用</span>union all <span style="color: black;">查找</span>结果如下</span></span></p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/9bd94cf349b4465099f8aabc78d62db3~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723338665&amp;x-signature=SS9qDWCsjOStUJtctOcOdoAZ76M%3D" style="width: 50%; margin-bottom: 20px;">
      <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">union all<span style="color: black;">查找</span>结果</p>
    </div>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;"><span style="color: black;">能够</span>看到，相同的<span style="color: black;">查找</span>语句union的<span style="color: black;">查找</span>结果为23行记录，而union all 的<span style="color: black;">查找</span>结果为131行记录。<span style="color: black;">因此呢</span>，<span style="color: black;">能够</span>简单总结</span><strong style="color: blue;"><span style="color: black;">union 与union all</span></strong><span style="color: black;"> 两种<span style="color: black;">查找</span>结果的</span><strong style="color: blue;"><span style="color: black;">要点与区别</span></strong><span style="color: black;">如下：</span></span></p><span style="color: black;"><strong style="color: blue;"><span style="color: black;"><span style="color: black;">经过</span>union连接的<span style="color: black;">查找</span>语句前后分别单独取出的列数<span style="color: black;">必要</span>相同；</span></strong></span><span style="color: black;"><strong style="color: blue;"><span style="color: black;">在不<span style="color: black;">需求</span>合并的前后列名<span style="color: black;">叫作</span>相<span style="color: black;">同期</span>，输出结果以合并前的<span style="color: black;">第1</span>段SQL列名<span style="color: black;">叫作</span>为准；</span></strong></span><span style="color: black;"><strong style="color: blue;"><span style="color: black;">union会对合并结果进行去重，而union all 只是简单对前后<span style="color: black;">查找</span>结果进行合并，不会去重；</span></strong></span><span style="color: black;"><strong style="color: blue;"><span style="color: black;">不<span style="color: black;">举荐</span>在union 或union all语句中进行order by 排序操作。</span></strong></span>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;"><strong style="color: blue;"><span style="color: black;">02</span></strong></span></span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;"><strong style="color: blue;"><span style="color: black;">笛卡尔连接cross join</span></strong></span></span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">MySQL笛卡尔连接是MySQL中的一种连接方式，区别于内连接和外连接，</span><strong style="color: blue;"><span style="color: black;"><span style="color: black;">针对</span>cross join连接<span style="color: black;">来讲</span>，其实<span style="color: black;">运用</span>的<span style="color: black;">便是</span>笛卡尔连接</span></strong><span style="color: black;">。在MySQL中，当两个表<span style="color: black;">运用</span>了笛卡尔连接时，cross join会产生一个结果集，该结果集是两个<span style="color: black;">相关</span>表的行的乘积。</span><strong style="color: blue;"><span style="color: black;"><span style="color: black;">一般</span>，<span style="color: black;">倘若</span><span style="color: black;">每一个</span>表分别<span style="color: black;">拥有</span>n和m行，则结果集将<span style="color: black;">拥有</span>n*m行。</span></strong></span></p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/3e78b25dc5c743aa9b987bde0c8a3c4b~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723338665&amp;x-signature=%2F5fPBDfzRRdxdtToa5fl7B1zqXk%3D" style="width: 50%; margin-bottom: 20px;">
      <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">笛卡尔连接示意图</p>
    </div>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">例如，<span style="color: black;">咱们</span>已知phone表有2097006行记录，titanic表有891行记录，<span style="color: black;">那样</span>两者做笛卡尔连接的总记录数<span style="color: black;">便是</span>2097006x891=1868432346，其基本用法如下。（<span style="color: black;">因为</span>结果太大，<span style="color: black;">咱们</span>就不<span style="color: black;">实质</span>运行了）</span></span></p><span style="color: black;">SELECT</span> * <span style="color: black;">from</span> phone a
    <span style="color: black;">cross</span> <span style="color: black;">join</span> titanic b<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;"><strong style="color: blue;"><span style="color: black;">03</span></strong></span></span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;"><strong style="color: blue;"><span style="color: black;">总结</span></strong></span></span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">以上<span style="color: black;">便是</span><span style="color: black;">咱们</span>本节课要介绍的<span style="color: black;">所有</span>内容，总结一下：</span></span></p><span style="color: black;"><strong style="color: blue;"><span style="color: black;">在MySQL中不支持全连接的用法，<span style="color: black;">不外</span><span style="color: black;">咱们</span><span style="color: black;">能够</span><span style="color: black;">运用</span>union或union all 对前后结果进行合并。</span></strong></span><span style="color: black;"><strong style="color: blue;"><span style="color: black;">union会对合并结果进行去重，而union all 不会对合并结果进行去重，但union的计算量显然更大，运行效率<span style="color: black;">无</span>union all高。</span></strong></span><span style="color: black;"><strong style="color: blue;"><span style="color: black;">笛卡尔连接cross join是对两个表的所有行记录进行乘积，计算量巨大，<span style="color: black;">通常</span>在<span style="color: black;">实质</span>中不<span style="color: black;">举荐</span><span style="color: black;">运用</span>。</span></strong></span>




ljj 发表于 2024-9-10 11:38:36

交流如星光璀璨，点亮思想夜空。

4lqedz 发表于 2024-10-5 09:51:44

“BS”（鄙视的缩写）‌

nykek5i 发表于 2024-10-11 19:20:05

感谢你的精彩评论，带给我新的思考角度。

4zhvml8 发表于 2024-10-18 08:13:51

我深受你的启发，你的话语是我前进的动力。

4zhvml8 发表于 昨天 01:00

这篇文章真的让我受益匪浅，外链发布感谢分享！
页: [1]
查看完整版本: 关于MySQL内连接与外连接用法，全都在这儿了