qzmjef 发表于 2024-8-4 13:40:38

mysql子查询和连表


    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">Nodejs第三十七章(子<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>，今天来学习Nodejs的第37章子<span style="color: black;">查找</span>和联表。先学子<span style="color: black;">查找</span>，<span style="color: black;">亦</span><span style="color: black;">便是</span>subquery。在之前是在一张表里边进行增删改差的，在真正的工作中是不会把所有的字段都定义在一张表里边，<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;"><span style="color: black;">因此</span>为了性能是会进行分表的，<span style="color: black;">乃至</span>还会进行分库、分表和读写分离。今天演示<span style="color: black;">便是</span>分成两个表里边，来看一下。user表里边一共有4个值，还有table表里边有3个值。</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>name去查到table表里边所定义的数据。table表里面<span style="color: black;">好似</span>并<span style="color: black;">无</span>小满数据，但能跟name对应吗？肯定是<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>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-axegupay5k/950f9ddde08045b3ae9397a77024dbb6~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723344414&amp;x-signature=LbuORJ53zLQIbelVq7F7kepb%2BtM%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>两个表<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>userID，<span style="color: black;">便是</span>table表里边的userID去对应的user表里边的ID，<span style="color: black;">亦</span><span style="color: black;">便是</span><span style="color: black;">经过</span>小满去查到ID，ID为一。<span style="color: black;">经过</span>一再去table表里边查user_id，不就把这条数据就查出来吗？这<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>碰见这种需求就<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>select*from肯定要查，查哪个表？查table表。<span style="color: black;">要求</span>是什么？<span style="color: black;">要求</span><span style="color: black;">便是</span>userID，<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>会包语法错误。</p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-dtbei8xehn/80f6d7f299574ab4862e8da368eebf15~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723344414&amp;x-signature=4lBF0QdE0P702Kyn77PmQ0t6rWY%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>去查，select ID，<span style="color: black;">源自</span>于哪？<span style="color: black;">源自</span>user表。<span style="color: black;">要求</span>是什么？<span style="color: black;">要求</span>就要<span style="color: black;">经过</span>name去查，查小满，<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;">·来看一下结果，执行，是不是<span style="color: black;">经过</span>名字把table标准数据去查出来？其实这条语句执行出来<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>ID，<span style="color: black;">而后</span>为一。<span style="color: black;">能够</span>来看一下，字形ID为一，把这个值再去交给他，不就查出来吗？</p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-dtbei8xehn/1289f0c77e67469f844fbcd3c49a7f8d~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723344414&amp;x-signature=89h26W6gOomUH0QoiiBLspL92Cw%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>了，这个是<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;"><span style="color: black;">然则</span>在工作中可能还会有这种需求，<span style="color: black;">便是</span>查出来的值想把user表跟table表只融合到<span style="color: black;">一块</span>，<span style="color: black;">此刻</span>只是查出table表的数据，<span style="color: black;">然则</span>想把table表跟user表的这几条<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;"><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;">先演示一下什么叫内连接，目的是什么？目的<span style="color: black;">便是</span>把table表的数据和user表的数据合并到<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/tos-cn-i-dtbei8xehn/dc86117b23e04ea2b2bc397678d330c3~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723344414&amp;x-signature=6bB9uxqjJgAS4OwdNEF6uF59NkI%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>select*from，<span style="color: black;">便是</span><span style="color: black;">一起</span>查两个表，其实<span style="color: black;">亦</span>很简单，<span style="color: black;">便是</span>查哪个表，查user表，再查一个table表，就<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>user表的ID去<span style="color: black;">相关</span>table表的user ID，user ID，就<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;">来查一下，<span style="color: black;">发掘</span>是不是查出来了，数据是不是放到<span style="color: black;">一块</span>的？<span style="color: black;">重视</span>看，数据是不是合并到<span style="color: black;">一块</span>的？Gole Codm，小满的数据，还有小满，它们把数据都合并到<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/tos-cn-i-dtbei8xehn/1498447c477a45188a5bc1516b88a7eb~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723344414&amp;x-signature=b1otQ24jkWsX8fBPivyLkjuIaIo%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>有个新的问题，点击user，明明是四条数据，查出来把第四条数据丢掉了，这是<span style="color: black;">为何</span>？<span style="color: black;">由于</span>table表里面<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>
    <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>table表<span style="color: black;">无</span>数据，补个null不就<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;">外连接这个语句该怎么写？外连接先演示左连接，左连接什么？<span style="color: black;">便是</span>left，跟表名表面后面跟什么？跟on，on后面跟的是什么？连接的<span style="color: black;">要求</span>，<span style="color: black;">因此</span>这个语句<span style="color: black;">亦</span>很简单，来看一下，select，<span style="color: black;">而后</span>leftjoin，<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/tos-cn-i-dtbei8xehn/65d0c33dbacd480a9625ce2e7bcff8f3~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723344414&amp;x-signature=cA%2BAl2Unj2juX%2B2AL8ICfoBNX9w%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>是填充null，<span style="color: black;">由于</span>是左边，肯定<span style="color: black;">便是</span>左边的表，<span style="color: black;">亦</span><span style="color: black;">便是</span>user表，它<span style="color: black;">便是</span>驱动表。table就说table就说被驱动表，它<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>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-dtbei8xehn/a4aa655bfd8d4da98a07188eb9dac40b~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723344414&amp;x-signature=i0OGbdTe%2Bvl40nEwJ0QlF4hNK4k%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>此时table表的它就<span style="color: black;">做为</span>驱动表了，user的<span style="color: black;">便是</span><span style="color: black;">做为</span>被驱动表了。查出来结果是什么？肯定是三条，<span style="color: black;">由于</span>table表里边不就三条数据，它又<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><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跟right，只要灵活的变通。</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>




星☆雨 发表于 2024-9-5 07:03:30

你的话深深触动了我，仿佛说出了我心里的声音。
页: [1]
查看完整版本: mysql子查询和连表