MySQL create schema and create database - Is there any difference

Question

Taking a peak into the information schema database and peaking at the metadata for one of my pet projects  I m having a hard time understanding what  if any  differences there are between the create schema command and the create database command for MySQL   Are there any differences  If not  is this a rather typical pattern of behavior for relational databases  I ve heard that for other databases  such as Oracle  a schema exists in a database  rather than being on the same level as a database    Thanks

User · Answer

Mysql documentation says   CREATE SCHEMA is a synonym for CREATE DATABASE as of MySQL 5 0 2     this all goes back to an ANSI standard for SQL in the mid-80s   That standard had a  CREATE SCHEMA  command  and it served to introduce  multiple name spaces for table and view names  All tables and views were  created within a  schema   I do not know whether that version defined  some cross-schema access to tables and views  but I assume it did  AFAIR  no product  at least back then  really implemented it  that whole    concept was more theory than practice   OTOH  ISTR this version of the standard did not have the concept of a   user  or a  CREATE USER  command  so there were products that used the  concept of a  user   who then had his own name space for tables and  views  to implement their equivalent of  schema    This is an area where systems differ   As far as administration is concerned  this should not matter too much   because here you have differences anyway   As far as you look at application code  you  only  have to care about  cases where one application accesses tables from multiple name spaces   AFAIK  all systems support a syntax       and for this it should not matter whether the name space is that of a  user  a  schema   or a  database

User · Answer

The documentation of MySQL says        CREATE DATABASE creates a database   with the given name  To use this   statement  you need the CREATE   privilege for the database  CREATE   SCHEMA is a synonym for CREATE   DATABASE as of MySQL 5 0 2    So  it would seem normal that those two instruction do the same

User · Answer

So  there is no difference between MySQL  database  and MySQL  schema   these are two names for the same thing - a namespace for tables and other DB objects   For people with Oracle background   MySQL  database  a k a  MySQL  schema  corresponds to Oracle schema  The difference between MySQL and Oracle CREATE SCHEMA commands is that in Oracle the CREATE SCHEMA command does not actually create a schema but rather populates it  with tables and views   And Oracle s CREATE DATABASE command does a very different thing than its MySQL counterpart

User · Answer

Database is a collection of schemas and schema is a collection of tables  But in MySQL they use it the same way

User · Answer

CREATE SCHEMA is a synonym for CREATE DATABASE  CREATE DATABASE Syntax

User · Answer

Strictly speaking  the difference between Database and Schema is inexisting in MySql     However  this is not the case in other database engines such as SQL Server  In SQL server        Every table belongs to a grouping of objects in the database called database schema  It s a container or namespace  Querying Microsoft SQL Server 2012    By default  all the tables in SQL Server belong to a default schema called dbo  When you query a table that hasn t been allocated to any particular schema  you can do something like   SELECT   FROM your table   which is equivalent to   SELECT   FROM dbo your table   Now  SQL server allows the creation of different schema  which gives you the possibility of grouping tables that share a similar purpose  That helps to organize the database    For example  you can create an schema called sales  with tables such as invoices  creditorders  and any other related with sales   and another schema called lookup  with tables such as countries  currencies  subscriptiontypes  and any other table used as look up table     The tables that are allocated to a specific domain are displayed in SQL Server Studio Manager with the schema name prepended to the table name  exactly the same as the tables that belong to the default dbo schema    There are special schemas in SQL Server  To quote the same book       There are several built-in database schemas  and they can t be dropped or altered        1  dbo  the default schema       2  guest contains objects available to a guest user   guest user  is a special role in SQL Server lingo  with some default and highly restricted permissions   Rarely used       3  INFORMATION SCHEMA  used by the Information Schema Views      4  sys  reserved for SQL Server internal use exclusively   Schemas are not only for grouping  It is actually possible to give different permissions for each schema to different users  as described MSDN    Doing this way  the schema lookup mentioned above could be made available to any standard user in the database  e g  SELECT permissions only   whereas a table called supplierbankaccountdetails may be allocated in a different schema called financial  and to give only access to the users in the group accounts  just an example  you get the idea    Finally  and quoting the same book again      It isn t the same Database Schema and Table Schema  The former is the namespace of a table  whereas the latter refers to the table definition

[sql] MySQL 'create schema' and 'create database' - Is there any difference

Examples related to sql

Examples related to mysql

Examples related to oracle

Examples related to jdbc

Examples related to database