DML (Data Manipulation Language)
DML is a sub-language of SQL. It is used to manipulate the data stored in a table.
DML provides the following statements for data manipulation:
INSERT statement is used to insert a new record into a table.
Syntax: INSERT INTO <TABLE_NAME>[(COLUMN1, COLUMN2, ... N)] VALUES(VALUE1, VALUE2 ... N);
Consider a table 'EMP' having columns named EMPNO, NAME, SAL and JOB. We can used the below INSERT statement to insert a new record to it.
INSERT INTO EMP VALUES('1','ALAN',5000,'CEO');
INSERT statement can be used without the column names, when data is inserted into all the columns of the table. The values to be inserted in each column should come in the same sequence in the insert statement as are the columns in the table.
The below example uses column name, since the columns are not in the same sequence as in the table. Also all columns are not used. The column left out will be filled with NULL.
INSERT INTO EMP(NAME, JOB, SAL) VALUES('NICK','MGR',3000);
The below example uses NULL keyword to insert null value into the JOB column.
Update statement is used to update an existing record in a table.
Syntax: UPDATE <TABLE_NAME> SET <COLUMN_NAME>=VALUE[, <COLUMN_NAME> VALUE ... N] [WHERE <CONDITION>];
If the where clause of omitted, the statement will update all the values in the particular column.
Example: UPDATE EMP SET SAL=15000 WHERE EMPNO=1 UPDATE EMP SET SAL=12000, JOB='VP' WHERE EMPNO=NULL
Note: IS NULL is used for assignment and =NULL is used to recognize.
Example: UPDATE EMP SET JOB=NULL WHERE EMPNO IS NULL;
Delete Statement is used to delete records from a table.
Syntax: DELETE FROM <TABLE_NAME> [WHERE <CONDITION>];
Examples: DELETE FROM EMP WHERE EMPNO=1;
Below statement will delete all records from the table.
DELETE FROM EMP;