一、学习目标
本节课的重点是学习openGauss数据库表的创建、搜索路径及访问方法这几个知识点。
1.1 建表语法格式
参照openGauss官网，openGauss的建表所对应的语法格式如下：
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
({ column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]
| table_constraint
| LIKE source_table [ like_option [...] ] }
[, ... ])
[ WITH ( {storage_parameter = value} [, ... ] ) ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ COMPRESS | NOCOMPRESS ]
[ TABLESPACE tablespace_name ];
-- 其中列约束column_constraint为：
[ CONSTRAINT constraint_name ]
{ NOT NULL |
NULL |
CHECK ( expression ) |
DEFAULT default_expr |
UNIQUE index_parameters |
PRIMARY KEY index_parameters }
[ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
-- 其中列的压缩可选项compress_mode为：
{ DELTA | PREFIX | DICTIONARY | NUMSTR | NOCOMPRESS }
-- 其中表约束table_constraint为：
[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) |
UNIQUE ( column_name [, ... ] ) index_parameters |
PRIMARY KEY ( column_name [, ... ] ) index_parameters |
PARTIAL CLUSTER KEY ( column_name [, ... ] ) }
[ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
表分普通表和临时表，创建普通表和临时表要遵循如下语法格式：
**UNLOGGED**
如果指定此关键字，则创建的表为非日志表。在非日志表中写入的数据不会被写入到预写日志中，这样就会比普通表快很多。但是非日志表在冲突、执行操作系统重启、强制重启、切断电源操作或异常关机后会被自动截断，会造成数据丢失的风险。非日志表中的内容也不会被复制到备服务器中。在非日志表中创建的索引也不会被自动记录。
使用场景：非日志表不能保证数据的安全性，用户应该在确保数据已经做好备份的前提下使用，例如系统升级时进行数据的备份。
故障处理：当异常关机等操作导致非日志表上的索引发生数据丢失时，用户应该对发生错误的索引进行重建。
GLOBAL | LOCAL
创建临时表时可以在TEMP或TEMPORARY前指定GLOBAL或LOCAL关键字。目前这两个关键字的设立，仅是为了兼容SQL标准，实际上无论指定GLOBAL还是LOCAL，openGauss都会创建本地临时表。
TEMPORARY | TEMP
如果指定TEMP或TEMPORARY关键字，则创建的表为临时表。临时表只在当前会话可见，本会话结束后会自动删除。因此，在除当前会话连接的数据库节点故障时，仍然可以在当前会话上创建和使用临时表。由于临时表只在当前会话创建，对于涉及对临时表操作的DDL语句，会产生DDL失败的报错。因此，建议DDL语句中不要对临时表进行操作。TEMP和TEMPORARY等价。
1.2 搜索路径
在设置模式搜路径时，会有会话级设置、数据库级别设置、用户级设置，会话级设置对于当前存在的活动会话有效，一旦退出该会话，设置的模式搜索路径失效，数据库级是对整个数据库级别进行设置,用户级设置是对当前用户生效，会话退出仍然生效，所有的会话都生效。会话级设置是通过SET SEARCH_PATH TO命令进行设置，数据库级设置是通过ALTER DATABASE xxx SET SEARCH_PATH TO进行设置，用户级设置是通过ALTER USER xxx SET SEARCH_PATH TO xxx。
二、测试练习
2.1 创建表
-- 要创建表，就要首先有承载表的载体表空间，表空间首先要有数据库存在
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=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+---------+-------+-------------------
omm | omm | UTF8 | C | C |
postgres | omm | UTF8 | C | C |
template0 | omm | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
(4 rows)
-- 查看访问路径
omm=# show SEARCH_PATH;
search_path
----------------
"$user",public
(1 row)
omm=# create table optb1(message varchar(100));
CREATE TABLE
omm=# insert into optb1 values('Hello from testtable!');
INSERT 0 1
omm=# select * from optb1;
omm=# message
-----------------------
Hello from testtable!
(1 row)
-- 查看public模式下存在哪些表
-- 创建表时如果未指定schema,会默认创建在public模式下
omm=# select table_catalog,table_schema,table_name,table_type
omm-# from information_schema.tables
omm-# where table_schema = 'public';
omm=# table_catalog | table_schema | table_name | table_type
---------------+--------------+------------+------------
omm | public | optb1 | BASE TABLE
(1 row)
-- 可以根据如下语句通过表查询其所在的schema
omm=# select t1.nspname,t2.relname from pg_namespace t1,pg_class t2 where t1.oid = t2.relnamespace and t2.relname = 'optb1';
nspname | relname
---------+---------
public | optb1
(1 row)
omm=# CREATE TABLESPACE enmtbs RELATIVE LOCATION 'tablespace/enmtbs';
CREATE TABLESPACE
omm=# CREATE DATABASE enmdb WITH TABLESPACE = enmtbs;
CREATE DATABASE
omm=# CREATE USER opuser IDENTIFIED BY 'kunpeng@1234';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# ALTER USER opuser SYSADMIN;
ALTER ROLE
omm=# \c enmdb
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "enmdb" as user "omm".
-- 在enmdb下创建两个schema
enmdb=# create schema schema01;
CREATE SCHEMA
enmdb=# create schema schema02;
CREATE SCHEMA
-- 可以在不同的schema下创建同名表
enmdb=# create table schema01.tab01 (id int);
CREATE TABLE
enmdb=# insert into schema01.tab01 values (1);
INSERT 0 1
enmdb=# create table schema02.tab01 (message varchar(10));
CREATE TABLE
enmdb=# insert into schema02.tab01 values ('Hello,openGauss');
ERROR: value too long for type character varying(10)
CONTEXT: referenced column: message
enmdb=# insert into schema02.tab01 values ('Hello op');
INSERT 0 1
enmdb=# select * from schema02.tab01;
message
----------
Hello op
(1 row)
2.2 测试同一用户访问不同模式下表
-- 使用上面创建的opuser用户连接enmdb数据库
omm@modb:~$ gsql -d enmdb -U opuser -W kunpeng@1234
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.
-- 查询当前数据库下有哪些schema
enmdb=> \dn+
List of schemas
Name | Owner | Access privileges | Description | WithBlockChain
-----------------+-------+-------------------+----------------------------------+----------------
blockchain | omm | | blockchain schema | f
cstore | omm | | reserved schema for DELTA tables | f
db4ai | omm | omm=UC/omm +| db4ai schema | f
| | =U/omm | |
dbe_perf | omm | | dbe_perf schema | f
dbe_pldebugger | omm | omm=UC/omm +| dbe_pldebugger schema | f
| | =U/omm | |
pkg_service | omm | | pkg_service schema | f
public | omm | omm=UC/omm +| standard public schema | f
dbe_pldeveloper | omm | omm=UC/omm +| dbe_pldeveloper schema | f
| | =U/omm | |
| | =U/omm | |
schema01 | omm | | | f
schema02 | omm | | | f
snapshot | omm | | snapshot schema | f
sqladvisor | omm | omm=UC/omm +| sqladvisor schema | f
| | =U/omm | |
(12 rows)
-- 同一个用户opuser可以访问并插入不同schema下的表
enmdb=> select * from schema01.tab01;
id
----
1
2
(2 rows)
enmdb=> insert into schema01.tab01 values (3);
INSERT 0 1
enmdb=> select * from schema01.tab01;
id
----
1
2
3
(3 rows)
enmdb=> select * from schema02.tab01;
message
----------
Hello op
(1 row)
enmdb=> insert into schema02.tab01 values('GaussDB');
INSERT 0 1
enmdb=> select * from schema02.tab01;
message
----------
Hello op
GaussDB
(2 rows)
2.3 会话级设置访问路径并查询模式下的表
omm@modb:~$ gsql -d enmdb -U opuser -W kunpeng@1234
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.
-- 查询访问路径
enmdb=> show SEARCH_PATH;
search_path
----------------
"$user",public
(1 row)
-- 设置会话级访问路径
enmdb=> SET SEARCH_PATH TO opuser;
SET
enmdb=> show SEARCH_PATH;
search_path
-------------
opuser
(1 row)
enmdb=> \dn
List of schemas
Name | Owner
-----------------+-------
blockchain | omm
cstore | omm
db4ai | omm
dbe_perf | omm
dbe_pldebugger | omm
dbe_pldeveloper | omm
pkg_service | omm
public | omm
schema01 | omm
schema02 | omm
snapshot | omm
sqladvisor | omm
(12 rows)
-- 在模式下创建并查询表数据
enmdb=> create table schema01.newtb (name varchar(20));
CREATE TABLE
enmdb=> insert into schema01.newtb values ('shanglei');
INSERT 0 1
enmdb=> select * from schema01.newtb;
name
----------
shanglei
(1 row)
三、学习心得
通过本节课的学习，要特别了解几个概念、表、表空间、数据库及模式，要多去练习在不同模式下创建相应表，从数据库级、会话级、用户级设置搜索路径，通过不同模式搜索路径查询不同模式下的表。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
