Oracle数据库——多表查询-05

多表查询
多表查询的基本概念

3.连接
对于数据表连接操作也是分为两种

内连接：等值连接，在之前所编写的限定条件都是使用了“字段=字段”的形式定义的
外连接：左外连接（左连接），右外连接（右连接），全外连接（全连接）

为了更好的理解所有连接的区别，那么下面首先要为emp表中增加一条没有部门（deptno=0）的雇员信息
执行如下的语句

INSERT INTO emp(empno,ename,job,hiredate,sal,comm)VALUES(8888,'TONY','CLERK',SYSDATE,850,10);

现在emp表中存在一个没有部门的雇员，部门表中存在一个没用雇员的部门
范例：观察内链接
SELECT e.empno,e.ename,d.deptno,d.dname
FROM emp e,dept d
WHERE e.deptno=d.deptno;
因为现在“e.deptno=d.deptno”的判断不成立，所以不成立的数据就不显示了，
而如果希望emp表或者是dept表中的数据全部显示，那么就需要通过外连接来解决

左外连接：字段=字段（+）；
右外连接：字段（+）=字段；

• 范例：使用左外连接
• SELECT e.empno,e.ename,d.deptno,d.dname
• FROM emp e,dept d
• WHERE e.deptno=d.deptno(+);

范例：使用右外连接
SELECT e.empno,e.ename,d.deptno,d.dname
FROM emp e,dept d
WHERE e.deptno(+)=d.deptno;

至于在工作之中是使用左外还是右外连接本质上来讲没有任何的思考意义，唯一的方案：当表之中的数据 没
有全部显示出来，使用外连接控制

范例：要求查询出每个雇员的编号，姓名，职位，工资，领导姓名
在emp表之中存在有一个mgr的字段，这个字段保存的是每一位雇员对应的领导编号
确定所需要的数据表

emp表:雇员的编号，姓名，职位，工资

emp表：领导姓名
确定已知的关联关系
雇员和领导：emp.mgr=emp.empno（雇员的领导编号=领导雇员编号）
第一步：实现自身关联

1. SELECT e.empno,e.ename,e.job,e.sal,m.ename mname 
2. FROM emp e,emp m
3. WHERE e.mgr=m.empno;

第二步：由于现在使用的是内连接的方式，所以如果mgr为null的数据不会显示，当需要的数据没有显示出来就
要外连接的方式

1. SELECT e.empno,e.ename,e.job,e.sal,m.ename mname 
2. FROM emp e,emp m
3. WHERE e.mgr=m.empno(+);

以上的连接方式称为自身连接，不管是自身连接还是外身连接，总之表关联就要确定使用的表，而后一定
要找到消除积的关联字段或者是关联条件

SQL 1999语法
之前所使用的“（+）”符号主要是进行外连接的控制，但是这个符号只是Oracle自己才有的，但是如果
想其它的数据库使用外连接，则需要通过SQL:1999语法标准操作，语法如下，

SELECT table1.column,table2.column

FROM table1[CROSS JOIN table2]|
[NATURAL JOIN table2]|
[JOIN table2 USING(column_name)]|
[JOIN table2 ON(table1.column_name=table2.column_name)]|
[LEFT|RIGHT|FULL OUTER JOIN table2 ON(table1.column_name=table2.column_name)]

以上的语法可以细分的，分为不同的子语法。

第一个：交叉连接
SELECT table1.column,table2.column
FROM table1 CROSS JOIN table2；
范例：验证交叉连接
SELECT *
FROM emp CROSS JOIN dept;
 使用交叉连接的最终的目的是产生笛卡尔积

第二个：自然连接
SELECT table1.column,table2.column
FROM table1 NATURAL JOIN table2；
范例：验证自然连接
SELECT * FROM emp NATURAL JOIN dept;
自然连接是自动使用关联字段（一般的关联字段都是同名的）消除笛卡尔积，同时在显示结果中也会把关联字段
显示在首列

第三个：USING子句
SELECT table1.column,table2.column
FROM table1 JOIN table2 USING(column_name)
使用USING子句设置两张表的关联字段名称
范例：验证JOIN...USING
SELECT *FROM emp JOIN dept USING(deptno);

第四个：ON子句，语法：
SELECT table1.column,table2.column
FROM table1 JOIN table2 ON(table1.column_name=table2.column_name)；
范例：验证JOIN...ON
SELECT * FROM emp e JOIN dept d ON(e.deptno=d.deptno);

第五个：外连接
SELECT table1.column,table2.column
FROM table1 LEFT|RIGHT|FULL OUTER JOIN table2 ON(table1.column_name=table2.column_name)；
范例：验证全外连接
SELECT * FROM emp e FULL OUTER JOIN dept d ON(e.deptno=d.deptno);
全外连接就是让两张表的记录全部进行显示，但是这样的操作意义不大

查询结果集合操作
在之前都是针对于数据表实现的关联，那么在实现之中也可以针对于查询结果进行一个连接，使用UNION,
UNION ALL，INTERSECT,MINUS实现，操作语法

