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为何值时,输出什么