SCHEMA

SCHEMA(模式)就是数据库对象的集合。所谓的数据库对象也就是常说的表、索引、视图和存储过程等。相同的对象名称可以在不同的模式中使用而不会发生冲突。与数据库不同，模式不是严格分开的：用户可以通过指定数据库，访问他所连接的数据库中任何模式中的对象。模式具有“增删改查”四种操作。

CREATE SCHEMA

​ 任何用户拥有父数据库的CREATE权限，即可执行创建新模式的操作。创建成功后，root用户拥有该模式的全部权限：CREATE，DROP，USAGE权限。当创建用户不是root用户时，则除root角色外，该用户也同样拥有该模式的全部权限。

​ 每条CREATE SCHEMA创建一个模式。在数据库创建的同时，已经默认为数据库创建了一个“public”模式，且将其USAGE权限赋给public角色，使所有用户均可访问public模式。与其他模式相同，public模式也可被删除并创建，但被重新创建的public模式并不会再将其USAGE权限赋给public角色。

​ 创建模式存在两种情况，第一种情况是用户同数据库操作，即在当前数据库下直接创建模式；第二种情况是用户跨数据库操作，即使用户当前处于数据库“a”中，仍可通过指定数据库在数据库“b”中创建模式。

语法格式

创建SCHEMA的语法格式如下：

SCHEMA - 图1

如果添加了IF NOT EXISTS，当要创建的模式不存在时，创建模式；如果已经存在，创建模式不成功，但是不抛出错误。

如果不添加IF NOT EXISTS，当要创建的模式不存在时，创建模式；如果已经存在，创建模式不成功，抛出错误：模式已存在。

参数说明

  • schema_name

    新建模式名称。可以是schema_name或者database_name.schema_name。如果只有schema_name，在当前数据库创建名称为schema_name的SCHEMA；如果跨库创建SCHEMA，需要指定数据库名称，使用database_name.schema_name。

示例

示例1：当前数据库创建名称为s1的SCHEMA。

CREATE SCHEMA s1;

CREATE SCHEMA

示例2：在当前数据库创建已经存在的名称为s1的SCHEMA，创建不成功但不抛出错误。

CREATE SCHEMA IF NOT EXISTS s1;

CREATE SCHEMA

示例3：跨数据库在数据库db1中创建名称为s1的SCHEMA。

CREATE SCHEMA db1.s1;

CREATE SCHEMA

示例4：创建SCHEMA s1并在其下面创建表和视图(当前SCHEMA不是s1时)。

本示例主要展示的是可通过SCHEMA名+“.”+对象名（表，视图等）的方式对SCHEMA下面的对象进行访问以及相关操作。

注意：进入到特定的SCHEMA下面，使用以下方式：

SET search_path= schema_name.

CREATE SCHEMA s1;

CREATE SCHEMA

SHOW search_path;

search_path

+——————-+

public

CREATE TABLE s1.t1 (id INT,address TEXT);

CREATE TABLE

show tables;

table_name

+——————+

(0 rows)

SHOW CREATE TABLE t1;

pq: relation “t1” does not exist

SHOW CREATE TABLE s1.t1;

table_name | create_statement

+——————+—————————————————————-+

s1.t1 | CREATE TABLE t1 ( | id INT NULL, | address TEXT NULL, | FAMILY “primary” (id, address, rowid) | )

(1 row)

SET search_path=s1;

SET

SHOW TABLES;

table_name

+——————+

t1 (1 row)

SHOW CREATE TABLE t1;

table_name | create_statement

+————————+—————————————-+

t1 | CREATE TABLE t1 ( | id INT NULL, | address TEXT NULL, | FAMILY “primary” (id, address, rowid) | )

SHOW CREATE TABLE s1.t1;

table_name | create_statement

+——————+—————————————————————-+

s1.t1 | CREATE TABLE t1 ( | id INT NULL, | address TEXT NULL, | FAMILY “primary” (id, address, rowid) | )

示例5：创建SCHEMA s2并在其下面创建表和视图(进入到SCHEMA s2中)。

CREATE SCHEMA s2;

CREATE SCHEMA

SET search_path=s2;

SET > CREATE TABLE t1 (id INT,address TEXT);

CREATE TABLE

SHOW CREATE TABLE s2.t1;

table_name | create_statement

+——————+—————————————————————-+

