openGauss每日一练第15天

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

1.利用gsql -r登录opengauss数据库

root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

2。创建一张表test。并查看表的结构。表属于哪个schema，表的owner等。

omm=# create table test(
omm(# id bigint,
omm(# omm(# name varchar(50) not null,
age int default 20,
omm(# primary key(id));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE 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=# \d+ test
                                 Table "public.test"
 Column |         Type          | Modifiers  | Storage  | Stats target | Description 
--------+-----------------------+------------+----------+--------------+-------------
 id     | bigint                | not null   | plain    |              | 
 name   | character varying(50) | not null   | extended |              | 
 age    | integer               | default 20 | plain    |              | 
Indexes:
    "test_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

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

我们看到此表属于public模式，owner为omm。

3.查看模式public下有哪些表。使用语句SELECT table_name FROM information_schema.tables WHERE table_schema='public';

omm=#   SELECT table_name FROM information_schema.tables WHERE table_schema='public';
 table_name 
------------
 test
(1 row)

4.利用\d test查看表有哪些约束。

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

5.查看test表属于哪个模式（利用语句或者\d test）

omm=# \x
Expanded display is on.
omm=# 
omm=# SELECT * FROM information_schema.tables WHERE table_name='test';
-[ RECORD 1 ]----------------+-----------
table_catalog                | omm
table_schema                 | public
table_name                   | test
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=# \dt test
List of relations
-[ RECORD 1 ]-----------------------------
Schema  | public
Name    | test
Type    | table
Owner   | omm
Storage | {orientation=row,compression=no}