视图和索引

视图

• 是一个虚拟表,介于表和结果集之间

--如果没有权限,登录sys管理员身份进行授权,使用完毕以后回收权限
GRANT DBA TO SCOTT;
REVOKE DBA FROM SCOTT;

--create or replace view 视图名 as select语句 [with read only];
create or replace view vw_emp_temp as select * from emp with read only;

select * from vw_emp_temp;

--drop view 视图名; 
drop view vw_emp_temp;
 
--查询公司中所有部门的经理人的平均工资最低的那个部门名称

--查询所有的经理人
select mgr from emp where mgr is not null;
--创建一个试图,存储所有经理人编号
create or replace view vw_emp_mgr as select mgr from emp where mgr is not null;
select * from vw_emp_mgr;
--对所有的经理人进行分组
select deptno
  from emp
 where empno in (select mgr from emp where mgr is not null)
 group by deptno;
--对每组所有的经理人进行求平局薪资,以组为单位(部门)
select deptno, avg(sal)
  from emp
 where empno in (select mgr from emp where mgr is not null)
 group by deptno;
--最低的那个部门的平均薪资值
select min(avg(sal))
  from emp
 where empno in (select mgr from emp where mgr is not null)
 group by deptno;
--哪一组的平均薪资与最低值相等,拿这个组的部门编号
select deptno
  from (select deptno, avg(sal) avg_sal
          from emp
         where empno in (select mgr from emp where mgr is not null)
         group by deptno)
 where avg_sal =
       (select min(avg(sal))
          from emp
         where empno in (select mgr from emp where mgr is not null)
         group by deptno);
--根据部门编号去部门表中找到部门名称
select dname
  from dept
 where deptno =
       (select deptno
          from (select deptno, avg(sal) avg_sal
                  from emp
                 where empno in (select mgr from emp where mgr is not null)
                 group by deptno)
         where avg_sal =
               (select min(avg(sal))
                  from emp
                 where empno in (select mgr from emp where mgr is not null)
                 group by deptno));
                 
--使用试图进行优化
select dname
  from dept
 where deptno =
       (select deptno
          from (select deptno, avg(sal) avg_sal
                  from emp
                 where empno in (select * from vw_emp_mgr)
                 group by deptno)
         where avg_sal =
               (select min(avg(sal))
                  from emp
                 where empno in (select * from vw_emp_mgr)
                 group by deptno)); 

索引

• 数据库的对象之一,需要维护的,相当于字典的目录

• 有没有添加索引,对于数据库中的表的使用完全没有影响,索引是透明的

• 可以帮助我们提高查询效率

• 大量的数据的频繁查询是有效,提高查询效率,如果大量做增删改,反而会降低效率

• oracle会自动为表的主键添加索引

  --创建索引
  --create index 索引名 on表名 (字段列表...)
  create index emp_sal on emp(sal);
  --删除索引
  --drop index 索引名
  drop index emp_sal;
  --使用索引
  select * from emp where sal>1500;