论坛 › 北京 › 28.MySQL中的左(右)外连接 LEFT(RIGHT) [OUTER] JOIN

6257rv7 发表于 2024-8-4 10:53:48

28.MySQL中的左(右)外连接 LEFT(RIGHT) [OUTER] JOIN

    <h3 style="color: black; text-align: left; margin-bottom: 10px;"><strong style="color: blue;">1.准备</strong></h3><span style="color: black;">CREATE</span> <span style="color: black;">DATABASE</span> mahaiwuji;
    <span style="color: black;">USE</span> mahaiwuji;
    <span style="color: black;">CREATE</span> <span style="color: black;">TABLE</span> grade(
    <span style="color: black;">id</span> <span style="color: black;">INT</span>(<span style="color: black;">4</span>) PRIMARY <span style="color: black;">KEY</span>,
    <span style="color: black;">name</span> <span style="color: black;">VARCHAR</span>(<span style="color: black;">36</span>)
    ) <span style="color: black;">ENGINE</span> = <span style="color: black;">INNODB</span> <span style="color: black;">DEFAULT</span> <span style="color: black;">CHARSET</span> = utf8;
    <span style="color: black;">INSERT</span> <span style="color: black;">INTO</span> grade <span style="color: black;">VALUES</span> (<span style="color: black;">1</span>,<span style="color: black;">一班</span>);
    <span style="color: black;">INSERT</span> <span style="color: black;">INTO</span> grade <span style="color: black;">VALUES</span> (<span style="color: black;">2</span>,<span style="color: black;">二班</span>);
    <span style="color: black;">INSERT</span> <span style="color: black;">INTO</span> grade <span style="color: black;">VALUES</span> (<span style="color: black;">3</span>,<span style="color: black;">三班</span>);
    <span style="color: black;">INSERT</span> <span style="color: black;">INTO</span> grade <span style="color: black;">VALUES</span> (<span style="color: black;">4</span>,<span style="color: black;">四班</span>);
    <span style="color: black;">CREATE</span> <span style="color: black;">TABLE</span> student(
    <span style="color: black;">id</span> <span style="color: black;">INT</span>(<span style="color: black;">4</span>) PRIMARY <span style="color: black;">KEY</span>,
    <span style="color: black;">name</span> <span style="color: black;">VARCHAR</span>(<span style="color: black;">36</span>),
    gid<span style="color: black;">INT</span>(<span style="color: black;">4</span>)
    ) <span style="color: black;">ENGINE</span> = <span style="color: black;">INNODB</span> <span style="color: black;">DEFAULT</span> <span style="color: black;">CHARSET</span> = utf8;
    <span style="color: black;">INSERT</span> <span style="color: black;">INTO</span> student <span style="color: black;">VALUES</span> (<span style="color: black;">1</span>,<span style="color: black;">a1</span>,<span style="color: black;">1</span>);
    <span style="color: black;">INSERT</span> <span style="color: black;">INTO</span> student <span style="color: black;">VALUES</span> (<span style="color: black;">2</span>,<span style="color: black;">a2</span>,<span style="color: black;">1</span>);
    <span style="color: black;">INSERT</span> <span style="color: black;">INTO</span> student <span style="color: black;">VALUES</span> (<span style="color: black;">3</span>,<span style="color: black;">a3</span>,<span style="color: black;">2</span>);<span style="color: black;">INSERT</span> <span style="color: black;">INTO</span> student <span style="color: black;">VALUES</span> (<span style="color: black;">4</span>,<span style="color: black;">a4</span>,<span style="color: black;">2</span>);
    <span style="color: black;">INSERT</span> <span style="color: black;">INTO</span> student <span style="color: black;">VALUES</span> (<span style="color: black;">5</span>,<span style="color: black;">a5</span>,<span style="color: black;">3</span>);
    <span style="color: black;">INSERT</span> <span style="color: black;">INTO</span> student <span style="color: black;">VALUES</span> (<span style="color: black;">6</span>,<span style="color: black;">a6</span>,<span style="color: black;">3</span>);
    <span style="color: black;">INSERT</span> <span style="color: black;">INTO</span> student <span style="color: black;">VALUES</span> (<span style="color: black;">7</span>,<span style="color: black;">a7</span>,<span style="color: black;">3</span>);
    <span style="color: black;">INSERT</span> <span style="color: black;">INTO</span> student <span style="color: black;">VALUES</span> (<span style="color: black;">8</span>,<span style="color: black;">a8</span>,<span style="color: black;">3</span>);
    <span style="color: black;">INSERT</span> <span style="color: black;">INTO</span> student <span style="color: black;">VALUES</span> (<span style="color: black;">9</span>,<span style="color: black;">a9</span>,<span style="color: black;">5</span>);<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/c6ecbf76e80a4538a3a3e4a15f0c3029~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723340035&amp;x-signature=9vsR%2Fv5jCM8RRwsqCT7iWkM4nq0%3D" style="width: 50%; margin-bottom: 20px;"></div>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/a08cb08e5e4d4840bdd3cc4d9d4ee65a~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723340035&amp;x-signature=8%2BiMY68KBo1rJVrlr91lm8ZLTG4%3D" style="width: 50%; margin-bottom: 20px;"></div>
    <h3 style="color: black; text-align: left; margin-bottom: 10px;"><strong style="color: blue;">2.左外连接</strong></h3>
    <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>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">它用于返回连接关键字（LEFT JOIN）左表中所有的记录，以及右表中符合连接<span style="color: black;">要求</span>的记录。</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>的记录将设为空值。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">语法</p><span style="color: black;">SELECT</span> <span style="color: black;">查找</span>字段 <span style="color: black;">FROM</span> 表<span style="color: black;">1</span> <span style="color: black;">LEFT</span> [<span style="color: black;">OUTER</span>] <span style="color: black;">JOIN</span> 表<span style="color: black;">2</span> <span style="color: black;">ON</span> 匹配<span style="color: black;">要求</span>;<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">关键字“LEFT JOIN”左边的表（表1）被<span style="color: black;">叫作</span>为<strong style="color: blue;">左表</strong>，<span style="color: black;">亦</span>可<span style="color: black;">叫作</span>为<strong style="color: blue;">主表</strong>。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">关键字右边的表（表2）被<span style="color: black;">叫作</span>为<strong style="color: blue;">右表</strong>，<span style="color: black;">亦</span>可<span style="color: black;">叫作</span>为<strong style="color: blue;">从表</strong>。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">OUTER</strong>在<span style="color: black;">查找</span>时<span style="color: black;">能够</span>省略。</p><span style="color: black;">SELECT</span> * <span style="color: black;">FROM</span> grade <span style="color: black;">LEFT</span> <span style="color: black;">JOIN</span> student <span style="color: black;">ON</span> grade.id=student.gid;<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/42b5f3a3bbae432182e9a271f4106f12~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723340035&amp;x-signature=MbDwPyS90osdLdwFFXRzJ74M%2FMU%3D" style="width: 50%; margin-bottom: 20px;"></div>
    <h3 style="color: black; text-align: left; margin-bottom: 10px;"><strong style="color: blue;">3.右外连接</strong></h3>
    <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>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">它用于返回连接关键字（RIGHT JOIN）右表（主表）中所有的记录，以及左表（从表）中符合连接<span style="color: black;">要求</span>的记录。</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>的记录将设为空值。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">语法</p><span style="color: black;">SELECT</span> <span style="color: black;">查找</span>字段 <span style="color: black;">FROM</span> 表<span style="color: black;">1</span> <span style="color: black;">RIGHT</span> [<span style="color: black;">OUTER</span>] <span style="color: black;">JOIN</span> 表<span style="color: black;">2</span> <span style="color: black;">ON</span> 匹配<span style="color: black;">要求</span>;<span style="color: black;">SELECT</span> * <span style="color: black;">FROM</span> grade <span style="color: black;">RIGHT</span> <span style="color: black;">JOIN</span> student <span style="color: black;">ON</span> grade.id=student.gid;<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/bcccd81275614287b7f5f71dd7d0f283~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723340035&amp;x-signature=L0mqnbGba%2BJI%2Bmr99ngb0xepALw%3D" style="width: 50%; margin-bottom: 20px;"></div>
    <h3 style="color: black; text-align: left; margin-bottom: 10px;"><strong style="color: blue;">4.总结</strong></h3>外连接是最常用的一种<span style="color: black;">查找</span>数据的方式，分为左外连接（LEFT JOIN）和右外连接（RIGHT 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 style="color: black;">不可</span>匹配的记录。右连接<span style="color: black;">查找</span>正好与左连接相反。<span style="color: black;">因此呢</span>，在应用外连接时仅<span style="color: black;">调节</span>关键字（LEFT JOIN或RIGHT JOIN）和主从表的位置，<span style="color: black;">就可</span>实现左连接和右连接的互换<span style="color: black;">运用</span>。

b1gc8v 发表于 2024-9-30 16:49:37

这夸赞甜到心里，让我感觉温暖无比。

b1gc8v 发表于 2024-10-1 22:41:51

网站建设seio论坛http://www.fok120.com/

7wu1wm0 发表于 2024-10-20 04:15:44

你的留言真是温暖如春，让我感受到了无尽的支持与鼓励。

查看完整版本: 28.MySQL中的左(右)外连接 LEFT(RIGHT) [OUTER] JOIN