The article provides a detailed overview of how to create a database in MySQL using different methods and tools (including the Command Line, Workbench, and dbForge Studio for MySQL).
MySQL is a relational database management system based on SQL. It is developed, distributed, and supported by the Oracle Corporation. MySQL is free and open-source software and it is gaining more and more popularity due to its reliability, compatibility, cost-effectiveness, and comprehensive support.
MySQL has a fork – MariaDB, which is made by the original developers of MySQL. MariaDB has the same database structure and indexes which allows it to be a drop-in replacement for MySQL.
MySQL and MariaDB both support a number of popular operating systems including but not limited to the following: Linux, Ubuntu, Debian, Microsoft Windows, and macOS.
Before you can start creating a new MySQL database, you need to download MySQL Server.
In this article, we study the different ways to create a MySQL database.
Contents
1. CREATE DATABASE: MySQL syntax example
2. Create a database from the Command Line Client
3. Create a database using MySQL Workbench
4. Create a database using dbForge Studio for MySQL
CREATE DATABASE: MySQL syntax example
The very first way to create a database in MySQL that should be mentioned is by using the CREATE DATABASE statement. This statement creates a database with the specified name. Please remember, that to use it, you need the CREATE privilege for the database.
CREATE DATABASE mydatabase;Note:
You will get an error if you run the CREATE DATABASE statement without specifying IF NOT EXISTS and the database already exists. So it’s better to use the IF NOT EXISTS clause to prevent errors.
CREATE DATABASE IF NOT EXISTS mydatabase;After you execute the CREATE DATABASE statement, MySQL will return a message to notify whether the database has been created successfully or not.
Create a database from the Command Line Client
MySQL Command Line Client usually comes with MySQL Server installation pack. It is installed in two versions – with the support for UTF-8 and without it. You can run the console client right from the Start menu.
To create a new MySQL database via MySQL Command Line Client:
1. Run the client.
2. Enter your password.
3. Execute the create database command.
You can learn more about working with MySQL Command-Line Client in our How to Connect to MySQL Server article.
How to create a database in MySQL Workbench
MySQL Workbench is a popular visual database tool for designing, developing, and administering MySQL databases.
How to use MySQL Workbench to create a database:
1. Launch MySQL Workbench and click the + button to open the Setup New Connection wizard.
2. Enter the name for the connection and username, then click Test Connection. Enter the password in the dialog asking for the password.
We enter localhost and root.
3. Click the required connection in the MySQL Connections section of the Workbench start page.
4. In the MySQL Workbench window that opens, click the Create a new schema in the connected server button on the main toolbar. Then enter the schema name, change the character set and collation if necessary, and click Apply.
5. In the Apply SQL Script to Database window that opens, click Apply. Then click Finish.
6. Check that the database has appeared in the Navigator.
Workbench today is one of the most popular professional tools for MySQL database development. However, dbForge Studio for MySQL is its worthy rival boasting, in many aspects, superior functionality. Let’s look at how you can create a new MySQL database with dbForge Studio for MySQL.
How to create a database using dbForge Studio for MySQL
dbForge Studio for MySQL offers a quite simple and intuitive way to create a new database in MySQL. You don’t need to be a professional developer or DBA to get started with dbForge Studio for MySQL.
1. First, you need to create a required connection. Click the New Connection button on the Database Explorer toolbar. Alternatively, go to the Database menu in the main toolbar and click New Connection.
2. In the Database Connection Properties window that opens, provide all the necessary credentials for your connection.
3. The new connection will appear in the Database Explorer. Right-click the connection name and select New Database. Alternatively, go to the Database menu in the main toolbar and click New Database.
4. In the New Database tab that will open, enter the name for your new database, select charset and collation. You can check the script for the database in the lower part of the window. Click Apply Changes, once you’ve configured everything as required.
5. Check that your newly created database has appeared on your MySQL server. To do this, right-click the connection name in the Database Explorer and click Refresh.
After Database Creation
When you have multiple databases in your MySQL server, then to start working with the database you’ve created, use the following statement:
USE database_name;To create a new table in the database, use the following syntax:
CREATE TABLE [IF NOT EXISTS] table_name(
column_1_definition,
column_2_definition,
…,
table_constraints
) ENGINE=storage_engine;In the following picture, you can see how you can create a MySQL table in dbForge Studio for MySQL. The Studio has an automatic code completion feature, so won’t have to type all the code.
To learn more about creating tables in MySQL, please refer to our How to Create Tables using MySQL CREATE TABLE Statement and via GUI Tool for MySQL article.
Along with our deep dive into MySQL databases, we’ve incorporated a helpful guide on how to show or list tables in a MySQL database, equipping you with a crucial skill in database management.
To delete a table, use the following statement:
DROP DATABASE databasename;dbForge Studio for MySQL allows deleting a database visually without having to write code. Just right-click the required database in the Database Explorer, then click Delete.
Conclusion
In the article, we have walked through the ways to create a MySQL database and proved that dbForge Studio for MySQL is the best alternative to MySQL Workbench.
As you can see, dbForge Studio for MySQL allows creating a new database quickly and easily in a convenient and modern interface. And dbForge Support Team is always ready to help you in case there are any difficulties. Moreover, we provide a fully-functional 30-day free trial of our products. Download dbForge Studio for MySQL and test all its features to their full capacity. Also, you can watch this video tutorial:
