Tables in MySQL database

The MySQL database contains tables. In the last chapter, we learned how to create MySQL database by using the Create database command. After creating a database, now we will learn how to create tables in MySQL. The tables are the combination of columns and rows that is used to store data.

Depending on the requirement, complexity and how big is your project, a MySQL database may contain hundreds of tables. As such, tables are the combination of columns and rows, each column in a table is referred to as a field. Where each field can store specified type of data. The data type can be specified at the time of creating a table or you can do this by using the Alter table statement as well, afterwards.

Consider, we have an employee’s table to store information about the employees namely, tbl_employee, that has four columns. These columns are employee_name, employee_age, employee_joindate and employee_salary.

The type of data that these columns can store is self-explanatory e.g. employee_name can store characters, employee_join_date should be able to store dates etc.

Now let us look at MySQL create table syntax.

MySQL create table syntax

MySQL provides Create table command to create tables in its databases.The syntax to use create table statement is:

Create Table table_name(

column1 date_type(length), column2 date_type(length), column3 date_type(length), column4 date_type(length)

Primary key (column name(s))

);

As you can see in the above syntax, the Create table command is followed by the name of the table to be created. The name of the table should follow some naming conventions like prefix as tbl_. Or you can use project abbreviation as a prefix.

After the table name, the columns are given with their respective data types. The column names should also be self-descriptive, according to what it will store. For example, in an employees’ table it may contain employee_name, employee_age, employee_join_date etc. This will help you look through the tables at a later stage or while manipulating data.

You can also see after each column name its data type is specified. You should know what type of data that column will store. For example, the employee_name is supposed to store the name of employees that should be able to store characters (A-Z or a-z). So we should use VARCHAR type with its maximum specified size.

Similarly, the age of employees should be numeric type without the decimal point (if supposed to store in years only). While the salary column should be numeric with the decimal point.

This is how we can create tbl_employee in MySQL test_db database.

Example of creating table in MySQL

Let us look at creating a table in MySQL database. We will create a table, tbl_employee with four columns as follows:

Until now we learned how to create a table in MySQL database. Generally, the tables are created with a unique or primary keys that distinguish each row of data in a table. The next chapter explains creating a table with a primary key.

Also see: MySQL create database