ORACLE中高级查询

ORACLE中高级查询
前面介绍的查询还局限在数据库的一张表内。但在实际应用中，我们经常需要在多张表中查询数据或者需要对表中的数据进行分类、汇总等。这就需要较为复杂的高级查询。
首先我们需要回顾一下表与表之间的关系：
多个表之间关系（3种）：一对多|(多对一) 多对多 一对一
关系的完整性约束：实体完整性、参照完整性、用户定义的完整性。 必须满足实体完整性和参照完整性.
实体完整性：规定了字段|属性的约束
参照完整性：关系与关系之间的引用 某个字段的约束 外键
用户定义完整性：例：在学生表中 学生的年龄不能够大于60（用户自定义的条件）
备注：实体完整性及参照完整性是任何关系数据库必须满足的条件。
以员工表 与部门表为例：（一对多的关联）
部门表dept：
-- Create table
create table DEPT
(
DEPTNO NUMBER(2) not null,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
)
tablespace SYSTEM
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table DEPT
add constraint DEPT_PRIMARY_KEY primary key (DEPTNO)
using index
tablespace SYSTEM
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
员工表emp：
-- Create table
create table EMP
(
EMPNO NUMBER(4) not null,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) not null
)
tablespace SYSTEM
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table EMP
add constraint EMP_PRIMARY_KEY primary key (EMPNO)
using index
tablespace SYSTEM
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
alter table EMP
add constraint EMP_FOREIGN_KEY foreign key (DEPTNO)
references DEPT (DEPTNO);
alter table EMP
add constraint EMP_SELF_KEY foreign key (MGR)
references EMP (EMPNO)
disable;
连接查询
在emp表与dept表之间存在着多对一的关联关系（现实中还有其他的关联）,往往我们希望查询出更多信息,这时候我们就要用到连接查询。
//查询员工及部门的详细信息 但是会产生一个笛卡尔积的效果
SQL> select * from emp,dept;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
----- ---------- --------- ----- ----------- --------- --------- ------ ------ -------------- -------------
7369 SMITH CLERK 7902 1980/12/17 800.00 20 10 ACCOUNTING NEW YORK
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30 10 ACCOUNTING NEW YORK
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30 10 ACCOUNTING NEW YORK
7566 JONES MANAGER 7839 1981/4/2 2975.00 20 10 ACCOUNTING NEW YORK
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30 10 ACCOUNTING NEW YORK
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 10 ACCOUNTING NEW YORK
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 1982/12/9 3000.00 20 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 1981/11/17 5000.00 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30 10 ACCOUNTING NEW YORK
7876 ADAMS CLERK 7788 1983/1/12 1100.00 20 10 ACCOUNTING NEW YORK
7900 JAMES CLERK 7698 1981/12/3 950.00 30 10 ACCOUNTING NEW YORK
7902 FORD ANALYST 7566 1981/12/3 3000.00 20 10 ACCOUNTING NEW YORK
7934 MILLER CLERK 7782 1982/1/23 1300.00 10 10 ACCOUNTING NEW YORK
7369 SMITH CLERK 7902 1980/12/17 800.00 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30 20 RESEARCH DALLAS
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30 20 RESEARCH DALLAS
7566 JONES MANAGER 7839 1981/4/2 2975.00 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30 20 RESEARCH DALLAS
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 20 RESEARCH DALLAS

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
----- ---------- --------- ----- ----------- --------- --------- ------ ------ -------------- -------------
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 1982/12/9 3000.00 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 1981/11/17 5000.00 10 20 RESEARCH DALLAS
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 1983/1/12 1100.00 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 1981/12/3 950.00 30 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 1981/12/3 3000.00 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 1982/1/23 1300.00 10 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 1980/12/17 800.00 20 30 SALES CHICAGO
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 1981/4/2 2975.00 20 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 30 SALES CHICAGO
7788 SCOTT ANALYST 7566 1982/12/9 3000.00 20 30 SALES CHICAGO
7839 KING PRESIDENT 1981/11/17 5000.00 10 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 1983/1/12 1100.00 20 30 SALES CHICAGO
7900 JAMES CLERK 7698 1981/12/3 950.00 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 1981/12/3 3000.00 20 30 SALES CHICAGO

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
----- ---------- --------- ----- ----------- --------- --------- ------ ------ -------------- -------------
7934 MILLER CLERK 7782 1982/1/23 1300.00 10 30 SALES CHICAGO
7369 SMITH CLERK 7902 1980/12/17 800.00 20 40 OPERATIONS BOSTON
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30 40 OPERATIONS BOSTON
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30 40 OPERATIONS BOSTON
7566 JONES MANAGER 7839 1981/4/2 2975.00 20 40 OPERATIONS BOSTON
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30 40 OPERATIONS BOSTON
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 40 OPERATIONS BOSTON
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 40 OPERATIONS BOSTON
7788 SCOTT ANALYST 7566 1982/12/9 3000.00 20 40 OPERATIONS BOSTON
7839 KING PRESIDENT 1981/11/17 5000.00 10 40 OPERATIONS BOSTON
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30 40 OPERATIONS BOSTON
7876 ADAMS CLERK 7788 1983/1/12 1100.00 20 40 OPERATIONS BOSTON
7900 JAMES CLERK 7698 1981/12/3 950.00 30 40 OPERATIONS BOSTON
7902 FORD ANALYST 7566 1981/12/3 3000.00 20 40 OPERATIONS BOSTON
7934 MILLER CLERK 7782 1982/1/23 1300.00 10 40 OPERATIONS BOSTON

