Data Manipulation
Data Manipulation
Like managing a warehouse — receiving goods (INSERT), adjusting shelf labels (UPDATE), clearing expired products (DELETE), and emptying the entire warehouse (TRUNCATE) — database CRUD operations are the core of daily data management. Learning to operate data safely is more important than learning the operations themselves.
1. Core Concepts
| Concept | Description |
|---|---|
INSERT INTO |
Insert new data into a table, supports single and multi-row insertion |
UPDATE ... SET |
Modify existing data in a table, must use WHERE to limit scope |
DELETE FROM |
Delete specific rows from a table, must use WHERE to limit scope |
TRUNCATE TABLE |
Quickly clear all data from a table, faster than DELETE but more dangerous |
| Safe operation habits | Before executing DELETE/UPDATE, use SELECT to confirm the affected scope |
⚠️ Core Warning: UPDATE and DELETE without WHERE will affect ALL rows in the table! This is the most common catastrophic mistake beginners make.
2. Basic Syntax
INSERT INTO — Insert Data
SQL
-- Insert a single row (specify columns)
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);
-- Insert a single row (all columns, in table creation order)
INSERT INTO table_name
VALUES (value1, value2, value3);
-- Insert multiple rows
INSERT INTO table_name (column1, column2)
VALUES (value1, value2),
(value3, value4),
(value5, value6);
💡 Tip: It's strongly recommended to specify column names. Don't rely on the default column order. If the table structure changes (e.g., new columns are added), INSERT statements without column names may error or insert incorrect data.
UPDATE — Update Data
SQL
-- Update specific rows
UPDATE table_name
SET column1 = new_value1, column2 = new_value2
WHERE condition;
-- Update with expressions
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 1;
💡 Tip: Before executing UPDATE, use SELECT to confirm which rows the WHERE condition matches. For example, first execute
SELECT * FROM employees WHERE department_id = 1; to see how many rows will be affected.
DELETE — Delete Data
SQL
-- Delete specific rows
DELETE FROM table_name
WHERE condition;
-- Delete all rows (row by row, keeps table structure)
DELETE FROM table_name;
💡 Tip: Deleted data can be recovered through transaction rollback (if within a transaction), but cannot be recovered after committing. Build the habit: SELECT first, then DELETE.
TRUNCATE — Clear Table
SQL
-- Clear the entire table (keeps table structure)
TRUNCATE TABLE table_name;
💡 Tip: TRUNCATE in SQLite is equivalent to
DELETE FROM table_name (SQLite doesn't support TRUNCATE syntax). In MySQL/PostgreSQL, TRUNCATE is much faster than DELETE because it doesn't delete row by row and resets the auto-increment ID.
INSERT vs DELETE vs TRUNCATE Comparison
| Operation | Effect | Rollbackable | Resets Auto-increment ID | Speed |
|---|---|---|---|---|
INSERT |
Insert new rows | ✅ | — | — |
DELETE |
Delete row by row (can use WHERE) | ✅ | ❌ | Slow |
TRUNCATE |
Clear entire table | ❌ | ✅ | Fast |
3. Code Examples
Example: Insert Employee and Department Data (Difficulty ⭐)
Insert initial data into the departments and employees tables.
SQL
-- Insert department data first
INSERT INTO departments (name, location)
VALUES ('Engineering', 'New York'),
('Marketing', 'Los Angeles'),
('Finance', 'New York');
-- Then insert employee data
INSERT INTO employees (name, department_id, salary, hire_date)
VALUES ('John', 1, 15000.00, '2023-01-15'),
('Jane', 1, 18000.00, '2022-06-01'),
('Bob', 2, 12000.00, '2023-03-20'),
('Alice', 3, 13000.00, '2021-11-10');
-- Verify insertion results
SELECT * FROM departments;
Output:
TEXT
id name location
-- ----------- ----------
1 Engineering New York
2 Marketing Los Angeles
3 Finance New York
Example: Safely Update Employee Salaries (Difficulty ⭐⭐)
Give all Engineering department employees a 10% raise. Confirm first, then execute.
SQL
-- Step 1: Use SELECT to confirm the affected scope
SELECT name, salary
FROM employees
WHERE department_id = 1;
Before update:
TEXT
name salary
------ --------
John 15000.00
Jane 18000.00
SQL
-- Step 2: Execute the update after confirmation
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 1;
-- Step 3: Verify the update results
SELECT name, salary
FROM employees
WHERE department_id = 1;
After update:
TEXT
name salary
------ --------
John 16500.00
Jane 19800.00
Example: Comparing Delete and Clear Operations (Difficulty ⭐⭐⭐)
Compare the behavior differences between DELETE and TRUNCATE.
SQL
-- Create a temporary test table
CREATE TABLE test_delete (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT
);
-- Insert test data
INSERT INTO test_delete (name) VALUES ('A'), ('B'), ('C');
-- Delete a specific row using DELETE
DELETE FROM test_delete WHERE name = 'B';
SELECT * FROM test_delete;
Output:
TEXT
id name
-- ----
1 A
3 C
SQL
-- Clear remaining data using DELETE
DELETE FROM test_delete;
-- Insert new data and observe auto-increment ID
INSERT INTO test_delete (name) VALUES ('D');
SELECT * FROM test_delete;
Output (note ID starts at 4; auto-increment ID was not reset):
TEXT
id name
-- ----
4 D
💡 In MySQL, if you use
TRUNCATE TABLE test_delete; to clear the table, the ID will restart from 1 when inserting new data.
4. Common Application Scenarios
Scenario 1: Batch Import Data
Batch import from a temporary table or external data source:
SQL
-- Copy high-salary employees to a backup table
INSERT INTO employees_backup (name, department_id, salary, hire_date)
SELECT name, department_id, salary, hire_date
FROM employees
WHERE salary > 15000;
Scenario 2: Conditional Batch Update
Adjust data under specific conditions:
SQL
-- Give employees who joined more than 3 years ago a 5% raise
UPDATE employees
SET salary = salary * 1.05
WHERE hire_date < DATE('now', '-3 years');
❓ FAQ
Q Both DELETE and TRUNCATE can clear a table. Which should I use?
A If you need to clear the entire table and don't care about the auto-increment ID, use TRUNCATE (faster). If you need to delete partial data or want to preserve rollback capability, use DELETE. In daily development, DELETE is safer; TRUNCATE is typically only used when initializing test data.
Q What if I forgot the WHERE condition and updated the entire table?
A If you haven't committed the transaction, immediately ROLLBACK. If already committed, in MySQL you can recover through binlog replay; in SQLite, recovery is very difficult without a backup. So always SELECT to confirm before executing UPDATE/DELETE.
Q What if I don't want to fill in certain columns during INSERT?
A Simply omit them from the INSERT column list. These columns will use their default values (DEFAULT) or NULL. For example,
INSERT INTO employees (name, salary) VALUES ('New Employee', 10000); — department_id and hire_date will be NULL.Q Does SQLite support TRUNCATE?
A No. In SQLite, use
DELETE FROM table_name; instead. The effect is the same, but the auto-increment ID won't reset. To reset the auto-increment ID, execute DELETE FROM sqlite_sequence WHERE name='table_name'; after deletion.📖 Summary
- INSERT INTO is used to insert data; always specify column names; supports inserting multiple rows at once
- UPDATE SET is used to update data; must use WHERE condition, otherwise the entire table will be updated
- DELETE FROM is used to delete data; must use WHERE condition, otherwise the entire table will be deleted
- TRUNCATE TABLE is used to quickly clear an entire table; faster than DELETE but not rollbackable
- Safe habits: Before executing DELETE or UPDATE, use SELECT to confirm the affected scope
- SQLite doesn't support TRUNCATE; use
DELETE FROM table_nameinstead
📝 Exercises
Exercise 1 (⭐)
Insert the following product data into the products table, then query to verify:
| name | category | price | stock |
|---|---|---|---|
| iPhone 15 | Phone | 5999.00 | 100 |
| MacBook Pro | Computer | 12999.00 | 50 |
| AirPods Pro | Accessory | 1899.00 | 200 |
| iPad Air | Tablet | 4799.00 | 80 |
Exercise 2 (⭐⭐)
Complete the following operations, writing a SELECT to confirm before each step:
- Increase the price of all products with stock below 100 by 5%
- Increase the stock of "Accessory" category products by 50
- Delete products priced below 2000
Exercise 3 (⭐⭐⭐)
Simulate a "product delisting" process:
- Copy products with stock = 0 from the products table to a new products_offline table (create the table first)
- Delete these out-of-stock products from the products table
- Verify that the data in both tables is correct
Next Lesson
