UXDE dot Net

MySQL Tutorial: Insert Query

By -

The table we created in the last tutorial was an empty table with no records. Here is the tutorial of writing query to insert records into that table.

 

1. Insert Records Into The Table

The typical insert query mainly consists of the following clauses:

  • INSERT
  • INTO
  • VALUES

Its broken into two parts;

  1. The first part comes after INSERT INTO contains the table name and names of columns in which the value is to be added.
  2. The second part comes after VALUES contains the new values.

Points to be kept in  mind:

  • VARCHAR and DATETIME value must always be enclosed in single inverted commas.
  • Values must be given in round brackets otherwise it will be a syntax error.
  • Column names and values must be in order, as the values correspond to the columns respectively.
  • Writing MySQL Keywords and clauses in caps is a convention to make the query statement legible.
  • Do not give value to the auto-increment column, as column id is auto-increment in the table employee_data.
  • Giving duplicate value will create a primary key constraint error.
  • now() is a MySQL function which returns current DATETIME value.
  • You may not give column names. For that case give the values in order of the columns in the database table, as shown in example 2 below.
  • If some column name and its value is missing, it will take its default value or null, depending upon the DB design.

Syntax:

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

Example:

INSERT INTO employee_data first_name, last_name, age, date_registered VALUES (‘Bob’, ‘Samuel’, 23, now())

Result:
1 row effected in 00:00.025

The table is now filled with one record.

id first_name last_name age date_registered
 1  Bob  Samuel  23  2012-09-18 10:56:02

Example 2:

If no column names are given the values must be in order of the columns in the database table.

INSERT INTO employee_data VALUES (‘Dan’, ‘Brown’, 22, now())

Result:
1 row effected in 00:00.025

The table now looks like this.

id first_name last_name age date_registered
 1  Bob  Samuel  23  2012-09-18 10:56:02
 2  Dan  Brown  22  2012-04-18 10:56:02

Example 3:

Advertisement

last_name value is not given. It will be taken as null in this case.

INSERT INTO employee_data first_name, age, date_registered VALUES (‘Bob’, 23, now())

Result:
1 row effected in 00:00.025
The table now looks like this.

id first_name last_name age date_registered
 1  Bob  Samuel  23  2012-09-18 10:56:02
 2  Dan  Brown  22  2012-04-18 10:56:02
 3  Robert  20  2012-04-20 10:56:02

For more MySQL tutorials visit related posts

How to write query  to create database

How to write query to create DB table

How to write query to select data

Farheen Bibi

You can find Farheen on , and .

3 Comments to MySQL Tutorial: Insert Query

Leave a Reply