第16天 :openGauss逻辑结构：表管理4

1.创建表，为表添加字段
create table tab_crm1(id bigint,name varchar(20));
alter table tab_crm1 add column age int;

omm-# \q
omm@911d320f8566:~$ gsql -r
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:46 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=# create table tab_crm1(id bigint,name varchar(20));
CREATE TABLE
omm=# alter table tab_crm1 add column age int;
ALTER TABLE
omm=# 

2.删除表中的已有字段

alter table tab_crm1 drop column age;

omm=# alter table tab_crm1 drop column age;
ALTER TABLEomm=# \d tab_crm1
          Table "public.tab_crm1"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | bigint                | 
 name   | character varying(20) | 

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

omm=# create table test1(id int primary key ,name varchar(20));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test1_pkey" for table "test1"
CREATE TABLE
omm=# \d+ test1
                                Table "public.test1"
 Column |         Type          | Modifiers | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+--------------+-------------
 id     | integer               | not null  | plain    |              | 
 name   | character varying(20) |           | extended |              | 
Indexes:
    "test1_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

omm=# alter table test1 drop constraint test1_pkey;
ALTER TABLE
omm=# \d+ test1
                                Table "public.test1"
 Column |         Type          | Modifiers | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+--------------+-------------
 id     | integer               | not null  | plain    |              | 
 name   | character varying(20) |           | extended |              | 
Has OIDs: no
Options: orientation=row, compression=noomm=# alter table test1 add constraint key_primary_test1 primary key(id); 
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "key_primary_test1" for table "test1"
ALTER TABLE
omm=# \d+ test1
                                Table "public.test1"
 Column |         Type          | Modifiers | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+--------------+-------------
 id     | integer               | not null  | plain    |              | 
 name   | character varying(20) |           | extended |              | 
Indexes:
    "key_primary_test1" PRIMARY KEY, btree (id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

4.修改表字段的默认值

omm=# alter table test1 modify  sex varchar(20);
ALTER TABLE
omm=# \d+ test1
                                            Table "public.test1"
 Column |         Type          |             Modifiers             | Storage  | Stats target | Description 
--------+-----------------------+-----------------------------------+----------+--------------+-------------
 id     | integer               | not null                          | plain    |              | 
 name   | character varying(20) |                                   | extended |              | 
 age    | integer               | default 10                        | plain    |              | 
 sex    | character varying(20) | default 'male'::character varying | extended |              | 
Indexes:
    "key_primary_test1" PRIMARY KEY, btree (id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

omm=# alter table test1 alter column sex set default 'female';
ALTER TABLE
omm=# \d+ test1
                                             Table "public.test1"
 Column |         Type          |              Modifiers              | Storage  | Stats target | Description 
--------+-----------------------+-------------------------------------+----------+--------------+-------------
 id     | integer               | not null                            | plain    |              | 
 name   | character varying(20) |                                     | extended |              | 
 age    | integer               | default 10                          | plain    |              | 
 sex    | character varying(20) | default 'female'::character varying | extended |              | 
Indexes:
    "key_primary_test1" PRIMARY KEY, btree (id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

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

omm=# alter table test1 add age varchar(20);
ERROR:  column "age" of relation "test1" already exists
omm=# alter table test1 add age1 varchar(20);
ALTER TABLE
omm=# alter table test1 modify age1 int;
ALTER TABLE---oromm=# alter table test1 alter column age1 type int;
ALTER TABLE
omm=# \d+ test1
                                             Table "public.test1"
 Column |         Type          |              Modifiers              | Storage  | Stats target | Description 
--------+-----------------------+-------------------------------------+----------+--------------+-------------
 id     | integer               | not null                            | plain    |              | 
 name   | character varying(20) |                                     | extended |              | 
 age    | integer               | default 10                          | plain    |              | 
 sex    | character varying(20) | default 'female'::character varying | extended |              | 
 age1   | integer               |                                     | plain    |              | 
Indexes:
    "key_primary_test1" PRIMARY KEY, btree (id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

6.修改表字段的名字

omm=# alter table test1 rename column age1 to age2;
ALTER TABLE
omm=# \d+ test1
                                             Table "public.test1"
 Column |         Type          |              Modifiers              | Storage  | Stats target | Description 
--------+-----------------------+-------------------------------------+----------+--------------+-------------
 id     | integer               | not null                            | plain    |              | 
 name   | character varying(20) |                                     | extended |              | 
 age    | integer               | default 10                          | plain    |              | 
 sex    | character varying(20) | default 'female'::character varying | extended |              | 
 age2   | integer               |                                     | plain    |              | 
Indexes:
    "key_primary_test1" PRIMARY KEY, btree (id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

7.修改表的名字

omm=# alter table test1 rename to test2;
ALTER TABLE
omm=# \d+ test1
Did not find any relation named "test1".
omm=# \d+ test2
                                             Table "public.test2"
 Column |         Type          |              Modifiers              | Storage  | Stats target | Description 
--------+-----------------------+-------------------------------------+----------+--------------+-------------
 id     | integer               | not null                            | plain    |              | 
 name   | character varying(20) |                                     | extended |              | 
 age    | integer               | default 10                          | plain    |              | 
 sex    | character varying(20) | default 'female'::character varying | extended |              | 
 age2   | integer               |                                     | plain    |              | 
Indexes:
    "key_primary_test1" PRIMARY KEY, btree (id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

8.删除表

omm=# drop table if exists test1;
NOTICE:  table "test1" does not exist, skipping
DROP TABLE
omm=# drop table if exists test2;
DROP TABLE