PostgreSQL基本使用（3）Schema

PostgreSQL 模式（Schema）可以理解为是一个表的集合（类似于Oracle的表空间概念）。

一个模式可以包含视图、索引、数据类型、函数和操作符等。

相同的对象名称可以被用于不同的模式中而不会出现冲突，例如 schema1 和 myschema 都可以包含名为 mytable 的表。

使用模式的优势：

• 允许多个用户使用一个数据库并且不会互相干扰
• 将数据库对象组织成逻辑组以便更容易管理
• 第三方应用的对象可以放在独立的模式中，这样他们就不会与其他对象的名称发生冲突

模式类似于操作系统层的目录，但是模式不能嵌套。

默认的Schema

每创建一个数据库，其都会有一个默认的schema，名称为 public

如下示例：

创建一个 mydb2 数据库，然后查看其有的 schema

 -- 创建数据库.
mydb=# create database mydb2;
CREATE DATABASE
mydb=#
mydb=# --- 切换到 mydb2 数据库中
mydb=# \c mydb2;
You are now connected to database "mydb2" as user "postgres".
mydb2=# 
mydb2=# -- 查看持有的 schema
mydb2=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
(1 row)

mydb2=# 

创建 Schema

除了默认的 public 这个Schema，也可以创建自定义的Schema，SQL 语句如下：

 -- 创建 schema
mydb2=# create schema testschema;
CREATE SCHEMA
mydb2=# 
mydb2=# -- 在 testschema 中创建表.
mydb2=# create table testschema.test(id int, name varchar(200));
CREATE TABLE
mydb2=# 
mydb2=# -- 给 testschema 中的表添加数据.
mydb2=# insert into testschema.test(id, name) values(1001, '张三');
INSERT 0 1
mydb2=# -- 查询数据
mydb2=# select * from testschema.test;
  id  | name 
------+------
 1001 | 张三
(1 row)

mydb2=# 

若添加数据时，不指定 testschema 是否可以？？

可以的 ，但是：若不指定 testschema，则表达的意思是给 public 这个schema中的 test 表添加数据。

由于当前 mydb2 这个数据库中的 public 中无 test 表，所以：若不指定 testschema将会报错

-- 如下报错的信息是：test 不存在
mydb2=# insert into test(id, name) values(1002, '小明');
ERROR:  relation "test" does not exist
LINE 1: insert into test(id, name) values(1002, '小明');
                    ^
mydb2=# 

查看 Schema

要查看当前 PostgreSQL 数据库中的所有 schema，你可以使用以下 SQL 查询：

SELECT schema_name
FROM information_schema.schemata;

这将返回一个结果集，其中包含数据库中所有 schema 的名称。请注意，这将包括 PostgreSQL 系统 schema（例如 public、pg_catalog 等）和用户自定义 schema（如果有的话）。

如果你使用的是命令行工具（例如 psql），你也可以执行以下命令来列出当前数据库中的所有 schema：

\dn

这个命令的效果与上述 SQL 查询是相同的，它会列出数据库中的所有 schema 名称。

示例：

-- 创建一个新的库 mydb3
mydb2=# create database mydb3;
CREATE DATABASE
mydb2=#  -- 切换值 mydb3
mydb2=# \c mydb3
You are now connected to database "mydb3" as user "postgres".
mydb3=# 
mydb3=# -- 查看information_schema.schemata ：查询 Schema
mydb3=# select schema_name from information_schema.schemata;
    schema_name     
--------------------
 pg_toast
 pg_temp_1
 pg_toast_temp_1
 pg_catalog
 public
 information_schema
(6 rows)

mydb3=# 
mydb3=# 使用 \dn ：查询 Schema
mydb3=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
(1 row)

mydb3=# 
mydb3=# 

删除 Schema

在 PostgreSQL 中，如果删除一个 schema（模式），默认情况下其中的表也会被删除。然而，这个行为可以通过设置删除模式时的选项来改变。

在 PostgreSQL 中，删除 schema 可以使用 DROP SCHEMA 命令。如果你使用以下方式删除一个 schema，那么其中的表也会被一并删除：

DROP SCHEMA schema_name CASCADE;

在上述命令中，CASCADE 选项表示级联删除，即删除 schema 的同时也删除其包含的所有对象，包括表、视图、函数等。

如果你不想删除表，而只是删除 schema，可以使用以下方式：

DROP SCHEMA schema_name;

这样会删除 schema，但是如果其中有任何对象（如表），则删除操作会失败并显示错误消息，因为默认情况下 PostgreSQL 不允许删除一个非空的 schema。

扩展

在 MySQL 中，Schema 代表的就是数据库。

在 Oracle 中，Schema 代表的就是用户账号。

在 PostgreSQL 中，Schema 可以粗略理解为 Oracle 中的表空间。

在 MySQL 中，查询 Schema 中有哪些资源？或者说：库中有哪些表？

使用如下SQL：

 show tables from 库的名称;
 或者查询 information_schema.tables

那在 PostgreSQL 中，如何查询 schema 有哪些资源？

-- 查询 schema 为 public 中的资源
mydb2=# select table_name from information_schema.tables where table_schema='public';
 table_name 
------------
(0 rows)

mydb2=# -- 查询 schema 为 testschema 中的资源
mydb2=# select table_name from information_schema.tables where table_schema='testschema';
 table_name 
------------
 test
(1 row)

mydb2=#