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.
The 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.
The 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:
Using IF NOT EXISTS
You can use IF NOT EXISTS to prevent an error from occuring if the database already exists. Here's an example:
Using 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:
About Semicolons
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.
Database Design
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.
Naming Conventions
It's a good idea to keep a consistent naming convention. You could call the database FruitShop (title case), FRUITSHOP (uppercase), fruitshop (lowercase), fruit_shop (with an underscore separator), etc.
Some people also like to prefix their database objects. For example, prefixing tables with tbl or tbl_ and stored procedures with sp or sp_. So a table could be called tblCustomers or tbl_customers etc.
You should also think about plurals. Do you call a table that holds all customer records customer or customers. If you choose customers, then you should also call a table that holds all products products and not product.
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.