使用不同数据库的数据库和模式之间的差异?[英] Differences between Database and Schema using different databases?

本文是小编为大家收集整理的关于使用不同数据库的数据库和模式之间的差异?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

MS SQL和MySQL之间的数据库术语有什么区别?

mySQL实例可以拥有多个数据库吗?看来它只能创建不同的模式.但是,SQL命令为create database.

在MS SQL中,您可以创建多个数据库……每个数据库都有DBO的默认模式?…但是数据库中的多个模式仍然可以吗?

推荐答案

来自此 link ,我们看到MS SQL模式不再与用户相关,这是相关的报价:

SQL中模式的行为发生了变化 服务器2005.模式不再是 等效于数据库用户;每个 架构现在是一个独特的名称空间 那是独立于 创建它的数据库用户.其他 单词,模式只是一个容器 对象.模式可以由 任何用户及其所有权是 可转移.

在MySQL中,数据库和模式完全相同,您甚至可以在命令中互换单词,即创建数据库具有同义词createschema.

mySQL支持多个数据库(schemas),而MS SQL支持多个数据库和多个模式.

其他推荐答案

一般而言,我发现以下文章在wikipedia on wikipedia 很有用./p>

文章底部是以下内容:

SQL规范阐明了" SQL模式"是什么;但是,不同的数据库错误地实现了它.为了使这种困惑复杂化,当功能错误实现时,可以与父数据库的功能重叠. SQL模式只是数据库中的名称空间,使用成员操作员点".".这似乎是所有实施中的普遍性. 完全(数据库,架构和表格)有资格查询的例证:select * from database.schema.table

现在,问题,一个架构和数据库都可以用来隔离一个表格,foo与另一个命名表foo相似.以下是伪代码:select * from db1.foo vs. select * from db2.foo(db和table之间没有明确的架构)select * from [db1.]default.foo vs. select * from [db1.]alternate.foo(无明确的db前缀)出现的问题是,以前的mySQL用户将创建多个数据库对于一个项目.在这种情况下,MySQL数据库的功能与Postgres-Schemas相似,因为Postgres缺乏MySQL具有的搁置跨数据库功能.相反,Postgres应用了更多的规范,实现了交叉式,交叉策略,然后离开了空间以获得将来的跨数据库功能. MySQL别名在幕后,带有数据库的模式,使创建模式和创建数据库是模拟.

可以说,MySQL因此已经实现了跨表功能,完全跳过了模式功能,并为其实现数据库提供了类似的功能.总而言之,Postgres充分支持schemas,但缺乏MySQL在数据库中的功能,而MySQL甚至没有尝试支持真正的模式.

其他推荐答案

我相信,通过为SQL说"架构",您是意思是"所有者".

从我的理解中,在mysql中进行

SELECT * from world.city;

此查询是从世界数据库中选择的餐桌城市.

在MSSQL中,您必须做

SELECT * from world.dbo.city;

其中'dbo'是表的默认所有者.

通过键入

来使生活更轻松地定义默认数据库
USE world
SELECT * from city;

在MySQL中,没有办法声明表的所有者. IE. 'dbo'.

本文地址:https://www.itbaoku.cn/post/597453.html

问题描述

What are the differences in database terminology between MS SQL and 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.

In MS SQL, you can create multiple databases… each have a default schema of dbo?… but multiple schemas in a database is still possible?

推荐答案

From this link, we see that MS SQL schemas are no longer tied to users, here's the relevant quote:

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.

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 supports multiple databases (schemas) and MS SQL supports multiple databases and multiple schemas.

其他推荐答案

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.table

Now, the issue, both a schema and a database can be used to isolate one table, foo from another like named table foo. The following is pseudo code: select * from db1.foo vs. select * from db2.foo (no explicit schema between db and table) select * from [db1.]default.foo vs. 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.

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.

其他推荐答案

I believe by saying 'schema' for MS SQL you are meaning 'owner'.

From my understand, in MySQL when you do a

SELECT * from world.city;

This query is selecting from the world database the table city.

In MsSQL you will have to do a

SELECT * from world.dbo.city;

Where 'dbo' is the default owner of the table.

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'.