oracle、db2数据库表字段修改

DB2:

• 新增表字段：alter table [TABLE_NAME] add [COLUMN_NAME] [COLUMN_TYPE]

    eg: alter table USER add USER_NAME varchar(20);

• 更改表字段：alter table [TABLE_NAME] alter [COLUMN_NAME] set data type [COLUMN_TYPE]

    eg: alter table USER alter USER_NO set data type varchar(12);

• 删除表字段：alter table [TABLE_NAME] drop column [COLUMN_NAME]

    eg:alter table USER drop column USER_SALARY;

• 为字段添加默认值：alter table [TABLE_NAME] alter column [COLUMN_NAME] set default [VALUE] 

    eg:alter table USER alter column USER_ADDRESS set default "浙江杭州"；

• 添加带默认值的字段：alter talbe [TABLE_NAME] add column [COLUMN_NAME] [COLUMN_TYPE] not null with default [VALUE]

    eg:alter table USER add column USER_ADDRESS varchar(50) not null with default "浙江杭州";

• 表注释添加：comment on table [TABLE_NAME] is 'COMMENT_CONTENT'

    eg:comment on table USER is '用户表';

• 列注释添加：comment on column [TABLE_NAME].[COLUMN_NAME] is 'COMMENT_CONTENT'

    eg:comment on column USER.USER_NO is '用户号';

 

 

Oracle:

• 新增表字段：alter table [TABLE_NAME] add ([COLUMN_NAME] [COLUMN_TYPE] default [DEFAULT_VALUE] [NULL/NOT NULL])

    eg:alter table USER add (USER_NO varchar(12) default "0000" not null);

• 更改表字段：alter table [TABLE_NAME] modify ([COLUMN_NAME] [COLUMN_TYPE] default [DEFAULT_VALUE])

    eg:alter table USER modify (USER_NAME varchar(20) default "unknown");

• 删除表字段：alter table [TABLE_NAME] drop column [COLUMN_NAME]

    eg:alter table USER drop column USER_ADDRESS;

 

有任何想法可以联系我！