跳到主要内容
Version: 2.1

更改表

概述

使用oracle语法更改表。

语法

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
action;

action:
ADD ( add_coldef [, ... ] )
| MODIFY ( modify_coldef [, ... ] )
| DROP [ COLUMN ] ( column_name [, ... ] )

add_coldef:
cloumn_name data_type

modify_coldef:
cloumn_name data_type alter_using

alter_using:
USING expression

参数

name 表名.
cloumn_name 列名.
data_type 列类型.
expression 值表达式.
ADD keyword 增加表的列, 可以增加一个列或多个列.
MODIFY keyword 修改表的列, 可以修改一个列或多个列.
DROP keyword 删除表的列, 可以删除一个列或多个列.
USING keyword 修改列的值.

示例

ADD:
create table tb_test1(id int, flg char(10));

alter table tb_test1 add (name varchar);

alter table tb_test1 add (adress varchar, num int, flg1 char);

\d tb_test1
Table "public.tb_test1"
Column | Type | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
id | integer | | |
flg | character(10) | | |
name | character varying | | |
adress | character varying | | |
num | integer | | |
flg1 | character(1) | | |

MODIFY:
create table tb_test2(id int, flg char(10), num varchar);

insert into tb_test2 values('1', 2, '3');

alter table tb_test2 modify(id char);

\d tb_test2
Table "public.tb_test2"
Column | Type | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
id | character(1) | | |
flg | character(10) | | |
num | character varying | | |

DROP:
create table tb_test3(id int, flg1 char(10), flg2 char(11), flg3 char(12), flg4 char(13),
flg5 char(14), flg6 char(15));

alter table tb_test3 drop column(id);

\d tb_test3
Table "public.tb_test3"
Column | Type | Collation | Nullable | Default
--------+---------------+-----------+----------+---------
flg1 | character(10) | | |
flg2 | character(11) | | |
flg3 | character(12) | | |
flg4 | character(13) | | |
flg5 | character(14) | | |
flg6 | character(15) | | |