database 模式与表和数据库之间有什么区别？

What is the difference between a schema and a table and a database?

提问by anbanm

This is probably a n00blike (or worse) question. But I've always viewed a schema as a table definition in a database. This is wrong or not entirely correct. I don't remember much from my database courses.

这可能是一个 n00blike（或更糟）的问题。但我一直将模式视为数据库中的表定义。这是错误的或不完全正确的。我不太记得我的数据库课程。

回答by MusiGenesis

schema :database :table ::floor plan :house :room

架构：数据库：表：：平面图：房子：房间

回答by Ian Varley

A relation schemais the logical definition of a table - it defines what the name of the table is, and what the name and type of each column is. It's like a plan or a blueprint. A database schemais the collection of relation schemas for a whole database.

一个关系模式是表的逻辑定义-它定义了表的名称，以及每个列的名称和类型。这就像一个计划或蓝图。一个数据库模式是关系模式对整个数据库收集。

A tableis a structure with a bunch of rows (aka "tuples"), each of which has the attributes defined by the schema. Tables might also have indexes on them to aid in looking up values on certain columns.

甲表是与一群行（又名“元组”）的结构，其中的每一个具有由模式定义的属性。表上也可能有索引以帮助查找某些列上的值。

A databaseis, formally, any collection of data. In this context, the database would be a collection of tables. A DBMS(Database Management System) is the software (like MySQL, SQL Server, Oracle, etc) that manages and runs a database.

从形式上讲，数据库是任何数据的集合。在这种情况下，数据库将是一组表。一个DBMS（数据库管理系统）是管理和运行一个数据库软件（如MySQL，SQL服务器，Oracle等）。

回答by Graeme Perrow

In a nutshell, a schema is the definition for the entire database, so it includes tables, views, stored procedures, indexes, primary and foreign keys, etc.

简而言之，模式是整个数据库的定义，因此它包括表、视图、存储过程、索引、主外键等。

回答by Robert

This particular posting has been shown to relate to Oracle only and the definition of Schema changes when in the context of another DB.

此特定发布已被证明仅与 Oracle 相关，并且在另一个 DB 的上下文中更改架构的定义。

Probably the kinda thing to just google up but FYI terms do seem to vary in their definitions which is the most annoying thing :)

可能只是谷歌搜索，但仅供参考的术语的定义似乎有所不同，这是最烦人的事情:)

In Oracle a database is a database. In your head think of this as the data files and the redo logs and the actual physical presence on the disk of the database itself (i.e. not the instance)

在 Oracle 中，数据库就是数据库。在您的脑海中，将其视为数据文件和重做日志以及数据库本身磁盘上的实际物理存在（即不是实例）

A Schema is effectively a user. More specifically it's a set of tables/procs/indexes etc owned by a user. Another user has a different schema (tables he/she owns) however user can also see any schemas they have select priviliedges on. So a database can consist of hundreds of schemas, and each schema hundreds of tables. You can have tables with the same name in different schemas, which are in the same database.

Schema 实际上是一个用户。更具体地说，它是用户拥有的一组表/过程/索引等。另一个用户有一个不同的模式（他/她拥有的表），但是用户也可以看到他们拥有选择权限的任何模式。所以一个数据库可以由数百个模式组成，每个模式有数百个表。您可以在同一个数据库中的不同模式中拥有具有相同名称的表。

A Table is a table, a set of rows and columns containing data and is contained in schemas.

表是一个表，包含数据并包含在模式中的一组行和列。

Definitions may be different in SQL Server for instance. I'm not aware of this.

例如，SQL Server 中的定义可能不同。我不知道这一点。

回答by earthdan

Schema behaves seem like a parent object as seen in OOP world. so it's not a database itself. maybe this linkis useful.

Schema 的行为就像在 OOP 世界中看到的父对象。所以它本身不是数据库。也许这个链接很有用。

But, In MySQL, the two are equivalent. The keyword DATABASE or DATABASES can be replaced with SCHEMA or SCHEMAS wherever it appears. Examples:

但是，在 MySQL 中，两者是等价的。关键字 DATABASE 或 DATABASES 可以在出现的任何地方替换为 SCHEMA 或 SCHEMAS。例子：

• CREATE DATABASE <=> CREATE SCHEMA
• SHOW DATABASES <=> SHOW SCHEMAS

• 创建数据库 <=> 创建架构
• 显示数据库 <=> 显示模式

Documentation of MySQL

MySQL 的文档

SCHEMA & DATABASE terms are something DBMS dependent.

SCHEMA 和 DATABASE 术语依赖于 DBMS。

