多表查询操作

1.多表查询：就是在一条查询语句中,从多张表里一起取出所需要的数据
多表查询的基本语法
  select [distinct] * | 列名称 [as] [列别名] ,
  列名称 [as] [列别名],...
  from 表名称 1 [表别名 1], 表名称 2 [表别名 2],...
  [where 条件(s)]
  [order by 排序的字段1 asc|desc,
  排序的字段2 asc|desc,...];

2.表的连接操作

表的连接操作	描述
内连接	也称为连接,还可以被称为普通连接,内连接是不满足匹配条件的记录不显示的连接
外连接	外连接是特定表中的数据全部显示的连接

外连接	描述	利用Oracle提供的+实现
左外连接	简称左连接,是左表中的数据，全部显示的连接	where子句的连接条件如下: 左关系属性=右关系属性(+)
右外连接	简称右连接,是右表中的数据，全部显示的连接	where子句的连接条件如下: 左关系属性(+)=右关系属性
全外连接	简称全连接, 是左表右表中的数据全部显示的连接

3.外连接语法
select [distinct] * | 列名称 [as] [列别名],
列名称 [as] [列别名],...
from 表1 表别名1
[left | right |full outer join 表2 on(关联条件)]
[where 条件(s)]
[order by 排序的字段1 asc|desc,
排序的字段2 asc|desc,...];

4.using子句的语法
select [distinct] * | 列名称 [as] [列别名],
列名称 [as] [列别名],...
from 表1 表别名1
[join 表2 using(关联列名称)]
[where 条件(s)]
[order by 排序的字段1 asc|desc,
排序的字段2 asc|desc,...];

5.on子句语法

select [distinct] * | 列名称 [as] [列别名],
列名称 [as] [列别名],...
from 表1 表别名1
[join 表2 on(关联条件)]
[where 条件(s)]
[order by 排序的字段1 asc|desc,
排序的字段2 asc|desc,...];
using子句指定的关联列名前不能出现表名或表别名,select子句中在该列也如此

具体实例可参考如下：

/* 查询每个雇员的编号、姓名、职位、基本工资、
  部门名称、部门位置等信息*/

select empno,ename,job,sal,emp.deptno,dname,loc,dept.deptno
from emp,dept
where emp.deptno=dept.deptno;

select e.empno,e.ename,e.job,e.sal,e.deptno,d.dname,d.loc,d.deptno
from emp e,dept d
where e.deptno=d.deptno;
/*查询出每个雇员的编号、姓名、雇佣日期、基本工资、
  工资等级*/

select e.empno,e.ename,e.hiredate,e.sal,s.grade,s.losal,s.hisal
from emp e,salgrade s
where e.sal between s.losal and s.hisal;
/*查询每个雇员的姓名、职位、基本工资、部门名称、工资
  所在公司的工资等级*/
select e.empno,e.ename,e.job,e.sal,d.dname,s.grade,s.losal,s.hisal
from emp e,dept d,salgrade s 
where e.deptno=d.deptno and e.sal between s.losal and s.hisal;

select d.deptno,d.dname,d.loc,e.*
from dept d,emp e 
where d.deptno=e.deptno;
/*+号方式做左外连接实现该任务，左表全部显示*/
select d.deptno,d.dname,d.loc,e.*
from dept d,emp e 
where d.deptno=e.deptno(+); 
/*左外连接，左表中的记录会全部显示，右表中如果不能找到与左表中的记录匹配的记录，则一null记录来显示*/
/*+号方式做右外连接实现该任务，右表全部显示*/
select e.*, d.deptno,d.dname,d.loc
from emp e, dept d
where e.deptno(+)= d.deptno;
/*右外连接，右表中的记录会全部显示，左表中如果不能找到与右表中的记录匹配的记录，则一null记录来显示*/
--sq199语法左外连接
select d.deptno,d.dname,d.loc,e.*
from dept d
left outer join emp e
on(d.deptno=e.deptno);
--右外连接
select e.*，d.deptno,d.dname,d.loc
from emp e
right outer join dept d
on(e.deptno=d.deptno);


/*自关联*/
/*查询出每个雇员的编号、姓名及其上级领导的编号、姓名*/
/*因为7839的雇员没有上级领导，所以该语句没有得到每个雇员的编号、姓名及其上级领导的编号、姓名*/
select e.empno,e.ename,e.mgr,m.empno,m.ename
from emp e,emp m
where e.mgr=m.empno;
/*左外连接*/
select e.empno,e.ename,e.mgr,m.empno,m.ename
from emp e,emp m
where e.mgr=m.empno(+);
--全外连接
select e.*，d.deptno,d.dname,d.loc
from emp e
full outer join dept d
on(e.deptno=d.deptno);

/*查询出在1981年雇佣的全部雇员的编号、姓名、雇佣日期
  (按照年-月-日显示)、工作、领导姓名、雇员月工资、
  雇员年工资(基本工资+奖金),雇员工资等级、部门编号、
  部门名称、部门位置,并且要求这些雇员的月基本工资在
  1500~3500之间,将最后的结果按照年工资的降序排列,
  如果年工资相等,则按照工作时间进行排序*/
select e.empno,e.ename,to_char(e.hiredate,'yyyy-mm-dd'),e.job,e.mgr,m.empno,
m.ename,e.sal+nvl(e.comm,0),(e.sal+nvl(e.comm,0))*12,s.grade,s.losal,s.hisal,
d.deptno,d.dname,d.loc
from emp e,emp m,dept d,salgrade s
where e.mgr=m.empno(+) and e.deptno=d.deptno and e.sal between s.losal and s.hisal
and e.sal between 1500 and 3500 and to_char(e.hiredate,'yyyy')=1981
order by (e.sal+nvl(e.comm,0))*12 desc,e.hiredate asc;

--要查询出每个员工的编号,姓名及其及其所在部门的编号，部门名字
--实现1
select e.empno,e.ename,d.deptno,d.dname
from emp e,dept d
where e.deptno=d.deptno;
--实现2
select e.empno,e.ename,deptno,d.dname
from emp e
join dept d
using(deptno);
--实现3
select e.empno,e.ename,d.deptno,d.dname
from emp e
join dept d
on(e.deptno=d.deptno);

6.数据的集合运算
集合运算是将多个查询的结果连接到一起

集合运算	运算符	描述
并集操作	union	返回若干个查询结果的全部内容,但是重复记录不显示
差集操作	minus	返回若干个查询结果中的不同部分
交集操作	intersect	返回若干个查询结果中的相同部分

7.操作集合的语法
查询语句
[union |  intersect | minus]
查询语句

--查询出办事员,销售员的信息
--实现1
select *
from emp
where job in('CLERK','SALESMAN');
--实现2
select *
from emp
where job ='CLERK'or job='SALESMAN';
--实现3
--第一步：查询出办事员的信息
select *
from emp
where job ='CLERK';
--第二步：查询出销售员的信息
select *
from emp
where job='SALESMAN';
--第三步：查询出办事员与销售员的信息
select *
from emp
where job ='CLERK'
union
select *
from emp
where job='SALESMAN';
--验证union
--第一步：查询出员工表中的所有记录
select *
from emp;
--第二步：查询出10部门的员工
select *
from emp
where deptno=10;
--第三步：将第一步和第二部的查询记录并集运算，员工表中的所有记录并10部门的员工
select *
from emp
union
select *
from emp
where deptno=10;
--验证intersect
--第一步：查询出员工表中的所有记录
select *
from emp;
--第二步：查询出10部门的员工
select *
from emp
where deptno=10;
--第三步：将第一步和第二部的查询记录交集运算
select *
from emp
intersect
select *
from emp
where deptno=10;
--验证minus
--第一步：查询出员工表中的所有记录
select *
from emp;
--第二步：查询出10部门的员工
select *
from emp
where deptno=10;
--第三步：将第一步和第二部的查询记录差集运算
select *
from emp
minus
select *
from emp
where deptno=10;