玩转PostgreSQL（二）：如何使用schema搜索路径

2023-06-0610:02:24数据库教程Comments2,182 views字数 5809阅读模式

总结: 在本文中，将学习PostgreSQL schema以及如何使用schema搜索路径、解析schema中的对象。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

?PostgreSQL schema是什么

在PostgreSQL中，schema是一个命名空间，其中包含名为database的对象，例如表，视图,索引,数据类型,函数,存储过程和标识符。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

要访问schema中的对象，我们需要使用以下语法对对象进行限定:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

schema_name.object_name

一个数据库可以包含一个或多个schema，并且每个schema仅属于一个数据库。两个schema可以具有共享相同名称的不同对象。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

例如，你可能有testschema ，user 表，public schema也具有user 表。当你提到user 表你必须限定如下:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

public.user

Or文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

test.user

?为什么需要使用schema

?为什么需要使用schema:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

  • Schema允许我们将数据库对象 (例如表) 组织到逻辑组中，以使它们更易于管理。
  • Schema使多个用户能够使用一个数据库而不会相互干扰。

?public schema

对于每个新数据库，PostgreSQL自动创建一个名为publicschema。无论我们创建的对象有没有指定schema名称，PostgreSQL都会将其放入publicschema。因此，以下语句是等效的:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

CREATE TABLE table_name(
  ...
);

和文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

CREATE TABLE public.table_name(
   ...
);

?schema搜索路径

实际上，引用没有其schema名称的表，例如，user表而不是完全限定的名称，例如test.user表。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

当仅使用表的名称引用表时，PostgreSQL使用schema搜索路径,这是要查找的schema列表。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

PostgreSQL将访问schema搜索路径中的第一个匹配表。如果没有匹配项，它将返回错误，即使该名称存在于数据库中的另一个schema中。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

搜索路径中的第一个schema称为当前schema。请注意，当我们在未显式指定schema名称的情况下创建新对象时，PostgreSQL还将为新对象使用当前schema。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

current_schema()函数返回当前schema:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

SELECT current_schema();

Output文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

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

这就是为什么对于我们创建的每个新对象，PostgreSQL使用public修饰。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

在psql工具要查看当前搜索路径，请使用show命令:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

SHOW search_path;

Output文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

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

?在此输出中:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

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

要创建新schema，请使用CREATE SCHEMA声明:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

CREATE SCHEMA test;

要将新schema添加到搜索路径，请使用以下命令:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

SET search_path TO test, public;

现在，如果我们创建一个名为user不指定schema名称，PostgreSQL将把它user表到testschema:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

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

在testschema访问user表我们可以使用以下语句之一:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

SELECT * FROM user;

Or文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

SELECT * FROM test.user;

public schema是搜索路径中的第二个元素，因此在public schema中要访问user表，我们必须限定表名，如下所示:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

SELECT * FROM public.staff;

如果使用以下命令，则需要显式引用public使用完全限定名称的schema:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

SET search_path TO public;

? public schema不是特殊的schema， 所以你可以删除他文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

?PostgreSQL schemas 和 权限

用户只能访问其拥有的schema中的对象。这意味着它们无法访问schema中不属于它们的任何对象。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

要允许用户访问其不拥有的schema中的对象，必须授予USAGEschema权限文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

GRANT USAGE ON SCHEMA schema_name 
TO role_name;

要允许用户在他们不拥有的schema中创建对象，我们需要向他们授予CREATEschema 权限:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

GRANT CREATE ON SCHEMA schema_name 
TO user_name;

? 请注意，默认情况下，在publicschema 每个用户都有CREATE和USAGE。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

?PostgreSQL schema 操作

  • 要创建新schema，请使用CREATE SCHEMA声明。
  • 要重命名schema或更改其所有者，请使用ALTER SCHEMA声明。
  • 要删除schema，请使用DELETE SCHEMA声明。

在这之前，我们已经了解了PostgreSQL schema以及PostgreSQL如何使用搜索路径来解析对象名称。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

?PostgreSQL CREATE SCHEMA 语句概述

? NOTE: 在此处，我们将学习如何使用PostgreSQLCREATE SCHEMA语句以在数据库中创建新schema。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

CREATE SCHEMA 语句允许我们在当前数据库中创建新schema。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

创建SCHEMA声明:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

CREATE SCHEMA [IF NOT EXISTS] schema_name;

在以下语法中:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

  • 首先，指定架构后CREATE SCHEMA关键词。schema名称在当前数据库中必须是唯一的。
  • 第二，可选使用IF NOT EXISTS只有当新schema不存在时才有条件地创建它。尝试创建已经存在的新schema而不使用IF NOT EXISTS语句将导致错误。

