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.

 

Syntax
     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”.

Query

MySQL Create table & insert data

 

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

Syntax
     SHOW TABLES;

 

In our example, MySQL shows the following output:

Output

MySQL Create table & insert data

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

 

Syntax
     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.

Syntax
     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.

INSERT Query

MySQL Create table & insert data

 

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

Syntax
     INSERT INTO table_name (column1,column2,column3,...)
     VALUES (value1,value2,value3,...), 
            (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...