openGauss一个数据库中可以创建多个模式
openGauss一个数据库中可以创建多个模式
·
1.查看当前数据库下有哪些模式
--进入数据库omm,创建测试用户user07、表空间music_tbs07、数据库musicdb07
[omm@ogdb1 ~]$ gsql -d omm -p 40000 -r
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 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 | SQL_ASCII | C | C |
postgres | omm | SQL_ASCII | C | C |
template0 | omm | SQL_ASCII | C | C | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | SQL_ASCII | C | C | =c/omm +
| | | | | omm=CTc/omm
(4 rows)
--执行下面的SQL语句,创建用户user07,授予user07数据库系统的SYSADMIN权限:
omm=# create user user07 identified by 'aabb@123';
CREATE ROLE
omm=# alter user user07 sysadmin;
ALTER ROLE
--创建测试表空间、测试数据库
omm=# create tablespace music_tbs07 relative location 'tablespace/test_ts07';
CREATE TABLESPACE
omm=#
omm=# create database musicdb07 with tablespace=music_tbs07;
CREATE DATABASE
--使用用户user07连接到数据库musicdb07,首先查看当前数据库下有哪些模式;
omm=# \q
[omm@ogdb1 ~]$ gsql -d musicdb07 -U user07 -p 40000 -r
Password for user user07:
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
musicdb07=> \dn
List of schemas
Name | Owner
-----------------+-------
blockchain | omm
cstore | omm
db4ai | omm
dbe_perf | omm
dbe_pldebugger | omm
dbe_pldeveloper | omm
dbe_sql_util | omm
pkg_service | omm
public | omm
snapshot | omm
sqladvisor | omm
(11 rows)
musicdb07=>
2.然后为数据库musicdb创建4个模式,名称自定义
–用户user07在数据库musicdb07中,创建了4个模式:
musicdb07=> create schema c07schema1 authorization user07;
CREATE SCHEMA
musicdb07=> create schema c07schema2 authorization user07;
CREATE SCHEMA
musicdb07=> create schema c07schema3 authorization user07;
CREATE SCHEMA
musicdb07=> create schema c07schema4 authorization user07;
CREATE SCHEMA
–查看musicdb07数据库下有哪些模式:
musicdb07=> \dn
List of schemas
Name | Owner
-----------------+--------
blockchain | omm
c07schema1 | user07
c07schema2 | user07
c07schema3 | user07
c07schema4 | user07
cstore | omm
db4ai | omm
dbe_perf | omm
dbe_pldebugger | omm
dbe_pldeveloper | omm
dbe_sql_util | omm
pkg_service | omm
public | omm
snapshot | omm
sqladvisor | omm
(15 rows)
–除了可以用gsql的元命令\dn来查看数据库有哪些模式,还可以执行下面的SQL语句,查看某个数据库下有哪些模式:
musicdb07=> select catalog_name, schema_name, schema_owner from information_schema.schemata;
catalog_name | schema_name | schema_owner
--------------+--------------------+--------------
musicdb07 | pg_toast | omm
musicdb07 | cstore | omm
musicdb07 | pkg_service | omm
musicdb07 | dbe_perf | omm
musicdb07 | snapshot | omm
musicdb07 | blockchain | omm
musicdb07 | pg_catalog | omm
musicdb07 | public | omm
musicdb07 | sqladvisor | omm
musicdb07 | dbe_pldebugger | omm
musicdb07 | dbe_pldeveloper | omm
musicdb07 | dbe_sql_util | omm
musicdb07 | information_schema | omm
musicdb07 | db4ai | omm
musicdb07 | c07schema1 | user07
musicdb07 | c07schema2 | user07
musicdb07 | c07schema3 | user07
musicdb07 | c07schema4 | user07
(18 rows)
3.在数据库musicdb07的不同的模式下创建同名的表
--在不同模式下,创建相同的表
musicdb07=> create table c07schema1.testtab(col varchar(100));
CREATE TABLE
musicdb07=> create table c07schema2.testtab(col varchar(100));
CREATE TABLE
musicdb07=> create table c07schema3.testtab(col varchar(100));
CREATE TABLE
musicdb07=> create table c07schema4.testtab(col varchar(100));
CREATE TABLE
--执行下面的SQL语句,往4个模式中的表testtab分别插入一条数据:
--在同一个数据库下,可以直接使用SchemaName.TableName来指定一个表,可以省略数据库名。
musicdb07=> insert into c07schema1.testtab values('Hello! from schema c07schema1 11111');
INSERT 0 1
musicdb07=> insert into c07schema1.testtab values('Hello! from schema c07schema2 22222');
INSERT 0 1
musicdb07=> insert into c07schema3.testtab values('Hello! from schema c07schema3 33333');
INSERT 0 1
musicdb07=> insert into c07schema4.testtab values('Hello! from schema c07schema4 44444');
INSERT 0 1
musicdb07=>
--执行下面的SQL语句,查看musicdb07数据库目前有哪些表
--创建视图:
musicdb07=> create or replace view my_tables as select table_catalog, table_schema, table_name, table_type from information_schema.tables where
musicdb07-> table_schema not in ('pg_catalog', 'information_schema', 'dbe_perf');
CREATE VIEW
--查看视图:
musicdb07=> select * from my_tables;
table_catalog | table_schema | table_name | table_type
---------------+-----------------+------------+------------
musicdb07 | db4ai | snapshot | BASE TABLE
musicdb07 | dbe_pldeveloper | gs_errors | BASE TABLE
musicdb07 | dbe_pldeveloper | gs_source | BASE TABLE
musicdb07 | public | my_tables | VIEW
musicdb07 | c07schema4 | testtab | BASE TABLE
musicdb07 | c07schema3 | testtab | BASE TABLE
musicdb07 | c07schema2 | testtab | BASE TABLE
musicdb07 | c07schema1 | testtab | BASE TABLE
(8 rows)
musicdb07=>
4.访问musicdb数据库下不同模式的同名表
--查看默认的搜索模式的顺序
musicdb07=> show SEARCH_PATH;
search_path
----------------
"$user",public
(1 row)
--访问musicdb07数据库下其他模式的表,需要指定模式名前缀:
musicdb07=> select * from c07schema1.testtab;
col
---------------------------------------
Hello! from schema c07schema1 11111
(1 rows)
musicdb07=> select * from c07schema2.testtab;
col
---------------------------------------
Hello! from schema c07schema2 22222
(1 row)
musicdb07=> select * from c07schema3.testtab;
col
---------------------------------------
Hello! from schema c07schema3 33333
(1 row)
musicdb07=> select * from c07schema4.testtab;
col
---------------------------------------
Hello! from schema c07schema4 44444
(1 row)
5.实验理解:模式是在数据库层面,用户是在实例层面
--登录musicdb07数据库,查看用户和模式
[omm@ogdb1 ~]$ gsql -d musicdb07 -p 40000 -U user07 -r
Password for user user07:
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
musicdb07=> \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
user07 | Sysadmin | {}
musicdb07=> \dn
List of schemas
Name | Owner
-----------------+--------
blockchain | omm
c07schema1 | user07
c07schema2 | user07
c07schema3 | user07
c07schema4 | user07
cstore | omm
db4ai | omm
dbe_perf | omm
dbe_pldebugger | omm
dbe_pldeveloper | omm
dbe_sql_util | omm
pkg_service | omm
public | omm
snapshot | omm
sqladvisor | omm
(15 rows)
musicdb07=>
--登录omm数据库,查看用户和模式
[omm@ogdb1 ~]$ gsql -d omm -p 40000 -r
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
omm=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
user07 | Sysadmin | {}
omm=# \dn
List of schemas
Name | Owner
-----------------+--------
blockchain | omm
cstore | omm
db4ai | omm
dbe_perf | omm
dbe_pldebugger | omm
dbe_pldeveloper | omm
dbe_sql_util | omm
pkg_service | omm
public | omm
snapshot | omm
sqladvisor | omm
user07 | user07
(12 rows)
--可以看到模式是在数据库层面,用户是在实例层面
更多推荐
已为社区贡献21条内容
所有评论(0)