The article provides a detailed description 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 Oracle Corporation. MySQL is free and open source software and 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 MySQL developers. MariaDB has the same database structure and indexes that allow it to be a direct replacement for MySQL.
MySQL and MariaDB 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 must download MySQL Server.
In this article, we study the different ways to create a MySQL database.
Contents
1. CREATE DATABASE: MySQL 2 syntax example. 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 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. 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. Therefore, it is better to use the IF IT DOES NOT EXIST clause to avoid mistakes.
CREATE DATABASE IF MYDATABASE DOES NOT EXIST;
After executing the CREATE DATABASE statement, MySQL will return a message to notify you if the database was successfully created or not.
Create a database from the command-line client
MySQL
Command Line Client usually comes with the MySQL Server installation package. It is installed in two versions: with support for UTF-8 and without it. You can run the console client directly from the Start menu.
To create a new MySQL database through MySQL Command Line Client:1. Run the client.2. Enter your password.3. Run the create database command.
You can learn more about working with the MySQL Command-Line Client in our article How to connect to MySQL Server. How
to create a database in MySQL
Workbench MySQL Workbench is a popular visual database tool for designing, developing, and managing
MySQL databases
.
How to use MySQL Workbench to create a database:
1. Start MySQL Workbench and click the + button to open the Set Up New Connection wizard.
2. Enter the connection name and user name, and then click Test Connection. Enter the password in the dialog box that prompts for the password. Enter localhost and root.
connection in Workbench” />
3. Click the required connection in the MySQL Connections section of the Workbench home page.
4. In the MySQL Workbench window that opens, click the Create a new schema on 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. Verify that the
database has appeared in the Navigator.
today is one of the most popular professional tools for MySQL database development. However, dbForge Studio for MySQL is its worthy rival that has, in many respects, superior functionality. Let’s see 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 fairly 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 on the main toolbar and click New Connection.
2. In the Database Connection Properties window that opens, provide all the credentials required for your connection.
3. The new connection appears in Database Explorer. Right-click the connection name and select New Database. Alternatively, go to the Database menu on the main toolbar and click New Database.
4. On the New Database tab to open, type the name of the new database, select character set and collation. You can check the database script at the bottom of the window. Click Apply changes, once you’ve set everything up as needed.
5. Verify that the newly created database has appeared on your MySQL server. To do this, right-click the connection name in Database Explorer and click Refresh.
After database creation When you have
multiple databases on your MySQL server, to start working with the database you have 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 image, you can see how you can create a MySQL table in dbForge Studio for MySQL. The Studio has an auto-code completion feature, so you won’t have to write all the code.
For
more information on creating tables in MySQL, see our article How to create tables using MySQL CREATE TABLE statement and via GUI Tool for MySQL. To
delete a table, use the following statement:
DROP DATABASE databasename;
dbForge Studio for MySQL allows you to delete a database visually without having to write code. Simply right-click the required database in Database Explorer, and then click Delete.
a database using dbForge Studio for MySQL” />
Conclusion
In the article, we have walked through the ways to create a MySQL database and proven that dbForge Studio for MySQL
is the best alternative to MySQL Workbench. As you can see, dbForge Studio for MySQL
allows you to create a new database quickly and easily in a comfortable and modern interface. And dbForge’s support team is always ready to help you in case there are any difficulties. In addition, we offer a fully functional 30-day free trial of our products. Download dbForge Studio for MySQL and test all its features to their maximum capacity. Also, you can watch this video tutorial:
