多表连接查询详细解析(详细版)

前言：写SQL语句：(先找出表的数据)找哪2个表，什么字段，过滤条件。(最后思考过滤条件,过滤条件肯定是2张表都有关联的字段，不一定是同名的字段，思考：表1哪个字段和表2哪个字段有关联)
1.为什么要多张表，因为设计成一张表会造成数据冗余，浪费存储空间。

2.连接查询的分类
根据语法出现的年代来划分：
SQL92(一些老的DBA可能还在使用这种语法。DBA：DataBase Administrator,数据库管理员)
SQL99(比较新的语法)
根据表的连接方式来划分：
内连接：

	等值连接
	非等值连接
	自连接

外连接：

	左外连接(左连接)
	右外连接(右连接)

全连接：(基本用不到)

3.多表查询有3种
①交叉查询(没有任何实际意义，得到的是2张表的笛卡尔积，表1的每一项都组合表2的每一项：笛卡尔乘积现象(没有条件限制)，结果条数是2张表记录条数的乘积)
如：select ename,dname from emp,dept; //ename和dname要联合起来一块显示，粘到一块。拿着第一张表的第一条记录和第二张表的每一条记录都匹配，然后每一条条记录也一样去找表2匹配。
用别名：select e.ename,d.dname from emp e,dept d; //select ename,dname from emp ,dept ; 如果不用别名，在找ename时，数据库既会去emp表找，也会去dept表找。若两张表存在一模一样的字段，会出现混淆。
表别名的好处：

第一：执行效率高。
第二：可读性好。

怎么避免笛卡尔积现象？加条件进行过滤。
思考：避免了笛卡尔积现象，会减少记录的匹配次数吗？
不会，次数还是56次。只不过显示的是有效记录。
SQL92语法(基本不用)：select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;//满足条件的才粘一块，进行了过滤。(避免了笛卡尔积)
②内连接查询
1)内连接之等值连接：最大特点是：条件是等量关系。
案例：查询每个员工的部门名称，要求显示员工名和部门名。
SQL92: select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;//语法太老，基本不用了
SQL99: select e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno;//常用，inner可以省略，带着inner目的是可读性好一些。
语法：… A join B on 连接条件 where 查询条件；//表连接的条件和数据进行过滤的where条件进行分离。语法结构更清晰。
2)内连接之非等值连接：最大的特点是：连接条件中的关系是非等量关系。
案例：找出每个员工的工资等级，要求显示员工名、工资、工资等级。
select e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;

3)自连接：最大的特点是：一张表看做两张表。自己连接自己。
案例：找出每个员工的上级领导，要求显示员工名和对应的领导名。//希望mgr里显示的是领导名，而不是编号
select empno, ename, mgr from emp;

其实都是同一张表，员工的领导编号=领导的员工编号。(核心点,在于找这个等量关系)
select a.ename as ‘员工名’, b.ename as ‘领导名’ from emp a inner join emp b on a.mgr=b.empno;

③外连接查询
什么是外连接，和内连接有什么区别？
内连接：(会忽略一些信息)
假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。
AB两张表没有主副之分，两张表是平等的。
外连接：(king没有上级领导，但是还是要查出来，但是King也是员工，这时一定用外连接)
假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是附表，主要查询主表中的数据(主表的数据无论如何都要查出来)，捎带者查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出null与之匹配。

外连接的分类？
左外连接(左连接)：表示左边的这张表是主表，主表的意思是左边的表数据不会为空，但是可能会重复，查出的数据可能比主表的行数还要多(与从表发生多次关联)。
右外连接(右连接)：表示右边的这张表是主表。
左连接有右连接的写法，右连接也会有对应的左连接的写法。
案列：找出每个员工的上级领导？(所有员工必须全部查询出来。)
外连接(左)：
select a.ename ‘员工’, b.ename ‘领导’ from emp a left outer join emp b on a.mgr=b.empno;//outer可以省略。
外连接(右)：
select a.ename ‘员工’, b.ename ‘领导’ from emp b right outer join emp a on a.mgr=b.empno;

外连接最重要的特点是：主表的数据无条件的全部查询出来。

4.三张以上表连接查询。
案列：找出每一个员工的部门名称以及工资等级。
分析：
①员工的部门名称：说明用到员工表和部门表。
②员工的工资等级：说明用到员工表和工资表。

③找出每2张表的连接条件(2张表都有关联的字段).
注意：
… A join B join C on …
表示：A表和B表先进行表连接，连接之后总表继续和C表进行连接。
语句：select e.ename,d.dname,s.grade from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.loasl and s.hisal;

案例2：找出每一个员工的部门名称、工资等级、以及上级领导。
select e.ename ‘员工’,d.dname,s.grade, e1.ename ‘领导’ from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.loasl and s.hisal left join emp e1 on e.mgr=e1.empno;
注意：①e与e1其实是同一张表，把一张表看出两张表。
②left不能放在第一个join的前面，若放在第一个join前面表示emp是主表，dept是从表。
③考虑清楚是否该用外连接。

---

以下是本人工作中关于多表连接更新数据遇到问题后的一些总结：
①2张表关联更新数据
sql92MySQL: update t_recon_contractcenter t1,t_recon_contractbill t2 set t1.famount = t2.famount,t1.foriamt=t2.foriamt,t1.flatestoriprice=t2.flatestoriprice where t1.FID = t2.FID and t1.FBILLNO =‘CT-2022-W-20046’;

sql99MySQL: update t_recon_payreqbill_c pay left join t_recon_payregister reg on pay.fid=reg.fpayreqbillid set pay.fpayedoriamt=reg.ftotalpayoriamt where pay.fid=1521201349948080128;

PG数据库：update t_recon_payreqbill_c pay set fpayedoriamt=reg.ftotalpayoriamt from t_recon_payregister reg where pay.fid=reg.fpayreqbillid and pay.fid=1521201349948080128; //pg库的语法还是相差挺大的，需要注意别名的使用等