oracle实用基本操作

1、建表

create table t_test0819 ( id number,name varchar2(32),age number(3),birthday date);

2、建序列

create sequence se_test0819
start with 1
increment by 1
minvalue 1
maxvalue 9999999999999999999

alter table t_test0819 add constraints se_test0819 primary key (id)

 

3.建索引

create index ind_test0819 on t_test0819(age)

4.表结构的操作

alter table t_test0819 add  id1 number;--增加一列id1
alter table t_test0819 rename  column id1 to agee;--改列名
alter table t_test0819 drop column agee;--删除列agee
alter table t_test0819 modify name varchar(64);--把列name的数据类型改为varchar(64)

4.基本的增删除改查

insert into t_test0819(id,name,age,birthday) values (se_test0819.nextval,'tom',23,to_date('2013-01-01','yyyy-mm-dd'))
update t_test0819 set age=100 where id=1;
delete from t_test0819 where id=2;
select * from t_test0819;

5.建函数

create function fun_test0819(id number)
return varchar2
as 
name varchar2(20);
begin 
  for ids in (select name from t_test0819 where id between 0 and id) loop
    name:=name||'=>'||ids.name;
    end loop;
    return name;
    end fun_test0819;

select fun_test0819(20) from dual

6.建存储过程

create procedure pro_t_test0819(num in varchar2)
as
i number; 
begin
  i:=0;
  while i<num loop
    i:=i+1;
    insert into t_test0819(id,name,age,birthday) values (se_test0819.nextval,'tom',i,to_date('2013-01-01','yyyy-mm-dd'));
    commit;
    end loop;
    end  pro_t_test0819;
    

declare
  i number;
begin 
  i:=5;
  pro_t_test0819(i);
  end;

select * from t_test0819

6.常用操作

delete from t_test0819 t
where t.rowid>(select min(rowid) from t_test0819 t1 where t1.name=t.name)--删除相同数据留一条

select * from user_recyclebin--查找删除的表
flashback  TABLE a TO BEFORE DROP;--利用闪回恢复表
select * from t_test0819 as of timestamp sysdate-1/(24)--1小时前的表

select * from t_test0819 t inner join (select * from t_test0819 where age=1) t1 on t.id=t1.id--内连接，只返关联的数据
select * from t_test0819 t left join (select * from t_test0819 where age=1) t1 on t.id=t1.id--左连接，返回on左边的表的所有数据和右边表关联数据
select * from t_test0819 t right join (select * from t_test0819 where age between 1 and 3) t1 on t.id=t1.id--右连接，返回on右边的表的所有数据和左边表关联数据
select * from t_test0819 t full join (select * from t_test0819 where age between 1 and 3) t1 on t.id=t1.id--全连接，返回关联数据和两表没关联数据
select * from t_test0819 t cross join (select * from t_test0819 where age between 1 and 3) t1 --返回M*N条数据

select * from t_test0819 t where exists (select * from t_test0819 where age between 1 and 3 and id=t.id )--exists的作用等同于in
select * from t_test0819 t where not exists (select * from t_test0819 where age between 1 and 3 and id=t.id )--exists的作用等同于not in 
select * from t_test0819 t union all (select * from t_test0819 where age between 1 and 3  )--将两表相同列的数据拼在一起，要求列数目一样，数据类型一样（包含相同数据）
select * from t_test0819 t union (select * from t_test0819 where age between 1 and 3  )--将两表相同列的数据拼在一起，要求列数目一样，数据类型一样（不包含相同数据）
select case age when 1 then 100 when 2 then 200 else 300 end from t_test0819 --当列age为何值时，输出什么