Menu
MySQL

SQL commands for DML

SQL provides several Data Manipulation Language (DML) commands to interact with and manipulate the data in a database. These commands include SELECT, INSERT, UPDATE, and DELETE. Below are the basic SQL commands for data manipulation, along with examples.

 

 

1. SELECT: Retrieve Data from a Table

The SELECT statement is used to query data from one or more tables. You can specify the columns you want to retrieve, apply filters, and sort the results.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column1 ASC|DESC;
 

Example:

Retrieve the first name and last name of employees who were hired after 2020.

SELECT FirstName, LastName
FROM Employees
WHERE HireDate > '2020-01-01'
ORDER BY LastName ASC;

 

 

2. INSERT: Add New Records into a Table

The INSERT statement is used to add new rows of data into a table. You can insert values for all columns or only specific columns.

Syntax (Inserting All Columns):

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
 

Example:

Add a new employee record.

INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate, DepartmentID)
VALUES (1, 'John', 'Doe', '2023-05-10', 101);

 

Syntax (Inserting Specific Columns):

INSERT INTO table_name
VALUES (value1, value2, ...);
 

Example:

Insert data for specific columns only (e.g., ignoring DepartmentID).

INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate)
VALUES (2, 'Jane', 'Smith', '2023-06-15');

 

 

 

3. UPDATE: Modify Existing Data in a Table

The UPDATE statement is used to modify existing records in a table. You can update one or multiple columns, and the WHERE clause specifies which rows should be updated.

Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
 

Example:

Update the hire date of an employee with EmployeeID = 1.

UPDATE Employees
SET HireDate = '2024-01-01'
WHERE EmployeeID = 1;

 

Note:

Always include the WHERE clause when using UPDATE to avoid modifying all rows in the table unintentionally.

 

 

 

4. DELETE: Remove Records from a Table

The DELETE statement is used to remove one or more rows from a table based on a condition.

Syntax:

DELETE FROM table_name
WHERE condition;
 

Example:

Delete an employee record with EmployeeID = 2.

DELETE FROM Employees
WHERE EmployeeID = 2;

 

Note:

  • Like UPDATE, always include a WHERE clause when using DELETE to avoid removing all rows from the table.
  • If you want to remove all rows from a table without deleting the table itself, you can use TRUNCATE instead of DELETE.

 

 

 

5. TRUNCATE: Remove All Rows from a Table

The TRUNCATE command is used to quickly remove all rows from a table, but unlike DELETE, it cannot be rolled back (in most cases).

Syntax:

TRUNCATE TABLE table_name;
 

Example:

Remove all employee records from the table.

TRUNCATE TABLE Employees;

 

 

 

Common Clauses Used in DML Statements

  • WHERE: Used to filter records that meet a certain condition.

Example:

SELECT * FROM Employees WHERE DepartmentID = 101;
 
  • ORDER BY: Used to sort the result set based on one or more columns.

Example:

SELECT * FROM Employees ORDER BY HireDate DESC;
 
  • GROUP BY: Used to group rows that share a property so aggregate functions can be applied to each group.

Example:

SELECT DepartmentID, COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY DepartmentID;
 
  • HAVING: Used to filter groups created by GROUP BY.

Example:

SELECT DepartmentID, COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY DepartmentID
HAVING COUNT(*) > 10;
 

 

 

 

MySQL