7wu1wm0 发表于 2024-8-4 15:32:12

【SQL】SQL语句中的连接查询

<span style="color: black;">数据库建表过程中为了减<span style="color: black;">少许</span>据冗余，<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 style="color: black;">便是</span>SQL<span style="color: black;">查找</span>语句中最<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 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 style="color: black;">查找</span>分为SQL92语法和SQL99语法。</span><span style="color: black;">其二是<span style="color: black;">按照</span>表的连接方式划分，将<span style="color: black;">查找</span>分为内连接、外连接及全连接，下图为连接</span><span style="color: black;">方式的细分。</span><span style="color: black;"><span style="color: black;"><img src="https://mmbiz.qpic.cn/mmbiz_png/H1xSYAkubGI63k6Acw7ibwibzroicUF8sKV7GiboF7eGXvFNicZibbElyUrnlaWAwRzQKE5hiakiallnHtOEVSs6gMzLfQ/640?wx_fmt=png&amp;tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;wx_co=1" style="width: 50%; margin-bottom: 20px;"></span></span><span style="color: black;">下述案例中用到的emp表为</span>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_png/H1xSYAkubGI63k6Acw7ibwibzroicUF8sKVAckcK7GJQXcWUMiabjOOcqiaquUHZZGRiccUzuHAydLPPu0DpAohbMiajQ/640?wx_fmt=png&amp;tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;wx_co=1" style="width: 50%; margin-bottom: 20px;"></p><span style="color: black;">下述案例中用到的<span style="color: black;">分部</span>表dept为</span>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_png/H1xSYAkubGI63k6Acw7ibwibzroicUF8sKV6LKakf3V4X9ia6qPO9jguSNyPQUuU1M2O7QjaUwunkVh918HWq6OWLA/640?wx_fmt=png&amp;tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;wx_co=1" style="width: 50%; margin-bottom: 20px;"></p><span style="color: black;"><strong style="color: blue;"><span style="color: black;">内连接之等值连接</span></strong></span><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><span style="color: black;"><span style="color: black;">经过</span>这道案例题，<span style="color: black;">首要</span>感受一下SQL92语法<span style="color: black;">暗示</span>内连接的等值连接。</span><span style="color: black;">select ename, dname from emp e, dept d where e.deptno = d.deptno;</span><span style="color: black;">下面用SQL99语法进行改写。</span><span style="color: black;">select ename,dname from emp e inner join dept on e.deptno = d.deptno;</span><span style="color: black;">不管是SQL92语法还是SQL99语法，emp表和dept表连接的<span style="color: black;">要求</span>都是等号，<span style="color: black;">因此</span><span style="color: black;">叫作</span>之为等值连接。</span><span style="color: black;">而SQL99语法为select xxx from A表 inner join B表 on 连接<span style="color: black;">要求</span> where 筛选<span style="color: black;">要求</span>;</span><span style="color: black;"><span style="color: black;">经过</span>语法格式<span style="color: black;">能够</span><span style="color: black;">发掘</span>，其将连接<span style="color: black;">要求</span>放在on后面，从而和where后面的筛选<span style="color: black;">要求</span><span style="color: black;">掰开</span>，更加清晰、明了。</span><span style="color: black;"><strong style="color: blue;"><span style="color: black;">内连接之非等值连接</span></strong></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;">select e.ename, e.sal, s.salgrade from emp e inner join sal s on e.sal between s.lowsal between s.hisal;</span><span style="color: black;"><span style="color: black;">经过</span><span style="color: black;">以上</span>SQL99<span style="color: black;">查找</span>语句<span style="color: black;">能够</span><span style="color: black;">发掘</span>，on后面紧跟的连接<span style="color: black;">要求</span>是between ... and ...语句，显然是非等值语句。</span><span style="color: black;"><span style="color: black;">另一</span>，非等值<span style="color: black;">要求</span>还<span style="color: black;">能够</span>是&gt;、&lt; 、&lt;&gt;等。</span><span style="color: black;"><strong style="color: blue;"><span style="color: black;">内连接之自连接</span></strong></span><span style="color: black;">案例：<span style="color: black;">表示</span>员工姓名和上级领导。</span><span style="color: black;">select e1.ename, e2.ename from emp e1 inner join emp e2 on e1.mgr = e2.empno;</span><span style="color: black;">从<span style="color: black;">以上</span><span style="color: black;">查找</span>语句<span style="color: black;">能够</span>看出emp表完<span style="color: black;">成为了</span>自连接，即<span style="color: black;">能够</span>将其<span style="color: black;">同期</span>看作e1表和e2表的连接。</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;"><img src="https://mmbiz.qpic.cn/mmbiz_png/H1xSYAkubGI63k6Acw7ibwibzroicUF8sKVCM66G2bJe8LUr1haIXz95AVn21CP5xuq8HNFbuNicp2Fntftic7HXlaA/640?wx_fmt=png&amp;tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;wx_co=1" style="width: 50%; margin-bottom: 20px;"></p><span style="color: black;"><span style="color: black;">然则</span>这条<span style="color: black;">查找</span>语句只能查出13条记录，<span style="color: black;">由于</span>KING的领导为空，而在内连接中，<span style="color: black;">倘若</span>连接字段中的数据<span style="color: black;">显现</span>空，<span style="color: black;">那样</span>就<span style="color: black;">查找</span>不出这条记录。</span><span style="color: black;">而想要查出所有数据就要用到外连接。</span><span style="color: black;"><strong style="color: blue;"><span style="color: black;">外连接之左外连接</span></strong></span><span style="color: black;">关于外连接<span style="color: black;">咱们</span>要弄清楚两点内容。</span><span style="color: black;"><span style="color: black;">首要</span><span style="color: black;">第1</span>点是内连接与外连接的区别。</span><span style="color: black;">假设A和B进行连接，<span style="color: black;">运用</span>内连接，凡是A表和B表能够匹配上的记录<span style="color: black;">查找</span>出来。AB两张表<span style="color: black;">无</span>主副之分，两张表是平等的。</span><span style="color: black;">假设A和B两张表进行连接，<span style="color: black;">运用</span>外连接的话，AB两张表有一张是主表，一张是副表，<span style="color: black;">重点</span><span style="color: black;">查找</span>主表中的数据，捎带着<span style="color: black;">查找</span>附表。</span><span style="color: black;">当附表中的数据<span style="color: black;">无</span>和主表中的数据匹配上，副表会自动用null与之匹配。</span><span style="color: black;"><span style="color: black;">以上</span>自连接<span style="color: black;">运用</span>的案例采用的是内连接，<span style="color: black;">因此</span>当员工king的领导编号为空时，员工king的记录就不会被匹配出来。</span><span style="color: black;"><span style="color: black;">然则</span><span style="color: black;">倘若</span><span style="color: black;">运用</span>外连接，SQL语句改为：</span><span style="color: black;">select e1.ename, e2.ename from emp e1 left join emp e2 on e1.mgr = e2.empno;</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;"><img src="https://mmbiz.qpic.cn/mmbiz_png/H1xSYAkubGI63k6Acw7ibwibzroicUF8sKVB3aLNicqOvLOQ1YZ8Hu8AvSYHSAj327rEkEngqNees3x57WNPc8w8GQ/640?wx_fmt=png&amp;tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;wx_co=1" style="width: 50%; margin-bottom: 20px;"></p><span style="color: black;"><span style="color: black;">经过</span><span style="color: black;">查找</span>结果<span style="color: black;">能够</span><span style="color: black;">发掘</span>一共产生了14条记录，其中king的领导是null即为空。</span><span style="color: black;"><span style="color: black;">亦</span><span style="color: black;">便是</span>说，<span style="color: black;">运用</span>外连接，副表中的数据都<span style="color: black;">不可</span>和主表匹配时，会自动产生null与之对应。</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 style="color: black;">倘若</span>用left join，<span style="color: black;">那样</span>left join左边的表<span style="color: black;">便是</span>主表，右边<span style="color: black;">显现</span>的表是副表。</span><span style="color: black;">而right join<span style="color: black;">暗示</span>左边<span style="color: black;">显现</span>的表是副表而右边<span style="color: black;">显现</span>的表是主表。</span><span style="color: black;">还是拿下述SQL做参考。</span><span style="color: black;">select e1.ename, e2.ename from emp e1 left join emp e2 on e1.mgr = e2.empno;</span><span style="color: black;">e1出<span style="color: black;">此刻</span>left join左边，<span style="color: black;">因此</span>其是主表而e2为副表。</span><span style="color: black;"><span style="color: black;">END</span></span>




1fy07h 发表于 2024-9-25 20:06:10

外贸B2B平台有哪些？

nqkk58 发表于 2024-10-14 17:44:59

期待你更多的精彩评论，一起交流学习。
页: [1]
查看完整版本: 【SQL】SQL语句中的连接查询