56 rows selected
//怎么避免笛卡尔积呢?加入where查询条件 引用关系的比较？
SQL> select * from emp ,dept where emp.deptno = dept.deptno;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
----- ---------- --------- ----- ----------- --------- --------- ------ ------ -------------- -------------
7369 SMITH CLERK 7902 1980/12/17 800.00 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 1981/4/2 2975.00 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 1982/12/9 3000.00 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 1981/11/17 5000.00 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 1983/1/12 1100.00 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 1981/12/3 950.00 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 1981/12/3 3000.00 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 1982/1/23 1300.00 10 10 ACCOUNTING NEW YORK

14 rows selected
//别名查询 为表起别名 采用别名查询
SQL> select * from emp e,dept d where e.deptno=d.deptno;
该语句查询结果与前一个查询结果相同。
//注意 以下写法是有问题的：
SQL> select e.empno,e.ename,deptno,d.dname from emp e,dept d where e.deptno=d.deptno;
select e.empno,e.ename,deptno,d.dname from emp e,dept d where e.deptno=d.deptno
ORA-00918: 未明确定义列
备注说明：deptno在两个表中都存在，所以一定要使用前缀区分。

SQL> select e.empno,e.ename,e.deptno,d.dname from emp e,dept d where e.deptno=d.deptno;
EMPNO ENAME DEPTNO DNAME
----- ---------- ------ --------------
7369 SMITH 20 RESEARCH
7499 ALLEN 30 SALES
7521 WARD 30 SALES
7566 JONES 20 RESEARCH
7654 MARTIN 30 SALES
7698 BLAKE 30 SALES
7782 CLARK 10 ACCOUNTING
7788 SCOTT 20 RESEARCH
7839 KING 10 ACCOUNTING
7844 TURNER 30 SALES
7876 ADAMS 20 RESEARCH
7900 JAMES 30 SALES
7902 FORD 20 RESEARCH
7934 MILLER 10 ACCOUNTING
14 rows selected
综上所述 创建连接查询时应遵循如下规则：
1、 from子句应当包括所有的表名
2、 where子句应定义连接条件 两个表1一个等值条件 三个表 2个等值条件…依次类推。
备注：连接 n个表,至少需要 n-1个连接条件。 例如：连接三个表，至少需要两个连接条件。
3、 当列名为多个表共有时,列名必须被限制。
使用join连接查询
语法格式：
From join_table join_type join_table on join_condition
备注： Join_table连接的表名 join_type连接的类型
Join_type连接类型分类如下：
1、 内连接：
内连接根据所使用的比较方式不同,把内连接分为了：
1） 等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。
SQL> select * from emp e inner join dept d on e.deptno = d.deptno;
2） 不等连接：在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。
SQL> select * from emp e inner join dept d on e.deptno>d.deptno;
3） 自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。
SQL> select * from emp natural join dept;
SQL> select e.*,d.dname,d.loc from emp e inner join dept d on e.deptno = d.deptno;
SQL> select d.*,e.ename,e.empno,e.job,e.mgr,e.hiredate,e.sal,e.comm from emp e inner join dept d on e.deptno=d.deptno;

