【PostgreSQL】从零开始:（十三）PostgreSQL-SQL语句操作架构（模式） Schema

news/2024/11/20 17:38:03/

Schema概述

PostgreSQL 数据库集群包含一个或多个命名数据库。角色和一些其他对象类型在整个集群中共享。与服务器的客户端连接只能访问单个数据库中的数据，该数据库在连接请求中指定。

用户不一定有权访问集群中的每个数据库。共享角色名称意味着不能在同一集群中的两个数据库中命名不同的角色;但是可以将系统配置为仅允许访问某些数据库。

数据库包含一个或多个命名架构，而这些架构又包含表。架构还包含其他类型的命名对象，包括数据类型、函数和运算符。相同的对象名称可以在不同的架构中使用而不会发生冲突;例如，both 和 可以包含名为 的表。与数据库不同，架构不是严格分离的：如果用户具有访问权限，则可以访问他们所连接到的数据库中任何架构中的对象。

可能想要使用架构的原因有以下几个：

  • 允许多个用户使用一个数据库而不会相互干扰。
  • 将数据库对象组织到逻辑组中，使其更易于管理。
  • 第三方应用程序可以放入单独的架构中，以便它们不会与其他对象的名称发生冲突。
    架构类似于操作系统级别的目录，只是架构不能嵌套。

若要在架构中创建或访问对象，请编写一个由架构名称和表名称组成的限定名称，并用点分隔：

schema.table

这适用于需要表名的任何位置，包括表修改命令和以下章节中讨论的数据访问命令。（为简洁起见，我们只讨论表，但同样的想法也适用于其他类型的命名对象，例如类型和函数。
实际上，更通用的语法

database.schema.table

也可以使用，但目前这只是为了形式上符合 SQL 标准。如果写入数据库名称，则该名称必须与连接到的数据库相同。

因此，若要在新架构中创建表，请使用：

CREATE TABLE myschema.mytable (...
);

创建Schema

命令

postgres=# \help create schema
Command:     CREATE SCHEMA
Description: define a new schema
Syntax:
CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ]
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specificationwhere role_specification can be:user_name| CURRENT_ROLE| CURRENT_USER| SESSION_USERURL: https://www.postgresql.org/docs/16/sql-createschema.htmlpostgres=#

测试

postgres=# \c ci_database_test02 circledba; #切换到ci_database_test02数据库下 用户名为circledba
Password for user circledba: 
You are now connected to database "ci_database_test02" as user "circledba".
ci_database_test02=> create schema circle_oa; # 创建模式circle_oa
CREATE SCHEMA
ci_database_test02=> create schema circle_center;# 创建模式circle_center
CREATE SCHEMA
ci_database_test02=> create schema circle_shop;# 创建模式circle_shop
CREATE SCHEMA
ci_database_test02=> \dn # 查看模式List of schemasName      |       Owner       
---------------+-------------------circle_center | circledbacircle_oa     | circledbacircle_shop   | circledbapublic        | pg_database_owner
(4 rows)ci_database_test02=>

再从pgAdmin看看
在这里插入图片描述

其中public是创建数据库时候自带的。

通常，您需要创建其他人拥有的架构（因为这是将用户的活动限制在定义明确的命名空间中的方法之一）。其语法为：

CREATE SCHEMA schema_name AUTHORIZATION user_name;

您甚至可以省略架构名称，在这种情况下，架构名称将与用户名相同。

以pg_开头的架构名称保留用于系统目的，用户无法创建。

公共模式

在前面的部分中，我们在未指定任何架构名称的情况下创建了表。默认情况下，此类表（和其他对象）会自动放入名为“public”的架构中。每个新数据库都包含这样的架构。因此，以下内容是等效的：

CREATE TABLE products ( ... );
CREATE TABLE public.products ( ... );

查看用户Schema

限定名称的编写起来很繁琐，通常最好不要将特定的架构名称连接到应用程序中。因此，表通常由非限定名称引用，这些名称仅由表名称组成。系统通过遵循搜索路径（要查找的架构列表）来确定哪个表。搜索路径中的第一个匹配表被视为所需的表。如果搜索路径中没有匹配项，则会报告错误，即使数据库中的其他架构中存在匹配的表名也是如此。

在不同架构中创建同名对象的能力使编写每次都引用完全相同对象的查询变得复杂。它还为用户提供了恶意或意外更改其他用户查询行为的可能性。由于查询中普遍存在非限定名称及其在 PostgreSQL 内部中的使用，因此添加架构以有效地信任对该架构具有权限的所有用户。当您运行普通查询时，能够在搜索路径的架构中创建对象的恶意用户可以控制并执行任意 SQL 函数，就像您执行了它们一样。search_pathCREATE

搜索路径中命名的第一个架构称为当前架构。除了是第一个搜索的架构之外，它还是在命令未指定架构名称时将在其中创建新表的架构。CREATE TABLE

若要显示当前搜索路径，请使用以下命令：

SHOW search_path;

在默认设置中，这将返回：

 search_path
--------------"$user", public

第一个元素指定要搜索与当前用户同名的架构。如果不存在此类架构，则忽略该条目。第二个元素指的是我们已经看到的公共架构。

搜索路径中存在的第一个架构是创建新对象的默认位置。这就是默认情况下在公共架构中创建对象的原因。在没有架构限定（表修改、数据修改或查询命令）的情况下在任何其他上下文中引用对象时，将遍历搜索路径，直到找到匹配的对象。因此，在默认配置中，任何非限定访问都只能引用公共架构。

设置默认Schema

SET search_path TOmyschema,public;

模式的权限

默认情况下，用户无法访问他们不拥有的架构中的任何对象。若要允许这样做，架构的所有者必须授予对架构的权限。默认情况下，每个人都对架构具有该权限。要允许用户使用架构中的对象，可能需要根据对象的需要授予其他权限。

还可以允许用户在其他人的架构中创建对象。若要允许这样做，需要授予对架构的权限。

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

修改模式

命令

ci_database_test02=> \help alter schema
Command:     ALTER SCHEMA
Description: change the definition of a schema
Syntax:
ALTER SCHEMA name RENAME TO new_name
ALTER SCHEMA name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
URL: https://www.postgresql.org/docs/16/sql-alterschema.htmlci_database_test02=>

测试

1.修改架构circle_center 的架构名为circle_blog
ci_database_test02=> alter schema circle_center rename to circle_blog;
ALTER SCHEMA
ci_database_test02=> \dnList of schemasName     |       Owner       
-------------+-------------------circle_blog | circledbacircle_oa   | circledbacircle_shop | circledbapublic      | pg_database_owner
(4 rows)ci_database_test02=>
2.修改架构circle_shop 的用户属主为postgres
ci_database_test02=> alter schema circle_shop owner to postgres;
ERROR:  must be able to SET ROLE "postgres" # 提示我们没有postgres权限,不让我们修改，circledba只是个普通用户
ci_database_test02=> \c - postgres #切换到数据库管理员postgres
Password for user postgres: 
You are now connected to database "ci_database_test02" as user "postgres".
ci_database_test02=# alter schema circle_shop owner to postgres; #再次修改
ALTER SCHEMA
ci_database_test02=# \dnList of schemasName     |       Owner       
-------------+-------------------circle_blog | circledbacircle_oa   | circledbacircle_shop | postgrespublic      | pg_database_owner
(4 rows)ci_database_test02=#

看到circle_shop 的属主已经变为了postgres

删除Schema

命令

ci_database_test02=# \help drop schema;
Command:     DROP SCHEMA
Description: remove a schema
Syntax:
DROP SCHEMA [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]URL: https://www.postgresql.org/docs/16/sql-dropschema.html
ci_database_test02=#

测试

删除名为cricle_shop的Schema

ci_database_test02=# drop schema circle_shop;
DROP SCHEMA
ci_database_test02=# \dnList of schemasName     |       Owner       
-------------+-------------------circle_blog | circledbacircle_oa   | circledbapublic      | pg_database_owner
(3 rows)ci_database_test02=#

已经成功了

查看Schema列表

命令1

\dn

测试

ci_database_test02=# \dnList of schemasName     |       Owner       
-------------+-------------------circle_blog | circledbacircle_oa   | circledbapublic      | pg_database_owner
(3 rows)ci_database_test02=#

命令2

SELECTpn.oid AS schema_oid,iss.CATALOG_NAME,iss.schema_owner,iss.SCHEMA_NAME 
FROMinformation_schema.schemata issINNER JOIN pg_namespace pn ON pn.nspname = iss.SCHEMA_NAME
WHERE iss.catalog_name = '[dbname]';

测试

