MySQL Create Database Statement

MySQL is one of the most popular relational database management systems based on Structured Query Language (SQL). It is a cross-platform RDBMS compatible with Windows, macOS, Linux, Solaris, and FreeBSD systems. Licensed under the GNU Public License, MySQL is an open-source database management system.

This article is intended to guide you through creating a database in MySQL using two different ways, i.e., using the command-line interface (CLI) and using MySQL Workbench. So, let us begin.

MySQL Create Database Statement

Creating a database is an elementary step while working with any database management system. Also, a single project can have the requirement of more than one database. Each database management system has its unique way of implementing a database.

MySQL implements a database as a directory consisting of files that correspond to tables. Before we proceed to create a database in MySQL, let us first inspect the syntax of the MySQL Create Database statement that helps to create a database in MySQL.

Syntax:

CREATE DATABASE [IF NOT EXISTS] database_name
[CHARACTER SET charset_name]
[COLLATE collation_name];

Parameters Explanation:

• database_name: It indicates the name of a database, which should be unique within the MySQL Server instance. When we use the ‘IF NOT EXISTS’, it creates a database only if the name we specify does not conflict with the names of existing databases.
• charset_name: It is the name of a character set and an optional parameter. MySQL supports a variety of character sets. If we do not specify a character set, MySQL takes the default one.
• collation_name: It is also an optional parameter. Collation is a set of rules instructing a database engine to compare and sort the character data in SQL Server. If we do not specify the collation_name, MySQL takes the default one.

We can define collate_name and charset_name at four different levels, i.e., SQL Server, database, table, and column.

Create MySQL Database using Command-Line Interface (CLI)

Using the command-line interface (CLI) to create a database in MySQL is one of the approaches. To open the command-line interface, search for ‘terminal’ and hit ‘Enter’ on the macOS system, and the command-line interface opens. For the Windows system, press Windows+R, the ‘Run’ box appears. Enter ‘cmd’, and then hit the ‘OK’ button.

Now, follow the below steps to create a database in MySQL using the CLI.

Step 1: To begin with, log in to the MySQL Server as a root server. To do this, run the following command:

>mysql -u root -p

Next, you need to enter the root user password.

Enter password:  _

After entering the password, hit the Enter key.

Step 2: If you wish to check the list of all databases present in the MySQL Server instance, you can simply use the show databases command. Also, it will help you to ensure that the name of the database you are going to create will not conflict with the names of the existing databases.

mysql> show databases;

It will list all the databases present in the MySQL Server instances as shown below:

Step 3: Now, let us create a database named ‘techgeekbuzz’ using the CREATE DATABASE statement.

mysql> CREATE DATABASE teckgeekbuzz;

The output you will see on the CLI interface after executing the above command will be:

Query OK, 1 row affected (0.01 sec)

If you want to review this database, then use the SHOW CREATE DATABASE command, as given below:

mysql> SHOW CREATE DATABASE teckgeekbuzz;

MySQL will return the database with the name ‘techgeekbuzz’ and its default character set and collation name.

Step 4: To access the newly created database and work with it, run the ‘USE” command as shown below:

mysql> USE teckgeekbuzz;

The output will be:

Database changed

Now you can create the tables and store data in the ‘teckgeekbuzz’ database.

Create Database using MySQL Workbench

Another approach to creating a database in MySQL is using the MySQL Workbench , which is a unified visual database design tool. It is an integrated development environment for MySQL that integrates database design, creation and maintenance, SQL development, and administration. It is compatible with macOS, Linux, and Windows.

MySQL Workbench does not require the CREATE DATABASE command for creating a new database. Instead, it enables creating a database graphically by entering a few details.

Following is a step-by-step guide for creating a new database using the MySQL Workbench:

Step 1: Launch the MySQL Workbench and click on the Setup New Connection button, represented with a ‘+’ sign that is enclosed in a circle as shown in the below screenshot:

Step 2: Next, enter the name for the connection in the text field with the ‘Connection Name’ label, and click the ‘Test Connection’ button present at the right-bottom.

Step3: A dialog box appears asking you to enter the password of the root user, as shown in the following screenshot:

Enter your root password, check ‘Save password in keychain’, and finally hit the ‘OK’ button. Again a dialog box will appear with a message - ‘Successfully made the MySQL connection”. Also, all information related to that connection, like host, port, user, and SSL, is displayed.

Step 4: Now, you can see a Local connection name; double-click it to go to the main MySQL Workbench.

You need to enter the password of the root and click the ‘OK’ button. The main MySQL Workbench appears, consisting of four parts, namely Navigator, Query1, Administration, and Action Output, as shown below in the screenshot:

Step 5: In the toolbar on the top, click on the create a new schema in the connected server button, as shown below, highlighted with a red-colored rectangle.

In MySQL, creating a new schema refers to creating a new database. After you click on the create schema button, a dialog box appears, where you need to enter a schema name and change the character set and collation if required.

We have entered ‘teckgeekbuzz’ as the schema name, i.e., database name, and didn’t change the character and collation name. Click on the ‘Apply’ button present at the right-bottom of the screen, and a dialog box will appear, as shown below.

Finally, click on the ‘Apply’ button, and the ‘techgeekbuzz’ schema will be created. You can see it in the ‘schema’ part of the MySQL Workbench.

Step 6: Now, it’s time to set the created database as a default schema. To do it, right-click on the schema name and then choose ‘Select as Default Schema’ from the list.

After setting the database as a default schema, you can work with it from the MySQL Workbench. You can create tables, views, functions, and stored procedures in that schema.

Conclusion

That’s all about creating a database in MySQL using the command-line interface (CLI) and the MySQL Workbench. Creating a database in MySQL using both approaches is easier. While using the command-line interface, you need to remember the commands to log in to MySQL Server, create a database, and use it for further use. Also, the MySQL Create Database statement is what you need to create a database.

On the other hand, MySQL Workbench does not require any commands. It just requires you to perform a couple of steps to create a new database.

People are also reading:

• PostgreSQL vs MySQL
• MongoDB vs MySQL
• MariaDB vs MySQL
• MySQL Cheat Sheet
• Run MySQL in Docker Containers
• Use MySQL Database in Python
• PHP MySQL Functions
• SQL vs MySQL
• PHP MySQL login
• PHP – Connecting to MySQL Database