SQL – Data Manipulation Language

In this post we will discuss about Data Manipulation language. Before proceeding further we suggest you read following posts:

• SQL – Structured Query Language
• SQL – Data Definition Language
• SQL – Table DDL Commands

Data Manipulation language (DML) is used to manipulate data within a table. Data manipulation means:

• Retrieval of data from the database
• Insertion of new data into the database
• Deletion of data from the database
• Modification of data in the database

To achieve this, there are four basic DML commands:

• SELECT
• INSERT
• UPDATE
• DELETE

Let’s discuss these one by one.

SELECT

The Select statement allows viewing the contents of the table in number of ways. You can select all or few columns based on your requirement.
Let’s take example of Student table having columns: Student_Id, Student_Name, Address, Father_Name, Gender.

Table: Student

• To select all data in a table, we use the following:
  SELECT * FROM Student;
  It will show all the data stored in the table.
• To select few columns of a table, the  following command is used:
  SELECT column_name1, column_name2 from table_name;
  Let’s take an example, to select Student_Name, Address from Student table; we will use:
  SELECT Student_Name, Address FROM Student;
  It will give us the following result:
  
• To filter data in a table, a WHERE clause condition is used in select statement. The syntax of command is:
  SELECT * from table_name WHERE condition;
  Let’s take an example where we want to select the male students only. The select statement will be:
  SELECT * from Student WHERE Gender = ‘Male’;
  This will show all the male students . No female student will be returned in the result.
  

INSERT

The INSERT statement is used to add new records/rows into the database table. The syntax to insert the data is:
INSERT INTO table_name (column_name1, column_name2,…) VALUES (column_value1, column_value2..);

Let’s insert data into our Student table. The insert statement will be:
INSERT INTO Student (Student_Id, Student_Name, Address, Father_Name, Gender) VALUES (4, ‘Akanksha Sharma’, ‘Shastri Nagar’, ‘Mohit Sharma’,’Female’);

Above command will insert record of student named Akanksha Sharma into the table.

UPDATE

The UPDATE statement is used to update an existing record in the table. The syntax of UPDATE statement is:

UPDATE table_name
SET column_name1 = column _value1, column_name2 = column _value2….
WHERE [condition];

The condition is used if we want to update a range of rows based on our requirement.

Let’s update some records in our Student table.

UPDATE Student set Gender=’Male’ where Student_Name = ‘Akanksha Sharma’;

Above command will update the value of Gender column of all records of Akanksha Sharma to ‘Male’.

DELETE

The DELETE statement is used to delete rows from table. The syntax of DELETE command is:
DELETE FROM table_name WHERE [condition];

It will delete all the rows from table which met the specified condition. Let’s take example of Student table:
DELETE FROM Student Where Student_Id = 2;

This will delete record from student table whose Student_Id is 2.

Note that atmost care should be taken while running the DELETE command as it removes data from table.

If we run delete command without using the condition, all the records from database will be removed:
DELETE FROM Student;

Download as PDF

Read next:  SQL – Data Control Language ››

« Back to Course page