openGauss每日一练第 15天|表管理3

实操作业

实操目标：学习查看表的相关信息

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 '年龄';
复制

2.使用\d tableNmae命令查看表的定义、模式和所有者

omm=# \d+ student
                                Table "public.student"
  Column  |         Type          | Modifiers | Storage  | Stats target | Description 
----------+-----------------------+-----------+----------+--------------+-------------
 stu_id   | integer               | not null  | plain    |              | 学号
 stu_name | character varying(20) | not null  | extended |              | 姓名
 stu_age  | integer               |           | plain    |              | 年龄
Indexes:
    "student_pkey" PRIMARY KEY, btree (stu_id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no
复制

3.查看某个模式下有哪些表

create schema sch1;
drop table if exists sch1.student;
create table sch1.student(
stu_id int4 primary key,
stu_name varchar(20) not null,
stu_age int
);
comment on table sch1.student is '学生信息表';
comment on column sch1.student.stu_id is '学号';
comment on column sch1.student.stu_name is '姓名';
comment on column sch1.student.stu_age is '年龄';
--查看public模式下的用户表
omm=# SELECT table_name FROM information_schema.tables WHERE table_schema='public';
 table_name 
------------
 ss
 student
(2 rows)
--查看sch1模式下的用户表
omm=# SELECT table_name FROM information_schema.tables WHERE table_schema='sch1';
 table_name 
------------
 student
(1 row)
复制

4.查看一个表下有哪些约束

--查看约束名称、约束类型
select conname, connamespace, contype, conkey     
  from pg_constraint 
 where conrelid in (  select oid
                  from pg_class
                 where relname='student');

--或者使用gsql的元命令\d tableName很方便地查看一个表上有哪些约束
 \d student
复制

5.查看一个表属于数据库的哪个模式

omm=# select * from pg_tables where tablename='student';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | tablecreator |            created            |         last_ddl_time         
------------+-----------+------------+------------+------------+----------+-------------+--------------+-------------------------------+-------------------------------
 public     | student   | omm        |            | t          | f        | f           | omm          | 2022-12-11 22:27:30.627769+08 | 2022-12-11 22:27:32.089471+08
 sch1       | student   | omm        |            | t          | f        | f           | omm          | 2022-12-11 22:27:44.731832+08 | 2022-12-11 22:27:46.430133+08
(2 rows
复制