openGauss逻辑结构:表管理4
openGauss逻辑结构:表管理4
·
学习目标
学习openGuass数据库中如何对表进行修改
1.创建表,为表添加字段
[omm@ogdb1 ~]$ gsql -p 40000 -r
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
--首先创建一张测试表。
omm=# drop table if exists test;
DROP TABLE
omm=# create table test(id bigint, name varchar(50) not null, age int default 20, primary key(id));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
omm=#
--查看表test的信息
omm=# \d test
Table "public.test"
Column | Type | Modifiers
--------+-----------------------+------------
id | bigint | not null
name | character varying(50) | not null
age | integer | default 20
Indexes:
"test_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
--为表test新增一列,列名为sex,数据类型为Boolean:
omm=# alter table test add column sex Boolean;
ALTER TABLE
omm=#
--执行下面gsql命令,查看表test的信息
omm=# \d test
Table "public.test"
Column | Type | Modifiers
--------+-----------------------+------------
id | bigint | not null
name | character varying(50) | not null
age | integer | default 20
sex | boolean |
Indexes:
"test_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
2.删除表中的已有字段
--执行下面的SQL语句,删除刚刚添加的列sex:
omm=# alter table test drop column sex;
ALTER TABLE
--执行下面gsql命令,再次查看表test的信息
omm=# \d test
Table "public.test"
Column | Type | Modifiers
--------+-----------------------+------------
id | bigint | not null
name | character varying(50) | not null
age | integer | default 20
Indexes:
"test_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
3.删除表的已有约束、添加约束
--表test上有一个名叫test_pkey的PRIMARY KEY约束。执行下面的SQL语句,删除这个约束:
omm=# alter table test drop constraint test_pkey;
ALTER TABLE
--执行下面的gsql命令,再次查看表test的信息:
omm=# \d test
Table "public.test"
Column | Type | Modifiers
--------+-----------------------+------------
id | bigint | not null
name | character varying(50) | not null
age | integer | default 20
--或直接查看约束是否被删除
omm=# select * from pg_constraint where conname like 'test_pkey';
conname | connamespace | contype | condeferrable | condeferred | convalidated | conrelid | contypid | conindid | confrelid | confup
dtype | confdeltype | confmatchtype | conislocal | coninhcount | connoinherit | consoft | conopt | conkey | confkey | conpfeqop | co
nppeqop | conffeqop | conexclop | conbin | consrc | conincluding
---------+--------------+---------+---------------+-------------+--------------+----------+----------+----------+-----------+-------
------+-------------+---------------+------------+-------------+--------------+---------+--------+--------+---------+-----------+---
--------+-----------+-----------+--------+--------+--------------
(0 rows)
--执行下面的SQL命令,为表test添加刚刚删除的主键约束:
omm=# alter table test add constraint test_pkey primary key(id);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "test_pkey" for table "test"
ALTER TABLE
--再次查看表test的信息:
omm=# select * from pg_constraint where conname like 'test_pkey';
conname | connamespace | contype | condeferrable | condeferred | convalidated | conrelid | contypid | conindid | confrelid | conf
updtype | confdeltype | confmatchtype | conislocal | coninhcount | connoinherit | consoft | conopt | conkey | confkey | conpfeqop |
conppeqop | conffeqop | conexclop | conbin | consrc | conincluding
-----------+--------------+---------+---------------+-------------+--------------+----------+----------+----------+-----------+-----
--------+-------------+---------------+------------+-------------+--------------+---------+--------+--------+---------+-----------+-
----------+-----------+-----------+--------+--------+--------------
test_pkey | 2200 | p | f | f | t | 16595 | 0 | 16601 | 0 |
| | | t | 0 | t | f | f | {1} | | |
| | | | |
(1 row)
omm=#
omm=# \d test
Table "public.test"
Column | Type | Modifiers
--------+-----------------------+------------
id | bigint | not null
name | character varying(50) | not null
age | integer | default 20
Indexes:
"test_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
4.修改表字段的默认值
--执行下面的SQL语句,将age的默认值变更为25
omm=# alter table test alter column age set default 25;
ALTER TABLE
omm=#
omm=# \d test
Table "public.test"
Column | Type | Modifiers
--------+-----------------------+------------
id | bigint | not null
name | character varying(50) | not null
age | integer | default 25
Indexes:
"test_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
5.修改表字段的数据类型
omm=# alter table test alter column age type bigint;
ALTER TABLE
omm=#
omm=# \d test
Table "public.test"
Column | Type | Modifiers
--------+-----------------------+------------
id | bigint | not null
name | character varying(50) | not null
age | bigint | default 25
Indexes:
"test_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
6.修改表字段的名字
--修改表字段的名字,并再次查询确认
omm=# alter table test rename column age to stuage;
ALTER TABLE
omm=#
omm=# \d test
Table "public.test"
Column | Type | Modifiers
--------+-----------------------+------------
id | bigint | not null
name | character varying(50) | not null
stuage | bigint | default 25
Indexes:
"test_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
7.修改表的名字
--修改表的名字。执行下面的SQL语句,将表test的名字变更为mytest:
omm=# alter table test rename to mytest;
ALTER TABLE
omm=#
omm=# \d mytest;
Table "public.mytest"
Column | Type | Modifiers
--------+-----------------------+------------
id | bigint | not null
name | character varying(50) | not null
stuage | bigint | default 25
Indexes:
"test_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
8.删除表
omm=# drop table mytest;
DROP TABLE
omm=#
更多推荐
已为社区贡献21条内容
所有评论(0)