openGauss每日一练第15天 | 学习心得体会

学习目标
学习查看表的相关信息

1.创建表和约束
表级主键：id bigint，primary key(id) 名称+类型 ，主键字端（名称）；
create table test(
id bigint, — 字段1
name varchar(50) not null, — 字段2
age int default 20, — 字段3
primary key(id) — 主键约束为字段1
);

2.使用\d tableNmae命令查看表的定义、模式和所有者
\d \d+ \dt \dt+ 命令区别：多了size及Description 选项；
Size | 0 bytes
Description |

3.查看某个模式下有哪些表
SELECT table_name FROM information_schema.tables WHERE table_schema=‘模式’;

4.查看一个表下有哪些约束
select conname, connamespace, contype, conkey
from pg_constraint
where conrelid in ( select oid
from pg_class
where relname=‘表名’);
或者使用\d \d+方式查看；

5.查看一个表属于数据库的哪个模式
SELECT * FROM information_schema.tables WHERE table_name=‘表名’;

课程作业：

--首先创建一张测试表。
omm=# drop table if exists test;
NOTICE:  table "test" does not exist, skipping
DROP TABLE
omm=# create table test(
omm(# id bigint,
omm(# name varchar(50) not null,
omm(# age  int default 20,
omm(# primary key(id)
     );
omm(# NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
omm=# 
omm=# \dt
                        List of relations
 Schema | Name | Type  | Owner |             Storage              
--------+------+-------+-------+----------------------------------
 public | test | table | omm   | {orientation=row,compression=no}
(1 row)

--在gsql中，使用\d tableNmae命令查看表的定义。
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

--在gsql中，使用\d tableNmae命令查看表的模式和所有者

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

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

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

--或者使用gsql的元命令\d tableName很方便地查看一个表上有哪些约束
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=# \x 
Expanded display is on.
omm=# 
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