模式、表和数据库之间的区别是什么?

时间:2022-09-15 16:25:36

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(或更糟)的问题。但是我一直将模式视为数据库中的表定义。这是错误的或者不是完全正确的。我的数据库课程记不太清了。

15 个解决方案

#1


191  

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

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

#2


90  

A relation schema is 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 schema is the collection of relation schemas for a whole database.

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

A table is 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 database is, 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.

正式地说,数据库是任何数据的集合。在此上下文中,数据库将是表的集合。数据库管理系统是管理和运行数据库的软件(如MySQL、SQL Server、Oracle等)。

#3


13  

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.

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

#4


10  

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 :)

也许谷歌只是一种形式,但FYI术语在定义上似乎有所不同,这是最烦人的事情:)

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.

模式实际上是一个用户。更具体地说,它是用户拥有的一组表/procs/索引等等。另一个用户有不同的模式(他/她拥有的表),但是用户也可以看到他们选择了priviliedges的任何模式。因此,一个数据库可以包含数百个模式,每个模式包含数百个表。可以在不同的模式中使用名称相同的表,这些模式位于同一个数据库中。

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中定义可能不同。我不知道。

#5


7  

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

模式行为看起来像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中,这两者是等价的。关键字数据库或数据库可以在出现的任何地方用模式或模式替换。例子:

  • CREATE DATABASE <=> CREATE SCHEMA
  • 创建数据库<=>创建模式
  • SHOW DATABASES <=> SHOW SCHEMAS
  • 显示数据库<=>显示模式

Documentation of MySQL

MySQL的文档

SCHEMA & DATABASE terms are something DBMS dependent.

模式和数据库术语与DBMS相关。

A Table is 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).

表是一组数据元素(值),使用垂直列(由它们的名称标识)和水平行的模型进行组织。数据库包含一个或多个(通常)表。你将数据存储在这些表格中。这些表可能是相互关联的(见此处)。

#6


2  

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放在一个特定的模式中,而将管理procs放在另一个模式中。将执行授予适当的用户/角色,您就完成了授予执行特定过程的任务。好了。

The dot notation would go like this:

点符号是这样的

Server.Database.Schema.Object

Server.Database.Schema.Object

or

myserver01.Adventureworks.Accounting.Beans

myserver01.Adventureworks.Accounting.Beans

#7


2  

A Schema is a collection of database objects which includes logical structures too. It has the name of the user who owns it. A database can 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.

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

#8


2  

the conception of schema is different in different db software.

在不同的db软件中,模式的概念是不同的。

here i just refer to oracle schema:

这里我只提到oracle schema:


Schema Objects

模式对象

A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema. Schema objects can be created and manipulated with SQL and include the following types of objects:

模式是数据或模式对象的逻辑结构的集合。模式由数据库用户拥有,并具有与该用户相同的名称。每个用户拥有一个模式。模式对象可以使用SQL创建和操作,包括以下类型的对象:

Clusters Constraints Database links Database triggers Dimensions External procedure libraries Index-organized tables Indexes Indextypes Java classes, Java resources, Java sources Materialized views Materialized view logs Object tables Object types Object views Operators Packages Sequences Stored functions, stored procedures Synonyms Tables Views

集群约束数据库链接数据库触发维度外部过程库索引组织表索引类型Java类,Java资源,Java源物化视图物化视图日志对象表类型对象视图操作符包序列存储函数,存储过程同义词表视图


Nonschema Objects

Nonschema对象

Other types of objects are also stored in the database and can be created and manipulated with SQL but are not contained in a schema:

其他类型的对象也存储在数据库中,可以使用SQL创建和操作,但不包含在模式中:

Contexts Directories Parameter files (PFILEs) and server parameter files (SPFILEs) Profiles Roles Rollback segments Tablespaces Users

上下文目录参数文件(PFILEs)和服务器参数文件(SPFILEs)配置角色回滚段表空间用户

#9


1  

In oracle Schema is one user under one database,For example scott is one schema in database orcl. In one database we may have many schema's like scott

在oracle Schema中,一个用户位于一个数据库中,例如scott是数据库orcl中的一个模式。在一个数据库中,我们可能有许多模式,如scott

#10


1  

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

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

#11


1  

A database schema is a way to logically group objects such as tables, views, stored procedures etc. Think of a schema as a container of objects. You can assign a user login permissions to a single schema so that the user can only access the objects they are authorized to access.

