How does one add a column to the middle of a table(ZT)

For example, in MySQL the following syntax is valid:

ALTER TABLE tablename ADD columnname AFTER columnname;

Oracle does not support this syntax. However, it doesn't mean that it cannot be done.

一. Solution1

There is a table T with col1 and col2 and you want to add a new column col3 after col1.

1. Rename the column col2 to col3 as:
ALTER TABLE tablename RENAME COLUMN col2 to col3;

2. Add a new column to the table
alter table t1 add (col2 datatype);

3.Now finally interchange the data contained in the two column:
UPDATE EMPLOYEE SET col2 = col3 ,col3 = col2;

commit;

Note: Data types of the interchanged columns should match.

二.Solution2

SQL>create table test (ename varchar2(20),salary number);
Table created

SQL>desc test;
Name Null? Type
---------------------- ----------- ---------------
ENAME VARCHAR2(20) SALARY NUMBER

SQL>rename test to test1;
Table renamed
SQL>create table test2 (id varchar2(20));
Table created
SQL>create table test as(select test1.ename,test2.id,test1.salary from test1,test2);
Table created
........................................................................................
SQL>desc test;
Name Null? Type
----------------------------------------- -------- --------------
ENAME VARCHAR2(20)
ID VARCHAR2(20)
SALARY NUMBER

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/628922/viewspace-712580/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/628922/viewspace-712580/