Mysql Create Database Command Line Ubuntu

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 CREATE DATABASE statement

To create a new database in MySQL, you 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 name of the database after the the CREATE DATABASE keywords. The database name must be unique within a MySQL server instance. If you attempt to create a database with a name that already exists, MySQL will issue an error.
• Second, use the IF NOT EXISTS option to conditionally create a database if it doesn't exist.
• Third, specify the character set and collation for the new database. If you skip the CHARACTER SET and COLLATE clauses, MySQL will the default character set and collation for the new database.

Creating a new database using the mysql client tool

To create a new database via the mysql client tool, you follow these steps:

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

            
              mysql -u root -p            
                          Code language:              SQL (Structured Query Language)              (              sql              )                      

It'll prompt you for entering a password. To authenticate, you need to type the password for the root user account and press the Enter key.

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

            
              SHOW DATABASES;            
          

Output:

            
              +--------------------+ | Database           | +--------------------+ | classicmodels      | | information_schema | | mysql              | | performance_schema | | sys                | +--------------------+                5                rows                in                set                (0.00 sec)            
                          Code language:              JavaScript              (              javascript              )                      

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

            
              CREATE DATABASE testdb;            
          

It'll return the following:

            
                              Query                OK, 1                row                affected                (0.02                sec)            
                          Code language:              CSS              (              css              )                      

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

            
                              SHOW                CREATE                DATABASE                testdb;            
                          Code language:              SQL (Structured Query Language)              (              sql              )                      

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

            
              +----------+----------------------------------------------------------------------------------------------------------------------------------+ | Database | Create Database                                                                                                                  | +----------+----------------------------------------------------------------------------------------------------------------------------------+ | testdb   | CREATE DATABASE                `testdb`                /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */                /*!80016 DEFAULT ENCRYPTION='N' */                | +----------+----------------------------------------------------------------------------------------------------------------------------------+                1                row                in                set                (0.01 sec)            
                          Code language:              JavaScript              (              javascript              )                      

Finally, select the newly created database to work with by using the USE statement:

            
                              USE                testdb;            
                          Code language:              SQL (Structured Query Language)              (              sql              )                      

Output:

            
              Database changed            
          

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

To quit the mysql program, type exit command:

            
                              exit                          
                          Code language:              PHP              (              php              )                      

Output:

            
              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 vault, and (3) click 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 theApply 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 showed 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.

Summary

• Use the CREATE DATABASE statement to create a new database.
• In MySQL, schemas are synonyms of databases.

Was this tutorial helpful?

Mysql Create Database Command Line Ubuntu

Source: https://www.mysqltutorial.org/mysql-create-database/

Share this post