Oracle 常用操作【01】修改、更新数据

1. oracle 修改表名、列名、字段类型、添加表列、删除表列 

alter table scott.test rename to test1--修改表名
alter table scott.test rename column name to name1 --修改表列名
alter table scott.test modify name1 number(20) --修改字段类型
alter table scott.test add address varchar2(40) --添加表列
alter table scott.test drop name cascadeconstraints --删除表列

2. 将一个表B的（某几个字段的数据)复制到新表A(某几个不同的字段)中

insert into tableA (A, B) select C,D from tableB t;

3. 取出一个表集合，循环（更新，删除,）另外一个表属性（数据，注释，结构）,多用于数据表局部同步数据

begin
  for i in (select A.a, A.b from A) loop
    execute immediate 'update B set B.c =' || i.a || ' set B.d=' || i.b;
  end loop;
end;

在字符串型的变量前面记得加 chr(39);

4. 更新字段数据为大写（小写）

  update A set A.a= upper(A.a); //大写
  update B set B.b = lower(B.b); //小写

5. 更新数据表字段顺序(有两种方法)

方法一：

a.将原表的建表SQL，通过查看表结构，粘贴下来；

b.将准备更新表字段顺序的数据表全表插入到临时表中；

c.drop 掉你准备更新数据表；

d.修改你刚才粘贴出来的原表建表语句，修改其中的字段的顺序为你想要的；

e.将临时表中的数据插入到刚创建好的表，drop 掉临时表；

 

方法二：

a.查看数据表物理ID；

select object_id from all_objects where owner='表的所有者' and object_name='具体的表';

b.通过上面的物理ID查看表的字段对应的物理顺序;

select obj#,col#,name from sys.col$ where obj#=131347 order by col#;

c.更新对应的表字段的物理顺序（这步更新需要数据库超级管理员的权限，请慎用）：

update sys.col$ set col#=5 where obj#=131347 and name='JKSE';

d.更新完毕之后，重启数据库；

   两种方法对比，如果你们项目是协同开发，一个数据库是多个人在使用，方法二并不适合你，因为你要重启数据库，会耽误别人的开发时间。

   如果你SQL 语句写的比较熟练，第一种方法，只会占用你大概5分钟的时间，就能够办到你的想法；

6. 查询和删除重复记录

1、查找表中多余的重复记录，重复记录是根据单个字段（Id）来判断

select * from 表 where Id in (select Id from 表 group byId having count(Id) > 1)

2、删除表中多余的重复记录，重复记录是根据单个字段（Id）来判断，只留有rowid最小的记录

DELETE from 表 WHERE (id) IN ( SELECT id FROM 表 GROUP BY id HAVING COUNT(id) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM 表 GROUP BY id HAVING COUNT(*) > 1);

3、查找表中多余的重复记录（多个字段）

select * from 表 a where (a.Id,a.seq) in(select Id,seq from 表 group by Id,seq having count(*) > 1)

4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录

delete from 表 a where (a.Id,a.seq) in (select Id,seq from 表 group by Id,seq having count(*) > 1) and rowid not in (select min(rowid) from 表 group by Id,seq having count(*)>1)

5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录

select * from 表 a where (a.Id,a.seq) in (select Id,seq from 表 group by Id,seq having count(*) > 1) and rowid not in (select min(rowid) from 表 group by Id,seq having count(*)>1)