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

课程学习内容

软件版本 ：openGauss 3.0.0

再次熟悉表的约束、表的默认值、自增类型等内容

数据库实操内容

环境准备

su - omm

gsql -r

root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=# 

测试环境准备

–首先创建一张测试表。

drop table if exists test; create table test( id bigint, name varchar(50) not null, age int default 20, primary key(id) );

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=# 

在gsql中查看表的定义、模式和所有者

–在gsql中，使用\d tableNmae命令查看表的定义。

\d+ test;

omm=# \d+ test;
 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

omm=#                                  Table "public.test"
 Column |         Type          | Modifiers  | Storage  | Stats target | Description 
--------+-----------------------+------------+----------+--------------+-------------

omm=# 

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

\dt+ test;

omm=# \dt+ test;
 public | test | table | omm   | 0 bytes | {orientation=row,compression=no} | 
(1 row)

omm=#                                     List of relations
 Schema | Name | Type  | Owner |  Size   |             Storage              | Description 
--------+------+-------+-------+---------+----------------------------------+-------------

omm=# 

查看某个模式下有哪些表

–查看public模式下的所有表名：

SELECT table_name FROM information_schema.tables WHERE table_schema=‘public’;

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


omm=# 

查看一个表下有哪些约束

–查看约束名称、约束类型

select conname, connamespace, contype, conkey

​ from pg_constraint

where conrelid in ( select oid

​ from pg_class

​ where relname=‘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');
(1 row)

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

omm=# 

–或者使用gsql的元命令\d tableName很方便地查看一个表上有哪些约束

\d test

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

SELECT * FROM information_schema.tables WHERE table_name=‘test’;

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
commit_action                | 

omm=# 

课程作业

创建表和约束

omm=# drop table if exists test1;
NOTICE:  table "test1" does not exist, skipping
DROP TABLE
omm=# create table test1(
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 "test1_pkey" for table "test1"
CREATE TABLE
omm=# 

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

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

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

omm=# 

查看某个模式下有哪些表

omm=# SELECT table_name FROM information_schema.tables WHERE table_schema='public';
-[ RECORD 1 ]-----
table_name | test
-[ RECORD 2 ]-----
table_name | test1

omm=# 

查看一个表下有哪些约束

omm=# select conname, connamespace, contype, conkey     
omm-#   from pg_constraint 
omm-#  where conrelid in (  select oid
omm(#                   from pg_class
omm(#                  where relname='test1');
-[ RECORD 1 ]+-----------
conname      | test1_pkey
connamespace | 2200
contype      | p
conkey       | {1}

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

omm=# 

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

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

课程学习体会

 通过本次的实训操作，再次学习数据库表的定义、模式和所有者，以及表下的约束，表模式下的表。