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

1、登陆opengauss数据库

[root@enmoedu ~]# su - omm
Last login: Thu Dec  8 23:26:15 CST 2022 on pts/0
[omm@enmoedu ~]$ gs_om -t status --detail
[   Cluster State   ]

cluster_state   : Unavailable
redistributing  : No
current_az      : AZ_ALL

[  Datanode State   ]

    node   node_ip         port      instance                            state
----------------------------------------------------------------------------------------------
1  enmoedu 192.168.94.135  15400      6001 /opt/huawei/install/data/dn   P Primary Manually stopped
[omm@enmoedu ~]$ gs_om -t start
Starting cluster.
=========================================
[SUCCESS] enmoedu
2022-12-10 01:06:31.360 63936b16.1 [unknown] 139646645288000 [unknown] 0 dn_6001 01000  0 [BACKEND] WARNING:  could not create any HA TCP/IP sockets
2022-12-10 01:06:31.360 63936b16.1 [unknown] 139646645288000 [unknown] 0 dn_6001 01000  0 [BACKEND] WARNING:  could not create any HA TCP/IP sockets
2022-12-10 01:06:31.379 63936b16.1 [unknown] 139646645288000 [unknown] 0 dn_6001 01000  0 [BACKEND] WARNING:  Failed to initialize the memory protect for g_instance.attr.attr_storage.cstore_buffers (1024 Mbytes) or shared memory (1894 Mbytes) is larger.
=========================================
Successfully started.
[omm@enmoedu ~]$ gsql -d postgres -p 15400 -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:34 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

openGauss=# 

2、创建表

drop table if exists test;
create table test(
      id bigint,
      name varchar(50) not null,
      age  int default 20,
      primary key(id)
     );

3、为test表新增一个列，列名为sex，数据类型为boolean

alter table test add column sex Boolean;

4、删除test表中已有的字段

alter table test drop column name ;

5、删除test表中已有的约束

表test上有一个名叫test_pkey的PRIMARY KEY约束，删除这个约束。

alter table test drop constraint test_pkey;

或者

直接查看约束是否被删除

select * from pg_constraint  where conname like 'test_pkey';

6、修改字段的默认值

将字段age的值变更为25

alter table test alter column age set default 55;

7、修改表字段的数据类型

alter table test ALTER COLUMN age TYPE bigint;

8、修改表字段的名字

ALTER TABLE test RENAME COLUMN age TO stuage;

9、修改表的名字

修改表的名字，将表test的名字变更为mytest

ALTER TABLE test RENAME TO mytest;

10、删除表

DROP TABLE mytest;