MySQL Create Database

A database is used to store the collection of records in an organized form. It allows us to hold the data into tables, rows, columns, and indexes to find the relevant information frequently. We can access and manage the records through the database very easily.

MySQL implements a database as a directory that stores all files in the form of a table. It allows us to create a database mainly in two ways:

1. MySQL Command Line Client
2. MySQL Workbench

MySQL Command Line Client

We can create a new database in MySQL by using the CREATE DATABASE statement with the below syntax:

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

Parameter Explanation

The parameter descriptions of the above syntax are as follows:

Example

Let us understand how to create a database in MySQL with the help of an example. Open the MySQL console and write down the password, if we have set during installation. Now we are ready to create a database. Here, we are going to create a database name "employeedb" using the following statement:

mysql> CREATE DATABASE employeesdb;

It will look like the below output:

We can review the newly created database using the below query that returns the database name, character set, and collation of the database:

mysql> SHOW CREATE DATABASE employeedb; 

We can check the created database using the following query:

mysql> SHOW DATABASES;  

After executing the above query, we can see all the created databases in the server.

Finally, we can use the below command to access the database that enables us to create a table and other database objects.

mysql> USE emplyeedb; 

NOTE: All the database names, table names, and table field names are case sensitive. We must have to use proper names while giving any SQL command.

MySQL Workbench

It is a visual database designing or GUI tool used to work with database architects, developers, and Database Administrators. This visual tool supports SQL development, data modeling, data migration, and comprehensive administration tools for server configuration, user administration, backup, and many more. It allows us to create new physical data models, E-R diagrams, and SQL development (run queries, etc.).

To create a new database using this tool, we first need to launch the MySQL Workbench and log in using the username and password that you want. It will show the following screen:

Now do the following steps for database creation:

1. Go to the Navigation tab and click on the Schema menu. Here, we can see all the previously created databases. If we want to create a new database, right-click under the Schema menu and select Create Schema or click the database icon (red rectangle), as shown in the following screen.

2. The new Schema window screen open. Enter the new database name (for example, employeedb) and use default character set and collation. Now, click on the Apply button as shown in the screen below:

3. A new popup window appears. Click on the Apply button.

4. A new popup screen appears. Click on the Finish button to complete the database creation.

5. After successful database creation, we can see new databases in the Schema menu. If we do not see this, click on the refresh icon into the Schema menu.

6. We can see more information about the database by selecting the database and click on the 'i' icon. The information window displays several options, like Table, Triggers, Indexes, Users, and many more.

7. MySQL Workbench does not provide an option to rename the database name, but we can create, update, and delete the table and data rows from the database.