Postgresql 简明教程

PostgreSQL - Schema

schema 是一组命名的表。架构还可以包含视图、索引、序列、数据类型、运算符和函数。架构类似于操作系统级别的目录，不同的是架构不能嵌套。PostgreSQL 语句 CREATE SCHEMA 创建了一个架构。

A schema is a named collection of tables. A schema can also contain views, indexes, sequences, data types, operators, and functions. Schemas are analogous to directories at the operating system level, except that schemas cannot be nested. PostgreSQL statement CREATE SCHEMA creates a schema.

Syntax

CREATE SCHEMA 的基本语法如下 -

The basic syntax of CREATE SCHEMA is as follows −

CREATE SCHEMA name;

其中 name 是架构的名称。

Where name is the name of the schema.

Syntax to Create Table in Schema

创建模式中的表的基本语法如下：

The basic syntax to create table in schema is as follows −

CREATE TABLE myschema.mytable (
...
);

Example

让我们看一个创建模式的示例。连接到数据库 testdb 并创建一个模式 myschema，如下所示：

Let us see an example for creating a schema. Connect to the database testdb and create a schema myschema as follows −

testdb=# create schema myschema;
CREATE SCHEMA

消息“CREATE SCHEMA”表示该模式已成功创建。

The message "CREATE SCHEMA" signifies that the schema is created successfully.

现在，让我们在上面的模式中创建一个表，如下所示：

Now, let us create a table in the above schema as follows −

testdb=# create table myschema.company(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25),
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

这将创建一个空表。您可以使用下面给出的命令验证已创建的表：

This will create an empty table. You can verify the table created with the command given below −

testdb=# select * from myschema.company;

这将产生以下结果 -

This would produce the following result −

 id | name | age | address | salary
----+------+-----+---------+--------
(0 rows)

Syntax to Drop Schema

要删除一个空的模式（其中的所有对象已被删除），请使用以下命令：

To drop a schema if it is empty (all objects in it have been dropped), use the command −

DROP SCHEMA myschema;

要删除一个模式，包括其中包含的所有对象，请使用以下命令：

To drop a schema including all contained objects, use the command −

DROP SCHEMA myschema CASCADE;

Advantages of using a Schema

  1. It allows many users to use one database without interfering with each other.

  2. It organizes database objects into logical groups to make them more manageable.

  3. Third-party applications can be put into separate schemas so they do not collide with the names of other objects.