T Sql Create Database if Not Exists

Summary: in this tutorial, you will learn how to manage databases in MySQL. You will learn how to create new databases, remove existing databases, and display all databases in the MySQL database server.

Let's start creating a new database in MySQL.

Creating Databases

Before doing anything else with the data, you need to create a database. A database is a container of data. It stores contacts, vendors, customers or any kind of data that you can think of.

In MySQL, a database is a collection of objects that are used to store and manipulate data such as tables, database views, triggers, and stored procedures.

To create a database in MySQL, you use the CREATE DATABASE statement as follows:

            
                              CREATE                DATABASE                [IF                NOT                EXISTS] database_name;            
                          Code language:              SQL (Structured Query Language)              (              sql              )                      

Let's examine the CREATE DATABASE statement in greater detail:

• Followed by theCREATE DATABASE statement is database name that you want to create. It is recommended that the database name should be as meaningful and descriptive as possible.
• The IF NOT EXISTS is an optional clause of the statement. The IF NOT EXISTS clause prevents you from an error of creating a new database that already exists in the database server. You cannot have 2 databases with the same name in a MySQL database server.

For example, to create classicmodels database, you can execute the CREATE DATABASE statement as follows:

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

After executing this statement, MySQL returns a message to notify whether the new database has been created successfully or not.

Displaying Databases

The SHOW DATABASES statement lists all databases in the MySQL database server. You can use the SHOW DATABASES statement to check the database that you've created or to see all the databases on the database server before you create a new database, for example:

            
                              SHOW                DATABASES;            
                          Code language:              SQL (Structured Query Language)              (              sql              )                      

As clearly shown in the output, we have three databases in the MySQL database server. The information_schema and mysql are the default databases that are available when we install MySQL, and the classicmodels is the new database that we have created.

Selecting a database to work with

Before working with a particular database, you must tell MySQL which database you want to work with by using the USE statement.

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

You can select the classicmodels sample database using the USE statement as follows:

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

From now, all operations such as querying data, create new tables or calling stored procedures which you perform, will take effects on the current database i.e.,classicmodels .

Removing Databases

Removing database means deleting all the tables contained in the database and the database itself permanently. Therefore, it is very important to execute this query with extra cautions.

To delete a database, you use the DROP DATABASE statement as follows:

            
                              DROP                DATABASE                [IF                EXISTS] database_name;            
                          Code language:              SQL (Structured Query Language)              (              sql              )                      

Following the DROP DATABASE  clause is the database name that you want to delete. Similar to the CREATE DATABASE statement, the IF EXISTS is an optional part of the statement to prevent you from removing a database that does not exist in the database server.

If you want to practice with the DROP DATABASE statement, you can create a new database, make sure that it is created, and remove it.

Let's look at the following queries:

            
                              CREATE                DATABASE                IF                NOT                EXISTS                tempdb;                SHOW                DATABASES;                DROP                DATABASE                IF                EXISTS                temp_database;            
                          Code language:              SQL (Structured Query Language)              (              sql              )                      

The sequence of three statements is as follows:

1. First, we created a database named tempdb using the CREATE DATABASE statement.
2. Second, we displayed all databases using the SHOW DATABASES statement.
3. Third, we removed the tempdb using the DROP DATABASE statement.

In this tutorial, you have learned various statements to manage databases in MySQL including creating a new database, removing an existing database, selecting a database to work with, and displaying all databases in a MySQL database server.

Was this tutorial helpful?

T Sql Create Database if Not Exists

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

Share this post