Operations Common SQL commands (Oracle)

Copy table structure and table data:
CREATE TABLE Table_name_new as SELECT * from Table_name_old

Copy table structure:
CREATE TABLE table_name_new like Table_name_old

To copy table data:
INSERT INTO k20_jdbc_tmp select * from K20_JDBC;


Set to NULL field data is-1:
Update k20_jdbc_tmp set Jdbctime_ms=-1 WHERE Jdbctime_ms is NULL;

To clear the table data:
TRUNCATE TABLE k20_jdbc_tmp

Rename Table name:
ALTER TABLE old_name RENAME to New_name
Or
Rename table to Table_new


Syntax for adding fields:
ALTER TABLE tablename Add (column datatype [default value][null/not null],....);

ALTER TABLE K20_JDBC_TMP2 add T1 CHAR (default null); Default allowed is empty


To modify the syntax of a field:
ALTER TABLE tablename Modify (column datatype [default value][null/not null],....);
ALTER TABLE K20_JDBC_TMP2 Modify T1 CHAR (+) default null;

Syntax for deleting a field:
ALTER TABLE tablename drop (column);
ALTER TABLE K20_JDBC_TMP2 drop column T1;


Comparison of two table data differences:
SELECT * from B minus select * from A
Selecet pno,cno from A minus select Pno,cno from B

Compares two tables with the same table structure and inserts different data into the third table.
Insert into K20_JDBC_TMP2 (Tmzdiff,writetime,node,timestamp,ip,nodename,servername,providername,jdbcname, Createcount,closecount,poolsize,freepoolsize,usedmax_pct,jdbctime,waittime,usetime,waitingthreadcount,jdbctime _ms,waittime_ms,usetime_ms) SELECT * from K20_jdbc minus select * from K20_jdbc_tmp

This article from "Life in the diligent, not So Ho get" blog, please be sure to keep this source http://wangjun51.blog.51cto.com/6124567/1729919

Operations Common SQL commands (Oracle)