课程作业

1.创建表和约束

--约束条件：primary key、not null、default
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=# create table zouyang(
omm(# id int,
omm(# name char(20) not null,
omm(# age int default 20,
omm(# primary key(id)
omm(#
omm(# );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "zouyang_pkey" for table "zouyang"
CREATE TABLE
omm=#

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

--查看表定义
omm=# \d+ zouyang;
Table "public.zouyang"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------------+------------+----------+--------------+-------------
id | integer | not null | plain | |
name | character(20) | not null | extended | |
age | integer | default 20 | plain | |
Indexes:
"zouyang_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no
--查看模式和所有者
omm=# \dt zouyang;
List of relations
Schema | Name | Type | Owner | Storage
--------+---------+-------+-------+----------------------------------
public | zouyang | table | omm | {orientation=row,compression=no}
(1 row)

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

--查看public模式下有哪些表
omm=# SELECT table_name FROM information_schema.tables WHERE table_schema='public';
table_name
------------
zouyang
(1 row)

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

--方式一：
omm=# \d zouyang

name | character(20) | not null
age | integer | default 20
Indexes:
"zouyang_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default

omm=# Table "public.zouyang"
Column | Type | Modifiers
--------+---------------+------------
id | integer | not null
omm=#
--方式二：
omm=# select conname, connamespace, contype, conkey
omm-# from pg_constraint
omm-# where conrelid in ( select oid
omm(# from pg_class
omm(# where relname='zouyang');
conname | connamespace | contype | conkey
--------------+--------------+---------+--------
zouyang_pkey | 2200 | p | {1}
(1 row)

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

omm=# \x
Expanded display is on.
omm=# SELECT * FROM information_schema.tables WHERE table_name='zouyang';
self_referencing_column_name |
reference_generation |
-[ RECORD 1 ]----------------+-----------
table_catalog | omm
table_schema | public
table_name | zouyang
table_type | BASE TABLE
user_defined_type_catalog |
user_defined_type_schema |
user_defined_type_name |
is_insertable_into | YES
is_typed | NO
commit_action |