Home arrow MySQL arrow MySQL: Create, Show, and Describe Database Tables

MySQL: Create, Show, and Describe Database Tables

This article will describe how to work with database tables. Specifically, you will learn how to create, show, and describe database tables.

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

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.

Conclusion

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.


 
 
>>> 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: