CRUD stands for Create, Read, Update, and Delete—the four fundamental operations performed on databases. Each operation corresponds to a specific SQL query, allowing you to interact with and manipulate data within a relational database. Let’s walk through each one.
1. Create (INSERT)
The Create operation refers to inserting new data into a table. In SQL, this is done using the INSERT INTO statement.
Syntax:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
Example:
INSERT INTO employees (first_name, last_name, age)
VALUES ('John', 'Doe', 30);
This adds a new row with the values ‘John’, ‘Doe’, and 30 into the employees table.
2. Read (SELECT)
The Read operation retrieves data from the database. This is accomplished using the SELECT statement. You can retrieve specific columns or all columns from a table.
Syntax:
SELECT column1, column2, ...
FROM table_name;
Example:
SELECT first_name, last_name, age
FROM employees;
This fetches the first_name, last_name, and age columns for all rows in the employees table.
To fetch all columns, you can use the * wildcard:
SELECT * FROM employees;
3. Update (UPDATE)
The Update operation modifies existing data in a table. This is done using the UPDATE statement, which allows you to change the values of one or more columns in a row.
Syntax:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
Example:
UPDATE employees
SET age = 31
WHERE first_name = 'John' AND last_name = 'Doe';
This changes the age of the employee named John Doe to 31. Always use a WHERE clause to specify which rows to update; otherwise, all rows will be updated.
4. Delete (DELETE)
The Delete operation removes data from the database. The DELETE statement deletes rows from a table based on a condition.
Syntax:
DELETE FROM table_name
WHERE condition;
Example:
DELETE FROM employees
WHERE first_name = 'John' AND last_name = 'Doe';
This deletes the row where the first_name is John and the last_name is Doe. Like UPDATE, always use a WHERE clause to avoid deleting all rows.
CRUD Summary:
- Create: Insert new records into a table using INSERT.
- Read: Retrieve data from a table using SELECT.
- Update: Modify existing data in a table using UPDATE.
- Delete: Remove data from a table using DELETE.
These four operations form the backbone of interacting with relational databases, allowing you to manage data effectively.