ci_database_test02=# SELECT                                                                                                                                                                                                                   pn.oid AS schema_oid,                                                                                                                                                                                                                         iss.CATALOG_NAME,                                                                                                                                                                                                                             iss.schema_owner,                                                                                                                                                                                                                             iss.SCHEMA_NAME                                                                                                                                                                                                                               FROM                                                                                                                                                                                                                                          information_schema.schemata iss                                                                                                                                                                                                               INNER JOIN pg_namespace pn ON pn.nspname = iss.SCHEMA_NAME                                                                                                                                                                                    WHERE iss.catalog_name = 'ci_database_test02';schema_oid |    catalog_name    |   schema_owner    |    schema_name     
------------+--------------------+-------------------+--------------------2200 | ci_database_test02 | pg_database_owner | public13918 | ci_database_test02 | postgres          | information_schema11 | ci_database_test02 | postgres          | pg_catalog99 | ci_database_test02 | postgres          | pg_toast16405 | ci_database_test02 | circledba         | circle_blog16404 | ci_database_test02 | circledba         | circle_oa
(6 rows)ci_database_test02=#

http://www.ppmy.cn/news/1273480.html

相关文章

Ubuntu18.04安装ffmpeg

Ubuntu18.04安装ffmpeg

前言 从本章开始我们将要学习嵌入式音视频的学习了 ，使用的瑞芯微的开发板 🎬 个人主页：ChenPi 🐻推荐专栏1: 《C_ChenPi的博客-CSDN博客》✨✨✨ 🔥 推荐专栏2: 《Linux C应用编程（概念类）_C…
阅读更多...
为什么在Android中需要Context？

为什么在Android中需要Context？

介绍 在Android开发中，Context是一个非常重要的概念，但是很多开发者可能并不清楚它的真正含义以及为什么需要使用它。本文将详细介绍Context的概念，并解释为什么在Android应用中需要使用它。 Context的来源 Context的概念来源于Android框架…
阅读更多...
【Qt图书管理系统】4.系统设计与详细设计

【Qt图书管理系统】4.系统设计与详细设计

文章目录 核心流程图软件架构设计流程图软件开发类图及功能点 核心流程图 用户登录图书查询图书借阅图书归还账户管理 软件架构设计 流程图 软件开发类图及功能点 Dlg_Login 登录界面 Cell_Main 主窗体 Cell_MyBook 我的书籍 Cell_BookMgr 书籍管理 Cell_RecoredMgr 借阅记录…
阅读更多...
系统架构设计师教程（七）系统架构设计基础知识

系统架构设计师教程（七）系统架构设计基础知识

系统架构设计基础知识 7.1 软件架构概念7.1.1 软件架构的定义7.1.2 软件架构设计与生命周期需求分析阶段设计阶段实现阶段构件组装阶段部署阶段后开发阶段 7.1.3 软件架构的重要性 7.2 基于架构的软件开发方法7.2.1 体系结构的设计方法概述7.2.2 概念与术语7.2.3 基于体系结构的…
阅读更多...
Content-Type是什么

Content-Type是什么

目录 Content-Type是什么 获取方式 设置方式 常见类型 application/x-www-form-urlencoded multipart/form-data application/json text/xml text/html text/plain Content-Type是什么 Content-Type出现在请求标头和响应标头中，意思是内容类型&#xff0…
阅读更多...
Trie树

Trie树

Trie树（字典树） 定义 平时查英语词典的时候，可以通过一个字母一个字母查，最终查到你想要的结果。字典树就像字典一样，通过一个字母一个字母查询，可以查到前缀单词。 引入 图片： 其中&#…
阅读更多...
LearnDash LMS ProPanel在线学习系统课程创作者的分析工具

LearnDash LMS ProPanel在线学习系统课程创作者的分析工具

点击阅读LearnDash LMS ProPanel在线学习系统课程创作者的分析工具原文 LearnDash LMS ProPanel在线学习系统课程创作者的分析工具通过整合报告和作业管理来增强您的 LearnDash 管理体验，使您能够发送特定于课程的通信，并显示课程的实时活动&#xff01…
阅读更多...
机器视觉技术与应用实战(开运算、闭运算、细化)

机器视觉技术与应用实战(开运算、闭运算、细化)

开运算和闭运算的基础是膨胀和腐蚀，可以在看本文章前先阅读这篇文章机器视觉技术与应用实战（Chapter Two-04）-CSDN博客 开运算：先腐蚀后膨胀。开运算可以使图像的轮廓变得光滑，具有断开狭窄的间断和消除细小突出物的作…
阅读更多...
最新文章

Copyright @ 2022~2023