Data Manipulation Language (DML) statements or commands are used for managing data within tables. Some commands of DML are:
Some commands of DML are:
- SELECT – retrieve data from the a database
- INSERT – insert data into a table
- UPDATE – updates existing data within a table
- DELETE – deletes all records from a table, the space for the records remain
- MERGE – UPSERT operation (insert or update)
- CALL – call a PL/SQL or Java subprogram
- LOCK TABLE – control concurrency
Insert :
The insert statement is used to add new row to a table.
INSERT INTO <table name> VALUES (<value 1>, ... <value n>);
Example:
INSERT INTO STUDENT VALUES (1001,‘Ram’);
The inserted values must match the table structure exactly in the number of attributes and the data type of each attribute. Character type values are always enclosed in single quotes; number values are never in quotes; date values are often (but not always) in the format ‘yyyy-mm-dd’ (for example, ‘2006-11- 30’).
UPDATE :
The update statement is used to change values that are already in a table.
UPDATE <table name> SET <attribute> = <expression> WHERE <condition>;
Example:
UPDATE STUDENT SET Name = ‘Amar’ WHERE StudID=1001;
The update expression can be a constant, any computed value, or even the result of a SELECT statement that returns a single row and a single column.
DELETE :
The delete statement deletes row(s) from a table.
DELETE FROM <table name> WHERE <condition>;
Example:
DELETE FROM STUDENT WHERE StudID=1001;
If the WHERE clause is omitted, then every row of the table is deleted that matches with the specified condition.
SELECT :
The SELECT statement is used to form queries for extracting information out of the database.
SELECT <attribute>, ….., <attribute n> FROM <table name>;
Example:
SELECT StudID, Name FROM STUDENT;
Apart from these statements, some statements are also used to control the transaction made by DML statements. The commands used for this purpose are called Transaction Control (TCL) statements. It allows statements to be grouped together into logical transactions. Some commands of TCL are:
- COMMIT – save work done.
- SAVEPOINT – identify a point in a transaction to which you can later roll back.
- ROLLBACK – restore database to original since the last COMMIT.
