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

学习目标

学习查看表的相关信息

课程学习

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

SELECT * FROM information_schema.tables WHERE table_name='test';
课程作业
1.创建表和约束
```sql
omm=# create table t1( id int ,name char(30) not null ,age int default 20,primary key(id));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
CREATE TABLE
```
2.使用\d tableNmae命令查看表的定义、模式和所有者
```sql

omm=# \d t1
          Table "public.t1"
 Column |     Type      | Modifiers  
--------+---------------+------------
 id     | integer       | not null
 name   | character(30) | not null
 age    | integer       | default 20
Indexes:
    "t1_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default

omm=# \d+ t1;
                              Table "public.t1"
 Column |     Type      | Modifiers  | Storage  | Stats target | Description 
--------+---------------+------------+----------+--------------+-------------
 id     | integer       | not null   | plain    |              | 
 name   | character(30) | not null   | extended |              | 
 age    | integer       | default 20 | plain    |              | 
Indexes:
    "t1_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

omm=# \dt+ t1;
                                    List of relations
 Schema | Name | Type  | Owner |  Size   |             Storage              | Description 
--------+------+-------+-------+---------+----------------------------------+-------------
 public | t1   | table | omm   | 0 bytes | {orientation=row,compression=no} | 
(1 row)

```

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

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


```

4.查看一个表下有哪些约束
```sql
omm=# select conname, connamespace, contype, conkey     
omm-#   from pg_constraint 
omm-#  where conrelid in (  select oid
omm(# omm(#                   from pg_class
                 where relname='t1');
(1 row)

omm=#  conname | connamespace | contype | conkey 
---------+--------------+---------+--------
 t1_pkey |         2200 | p       | {1}

omm=# \d t1
          Table "public.t1"
 Column |     Type      | Modifiers  
--------+---------------+------------
 id     | integer       | not null
 name   | character(30) | not null
 age    | integer       | default 20
Indexes:
    "t1_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default



```

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

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

```