持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第24天，点击查看活动详情

---

🗻 简介:

往期精选文章，求赞求关注！

🤣一文带你玩转Kubernetes🤣

Proxy 代理的源码和原理解析

Spring + LDAP + JPA 实现事务管理器

容器服务Istio实践 Istio Sidecar 注入方式

Java EasyPoi之复杂多表头多sheet页excel模板导出实例

---

🗺️ PG系列文章:

PG系列文章，想学习更多PG芝士，快来吧🦸🏻‍♂️！

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🔥 （一）

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🍭（二）

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🍌 （三）

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🎵（四）

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL✒️（五）

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🔬（六）

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🐳（七）

[千字长文系列] 🌏探秘PG之一文带你玩转PostgreSQL🗺️（八）

---

总结: 在本文中，将学习PostgreSQL schema以及如何使用schema搜索路径、解析schema中的对象。

🐔 🐓 🐤 🐥 🐣

🐔PostgreSQL schema是什么

在PostgreSQL中，schema是一个命名空间，其中包含名为database的对象，例如表，视图,索引,数据类型,函数,存储过程和标识符。

要访问schema中的对象，我们需要使用以下语法对对象进行限定:

schema_name.object_name

一个数据库可以包含一个或多个schema，并且每个schema仅属于一个数据库。两个schema可以具有共享相同名称的不同对象。

例如，你可能有testschema ，user 表，public schema也具有user 表。当你提到user 表你必须限定如下:

public.user

Or

test.user

🐔为什么需要使用schema

🐓为什么需要使用schema:

• Schema允许我们将数据库对象 (例如表) 组织到逻辑组中，以使它们更易于管理。

• Schema使多个用户能够使用一个数据库而不会相互干扰。

🐔public schema

对于每个新数据库，PostgreSQL自动创建一个名为publicschema。无论我们创建的对象有没有指定schema名称，PostgreSQL都会将其放入publicschema。因此，以下语句是等效的:

CREATE TABLE table_name(
  ...
);

和

CREATE TABLE public.table_name(
   ...
);

🐔schema搜索路径

实际上，引用没有其schema名称的表，例如，user表而不是完全限定的名称，例如test.user表。

当仅使用表的名称引用表时，PostgreSQL使用schema搜索路径,这是要查找的schema列表。

PostgreSQL将访问schema搜索路径中的第一个匹配表。如果没有匹配项，它将返回错误，即使该名称存在于数据库中的另一个schema中。

搜索路径中的第一个schema称为当前schema。请注意，当我们在未显式指定schema名称的情况下创建新对象时，PostgreSQL还将为新对象使用当前schema。

current_schema()函数返回当前schema:

SELECT current_schema();

Output

 current_schema
----------------
public
(1 row)

这就是为什么对于我们创建的每个新对象，PostgreSQL使用public修饰。

在psql工具要查看当前搜索路径，请使用show命令:

SHOW search_path;

Output

 search_path
-----------------
"$user", public
(1 row)

🐣在此输出中:

• "$ user"指定PostgreSQL将用于搜索对象的第一个schema，该对象与当前用户具有相同的名称。例如，如果我们使用postgres用户登录并访问user表。PostgreSQL将在postgresschema搜索user表。如果找不到这样的对象，它将继续在publicschema搜索user表。
• 第二个要素是指public我们之前已经看到的模式。

要创建新schema，请使用CREATE SCHEMA声明:

CREATE SCHEMA test;

要将新schema添加到搜索路径，请使用以下命令:

SET search_path TO test, public;

现在，如果我们创建一个名为user不指定schema名称，PostgreSQL将把它user表到testschema:

CREATE TABLE user(
    user_id SERIAL PRIMARY KEY,
    name VARCHAR(45) NOT NULL
);

在testschema访问user表我们可以使用以下语句之一:

SELECT * FROM user;

Or

SELECT * FROM test.user;

public schema是搜索路径中的第二个元素，因此在public schema中要访问user表，我们必须限定表名，如下所示:

SELECT * FROM public.staff;

如果使用以下命令，则需要显式引用public使用完全限定名称的schema:

SET search_path TO public;

🐣 public schema不是特殊的schema， 所以你可以删除他

🐔PostgreSQL schemas 和 权限

