MySQL CREATE DATABASE

 MySQL CREATE DATABASE

Summary: in this tutorial, you will learn how to use the MySQL CREATE DATABASE statement to create a new database in the server.

MySQL implements a database as a directory that contains all files which correspond to tables in the database.

To create a new database in MySQL, you use the CREATE DATABASE the statement with the following syntax:

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

First, specify the database_name following the CREATE DATABASE clause. The database name must be unique within the MySQL server instance. If you try to create a database with a name that already exists, MySQL issues an error.

Second, to avoid an error in case you accidentally create a database that already exists, you can specify the IF NOT EXISTS option. In this case, MySQL does not issue an error but terminates the CREATE DATABASE statement instead.

Third, specify the character set and collation for the new database at creation time. If you omit the CHARACTER SET and COLLATE clauses, MySQL uses the default character set and collation for the new database.

Creating a new database using mysql program

To create a new database via the mysql the program, you use the following steps:

First, log in to the MySQL Server using the root user

>mysql -u root -p
Enter password: ********

Type the password for the root user and press Enter.

Next, to display the existing database in the server to make sure that you are not creating a new database that already exists, you use the SHOW DATABASES command as follows:

mysql> SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| classicmodels      |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

MySQL returns five existing databases in the current server.

Then, issue the CREATE DATABASE command with the database e.g., testdb and press Enter:

mysql> CREATE DATABASE testdb;
Query OK, 1 row affected (0.12 sec)

After that, if you want to review the created database, you can use the SHOW CREATE DATABASE command:

mysql> SHOW CREATE DATABASE testdb;

MySQL returns the database name and the character set and collation of the database.

Finally, to access the newly created database, you use the USE database command as follows:

mysql> USE testdb;
Database changed

Now, you can start creating tables and other databases objects within the  testdb database.

To quit the MySQL program, type exit the command:

mysql> exit
Bye

Creating a new database using MySQL Workbench

To create a new database using the MySQL Workbench, you follow these steps:

First, launch the MySQL Workbench and click the setup new connection button as shown in the following screenshot:

Second, type the name for the connection and click the Test Connection button.

MySQL Workbench displays a dialog asking for the password of the root user:

You need to (1) type the password for the root user, (2) check the Save password in the vault, and (3) click the OK button.

Third, double-click the connection name Local to connect to the MySQL Server.

MySQL Workbench opens the following window which consists of four parts: Navigator, Query, Information, and Output.

Fourth, click the create a new schema in the connected server button from the toolbar:

In MySQL, the schema is the synonym for the database. Creating a new schema also means creating a new database.

Fifth, the following window is open. You need to (1) enter the schema name, (2) change the character set and collation if necessary, and click the Apply button:

Sixth, MySQL Workbench opens the following window that displays the SQL script which will be executed. Note that the CREATE SCHEMA statement command has the same effect as the CREATE DATABASE statement.

If everything is fine, you will see the new database created and shown in the schemas tab of the Navigator section.

Seventh, to select the testdb2 database, (1) right-click the database name and (2) choose Set as Default Schema menu item:

The testdb2 node is open as shown in the following screenshot.

Now, you can work with testdb2 from the MySQL Workbench.

In this tutorial, you have learned how to create a new database from the MySQL program using the MySQL CREATE DATABASE statement and from MySQL Workbench using the CREATE SCHEMA statement.