MYSQL: CREATE A DATABASE

MYSQL CREATE DATABASE – SYNTAX and EXAMPLE

Let us start with a very basic statement to create the databases in MySQL. 

SYNTAX :-

CREATE DATABASE <database_name> ;

We can specify the database_name of our choice like testDatabase.

Example:- 

CREATE DATABASE MyFirstTestDatabase;

Here we are creating a database with the name ‘MyFirstTestDatabase’. The below image shows the action output from mysql workbench,

We can always verify if the database has been successfully created by running the SHOW DATABASES command.

SHOW DATABASES;

Output:-

As you can see in the above image, our database ‘MyFirstTestDatabase‘ has been created successfully and is a part of other databases that exist in the server.

MYSQL CREATE DATABASE IF NOT EXISTS

If we try to create a database with a name that already exists, mysql server will give us an error. For example if we run the CREATE DATABASE command after MyFirstTestDatabase has been created, we will get an error.

CREATE DATABASE MyFirstTestDatabase;

<span class="has-inline-color has-vivid-red-color">Error Code: 1007. Can't create database 'MyFirstTestDatabase'; database exists.</span>

So, to avoid such a situation, we will use the IF NOT EXISTS clause which is optional along with CREATE DATABASE. For example, re-running the above statement including IF NOT EXISTS will give a warning and not an error.

CREATE DATABASE IF NOT EXISTS MyFirstTestDatabase;

Output:- 

MYSQL CREATE DATABASE – COLLATION AND CHARACTER SET

While creating a database we can also specify the collation and character set. Character set allows us to store all characters from different languages and collation is used to compare the characters in a character set. We can also say that collation is a set of rules to compare the character set. Hence the collation and character set go hand in hand.

For example, we can set – latin1 as the character set and latin1_german1_ci as the collation while creating a database. See the below command for clarity.

CREATE DATABASE IF NOT EXISTS MyFirstTestDatabaseWithCollation CHARACTER SET latin1 
COLLATE latin1_german1_ci; 

PRO TIP : By default, the character set and collation are utf8,  utf8_unicode_ci respectively.

MYSQL – REVIEW THE CREATED DATABASE

Once the database is created we can review its characteristics by running the SHOW CREATE DATABASE command. See the below code example.

SHOW CREATE DATABASE MyFirstTestDatabase;

Output:-

Here, we can review the collation and character set of the database. The above image shows that we have used the default collation and character set while creating this database.

MYSQL: DROP a Database

To delete the database from the server, we can use the below command in MYSQL

SYNTAX:-

DROP DATABASE <database_name>;

EXAMPLE:- 

DROP DATABASE MyFirstTestDatabaseWithCollation;

To verify if the database has been dropped, we can again run the query SHOW DATABASES to confirm if we were successful.

SHOW DATABASES;

Output:-

As we can see in the above image, we do not have the database ‘MyFirstTestDatabaseWithCollation’.

MYSQL CREATE DATABASE – COMMAND LINE or TERMINAL

In the above sections we discussed creating a database and we used MYSQL workbench sql visual editor window and results grid. This can be achieved through terminal window as well using the same commands. Let us create a database again using a terminal window.

Connect to mysql using the terminal , provide the user as root and your password.

>mysql --user=root --password= **ENTER YOUR PASSWORD**

Once you have connected to mysql, type the command to create the database and press enter.

mysql> CREATE DATABASE IF NOT EXISTS MyFirstDatabaseCommandLine;

To verify, we can use SHOW DATABASES command.

mysql> SHOW DATABASES;

Output :-

We hope that this article will help you create, review, and drop databases in MYSQL. Have a great time playing around with databases to strengthen your knowledge !!.