openGauss每日一练第 16天|表管理4

实操作业

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

drop table if exists student;
create table student(
stu_id int4 primary key,
stu_name varchar(20) not null,
stu_age int
);
comment on table student is '学生信息表';
comment on column student.stu_id is '学号';
comment on column student.stu_name is '姓名';
comment on column student.stu_age is '年龄';
--为student表添加字段
alter table student add  column stu_t varchar(20);
omm=# \d student
            Table "public.student"
  Column  |         Type          | Modifiers 
----------+-----------------------+-----------
 stu_id   | integer               | not null
 stu_name | character varying(20) | not null
 stu_age  | integer               | 
 stu_t    | character varying(20) | 
Indexes:
    "student_pkey" PRIMARY KEY, btree (stu_id) TABLESPACE pg_default

2.删除表中的已有字段

omm=# alter table student drop column stu_t;
ALTER TABLE
omm=# \d student
            Table "public.student"
  Column  |         Type          | Modifiers 
----------+-----------------------+-----------
 stu_id   | integer               | not null
 stu_name | character varying(20) | not null
 stu_age  | integer               | 
Indexes:
    "student_pkey" PRIMARY KEY, btree (stu_id) TABLESPACE pg_default

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

omm=# \d student
            Table "public.student"
  Column  |         Type          | Modifiers 
----------+-----------------------+-----------
 stu_id   | integer               | not null
 stu_name | character varying(20) | not null
 stu_age  | integer               | 
Indexes:
    "student_pkey" PRIMARY KEY, btree (stu_id) TABLESPACE pg_default
--删除约束名student_pkey
omm=# alter table student  drop constraint student_pkey;
ALTER TABLE
--添加约束名student_stu_id_key
omm=# alter table student  add constraint student_stu_id_key primary key(stu_id);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "student_stu_id_key" for table "student"
ALTER TABLE
omm=# \d student
            Table "public.student"
  Column  |         Type          | Modifiers 
----------+-----------------------+-----------
 stu_id   | integer               | not null
 stu_name | character varying(20) | not null
 stu_age  | integer               | 
Indexes:
    "student_stu_id_key" PRIMARY KEY, btree (stu_id) TABLESPACE pg_default

4.修改表字段的默认值

omm=# \d student
            Table "public.student"
  Column  |         Type          | Modifiers 
----------+-----------------------+-----------
 stu_id   | integer               | not null
 stu_name | character varying(20) | not null
 stu_age  | integer               | 
Indexes:
    "student_stu_id_key" PRIMARY KEY, btree (stu_id) TABLESPACE pg_default
    
omm=# alter table student alter COLUMN stu_age set  default 20;
ALTER TABLE

omm=# \d student
            Table "public.student"
  Column  |         Type          | Modifiers  
----------+-----------------------+------------
 stu_id   | integer               | not null
 stu_name | character varying(20) | not null
 stu_age  | integer               | default 20
Indexes:
    "student_stu_id_key" PRIMARY KEY, btree (stu_id) TABLESPACE pg_default

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

omm=# \d student
            Table "public.student"
  Column  |         Type          | Modifiers  
----------+-----------------------+------------
 stu_id   | integer               | not null
 stu_name | character varying(20) | not null
 stu_age  | integer               | default 20
Indexes:
    "student_stu_id_key" PRIMARY KEY, btree (stu_id) TABLESPACE pg_default
    
omm=# alter table student ALTER COLUMN stu_age TYPE int8;
ALTER TABLE
omm=# \d student
            Table "public.student"
  Column  |         Type          | Modifiers  
----------+-----------------------+------------
 stu_id   | integer               | not null
 stu_name | character varying(20) | not null
 stu_age  | bigint                | default 20
Indexes:
    "student_stu_id_key" PRIMARY KEY, btree (stu_id) TABLESPACE pg_default

6.修改表字段的名字

omm=# \d student
            Table "public.student"
  Column  |         Type          | Modifiers  
----------+-----------------------+------------
 stu_id   | integer               | not null
 stu_name | character varying(20) | not null
 stu_age  | bigint                | default 20
Indexes:
    "student_stu_id_key" PRIMARY KEY, btree (stu_id) TABLESPACE pg_default
    
omm=# alter  table student rename COLUMN stu_age to age;
ALTER TABLE
omm=# \d student
            Table "public.student"
  Column  |         Type          | Modifiers  
----------+-----------------------+------------
 stu_id   | integer               | not null
 stu_name | character varying(20) | not null
 age      | bigint                | default 20
Indexes:
    "student_stu_id_key" PRIMARY KEY, btree (stu_id) TABLESPACE pg_default

7.修改表的名字

omm=# alter  table student rename TO student_new;
ALTER TABLE
omm=# \d
                            List of relations
 Schema |    Name     | Type  | Owner |             Storage              
--------+-------------+-------+-------+----------------------------------
 public | ss          | table | omm   | {orientation=row,compression=no}
 public | student_new | table | omm   | {orientation=row,compression=no}
(2 rows)

8.删除表

drop table if exists student_new;