数据库模式是一种逻辑地将对象(如表、视图、存储过程等)分组的方法。您可以为单个模式分配用户登录权限,以便用户只能访问授权访问的对象。

#12


0  

Schemas contains Databases.

模式包含数据库。

Databases are part of a Schema.

数据库是模式的一部分。

So, schemas > databases.

所以,模式>数据库。

Schemas contains views, stored procedure(s), database(s), trigger(s) etc.

模式包含视图、存储过程、数据库、触发器等。

#13


0  

A schema is not a plan for the entire database. It is a plan/container for a subset of objects (ex.tables) inside a a database.

This goes to say that you can have multiple objects(ex. tables) inside one database which don't neccessarily fall under the same functional category. So you can group them under various schemas and give them different user access permissions.

That said, I am unsure whether you can have one table under multiple schemas. The Management Studio UI gives a dropdown to assign a schema to a table, and hence making it possible to choose only one schema. I guess if you do it with TSQL, it might create 2 (or multiple) different objects with different object Ids.

模式不是整个数据库的计划。它是数据库中对象子集(例如.tables)的计划/容器。这就是说你可以有多个对象(例如。表)在一个不需要属于同一功能类别的数据库中。因此,您可以在不同的模式下对它们进行分组,并赋予它们不同的用户访问权限。也就是说,我不确定是否可以在多个模式下使用一个表。管理Studio UI提供了一个下拉菜单,将一个模式分配给一个表,因此可以只选择一个模式。我猜如果你用TSQL来做,它可能会用不同的对象id创建2个(或多个)不同的对象。

#14


0  

A database schema is a way to logically group objects such as tables, views, stored procedures etc. Think of a schema as a container of objects. And tables are collections of rows and columns. combination of all tables makes a db.

数据库模式是一种逻辑地将对象(如表、视图、存储过程等)分组的方法。表是行和列的集合。所有表的组合构成一个db。

#15


0  

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

与上面的一些答案相反,以下是我对它们的理解:

  • MySQL: database/schema :: table
  • MySQL数据库/模式::表
  • SQL Server: database :: (schema/namespace ::) table
  • SQL Server:数据库::(模式/名称空间::)表。
  • Oracle: database/schema/user :: (tablespace ::) table
  • Oracle:数据库/模式/用户::(表空间::

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

请纠正我,在Oracle中表空间是否是可选的,我已经很久没有使用过了。

#1


191  

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

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

#2


90  

A relation schema is 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 schema is the collection of relation schemas for a whole database.

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

A table is 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 database is, 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.

正式地说,数据库是任何数据的集合。在此上下文中,数据库将是表的集合。数据库管理系统是管理和运行数据库的软件(如MySQL、SQL Server、Oracle等)。

#3


13  

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.

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

#4


10  

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 :)

也许谷歌只是一种形式,但FYI术语在定义上似乎有所不同,这是最烦人的事情:)

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.

模式实际上是一个用户。更具体地说,它是用户拥有的一组表/procs/索引等等。另一个用户有不同的模式(他/她拥有的表),但是用户也可以看到他们选择了priviliedges的任何模式。因此,一个数据库可以包含数百个模式,每个模式包含数百个表。可以在不同的模式中使用名称相同的表,这些模式位于同一个数据库中。

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中定义可能不同。我不知道。

#5


7  

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

模式行为看起来像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中,这两者是等价的。关键字数据库或数据库可以在出现的任何地方用模式或模式替换。例子:

  • CREATE DATABASE <=> CREATE SCHEMA
  • 创建数据库<=>创建模式
  • SHOW DATABASES <=> SHOW SCHEMAS
  • 显示数据库<=>显示模式

Documentation of MySQL

MySQL的文档

SCHEMA & DATABASE terms are something DBMS dependent.

模式和数据库术语与DBMS相关。

A Table is 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).

表是一组数据元素(值),使用垂直列(由它们的名称标识)和水平行的模型进行组织。数据库包含一个或多个(通常)表。你将数据存储在这些表格中。这些表可能是相互关联的(见此处)。

#6


2  

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放在一个特定的模式中,而将管理procs放在另一个模式中。将执行授予适当的用户/角色,您就完成了授予执行特定过程的任务。好了。

The dot notation would go like this:

点符号是这样的

Server.Database.Schema.Object

Server.Database.Schema.Object

or

myserver01.Adventureworks.Accounting.Beans

myserver01.Adventureworks.Accounting.Beans

#7


2  

