Oracle数据库常用命令记录

1.Sql建表

CREATE  TABLE  AAABBBCCCDDD(
     ID VARCHAR2(50) primary key,
    AAAAAAAA   VARCHAR2(50) not NULL,
    BBBBBBBB   VARCHAR2(50),
    CCCCCCCC   VARCHAR2(50),
    DDDDDDD    VARCHAR2(500),
)

2.新增字段

REVISE_TIME  

ALTER TABLE  ACCEPT_SYSTEM  ADD  REVISE_TIME  DATE

3.清表

truncate table AAAAA;
truncate table BBBBB;

4.修改表名

ALTER TABLE rejion_check RENAME TO region_check;

5.用Excel拼装SQL的insert语句

=CONCATENATE("insert into ex_organization_check 
　　　　　　　　(ID,STANDARD_CODE,CURRENT_CODE,REGION_CODE,UNITE_SOCIAL_CODE,CREAT_TIME) 
 　　　　　　　　values (sys_guid(),'",F3,"','",D3,"','",C3,"','",G3,"',SYSDATE);")

6.在某字段有值不为空的情况下，修改该字段的属性

 1. 把原字段换个名字，address改为myaddress
  ● alter table t_person rename column address to myaddress;  
2.在表中添加一个原字段名字 address 并把类型定义为自己想改变的类型
  ● alter table t_person add address number(10); 
3.把备份的myaddress字段内容添加到新建字段address中来
  ● update t_person set address = myaddress; 
4.把备份字段myaddress删除
  ● alter table t_person drop column myaddress;

7.查询所有表名

 select table_name from user_tables

8.锁表查询

SELECT object_name, machine, s.sid, s.serial# 
FROM gv$locked_object l, dba_objects o, gv$session s 
WHERE l.object_id　= o.object_id  AND l.session_id = s.sid; 

9.释放锁的表

--alter system kill session 'sid, serial#'; 
ALTER system kill session '23, 1647'; 

10.oracle扩充字段长度

alter table ACCEPT_COMPANY modify ENTERPRISE_SORT_CODE varchar2(20);

11.表A和表B比较，有相同数据返回1否则返回0

select *, case when (select count(1) from b where id = a.id)>0 then 1 else 0 end as flag from a  

12.将A表中一列数据插入到B表中通过共同的字段

表A：G_NO、G_Detail 两列
表B：G_NO、MARKS 两列

--sqlserver
update a set a.G_Detail =(select  MARKS from 表B b where a.G_NO=b.G_NO) from 表A a

--oracle
update 表A a a set a.G_Detail =(select MARKS from 表B b where a.G_NO=b.G_NO)