oracle心得4--集合查询

1. 简单介绍集合查询 关键字 并集：union（返回两个集合去掉重复元素后的所有记录） union all （返回两个集合的所有记录，包括重复的） 交集：intersect （返回同时属于两个集合的记录） 差集：minus (属于第一个集合，但不属于第二个集合的记录) select语

1.简单介绍集合查询

关键字

并集：union（返回两个集合去掉重复元素后的所有记录）

union all （返回两个集合的所有记录，包括重复的）

交集：intersect （返回同时属于两个集合的记录）

差集：minus (属于第一个集合，但不属于第二个集合的记录)

select语句中参数类型和个数要一致;可以使用括号改变集合执行的顺序;集合运算采用第一个语句的表头作为表头;Order by子句 只能出现在最后一个语句上，第一个select语句接受的列名称或者别名 （查询结果的列名）order by ** 必须与第一个select的列名一致

案例分析

--并集

select employee_id,job_id from employees

union

select employee_id,job_id from job_history;

--所有并集

select employee_id,job_id from employees

unionall

select employee_id,job_id from job_history;

--交集

select employee_id,job_id from employees

intersect

select employee_id,job_id from job_history;

--差集

select ename,sal from emp where sal between700and1300

minus

select ename,sal from emp where sal between1201and1400

前面介绍的查询还局限在数据库的一张表内。但在实际应用中，我们经常需要在多张表中查询数据或者需要对表中的数据进行分类、汇总等。这就需要较为复杂的高级查询

2.表关系

多个表之间关系：与mysq一样存在三种表关系：一对多|(多对一) 、多对多 、一对一 ；

关系的完整性约束：实体完整性、参照完整性、用于定义的完整性。 必须满足实体完整性和参照完整性.实体完整性：规定了字段|属性的约束。参照完整性：关系与关系之间的引用某个字段的约束、外键；

备注：实体完整性及参照完整性是任何关系数据库必须满足的条件。

用户定义完整性：举例：在学生表中学生的年龄不能够大于60（用户自定义的条件）

3.连接查询

Oracle中的连接与mysql一样，同样存在自连接查询、外连接（包括左外连接、右外连接、全外连接）、还有内连接（等值连接、不等连接、自然连接）；下面将详细介绍。

在emp表与dept表之间存在着多对一的关联关系（现实中还有其他的关联）,往往我们希望查询出更多信息,这时候我们就要用到连接查询。

//查询员工及部门的详细信息 但是会产生一个笛卡尔积的效果

SQL> select * from emp,dept;

//怎么避免笛卡尔积呢?加入where查询条件引用关系的比较

SQL> select * from emp e,dept d where e.deptno=d.deptno;

//注意 以下写法是有问题的：ORA-00918: 未明确定义列

SQL> select e.empno,e.ename,deptno,d.dname from emp e,dept d where e.deptno=d.deptno;

备注说明：deptno在两个表中都存在，所以一定要使用前缀区分。

SQL> select e.empno,e.ename,e.deptno,d.dname from emp e,dept d where e.deptno=d.deptno;

综上所述 创建连接查询时应遵循如下规则：

from子句应当包括所有的表名；where子句应定义连接条件两个表1一个等值条件 三个表 2个等值条件…依次类推。连接 n个表,至少需要 n-1个连接条件。 例如：连接三个表，至少需要两个连接条件。当列名为多个表共有时,列名必须被限制。

使用join连接查询

语法： From table1 join_type table2 on join_condition

Join_type连接类型分类如下：

内连接：

内连接根据所使用的比较方式不同,把内连接分为了：

等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。例：select * from emp e inner join dept d on e.deptno = d.deptno;

不等连接：在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、、!即！=。 例：select * from emp e inner join dept d on e.deptno>d.deptno;

自然连接：注意区分与自连接查询的区别；在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。例：select * from emp natural join dept;

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

外连接：

内连接的查询结果都是满足连接条件的记录。但是，有时我们也希望输出那些不满足连接条件的记录的信息。比如，我们想知道这个部门中所有员工的情况,也包括没有员工的部门，这时就需要使用外连接。外连接是只限制一张表中的数据必须满足连接条件，而另一张表中的数据可以不满足连接条件的连接方式。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;

　　全外连接查询中所有表中的元组信息都得到了保留。

交叉联接：

　交叉连接即笛卡儿乘积，是指两个关系中所有记录的任意组合。一般情况下，交叉查询是没有实际意义的。

SQL> select * from emp e cross join dept d;

备注：可以添加where子句筛选出有意义的数据。建议不使用。

自连接查询：

如果在一个连接查询中，涉及到的两个表都是同一个表，这种查询称为自连接查询；而内自然连接查询的两个表数据，关键字是natural。同一张表在FROM字句中多次出现，为了区别该表的每一次出现，需要为表定义一个别名。自连接是一种特殊的内连接，它是指相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表。

　　例如：要求检索出员工编号为7369的上司的详细信息，查询语句为;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;

在NATURAL JOIN 子句创建等值连接时，可以使用 USING 子句指定等值连接中需要用到的列。 使用 USING 可以在有多个列满足条件时进行选择。 不要给选中的列中加上表名前缀或别名。NATURAL JOIN 和 USING 子句经常同时使用。

SQL> select * from emp e join dept d using(deptno);