4.多表查询&&表连接

2.5 SQL语句------》多表查询

也属于简单查询

2.5.1什么是多表查询

1．多张表联合查询----》dept表+emp表
Deptno dname loc

1.在之前进行查询语句编写的时候发现FROM子句之后都是只是存在一张数据表，那么现在你需要查询的数据来自于多张数据表，，就需要通过多表查询来解决当前的实际问题。
2.实际开发中，多表查询慎重选择（根据数量决定）
3.多表查询的本质就在于FROM之后可以编写多张表的名称。
4.要进行多表查询，往往都会有一些关联的字段或关联的条件。使2个数据表之间，存在对应关系。

2.5.2多表查询基本语法

执行顺序：from—》where–》select–》order by
FROM 后可以设置多张表，多表查询的参考语法
SELECT [DISTINCT] * | 列名1【别名1】，列名2 【别名2】。。。。|计算过程|统计函数
FROM 数据表1 【别名1】，数据表2【别名2】，。。。。
【WHERE 限定条件1，限定条件2。。。。】
【ORDER BY 排序字段1 【ASC|DESC】, 排序字段2【ASC|DESC】……】；
以分号；结束

2.5.3验证数据量

多表查询的使用取决于数据量
主要用到的是2个表----》emp和dept表
1.查询emp表中的数据量
可以这样查询

也可以使用统计函数查询

2.查询dept表中的数据量
可以这样查询

也可以使用统计函数查询

Emp有14行数据，dept有4行数据，共计有14*4=56行数据,在数据少情况下可以使用。如果是成百上千行数据，禁止使用多表查询。

2.5.4笛卡尔积出现与消除

多表查询一定会出现笛卡尔积
1.直接实现emp与dept表的多表查询处理
SQL> SELECT *
2 FROM emp,dept;

56行记录=emp的14行记录*dept的4行记录，这个积，在数据库中称为笛卡尔积，（积是永远存在的，关联的数据表越多，那么积就越大，唯一可以消除就是显示的积的信息）。
2.消除显示的笛卡尔积
要进行多表查询，往往都会有一些关联的字段或关联的条件。使2个数据表之间，存在对应关系。要使用where限定查询，实际上笛卡尔积还是会发生，只不过显示限定查询后的结果。
使用where子句来过滤笛卡尔积的数据，将我们需要的数据展示出来。（笛卡尔积始终存在）

2.5.5表名.字段名

1.同一个字段在2个数据表中同时出现，需要在字段前表示明确是是哪个表的字段，方便区分。不同表中存在相同的字段，必须使用表名/别名限定字段的来源，若字段在多表中是唯一的，则表名/别名的限定不是必须的，（习惯上都需要标记清楚）
SELECT子句中，如果能分清楚字段，就不要使用别名。如果区分困难，可以使用别名标注。

2.练习1：
SQL> SELECT ename,dname,emp.deptno,dept.deptno
2 FROM emp,dept;
这样显示名字太长不方便书写，可以在表后添加别名

练习2：
SQL> SELECT ename,dname,e.deptno,d.deptno
2 FROM emp e,dept d;
但是这个还可能出现分不清楚哪个数据库表对应哪个deptno，因此可以在字段后面添加别名。

练习3：
SQL> SELECT ename,dname,e.deptno eno ,d.deptno dno
2 FROM emp e,dept d;

练习4：如果只是查询Smith员工的的信息，就需要添加where条件限定
SQL> SELECT ename,dname,e.deptno eno ,d.deptno dno
2 FROM emp e,dept d
3 where e.deptno=d.deptno;

2.5.6多表查询的分析

对于多表查询的前提是，关联的数据表之前存在关系（字段，条件）
多表查询的分析方法就是一点点的剖析数据表的机构，而后找出对应的关联关系。
1.例如：
A．查询每个雇员的姓名，编号，职位，工资，部门名称
----》确定数据表：
Emp： ename ，empno，job，sal
Dept：dname
----》确定的已知的关联关系： emp.deptno=dept.deptno
SELECT e.ename,e.job,e.sal,d.dname ,e.empno
FROM emp e, dept d
WHERE e.deptno=d.deptno;

B．查询每个雇员的姓名，编号，工资，工资等级
----》确定数据表：
Emp： ename ，sal，empno
salgrade：grade
----》确定的已知的关联关系：e.sal BETWEEN s.losal AND s.hisal;
SELECT e.ename,e.sal,e.empno,s.grade
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;

C．查询雇员的姓名，编号,工资,工资等级,职位，部门名称，部门位置
----》确定数据表：
Emp表： ename ,empno,sal,job
salgrade表：grade
dept表: dname,loc
----》确定的已知的关联关系：e.sal BETWEEN s.losal AND s.hisal e.deptno=d.deptno;
SQL> SELECT e.ename,e.empno,e.sal,e.job,s.grade,d.dname,d.loc
2 FROM emp e,salgrade s,dept d
3 WHERE e.sal BETWEEN s.losal AND s.hisal
4 AND e.deptno=d.deptno;

2.6 SQL语句--------》表连接

