How to Create a Database In MySQL?

The MySQL CREATE DATABASE statement creates a new database. It gives you the chance to specify a name for your database and additional options, including character set and collation, ensuring that your database is set up to accept data and its management.

We shall learn, in this article, how one creates databases within the MySQL database management system, with the use of the application called MySQL Workbench.

MySQL Workbench

MySQL Workbench is the visual database design and management tool that offers the GUI for interactions with MySQL databases. It suits users who need a visual interface rather than the command-line tool.

How to Create Database Using the MySQL Workbench Application

To create a database through MySQL Workbench, follow the steps below:

Step 1: Open MySQL Workbench
Search for MySQL Workbench in your applications and open it.

Step 2: Connect to a MySQL Instance
Click the “+” icon next to “MySQL Connections” to create a new connection or select an existing connection to your MySQL server.

Step 3: Enter Connection Details
In the “Setup New Connection” dialog, enter a connection name (e.g., “Local MySQL”), the hostname (usually “localhost”), and the port number (usually “3306”). Enter your username (often “root”).\

Step 4: Test the Connection
Click the “Test Connection” button. You may be prompted for your password. Enter your password and click “OK”. If the connection is successful, you’ll see a confirmation message.

Step 5: Create a New Schema (Database)
Once connected, navigate to the “Navigator” pane on the left side of the window. Under the “Administration” tab, click on “Data Export”.
Alternatively, click the “Create a new schema in the connected server” icon in the toolbar (it looks like a cylinder with a plus sign).

Step 6: Enter Database Name and Options
In the schema creation dialog, enter the name of your new database. For example, “your_database_name”. You can optionally set the character set and collation for the database. The defaults are usually fine.

Step 7: Apply the Changes
Click the “Apply” button. A SQL script will be generated. Review the script to ensure it contains the CREATE DATABASE your_database_name; statement. Click the “Apply” button again to execute the script.

Step 8: Finish the Creation
Click the “Finish” button to close the schema creation dialog.

Step 9: Verify the Database Creation
In the “Navigator” pane, click on the “Schemas” tab to refresh the list of databases. You should see your newly created database, “your_database_name”, listed.

Step 10: Create Tables Within Your Database
Right-click on your database in the Schemas list and select “Set as Default Schema”. Now, any new SQL queries you run will be executed against this database. To create a table, open a new query tab (File -> New Query Tab) and use the following syntax:

CREATE TABLE table_name (

    column1 datatype,

    column2 datatype,

    ...

);

For example, to create a books table:

CREATE TABLE books (

    id INT PRIMARY KEY AUTO_INCREMENT,

    title VARCHAR(255),

    author VARCHAR(100),

    published_date DATE

);

Step 11: Know Data Types
Selecting the proper data types for your columns is important. Here are some common types:

• INT: For integer values.
• VARCHAR(n): For variable-length strings (up to n characters).
• DATE: For storing date values.

Step 12: Set Primary Keys
A primary key uniquely identifies each record in a table. In our books table example, the id column serves as the primary key:

PRIMARY KEY (id)

Step 13: Execute the Table Creation Query
Click the lightning bolt icon to execute the SQL query. The table will be created in your selected database.

Conclusion

We learned in this article how one can create the database in MySQL using MySQL Workbench. Any ordinary project and even large scale applications require having a knowledge base on how one should develop databases, and one gets to design them using an interactive visual front-end provided by MySQL Workbench.