Create a MySQL Database
Creating a database in MySQL is about as easy as it gets. One line is all it takes. In fact, it usually takes me longer to think of a name for the database than it does to create it!
While you can certainly create your databases via the MySQL Workbench GUI, you will probably find it quicker to create them programatically using the
CREATE DATABASE statement.
CREATE DATABASE Statement
You can create a new database using the
CREATE DATABASE statement. This statement is part of SQL, which is a special-purpose language for querying and programming databases.
The syntax is
CREATE DATABASE db_name where
db_name is the name of the database you want to create.
For example, to create a database called
FruitShop type the following into the Query Tab and run the query:
That's all it takes. The SCHEMAS tab in the left menu should now be displaying your database:
If you can't see your database, click the little refresh icon next to the SCHEMAS heading.
You can also use the following command to display a list of databases on the server:
But of course, you now have an empty database. You will need to add tables and insert data before you have a fully functioning database. We'll get to that soon.
CREATE SCHEMA Statement
You could also use the
CREATE SCHEMA statement. This uses the same syntax, so the above statement could be rewritten to this:
IF NOT EXISTS
You can use
IF NOT EXISTS to prevent an error from occuring if the database already exists. Here's an example:
DROP DATABASE IF EXISTS
Using the above
IF NOT EXISTS is great as long as you have no intention of replacing the database (and all its data) with a fresh new one. But sometimes you might want to delete the old database and start again from scratch. This is where
DROP DATABASE comes in handy.
You can use
DROP DATABASE to drop all tables in the database and delete the database before running the
CREATE DATABASE statement. You can combine that with
IF EXISTS to specify that the statement should only be executed if the database currently exists. This will prevent an error occuring if you try to drop a database that doesn't actually exist.
So here's what that could look like:
The above example uses semicolons (
;) to mark the end of each statement. MySQL commands usually consist of an SQL statement followed by a semicolon. There are some exceptions but most commands follow this convention. If you find that a command that you write doesn't run, check that you've included the semicolon after each statement.
Before you create your database you need to think about its design. You need to think about what tables and other objects it will contain, as well as the data that will be stored in the database.
In reality, when you create a database, you don't just create an empty database and then think about what tables will go into it later. You would normally write a script that creates the database and all its tables and other objects all in one go.
Fortunately, doing this is quite straightforward. It is simply a matter of combining SQL statements, one after the other, for each object you want to create.
We'll look at creating tables next, but for now, here's a quick example:
This is a simple example that creates a database and a table within that database. You could write a long script that creates a database and many tables as well as other database objects.
You could also write another script that populates the tables with the initial data required. You could even combine both scripts into one if you wish.
It's a good idea to keep a consistent naming convention. You could call the database
FruitShop (title case),
fruit_shop (with an underscore separator), etc.
Some people also like to prefix their database objects. For example, prefixing tables with
tbl_ and stored procedures with
sp_. So a table could be called
You should also think about plurals. Do you call a table that holds all customer records
customers. If you choose
customers, then you should also call a table that holds all products
products and not
Whichever you choose, you should try to maintain the same convention throughout your database.
Establishing a naming convention will help enormously when you start to write queries against your database. You won't have to remember whether you named a particular table with an underscore, title case, etc.