MySQL Create Database Statement

MySQL is one of the most popular relational database management systems. It is developed and maintained by the Oracle Corporation. Other than Drupal, Joomla, and WordPress, MySQL is used by global brands like Facebook, Flickr, Twitter, and YouTube.

MySQL Create Database

In addition to being available as a free and open-source software under the GNU General Public License, MySQL is also available under a plethora of proprietary licenses.

Creating a Database in MySQL

Creating a database is the very first step, or you can also say elementary, in dealing with any database management system project.

Depending on the size and scope, a database management project can have one or many databases. Once you’ve successfully created a database, SQL commands are used for working with the database.

Different database management systems have different ways of implementing a database. MySQL implements a database as a directory containing all files corresponding to the tables present in that very database.

Although the underlying principle remains the same, there are two ways in which a database is created in MySQL. The first way involves creating a database directly from the MySQL, while in the other way we use MySQL Workbench.

Before explaining each one of the two ways for creating a database in MySQL, let’s first inspect the MySQL Create Database statement.

The MySQL Create Database Statement

The CREATE DATABASE statement is used for creating a new database in the server. Its general syntax is:

CREATE DATABASE database_name
[CHARACTER SET charset_name]
[COLLATE collation_name]

You start a create database statement by specifying the database name. It must be unique within the MySQL server instance. If you give a name to the database being created that has already been assigned to some other existing database, then MySQL will generate an error.

It is possible to specify the character set as well as the collation for the new database at the creation time. However, if you choose not to, then MySQL will use the default character and collation for the newly-created database.

Creating a MySQL Database Using:

(i) The MySQL Program

One way of creating a database in MySQL is using the mysql program and the CREATE DATABASE statement. For creating a database in this manner, you need to:

Step 1 - Log in to the MySQL as the root user. For this, use the following command:

>mysql -u root -p

Now, you will be prompted to enter the root user password, like this:

Enter password: _

Type the root user password and hit Enter

Step 2 - To check and ensure that you are not actually creating a database with a name that has already been assigned to some other existing database in the MySQL server instance, you can use the SHOW DATABASES command. It will return with all the database names that are currently in use

Step 3 - Now, use the CREATE DATABASE command for creating a new database in the MySQL server instance as demonstrated below:

mysql> CREATE DATABASE database_name;

For reviewing the newly created database, you can use the SHOW CREATE DATABASE command:

mysql> SHOW CREATE DATABASE database_name;

It will return the database details along with the character set and collation for the same.

Step 4 - To access the newly created database, use the USE command, demonstrated as follows:

mysql> USE database_name;

Database changed

You can start creating tables, storing values, etc. in the newly created database from here on.

(ii) The MySQL Workbench

MySQL Workbench brings together SQL administration, development, database management, et cetera: all accessible from a single access point. It is a great way for streamlining everything that you do with MySQL.

Obviously, you can also create a database in MySQL using the MySQL Workbench. However, in doing so, you don’t need to use the CREATE DATABASE command. Instead, you will create a database graphically i.e. by entering some details and doing a lot of clicking.

Here is the step-by-step procedure for creating a new database with the MySQL Workbench:

• Step 1 - Launch the MySQL Workbench and click on the Setup New Connection button (It is the one with the ‘+ contained in a circle’ symbol)
• Step 2 - Enter name for the connection followed by clicking the Test Connection button
• Step 3 - You will now be prompted with a dialogue box, requesting the root password. Enter it, check the Save password in vault checkbox (if not done already) and hit the Ok button
• Step 4 - Next, double-click the Local connection name in order to connect to the MySQL server instance. Now you will be taken to the main MySQL Workbench window consisting 4 sections, namely Information, Navigator, Output, and Query
• Step 5 - Click on the create a new schema in the connected server button present in the toolbar (In MySQL, the schema is just another name for referring a database)
• Step 6 - A window will now pop up. Supply it with the schema name and change the character set and collation settings, if needs be. Finally, click on the Apply button
• Step 7 - Next, MySQL Workbench will open a window displaying the SQL script that will be executed. You will notice a CREATE SCHEMA command here, which is equivalent to the CREATE DATABASE command. As such, it is followed by the database name. To continue, click on the Apply button
• Step 8 - The newly created database will be reflected in the SCHEMAS tab present in the Navigator section of the MySQL Workbench window
• Step 9 - To select the newly created database, right-click on it and select the Set as Default Schema option

Now, you are able to work with the newly created database via the MySQL Workbench. You can now add new tables and data to the database from here or from the MySQL program as you find suitable.

The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert

All Complete!

That wraps up creating a new database in MySQL and the MySQL Create Database statement. Creating a database is the entry point of getting started with a database management project.

So now that you know how to create a database in MySQL using the Create Database statement, it’s time you get going and work with your database projects.

Do you find this post useful? Or were you disappointed? Let us know via comments. If there are any mistakes in the blog post or any ways to make it better, please share them via the dedicated comments window below. Thanks in advance!

Looking to improve your MySQL skills or interested in learning something new about the popular relational database management system? Check out these best MySQL tutorials now!

People are also Reading:

• SQL Courses
• SQL Certifications
• SQL Books
• Download SQL Cheat Sheet PDF
• Top DBMS Interview Questions & Answers
• Difference between MongoDB vs MySQL
• What is Stored Procedure?
• Difference between OLTP vs OLAP
• What is MongoDB?
• SQL Server Ceritifcaitons