Data Manipulation Language

Data Manipulation Language(DML)

The Data Manipulation Language (DML) is used to retrieve, insert and modify database information. DML commands are not auto-commited.These commands will be used by all database users during the routine operation of the database.

Different types of DML commands are:

1.INSERT
2.SELECT
3.UPDATE
4.DELETE
-----------------------------
1.INSERT


The INSERT command in SQL is used to add records into an existing table.

The INSERT command has two types of syntax, i.e. it can be written in two ways, here are the two ways :

SYNTAX

First way is :

INSERT INTO table_name
VALUES (value1, value2, value3,...)

Second way :

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

INSERT Example

P_Id    FirstName     LastName    Address     City
1    Rajesh               Kumar    Dadar            Mumbai
2    Sahil               Shah            Babar Road    New Delhi
3    Harsh               Gupta    PRakash nagar    Hyderabad


Now we want to insert a new row in the "Persons" table.

We use the following SQL statement:

INSERT INTO Persons
VALUES (4,'swetha', 'Menon', 'ANDHERI' , 'MUMBAI')

2.SELECT

 The SELECT statement is a limited form of DML statement in that it can only access data in the database. It cannot manipulate data in the database, although it can operate on the accessed data before returning the results of the query.

SYNTAX:

  SELECT column_name
  FROM table_name
  and
  SELECT*FROM table_name

3.UPDATE

   The UPDATE command can be used to modify the records in the existing table, either in bulk or individually.

SYNTAX:

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

NOTE : As we  mention above that update can modify either a single record or a bulk record. So the where  clause in the update statement helps us to do that. If we use a where clause then whenever the condition in true the record will get updates and if don't use the where clause then all the records are appended.

Ex:

UPDATE EMPLOYEES
SET LAST_NAME = 'Keats'
WHERE SALARY = 8500;

4.DELETE

The DELETE command is used to remove records from an existing table. It can only delete row(s) and not column(s).The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted.

SYNTAX:

DELETE FROM table_name
WHERE some_column=some_value

Ex:
Emp_No      FirstName     LastName       City
1               mahesh            katla         hyderabad
2               ramu                Bandi         guntur

Now we want to delete the person "ramu" in the "EMP" table.

We use the following SQL statement:

DELETE FROM EMP
WHERE FirstName='ramu' AND LastName='Bandi';

0 comments:

Post a Comment