openGauss每日一练第16天 | 学习笔记

前言：

最近参加了由opengauss、墨天轮、鲲鹏社区一起推出的活动《每日一练 opengauss 3.0.0 数据库在线实训课程》，共21天，墨天轮提供实操环境，特此记录学习笔记。
活动详情：https://www.modb.pro/db/551619

主题：

学习openGuass数据库中如何对表进行修改

学习笔记

第16天：表管理4

先来看看表的概念：

表（Table）
表是由行与列组合成的，是数据库中用来存储数据的对象，是整个数据库系统的基础。
每张表只能属于一个数据库，也只能对应到一个表空间。每张表对应的数据文件必须在同一个表空间中。

1.创建测试表

omm=# \conninfo
You are connected to database "omm" as user "omm" via socket in "/tmp" at port "5432".
omm=# 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=# 

2.为表添加字段

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

omm=# 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

omm=#

3.删除表中的已有字段

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

omm=#

4.删除表的已有约束

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

omm=# alter table test drop constraint test_pkey;
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 20

omm=# 

5.为表添加约束

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=# 
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

omm=#

6.修改表字段的默认值

omm=# 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

omm=# 

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

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

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

omm=# 

8.修改表字段的名字

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

omm=# 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

omm=#

9.修改表的名字

omm=# \d test
omm=#              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


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

omm=# 

10.删除表

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

总结：

第16天我们学习了如何为表添加字段、删除已有字段、删除约束、添加约束、修改字段默认值、修改字段数据类型、修改表字段的名字、修改表的名字、删除表的操作。

那如果我们不知道具体语法的话还可以查看帮助：

omm=# \help alter table
Command:     ALTER TABLE
Description: change the definition of a table
Syntax:
ALTER TABLE [ IF EXISTS ] { table_name  [*] | ONLY table_name | ONLY ( table_name  )}
    action [, ... ];
ALTER TABLE [ IF EXISTS ] table_name
    ADD ( { column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]} [, ...] );
ALTER TABLE [ IF EXISTS ] table_name
    MODIFY ( { column_name data_type | column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ] | column_name [ CONSTR
AINT constraint_name ] NULL } [, ...] );
ALTER TABLE [ IF EXISTS ] table_name
    RENAME TO new_table_name;