--直接创建表并且插入数据,依赖于后面的select语句的结果 create table test11 as select * from test1; --只复制表结构,约束不赋值。1=0不查询记录,只复制基本结构。 create table test12 as select * from test1 where 1=0; --通过select语句查询的记录插入到指定表的所有列或者指定列中。 insert into test12(a,b) select teset11.a,test11.b from test11; --单表插多行 --多表插多行 insert all into test11(a,b) into test12(a,b) select teset11.a,test11.b from test10; --带条件插入 insert all when a>1 then into test11(a,b) when b>1 then into test12(a,b) else into test13(a,b) select from test10; --插入规则是如果前面的when 匹配过的,下一个when 会自动不考虑上面已经匹配过的行记录。 insert first when a>1 then into test11(a,b) when b>1 then into test12(a,b) else into test13(a,b) select teset11.a,test11.b from test10; --旋转插入 insert all into sales_info values(employee_id,week_id,sales_mon) --分别按每个工作日插入 into sales_info values(employee_id,week_id,sales_tue) into sales_info values(employee_id,week_id,sales_wed) into sales_info values(employee_id,week_id,sales_thur) into sales_info values(employee_id,week_id,sales_fri) select employee_id,week_id,sales_mon,sales_tue,sales_wed,sales_thur,sales_fri from sales_source_data; select * from sales_source_data; ------------------ 1 5 10 20 30 40 50 select * from sales_info; ------------ 1 5 10 1 5 20 1 5 30 1 5 40 1 5 50 rename test1 to test10; --重命名 --随机产生3条记录: select * from (select * from test order by dbms_random.value) where rownum<=3; --删除表中重复记录: --保留行号最大的一行,适用于有少量重复记录的情况。注意,对于有大量重复记录的情况,用以下语句效率会很低。 delete from test1 t1 where t1.rowid != ( select max(t2.rowid) from test1 t2 where t1.a=t2.a and t1.b=t2.b); --保留行号最小的一行:用于有大量重复记录的情况。在a和b列上建有索引的时候,用以下语句效率会很高: delete from test1 t1 where t1.rowid not in( select min(t2.rowid) from test1 t2 group by t2.a,t2.b);