论坛 › 软文营销 › Mysql中的相关查询(内连接，外连接，自连接)

lbk60ox 发表于 2024-8-4 15:11:26

Mysql中的相关查询(内连接，外连接，自连接)

<strong style="color: blue;">内连接<span style="color: black;">查找</span></strong><strong style="color: blue;">左外连接<span style="color: black;">查找</span></strong><strong style="color: blue;">右外连接<span style="color: black;">查找</span></strong><strong style="color: blue;">全外连接<span style="color: black;">查找</span></strong><strong style="color: blue;">自连接<span style="color: black;">查找</span></strong>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;">内连接<span style="color: black;">查找</span></h1>
    <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>所有<span style="color: black;">查找</span>出的结果都是能够在连接的表中有对应记录的。</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">以t_employee(员工表)和t_dept(<span style="color: black;">分部</span>表)为例：</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">t_employee表：</p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/795c5610f6ff46d4bc590dafbc9d478f~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723346802&amp;x-signature=KDo6nkDh1YptnPG8m16qYZeZaho%3D" style="width: 50%; margin-bottom: 20px;"></div>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">t_dept表：</p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p9-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/19b086eedd74485aba11f826d73812fe~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723346802&amp;x-signature=9gcIlENoA2LRLaldIqD5nAGptmE%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 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>，<span style="color: black;">此刻</span>想要<span style="color: black;">查找</span>出员工姓名以及其对应的<span style="color: black;">分部</span>名<span style="color: black;">叫作</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>内连接<span style="color: black;">查找</span>，关键字（inner join）e.</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>说一下<span style="color: black;">相关</span><span style="color: black;">查找</span>sql编写的思路：</span></p><span style="color: black;">1</span><span style="color: black;">，先确定所连接的表</span>
    <span style="color: black;">2</span><span style="color: black;">，再确定所要<span style="color: black;">查找</span>的字段</span>
    <span style="color: black;">3</span><span style="color: black;">，确定连接<span style="color: black;">要求</span>以及连接方式</span>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">查找</span>：</p><span style="color: black;">select</span>e.id,e.empName,d.deptName<span style="color: black;">from</span> t_employee e <span style="color: black;">inner</span> <span style="color: black;">join</span> t_dept <span style="color: black;">on</span> e.dept=d.id<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">查找</span>结果如下：</p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/d978d2b248714b768262de4b267423dd~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723346802&amp;x-signature=OM8nd4L0HB74QG03LAg6WSPMpgY%3D" style="width: 50%; margin-bottom: 20px;"></div>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;"><span style="color: black;">其中，<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>在连接的表中能够有对应的记录，其中e.dept = d.id是连接<span style="color: black;">要求</span>，如下集合图形所示：</span></span></strong></p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/e21dbaf487114c29946f2e18da93b9a5~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723346802&amp;x-signature=HWDFCpi9rcgN0ap2o%2BFbOTBxpYo%3D" style="width: 50%; margin-bottom: 20px;"></div>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;">左外连接<span style="color: black;">查找</span></h1>
    <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>匹配到就<span style="color: black;">表示</span>，匹配不到就<span style="color: black;">表示</span>为null。例如：</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>所有员工姓名以及他所在的<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>查出来，就要<span style="color: black;">运用</span>左外连接：</span></p><span style="color: black;">select</span> e.id,e.empName,d.deptName <span style="color: black;">from</span>employee e<span style="color: black;">left</span> <span style="color: black;">join</span> dept d <span style="color: black;">on</span> e.dept = d.id<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>，t_employee<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>记录对应的话就<span style="color: black;">表示</span>null</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">查找</span>结果：</p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/0f679184e6e6492b83cbf2c52d7cf402~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723346802&amp;x-signature=bkWdPHr5af4o0Rce4rm%2B%2BZdGg7U%3D" style="width: 50%; margin-bottom: 20px;"></div>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;"><span style="color: black;">关键字是left outer join，等效于left join，在<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></span></strong></p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/4d1c34ab62b546bab1370916f6118843~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723346802&amp;x-signature=%2BHeWafz77NkRxXSl5z4jB%2BszwJA%3D" style="width: 50%; margin-bottom: 20px;"></div>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">左外连接扩展：</strong></p>
    <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>员工<span style="color: black;">必要</span>要有<span style="color: black;">分部</span></p><span style="color: black;">select</span>
    e.id,e.empName,d.deptName
    <span style="color: black;">from</span>
    employee e
    <span style="color: black;">left</span> <span style="color: black;">join</span>
    dept d
    <span style="color: black;">on</span>e.dept = d.id<span style="color: black;">where</span>
    d.i <span style="color: black;">is</span> <span style="color: black;">not</span> <span style="color: black;">null</span>;<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">查找</span>结果：</p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/85efbe486754446db560770f7611f203~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723346802&amp;x-signature=OjkyCK3SfieAMSzUipiMS4PoBXU%3D" style="width: 50%; margin-bottom: 20px;"></div>
    <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/tos-cn-i-qvj2lq49k0/fdb31e07f0264d46a7d89a2377c266f8~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723346802&amp;x-signature=U1uEgy0IFYX5nBwZWdthIJbD0yw%3D" style="width: 50%; margin-bottom: 20px;"></div>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;">右外连接</h1>
    <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>，只需将left join 改为 right join<span style="color: black;">就可</span>，不做<span style="color: black;">太多</span>的赘述。</span></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/tos-cn-i-qvj2lq49k0/9b2f5ab60aa840b9b18f0642e246c0e1~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723346802&amp;x-signature=HqYL2Cx1DALrERfDtVA369WBeLs%3D" style="width: 50%; margin-bottom: 20px;"></div>
    <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/tos-cn-i-qvj2lq49k0/35a08f4da0434e1ebaaf15d43e93df68~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723346802&amp;x-signature=aenyG8GQf8xpjqMDUSLBWK3qdPQ%3D" style="width: 50%; margin-bottom: 20px;"></div>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;">全外连接</h1>
    <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>null，<span style="color: black;">然则</span><span style="color: black;">重视</span>：mysql是<span style="color: black;">无</span>全外连接的(mysql中<span style="color: black;">无</span>full outer join关键字)，想要达到全外连接的效果，<span style="color: black;">能够</span><span style="color: black;">运用</span>union关键字连接左外连接和右外连接。例如：</span></span></p><span style="color: black;">select</span>
    <span style="color: black;">e.id,e.empName,d.deptName</span>
    <span style="color: black;">from</span>
    <span style="color: black;">employee</span> <span style="color: black;">e </span>
    <span style="color: black;">left</span> <span style="color: black;">join </span>
    <span style="color: black;">dept</span> <span style="color: black;">d </span>
    <span style="color: black;">on</span>
    <span style="color: black;">e.dept</span> = <span style="color: black;">d.id </span>
    <span style="color: black;">union</span>
    <span style="color: black;">select</span>
    <span style="color: black;">e.id,e.empName,d.deptName</span>
    <span style="color: black;">from</span>
    <span style="color: black;">employee</span> <span style="color: black;">e </span>
    <span style="color: black;">right</span> <span style="color: black;">join </span>
    <span style="color: black;">dept</span> <span style="color: black;">d </span>
    <span style="color: black;">on</span>
    <span style="color: black;">e.dept</span> = <span style="color: black;">d.id ;</span>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">查找</span>结果：</p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/40f3824dba6b481ca34f534a53c4a74e~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723346802&amp;x-signature=dxcQc96ntauVeWawcTzXOwYhSrw%3D" style="width: 50%; margin-bottom: 20px;"></div>
    <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/tos-cn-i-qvj2lq49k0/f2ff2e73c6b649d5a6e14c2444957588~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723346802&amp;x-signature=7JQVYM%2FKnlkzn2sIkx0AX9maSOc%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 style="color: black;"><span style="color: black;">倘若</span>在oracle中，直接就<span style="color: black;">运用</span>full outer join关键字连接两表就行了</span></span></p>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;">自连接<span style="color: black;">查找</span> </h1>
    <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><span style="color: black;">便是</span>当前表与<span style="color: black;">自己</span>的连接<span style="color: black;">查找</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>员工以及他的上司的名<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></p><span style="color: black;">select</span>
    a.id <span style="color: black;">id</span>,a.empName empName,b.empName bossName
    <span style="color: black;">from</span>
    employee a
    <span style="color: black;">left</span> <span style="color: black;">join</span>
    employee b
    <span style="color: black;">on</span>b.bossId = a.id;<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">查找</span>结果：</p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/731d1f7732c34d9585730924a746ecb6~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723346802&amp;x-signature=WryuctSeooINo9BsLjB6koEuUBM%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 style="color: black;">自连接<span style="color: black;">查找</span><span style="color: black;">通常</span>用作表中的某个字段的值是引用另一个字段的值，<span style="color: black;">例如</span>权限表中，父权限<span style="color: black;">亦</span>属于权限</span></span><span style="color: black;">。</span></p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/c58abf87b5a247449347cf20b4e0fef3~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723346802&amp;x-signature=ayX7G9HDKoCKaadR4aojgm%2BEb34%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 style="color: black;"><span style="color: black;"><span style="color: black;">因为</span>工作关系，后期<span style="color: black;">文案</span>将以一天一小篇七天一大片模式<span style="color: black;">连续</span>更新</span></span></span></p>

j8typz 发表于 2024-10-24 20:14:11

论坛的成功是建立在我们诚恳、务实、高效、创新和团结合作基础上，我们要把这种精神传递下去。

查看完整版本: Mysql中的相关查询(内连接，外连接，自连接)