学习目标

    学习查看表的相关信息

课程学习

1.测试环境准备

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

执行过程如下：

[omm@opengauss ~]$ gsql -r
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=# drop table if exists test;
NOTICE:  table "test" does not exist, skipping
DROP TABLE
omm=# create table test(id bigint,name varchar(50) not null,age  int default 20,primary key(id));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
omm=# 

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

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

执行过程如下：

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)

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)

omm=#

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

执行过程如下：

omm=# select conname, connamespace, contype, conkey from pg_constraint where conrelid in (select oid from pg_class 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

omm=#

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

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

执行过程如下：

omm=# \x 
Expanded display is on.
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=# 

课程作业

1.创建表和约束

create schema xiaocx_schm1;
create table xiaocx_schm1.txiaocx(id bigint,name varchar(50) not null,age int default 20,primary key(id));

omm=# create schema xiaocx_schm1;
CREATE SCHEMA
omm=# create table xiaocx_schm1.txiaocx(id bigint,name varchar(50) not null,age  int default 20,primary key(id));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "txiaocx_pkey" for table "txiaocx"
CREATE TABLE
omm=# 

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

\d+ xiaocx_schm1.txiaocx
\dt+ xiaocx_schm1.txiaocx

omm=# \d+ xiaocx_schm1.txiaocx
                            Table "xiaocx_schm1.txiaocx"
 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:
    "txiaocx_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

omm=# \dt+ xiaocx_schm1.txiaocx
List of relations
-[ RECORD 1 ]---------------------------------
Schema      | xiaocx_schm1
Name        | txiaocx
Type        | table
Owner       | omm
Size        | 0 bytes
Storage     | {orientation=row,compression=no}
Description | 

omm=#

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

SELECT table_name FROM information_schema.tables WHERE table_schema='xiaocx_schm1';

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

omm=#

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

\d xiaocx_schm1.txiaocx

--or

select conname, connamespace, contype, conkey from pg_constraint where conrelid in (select oid from pg_class where relname='txiaocx');

omm=#  \d xiaocx_schm1.txiaocx
        Table "xiaocx_schm1.txiaocx"
 Column |         Type          | Modifiers  
--------+-----------------------+------------
 id     | bigint                | not null
 name   | character varying(50) | not null
 age    | integer               | default 20
Indexes:
    "txiaocx_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default

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

omm=#

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

\x
SELECT * FROM information_schema.tables WHERE table_name='txiaocx';

omm=# \x 
Expanded display is on.
omm=# SELECT * FROM information_schema.tables WHERE table_name='txiaocx';
-[ RECORD 1 ]----------------+-------------
table_catalog                | omm
table_schema                 | xiaocx_schm1
table_name                   | txiaocx
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=#