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

课程作业

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

--创建表yangkai
root@modb:~# su - omm
omm@modb:~$ 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.

omm=# create table yangkai
omm-# ( id int ,
omm(# name varchar(20) not null,
omm(# age int default 20,
omm(# primary key(id)
omm(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "yangkai_pkey" for table "yangkai"
CREATE TABLE
--为表yangkai增加字段sex 
omm=# \d yangkai
       Table "public.yangkai"
Column |         Type          | Modifiers  
--------+-----------------------+------------
id     | integer               | not null
name   | character varying(20) | not null
	age    | integer               | default 20
Indexes:
"yangkai_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default

omm=# alter table yangkai add column sex boolean;

2.删除表中的已有字段

omm=# alter table yangkaidrop column sex ;
ALTER TABLE
omm=# \d yangkai
       Table "public.yangkai"
 Column |         Type          | Modifiers  
--------+-----------------------+------------
id     | integer               | not null
name   | character varying(20) | not null
age    | integer               | default 20

3.删除表的已有约束、添加约束

omm=# alter table yangkai add constraint yk_key primary key(id);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "yk_key" for table "yangkai"
ALTER TABLE
omm=# \d yangkai
       Table "public.yangkai"
Column |         Type          | Modifiers  
--------+-----------------------+------------
id     | integer               | not null
name   | character varying(20) | not null
age    | integer               | default 20
Indexes:
"yk_key" PRIMARY KEY, btree (id) TABLESPACE pg_default

4.修改表字段的默认值

omm=# alter table yangkai alter column age set default 25;
ALTER TABLE

5.修改表字段的数据类型

omm=# alter table yangkai ALTER COLUMN age TYPE bigint;
ALTER TABLE

6.修改表字段的名字

omm=# alter table yangkai RENAME COLUMN age TO stuage;
ALTER TABLE
omm=# \d yangkai
       Table "public.yangkai"
Column |         Type          | Modifiers  
--------+-----------------------+------------
id     | integer               | not null
name   | character varying(20) | not null
stuage | bigint                | default 25
Indexes:
"yk_key" PRIMARY KEY, btree (id) TABLESPACE pg_default

7.修改表的名字

omm=# ALTER TABLE yangkai rename to yangbaji;
ALTER TABLE
omm=# \d yangbaji
       Table "public.yangbaji"
Column |         Type          | Modifiers  
--------+-----------------------+------------
id     | integer               | not null
name   | character varying(20) | not null
stuage | bigint                | default 25
Indexes:
"yk_key" PRIMARY KEY, btree (id) TABLESPACE pg_default

8.删除表

omm=# drop table yangbaji;
DROP TABLE
omm=# \d yangbaji
Did not find any relation named "yangbaji".