How to MySQL- Create table & Insert data

MySQL database system contains one or more objects called tables. A table is a collection of related data held in a structured format within a database. It consists of columns, and rows. In this guide, we are learning how to create table & Insert data in it.


Create MySQL Table

The CREATE TABLE statement is used to create a table in MySQL.


     CREATE TABLE table_name ( 
       column1 datatype [ NULL | NOT NULL ],
       column2 datatype [ NULL | NOT NULL ],

Data types specify what the type of data can be for that particular column. If a column called “fastname”, is to be used to hold names, then that particular column should have a “varchar” (variable-length character) data type.


Here are the MySQL Data types:

Data typeDescription
CHARACTER(n)Character string. Fixed-length n
VARCHAR(n) Character string. Variable length. Maximum length n
BINARY(n)Binary string. Fixed-length n
BOOLEANStores TRUE or FALSE values
SMALLINTInteger numerical (no decimal). Precision 5
INTEGERInteger numerical (no decimal). Precision 10
BIGINTInteger numerical (no decimal). Precision 19
DECIMAL(p,s)Exact numerical, precision p, scale s. Example: decimal(5,2) is a number that has 3 digits before the decimal and 2 digits after the decimal
NUMERIC(p,s)Exact numerical, precision p, scale s. (Same as DECIMAL)
REALApproximate numerical, mantissa precision 7
FLOATApproximate numerical, mantissa precision 16
DOUBLE PRECISIONApproximate numerical, mantissa precision 16
DATEStores year, month, and day values
TIMEStores hour, minute, and second values
TIMESTAMPStores year, month, day, hour, minute, and second values
INTERVALComposed of a number of integer fields, representing a period of time, depending on the type of interval
ARRAYA set-length and ordered collection of elements
MULTISETA variable-length and unordered collection of elements
XMLStores XML data


We will create a table named “contacts”, with four columns: “id”, “firstname”, “lastname” and “email”.


MySQL Create table & insert data


Issue the MySQL show tables command to list the tables in the current database.



In our example, MySQL shows the following output:


MySQL Create table & insert data

To see the structure of a table, use DESCRIBE function.


     DESCRIBE table_name;

The resulting data from the MySQL CLI looks like this for the table contacts.


Insert data into MySQL Table

The insert statement is used to insert or add a row of data into the table.

     INSERT INTO table_name (column1,column2,column3,...)
     VALUES (value1,value2,value3,...);


In its simplest form, the syntax for the INSERT statement when inserting a single record using the VALUES keyword in MySQL.


MySQL Create table & insert data


To insert multiple records to our table, use following syntax.

     INSERT INTO table_name (column1,column2,column3,...)
     VALUES (value1,value2,value3,...), 


The following statement will insert 6 records into our table “contacts” using the VALUES keyword.

Sample Image

MySQL Create table & insert data


In next tutorial, we’ll learn how to query data from a table and output it.

Anwar Yakkiparamban

Anwar Yakkiparamban is the founder of Lauyou Learning. Prior to Lauyou learning, Anwar worked at ARD Engineering & Development, Qatar. He holds bachelor degree in Electronics and Communication Engineering from Govt. Engineering College Idukki.

You may also like...