用户只能访问其拥有的schema中的对象。这意味着它们无法访问schema中不属于它们的任何对象。

要允许用户访问其不拥有的schema中的对象，必须授予USAGEschema权限

GRANT USAGE ON SCHEMA schema_name 
TO role_name;

要允许用户在他们不拥有的schema中创建对象，我们需要向他们授予CREATEschema 权限:

GRANT CREATE ON SCHEMA schema_name 
TO user_name;

🐣 请注意，默认情况下，在publicschema 每个用户都有CREATE和USAGE。

🐔PostgreSQL schema 操作

• 要创建新schema，请使用CREATE SCHEMA声明。

• 要重命名schema或更改其所有者，请使用ALTER SCHEMA声明。

• 要删除schema，请使用DELETE SCHEMA声明。

在这之前，我们已经了解了PostgreSQL schema以及PostgreSQL如何使用搜索路径来解析对象名称。

🐔PostgreSQL CREATE SCHEMA 语句概述

🐣 NOTE: 在此处，我们将学习如何使用PostgreSQLCREATE SCHEMA语句以在数据库中创建新schema。

CREATE SCHEMA 语句允许我们在当前数据库中创建新schema。

创建SCHEMA声明:

CREATE SCHEMA [IF NOT EXISTS] schema_name;

在以下语法中:

• 首先，指定架构后CREATE SCHEMA关键词。schema名称在当前数据库中必须是唯一的。

• 第二，可选使用IF NOT EXISTS只有当新schema不存在时才有条件地创建它。尝试创建已经存在的新schema而不使用IF NOT EXISTS语句将导致错误。

🐣 NOTE:执行CREATE SCHEMA语句，我们必须具有当前数据库中CREATE的权限。

我们还可以为用户创建schema:

CREATE SCHEMA [IF NOT EXISTS] 
AUTHORIZATION username;

在这种情况下，将为username创建schema

PostgreSQL还允许我们创建schema和对象列表，例如使用单个语句创建表和视图，如下所示:

CREATE SCHEMA schema_name
    CREATE TABLE table_name1 (...)
    CREATE TABLE table_name2 (...)
    CREATE VIEW view_name1
        SELECT select_list FROM table_name1;

🐣 NOTE:请注意，每个子命令不以分号 (;) 结尾。

🐔PostgreSQL CREATE SCHEMA 示例

让我们举一些使用CREATE SCHEMA例子，以获得更好的理解。

🐓1) 使用CREATE SCHEMA创建新SCHEMA示例

以下语句使用创建SCHEMA语句以创建名为的新schema tests:

CREATE SCHEMA tests;

以下语句返回当前数据库中的所有schema:

SELECT * 
FROM pg_catalog.pg_namespace
ORDER BY nspname;)

🐓2) 使用CREATE SCHEMA为用户创建schema

首先，创建一个角色 名为 gg

CREATE ROLE gg 
LOGIN
PASSWORD 'Postgr@123#';

第二，为gg创建schema语法如下 :

CREATE SCHEMA AUTHORIZATION gg;

第三，为gg创建一个名为dd的 schema :

CREATE SCHEMA IF NOT EXISTS dd AUTHORIZATION john;

🐓3) 使用CREATE SCHEMA创建schema及其对象的示例

以下示例使用CREATE SCHEMA语句以创建名为的新schemassm。它还会创建一个名为spring的表和一个名为spring_boot的视图

CREATE SCHEMA ssm 
    CREATE TABLE spring(
        id SERIAL NOT NULL, 
        version DATE NOT NULL
    )
    CREATE VIEW spring_boot AS 
        SELECT ID, version
        FROM spring 
        WHERE version <= 2.2.0;

🐣 NOTE: 在上面的文章中，我们已经学习了如何使用PostgreSQLCREATE SCHEMA语句以在数据库中创建新schema。

🐔PostgreSQL ALTER SCHEMA 语句概述

ALTER SCHEMA 语句允许我们更改schema的定义。例如，我们可以按如下方式重命名schema:

ALTER SCHEMA schema_name 
RENAME TO new_name;

🐣 NOTE:

• 首先，在ALTER SCHEMA关键词后指定旧的schema_name。
• 第二，在RENAME关键词后指定新的new_name。

