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:
- MySQL Command Line Client
- MySQL Workbench
MySQL Command Line Client
We can create a new database in MySQL by using the CREATE DATABASE statement with the below syntax:
Parameter Explanation
The parameter descriptions of the above syntax are as follows:
| Parameter | Description |
|---|---|
| database_name | It is the name of a new database that should be unique in the MySQL server instance. The IF NOT EXIST clause avoids an error when we create a database that already exists. |
| charset_name | It is optional. It is the name of the character set to store every character in a string. MySQL database server supports many character sets. If we do not provide this in the statement, MySQL takes the default character set. |
| collation_name | It is optional that compares characters in a particular character set. |
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:
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:
We can check the created database using the following query:
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.
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.