MySQL - Manage Database

In this tutorial, you will learn how to manage databases in MySQL. You will learn how to create new databases, remove existing databases, rename database and display all databases in the MySQL database server.
 
Creating Database
 
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, stored procedures, etc.
 
To create a database in MySQL, you use the CREATE DATABASE statement as follows: 
 
CREATE DATABASE [IF NOT EXISTS] database_name;
 
Let’s examine the CREATE DATABASE statement in greater detail:
  - Followed by the CREATE 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 element of the statement. The IF NOT EXISTS statement 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. The IF NOT EXISTS first remove existing database and create new database.
 
For example, to create vision database, you can execute the CREATE DATABASE statement as follows:
 
CREATE DATABASE vision;
 
After executing the statement, MySQL returns a message to notify that the new database has been created successfully or not.
 
Displaying Databases
 
The SHOW DATABASES statement displays 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;
 
We have thee databases in the MySQL database server. The information_schema and mysql are the default databases that are available when we install MySQL, and the vision is the new database that we have created.
 
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>>;
 
You can select the vision database using the USE statement as follows:
 
USE vision;
 
From now all operations such as querying data, create new tables or stored procedures, function which you perform, will take effects on the current database.
 
Removing Databases
 
Removing database means you delete the database physically. All the data and related objects inside the database are permanently deleted and this cannot be undone, 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>>;
 
Followed the DROP DATABASE is the database name that you want to remove. 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. Take a look at the following queries:
 
CREATE DATABASE IF NOT EXISTS temp_database;
SHOW DATABASES;
DROP DATABASE IF EXISTS temp_database;
 
In this tutorial, you’ve 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.
 
Rename Databases
 
RENAME {DATABASE | SCHEMA} db_name TO new_db_name;
 
This statement is added in Mysql 5.1.7 but was found to be dangerous and was remove after this release.
To perform the task of upgrading database names with the new encoding, use ALTER DATABASE <<DBNAME>>;