openGauss每日一练第15天 | 信创之路

学习目标

学习查看表的相关信息

课程学习

1.测试环境准备

--首先创建一张测试表。
drop table if exists test;
create table test(
id bigint,
name varchar(50) not null,
age  int default 20,
primary key(id)
     );

2.在gsql中查看表的定义、模式和所有者

--在gsql中，使用\d tableNmae命令查看表的定义。

 \d+ test;
--在gsql中，使用\d tableNmae命令查看表的模式和所有者。

\dt+ test;

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

--查看public模式下的所有表名：
  SELECT table_name FROM information_schema.tables WHERE table_schema='public';

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

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

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

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

\x 

SELECT * FROM information_schema.tables WHERE table_name='test';

课程作业

1.创建表和约束

omm=# create table test(
name varchar(50) not null,
omm(# omm(# id bigint,
omm(# age  int default 20,
omm(# primary key(id)
omm(#      );

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

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 |  Size   |             Storage              | Description 
--------+------+-------+-------+---------+----------------------------------+-------------
 public | test | table | omm   | 0 bytes | {orientation=row,compression=no} | 
(1 row)

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

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

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

omm=# select conname, connamespace, contype, conkey     
omm-#   from pg_constraint 
omm-# omm(#  where conrelid in (  select oid
                  from pg_class
omm(#                  where relname='test');
  conname  | connamespace | contype | conkey 
-----------+--------------+---------+--------
 test_pkey |         2200 | p       | {1}
(1 row)

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.查看一个表属于数据库的哪个模式

omm=# \x
Expanded display is off.
omm=# SELECT * FROM information_schema.tables WHERE table_name='test';
 table_catalog | table_schema | table_name | table_type | self_referencing_column_name | reference_generation | user_defined_type_c
atalog | user_defined_type_schema | user_defined_type_name | is_insertable_into | is_typed | commit_action 
---------------+--------------+------------+------------+------------------------------+----------------------+--------------------
-------+--------------------------+------------------------+--------------------+----------+---------------
 omm           | public       | test       | BASE TABLE |                              |                      |                    
       |                          |                        | YES                | NO       | 
(1 row)

omm=#