Summary: in this tutorial, you will learn how to create a new database in MySQL in different ways with examples.
In order to create tables and subsequently store data into them, you need to have a database. Apart from tables, the database holds objects like views, triggers, stored procedures, etc. I hope you already have installed MySQL in your system. If not, you can check our tutorial on how to install MySQL database on your computer.
The CREATE DATABASE statement is used to create a database in MySQL.
Syntax
The basic syntax of the MySQL CREATE DATABASE is as follows.
CREATE DATABASE [IF NOT EXISTS] database_name [CHARACTER SET charset_name] [COLLATE collation_name];
In this syntax,
First, you need to specify the database name which needs to be created after the CREATE DATABASE keywords. The database name you are specifying here needs to be unique throughout the MySQL server instance. If the database name already exists, MySQL will give an error.
Second, optionally you can specify IF NOT EXIST which ensures that if the database already exist MySQL will not throw any error.
Third, optionally you can specify the character set and collation for the database. If not specify MySQL database will be created with default character set and collation.
You can create a database in MySQL in two methods:
- Using MySQL Command Line Client
- Using MySQL Workbench
Let’s check these methods one by one.
1) Creating MySQL database using MySQL Command Line Client
For creating a new database via MySQL Command Line Client you need to follow the below steps:
First, logon to the MySQL server Command Line Client using a user who has CREATE DATABASE privileges or if you are using command prompt type the following command.
mysql -u root -p
It will ask for the root password which you have kept during the MySQL installation. Type the root user password and press the enter key.
Second, check the already available databases in MySQL using the show databases command. This step is optional.
mysql> show databases;
Output:
+--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | world | +--------------------+ 5 rows in set (0.00 sec)
After that, you can use the CREATE DATABASE statement and the database name i.e. here companydb to create a new database in MySQL.
mysql> CREATE DATABASE companydb;
Output:
Query OK, 1 row affected (0.10 sec)
Third, verify if the database is created correctly or not by issuing the same show databases command.
mysql> show databases;
Output:
+--------------------+ | Database | +--------------------+ | companydb | | information_schema | | mysql | | performance_schema | | sys | | world | +--------------------+ 6 rows in set (0.00 sec)
As you can from the above result that the new database companydb is successfully created.
Also, you can review the newly created database using the following command:
+-----------+-------------------------------------------------------------------------------------------------------------------------------------+ | Database | Create Database | +-----------+-------------------------------------------------------------------------------------------------------------------------------------+ | companydb | CREATE DATABASE `companydb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ | +-----------+-------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
Finally, you can select the database by using the use statement.
mysql> use companydb;
Output:
Database changed
Now, you can start creating database objects like tables, views, procedures, etc.
To quit the mysql program, type exit command:
mysql> exit
Output:
Bye
2) Creating MySQL database using MySQL Workbench
For creating a new database using MySQL Workbench, you need to follow the below steps:
First, launch the MySQL Workbench and create a new connection by clicking on the “+” button which is next to the MySQL Connections as shown below.
Second, enter the connection name as per your choice. I have given localhost. By default, the username is root. If you use a different user account, you can change it in the Username textbox.
Next, click the Store in Vault... button to enter the password for the user. A window will display. You enter the correct password and click the OK button.
Click the Test Connection button to test if the connection to the MySQL Server is successful or not. Then click the OK button if the connection is established successfully.
Now, click the OK button to save the connection.
Click the newly created connection under MySQL Connections to connect to the MySQL Server:
MySQL Workbench opens the following window which consists of four parts: Navigator, Query, Information, and Output.
Third, from the toolbar click the create a new schema in the connected server button.
In MySQL, database and schema are synonyms. Creating a new database means creating a new schema.
Forth, Now the following window will open. 1) Specify the name of the schema 2) specify the default character set and default collation 3) click on Apply button.
Sixth, Once you click on the Apply button the following window will appear. Here the SQL script which will be executed will be displayed. Note that the CREATE SCHEMA statement command has the same effect as the CREATE DATABASE statement.
As soon as, you click on the Apply button the new database companydb1 will be created. You can see the newly created database in Schemas under the Navigator section.
Seventh, to select and use the companydb1 database, (1) right-click the database name and (2) choose Set as Default Schema menu item as below:
The newly created database companydb1 is in open mode as shown below:
Now you create any kind of database objects like tables, views, functions, etc in companydb1 database from MySQL workbench and manipulate the data.