课程学习内容
软件版本 ：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=#
