openGauss每日一练第16天 | 学习心得体会

学习目标

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

学习内容

本章节学习了为表增加列、约束、改列名、修改列数据类型等

课程作业

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

openGauss=# drop table if exists student;
NOTICE:  table "student" does not exist, skipping
DROP TABLE
openGauss=# create table student(id int primary key, name varchar(50) not null, age int default 19);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "student_pkey" for table "student"
CREATE TABLE
openGauss=# \d student
           Table "public.student"
 Column |         Type          | Modifiers
--------+-----------------------+------------
 id     | integer               | not null
 name   | character varying(50) | not null
 age    | integer               | default 19
Indexes:
    "student_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default

2.删除表中的已有字段

openGauss=#  --为表student新增一列
openGauss=# alter table student add column class  bigint;
ALTER TABLE
openGauss=# --查看表test的信息
openGauss=#  \d student
           Table "public.student"
 Column |         Type          | Modifiers
--------+-----------------------+------------
 id     | integer               | not null
 name   | character varying(50) | not null
 age    | integer               | default 19
 class  | bigint                |
Indexes:
    "student_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
openGauss=# --删除刚刚添加的列：
openGauss=# alter table student drop column class ;
ALTER TABLE
openGauss=# --查看表test的信息
openGauss=# \d student
           Table "public.student"
 Column |         Type          | Modifiers
--------+-----------------------+------------
 id     | integer               | not null
 name   | character varying(50) | not null
 age    | integer               | default 19
Indexes:
    "student_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default

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

openGauss=# --表student的student_pkey删除约束：
openGauss=# alter table student drop constraint student_pkey;
ALTER TABLE
openGauss=# --查看表test的信息：
openGauss=# \d student
           Table "public.student"
 Column |         Type          | Modifiers
--------+-----------------------+------------
 id     | integer               | not null
 name   | character varying(50) | not null
 age    | integer               | default 19

openGauss=# --或直接查看约束是否被删除
openGauss=# select * from pg_constraint where conname like 'student_pkey';
 conname | connamespace | contype | condeferrable | condeferred | convalidated | conrelid | contypid | conindi
d | confrelid | confupdtype | confdeltype | confmatchtype | conislocal | coninhcount | connoinherit | consoft
| conopt | conkey | confkey | conpfeqop | conppeqop | conffeqop | conexclop | conbin | consrc | conincluding
---------+--------------+---------+---------------+-------------+--------------+----------+----------+--------
--+-----------+-------------+-------------+---------------+------------+-------------+--------------+---------
+--------+--------+---------+-----------+-----------+-----------+-----------+--------+--------+--------------
(0 rows)

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

openGauss=#

4.修改表字段的默认值

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

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

openGauss=# alter table student  ALTER COLUMN age TYPE varchar(10);
ALTER TABLE
openGauss=# \d+ student
                               Table "public.student"
 Column |         Type          | Modifiers  | Storage  | Stats target | Description
--------+-----------------------+------------+----------+--------------+-------------
 id     | integer               | not null   | plain    |              |
 name   | character varying(50) | not null   | extended |              |
 age    | character varying(10) | default 18 | extended |              |
Indexes:
    "student_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

6.修改表字段的名字

openGauss=# ALTER TABLE student RENAME COLUMN name TO stu_name;
ALTER TABLE
openGauss=# \d+ student
                                Table "public.student"
  Column  |         Type          | Modifiers  | Storage  | Stats target | Description
----------+-----------------------+------------+----------+--------------+-------------
 id       | integer               | not null   | plain    |              |
 stu_name | character varying(50) | not null   | extended |              |
 age      | character varying(10) | default 18 | extended |              |
Indexes:
    "student_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

7.修改表的名字

openGauss=# ALTER TABLE student RENAME TO student2;
ALTER TABLE
openGauss=# \d+ student2
                                Table "public.student2"
  Column  |         Type          | Modifiers  | Storage  | Stats target | Description
----------+-----------------------+------------+----------+--------------+-------------
 id       | integer               | not null   | plain    |              |
 stu_name | character varying(50) | not null   | extended |              |
 age      | character varying(10) | default 18 | extended |              |
Indexes:
    "student_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

openGauss=# \d+ student
Did not find any relation named "student".

8.删除表

openGauss=# drop table if exists student2;
DROP TABLE
openGauss=# \d+ student2
Did not find any relation named "student2".