You do not have to be a genius to create a table in MySQL. In fact, it is a rather simple process. Once you have your database created, you have plan out and implement its structure. In other words, you must begin to answer some questions, such as what tables do you want your database to have? What information do you want those tables to store? What columns do you want each table to have?
Answering these questions can get a little tricky, depending on the data you are working with. You want to be as descriptive as possible with your columns, but you do not want to overdo it. This tutorial will begin with creating a sample database table that is simple in nature. We will then use the sample table and perform some other common tasks with it in MySQL.
How to Create a Database Table
Before we create a new table, we should see if our database has any tables that already exist. This can be achieved using the SHOW TABLES statement:
mysql> SHOW TABLES;
Empty set (0.00 sec)
As you can see, we have no tables at this time, so it is time to create a new one. For the purpose of this tutorial, we are going to pretend that we have a nursing home where we take care of elderly patients. We want our table to store data for each patient that is a part of our nursing home program and that lives in our facility. As for the name of our table, patient seems like a logical choice, so let’s go with that.
Now comes the point where we need to decide what type of data we are going to store in the table for each patient. The table should at least have a column that details the patients’ names. If you only have names in a table, however, the table is not very descriptive. Our whole purpose here is to have a database filled with tables that contain valuable information, so we need to add some more columns to give us a more valuable table.
Since we are caring for people in our business, there will come a time when we have to contact a relative if something goes wrong. Let’s call that column of data relative. Another important piece of data worth keeping is the gender of the patient. Let’s call that column gender. Maybe you want to keep statistics on where your patients come from before they arrive. You can add the hometown column for that data.
Age is obviously very important, especially considering the nature of our business. You may think that simply adding a column titled age is fine, but it could cause problems in the future. Think about it, every time someone has a birthday, you will have to update their age in the database. If you have 1,000 patients, this can become a pain and cause an unnecessary waste of time. Age itself is variable. You are better off using a fixed value. Date of birth is just that, because it is something that never changes. Having the date of birth entered will allow you to determine age via MySQL’s functions that perform such simple arithmetic. MySQL can simply use the current date and compare it to the date of birth to generate the patient’s age. Any age changes will display automatically, and you will not have to worry about making manual changes. We will call our birth date column birth. We should also add a column for the date a patient died. We will call that one death.
The use of date of birth as a column shows how important it is to set up your tables properly from the start. Having date of birth in the table instead of just age serves other purposes too. For example, date of birth can tell you when a patient’s birthday is coming up. This helps you improve customer service and lets you plan birthday parties and the like.
The columns we have so far seem sufficient enough. It is time to begin creating the database table.
You can create a table in MySQL using the CREATE TABLE statement. When you do this, you specify your table’s layout in terms of its columns, etc. Here is our example:
mysql> CREATE TABLE patient (name VARCHAR(30), relative VARCHAR(30),
-> hometown VARCHAR(30), gender CHAR(1), birth DATE, death DATE);
Here you see our table setup. We use VARCHAR for name, relative, and hometown columns because these values will vary in length. As for the lengths of those columns, you can choose a value between 1 and 65535. We used 30 here, which should be good enough to accommodate the names of people and towns. You will be able to change the length of the fields at a later date if necessary using the ALTER TABLE statement.
For gender, we are using “m” or “f” as descriptors, so we enter 1 for the length there. Lastly, we use the DATE data type for our birth and death columns.
How to Show Database Tables
We want to see the tables in our database. Once again, that is done via the SHOW TABLES statement.
mysql> SHOW TABLES;
| Tables in devdata |
| patient |
There you can see our newly created patient table within the devdata database.
How to Describe Database Tables
We now want to see what our patient table looks like. This is done with the DESCRIBE statement:
mysql> DESCRIBE patient;
| Field | Type | Null | Key | Default | Extra |
| name | varchar(30) | YES | | NULL | |
| relative | varchar(30) | YES | | NULL | |
| home | varchar(30) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
There you see the details of the layout of our new patient table.
As you can see, it is not too difficult to create a database table in MySQL. You just need to plan out its structure so you can make the most out of your data. Once you have the structure set, you can use the SHOW TABLES and DESCRIBE statements to verify the table is how you want it.