What is the difference between command create database and create schema in mysql?

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

In MySQL, schema is synonymous with database. As the query is written to create the database, similarly the query can be written to create the schema.

Logical structure can be used by the schema to store data while memory component can be used by the database to store data. Also, a schema is collection of tables while a database is a collection of schema.

To clarify this concept, a database and a schema are created. The steps for this are as follows −

First, a database is created with the following syntax −

create database yourDatabaseName;

The above syntax is used in a query as follows −

mysql> create database DatabaseSample;
Query OK, 1 row affected [0.14 sec]

The syntax to create a schema is as follows −

create schema yourSchemaName;

The above syntax is used in a query as follows −

mysql> create schema SchemaSample;
Query OK, 1 row affected [0.19 sec]

Now both the database and the schema have been created

To display the database and the schema as well, the show command is used. The query for that is as follows −

mysql> show databases;

The following is the output of the above query

+--------------------+
| Database           |
+--------------------+
| business           |
| databasesample     |
| hello              |
| information_schema |
| mybusiness         |
| mysql              | 
| performance_schema |
| sample             |
| schemasample       |
| sys                | 
| test               |
+--------------------+
11 rows in set [0.07 sec]

In the oracle database, the schema can be used to represent a part of the database.

Updated on 24-Jun-2020 14:07:57

• Related Questions & Answers
• Difference between Star Schema and Snowflake Schema?
• Difference Between Schema and Instance
• Difference between Star schema and Snowflake schema in SQL Server
• Difference Between Star and Snowflake Schema
• Difference between _SYS_BIC and _SYS_BI schema in SAP HANA
• Methods for tracking database schema changes in MySQL?
• Difference between Database and a Blockchain
• Difference between Data warehouse and Operational database
• Difference between Operational Database and Data Warehouse?
• Difference between a data warehouse database and an OLTP database?
• What are some good tools to visualize MySQL database schema?
• Difference between Row oriented and column oriented database
• Difference between hierarchical and network database model in SQL
• Difference between Primary key and Foreign key in Database
• Checking schema creation in SAP HANA database

What is the difference between schema and database in MySQL?

What is the difference between a database schema and a database?

What is the difference between database and schema in SQL?

Can we create schema in MySQL?