ikkhksvu 发表于 2024-8-4 09:48:53

mysql 外连接总结


    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">外连接和内连接的区别：</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">1、内连接：假设A表和B表进行连接，<span style="color: black;">运用</span>内连接的话，凡是A表和B表能够匹配上的记录<span style="color: black;">查找</span>出来，这<span style="color: black;">便是</span>内连接。AB两张表<span style="color: black;">无</span>主副之分，两张表是平等的。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">2、外连接：假设A表和B表进行连接，<span style="color: black;">运用</span>外连接的话，AB两张表中有一张是主表，一张是副表，<span style="color: black;">重点</span><span style="color: black;">查找</span>主表中的数据，捎带着<span style="color: black;">查找</span>副表中的数据，当副表中的数据<span style="color: black;">无</span>和主表中的数据匹配上，副表自动模拟出null与之匹配。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">3、内连接会<span style="color: black;">显现</span>数据丢失的<span style="color: black;">状况</span>，内连接只展示能够匹配上的数据，匹配不上的数据就不是<span style="color: black;">表示</span>。外连接中主表的数据<span style="color: black;">必定</span>会展示的，<span style="color: black;">倘若</span>主表中的一条数据副表中<span style="color: black;">无</span>任何数据<span style="color: black;">能够</span>与之匹配，<span style="color: black;">那样</span>副表自动模拟null值与主表中的那条数据进行匹配。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">现有如下一张员工表数据：</p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/6ec55078459242b48d53033bff391c5c~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723338645&amp;x-signature=eaGyWb1BvY%2BBmwTDS%2BKc591V5LE%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;">1）<span style="color: black;">查找</span><span style="color: black;">每一个</span>员工的上级领导名字：MGR：上级领导编号</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">分析：KING的职位是最高的，<span style="color: black;">因此</span>他的上级领导mgr的值为null。<span style="color: black;">无</span>与之匹配的，<span style="color: black;">因此</span><span style="color: black;">咱们</span><span style="color: black;">运用</span>内连接的话，king这条记录应该是<span style="color: black;">无</span>的。</p><span style="color: black;">select</span>&nbsp;
    a.ename , b.ename
    <span style="color: black;">from</span>
    emp a
    <span style="color: black;">join</span>emp b<span style="color: black;">on</span>
    a.mgr = b.empno;<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/ca065185183c4a41a472e14433261a74~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723338645&amp;x-signature=feozbyM1ho3Ieyr%2BKEgtAoNkDOE%3D" style="width: 50%; margin-bottom: 20px;">
      <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">内连接<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>结果<span style="color: black;">咱们</span><span style="color: black;">发掘</span>再内连接<span style="color: black;">查找</span>员工领导信息的时候，总共<span style="color: black;">仅有</span>13条记录，而员工信息一共有14条，将领导信息为king的员工自动忽略了。这个时候<span style="color: black;">咱们</span><span style="color: black;">能够</span><span style="color: black;">经过</span>外连接再来<span style="color: black;">查找</span>员工的领导信息。</p><span style="color: black;">select</span>&nbsp;
    a.ename , b.ename
    <span style="color: black;">from</span>emp a<span style="color: black;">left</span> <span style="color: black;">join</span>
    emp b
    <span style="color: black;">on</span>
    a.mgr = b.empno;<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/b3037955b37e4d37b2e9011ee60ad711~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723338645&amp;x-signature=oBpJrIsKa46VeHXMypyQNQFNfiM%3D" style="width: 50%; margin-bottom: 20px;">
      <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">左连接<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>在看一个案列，<span style="color: black;">咱们</span><span style="color: black;">增多</span>一张表 <span style="color: black;">分部</span>表：</p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/c6bb494cb69548b9920d8f9465e838f7~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723338645&amp;x-signature=KOF3bL0hA%2Bi2cMICSzMSDfIzTLM%3D" style="width: 50%; margin-bottom: 20px;">
      <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">分部</span>表</p>
    </div>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">1）<span style="color: black;">查找</span>哪个<span style="color: black;">分部</span><span style="color: black;">无</span>员工</p><span style="color: black;">select</span>
    d.* , e.ename
    <span style="color: black;">from</span>
    dept d
    <span style="color: black;">left</span> <span style="color: black;">join</span>
    emp e
    <span style="color: black;">on</span>
    d.deptno = e.deptno;<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p26-sign.toutiaoimg.com/pgc-image/1e06ac31fa24453f912e5126bafdda8b~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723338645&amp;x-signature=WUtQ%2BtVTxDSFfCFdczGBxzeFZ0o%3D" style="width: 50%; margin-bottom: 20px;"></div>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">经过</span>结果<span style="color: black;">咱们</span><span style="color: black;">发掘</span>，<span style="color: black;">仅有</span><span style="color: black;">分部</span>编号为40的<span style="color: black;">分部</span>员工信息为null，<span style="color: black;">因此</span><span style="color: black;">咱们</span>只要稍微修改下sql，将<span style="color: black;">最后</span>展示的信息为<span style="color: black;">分部</span>40的就行了。</p><span style="color: black;">select</span>
    d.*
    <span style="color: black;">from</span>
    dept d
    <span style="color: black;">left</span> <span style="color: black;">join</span>
    emp e
    <span style="color: black;">on</span>
    d.deptno = e.deptno
    <span style="color: black;">where</span>
    e.ename <span style="color: black;">is</span> <span style="color: black;">null</span>;<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/cb7063c0149a40b8b6ae716bef33bc82~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723338645&amp;x-signature=Em0%2BSVddotXmmAK%2Fr6WzARjb%2Fv8%3D" style="width: 50%; margin-bottom: 20px;">
      <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">查找</span><span style="color: black;">无</span>员工的<span style="color: black;">分部</span>信息</p>
    </div>




1fy07h 发表于 2024-10-3 18:20:45

谢谢、感谢、感恩、辛苦了、有你真好等。

nykek5i 发表于 2024-10-13 04:40:31

我完全同意你的看法，期待我们能深入探讨这个问题。

m5k1umn 发表于 2024-11-5 18:16:25

你的话语如春风拂面，温暖了我的心房，真的很感谢。

7wu1wm0 发表于 2024-11-9 05:10:18

这篇文章真的让我受益匪浅，外链发布感谢分享！

b1gc8v 发表于 2024-11-9 23:58:35

我赞同你的看法，你的智慧让人佩服，谢谢分享。

1fy07h 发表于 2024-11-10 13:47:11

回顾过去一年，是艰难的一年；展望未来，是辉煌的一年。

nykek5i 发表于 昨天 16:04

你的努力一定会被看见，相信自己，加油。
页: [1]
查看完整版本: mysql 外连接总结