2.6.1表连接的含义及分类

在数据库中，多表查询的表连接分为外连接和内连接
1.内连接
内连接：等值连接
在之前进行判断时会使用一些条件，例如：e.deptno=d.deptno，只有条件判断满足了，才会显示相应的数据信息。
2.外连接
外连接就是保证某一个表的数据全部显示。
1）外连接的分类
左外链接-----》左连接
------》FROM A表，B表----》A表在左边----》符合A表条件的数据取出来，不符合A表的数据也取出来—》左外链接
--------->WHERE e.deptno=d.deptno(+);----->ORACLE专用的语法（+）
右外连接-----》右连接
------》FROM A表，B表----》B表在右边----》符合B表条件的数据取出来，不符合B表的数据也取出来—》右外链接
全外连接-----》全连接
------》FROM A表，B表----》符合条件的A，B表的数据都取出来，不符合A，B表的数据也取出来—》全外链接

2.6.2.连接的验证测试

要想继续连接的验证测试，需要准备出一个新的数据
1.创建新的数据
1）插入数据语法格式
INSERT INTO 表名（字段1，字段2，字段3，。。。。） VALUES （值1，值2，值3，。。。）
练习
SQL> INSERT INTO emp(empno,ename,job) VALUES(9999,‘YYN’,‘CLERK’);

查看新插入的数据

2. 删除数据的语法格式
   DELETE FROM 表名 WHERE 限定条件
   

删除数据需要使用commit—》提交-----》生成删除修改

2.外连接的测试
1）左外连接
左外连接：左表的数据要求全部显示；
显示的内容还是基于等值连接（内连接）上作出扩展
左外链接（左连接）-------》字段=字段（+）
A．查询所有雇员的姓名，编号，工资，部门名称
a）先查看emp，dept表中员工的数据

b）进行限定查询 WHERE e.deptno=d.deptno;
SELECT e.ename,e.job,e.sal,d.dname ,e.empno
FROM emp e, dept d
WHERE e.deptno=d.deptno;

C）添加左外连接就可以显示yyn行记录
SELECT e.ename,e.job,e.sal,d.dname ,e.empno
FROM emp e, dept d
WHERE e.deptno=d.deptno;
分析1：因为emp 表在左边，现在又要显示emp中的yyn行记录，而yyn行记录又不满足where的限定条件，因此就需要使用左外链接。
分析2：左外连接如何表示----》在where条件后关联条件的右边加(+)
----》在WHERE e.deptno=d.deptno(+);
SQL> SELECT e.ename,e.job,e.sal,d.dname ,e.empno
2 FROM emp e, dept d
3 WHERE e.deptno=d.deptno(+);

2）右外连接
右外链接：右表的数据要求全部显示；
显示的内容还是基于等值连接（内连接）上作出扩展
右外连接（右连接）-------》字段（+）=字段
A．查询所有雇员的姓名，编号，工资，部门名称
a）先查看emp，dept表中员工的数据

b）进行限定查询 WHERE e.deptno=d.deptno;
SELECT e.ename,e.job,e.sal,d.dname ,e.empno , d.deptno
FROM emp e, dept d
WHERE e.deptno=d.deptno;

C）添加右外连接就可以显示40编号部门行记录
SELECT e.ename,e.job,e.sal,d.dname ,e.empno
FROM emp e, dept d
WHERE e.deptno=d.deptno;
分析1：因为dept 表在右边，现在又要dept中的40编号行记录，而40编号行记录又不满足where的限定条件，因此就需要使用右外链接。
Emp表中有yyn雇员，但是没有对应的部门信息，dept表有40编号部门，但部门没有雇员
分析2：左外连接如何表示----》在where条件后关联条件的左边加(+)
----》在WHERE e.deptno（+）=d.deptno；
SELECT e.ename,e.job,e.sal,d.dname ,e.empno, d.deptno
FROM emp e, dept d
WHERE e.deptno(+)=d.deptno;

2.6.3如何区分左外连接

1.大部分情况下，如果发现没有数据，就采用外连接即可。
当查看所有的雇员信息，因为没有添加外连接，导致雇员的信息缺失，只需要添加相应的符号让数据全部显示出来即可，没有必要区分左外连接和右外连接。
2.使用的时候，如果需要就测试一些，然后做区分，能记住就区分，记不住就不用区分了。
3.利用外连接来做一个连接自身的出来操作，所谓的自身连接指的数据表自己关联自己。

2.6.4例如

1.显示雇员的名字和所在的部门的名字
-----显示的字段：enamel和dname
-----确定表： emp 和dept
-----确定关系：e.deptno=d.deptno
SQL> SELECT e.ename,d.dname
2 FROM emp e,dept d
3 WHERE e.deptno=d.deptno(+);

2.显示每个雇员的名字职位，领导的名字
----确定表：emp-----》ename job
Emp-----》领导名字—mgrn表
----确定关系：emp的mgr =领导表.empno
SELECT e.ename,e.job,mgrn.ename
FROM emp e,emp mgrn
WHERE e.mgr=mgrn.empno;

