What are the differences in database terminology between MS SQL and MySQL?
MS SQL和MySQL在数据库术语上有什么不同?
Can a MySQL instance have more than one database? It appears that it can only create different schemas. However, the SQL command is create database.
一个MySQL实例可以有多个数据库吗?它似乎只能创建不同的模式。但是，SQL命令是create database。
In MS SQL, you can create multiple databases… each have a default schema of dbo?… but multiple schemas in a database is still possible?
在MS SQL中，您可以创建多个数据库……每个数据库都有一个dbo的默认模式?但是数据库中的多个模式仍然是可能的?
4 个解决方案
#1
64
From this link, we see that MS SQL schemas are no longer tied to users, here's the relevant quote:
从这个链接中，我们看到MS SQL模式不再与用户绑定，以下是相关的引用:
The behavior of schemas changed in SQL Server 2005. Schemas are no longer equivalent to database users; each schema is now a distinct namespace that exists independently of the database user who created it. In other words, a schema is simply a container of objects. A schema can be owned by any user, and its ownership is transferable.
在SQL Server 2005中，模式的行为发生了变化。模式不再等同于数据库用户;每个模式现在都是一个独立于创建它的数据库用户的独立名称空间。换句话说，模式只是对象的容器。模式可以由任何用户拥有，它的所有权是可以转移的。
In MySQL, databases and schemas are exactly the same thing, you can even interchange the word in the commands, i.e. CREATE DATABASE has the synonym CREATE SCHEMA.
在MySQL中，数据库和模式是完全相同的，您甚至可以在命令中交换单词，例如，CREATE DATABASE具有同义词CREATE SCHEMA。
MySQL supports multiple databases (schemas) and MS SQL supports multiple databases and multiple schemas.
MySQL支持多个数据库(模式)，而MS SQL支持多个数据库和多个模式。
#2
27
In general, I found the following article on Wikipedia to be useful.
总的来说，我发现以下关于*的文章很有用。
At the bottom of the article is the following:
文章的结尾是:
The SQL specification makes clear what an "SQL schema" is; however, different databases implement it incorrectly. To compound this confusion the functionality can, when incorrectly implemented, overlap with that of the parent-database. An SQL schema is simply a namespace within a database, things within this namespace are addressed using the member operator dot ".". This seems to be a universal amongst all of the implementations. A true fully (database, schema, and table) qualified query is exemplified as such:
select * from database.schema.tableSQL规范明确了什么是“SQL模式”;但是，不同的数据库不正确地实现它。为了使这种混淆更加复杂，在不正确实现时，功能可以与父类数据库的功能重叠。SQL模式只是数据库中的一个名称空间，这个名称空间中的内容是使用成员操作符点“来处理的。”这在所有实现中似乎都是通用的。一个真正的完整的(数据库、模式和表)查询实例如下:select * from database.schema.table
Now, the issue, both a schema and a database can be used to isolate one table,
foofrom another like named tablefoo. The following is pseudo code:select * from db1.foovs.select * from db2.foo(no explicit schema between db and table)select * from [db1.]default.foovs.select * from [db1.]alternate.foo(no explicit db prefix) The problem that arises is that former MySQL users will create multiple databases for one project. In this context MySQL databases are analogous in function to Postgres-schemas, insomuch as Postgres lacks off-the-shelf cross-database functionality that MySQL has. Conversely, Postgres has applied more of the specification implementing cross-table, cross-schema, and then left room for future cross-database functionality. MySQL aliases behind the scenes, schema with database, such that create schema, and create database are analogs.现在，问题是，模式和数据库都可以用来将一个表与另一个表分离，如命名表foo。下面是伪代码:从db1中选择*。foo vs.从db2中选择*。foo (db和表之间没有显式模式)选择* from [db1.]default。foo vs.从[db1.]中选择*。出现的问题是，以前的MySQL用户将为一个项目创建多个数据库。在这个上下文中，MySQL数据库在功能上类似于Postgres-schemas，因为Postgres缺乏MySQL所具有的现成的跨数据库功能。相反，Postgres应用了更多实现跨表、跨模式的规范，然后为未来的跨数据库功能留下了空间。MySQL的后台别名，带有数据库的模式，例如创建模式和创建数据库都是类似的。
It can be said, that MySQL therefore, has implemented cross-table functionality, skipped schema functionality entirely and provided similar functionality into their implementation of a database. In summary, Postgres fully supports schemas, but lacks some functionality MySQL has with databases, while MySQL doesn't even attempt to support true schemas.
可以说，MySQL因此实现了跨表功能，完全跳过了模式功能，并在数据库的实现中提供了类似的功能。总而言之，Postgres完全支持模式，但缺乏MySQL对数据库的某些功能，而MySQL甚至不尝试支持真正的模式。
#3
4
I believe by saying 'schema' for MS SQL you are meaning 'owner'.
我相信对于MS SQL来说，“schema”就是“owner”的意思。
From my understand, in MySQL when you do a
从我的理解，在MySQL中。
SELECT * from world.city;
This query is selecting from the world database the table city.
这个查询是从世界数据库表城中选择的。
In MsSQL you will have to do a
在MsSQL中，您必须执行a
SELECT * from world.dbo.city;
Where 'dbo' is the default owner of the table.
其中'dbo'是表的默认所有者。
To make life easier define the default database by typing
通过输入定义默认数据库，使工作更简单
USE world
SELECT * from city;
In MySQL there is no way to declare the owner of the table. ie. 'dbo'.
在MySQL中，无法声明表的所有者。ie。“dbo”。
#4
2
MS SQL Definately supports multiple databases, each with 1 to many schemas, dbo is merely the default for backwards compatibility purposes.
MS SQL肯定支持多个数据库，每个数据库具有1到多个模式，dbo仅仅是用于向后兼容的默认数据库。
#1
64
From this link, we see that MS SQL schemas are no longer tied to users, here's the relevant quote:
从这个链接中，我们看到MS SQL模式不再与用户绑定，以下是相关的引用:
The behavior of schemas changed in SQL Server 2005. Schemas are no longer equivalent to database users; each schema is now a distinct namespace that exists independently of the database user who created it. In other words, a schema is simply a container of objects. A schema can be owned by any user, and its ownership is transferable.
在SQL Server 2005中，模式的行为发生了变化。模式不再等同于数据库用户;每个模式现在都是一个独立于创建它的数据库用户的独立名称空间。换句话说，模式只是对象的容器。模式可以由任何用户拥有，它的所有权是可以转移的。
In MySQL, databases and schemas are exactly the same thing, you can even interchange the word in the commands, i.e. CREATE DATABASE has the synonym CREATE SCHEMA.
在MySQL中，数据库和模式是完全相同的，您甚至可以在命令中交换单词，例如，CREATE DATABASE具有同义词CREATE SCHEMA。
MySQL supports multiple databases (schemas) and MS SQL supports multiple databases and multiple schemas.
MySQL支持多个数据库(模式)，而MS SQL支持多个数据库和多个模式。
#2
27
In general, I found the following article on Wikipedia to be useful.
总的来说，我发现以下关于*的文章很有用。
At the bottom of the article is the following:
文章的结尾是:
The SQL specification makes clear what an "SQL schema" is; however, different databases implement it incorrectly. To compound this confusion the functionality can, when incorrectly implemented, overlap with that of the parent-database. An SQL schema is simply a namespace within a database, things within this namespace are addressed using the member operator dot ".". This seems to be a universal amongst all of the implementations. A true fully (database, schema, and table) qualified query is exemplified as such:
select * from database.schema.tableSQL规范明确了什么是“SQL模式”;但是，不同的数据库不正确地实现它。为了使这种混淆更加复杂，在不正确实现时，功能可以与父类数据库的功能重叠。SQL模式只是数据库中的一个名称空间，这个名称空间中的内容是使用成员操作符点“来处理的。”这在所有实现中似乎都是通用的。一个真正的完整的(数据库、模式和表)查询实例如下:select * from database.schema.table
Now, the issue, both a schema and a database can be used to isolate one table,
foofrom another like named tablefoo. The following is pseudo code:select * from db1.foovs.select * from db2.foo(no explicit schema between db and table)select * from [db1.]default.foovs.select * from [db1.]alternate.foo(no explicit db prefix) The problem that arises is that former MySQL users will create multiple databases for one project. In this context MySQL databases are analogous in function to Postgres-schemas, insomuch as Postgres lacks off-the-shelf cross-database functionality that MySQL has. Conversely, Postgres has applied more of the specification implementing cross-table, cross-schema, and then left room for future cross-database functionality. MySQL aliases behind the scenes, schema with database, such that create schema, and create database are analogs.现在，问题是，模式和数据库都可以用来将一个表与另一个表分离，如命名表foo。下面是伪代码:从db1中选择*。foo vs.从db2中选择*。foo (db和表之间没有显式模式)选择* from [db1.]default。foo vs.从[db1.]中选择*。出现的问题是，以前的MySQL用户将为一个项目创建多个数据库。在这个上下文中，MySQL数据库在功能上类似于Postgres-schemas，因为Postgres缺乏MySQL所具有的现成的跨数据库功能。相反，Postgres应用了更多实现跨表、跨模式的规范，然后为未来的跨数据库功能留下了空间。MySQL的后台别名，带有数据库的模式，例如创建模式和创建数据库都是类似的。
It can be said, that MySQL therefore, has implemented cross-table functionality, skipped schema functionality entirely and provided similar functionality into their implementation of a database. In summary, Postgres fully supports schemas, but lacks some functionality MySQL has with databases, while MySQL doesn't even attempt to support true schemas.
可以说，MySQL因此实现了跨表功能，完全跳过了模式功能，并在数据库的实现中提供了类似的功能。总而言之，Postgres完全支持模式，但缺乏MySQL对数据库的某些功能，而MySQL甚至不尝试支持真正的模式。
#3
4
I believe by saying 'schema' for MS SQL you are meaning 'owner'.
我相信对于MS SQL来说，“schema”就是“owner”的意思。
From my understand, in MySQL when you do a
从我的理解，在MySQL中。
SELECT * from world.city;
This query is selecting from the world database the table city.
这个查询是从世界数据库表城中选择的。
In MsSQL you will have to do a
在MsSQL中，您必须执行a
SELECT * from world.dbo.city;
Where 'dbo' is the default owner of the table.
其中'dbo'是表的默认所有者。
To make life easier define the default database by typing
通过输入定义默认数据库，使工作更简单
USE world
SELECT * from city;
In MySQL there is no way to declare the owner of the table. ie. 'dbo'.
在MySQL中，无法声明表的所有者。ie。“dbo”。
#4
2
MS SQL Definately supports multiple databases, each with 1 to many schemas, dbo is merely the default for backwards compatibility purposes.
MS SQL肯定支持多个数据库，每个数据库具有1到多个模式，dbo仅仅是用于向后兼容的默认数据库。