The various topics we are covering in this tutorial are far from advanced. They are as basic as they come, but they are important if you are new to MySQL. With that being said, let us jump into the first part of the tutorial, which is how to create a new database in MySQL.
How to Create a Database in MySQL
It is rather obvious that if you are planning to work with databases in MySQL, that you must know how to create one first. This, of course, applies if you have no other pre-existing databases to work with and want to begin with a fresh slate. Luckily, creating a database in MySQL is quite a simple process.
First, however, before jumping into the steps of creating a database, we should know what a database is. The basic definition of the term refers to a database as a collection of related data that is organized for easy and convenient access. Depending on your needs, that data can be cover a wide variety of subjects, whether it be employee data, customer data, contact data, financial data, and the list goes on and on. Thanks to programs like MySQL, you can take your data and apply all sorts of functions and procedures to make it more useful and provide added insight when performing analysis and making decisions.
So, now you know what a database basically does, and now it is time to create a new one in MySQL. Creation of a database in MySQL is done via the CREATE DATABASE statement.
For our first example, the following format would be used:
1 | CREATE DATABASE [IF NOT EXISTS] your_database;
You are probably wondering what the [IF NOT EXISTS] part stands for. We put that there optionally. You do not have to include it, but it is included in this example because we are creating a new database and want to avoid having duplicates. You will not be able to have two databases with the same name in one catalog, and even if you could, it would not make any sense to. Putting [IF NOT EXISTS] means that you want to create a new database using the selected name, only if it does not currently exist.
As for the your_database part, that refers to the name of the new database that you wish to use when it is created. It is rather self-explanatory, but when creating a new database, you want the name to be as descriptive as possible for future reference and overall organization.
How about creating the new database without the [IF NOT EXISTS] part? You can if you know for sure there are no others like it that already exist. The format would be as follows, using the new database name of devdata as our example:
1 | CREATE DATABASE devdata;
As you can see from the simplicity of the line above, we have the CREATE DATABASE statement to create the new MySQL database, followed by the name (devdata) we want to use. If all goes well and there are no pre-existing databases with the devdata name, MySQL will output a message that tells you the database has been created successfully.
It is worth mentioning that when thinking of a new database name, it should not only be descriptive, but you should use the same letter case (upper or lower) when working with that database in the future. In other words, if you use devdata when creating the database, continue to refer to it in that matter when working in MySQL. The same holds true whether it is DEVDATA, Devdata, etc. and this also applies to table names.
How to List or Show Existing Databases in MySQL
Listing all the databases in your MySQL server host is a task that can come in handy when creating new databases or when you want an inventory of what you have. Doing this will allow you to see what already exists and can help stop you from trying to create a new database with a pre-existing name. Listing databases in MySQL is done via the SHOW DATABASES statement.
When you use this statement, you will only see databases for which you have a certain privilege. If you have the global SHOW DATABASES privilege, however, you will see more. Let us put it to use:
1 | SHOW DATABASES;
Entering that should give you a listing of the new devdata database we created, as well as others that already exist.
How to Select a Database in MySQL
Just because you create a new database does not mean that it is selected for you to use right away. To make it your current database, you will have to select it. Even though creating a database is a one-time thing, selecting it for use will have to be done each time. This is done using the USE statement. Let’s select our new devdata database for use as an example:
1 | USE devdata;
There you see the USE statement to initiate your request of selecting a database, followed by the name of the database you want to use. After the database is selected, your actions in MySQL will affect that specific database.
How to Remove a Database in MySQL
There will come times when you want to delete or remove a database you have created in MySQL. This is done via the DROP DATABASE statement. Just remember that when you do so, that database is gone and so is all of the data inside of it. For this reason you want to be very careful about how and when you use the DROP DATABASE statement. Let’s try it with our devdata database.
1 | DROP DATABASE [IF EXISTS] devdata;
Here you see the DROP DATABASE which corresponds to the fact that we want to delete a database, followed by [IF EXISTS], which says to delete a database with that name only if it exists. This part is optional. Lastly, we have the name of the database we want to delete in the form of devdata.
You have now seen how to create a new database, list databases, select a database, and remove a database in MySQL. Thanks for stopping by and keep an eye out for more MySQL articles in the near future. While you are at it, feel free to leave any comments to share your own examples and techniques.