MySQL Create Tables

In this tutorial you will learn about the MySQL Create Tables and its application with practical example.

MySQL Create table

MySQL CREATE TABLE is used to create a table within a database.The table creation command requires:

Table Of Contents
  • Name of the table
  • Names of fields
  • Definitions for each field

MySQL storage engine represents each table by following files

File Purpose
table_name.frm Table format (definition) file.
table_name.MYD Data file.
table_name.MYI Index file.

The CREATE TABLE statement Syntax.

Example:

Here few items need explanation:

  • Constraint NOT NULL is used because we do not want this field to be NULL. if we try to create a entry with NULL value then MySQL Engine will through an error.
  • AUTO_INCREMENT tells to MySQL Insert new record with next available incremented value.
  • Constraint PRIMARY KEY is used to define a column as primary key. Multiple columns need to be separated by comma to define a primary key.
  • The UNIQUE constraint used to uniquely identify each record in a table.The UNIQUE and PRIMARY KEY constraints both assures for uniqueness for a field or set of fields. A PRIMARY KEY constraint by default has a UNIQUE constraint defined for it.We can have many UNIQUE constraints for a table, but only one PRIMARY KEY per table.

The statement above will create ’emp_info’ table which have columns like following

Column Name Data Type Size
empID Int
empName Varchar 50
empCode Varchar 50
empSalary Varchar 50
empDept Varchar 50

 

 

In this tutorial we have learn about the MySQL Create Tables and its application with practical example. I hope you will like this tutorial.