A Schema is a collection of database objects which includes logical structures too. It has the name of the user who owns it. A database can 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.

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

#8


2  

the conception of schema is different in different db software.

在不同的db软件中,模式的概念是不同的。

here i just refer to oracle schema:

这里我只提到oracle schema:


Schema Objects

模式对象

A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema. Schema objects can be created and manipulated with SQL and include the following types of objects:

模式是数据或模式对象的逻辑结构的集合。模式由数据库用户拥有,并具有与该用户相同的名称。每个用户拥有一个模式。模式对象可以使用SQL创建和操作,包括以下类型的对象:

Clusters Constraints Database links Database triggers Dimensions External procedure libraries Index-organized tables Indexes Indextypes Java classes, Java resources, Java sources Materialized views Materialized view logs Object tables Object types Object views Operators Packages Sequences Stored functions, stored procedures Synonyms Tables Views

集群约束数据库链接数据库触发维度外部过程库索引组织表索引类型Java类,Java资源,Java源物化视图物化视图日志对象表类型对象视图操作符包序列存储函数,存储过程同义词表视图


Nonschema Objects

Nonschema对象

Other types of objects are also stored in the database and can be created and manipulated with SQL but are not contained in a schema:

其他类型的对象也存储在数据库中,可以使用SQL创建和操作,但不包含在模式中:

Contexts Directories Parameter files (PFILEs) and server parameter files (SPFILEs) Profiles Roles Rollback segments Tablespaces Users

上下文目录参数文件(PFILEs)和服务器参数文件(SPFILEs)配置角色回滚段表空间用户

#9


1  

In oracle Schema is one user under one database,For example scott is one schema in database orcl. In one database we may have many schema's like scott

在oracle Schema中,一个用户位于一个数据库中,例如scott是数据库orcl中的一个模式。在一个数据库中,我们可能有许多模式,如scott

#10


1  

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

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

#11


1  

A database schema is a way to logically group objects such as tables, views, stored procedures etc. Think of a schema as a container of objects. You can assign a user login permissions to a single schema so that the user can only access the objects they are authorized to access.

数据库模式是一种逻辑地将对象(如表、视图、存储过程等)分组的方法。您可以为单个模式分配用户登录权限,以便用户只能访问授权访问的对象。

#12


0  

Schemas contains Databases.

模式包含数据库。

Databases are part of a Schema.

数据库是模式的一部分。

So, schemas > databases.

所以,模式>数据库。

Schemas contains views, stored procedure(s), database(s), trigger(s) etc.

模式包含视图、存储过程、数据库、触发器等。

#13


0  

A schema is not a plan for the entire database. It is a plan/container for a subset of objects (ex.tables) inside a a database.

This goes to say that you can have multiple objects(ex. tables) inside one database which don't neccessarily fall under the same functional category. So you can group them under various schemas and give them different user access permissions.

That said, I am unsure whether you can have one table under multiple schemas. The Management Studio UI gives a dropdown to assign a schema to a table, and hence making it possible to choose only one schema. I guess if you do it with TSQL, it might create 2 (or multiple) different objects with different object Ids.

模式不是整个数据库的计划。它是数据库中对象子集(例如.tables)的计划/容器。这就是说你可以有多个对象(例如。表)在一个不需要属于同一功能类别的数据库中。因此,您可以在不同的模式下对它们进行分组,并赋予它们不同的用户访问权限。也就是说,我不确定是否可以在多个模式下使用一个表。管理Studio UI提供了一个下拉菜单,将一个模式分配给一个表,因此可以只选择一个模式。我猜如果你用TSQL来做,它可能会用不同的对象id创建2个(或多个)不同的对象。

#14


0  

A database schema is a way to logically group objects such as tables, views, stored procedures etc. Think of a schema as a container of objects. And tables are collections of rows and columns. combination of all tables makes a db.

数据库模式是一种逻辑地将对象(如表、视图、存储过程等)分组的方法。表是行和列的集合。所有表的组合构成一个db。

#15


0  

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

与上面的一些答案相反,以下是我对它们的理解:

  • MySQL: database/schema :: table
  • MySQL数据库/模式::表
  • SQL Server: database :: (schema/namespace ::) table
  • SQL Server:数据库::(模式/名称空间::)表。
  • Oracle: database/schema/user :: (tablespace ::) table
  • Oracle:数据库/模式/用户::(表空间::

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

请纠正我,在Oracle中表空间是否是可选的,我已经很久没有使用过了。