Create, Alter, Drop Database using MySQL Workbench
In this article, I am going to discuss how to Create, Alter, Drop Databases in MySQL Server using MySQL Workbench. Please read our previous article where we discussed how to create new users using MySQL Workbench.
Login to MySQL Server using MySQL Workbench:
First, I’ve opened MySQL Workbench and logged in, to the MySQL database server using the ‘root’ username and password. On the left-hand side, under the ‘schemas’ menu, you can see previously created databases if any and you can also see the ‘sys’ database that is the default system database for MySQL as shown in the below image.
How to Create a new Database in MySQL Server?
Let us see the different ways to create a database in MySQL using Workbench.
Way1: To create a new database, from the top window toolbars, select the ‘create a new schema in the connected server’ icon as shown in the below image. This will create the database on the server to which we are connected.
Once you click on the above create database schema icon, it will open the below new schema window. In this new schema window, we need to give the database name. Here, I am giving the database name as testdb1 and use default charset/collation and click on the Apply button as shown in the below image. The collation is used to store specific data characters, mainly useful for storing foreign languages.
Once you click on the Apply button, it will open Apply SQL Script to the database popup window. Here, simply click on the Apply button as shown in the below image.
Once you click on Apply button, it will open the below window. Here, simply click on the Finish button as shown in the below image to create the database.
Once you click on the Finish button, now you can see the new database i.e. testdb1 is listed under the SCHEMAS menu as shown in the below image.
Way2: Using SQL Script to Create MySQL Database using Workbench
Using SQL Script, we can also create a database in MySQL Workbench. For this, first, open the query tab. To open a new query tab, simply go to the File Menu and select New Query Tab option as shown in the below image. The Query Tab in MySQL Workbench is the place where we can write and execute our raw SQL Statements or queries.
Once you click on the New Query Tab option, it will open the below Query Tab window. Type CREATE DATABASE testdb2; SQL Statement and then click on the Execute button. The Execute Button shown in the below image will execute the selected portion of the SQL Script or everything if there is no selection.
Once you execute the above script, then the new testdb2 database will be created and you can see that database in the SCHEMAS section as shown in the below image.
If you are unable to see the newly created database in the SCHEMAS section, then simply refresh the schema section by clicking on the refresh button as shown in the below image. Once you click on the refresh button, then definitely you will be able to see the new database.
MySQL Workbench does not provide an option to rename the ‘database name’ but we can CREATE, UPDATE, OR DELETE tables and data rows using the MySQL Workbench tool.
How to Drop a Database in MySQL Server using MySQL Workbench?
To drop a database, select the database name, from under ‘SCHEMAS menu, right-click on the database which you want to delete and then select the Drop Schema option as shown in the below image.
Once you click on the Drop Schema option, the following popup will open, and here simply click on the Drop Now option as shown in the below image. The database including tables and data rows will be deleted from the database server.
You Can also delete a database using the following SQL Query in MySQL.
DROP DATABASE testdb2;
In the next article, I am going to discuss how to Create, Alter, Drop Tables using MySQL Workbench. Here, in this article, I try to explain how to Create, Alter, Drop Databases in MySQL Server using MySQL Workbench and I hope you enjoy this Create, Alter, Drop Databases in MySQL Server using MySQL Workbench article.
About the Author: Pranaya Rout
Pranaya Rout has published more than 3,000 articles in his 11-year career. Pranaya Rout has very good experience with Microsoft Technologies, Including C#, VB, ASP.NET MVC, ASP.NET Web API, EF, EF Core, ADO.NET, LINQ, SQL Server, MYSQL, Oracle, ASP.NET Core, Cloud Computing, Microservices, Design Patterns and still learning new technologies.