A Tableis a set of data elements (values) that is organized using a model of vertical columns (which are identified by their name) and horizontal rows. A database contains one or more(usually) Tables. And you store your data in these tables. The tables may be related with one another(See here).

甲表是一组是使用垂直列和水平行（其可以通过名称来标识）的模型组织的数据元素（值）的。一个数据库包含一个或多个（通常）Tables。您将数据存储在这些表中。这些表可能彼此相关（请参阅此处）。

回答by Iswarya

A Schemais a collection of database objects which includes logical structures too. It has the name of the user who owns it. A databasecan have any number of Schema's. One table from a database can appear in two different schemas of same name. A user can view any schema for which they have been assigned select privilege.

ASchema是包含逻辑结构的数据库对象的集合。它具有拥有它的用户的名称。Adatabase可以有任意数量的 Schema。数据库中的一个表可以出现在两个同名的不同模式中。用户可以查看为其分配了选择权限的任何架构。

回答by Sam

More on schemas:

更多关于模式：

In SQL 2005 a schema is a way to group objects. It is a container you can put objects into. People can own this object. You can grant rights on the schema.

在 SQL 2005 中，模式是一种对对象进行分组的方法。它是一个容器，您可以将对象放入其中。人们可以拥有这个对象。您可以授予对架构的权限。

In 2000 a schema was equivalent to a user. Now it has broken free and is quite useful. You could throw all your user procs in a certain schema and your admin procs in another. Grant EXECUTE to the appropriate user/role and you're through with granting EXECUTE on specific procedures. Nice.

在 2000 年，模式相当于用户。现在它已经免费并且非常有用。您可以将所有用户 procs 放入某个架构中，并将您的 admin procs 放入另一个架构中。将 EXECUTE 授予适当的用户/角色，您就完成了对特定过程的 EXECUTE 授予。好的。

The dot notation would go like this:

点符号会是这样的：

Server.Database.Schema.Object

服务器.数据库.架构.对象

or

或者

myserver01.Adventureworks.Accounting.Beans

myserver01.Adventureworks.Accounting.Beans

回答by cmrust

As MusiGenesis put so nicely, in most databases:

正如 MusiGenesis 所说的那样，在大多数数据库中：

schema : database : table :: floor plan : house : room

架构：数据库：表：：平面图：房子：房间

But, in Oracle it may be easier to think of:

但是，在 Oracle 中可能更容易想到：

schema : database : table :: owner : house : room

架构：数据库：表：：所有者：房子：房间

回答by Sridhar Sarnobat

Contrary to some of the above answers, here is my understanding based on experience with each of them:

与上述一些答案相反，以下是我基于对每个答案的经验的理解：

• MySQL: database/schema :: table
• SQL Server: database :: (schema/namespace ::) table
• Oracle: database/schema/user :: (tablespace ::) table

• MySQL： database/schema :: table
• SQL 服务器： database :: (schema/namespace ::) table
• 甲骨文： database/schema/user :: (tablespace ::) table

Please correct me on whether tablespace is optional or not with Oracle, it's been a long time since I remember using them.

请纠正我关于 Oracle 表空间是否是可选的，自从我记得使用它们以来已经很长时间了。

回答by Siddharth Kumar

A database contains one or more named schemas, which in turn contain tables. Schemas also contain other kinds of named objects, including data types, functions, and operators. The same object name can be used in different schemas without conflict; for example, both schema1 and myschema can contain tables named mytable. Unlike databases, schemas are not rigidly separated: a user can access objects in any of the schemas in the database he is connected to, if he has privileges to do so.

一个数据库包含一个或多个命名模式，这些模式又包含表。模式还包含其他类型的命名对象，包括数据类型、函数和运算符。相同的对象名称可以在不同的模式中使用而不会发生冲突；例如，schema1 和 myschema 都可以包含名为 mytable 的表。与数据库不同，模式不是严格分开的：用户可以访问他所连接的数据库中任何模式中的对象，只要他有权限这样做。

There are several reasons why one might want to use schemas:

人们可能想要使用模式的原因有几个：

To allow many users to use one database without interfering with each other.

允许多个用户使用一个数据库而互不干扰。

To organize database objects into logical groups to make them more manageable.

将数据库对象组织成逻辑组，使其更易于管理。

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

第三方应用程序可以放在单独的模式中，这样它们就不会与其他对象的名称发生冲突。

Schemas are analogous to directories at the operating system level, except that schemas cannot be nested.

模式类似于操作系统级别的目录，只是模式不能嵌套。

Official documentation can be referred https://www.postgresql.org/docs/9.1/ddl-schemas.html

官方文档可参考 https://www.postgresql.org/docs/9.1/ddl-schemas.html