Data Manipulation Language ( DML Commands)
Data Manipulation Commands are used for managing the data within the database objects.
These commands are not auto commit unlike DDL commands.
Types of Data Manipulation Language ( DML Commands) :
1. SELECT :
SELECT Statement is used to retrieve data from the database objects.
Note : SELECT does not performs any manipulation operation on the data , then why it is categorized as DML Command ??
Although SELECT does not perform any manipulation operation , but it is widely used before and after other manipulation commands like insert , update , delete. Hence it is classified as DML command.
Syntax :
To View all Column Information :
SELECT * FROM <TABLENAME>;
To view specific Column Information :
SELECT <col1>, <col2>,….<col N> FROM <TABLENAME>;
To view any particular column ( suppose col3) as first column.
SELECT <col3>, * FROM <TABLENAME>;
2. INSERT :
Insert Statement is used to insert data into the database.
Syntax :
To insert a single row :
INSERT INTO <TABLENAME> VALUES ( <col1 value> <col N value>);
For using above syntax-
It is mandatory to provide values for all the columns in the table.
The values should be in the same order as the columns of the table.
The datatype of the values should be same as that of the column.
The string values should be given in single quotes.
To insert values in particular columns :
INSERT INTO <TABLENAME> (<col1>,<col3>,<col4>) VALUES(<value1> , <value3> , <value4>);
For using above syntax –
It is mandatory to provide values in primary key column other then the desired columns in the syntax.
The datatype of the values should be same as that of the column.
The string values should be given in single quotes.
For the remaining columns for whom value is not defined , null is inserted except primary key column.
INSERT INTO <TABLENAME> VALUES(&COL1, &COL2…….&COL N);
INSERT ALL :
This command is used to insert multiple rows :
Syntax :
INSERT ALL INTO <TABLENAME> (<col1>,<col3>,<col4>) VALUES(<value1> , <value3> , <value4>) INTO <TABLENAME> (<col1>,<col3>,<col4>) VALUES(<value1> , <value3> , <value4>) INTO <TABLENAME> (<col1>,<col2>,<col4>) VALUES(<value1> , <value2> , <value4>) SELECT * FROM DUAL;
INSERT ALL INTO <TABLENAME> VALUES(<value1> , <value3> ,….. <value N>) INTO <TABLENAME> VALUES(<value1> , <value3> ,….. <value N>) INTO <TABLENAME> VALUES(<value1> , <value3> ,….. <value N>) SELECT * FROM DUAL;
INSERT ALL INTO <TABLE 1> (<col1>,<col3>,<col4>) VALUES (<value1> , <value3> , <value4>) INTO <TABLE 1> (<col1>,<col3>,<col4>) VALUES(<value1> , <value3> , <value4>) INTO <TABLE 2> (<col1>,<col2>,<col4>) VALUES(<value1> , <value2> , <value4>) SELECT * FROM DUAL;
INSERT ALL INTO <TABLE 1> VALUES(<value1> , <value3> ,….. <value N>) INTO <TABLE 1> VALUES(<value1> , <value3> ,….. <value N>) INTO <TABLE 2> VALUES(<value1> , <value3> ,….. <value N>) SELECT * FROM DUAL;
3. UPDATE :
This command is used to modify the data of the database.
Syntax :
To update single column:
UPDATE <TABLENAME> SET <COL> = <VALUE> WHERE <CONDITION>;
In above syntax WHERE clause is optional. If it is given , then specific rows meeting the criteria updated , else entire table gets updated. The string values should be given in single quotes.
To update multiple columns :
UPDATE <TABLENAME> SET <COL1> = <VALUE1>, <COL2> = <VALUE2>…. WHERE <CONDITION>;
In above syntax WHERE clause is optional .If it is given , then specific rows meeting the criteria updated , else entire table gets updated. The string values should be given in single quotes.
4. DELETE :
This command is used to delete data from table.
Syntax :
DELETE FROM <TABLENAME> WHERE <CONDITION>;
In above syntax WHERE clause is optional .If it is given , then specific rows meeting the criteria are deleted , else entire table data gets deleted. The string values should be given in single quotes.
