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 两列--sqlserverupdate a set a.G_Detail =(select MARKS from 表B b where a.G_NO=b.G_NO) from 表A a --oracleupdate 表A a a set a.G_Detail =(select MARKS from 表B b where a.G_NO=b.G_NO)