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

学习目标

学习查看表的相关信息

学习内容

通过本章学习，进一步了解了表在opengauss是如何管理的，可以系统表和元命令查询表的相关信息。

课程作业

1.创建表和约束

omm=# drop table if exists t1;
NOTICE:  table "t1" does not exist, skipping
DROP TABLE
omm=# create table t1(a int primary key,name varchar(100) not null,calss varchar(50));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
CREATE TABLE
omm=# insert into t1 values (10001,'测试0001','五年级一班'),(10002,'测试0002','五年级一班'),(10003,'测试0003','五年级一班');
INSERT 0 3

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

omm=# \d+ t1;
                                  Table "public.t1"
 Column |          Type          | Modifiers | Storage  | Stats target | Description 
--------+------------------------+-----------+----------+--------------+-------------
 a      | integer                | not null  | plain    |              | 
 name   | character varying(100) | not null  | extended |              | 
 calss  | character varying(50)  |           | extended |              | 
Indexes:
    "t1_pkey" PRIMARY KEY, btree (a) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

omm=# \dt+ t1;
                                      List of relations
 Schema | Name | Type  | Owner |    Size    |             Storage              | Description 
--------+------+-------+-------+------------+----------------------------------+-------------
 public | t1   | table | omm   | 8192 bytes | {orientation=row,compression=no} | 
(1 row)

omm=# \d t1
              Table "public.t1"
 Column |          Type          | Modifiers 
--------+------------------------+-----------
 a      | integer                | not null
 name   | character varying(100) | not null
 calss  | character varying(50)  | 
Indexes:
    "t1_pkey" PRIMARY KEY, btree (a) TABLESPACE pg_default

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

omm=# select table_schema,table_name from information_schema.tables where table_name ='t1';
 table_schema | table_name 
--------------+------------
 public       | t1
(1 row)

omm=#

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

omm=# select conname, connamespace, contype, conkey     
omm-#   from pg_constraint a,pg_class b
omm-#  where a.conrelid =b.oid
omm-#    and relname='t1';
omm=#  conname | connamespace | contype | conkey 
---------+--------------+---------+--------
 t1_pkey |         2200 | p       | {1}
(1 row)

5.查看一个表属于数据库的哪个模式 \x打开列显示

omm=# \x
Expanded display is on.
omm=# SELECT * FROM information_schema.tables WHERE table_name='t1';
-[ RECORD 1 ]----------------+-----------
table_catalog                | omm
table_schema                 | public
table_name                   | t1
table_type                   | BASE TABLE
self_referencing_column_name | 
reference_generation         | 
user_defined_type_catalog    | 
user_defined_type_schema     | 
user_defined_type_name       | 
is_insertable_into           | YES
is_typed                     | NO
commit_action                | 

omm=#