openGauss每日一练第16天 |openGauss逻辑结构：表管理4

今天是openGauss每日一练的第16天，今天我们将继续在前面几天的基础上来重点来学习openGauss的逻辑结构-表管理的相关知识。今天学习的重点是如何在openGuass数据库中对表进行修改操作。

我们通过下面的实验来学习修改表名，修改表字段名，修改表字段数据类型，修改表字段默认值，修改表的约束等来了解在openGuass数据库中对表的修改操作，接下来，我们一起动手做实验吧~~~

实验内容

1.创建表，为表添加字段

--在数据库中创建一个测试表testomm=# create table test(
omm(#       id bigint,
omm(#       name varchar(50) not null,
omm(#       age  int default 20,
omm(#       primary key(id)
     );omm(# 
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
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--在表中添加一个字段sex,字段类型为Booleanomm=# alter table test add column sex Boolean;
ALTER TABLE
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.删除表中的已有字段

omm=# alter table test drop column sex ;
ALTER TABLE
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_pkeyomm=# alter table test drop constraint test_pkey;
ALTER TABLE
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 | confupdtype | confdeltype | confmatchtype | conislocal | coninhcount | connoinherit | consoft 
| conopt | conkey | confkey | conpfeqop | conppeqop | conffeqop | conexclop | conbin | consrc | conincluding 
---------+--------------+---------+---------------+-------------+--------------+----------+----------+----------+-----------+-------------+-------------+---------------+------------+-------------+--------------+---------
+--------+--------+---------+-----------+-----------+-----------+-----------+--------+--------+--------------
(0 rows)
--创建一个新的主键约束test_pkey
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
omm=# \d test 
             Table "public.test"
 Column |         Type          | Modifiers  
--------+-----------------------+------------
 id     | bigint                | not null
 age    | integer               | default 20
Indexes:
    "test_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default

omm=#  name   | character varying(50) | not null

4.修改表字段的默认值

--修改表test的字段age的默认值为25omm=# alter table test alter column age set default 25;
ALTER TABLE
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.修改表字段的数据类型

--修改表中的字段age的字段类型为bigint类型omm=# alter table test ALTER COLUMN age TYPE bigint;
ALTER TABLE
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.修改表字段的名字

--将表中字段age的名称修改为stuageomm=# ALTER TABLE test RENAME COLUMN age TO stuage;
ALTER TABLE
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.修改表的名字

--修改表名test为mytestomm=# ALTER TABLE test RENAME TO mytest;
ALTER TABLE
omm=# \d test
Did not find any relation named "test".
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

结论

通过上面的实验我们可以看到，在openGauss数据库中，对于表来说，可以修改表名，修改表中的字段名称，也可以修改表中的字段类型，还可以调整表的约束以及为表添加字段等修改操作。