How to create a database with SQL CREATE DATABASE

To create your own database, use the SQL CREATE DATABASE command. Make sure you’ve got admin rights and that there isn’t already a database with the same name in the same location.

What is SQL CREATE DATABASE?

With Structured Query Language you can edit databases and manage the data stored in them. Each action starts with the SQL CREATE DATABASE statement, which is used to create a new database. In this database, you can then create and save tables. To do this, you must have admin rights. If you don’t have these rights and try to create a database, you’ll see an error message: ERROR 1044 (42000): Access denied for user 'username'@'host' to database 'DatabaseName'. However, if you have the rights, creating a new database is easy.

Syntax and limitations

The syntax of SQL CREATE DATABASE is simple. It consists of a single line:

CREATE DATABASE name_of_database;

When naming your new database, you can use numbers or underscores** in addition to letters. However, you cannot use SQL keywords. If there is already a database with the same name, the system will display an error message. How to handle this situation is explained below.

Example for creating your own database

To create your first database with the SQL command CREATE DATABASE, you only need to come up with a name for it. An example of a statement like this in practice could look like this:

CREATE DATABASE My_Database;

If you execute this command, the system will create an empty database named ‘My_Database’. You can then work within this database according to your own ideas and requirements.

The command combined with IF NOT EXISTS

Above, we mentioned the issue of an existing database. In fact, there cannot be two databases with identical names on one server. To prevent an error message, you can use the IF NOT EXISTS statement. This extension to SQL CREATE DATABASE tells the system to create a database with a specific name only if it doesn’t already exist in the same location. For our example, the command looks like this:

CREATE DATABASE IF NOT EXISTS My_Database;

Overview of all databases

After creating the database with SQL CREATE DATABASE, it will be listed. You can get an overview of all available databases with the SHOW DATABASES command. Use it as follows:

SHOW DATABASES;

Access database with USE

To access your database or switch from one database to another, use the USE command. For our example, the corresponding command is:

USE My_Database;

Now you can use your database.

Similar commands to SQL CREATE DATABASE

If you’ve successfully created a database, you can create a new table in it. To do this, use SQL CREATE TABLE. To ensure that you can work in your database at all times, you should create regular backups. The appropriate command for this is SQL BACKUP DATABASE.