DML COMMANDS

DML COMMANDS

Once the schema or structure of the table is created, values can be added to the table.

The DML commands consist of inserting, deleting and updating rows into the table.

(i) INSERT command

The INSERT command helps to add new data to the database or add new records to the table. The command is used as follows:

INSERT INTO <table-name> [column-list] VALUES (values);

INSERT INTO Student (Admno, Name, Gender, Age, Place)

VALUES (100,’ Ashish’,’ M’, 17,’ Chennai’);

INSERT INTO Student (Admno, Name, Gender, Age, Place)

VALUES (102, ‘Adarsh’, ‘M’, 18, ‘Delhi’);

Two new records are added to the table as shown below:

The order of values must match the order of columns in the CREATE TABLE command. Specifying the column names is optional if data is to be added for all columns. The command to add values into the student table can also be used in the following way:

INSERT INTO Student VALUES ( 102, ‘Akshith’, ‘M’, ‘17,’ ‘Bangalore’);

The above command inserts the record into the student table.

To add data to only some columns in a record by specifying the column name and their data, it can be done by:

INSERT INTO Student(Admno, Name, Place) VALUES (103, ‘Ayush’, ‘Delhi’);

The above command adds the following record with default values of ‘M’ for Gender and Age as 18.

INSERT INTO Student (Admno, Name, Place) VALUES (104, ‘Abinandh’, ‘Chennai’);

The student table will have the following data:

The fields that are not given in the INSERT command will take default values, if it is defined for them, otherwise NULL value will be stored.

(ii) DELETE COMMAND

The DELETE command permanently removes one or more records from the table. It removes the entire row, not individual fields of the row, so no field argument is needed. The DELETE command is used as follows :

DELETE FROM table-name WHERE condition;

For example to delete the record whose admission number is 104 the command is given as follows:

DELETE FROM Student WHERE Admno=104;

The following record is deleted from the Student table.

To delete all the rows of the table, the command is used as :

DELETE * FROM Student;

The table will be empty now and could be destroyed using the DROP command (Discussed in section 12.7.4.3).

(iii) UPDATE COMMAND

The UPDATE command updates some or all data values in a database. It can update one or more records in a table. The UPDATE command specifies the rows to be changed using the WHERE clause and the new data using the SET keyword. The command is used as follows:

UPDATE <table-name> SET column-name = value, column-name = value,… WHERE condition;

For example to update the following fields:

UPDATE Student SET Age = 20 WHERE Place = “Bangalore”;

The above command will change the age to 20 for those students whose place is “Bangalore”.

The table will be as updated as below:

To update multiple fields, multiple field assignment can be specified with the SET clause separated by comma. For example to update multiple fields in the Student table, the command is given as:

UPDATE Student SET Age=18, Place = ‘Chennai’ WHERE Admno = 102;

The above command modifies the record in the following way.