how to create mysql database in command-line windows

 

Create mysql database in command-line windows

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 statement for example:

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 throws 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 will not issue an error but terminate 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 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., mydb and press Enter:

mysql> CREATE DATABASE mydb;
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 mydb;

  

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 mydb;
Database changed

To quit the mysql program, type exit command:

mysql> exit
Bye