? NOTE:执行CREATE SCHEMA语句，我们必须具有当前数据库中CREATE的权限。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

我们还可以为用户创建schema:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

CREATE SCHEMA [IF NOT EXISTS] 
AUTHORIZATION username;

在这种情况下，将为username创建schema文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

PostgreSQL还允许我们创建schema和对象列表，例如使用单个语句创建表和视图，如下所示:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

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

? NOTE:请注意，每个子命令不以分号 (;) 结尾。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

?PostgreSQL CREATE SCHEMA 示例

让我们举一些使用CREATE SCHEMA例子，以获得更好的理解。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

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

以下语句使用创建SCHEMA语句以创建名为的新schema tests:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

CREATE SCHEMA tests;

以下语句返回当前数据库中的所有schema:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

SELECT * 
FROM pg_catalog.pg_namespace
ORDER BY nspname;)

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

首先，创建一个角色 名为 gg文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

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

第二，为gg创建schema语法如下 :文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

CREATE SCHEMA AUTHORIZATION gg;

第三，为gg创建一个名为dd的 schema :文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

CREATE SCHEMA IF NOT EXISTS dd AUTHORIZATION john;

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

以下示例使用CREATE SCHEMA语句以创建名为的新schemassm。它还会创建一个名为spring的表和一个名为spring_boot的视图文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

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。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

?PostgreSQL ALTER SCHEMA 语句概述

ALTER SCHEMA 语句允许我们更改schema的定义。例如，我们可以按如下方式重命名schema:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

ALTER SCHEMA schema_name 
RENAME TO new_name;

? NOTE:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

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

请注意，要执行此语句，我们必须是schema的所有者，并且必须具有CREATE数据库特权。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

除了重命名schema之外，ALTER SCHEMA还允许我们将schema的所有者更改为新的所有者，如以下语句所示:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

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

? NOTE:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

  • 首先，ALTER SCHEMA指定要在其中更改所有者的schema的名称。
  • 第二，OWNER TO 指定新所有者.

?PostgreSQL ALTER SCHEMA 语句示例

让我们举一些使用ALTER SCHEMA的示例，以获得更好的理解。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

请注意，以下部分中的示例基于我们在CREATE SCHEMA中的操作。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

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

此示例使用ALTER SCHEMA重命名ddschema到ppschema的语句:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

ALTER SCHEMA dd
RENAME TO pp;

同样，以下示例重命名testsschema:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

ALTER SCHEMA tests
RENAME TO test;

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

以下示例使用ALTER SCHEMA将schema dd的所有者更改为postgres的语句;文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

ALTER SCHEMA dd 
OWNER TO postgres;

以下是查询用户创建的schema的语句:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

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

从输出中可以清楚地看到，ddschema现在由id为10的所有者拥有，即postgres。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

同样，此语句将test的所有者更改为postgres:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

ALTER SCHEMA test 
OWNER TO postgres;

? 在上面的文章中，我们学习了如何使用PostgreSQL更改模式语句以重命名schema或将schema的所有者更改为新的schema。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

?PostgreSQL DROP SCHEMA 语句概述

DROP SCHEMA移除一个SCHEMA以及数据库中的所有对象。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

以下是的DROP SCHEMA语法:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

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

?释义：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

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

执行DROP SCHEMA语句，我们必须是要删除的schema的所有者或超级用户。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

PostgreSQL允许你在一个DROP SCHEMA声明中删除多个schema:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

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

?PostgreSQL DROP SCHEMA 语句示例

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

此示例使用DROP SCHEMA删除gg的语句:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

DROP SCHEMA IF EXISTS gg;

要刷新列表中的schema，请右键单击schema节点，然后选择 “刷新” 菜单项:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

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

以下示例使用DROP SCHEMA删除多个schema的语句gg和test使用单个语句:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

DROP SCHEMA IF EXISTS gg, test;

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

此声明删除ssmschema:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

DROP SCHEMA ssm;

以下是控制台输出:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

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）:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

DROP SCHEMA scm CASCADE;

同样，使用以下语句我们可以删除pp的schema及其对象:文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

DROP SCHEMA pp CASCADE;

?在本文中，我们已经学习了如何使用PostgreSQL 'DROP SCHEMA' 语句在数据库中删除一个或多个schema。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

 文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html

作者：只注视着你
来源：稀土掘金
文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/45059.html
  • 本站内容整理自互联网，仅提供信息存储空间服务，以方便学习之用。如对文章、图片、字体等版权有疑问，请在下方留言，管理员看到后，将第一时间进行处理。
  • 转载请务必保留本文链接：https://www.cainiaoxueyuan.com/sjk/45059.html

Comment

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定