DML (data manipulation language) statements are the SQL commands that deal with manipulating the data in a database. These statements manage data using database schemas.
DML statements allow the addition, modification, and deletion of the data. They control the access to the data as well. The list of the most commonly used DML commands is as follows:
INSERT
UPDATE
SELECT
DELETE
Let's see the uses of all the commands mentioned above.
The INSERT command helps to insert a new value in a database table.
INSERT INTO TableName VALUES();
The UPDATE command helps to update existing data in a table. While updating the data in a database, we have to be very careful because we can update all the rows in a column with the same value unless we use the WHERE clause.
UPDATE TableNameSET Column_1 = 'New_Value'WHEREColumn_1 = 'Old_Value';
The SELECT command helps to retrieve data from a particular table.
SELECTColumn_1 , Column_2FROMTableName
For selecting all the data, we can use the following:
SELECT * FROM TableName
The DELETE command deletes the data from a specific table. Again, we need to be cautious while deleting something from a database table, so we do not accidentally delete the whole table.
DELETE FROM TableNameWHERE Column_Name = 'Value'
To delete all the values from the table, we use the following:
DELETE FROM TableName
Note: It is a best practice to use the primary key in the
WHEREclause. To check what we update or delete, we can switch theUPDATEandDELETEstatements with theSELECTstatement and observe the results.
Let's see the use of the DML statements in the example below:
CREATE DATABASE Educative
GO
USE Educative
GO
CREATE SCHEMA Dept
GO
CREATE TABLE Dept.Department
(
Department_Id INT PRIMARY KEY,
Department_Name VARCHAR(30),
Department_Floor INT
)
GO
INSERT INTO Dept.Department
VALUES
(01,'Human Resource',03),
(02, 'Development',02),
(03, 'Technical Content',01)
GO
SELECT * FROM Dept.Department
GO
UPDATE Dept.Department
SET Department_Name = 'TCE'
WHERE
Department_Floor= 1;
GO
DELETE FROM Dept.Department
WHERE Department_Id = 2
GO
SELECT * FROM Dept.Department
GO
Lines 1–8: We create a database with the name Educative and a database schema called Dept.
Lines 10–16: We create a database table called Department with the columns Department_Id, Department_Name, and Department_Floor.
Lines 18–40: We use DML statements to add and modify the data in the Department table.
Free Resources