课程作业

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 yangkai(                                                
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 "yangkai_pkey" for table "yangkai"
CREATE TABLE
omm=# 	

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

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

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

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

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

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

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

omm=#        Table "public.yangkai"
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='yangkai');
conname    | connamespace | contype | conkey 
--------------+--------------+---------+--------
yangkai_pkey |         2200 | p       | {1}
(1 row)

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

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