Blogs

MySQL CREATE DATABASE – Creating a New Database in MySQL

Posted on by admin

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

Introduction to the MySQL statement

CREATE DATABASE To

create a new

database in MySQL, use the CREATE DATABASE statement with the following syntax:CREATE DATABASE [IF NOT EXISTS] database_name [CHARACTER SET charset_name] [COLLATE collation_name]Code language: SQL (Structured Query Language) (sql)

In this syntax

  • :First, specify the name of the database after the CREATE DATABASE keywords. The database name must be unique within a MySQL server instance. If you try to create a database with a name that already exists, MySQL will fail.
  • Second, use the IF IT DOES NOT EXIST option to conditionally create a database if it does not exist.
  • Third, specify the character set and collation of the new database. If you omit the CHARACTER SET and COLLATE clauses, MySQL will have the default character set and collation for the new database.

Creating a new database using the mysql client tool

To create a new database through the mysql client tool, follow these steps

:

First, log in to MySQL Server with a user account that has the CREATE DATABASE privilege:

mysql

-u root -pCode language: SQL (Structured Query Language) (sql)

It will ask you to enter a password. To authenticate, you must type the password of the root user account and press the Enter key.

Then, display the current databases available on the server by using the SHOW DATABASES statement. This step is optional.

DISPLAY DATABASES;

Output:

+-+ | Database | +-+ | Classic models | | information_schema | | MySQL | | performance_schema | | sys | +-+ 5 rows together (0.00 sec)Code language: JavaScript (javascript)Then,

run the CREATE DATABASE command with a database name, for example, testdb and press Enter:CREATE DATABASE testdb

;

It will return the following:

Query successful, 1 row affected (0.02 seconds)Code language: CSS (css)

After that, use the SHOW CREATE DATABASE command to review the created database:SHOW CREATE DATABASE

testdb; Code language: Structured Query Language (SQL)MySQL returns database name and

character set and database collation:

+-+-+ | Database | Create Database | +-+-+ | TestDB | CREATE DATABASE ‘TESTDB’ /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION=’N’ */ | +-+-+ 1 row in aggregate (0.01 sec)Code language: JavaScript (javascript)

Finally, select the newly created database you want to work with by using the testdb USE:USE

statement; Code language: SQL (Structured Query Language) (sql)

Output:

Changed database

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

To exit the

mysql program, type exit command

:exitCode language: PHP (php)

Output

:Bye Creating a new database using MySQL

Workbench To create a new database using MySQL Workbench, follow these steps:First, start MySQL Workbench

and click the new connection setup button as shown in the following screenshot:

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

<img

src=”https://www.mysqltutorial.org/wp-content/uploads/2018/09/MySQL-CREATE-DATABASE-connection-name.png

” alt=”” />

MySQL Workbench displays a dialog box requesting the

root password:

You must (1) enter the root password, (2) check the password Save password to vault, and (3) click the OK button

.

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

<img

src=”https://www.mysqltutorial.org/wp-content/uploads/2018/09/MySQL-CREATE-DATABASE-new-connection-created.png” alt=”” />

MySQL Workbench opens the following window consisting of four parts: Browser, Query, Info, and Output.

Fourth, click the Create a new schema button on the connected server on toolbar:

In MySQL, the schema is synonymous with 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:

Sixth, MySQL Workbench opens the following window showing the SQL script to 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 displayed in the schemas tab of the Navigator section.

<

img src=”https://www.mysqltutorial.org/wp-content/uploads/2018/09/MySQL-CREATE-DATABASE-new-database-created.png” alt=”” />

Seventh, to select the database testdb2

, (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.

You can now work with testdb2 from MySQL Workbench

.

Summary

  • Use the CREATE DATABASE statement to create a new database

    • .

  • In MySQL, schemas are synonymous with databases.
admin