总结：左外连接 字段=字段(+)
右外连接 字段（+）=字段
这个（+）定义方式是Oracle数据库中使用的。

2.7.1 SQL：1999语法标准

SELECT 列名1 【别名1】，列名2【别名2】。。。。。
FROM 表名1
【CROSS JOIN 表名2】|
【NATURAL JOIN 表2】|
【 JOIN 表2 USING（列名）】|
【JOIN 表2 ON （表1.列名=表2.列名）】|
【LEFT | RIGHT | FULL OUTER JOIN 表2 ON (表1.列名=表2.列名)】;
1.第1种格式—》交叉连接
SELECT 列名1 【别名1】，列名2【别名2】。。。。。
FROM 表名1 【CROSS JOIN 表名2】；
1）练习
SQL> SELECT *
2 FROM emp e CROSS JOIN dept d;

等同于
SELECT *
FROM emp e , dept d；
最终显示结果为：乘积-----》笛卡尔积------》60行数据
2.第2种格式—》自然连接
SELECT 列名1 【别名1】，列名2【别名2】。。。。。
FROM 表名1 【NATURAL JOIN 表2】；
NATURAL JOIN自然连接----》自动选择相同列，关联做过滤查询-----》这也是内连接
3.使用自然连接时，不能使用表名或表的别名修饰列。
1）练习
SQL> SELECT *
2 FROM emp e NATURAL JOIN dept d;

对于emp和dept表来讲，自动选择的结果就是deptno字段
等同于
SELECT *
FROM emp e , dept d；
Where e.deptno=d.deptno
最终显示结果为：14行数据
3.第3种格式—》USING
SELECT 列名1 【别名1】，列名2【别名2】。。。。。
FROM 表名1 【 JOIN 表2 USING（列名）】；

JOIN ……USING----->指定字段内连接，要求表中的字段名相同
1）USING子句语法
有些情况下，若数据表之间存在多个关联的列，在这种情况下，使用USING 指定关联的列，可以手动指定某一个关联的列。------这也是内连接
适用于2个表中字段名相同的情况使用-----USING
2）练习
A）SQL> SELECT *
2 FROM emp e JOIN dept d USING(deptno);
表示emp和dept这2个表关联，按照指定的deptno列名做关联
表示：USING(deptno)========e.deptno=d.deptno

等同于
SELECT *
FROM emp e , dept d；
Where e.deptno=d.deptno
最终显示结果为：14行数据
4.第4种格式—》ON
SELECT 列名1 【别名1】，列名2【别名2】。。。。。
FROM 表名1 【JOIN 表2 ON （表1.列名=表2.列名）】；

JOIN ……ON ……------》指定字段内连接，若表中关联的字段名不同时，使用ON 语法。
1）练习
SQL> SELECT *
2 FROM emp e JOIN emp me ON(e.mgr=me.empno);
表示让e.mgr字段和me.empno字段对应，这是字段不同情况下使用join……on，Me表示emp的领导表，e表示emp的员工表，让员工表的mgr字段对应领导表的empno字段

等同于
SELECT *
FROM emp e ,emp me
WHERE e.mgr=me.empno ；
最终结果为：自连接-----》显示符合条件的行记录-----》13行数据
5.第5种格式—》左右全外连接
外联不可以跟OR IN 联用
SELECT 列名1 【别名1】，列名2【别名2】。。。。。
FROM 表名1 【LEFT | RIGHT | FULL OUTER JOIN 表2 ON (表1.列名=表2.列名)】；
1）左外连接-----》
SELECT 列名1 【别名1】，列名2【别名2】。。。。。
FROM 表名1 【LEFT OUTER JOIN 表2 ON (表1.列名=表2.列名)】；
A.练习
SQL> SELECT *
2 FROM emp e LEFT OUTER JOIN dept d ON (e.deptno=d.deptno);

等同于
SELECT *
FROM emp e , dept d；
Where e.deptno=d.deptno（+）；-----》让emp表的内容显示完全
最后显示----》15行数据，多一行emp表中的yyn信息
3）右外连接
SELECT 列名1 【别名1】，列名2【别名2】。。。。。
FROM 表名1 【RIGHT OUTER JOIN 表2 ON (表1.列名=表2.列名)】；
A.练习
SQL> SELECT *
2 FROM emp e RIGHT OUTER JOIN dept d ON (e.deptno=d.deptno);

等同于
SELECT *
FROM emp e , dept d；
Where e.deptno（+）=d.deptno；-----》让dept表的内容显示完全
最后显示----》15行数据，多一行dept表中的40编号信息
3）全外连接
SELECT 列名1 【别名1】，列名2【别名2】。。。。。
FROM 表名1 【 FULL OUTER JOIN 表2 ON (表1.列名=表2.列名)】；
A.练习
SELECT *
FROM emp e FULL OUTER JOIN dept d ON (e.deptno=d.deptno);

最后显示----》16行数据，包含emp表中的yyn信息和dept表中40编号的信息

总结

不用刻意区分，左右全外连接，外连接===》将指定表中的不符合要求的行也显示。