学习目标
学习openGauss表的创建、搜索路径和访问方法等
课程学习
演示查看修改当前会话的模式搜索顺序，创建查看不同模式下的表
课程作业
1.创建一个表（默认，不指定模式），查看该表在哪个模式下
/*
默认情况下，在某个数据库上创建的数据库对象都位于该数据库中的public模式中。
--查看默认搜索路径：
show SEARCH_PATH;
--创建一个测试表testtable，并插入一条数据：
drop table if exists test13;
create table test13(com varchar(100));
insert into test13 values('Ni Hao A!');
select * from test13;
*/
omm=# show SEARCH_PATH;
search_path
----------------
"$user",public
(1 row)
omm=# drop table if exists test13;
DROP TABLE
omm=# create table test13(com varchar(100));
CREATE TABLE
omm=# insert into test13 values('Ni Hao A!');
INSERT 0 1
omm=# select * from test13;
com
-----------
Ni Hao A!
(1 row)
/*
--查看该表在哪个模式下
利用数据库系统表pg_tables, 进行查询(或\d \dt查看)
\conninfo
select schemaname,tablename,created from pg_tables where tablename = 'test13';
--同时也可以看下当前数据库下的public模式有哪些表：
select table_catalog,table_schema,table_name,table_type
from information_schema.tables
where table_schema = 'public';
*/
omm=# \conninfo
You are connected to database "omm" as user "omm" via socket in "/tmp" at port "5432".
omm=# select schemaname,tablename,created from pg_tables where tablename = 'test13';
schemaname | tablename | created
------------+-----------+-------------------------------
public | test13 | 2022-12-13 21:10:25.352954+08
(1 row)
omm=# select table_catalog,table_schema,table_name,table_type
omm-# from information_schema.tables
omm-# where table_schema = 'public';
table_catalog | table_schema | table_name | table_type
---------------+--------------+------------+------------
omm | public | test | BASE TABLE
omm | public | customer | BASE TABLE
omm | public | test13 | BASE TABLE
(3 rows)
omm=#
--明显创建的表test13在模式pubilc下，验证默认情况下，在某个数据库上创建的数据库对象都位于该数据库中的public模式中
2.使用一个用户连接到enmdb数据库，测试该用户可以访问不同模式中的表
/*
--创建表空间enmtbs、数据库enmdb、用户user13，并授予用户user13 SYSADMIN权限：
DROP TABLESPACE IF exists enmtbs;
CREATE TABLESPACE enmtbs RELATIVE LOCATION 'tablespace/enmtbs1';
CREATE DATABASE enmdb WITH TABLESPACE = enmtbs;
CREATE USER user13 IDENTIFIED BY 'meiriyilian@1234';
ALTER USER user13 SYSADMIN;
*/
omm=# DROP TABLESPACE IF exists enmtbs;
NOTICE: Tablespace "enmtbs" does not exist, skipping.
DROP TABLESPACE
omm=# CREATE TABLESPACE enmtbs RELATIVE LOCATION 'tablespace/enmtbs1';
CREATE TABLESPACE
omm=# CREATE DATABASE enmdb WITH TABLESPACE = enmtbs;
CREATE DATABASE
omm=# CREATE USER user13 IDENTIFIED BY 'meiriyilian@1234';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# ALTER USER user13 SYSADMIN;
ALTER ROLE
/*
--以数据库用户user13的身份，连接到刚刚创建的数据库enmdb：
gsql -d enmdb -U user13 -W meiriyilian@1234 -r
--创建enmschm1、enmschm2模式
create schema enmschm1;
create schema enmschm2;
*/
omm@modb:~$ gsql -d enmdb -U user13 -W meiriyilian@1234 -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.
enmdb=> \conninfo
You are connected to database "enmdb" as user "user13" via socket in "/tmp" at port "5432".
enmdb=> create schema enmschm1;
CREATE SCHEMA
enmdb=> create schema enmschm2;
CREATE SCHEMA
/*
--测试该用户可以访问不同模式中的表
--分别在不同模式下创建表格test13table
--首先public模式下
show SEARCH_PATH;
create table test13table(col1 char(100));
\dt
select * from test13table;
insert into test13table values('Hello from testtable IN SCHEMA public!');
select * from test13table;
*/
enmdb=> show SEARCH_PATH;
search_path
----------------
"$user",public
(1 row)
enmdb=> create table test13table(col1 char(100));
CREATE TABLE
enmdb=> \dt
List of relations
Schema | Name | Type | Owner | Storage
--------+-------------+-------+--------+----------------------------------
public | test13table | table | user13 | {orientation=row,compression=no}
(1 row)
enmdb=> select * from test13table;
col1
------
(0 rows)
enmdb=> insert into test13table values('Hello from testtable IN SCHEMA public!');
INSERT 0 1
enmdb=> select * from test13table;
col1
------------------------------------------------------------------------------------------------------
Hello from testtable IN SCHEMA public!
(1 row）
/*
--然后enmschm1模式下
show SEARCH_PATH;
SET SEARCH_PATH TO enmschm1;
show SEARCH_PATH;
create table test13table(col1 char(100));
\dt
insert into test13table values('Hello from testtable IN SCHEMA enmschm1!');
select * from test13table;
*/
enmdb=> show SEARCH_PATH;
search_path
----------------
"$user",public
(1 row)
enmdb=> SET SEARCH_PATH TO enmschm1;
SET
enmdb=> show SEARCH_PATH;
search_path
-------------
enmschm1
(1 row)
enmdb=> create table test13table(col1 char(100));
\dt
CREATE TABLE
enmdb=> enmdb=> List of relations
Schema | Name | Type | Owner | Storage
----------+-------------+-------+--------+----------------------------------
enmschm1 | test13table | table | user13 | {orientation=row,compression=no}
(1 row)
insert into test13table values('Hello from testtable IN SCHEMA enmschm1!');
INSERT 0 1
enmdb=> select * from test13table;
col1
------------------------------------------------------------------------------------------------------
Hello from testtable IN SCHEMA enmschm1!
(1 row)
/*
--最后enmschm1模式下
show SEARCH_PATH;
SET SEARCH_PATH TO enmschm2;
show SEARCH_PATH;
create table test13table(col1 char(100));
\dt
insert into test13table values('Hello from testtable IN SCHEMA enmschm2!');
select * from test13table;
*/
enmdb=> show SEARCH_PATH;
search_path
-------------
enmschm1
(1 row)
enmdb=> SET SEARCH_PATH TO enmschm2;
SET
enmdb=> show SEARCH_PATH;
search_path
-------------
enmschm2
(1 row)
enmdb=> create table test13table(col1 char(100));
CREATE TABLE
enmdb=> \dt
List of relations
Schema | Name | Type | Owner | Storage
----------+-------------+-------+--------+----------------------------------
enmschm2 | test13table | table | user13 | {orientation=row,compression=no}
(1 row)
enmdb=> insert into test13table values('Hello from testtable IN SCHEMA enmschm2!');
INSERT 0 1
enmdb=> select * from test13table;
col1
------------------------------------------------------------------------------------------------------
Hello from testtable IN SCHEMA enmschm2!
(1 row)
3.在会话级设置模式搜索路径为:模式enmschm1,使用SchemaName.TableName的表标识方法访问表(创建表、插入数据和查询表中数据)
/*
--在会话级设置模式搜索路径为:模式enmschm1：
show SEARCH_PATH;
SET SEARCH_PATH TO enmschm1;
show SEARCH_PATH;
--在数据库enmdb的模式enmschm2中创建表testtable、testtable1：
create table enmschm2.testtable(col1 char(100));
create table enmschm2.testtable1(col1 char(100));
select * from enmschm2.testtable;
select * from enmschm2.testtable1;
--向模式enmschm2中新创建的表testtable，插入一行数据：
insert into enmschm2.testtable values('Hello from testtable IN SCHEMA enmschm2!');
--查看表中内容：
select * from enmschm2.testtable;
*/
enmdb=> show SEARCH_PATH;
search_path
-------------
enmschm2
(1 row)
enmdb=> SET SEARCH_PATH TO enmschm1;
SET
enmdb=> show SEARCH_PATH;
search_path
-------------
enmschm1
(1 row)
enmdb=> create table enmschm2.testtable(col1 char(100));
CREATE TABLE
enmdb=> create table enmschm2.testtable1(col1 char(100));
CREATE TABLE
enmdb=> select * from enmschm2.testtable;
col1
------
(0 rows)
enmdb=> select * from enmschm2.testtable1;
col1
------
(0 rows)
enmdb=> --向模式enmschm2中新创建的表testtable，插入一行数据：
enmdb=> insert into enmschm2.testtable values('Hello from testtable IN SCHEMA enmschm2!');
INSERT 0 1
enmdb=>
enmdb=> enmdb=> --查看表中内容：
select * from enmschm2.testtable;
col1
------------------------------------------------------------------------------------------------------
Hello from testtable IN SCHEMA enmschm2!
(1 row)
最后修改时间：2022-12-13 23:55:20
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
