Home arrow MySQL arrow MySQL: Creating, Listing, and Removing Databases

MySQL: Creating, Listing, and Removing Databases

Todayís MySQL database tutorial covers a few basic concepts. Specifically, we are going to discuss how to create a MySQL database in the first place. Next, you will learn how to list and select databases in MySQL. Finally, you will see the steps to remove databases in MySQL.

By: wubayou
Rating: starstarstarstarstar / 0
April 17, 2012

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.

Conclusion

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.


 
 
>>> More MySQL Articles          >>> More By wubayou
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: