复制表

<<复制一张表的结构>>
--数据一起复制
  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);