openGauss每日一练第15天 openGauss逻辑结构：表管理3

学习目标

学习查看表的相关信息

课程学习

测试练习创建表和约束，并查看约束，模式。

课程作业

1.创建表和约束

/*
--创建一张测试表test15
drop table if exists test15;
create table test15(
id bigint,
name varchar(50) not null,          --not null约束
age  int default 20,
primary key(id)                     --主键约束
);
*/

omm=# drop table if exists test15;
WARNING:  Session unused timeout.
FATAL:  terminating connection due to administrator command
could not send data to server: Broken pipe
The connection to the server was lost. Attempting reset: Succeeded.
omm=# create table test15(
omm(# id bigint,
omm(# name varchar(50) not null,          --not null约束
omm(# age  int default 20,
omm(# primary key(id)                     --主键约束
omm(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test15_pkey" for table "test15"
CREATE TABLE

复制

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

/*
--在gsql中，使用\d tableNmae命令查看表的定义。
\d+ test15;

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

omm=# --在gsql中，使用\d tableNmae命令查看表的定义。
omm=# \d+ test15;
                                Table "public.test15"
 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:
    "test15_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

omm=# 
omm=# --在gsql中，使用\d tableNmae命令查看表的模式和所有者。
omm=# \dt+ test15;
                                     List of relations
 Schema |  Name  | Type  | Owner |  Size   |             Storage              | Description 
--------+--------+-------+-------+---------+----------------------------------+-------------
 public | test15 | table | omm   | 0 bytes | {orientation=row,compression=no} | 
(1 row)


复制

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

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

omm=# --查看public模式下的所有表名：
omm=# SELECT table_name FROM information_schema.tables WHERE table_schema='public';
     table_name     
--------------------
 test13
 test
 test001
 test002
 test003
 invoice
 newtestwithdata
 testnewwithoutdata
 test15
(9 rows)

复制

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

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

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

omm=# --查看约束名称、约束类型
omm=# select conname, connamespace, contype, conkey     
omm-# omm-# from pg_constraint 
where conrelid in (  
omm(# omm(# from pg_class
omm(# select oid
where relname='test15');
   conname   | connamespace | contype | conkey 
-------------+--------------+---------+--------
 test15_pkey |         2200 | p       | {1}
(1 row)

omm=# --或者使用gsql的元命令\d tableName很方便地查看一个表上有哪些约束
omm=# \d test15
            Table "public.test15"
 Column |         Type          | Modifiers  
--------+-----------------------+------------
 id     | bigint                | not null
 name   | character varying(50) | not null
 age    | integer               | default 20
Indexes:
    "test15_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
复制

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

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

omm=# SELECT * FROM information_schema.tables WHERE table_name='test';
-------+----------+---------------
 omm           | public       | test       | BASE TABLE |                              |              
        |                           |                          |                        | YES         
       | NO       | 
(1 row)

 table_catalog | table_schema | table_name | table_type | self_referencing_column_name | reference_gen
eration | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | is_insertabl
e_into | is_typed | commit_action 
---------------+--------------+------------+------------+------------------------------+--------------
--------+---------------------------+--------------------------+------------------------+-------------
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                | 

复制