oracle笔记（十四）Oracle的高级查询2

1、  交叉联接

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

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

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

2、  自连接查询

如果在一个连接查询中，涉及到的两个表都是同一个表，这种查询称为自连接查询。同一张表在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;的理解?

l          使用外连接可以查询不满足连接条件的数据。

l          外连接的符号是 (+)。

l          在NATURAL JOIN 子句创建等值连接时，可以使用 USING 子句指定等值连接中需要用到的列。

l          使用 USING 可以在有多个列满足条件时进行选择。

l          不要给选中的列中加上表名前缀或别名。

l          NATURAL JOIN 和 USING 子句经常同时使用。

//理解

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

3、  集合操作

集合操作符有union、intersect、minus

5.1、union运算符可以将多个查询结果集相加，形成一个结果集，要是用union操作，源表列必须一致，并且必须从每个表中读取相同个数的列。语法格式如下：

Select columns from table union select column from table union …

   5.2、intersect与union指令相似，intersect也是对两个sql语句产生的结果做处理的。不同的是，union像or运算，而intersect像and运算，使用intersect求两个查询结果的交集，语法如下：

Select columns from table intersect select column from table union …

   5.3、minus集合运算符是非常重要的，该运算符可以找到给定的两个集合之间的差异意味着找到一个数值集合，其中的元素仅存在于前一个集合，而不存在于另一个集合中，格式如下：

Select columns from table Minus select column from table union …

所需要的结果在前一个select中，不在后一个select中

 

4、  子查询

子查询概念 ：当一个查询的结果是另一个查询的条件时，称之为子查询。

使用子查询注意事项：

         子查询可以嵌套多层

         子查询需要圆括号()括起来

子查询语法：

SELECT     select_list

FROM       table

WHERE    expr operator

                           (SELECT    select_list

                          FROM                  table);

l       子查询 (内查询) 在主查询之前一次执行完成。

l       子查询的结果被主查询使用 (外查询)。

举例：查询员工的工资大于JONES的员工信息

分析过程如下：

首先：查询JONES的员工工资是多少 ：结果2975

SQL> select sal from emp where ename='JONES';

实际上我们要查询的是：薪资大于2975的员工的信息写法如下：

SQL> select * from emp where sal>2975;

//综合以上写出子查询的结果如下：

SQL> select * from emp where sal>(select sal from emp where ename='JONES');

注意：

l        子查询要包含在括号内。

l        将子查询放在比较条件的右侧。

 

根据查询的结果(内部嵌套查询的结果)把子查询的类型分为单行子查询与多行子查询,

   注意：

l        单行操作符对应单行子查询，多行操作符对应多行子查询。

单行操作符

>、>=、 <、 <= 、<>、=

举例：

//查询编号7876相同职位的员工信息 并且薪资大于编号为7521的薪资的员工信息

SQL> select * from emp where job=( select job from emp where empno=7876) and sal>( select sal from emp where empno=7521);

//子查询含有组函数

SQL> select * from emp where sal>(select avg(nvl(sal,0)) from emp);

//子查询含有having子句 查询部门的最小工资大于20号部门最小工资的部门号及最小工资数

SQL> select deptno,min(sal) from emp group by deptno having min(sal)>( select min(sal) from emp where deptno=20);

 

备注：子查询可以返回空行 没要查询到结果是可以的。

 

多行子查询

l         返回多行。

l        使用多行比较操作符。

操作符如下图：

操作符	描述
In	等于列表中的任何一个
Any	子查询返回的任意一个值比较 相同还有some
All	和子查询返回的所有值比较
Exists

 

//查询薪水小于工作岗位CLERK的任何一个薪资的员工信息 并且不包含工作岗位为CLERK的员工信息

SQL> select * from emp where sal < any (select sal from emp where job='CLERK') and job<>'CLERK';

//all与所有值比较 >all 代表的是大于查询结果的最大值

SQL> select * from emp where sal > all (select sal from emp where job='CLERK') and job<>'CLERK';

//查询岗位与部门编号为10相同的员工信息 不包含自己。

SQL> select * from emp where job in(select job from emp where deptno=10) and deptno<>10;

 

SQL> select * from emp where exists(select * from dept);

 

EMPNO ENAME  JOB        MGR HIREDATE       SAL      COMM DEPTNO