oracle添加字段或者删除字段-转载

添加字段的语法：alter table tablename add (column datatype [default value][null/not null],….);

修改字段的类型的语法：alter table tablename modify (column datatype [default value][null/not null],….);

删除字段的语法： ALTER TABLE table_name   (MODIFY column_name column_type , .... );

 

例子：

使用一个SQL语句同时添加三个字段：

代码如下:

alter table test1
add (
　　name varchar2(30) default ‘无名氏' not null,
　　age integer default 22 not null, 
　　has_money number(9,2)

);

修改一个字段

 代码如下: 

alter table test1
modify (name varchar2(16) default ‘unknown');

　　

 

这里要注意几个地方，首先，增加和修改列是不需要加关键字COLUMN，否则会报错ora-00905。

其次，对删除单列的话，一定要加COLUMN，然后记住，删除是不需要加列类型的。

 

增加多列：

   alter table emp4 add (test varchar2(10),test2 number);

修改多列：

   alter table emp4 modify (test varchar2(20),test2 varchar2(20));

删除多列：

   alter table emp4 drop (test,test2);

很奇怪的现象，再单列中要加关键字COLUMN，然而再删除多列的时候，不能加COLUMN关键字。

 

Add column in table

syntax:
ALTER TABLE table_name
  ADD column_name column-definition;

Example:
ALTER TABLE customers
  ADD city varchar2(40) DEFAULT 'Seattle';
---------------
Add multiple columns in table

syntax:
ALTER TABLE table_name
  ADD (column_1 column-definition,
       column_2 column-definition,
       ...
       column_n column_definition);

Example:
ALTER TABLE customers
  ADD (customer_name varchar2(45),
       city varchar2(40) DEFAULT 'Seattle');

---------------
Modify column in table
syntax:

ALTER TABLE table_name
  MODIFY column_name column_type;

Example:
ALTER TABLE customers
  MODIFY customer_name varchar2(100) NOT NULL;

ALTER TABLE customers
  MODIFY city varchar2(75) DEFAULT 'Seattle' NOT NULL;

------------
Modify Multiple columns in table

syntax:
ALTER TABLE table_name
  MODIFY (column_1 column_type,
          column_2 column_type,
          ...
          column_n column_type);

Example:
ALTER TABLE customers
  MODIFY (customer_name varchar2(100) NOT NULL,
          city varchar2(75) DEFAULT 'Seattle' NOT NULL);


-------------------------
Drop column in table

Syntax:
ALTER TABLE table_name
  DROP COLUMN column_name;

Example:
ALTER TABLE customers
  DROP COLUMN customer_name;

--------------

Rename column in table
(NEW in Oracle 9i Release 2)
Syntax:
ALTER TABLE table_name
  RENAME COLUMN old_name TO new_name;
  
Example:
ALTER TABLE customers
  RENAME COLUMN customer_name TO cname;
  
------------------
Rename table

Syntax:
ALTER TABLE table_name
  RENAME TO new_table_name;
  
For example:
ALTER TABLE customers
  RENAME TO contacts;
  
  
  -------------------
  Practice Exercise #1:
  Based on the departments table below, rename the departments table to depts.

CREATE TABLE departments
( department_id number(10) NOT NULL,
  department_name varchar2(50) NOT NULL,
  CONSTRAINT departments_pk PRIMARY KEY (department_id)
);
Solution for Practice Exercise #1:
ALTER TABLE departments
  RENAME TO depts;