wloe2gf 发表于 2024-8-4 10:49:20

「每日一道面试题」Mysql内连接与外连接


    <h1 style="color: black; text-align: left; margin-bottom: 10px;">Mysql内连接与外连接</h1>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;">Mysql</span></strong><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></p>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;">Mysql内连接与外连接区别</h1>
    <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></strong><span style="color: black;">：指连接结果仅<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;"><strong style="color: blue;"><span style="color: black;">外连接</span></strong><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></p>
    <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></strong><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;"><strong style="color: blue;"><span style="color: black;">右外连接</span></strong><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;"><strong style="color: blue;"><span style="color: black;">全外连接</span></strong><span style="color: black;">：左外连接 union 右外连接，Mysql 中暂不支持。</span></p>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;">数据准备</h1>
    <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><span style="color: black;">首要</span>创建课程表，<span style="color: black;">详细</span>建表语句如下：</span></p><span style="color: black;"># 创建课程表</span>
    <span style="color: black;">CREATE</span> <span style="color: black;">TABLE</span>course(<span style="color: black;">id</span> <span style="color: black;">int</span>,
    <span style="color: black;">name</span> <span style="color: black;">varchar</span>(<span style="color: black;">40</span>),
    <span style="color: black;">url</span> <span style="color: black;">varchar</span>(<span style="color: black;">128</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>创建学生表，<span style="color: black;">详细</span>建表语句如下：</span></p><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;">name</span> <span style="color: black;">varchar</span>(<span style="color: black;">40</span>),
    course_id <span style="color: black;">int</span>
    );<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><span style="color: black;"># <span style="color: black;">插进</span>课程信息</span>
    <span style="color: black;">INSERT</span> <span style="color: black;">INTO</span> course(<span style="color: black;">id</span>, <span style="color: black;">name</span>, <span style="color: black;">url</span>)<span style="color: black;">values</span>(<span style="color: black;">1</span>, <span style="color: black;">"python"</span>, <span style="color: black;">"https://haicoder.net/python/python-tutorial.html"</span>);
    <span style="color: black;">INSERT</span> <span style="color: black;">INTO</span> course(<span style="color: black;">id</span>, <span style="color: black;">name</span>, <span style="color: black;">url</span>)<span style="color: black;">values</span>(<span style="color: black;">2</span>, <span style="color: black;">"golang"</span>, <span style="color: black;">"https://haicoder.net/golang/golang-tutorial.html"</span>);
    <span style="color: black;">INSERT</span> <span style="color: black;">INTO</span> course(<span style="color: black;">id</span>, <span style="color: black;">name</span>, <span style="color: black;">url</span>)<span style="color: black;">values</span>(<span style="color: black;">3</span>, <span style="color: black;">"java"</span>, <span style="color: black;">"https://haicoder.net/java/java-development.html"</span>);
    <span style="color: black;">INSERT</span> <span style="color: black;">INTO</span> course(<span style="color: black;">id</span>, <span style="color: black;">name</span>, <span style="color: black;">url</span>)<span style="color: black;">values</span>(<span style="color: black;">4</span>, <span style="color: black;">"javascript"</span>, <span style="color: black;">"https://haicoder.net/javascript/javascript-tutorial.html"</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>在学生表<span style="color: black;">插进</span>数据，<span style="color: black;">详细</span>语句如下：</span></p><span style="color: black;"># 插入学生信息</span>
    <span style="color: black;">INSERT</span> <span style="color: black;">INTO</span> student(<span style="color: black;">id</span>, <span style="color: black;">name</span>, course_id)<span style="color: black;">values</span>(<span style="color: black;">1</span>, <span style="color: black;">"jobs"</span>, <span style="color: black;">1</span>);
    <span style="color: black;">INSERT</span> <span style="color: black;">INTO</span> student(<span style="color: black;">id</span>, <span style="color: black;">name</span>, course_id)<span style="color: black;">values</span>(<span style="color: black;">2</span>, <span style="color: black;">"gates"</span>, <span style="color: black;">3</span>);
    <span style="color: black;">INSERT</span> <span style="color: black;">INTO</span> student(<span style="color: black;">id</span>, <span style="color: black;">name</span>, course_id)<span style="color: black;">values</span>(<span style="color: black;">3</span>, <span style="color: black;">"jack"</span>, <span style="color: black;">5</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><span style="color: black;">运用</span> SELECT 语句，查看创建好的表的数据，<span style="color: black;">首要</span>，查看课程表的数据，<span style="color: black;">详细</span>语句如下：</span></p><span style="color: black;">SELECT</span> * <span style="color: black;">FROM</span> course;<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/pgc-image/d1d3418add8b49a6b5b1794ffce16e30~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723339877&amp;x-signature=UjGlnUiXuJMD406MB25PCZ8X%2FCI%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></p><span style="color: black;">SELECT</span> * <span style="color: black;">FROM</span> student;<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/pgc-image/4d8b253f1ae743388aac0b988c921ded~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723339877&amp;x-signature=hcNABh6gZxm8mLMG8ZQVSRu3NrM%3D" style="width: 50%; margin-bottom: 20px;"></div>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;">内连接</h1>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;">语法</h1><span style="color: black;">INNER</span> JOIN <span style="color: black;">ON</span>
    <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>字段相符的记录，<span style="color: black;">亦</span><span style="color: black;">便是</span>返回两个表的交集（<span style="color: black;">暗影</span>）部分，如下图所示：</span></p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/9cdb83e02f3b4c7ba672670e8f17e27d~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723339877&amp;x-signature=s3skN8JpK4Rhid52PydPdyac%2BLk%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><span style="color: black;">运用</span>内连接<span style="color: black;">查找</span>，执行如下语句：</span></p><span style="color: black;">SELECT</span> * <span style="color: black;">FROM</span> student <span style="color: black;">as</span> stu <span style="color: black;">inner</span> <span style="color: black;">join</span> course <span style="color: black;">as</span> cou <span style="color: black;">on</span>stu.course_id=cou.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/pgc-image/45ef810bba954ff1a603236a1ffef92f~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723339877&amp;x-signature=LXwktjLb46WSXut0kf0iGtixG5s%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>出了两个表都有的数据，即，student 表里面有一条 id 为 3 的记录，其 course_id 为 5 在 course 表里面<span style="color: black;">无</span> id 为 5 的数据，<span style="color: black;">因此呢</span>，该条记录未被<span style="color: black;">查找</span>出来，<span style="color: black;">同期</span>，在 course 表里面有条 id 为 4 的记录，但在 student 表里面<span style="color: black;">无</span> course_id 为 4 的记录，<span style="color: black;">因此</span> course 表里面的该条记录未被<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 关键字，<span style="color: black;">详细</span>语法如下：</span></p><span style="color: black;">SELECT</span>stu.id stu_id, stu.name stu_name, cou.id cou_id, cou.name cou_name, cou.url cou_url<span style="color: black;">FROM</span> student stu, course cou <span style="color: black;">WHERE</span> stu.course_id=cou.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/pgc-image/69e7b1b3323341fa8bcdece81d077e6f~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723339877&amp;x-signature=4987TXiooj9HfcsvCGpXQ6WQYEw%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></p>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;">左外连接</h1>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;">语法</h1><span style="color: black;">LEFT</span> JOIN <span style="color: black;">ON</span>
    <span style="color: black;">LEFT</span> OUTER JOIN <span style="color: black;">ON</span>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">left join 是 left outer join 的简写，它的全<span style="color: black;">叫作</span>是左外连接，是外连接中的一种。</span></p>
    <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><span style="color: black;">暗示</span>出来，而右表只会<span style="color: black;">表示</span>符合搜索<span style="color: black;">要求</span>的记录。右表记录不足的<span style="color: black;">地区</span>均为 NULL，如下图所示：</span></p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/dcbd7d1ead324a8c81300941009bab53~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723339877&amp;x-signature=H7tLzd5Ua1GmqRKyM2RiANsMcCQ%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><span style="color: black;">运用</span>内连接<span style="color: black;">查找</span>，执行如下语句：</span></p><span style="color: black;">SELECT</span> * <span style="color: black;">FROM</span> student <span style="color: black;">as</span> stu <span style="color: black;">LEFT</span> <span style="color: black;">OUTER</span> <span style="color: black;">JOIN</span> course <span style="color: black;">as</span> cou <span style="color: black;">on</span> stu.course_id=cou.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://p26-sign.toutiaoimg.com/pgc-image/21f2c3c483824e838fc35986a1a15090~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723339877&amp;x-signature=xRBRImh%2FRoCi1Tjp9Y5sHVbdbrI%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>看到，左外连接 student 表里面 id 为 3 的记录，其 course_id 为 5，但 course_id 为 5 的记录在 course 表里面是不存在的，此时，<span style="color: black;">咱们</span>用的是左外连接，<span style="color: black;">因此呢</span>，<span style="color: black;">能够</span>查出该条记录，但 course 表里面的字段的值都是 NULL。</span></p>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;">右外连接</h1>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;">语法</h1><span style="color: black;">RIGHT</span> JOIN <span style="color: black;">ON</span>
    <span style="color: black;">RIGHT</span> OUTER JOIN <span style="color: black;">ON</span>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">right join 是 right outer join 的简写，它的全<span style="color: black;">叫作</span>是右外连接，是外连接中的一种。</span></p>
    <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><span style="color: black;">暗示</span>出来，而左表只会<span style="color: black;">表示</span>符合搜索<span style="color: black;">要求</span>的记录。左表记录不足的<span style="color: black;">地区</span>均为 NULL，如下图所示：</span></p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/4d307b1acb3a4c399f3a57c827cec94a~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723339877&amp;x-signature=14cB9cnex1xzS16I3OdBUBedDK8%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><span style="color: black;">运用</span>内连接<span style="color: black;">查找</span>，执行如下语句：</span></p><span style="color: black;">SELECT</span> * <span style="color: black;">FROM</span> student <span style="color: black;">as</span> stu <span style="color: black;">RIGHT</span> <span style="color: black;">OUTER</span> <span style="color: black;">JOIN</span> course <span style="color: black;">as</span> cou <span style="color: black;">on</span>stu.course_id=cou.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/pgc-image/bb708ffd65b544798cb6e6756b0df31d~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723339877&amp;x-signature=JFUM62nS8zQonk7ZEBsPcXd2HH8%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>看到，右外连接 course 表里面 id 为 2 和 4 的记录，虽然在 student 表里面<span style="color: black;">无</span> course_id 字段与其对应，但<span style="color: black;">亦</span>被查出来了，其 student 表里面的字段都为 NULL。</span></p>




页: [1]
查看完整版本: 「每日一道面试题」Mysql内连接与外连接