In this article, you will learn how to manage or we can say list, create, select and drop databases in MySQL.
Read the entire article to better understand how you can manage MySQL databases. Each statement is explained with examples.
To display the list of existing databases, we use following statement:
Apart from list of databases, it also return the count
Sometimes, you may need to list out all databases and this statement would be really helpful.
To create a MySQL database, we use CREATE DATABASE statement.
CREATE DATABASE [IF NOT EXISTS] db_name [CHARACTER SET charset_name] [COLLATE collation_name]
Let’s understand the above syntax:
The CREATE DATABASE statement must be followed by name of the database. Database name must be unique, but if a database with same name already exists then it will result an error.
If you specify IF NOT EXISTS option then it will not result an error even if the database already exists.
Besides, you can also specify character set and collation for the database with CHARACTER SET and COLLATE options.
NOTE: Square brackets indicate, it is optional.
Let’s display the list of existing database.
Again, if you try to execute the same CREATE DATABASE statement which we executed previously, but without IF NOT EXISTS option then you will get this error:
To select a MySQL database, we use USE statement.
As soon as you execute the USE statement, none is replaced with the selected database name.
To verify, execute following statement:
DROP DATABASE statement deletes all the tables and database permanently.
DROP DATABASE [IF EXISTS] db_name;
It returns the number of tables it deleted.
If the specified database does not exist then it will result an error and to prevent this error use IF EXISTS option.
- If you want to display the list of databases, there is SHOW DATABASES statement.
- CREATE DATABASE statement, as the name states, creates a database.
- To select the database, there is USE statement.
- If you want to delete a database, use DROP DATABASE statement.