SQL Data Manipulation involves modifying data within the database using SQL commands. These operations typically consist of inserting, updating, deleting, and selecting data. The SQL commands used for these tasks are part of the Data Manipulation Language (DML). Here are the key DML commands:
1. INSERT INTO – Adding New Data
The INSERT INTO statement is used to add new rows of data into a table.
Basic Syntax:
INSERT INTO employees (first_name, last_name, department_id, salary)
VALUES ('John', 'Doe', 1, 50000);
This query inserts a new employee named John Doe into the
employeestable with a specifieddepartment_idandsalary.
Inserting Multiple Rows:
INSERT INTO employees (first_name, last_name, department_id, salary)
VALUES
('Jane', 'Smith', 2, 55000),
('Peter', 'Johnson', 1, 60000);
This query inserts two rows of data at once into the employees table.
2. UPDATE – Modifying Existing Data
The UPDATE statement is used to modify existing records in a table. It updates one or more columns for rows that match a specified condition.
Basic Syntax:
UPDATE employees
SET salary = 60000
WHERE employee_id = 3;
This query updates the salary of the employee with employee_id 3 to 60,000.
Example with Multiple Column Updates:
UPDATE employees
SET department_id = 2, salary = 65000
WHERE employee_id = 4;
This query updates both the department_id and salary for the employee with employee_id 4.
Important Note:
Always use the WHERE clause when performing an UPDATE to prevent updating all rows in the table unintentionally. Without the WHERE clause, all records in the table will be updated.
3. DELETE – Removing Data
The DELETE statement is used to remove one or more rows from a table based on a specified condition.
Basic Syntax:
DELETE FROM employees
WHERE employee_id = 5;
This query deletes the employee with employee_id 5 from the employees table.
Deleting All Rows:
If you want to delete all rows from a table, you can omit the WHERE clause:
DELETE FROM employees;
This will remove all rows from the employees table, but the table structure remains intact.
4. SELECT – Retrieving Data
The SELECT statement is used to retrieve data from one or more tables.
Basic Syntax:
SELECT first_name, last_name, salary
FROM employees
WHERE department_id = 2
ORDER BY salary DESC;
