MySQL 'Create Schema' and 'Create Database' - Is There Any Difference

MySQL 'create schema' and 'create database' - Is there any difference

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.

Difference Between Schema / Database in MySQL

As defined in the MySQL Glossary:

In MySQL, physically, a schema is synonymous with a database. You can substitute the keyword SCHEMA instead of DATABASE in MySQL SQL syntax, for example using CREATE SCHEMA instead of CREATE DATABASE.

Some other database products draw a distinction. For example, in the Oracle Database product, a schema represents only a part of a database: the tables and other objects owned by a single user.

In C++, what do you do nearly all the time?

I find turning on the gcc flags -Wall, -Werror, and (this is the fun one) -Weffc++ help catch a lot of potential problems. From the gcc man page:

  -Weffc++ (C++ only)
      Warn about violations of the following style guidelines from Scott
      Meyers’ Effective C++ book:

      ·   Item 11:  Define a copy constructor and an assignment operator
          for classes with dynamically allocated memory.

      ·   Item 12:  Prefer initialization to assignment in constructors.

      ·   Item 14:  Make destructors virtual in base classes.

      ·   Item 15:  Have "operator=" return a reference to *this.

      ·   Item 23:  Don’t try to return a reference when you must return
          an object.

      and about violations of the following style guidelines from Scott
      Meyers’ More Effective C++ book:

      ·   Item 6:  Distinguish between prefix and postfix forms of incre-
          ment and decrement operators.

      ·   Item 7:  Never overload "&&", "││", or ",".

      If you use this option, you should be aware that the standard
      library headers do not obey all of these guidelines; you can use
      grep -v to filter out those warnings.

Are SQL CREATE SCHEMA and CREATE DATABASE equivilent?

They are definitely not that same! A database may consist of several schemas. Schemas are basically securable objects that can contain other securable objects such as tables, views, procedures etc. Securable in this context means something that is owned by someone and to which operations can be granted.

MySQL Workbench 6 - difference in creating database / schema as a model and within a MySQL Connector

You started from the wrong premise. Models and connections are two completely different things (why would there be different sections in MySQL Workbench if not?).

Via connections you can reach a server and work on it. Create users, retrieve data, create db objects etc.

Modeling is however the task to design a database structure. All the objects you create only exist within that model. You can design your structure from a higher level of view instead of going down to the pure SQL (which you can too, if you want). Nothing reaches a server until you either forward engineer your model or synchronize it to that server. The first is simply creating all the objects as you designed them, the latter is a two-way 'merge', that is, a diff is generated between the model and the server content and changes are applied to make the structure on the server be the same as in the model and vice versa.

Understanding that fundamental difference answer all your questions above.

Differences between Database and Schema using different databases?

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.

what exactly is database schema?

Both are correct. "Schema" has multiple meanings. Your first meaning is the kind of schema you get with CREATE SCHEMA -- an object container. Your second meaning is the general meaning of "database schema" that's independent of SQL Server. Usually it's clear from context which meaning is intended -- "database schema" is typically singular, while "schemas" within a single database refer to the containers. So, if you wanted to be particularly confusing, you could talk about a database schema₂ that has multiple schemas₁ in it.

Mysql Backup with create schema

Use the option --databases BANCO, i.e.

mysqldump -h HOST -u LOGIN -pSENHA --opt --routines --triggers --databases BANCO > backup.sql

This will add create database (and drop database) statements to the dump.

And in case you wonder: schema and database are synonymous in MySQL: Difference Between Schema / Database in MySQL.

---

Related Topics