s2.t1 | CREATE TABLE t1 ( | id INT NULL, | address TEXT NULL, | FAMILY “primary” (id, address, rowid)

示例6：不同模式下可以存在同名表。

SHOW CREATE TABLE s2.t1;

table_name | create_statement

+——————+—————————————————————-+

s2.t1 | CREATE TABLE t1 ( | id INT NULL, | address TEXT NULL, | FAMILY “primary” (id, address, rowid) | )

SHOW CREATE TABLE s1.t1;

table_name | create_statement

+——————+—————————————————————-+

s1.t1 | CREATE TABLE t1 ( | id INT NULL, | address TEXT NULL, | FAMILY “primary” (id, address, rowid) | )

(1 row) |

在一个数据库中，创建两个及以上同名模式是不允许的，但是可以在不同数据库中分别创建同名的模式。

在创建新的模式时，不能创建数据库原生模式（crdb_internal、information_schema和pg_catalog）和public。

使用模式可以产生以下益处：

  • 允许多个用户使用一个数据库而不干扰其他用户。

  • 把数据库对象组织成逻辑组，让它们便于管理。

  • 第三方的应用可以放在不同的模式中，不会和其他对象的名字冲突。

SHOW SCHEMAS

​ SHOW SCHEMAS可显示目标数据库下的模式（如未指定数据库则默认为当前数据库），包括系统原生的模式、public和用户创建的模式。用户拥有目标数据库下的模式的任意权限，即可显示。

语法格式

查看SCHEMA的语法格式如下：

SCHEMA - 图2

当存在FROM database_name时，查询指定数据库的所有SCHEMA；否则，查询当前数据库的所有SCHEMA。

参数说明

  • database_name

    数据库名称。

示例

示例1：查看SCHEMA。

SHOW SCHEMAS；

schema_name

+————————————————-+

s1

crdb_internal

information_schema

pg_catalog

public (5 rows) |

ALTER SCHEMA

​ ALTER SCHEMA语句可修改模式名称，但不能修改数据库原生模式的名称，也不能修改为数据库中已存在的模式的名称。模式的重命名支持跨数据库库迁移操作，即重命名后的模式可迁移到新的数据库中。任何用户拥有模式重命名后所属数据库的CREATE权限、重命名前原模式和其下全部对象的DROP权限时，即可执行重命名目标模式的操作。修改成功后，该用户拥有该模式的全部权限：CREATE，DROP，USAGE权限，且其他用户保留对该模式和其下对象的原有权限。

语法格式

修改SCHEMA的语法格式如下：

SCHEMA - 图3

如果添加了IF EXISTS，当要修改的SCHEMA存在时，进行修改；不存在时不会抛出错误。

如果不添加IF EXISTS，当要修改的SCHEMA存在时，进行修改；不存在时抛出错误：模式不存在。

参数说明

  • schema_old_name

    SCHEMA当前名称。

  • schema_new_name

    SCHEMA要修改的新名称。

示例

示例1：将名称为s1的SCHEMA修改为s2。

SHOW SCHEMAS；

schema_name

+————————————————-+

s1

crdb_internal

information_schema

pg_catalog

public

ALTER SCHEMA s1 RENAME TO s2;

ALTER SCHEMA

SHOW SCHEMAS；

schema_name

+————————————————-+

s2

crdb_internal

information_schema

pg_catalog

public

DROP SCHEMA

​ 任何用户拥有目标模式和其下全部对象的DROP权限，即可执行删除目标模式的操作。删除成功后，所有用户针对目标模式和其下全部对象的所有权限均被删除。当要删除的模式下存在表（table）时，需要级联（CASCADE）删除，否则不能删除。

语法格式

删除SCHEMA的语法格式如下：

SCHEMA - 图4

如果存在IF EXISTS，当删除的模式存在时才进行删除操作。模式不存在，不抛出错误。

如果不存在IF EXISTS，当删除的模式存在时才进行删除操作。模式不存在，抛出错误：模式不存在。

RESTRICT：默认参数，有约束删除。如果删除的模式中包含数据库对象，则删除失败。需要删除该模式下的所有数据库对象后才能删除该模式。

CASCADE：级联删除。在删除模式时将该模式下所有数据库对象一起删除。

参数说明

  • name_list

    预删除模式名称列表。可同时删除多个模式，当删除多个模式时，模式间以“，”隔开。

示例

示例1：删除模式s2。

SHOW SCHEMAS；

schema_name

+————————————————-+

s2

crdb_internal

information_schema

pg_catalog

public

DROP SCHEMA s2 CASCADE;

DROP SCHEMA

SHOW SCHEMAS；

schema_name

+————————————————-+

crdb_internal

information_schema

pg_catalog

public

示例2：删除不存在的模式s2。添加IF EXISTS，删除错误但是不报错。

DROP SCHEMA IF EXISTS s2 CASCADE;

DROP SCHEMA