概述

本文档记录openGauss 3.0.0数据库每日一练第15天课程作业，学习学习查看表的相关信息。

课程练习

创建表和约束

1、创建数据库musicdb15
2、在数据库musicdb15 创建schema schm1/schm12/schm3
3、在数据库创建表test1、schm1.test1_m1、schm1.test2_m1、schm1.test2_m1、schm2.test3_m2

omm@local:/opt/huawei/tmp [postgres]=#CREATE TABLESPACE day15 RELATIVE LOCATION 'tablespace/day15';
CREATE TABLESPACE
omm@local:/opt/huawei/tmp [postgres]=#CREATE DATABASE musicdb15 WITH TABLESPACE = day15;
CREATE DATABASE
omm@local:/opt/huawei/tmp [postgres]=#CREATE USER user15 IDENTIFIED BY 'zs@123456';
CREATE ROLE
omm@local:/opt/huawei/tmp [postgres]=#ALTER USER user15  SYSADMIN;
ALTER ROLE
omm@local:/opt/huawei/tmp [postgres]=#\c musicdb15 user15
Password for user user15: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb15" as user "user15".
user15@local:/opt/huawei/tmp [musicdb15]=>create schema schm1;
CREATE SCHEMA
user15@local:/opt/huawei/tmp [musicdb15]=>create schema schm2;
CREATE SCHEMA
user15@local:/opt/huawei/tmp [musicdb15]=>create schema schm3;
CREATE SCHEMA
user15@local:/opt/huawei/tmp [musicdb15]=>create table test1(
musicdb15(>       id bigint,
musicdb15(>       name varchar(50) not null,  -- 创建列级not null约束
musicdb15(>       age  int,
musicdb15(>       primary key(id) -- 创建表级约束
musicdb15(>      );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test1_pkey" for table "test1"
CREATE TABLE
user15@local:/opt/huawei/tmp [musicdb15]=>create table schm1.test1_m1(
musicdb15(>       id bigint,
musicdb15(>       name varchar(50) not null,  -- 创建列级not null约束
musicdb15(>       age  int,
musicdb15(>       primary key(id) -- 创建表级约束
musicdb15(>      );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test1_m1_pkey" for table "test1_m1"
CREATE TABLE
user15@local:/opt/huawei/tmp [musicdb15]=>create table schm1.test2_m1(
musicdb15(>       id bigint,
musicdb15(>       name varchar(50) not null,  -- 创建列级not null约束
musicdb15(>       age  int,
musicdb15(>       primary key(id) -- 创建表级约束
musicdb15(>      ); 
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test2_m1_pkey" for table "test2_m1"
CREATE TABLE
user15@local:/opt/huawei/tmp [musicdb15]=>create table schm2.test3_m2(
musicdb15(>       id bigint,
musicdb15(>       name varchar(50) not null,  -- 创建列级not null约束
musicdb15(>       age  int,
musicdb15(>       primary key(id) -- 创建表级约束
musicdb15(>      ); 
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test3_m2_pkey" for table "test3_m2"
CREATE TABLE

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

user15@local:/opt/huawei/tmp [musicdb15]=>\d test1
            Table "public.test1"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | bigint                | not null
 name   | character varying(50) | not null
 age    | integer               | 
Indexes:
    "test1_pkey" PRIMARY KEY, btree (id) TABLESPACE day15

user15@local:/opt/huawei/tmp [musicdb15]=>\d schm1.test1_m1
           Table "schm1.test1_m1"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | bigint                | not null
 name   | character varying(50) | not null
 age    | integer               | 
Indexes:
    "test1_m1_pkey" PRIMARY KEY, btree (id) TABLESPACE day15

user15@local:/opt/huawei/tmp [musicdb15]=>\d schm1.test2_m1
           Table "schm1.test2_m1"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | bigint                | not null
 name   | character varying(50) | not null
 age    | integer               | 
Indexes:
    "test2_m1_pkey" PRIMARY KEY, btree (id) TABLESPACE day15

user15@local:/opt/huawei/tmp [musicdb15]=>\d schm2.test3_m2
           Table "schm2.test3_m2"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | bigint                | not null
 name   | character varying(50) | not null
 age    | integer               | 
Indexes:
    "test3_m2_pkey" PRIMARY KEY, btree (id) TABLESPACE day15

查看某个模式下有哪些表

user15@local:/opt/huawei/tmp [musicdb15]=>SELECT table_schema,table_name FROM information_schema.tables WHERE table_schema='public';
 table_schema | table_name 
--------------+------------
 public       | test1
(1 row)

user15@local:/opt/huawei/tmp [musicdb15]=>SELECT table_schema,table_name FROM information_schema.tables WHERE table_schema='schm1';
 table_schema | table_name 
--------------+------------
 schm1        | test1_m1
 schm1        | test2_m1
(2 rows)

user15@local:/opt/huawei/tmp [musicdb15]=>SELECT table_schema,table_name FROM information_schema.tables WHERE table_schema='schm2';
 table_schema | table_name 
--------------+------------
 schm2        | test3_m2
(1 row)

user15@local:/opt/huawei/tmp [musicdb15]=>SELECT table_schema,table_name FROM information_schema.tables WHERE table_schema='schm3';
 table_schema | table_name 
--------------+------------
(0 rows)

查看一个表下有哪些约束

user15@local:/opt/huawei/tmp [musicdb15]=>select conname, connamespace, contype, conkey     
musicdb15->   from pg_constraint 
musicdb15->  where conrelid in (  select oid
musicdb15(>                   from pg_class
musicdb15(>                  where relname='test1_m1');
    conname    | connamespace | contype | conkey 
---------------+--------------+---------+--------
 test1_m1_pkey |        24691 | p       | {1}
(1 row)

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

user15@local:/opt/huawei/tmp [musicdb15]=>\x
Expanded display is on.
user15@local:/opt/huawei/tmp [musicdb15]=>SELECT * FROM information_schema.tables WHERE table_name='test1_m1'
;
-[ RECORD 1 ]----------------+-----------
table_catalog                | musicdb15
table_schema                 | schm1
table_name                   | test1_m1
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