0.学习内容与环境进入
学习openGuass数据库中如何对表进行修改
-
查看表约束
\d <tablename> -
修改表字段默认值
alter table t1 set column <name> default <value>; -
进入环境
su - omm gsql -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
\l
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+---------+-------+-------------------
omm | omm | UTF8 | C | C |
postgres | omm | UTF8 | C | C |
template0 | omm | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
| template1 | omm | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
| (4 rows)
1.创建表，为表添加字段
- 创建表
drop table if exists t1;
create table t1(
id bigint,
name varchar(50) not null,
age int default 20,
primary key(id)
);
\d t1
CREATE TABLE
Table "public.t1"
Column | Type | Modifiers
--------+-----------------------+------------
id | bigint | not null
name | character varying(50) | not null
age | integer | default 20
Indexes:
"t1_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
Table "public.t1"
Column | Type | Modifiers
--------+-----------------------+------------
id | bigint | not null
name | character varying(50) | not null
age | integer | default 20
Indexes:
"t1_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
- 为表t1新增一列，列名为gender，数据类型为Boolean
alter table t1 add column gender Boolean;
\d t1
Column | Type | Modifiers
--------+-----------------------+------------
id | bigint | not null
name | character varying(50) | not null
age | integer | default 20
gender | boolean |
Indexes:
"t1_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
2.删除表中的已有字段
alter table t1 drop column gender;
Column | Type | Modifiers
--------+-----------------------+------------
id | bigint | not null
name | character varying(50) | not null
age | integer | default 20
Indexes:
"t1_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
3.删除表的已有约束、添加约束
drop删除约束
alter table t1 drop constraint t1_pkey;
\d t1
Column | Type | Modifiers
--------+-----------------------+------------
id | bigint | not null
name | character varying(50) | not null
age | integer | default 20
add增加约束
alter table t1 add constraint t1_new_pkey primary key(id);
\d t1
Column | Type | Modifiers
--------+-----------------------+------------
omm=# id | bigint | not null
name | character varying(50) | not null
age | integer | default 20
Indexes:
"t1_new_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
4.修改表字段的默认值
alter table t1 alter column age set default 35;
\d t1
Column | Type | Modifiers
--------+-----------------------+------------
id | bigint | not null
name | character varying(50) | not null
age | integer | default 35
Indexes:
"t1_new_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
5.修改表字段的数据类型
- 修改age从integer到bigint
alter table t1 ALTER COLUMN age TYPE bigint;
\d t1
Column | Type | Modifiers
--------+-----------------------+------------
id | bigint | not null
name | character varying(50) | not null
age | bigint | default 35
Indexes:
"t1_new_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
6.修改表字段的名字
- 修改命名列从age到years
ALTER TABLE t1 RENAME COLUMN age TO years;
\d t1
Column | Type | Modifiers
--------+-----------------------+------------
id | bigint | not null
name | character varying(50) | not null
age | bigint | default 35
Indexes:
"t1_new_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
7.修改表的名字
- 修改表的名字从t1到t2
ALTER TABLE t1 RENAME TO t2;
\d t2
Column | Type | Modifiers
--------+-----------------------+------------
id | bigint | not null
name | character varying(50) | not null
age | bigint | default 35
Indexes:
"t1_new_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
8.删除表
- 删除表t2
DROP TABLE t2;
\d
No relations found.
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
