MySQL Basic: How to create or drop a database?

By /
Create Or Drop A MySQL Database

In this tutorial, We’ll learn how to create or drop a database in MySQL.

Features of MySQL database:

  • It is open-source software.
  • It is a relational Database Management System(RDBMS)
  • It has both the console as well as GUI(Graphical User Interface)
  • It is a widely used RDBMS.
  • It is useful for both small as well as large scaled applications.

Prerequisites

You must have MySQL installed on your desktop or laptop to be able to create a database. To download MySQL you can access the below link:
https://www.mysql.com/downloads/

Create and Drop a Database UsingMySQL command line

Step 1, click on windows and search for the MySQL command line.

Step 2, Enter the password for logging in to MySQL, which you have created.

To create a database in MySQL, write the following statement:

CREATE DATABASE databasename;
Create Database

The database name must be according to your application’s requirement, for example, if you are creating a project on Hospital Management, then you can name the database as hospital, or if you are creating a college management system then you can use the database name as college or the name of the college.

Naming Convention of Databases

Yes, databases also have naming conventions. In MySQL, You can only create a database using lower-case alphabets(a-z), numbers(0-9), and two special characters which are underscore(_) and hyphen(-).

For example, if you are creating a database as

CREATE DATABASE school database;

It’s the wrong way to write it, you cannot put space between each word of your database name, if you want to write two or more words you can use either of the delimiters, a hyphen, or an underscore.

CREATE DATABASE school_database;

It’s the correct way of writing the name of the database. The typical way of writing a database name is to write a single word with a self-explanatory name, in this way, finding a database from a huge lot will be easy.

To drop a database in MySQL, write the following statement:

Drop Database

Keep in mind, a drop database statement will remove your database from MySQL and the tables you’ve created within it, therefore, always make sure that the database is empty or does not contain any useful table. If in case, you have a table present in the database you want to drop then just copy that table to another database.

Create and Drop Database Using MySQL Workbench

Open MySQL Workbench and connect to MySQL instance.

To create a database using MySQL Workbench, click on the database icon present on the toolbar, a new tab will appear on the screen called new_schema and click on apply.

Creating New Schema In MySQL 2

After clicking on apply, a pop-up window will be displayed over your screen, you can change the name of the schema or database according to the requirements and click Next. Let the settings remain default and click on finish.

To check if your database is successfully created, then on the left side you’ll see the navigator tab, and there will be two options, administration and schemas, click on schemas and check for the database name you’ve just created.

Check The Database Over Schema Section

This is how you create a database using MySQL Workbench.

Drop Database Using MySQL Workbench

To drop a database, go to the schema section under the navigator bar, right-click on the database you want to drop, and click on drop schema.

Right Click On Database

After clicking on drop schema, a pop-up window will appear, to review SQL or to drop the database, as in our case the database is empty, therefore, we’ll directly drop the database by clicking on drop now.

Select Drop Now

You’ll see output at the bottom of MySQL Workbench prompting that the database has been dropped successfully and that’s how your database will be dropped by using MySQL Workbench.

Useful Resource:
https://dev.mysql.com/doc/refman/8.0/en/create-database.html
https://dev.mysql.com/doc/refman/8.0/en/drop-database.html