MySQL Create Database – How To Create A Database In MySQL

This tutorial explains the steps to Create a Database in MySQL with syntax and examples. Also includes how to delete a database with an example:

In MySQL, to perform any of the operations, the primary necessity is of the availability of a database. A database holds various tables within it, which in turn holds crucial data. Therefore, it’s important to learn about the creation process of the database.

Please note, you may not have these kinds of privileges in an actual production environment. Creation of a database, in an actual production environment, is entirely a domain of DBAs.

=> Click here for the complete MySQL tutorial series

Before proceeding ahead, please note that we have used MySQL version 8.0.

>> Download MySQL Version8.0

MySQL Create Database

Syntax:

CREATE DATABASE [IF NOT EXISTS] db_name
[CHARACTER SET characterset_name
[COLLATE coll_name];

Syntax Explanation:

• The syntax starts with the keyword “CREATE DATABASE”, thereby informing the MySQL Server about the type of activity to be performed. This is a mandatory keyword and cannot be omitted.
• Next comes the optional field of “IF NOT EXISTS”. As it’s an optional field, it can be ignored. If we try to create a database that already exists and we have not mentioned this optional keyword then it will throw an error.
• Character set is a legal set of characters that are valid in a string in MySQL. This is an optional clause and if not mentioned the default character set of latin1 is used.
• Collate is a set of rules to be used to compare characters of a particular character set. This is an optional clause and if not mentioned, the default collate of latin1_swedish_ci is used.
• Next, will be the database name and it has to be unique.
• One needs to have CREATE DATABASE privilege in order to execute this command.
• In other databases, like DB2, there is a difference between database and schema. Its like, the database is a group of schemas and schemas are a group of tables.
• But in MySQL, a schema is synonymous with the database. Therefore, database or schema are groups of tables.
• This implies that the following syntax will achieve the same results as the one we have discussed above.

CREATE SCHEMA [IF NOT EXISTS] db_name;

MySQL Create Database Example

We will show a step-by-step process to create a database and schema as well. The output should help you understand the fact that schema is synonymous with the database.

#1) Open MySQL Workbench for Executing the “Create Database” query.

#2) To see the output of the executed query, press the “Refresh” button (as highlighted in the image below). This will result in showing up of the new database under the Schemas. Also, note the information under the output tab which depicts the successful completion of the query.

#3) Now let’s create a schema and see if there is any difference. Execute a CREATE SCHEMA command with the schema name as pacificppk_schema.

#4) After executing the command, refresh the schemas to find the new schema popping up there.

#5) Please do observe the results of both the queries closely. It shows that database and schema objects created by the above queries are stored in MySQL as “Schemas”.

#6) Let’s learn about one more way of verifying this. It’s done by executing the command: SHOW DATABASES. This command will list all the databases that have been created under the MySQL Server that we have logged on to.

#7) In the above image, please observe the highlighted areas. The two objects, database, and schema that we have created are listed in it as databases. Your database is now ready to hold tables!

Alternate Way To Create Database

Apart from creating a MySQL database using commands, we can also create using the MySQL Workbench.

The below steps depict the procedure:

#1) Click on the new schema symbol as shown below:

#2) Provide the name of the database of the schema. Please observe the character set and collate options.

#3) Below image depicts the Online DDL creation based on the inputs provided above.

#4) Completion of the MySQL Database create process is shown in the image below.

#5) On refreshing the database details, we can see a new database, test_schema, being added.

Default Database Or Schemas

Following are the default databases of schemas in MySQL:

1. information_schema
2. mysql
3. performance_schema
4. sys

Operational Commands

We can use the following commands while working with the databases:

• use db_name: This command sets the database “db_name” as the default database for all DML (Data Manipulation Language) operations. We don’t need to specify the DB name every time.
• show databases: This command shows the list of all the databases, including the default databases mentioned above, present under the MySQL Server.

The above two commands do not need DBA privileges and even with developer access, one can execute it in real-time projects.

MySQL Delete Database

So far, we have understood how to create and work with the databases in MySQL. Now, let’s learn about how to delete a database.

Again, as mentioned above, Delete Database privilege is only with the DBAs. One cannot perform this operation with DBA access in real-time projects.

Delete Database Syntax:

DROP DATABASE [IF EXISTS] db_name;

Syntax Explanation:

#1) The first keyword in the syntax, “DROP”, informs the MySQL Server about the DDL (Data Definition Language) to be performed.

#2) The second keyword in the syntax, “DATABASE”, informs the MySQL Server about the MySQL object on which the DDL operation is to be performed.

#3) “IF EXISTS” is an optional command. If we do not mention this command and if the database does not exist, then the SQL will return the error code. Its best practice to use it.

#4) “db_name” is the name of the database that we intend to drop.

Delete Database Example

#1) Execute the drop database command on the MySQL Workbench.

#2) Observe the output of the command. The MySQL database “test_schema” has been removed from the list, and the execution of the command is successful.

Frequently Asked Questions

Q #1) How do I create a new database in MySQL?

Answer: Use the CREATE DATABASE command as explained in the above sections to create a database in MySQL.

Q #2) How can I see the fMySQL database?

Answer: Execute the command SHOW DATABASES and it will list all the databases in that particular MySQL Server.

Q #3) How do I select MySQL database?

Answer: Login to MySQL Server Database and execute the command “USE db_name” to select the database of your choice.

Q #4) How to delete a MySQL database?

Answer: Use the “DROP DATABASE” command to delete MySQL Database.

Q #5) I am not sure if the database name that I am creating exists on the MySQL Server. How to create a database in such a situation?

Answer: While creating a database, use the option “IF NOT EXISTS” and it will create your database only if the db_name that you are using is not present on the MySQL Server.

Conclusion

With this course, we have tried to explain about a database in MySQL, how to create a database, differences between databases and schemas, default databases and schemas available in MySQL, and last, steps to perform MySQL Delete Database.