Oracle之索引，视图，序列综合

--授予scott用户dba权限
grant dba to scott;

select * from emp;
select * from dept;
select * from salgrade;

select * from user_indexes;

--自动创建索引
alter table emp add constraint un_ename unique(ename);

--删除约束
alter table emp drop constraint un_ename;


/*
   手动创建索引
   create index 索引名 on 表名(列名1，列名2)
*/
create table bigtable(
   ename varchar2(20),
   address varchar2(30)
);

select * from bigtable;

--插入5000000条数据
declare 
begin 
  for i in 1..5000000 loop
    insert into bigtable values('姓名'||i,'地址'||i);
  end loop;
end;

select count(ename) from bigtable;

--没有索引的情况下  查询name='姓名3000000'的时间
select * from bigtable where ename='姓名3000000';

--创建索引
create index index_name on bigtable (ename) ;
select * from bigtable where ename= '姓名3000000' ;    --效率明显增加很多
--创建复合索引
create index index_bigtable on bigtable (ename, address) ;
select * from bigtable where ename='姓名3000000' and address='地址3000000' ;

--.刚除索引
drop index index_bigtable;
drop index index_name;

--删除表
drop table bigtable;
select * from tab;



/**
创建视图
   CREATE [ot replace] view viewname
   [(列名)]
   as subquery
   [with read only]
*/

--HR经理视图
create view HR_view 
as 
select * from emp;
select * from HR_VIEW;
--项目经理视图
create view MGR_VIEW
as
select empno, ename from emp;
select * from mgr_view;

--删除视图
drop view mgr_view;

--查询所有员工的基本信息，部门信息
create or replace view emp_view
as 
select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno,d.dname,d.loc
from emp e,dept d 
where e.deptno=d.deptno;

select * from emp_view;


--查询所有员工的基本信息，年薪，年收入，部门信息
create or replace view emp_view
as 
select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,(e.sal*12) as "年薪",
(e.sal*12+nvl(e.comm, 0)) as "年输入",e.comm,e.deptno,d.dname,d.loc
from emp e,dept d 
where e.deptno=d.deptno;


--统计每个部门的薪水，最高工资，最低工资，平均工资
create or replace view v_emp_sal
as
select deptno,
max(sal) as "最高工资"，
min(sal) as "最低工资"，
avg(sal) as "平均工资"
from emp
group by deptno;

select * from v_emp_sal;


/**
序列
  CREATE SEQUENCE序列名字
  START WITH序列初始值
  INCREMENT BY每次增长步长
  MAXVALUE最大值|NOMAXVALUE
  MINVALUE最小值|NOMINVALUE
  CYCLE|NOCYCLE|是否循环
  CACHE缓存的数量|NOCACHE

*/

--创建一-个循环序列
CREATE SEQUENCE seq_test1
START WITH 1
INCREMENT BY 2
MAXVALUE 30
CYCLE
CACHE 10;

--查询序列
select * from user_sequences;


--序列取值
select seq_test1.currval from dual;
--必须执行一次nextval才能产生
select seq_test1.nextval from dual;

--常用序列
create sequence seq_test2;
select seq_test2.nextval from dual;

--使用序列实现表的id自增长
create sequence seq_test4;
create table test2(
  tid number primary key,
  tname varchar2(20)
);

--将序列运用到test2表中
insert into test2 values(seq_test4.nextval,'cici');
select * from test2;

delete from test2 where tid=4;