Modifying Data ​

In relational databases, the fundamental operations are known as CRUD: Create, Retrieve, Update, and Delete. We have already covered the details of using the SELECT statement for querying data. Now, let's discuss the SQL statements corresponding to inserting, updating, and deleting records:

1. INSERT: Inserting New Records ​

The INSERT statement is used to add new records to a table. The basic syntax is:

INSERT INTO <table_name> (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Example: To insert a new student into the students table:

INSERT INTO students (class_id, name, gender, score)
VALUES (1, 'John Doe', 'M', 85);

2. UPDATE: Updating Existing Records ​

The UPDATE statement is used to modify existing records in a table. The syntax is as follows:

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

Example: To update the score of a student with a specific id:

UPDATE students
SET score = 90
WHERE id = 1;

Important: Always include a WHERE clause to specify which records to update; otherwise, all records in the table will be modified.

3. DELETE: Deleting Existing Records ​

The DELETE statement is used to remove records from a table. The basic syntax is:

DELETE FROM <table_name>
WHERE <condition>;

Example: To delete a student with a specific id:

DELETE FROM students
WHERE id = 1;

Important: Like the UPDATE statement, always use a WHERE clause to avoid deleting all records in the table.

Summary ​

• INSERT is used to add new records to a table.
• UPDATE modifies existing records based on a condition.
• DELETE removes records from a table based on a condition.

Understanding these three operations is crucial for effectively managing data in a relational database.