--授予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;
Oracle之索引,视图,序列综合
最新推荐文章于 2024-06-15 20:21:51 发布