MySQL Create Database 2

MySQL Create Database

How to Create Database in MySQL Server with example? Here, we will use both the command prompt and the Workbench.

A Database is a storage location where you can store business data. Every Database uses tables to store the information in a normalized way. So, we can easily Insert, Select, Update, and Delete the business data.

MySQL Create Database example

Before we start to create a database, Let us see the list of available ones. To get this information, you have to use the following command.

SHOW DATABASES;

The below screenshot will show you the available DBS.

Available DBS from Command Prompt 1

The basic syntax for MySQL create Database is:

CREATE DATABASE Database_Name

For the demonstration purpose, we are going to create a database called First_Database. So, Replace the Database_Name with First_Database, as shown below

CREATE DATABASE First_Database;

From the below screenshot, you can observe that the MySQL command was executed successfully.

View dbs from command prompt 2

Now, let me show you the available DBS. And you can see the first_database on the list

3

MySQL Create Database using Workbench

Before we start, let me open the Workbench. From the below screenshot, you can see a list of the available ones under the Schemas pane.

Workbench GUI 4

Create Database using Query in Workbench

In this example, we create a new DB called second_database.

CREATE DATABASE second_database;

Click on the Execute button to execute the command

5

From the below screenshot, you can see the command executed successfully, and you can see the second_database under schemas

MySQL Create Database 6

Create DB using Wizard in Workbench

If you are unaware or not familiar with queries, use the MySQL Workbench to create a database. To do so, within the Menu, click on the Create a New Schema in the Connected Server button.

7

Once you select the option, the following tab window will open. Please change the Name of the schema from new_schema to Third_Database.

8

You can use the Collation drop-down box to change the Server Default collation.

9

Click the apply button to create a new schema or db.

TIP: We configured MySQL to convert the schema names and table names to lowercase by default. Because we used the Third_Database as the schema, it is saying that it will convert the name to lowercase.

apply changes to object 10

Click Apply button

Click Apply Button 11

Next, click the Finish button

Execute Statement 12

Now, you can see the third_database under the schemas.

13

TIP: If you didn’t find the newly created DB in Workbench, Please click on the refresh button beside the Schemas

How to Check name exists or not?

Let us see what will happen when we create a database in MySQL with an existing name. From the below screenshot, you can see it is throwing an error saying: Can’t create third_database.

CREATE DATABASE third_database;
Create DB with existing Name Error code 1007 14

NOTE: In an organization, we may or may not have the privileges to know the available DBS. So, it is always advisable to check whether the name already exists or not.

If you have the privilege to see the available ones, then you can use the SHOW DATABASES command to see the list. If not, use the below option.

The following statement will only execute the Statement only if the third_database is not available in the system.

CREATE DATABASE IF NOT EXISTS third_database;
MySQL Create Database If not Exists 16

Let me try a different name in this command.

CREATE DATABASE IF NOT EXISTS fourth_database;
15