复制表

<<复制一张表的结构>>
--数据一起复制
create table mydept as select * from dept;
--只复制结构
create table mydept1 as select * from dept where 1=2;
--把数据从一个表复制到另一个结构相同的表
insert into mydept1 select * from dept;
--只复制部分数据
insert into mydept1 select * from dept where deptno>20;
insert into mydept1(deptno,loc) select deptno,loc
from dept;

--表的约束是复制不过来的，需要自己去添加

<<如何消重复记录>>

a.没有主键的表如何消重复记录

create table test(
id number(2),
name varchar2(10));

insert into test values (1,'aa');
insert into test values (1,'aa');
insert into test values (1,'aa');
insert into test values (2,'bb');
insert into test values (3,'cc');

create table test1 as select distinct * from
test;
或者
create table test1 as select id,name from test
group by id,name;


rename test to test2;
rename test1 to test;

b.有主键消重复行

create table test(
id number(2) primary key,
name varchar2(10));

insert into test values (1,'aa');
insert into test values (2,'aa');
insert into test values (3,'aa');
insert into test values (4,'bb');
insert into test values (5,'cc');

create table test1 as select name from test
group by name;

alter table test1 add (id number(2));

update test1 set id=(select max(id) from test
where name=test1.name);

create table test2 as select id,name from test1;

rename test to testb;
rename test2 to test;

alter table test add primary key (id);