请注意，要执行此语句，我们必须是schema的所有者，并且必须具有CREATE数据库特权。

除了重命名schema之外，ALTER SCHEMA还允许我们将schema的所有者更改为新的所有者，如以下语句所示:

ALTER SCHEMA schema_name 
OWNER TO { new_owner | CURRENT_USER | SESSION_USER};

🐣 NOTE:

• 首先，ALTER SCHEMA指定要在其中更改所有者的schema的名称。

• 第二，OWNER TO 指定新所有者.

🐔PostgreSQL ALTER SCHEMA 语句示例

让我们举一些使用ALTER SCHEMA的示例，以获得更好的理解。

请注意，以下部分中的示例基于我们在CREATE SCHEMA中的操作。

🐓1) 使用ALTER SCHEMA 重命名schema的示例

此示例使用ALTER SCHEMA重命名ddschema到ppschema的语句:

ALTER SCHEMA dd
RENAME TO pp;

同样，以下示例重命名testsschema:

ALTER SCHEMA tests
RENAME TO test;

🐓2) 使用ALTER SCHEMA语句以更改schema的所有者

以下示例使用ALTER SCHEMA将schema dd的所有者更改为postgres的语句;

ALTER SCHEMA dd 
OWNER TO postgres;

以下是查询用户创建的schema的语句:

SELECT * 
FROM 
    pg_catalog.pg_namespace
WHERE 
    nspacl is NULL AND
    nspname NOT LIKE 'pg_%'
ORDER BY 
    nspname;

从输出中可以清楚地看到，ddschema现在由id为10的所有者拥有，即postgres。

同样，此语句将test的所有者更改为postgres:

ALTER SCHEMA test 
OWNER TO postgres;

🐣 在上面的文章中，我们学习了如何使用PostgreSQL更改模式语句以重命名schema或将schema的所有者更改为新的schema。

🐔PostgreSQL DROP SCHEMA 语句概述

DROP SCHEMA移除一个SCHEMA以及数据库中的所有对象。

以下是的DROP SCHEMA语法:

DROP SCHEMA [IF EXISTS] schema_name 
[ CASCADE | RESTRICT ];

🐣释义：

• 首先， DROP SCHEMA 关键字后指定架构要从中删除的schema_name.
• 第二，使用IF EXISTS仅在schema存在时才有条件删除schema的选项。
• 第三，使用CASCADE （级联）删除schema及其所有对象，进而删除依赖于这些对象的所有对象。如果仅在schema为空时才删除schema，则可以使用RESTRICT 选项。默认情况下，DROP SCHEMA使用RESTRICT 选项。

执行DROP SCHEMA语句，我们必须是要删除的schema的所有者或超级用户。

PostgreSQL允许你在一个DROP SCHEMA声明中删除多个schema:

DROP SCHEMA [IF EXISTS] schema_name1 [,schema_name2,...] 
[CASCADE | RESTRICT];

🐔PostgreSQL DROP SCHEMA 语句示例

🐓1) 使用DROP SCHEMA删除空schema的语句示例

此示例使用DROP SCHEMA删除gg的语句:

DROP SCHEMA IF EXISTS gg;

要刷新列表中的schema，请右键单击schema节点，然后选择 “刷新” 菜单项:

🐓2) 使用DROP SCHEMA删除多个schema的语句示例

以下示例使用DROP SCHEMA删除多个schema的语句gg和test使用单个语句:

DROP SCHEMA IF EXISTS gg, test;

🐓3) 使用DROP SCHEMA删除非空schema示例的语句

此声明删除ssmschema:

DROP SCHEMA ssm;

以下是控制台输出:

ERROR:  cannot drop schema scm because other objects depend on it
DETAIL:  table scm.deliveries depends on schema scm
view scm.delivery_due_list depends on schema scm
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
SQL state: 2BP01

因此，如果schema不为空，并且我们想要删除schema及其对象，则必须使用级联删除（CASCADE）:

DROP SCHEMA scm CASCADE;

同样，使用以下语句我们可以删除pp的schema及其对象:

DROP SCHEMA pp CASCADE;

🐣在本文中，我们已经学习了如何使用PostgreSQL 'DROP SCHEMA' 语句在数据库中删除一个或多个schema。

未完待续..既然看到这里了，不妨来个大大的点赞吧