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 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'; 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