论坛 › 辽宁 › SQL - 联表查询(Join)内联接,外联接,交叉联接 161

219mze 发表于 2024-8-4 15:13:33

SQL - 联表查询(Join)内联接,外联接,交叉联接 161

    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><a style="color: black;">#头条创作挑战赛#</a></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><strong style="color: blue;"><span style="color: black;">join</span></strong><span style="color: black;">关键字将多个表基于某些<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;">内联结(Inner join)仅返回两个联接表中都<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;">示例1:<span style="color: black;">查找</span>所有学生的姓名,学生班级编号,班级编号与班级名<span style="color: black;">叫作</span></span></p><span style="color: black;">--前两项在student表中,班级名<span style="color: black;">叫作</span>在class表中</span>
    <span style="color: black;">--内联接后要<span style="color: black;">表示</span>的数据基于两表的主外键都对应的数据才会<span style="color: black;">表示</span></span>
    <span style="color: black;">--简单<span style="color: black;">来讲</span>只会<span style="color: black;">查找</span>出两表中学生班级编号等于班级编号的学生数据</span>
    <span style="color: black;">--<span style="color: black;">倘若</span>学生表中的 班级编号=66 而班级表中 <span style="color: black;">无</span> 班级编号=66 </span>
    <span style="color: black;">--就不会<span style="color: black;">表示</span>班级编号=66 的学生数据</span>
    <span style="color: black;">SELECT</span>
    stu.SName N<span style="color: black;">学生姓名</span>,
    stu.SCId N<span style="color: black;">学生比较编号</span>,
    cls.CId N<span style="color: black;">班级编号</span>,
    cls.CName N<span style="color: black;">班级名<span style="color: black;">叫作</span></span>
    <span style="color: black;">FROM</span> dbo.Student <span style="color: black;">AS</span> stu <span style="color: black;">--学生表</span>
    <span style="color: black;">INNER</span> <span style="color: black;">JOIN</span> dbo.Class <span style="color: black;">AS</span> cls <span style="color: black;">--以内联接的方式,连接班级表</span>
    <span style="color: black;">ON</span> Cls.CId = stu.SCId <span style="color: black;">--相当于where<span style="color: black;">要求</span>如where SCId in(所有的班级编号)</span>

    <span style="color: black;">--ON Cls.CId = stu.SCId AND stu.SId&lt;6 </span>
    两个<span style="color: black;">要求</span>:不仅主外键一致还要学生编号&lt;<span style="color: black;">6</span>的学生数据

    表<span style="color: black;">1</span> <span style="color: black;">inner</span> <span style="color: black;">join</span> 表<span style="color: black;">2</span> <span style="color: black;">on</span><span style="color: black;">要求</span> 固定格式

    两表中的别名:可省略
    列名前缀(stu/cls):<span style="color: black;">倘若</span>两表中的列名<span style="color: black;">无</span><span style="color: black;">同样</span>的可省略
    <span style="color: black;">倘若</span>两表中有相同的列名(假设都是 SName)<span style="color: black;">必须</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>下在效率上Where可能<span style="color: black;">拥有</span>和Inner join<span style="color: black;">同样</span>的效率,<span style="color: black;">然则</span>在多表连接时,不<span style="color: black;">举荐</span><span style="color: black;">运用</span>where语句</span></p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/de7dd1b3a38d4f83ab4b2bcb396f2977~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723346820&amp;x-signature=yZD89dubiHZAvY3UolpAnPyM7V8%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>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;">外联结(Outer join)</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>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"> 外联接共有三种类型：左联接(left join)右联接(right join)和完全联接(full join)</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;">1 左联接(left join)</span></strong><span style="color: black;">返回左表(即from语句中的表)中的所有行,<span style="color: black;">同期</span>返回右表(left join语句中的表)中匹配的行,<span style="color: black;">倘若</span>右表中<span style="color: black;">无</span>匹配的行,则返回 NULL 值</span></p><span style="color: black;">--创建示例表 成绩表</span>
    <span style="color: black;">CREATE</span> <span style="color: black;">TABLE</span> Score
    (
    <span style="color: black;">SId</span> <span style="color: black;">INT</span> <span style="color: black;">IDENTITY</span>(<span style="color: black;">1</span>,<span style="color: black;">1</span>) PRIMARY <span style="color: black;">KEY</span>,<span style="color: black;">--自动编号</span>
    SSId <span style="color: black;">INT</span>,<span style="color: black;">--学生编号</span>
    SChinese <span style="color: black;">INT</span>,<span style="color: black;">--语文</span>
    SMath <span style="color: black;">INT</span>,<span style="color: black;">--数学</span>
    SHistory <span style="color: black;">INT</span>,<span style="color: black;">--历史</span>
    SCredit <span style="color: black;">INT</span> <span style="color: black;">--学分</span>)<span style="color: black;">--<span style="color: black;">插进</span>数据</span>
    <span style="color: black;">INSERT</span> <span style="color: black;">INTO</span> dbo.Score(SSId,SChinese,SMath,SHistory,SCredit)
    <span style="color: black;">SELECT</span> <span style="color: black;">1</span>,<span style="color: black;">90</span>,<span style="color: black;">88</span>,<span style="color: black;">95</span>,<span style="color: black;">5</span> <span style="color: black;">UNION</span>
    <span style="color: black;">SELECT</span> <span style="color: black;">2</span>,<span style="color: black;">70</span>,<span style="color: black;">58</span>,<span style="color: black;">55</span>,<span style="color: black;">4</span> <span style="color: black;">UNION</span>
    <span style="color: black;">SELECT</span> <span style="color: black;">3</span>,<span style="color: black;">30</span>,<span style="color: black;">81</span>,<span style="color: black;">36</span>,<span style="color: black;">3</span> <span style="color: black;">UNION</span>
    <span style="color: black;">SELECT</span> <span style="color: black;">5</span>,<span style="color: black;">90</span>,<span style="color: black;">79</span>,<span style="color: black;">67</span>,<span style="color: black;">3</span> <span style="color: black;">UNION</span>
    <span style="color: black;">SELECT</span> <span style="color: black;">7</span>,<span style="color: black;">60</span>,<span style="color: black;">28</span>,<span style="color: black;">75</span>,<span style="color: black;">1</span> <span style="color: black;">UNION</span>
    <span style="color: black;">SELECT</span> <span style="color: black;">8</span>,<span style="color: black;">53</span>,<span style="color: black;">58</span>,<span style="color: black;">85</span>,<span style="color: black;">2</span> <span style="color: black;">UNION</span>
    <span style="color: black;">SELECT</span> <span style="color: black;">9</span>,<span style="color: black;">50</span>,<span style="color: black;">87</span>,<span style="color: black;">29</span>,<span style="color: black;">5</span>

    <span style="color: black;">--以学生表为主的左联接</span>
    <span style="color: black;">--<span style="color: black;">表示</span>的<span style="color: black;">最后</span>行数 以学生表中的行数为准</span>
    <span style="color: black;">--假设符合<span style="color: black;">要求</span>的 学生表行数为 20 就会<span style="color: black;">表示</span>20条数据</span>
    <span style="color: black;">--简单<span style="color: black;">来讲</span>:<span style="color: black;">首要</span>筛选出学生表中符合<span style="color: black;">要求</span>的数据</span>
    <span style="color: black;">--成绩表中的数据去匹配学生表中的数据即stu.SId=sco.SSId</span>
    <span style="color: black;">--匹配上的<span style="color: black;">表示</span>其成绩,匹配不上的<span style="color: black;">表示</span>为 NULL</span>
    <span style="color: black;">SELECT</span>
    stu.SName N<span style="color: black;">学生姓名</span>,
    sco.SSId N<span style="color: black;">学生编号</span>,
    sco.SChinese N<span style="color: black;">语文成绩</span>,
    sco.SMath N<span style="color: black;">数学成绩</span>,
    sco.SHistory N<span style="color: black;">历史成绩</span>
    <span style="color: black;">FROM</span> dbo.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> dbo.Score sco
    <span style="color: black;">ON</span> stu.SId=sco.SSId

    <span style="color: black;">--以成绩表为主的左联接</span>
    <span style="color: black;">--<span style="color: black;">表示</span>的<span style="color: black;">最后</span>行数 以成绩表中的行数为准</span>
    <span style="color: black;">--假设符合<span style="color: black;">要求</span>的 成绩表行数为 10 就会<span style="color: black;">表示</span>10条数据</span>
    <span style="color: black;">SELECT</span>
    stu.SName N<span style="color: black;">学生姓名</span>,
    sco.SSId N<span style="color: black;">学生编号</span>,
    sco.SChinese N<span style="color: black;">语文成绩</span>,
    sco.SMath N<span style="color: black;">数学成绩</span>,
    sco.SHistory N<span style="color: black;">历史成绩</span>
    <span style="color: black;">FROM</span> dbo.Score sco
    <span style="color: black;">LEFT</span> <span style="color: black;">OUTER</span> <span style="color: black;">JOIN</span>dbo.Student<span style="color: black;">AS</span> stu
    <span style="color: black;">ON</span> sco.SSId=stu.SId

    <span style="color: black;">--LEFT OUTER JOIN 语句中的 OUTER 可省略</span>
    <span style="color: black;">重视</span>:左联接时以哪个表为主会影响<span style="color: black;">最后</span><span style="color: black;">表示</span>数据的行号<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p26-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/adc55ec327744348b4ed5bc559b45e41~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723346820&amp;x-signature=ZiPM7fveffORk9Zo8OCOblPNv5U%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;"><strong style="color: blue;"><span style="color: black;">2 右联接(right join)</span></strong><span style="color: black;">返回右表(即right join语句中的表)中的所有行,<span style="color: black;">同期</span>返回右表(from语句中的表)中匹配的行,<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;">示例将左联接的 left 替换为 right 结果与上图中的正好相反(<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 style="color: black;">重视</span>:不论左联接还是右联接<span style="color: black;">最后</span><span style="color: black;">表示</span>的行数以为主的表中行数为准</span></strong></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;">3 完全联接(full join)</span></strong><span style="color: black;">返回左表和右表中的所有行,<span style="color: black;">倘若</span>某个表中<span style="color: black;">无</span>匹配的行,则返回 NULL 值</span></p><span style="color: black;">SELECT</span> * <span style="color: black;">--<span style="color: black;">表示</span>两个表中的所有列名</span>
    <span style="color: black;">FROM</span> dbo.Student <span style="color: black;">AS</span> stu
    <span style="color: black;">FULL</span> <span style="color: black;">OUTER</span> <span style="color: black;">JOIN</span> dbo.Score sco
    <span style="color: black;">ON</span> sco.SSId=stu.SId
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/01a62a9da29d45659a4667320bfc3415~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723346820&amp;x-signature=PACjAHgR32hsZNc4gFGRt8SyquI%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>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;">交叉联接(cross join)</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>)</span></p><span style="color: black;">--学生表 24条数据</span>
    <span style="color: black;">--班级表 8条数据</span>
    <span style="color: black;">--<span style="color: black;">表示</span>总数据行数 192</span>
    <span style="color: black;">SELECT</span> *
    <span style="color: black;">FROM</span> dbo.Student
    <span style="color: black;">CROSS</span> <span style="color: black;">JOIN</span> dbo.Class<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/28615fe3b9a4451e92c6531d1577ad27~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1723346820&amp;x-signature=YV7oik9rvEJfuyL6jhT3gYBgYbc%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>

星☆雨 发表于 2024-9-5 16:13:13

期待楼主的下一次分享！”

qzmjef 发表于 2024-10-10 04:23:57

楼主听话，多发外链好处多，快到碗里来！外链论坛 http://www.fok120.com/

查看完整版本: SQL - 联表查询(Join)内联接,外联接,交叉联接 161