备注：Distinct是去掉重复的行,而自然连接是去掉重复的列。

2、 外连接
内连接的查询结果都是满足连接条件的记录。但是，有时我们也希望输出那些不满足连接条件的记录的信息。比如，我们想知道这个部门中所有员工的情况,也包括没有员工的部门，这时就需要使用外连接。外连接是只限制一张表中的数据必须满足连接条件，而另一张表中的数据可以不满足连接条件的连接方式。3种外连接：
　　1) 左外连接（LEFT OUTER JOIN）
　　如果在连接查询中，连接表左端的表中所有的记录都列出来，并且能在右端的表中找到匹配的记录，那么连接成功。如果在右端的表中，没能找到匹配的记录，那么对应的记录是空值（NULL）。这时，查询语句使用关键字 LEFT OUTER JOIN，也就是说，左外连接的含义是限制连接关键字右端的表中的数据必须满足连接条件，而不关左端的表中的数据是否满足连接条件，均输出左端表中的内容。
　　例如：要查询所有部门的员工信息查询语句为
SQL> select * from dept d left outer join emp e on e.deptno=d.deptno order by d.deptno;　　左外连接查询中左端表中的所有记录的信息都得到了保留。
备注：部门表中记录保留,如果部门中没有员工,部门显示 员工记录用null补充。
　　2）右外连接（RIGHT OUTER JOIN）
　　右外连接与左外连接类似，只是右端表中的所有元组都列出，限制左端表的数据必须满足连接条件，而不管右端表中的数据是否满足连接条件，均输出表中的内容。
　　例如：同上例内容，查询语句为
SQL> select * from emp e right outer join dept d on e.deptno=d.deptno order by d.deptno;
　　右外连接查询中右端表中的所有元组的信息都得到了保留。
　　3）全外连接（FULL OUTER JOIN）
　　全外连接查询的特点是左、右两端表中的记录都输出，如果没能找到匹配的记录，就使用NULL来代替。
　　例如：同左外连接例子内容，查询语句为
　　SQL> select * from emp e full outer join dept d on e.deptno=d.deptno order by d.deptno;
　　全外连接查询中所有表中的元组信息都得到了保留。
备注：一定分清：左与右 在join后边的是右
3、 交叉联接
　交叉连接即笛卡儿乘积，是指两个关系中所有记录的任意组合。一般情况下，交叉查询是没有实际意义的。
SQL> select * from emp e cross join dept d;
备注：可以添加where子句筛选出有意义的数据。建议不使用。
4、 自连接查询
如果在一个连接查询中，涉及到的两个表都是同一个表，这种查询称为自连接查询。同一张表在FROM字句中多次出现，为了区别该表的每一次出现，需要为表定义一个别名。自连接是一种特殊的内连接，它是指相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表。
　　例如：要求检索出员工编号为7369的上司的详细信息，查询语句为
SQL> select e1.* from emp e inner join emp e1 on e.mgr=e1.empno where e.empno=7369;
注意：对于连接查询中使用到的 inner outer是可以省略的。但为规范最好不要省略。
提高部分：（+）连接符：
//查询出两个表的所有数据 包括重复的列
SQL> select * from emp e inner join dept d on e.deptno(+)=d.deptno;
 使用外连接可以查询不满足连接条件的数据。
 外连接的符号是 (+)。

using子句
 在NATURAL JOIN 子句创建等值连接时，可以使用 USING 子句指定等值连接中需要用到的列。
 使用 USING 可以在有多个列满足条件时进行选择。
 不要给选中的列中加上表名前缀或别名。
 NATURAL JOIN 和 USING 子句经常同时使用。
//根据deptno查询员工表与部门表中的所有信息不包括重复的列
SQL> select * from emp e join dept d using(deptno);