SELECT [DISTINCT] * | 列名称 [别名],...  ==<3.确定数据显示列
FROM 表名称 [别名]    ==<1.确定数据来源
WHERE 限定条件(s)    ==<2.针对于数据行进行筛选
[ORDER BY 排序字段[ASC|DESC],排序字段[ASC|DESC],...];  ==<4.数据列排序
    [UNION | UNION ALL | INTERSECT | MINUS]
SELECT [DISTINCT] * | 列名称 [别名],...  ==<3.确定数据显示列
FROM 表名称 [别名]    ==<1.确定数据来源
WHERE 限定条件(s)    ==<2.针对于数据行进行筛选
[ORDER BY 排序字段[ASC|DESC],排序字段[ASC|DESC],...];  ==<4.数据列排序
    [UNION | UNION ALL | INTERSECT | MINUS]

但是进行集合操作的时候，由于其主要是将若干个查询结果连接在一起显示，所以要求这些查询结果返回
的结构必须完全相同

范例：使用UNION——重复的数据不显示
SELECT empno,ename,job,sal,deptno FROM emp WHERE deptno=10
    UNION
SELECT empno,ename,job,sal,deptno FROM emp;

范例：使用UNION ALL操作——所有的数据都显示，包含重复数据
SELECT empno,ename,job,sal,deptno FROM emp WHERE deptno=10
    UNION ALL
SELECT empno,ename,job,sal,deptno FROM emp;

范例：使用INTERSECT操作——返回若干个查询之中相同的部分
SELECT empno,ename,job,sal,deptno FROM emp WHERE deptno=10
    INTERSECT
SELECT empno,ename,job,sal,deptno FROM emp;
范例：验证MINUS操作
SELECT empno,ename,job,sal,deptno FROM emp
    MINUS
SELECT empno,ename,job,sal,deptno FROM emp WHERE deptno=10;
以上的集合操作在进行多个查询结果连接的时候一定会使用到

思考题：
1.列出所有员工的编号，姓名，及其直接上级的编号，姓名，显示的结果按领导年工资的降序排列
确定所需要的数据表
      emp表：员工的编号，姓名
    emp表：上级的编号，姓名
确定已知的关联字段
    雇员和领导:emp.mgr=memp.empno;
第一步：进行emp表的自身关联
SELECT e.empno,e.ename,m.empno,m.ename
FROM emp e,emp m
WHERE e.mgr=m.empno;
第二步外连接，找到所有的雇员
SELECT e.empno,e.ename,m.empno,m.ename
FROM emp e,emp m
WHERE e.mgr=m.empno(+);
第三步：计算领导的年工资，同时进行排序，利用ORDER BY完成，在SELECT子句里定义别名
SELECT e.empno,e.ename,m.empno,m.ename,
(m.sal+NVL(m.comm,0))*12 income
FROM emp e,emp m
WHERE e.mgr=m.empno(+)
ORDER BY income;
如果现在使用的是数字型的列，那么返回的结果为null，则在程序之中会将null自动变为数字0
NVL((m.sal+NVL(m.comm,0)),0)*12 income

2.列出在部门“SALES”（销售部）工作的员工姓名，基本工资，雇佣日期，部门名称，假定不知道销售部的部门编号
确定所需要的数据表
emp表:员工姓名，基本工资，雇佣日期
dept表:部门名称
确定已知的关联字段
雇员和部门关联：emp.deptno=dept.deptno;
第一步：将emp和dept表关联
SELECT e.ename,e.sal,e.hiredate,d.dname
FROM emp e,dept d
WHERE e.deptno=d.deptno;
第二步：找到销售部
SELECT e.ename,e.sal,e.hiredate,d.dname
FROM emp e,dept d
WHERE e.deptno=d.deptno AND d.dname='SALES';

3.列出所有员工的姓名，部门名称和工资
确定所需要的数据表
    emp表:姓名,工资
    dept表:部门名称
确定已知的关联字段
雇员和部门：emp.deptno=dept.deptno
SELECT e.ename,e.sal,d.dname
FROM emp e,dept d
WHERE e.deptno=d.deptno;

4.列出所有的员工的年工资，所在部门名称，按年薪从低到高排序
确定所需要的数据表
    emp表:工资
    dept表:部门名称
确定已知的关联字段
雇员和部门：emp.deptno=dept.deptno
SELECT (e.sal+NVL(e.comm,0))*12 income,d.dname
FROM emp e,dept d
WHERE e.deptno=d.deptno
ORDER BY income;

5.查出某个员工的上级主管及所在部门名称，并要求出这些主管中的薪水超过3000
确定所需要的数据表
    emp表：需要的mgr字段确定领导
    emp表：找到领导的姓名和工资；
    dept表：领导部门名称
确定已知的关联字段
    雇员和领导：emp.mgr=memp.empno
    雇员(领导)和部门：emp.deptno=dept.deptno
第一步：找到薪水超过3000的领导的信息
SELECT m.ename,m.sal
FROM emp e,emp m
WHERE e.mgr=m.empno AND m.sal>3000;
第二步：找到部门名称同时消除重复数据
SELECT DISTINCT m.ename,m.sal,d.dname
FROM emp e,emp m,dept d
WHERE e.mgr=m.empno AND m.sal>3000 AND m.deptno=d.deptno;