openGauss每日一练第15天|表管理3

1.创建表和约束。

--创建mytest表：
drop table if exists mytest;
create table mytest(id bigint,name varchar(50) not null,age  int default 20,primary key(id));

omm=# drop table if exists mytest;
NOTICE:  table "mytest" does not exist, skipping
DROP TABLE
omm=# create table mytest(id bigint,name varchar(50) not null,age  int default 20,primary key(id));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "mytest_pkey" for table "mytest"
CREATE TABLE
omm=# 

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

\d+ mytest;
\dt+ mytest;

omm=#  \d+ mytest;
                                Table "public.mytest"
 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:
    "mytest_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

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

omm=# 

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

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

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

omm=# 

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

--查看约束名称、约束类型
select conname,connamespace,contype,conkey from pg_constraint where conrelid in(select oid from pg_class where relname='mytest');
或者
\d

omm=# select conname,connamespace,contype,conkey from pg_constraint where conrelid in(select oid from pg_class where relname='mytest');
omm=#    conname   | connamespace | contype | conkey 
-------------+--------------+---------+--------
 mytest_pkey |         2200 | p       | {1}
(1 row)


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

omm=# ```

**5.查看一个表属于数据库的哪个模式。**
```sql
--查看一个表属于数据库的哪个模式
\x 
SELECT * FROM information_schema.tables WHERE table_name='mytest';

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

omm=#