oracle我的笔记（6）-oracle多表查询

1.        多标查询是指基于两个和两个以上的表或视图的查询，在实际应用中，查询单个表可能不能满足你的要求，（如显示sales部门位置和员工的姓名），这种情况下奥使用到（dept表和emp表）

[笛卡尔集]规定：多表查询的条件是至少不能少于表的个事-1

SQL> select * from dept;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> desc emp;
名称                                      是否为空? 类型
----------------------------------------- -------- -----------------
EMPNO                                     NOT NULL NUMBER(4)
ENAME                                              VARCHAR2(10)
JOB                                                VARCHAR2(9)
MGR                                                NUMBER(4)
HIREDATE                                           DATE
SAL                                                NUMBER(7,2)
COMM                                               NUMBER(7,2)
DEPTNO                                             NUMBER(2)

示例
显示雇员名，雇员工资及所在的部门的名字
select a1.dname,a2.ename,a2.sal from dept a1,emp a2 where a1.deptno=a2.deptno;

如何显示部门号为10的部门名，员工名和工资
select a1.dname,a2.ename,a2.sal from dept a1,emp a2 where a1.deptno=a2.deptno and a1.deptno=10;

显示各个员工的姓名，工资，及其工资的级别
SQL> select a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between a2.losal and a2.hisal;

显示雇员名，雇员工资及所在部门的名字，并按部门排序。
SQL> select a1.dname,a2.ename,a2.sal from dept a1,emp a2 where a1.deptno=a2.deptno order by a1.deptno;

2.自连接
自连接是指在通一张表的连接查询
示例：
显示某个员工的上级领导的姓名不如显示‘ford’的上级
SQL> select worke.ename,boss.ename from emp worke,emp boss where worke.mgr=boss.empno and worke.enam
e='FORD';

3.什么是子查询
子查询是指嵌入在其它sql语句中的select语句，也叫嵌套查询
1.单行子查询
单行子查询是指只返回一行数据的子查询语句
示例：如何显示与smith通一部门的所有员工
SQL> select * from emp where deptno=(select deptno from emp where ename='SMITH');

2.多行子查询
多行子查询指返回多行数据的子查询
示例;如何查询和部门10的工资相同的雇员的名字、岗位、工资、部门号
SQL> select * from emp where job in (select distinct job from emp where deptno=10);

3.在多行子查询中使用all操作符
示例:
如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
SQL> select ename,sal,deptno from emp where sal>all (select sal from emp where deptno=30);
或SQL> select  ename,sal,deptno from emp where sal>(select max(sal) from emp where deptno=30);
不过下面那句效率比较高

4.在多行子查询中使用any操作符
示例：如何显示工资比部门30的任意一个员工的工资高的员工的姓名、工资和部门号
SQL> select ename,sal,deptno from emp where sal>any (select sal from emp where deptno=30);
或SQL> select  ename,sal,deptno from emp where sal>any(select min(sal) from emp where deptno=30);

5.多列子查询
单行子查询是指查询只返回单列、单行数据，多行子查询是指返回多行数据，都是针对单列而言的，而多列子查询则是指返回多个列数据的子查询语句
示例：如何查询与smith的部门和岗位完全相同的所有雇员
SQL> select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');
其中(deptno,job)=(select deptno,job这两个顺序要一样

oracle数据查询时  是从右往左开始执行的

 

在from字句中使用子查询
示例：如何显示高自己于部门平均工资的员工的信息
思路：先查出每个部门的平均工资
select deptno ,avg(sal) mysal from emp group by deptno;
然后在把查出的平均工资当作一个表
SQL> select a2.ename,a2.sal,a2.deptno,a1.mysal from emp a2,(select deptno,avg(sal) mysal from emp group by deptno) a1 where a2.deptno=a1.deptno and a2.sal>a1.mysal;

要点：当在from字句中使用子查询时，该子查询会呗作为一个视图来对待，因此叫作内嵌视图，当在from字句中使用子查询时，必须给子查询指定别名，如上面下划线部分，别名为a1.（给表取别名不能加as给列取别名可以加as）

分页查询
示例：按雇员的id号升序取出，显示第十条到第六条之间的
SQL> select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum<=10) where rn>=6;
其中：1.显示rownum[oracle分配的]2.指定查询列，只需修改最里层的子查询。3.如何排序，只需修改最里层的子查询。里面rownum自能出现一次如select a1.*,rownum rn from (select * from emp) a1 where rownum<=10 and rownum<=6;会出错。

分页分为三种
1.根据rowid来分
select * from t_xiaoxi where rowid in(select rid from (select rownum rn,rid from (select rowid rid,cid from t_xiaoxi order by cid desc) where rownum<10000)where rn>9980)order by cid desc;
执行时间0.03秒
2.按分析函数来分
select * from (select t.*,row_number() over(order by cid desc) rk from t_xiaoxi t)where rownum<10000) where rn>9980;
执行时间1.01秒
3.按rownum来分
select * from(select t.*,rownum rn from (selec * from t_xiaoxi order by cid desc) t where rownum<10000) where rn>9800;
z执行时间0.1秒
其中t_xiaoxi为表名称，cid为表的关键字段，取按cid降序排序后的第9981-9999条记录，t_xiaoxi表有700000多条记录
个人感觉1的效率最好，3次之，2最差

用查询结果创建新表
这个命令是一种快捷的建表方法
create table myemp (id,ename,sal) as select empno,ename,sal from emp;

合并查询
有时在集合操作符号实践应用中，为了合并多个select语句的结果，可以使用union，union all，intersect，minus
1）.union
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。
示例：
单独查询
SQL> select ename,sal,job from emp where sal>2500;
ENAME             SAL JOB
---------- ---------- ---------
JONES            2975 MANAGER
BLAKE            2850 MANAGER
SCOTT            3000 ANALYST
KING             5000 PRESIDENT
FORD             3000 ANALYST

SQL>  select ename,sal,job from emp where job='MANAGER';
ENAME             SAL JOB
---------- ---------- ---------
JONES            2975 MANAGER
BLAKE            2850 MANAGER
CLARK            2450 MANAGER
合并查询
SQL> select ename,sal,job from emp where sal>2500 union
  2  select ename,sal,job from emp where job='MANAGER';
ENAME             SAL JOB
---------- ---------- ---------
BLAKE            2850 MANAGER
CLARK            2450 MANAGER
FORD             3000 ANALYST
JONES            2975 MANAGER
KING             5000 PRESIDENT
SCOTT            3000 ANALYST
已选择6行。

2）.union all
该操作赋予union相似，但是它不会取消重复行，而且不会排序。
示例：
select ename,sal,job from emp where sal>2500
union all select ename,sal,job from emp where job='manager';
该操作符用于取得两个结果集的并集，当使用该操作符时，会自动去掉结果集中重复行。

3）.intersect
使用该操作符用于取得两个结果集的交集
select ename,sal,job from emp where sal>2500 intersect
select ename,sal,job from emp where job='manager';

4).munus
使用该操作符用于取得两个结果集的差集，它只会显示存在第一个集合中，而不存在第二个集合中的数据。
select ename,sal,job from emp where sal>2500 minus