CRUD Operations

CRUD stands for Create, Read, Update, and Delete — the four fundamental operations you perform on data in any database. This lesson covers the SQL statements that implement each operation in SQLite.

Create — INSERT

The INSERT INTO statement adds new rows to a table.

-- Insert a single row with named columns (recommended)
INSERT INTO users (name, email, created_at)
VALUES ('Alice', 'alice@example.com', datetime('now'));

-- Insert multiple rows in one statement
INSERT INTO users (name, email) VALUES
  ('Bob',   'bob@example.com'),
  ('Carol', 'carol@example.com'),
  ('Dave',  'dave@example.com');

INSERT OR REPLACE (also written as REPLACE INTO) deletes a conflicting row and inserts a new one:

INSERT OR REPLACE INTO users (id, name, email)
VALUES (1, 'Alice Updated', 'alice@new.com');

INSERT OR IGNORE silently skips rows that violate a UNIQUE constraint:

INSERT OR IGNORE INTO users (name, email)
VALUES ('Alice', 'alice@example.com');

Read — SELECT

The SELECT statement retrieves rows from one or more tables.

-- Select all columns
SELECT * FROM users;

-- Select specific columns
SELECT id, name, email FROM users;

-- Filter with WHERE
SELECT name, email FROM users WHERE created_at >= date('now', '-30 days');

-- Sort results
SELECT name FROM users ORDER BY name ASC;

-- Limit the number of rows returned
SELECT name FROM users ORDER BY id DESC LIMIT 10;

Update — UPDATE

The UPDATE statement modifies existing rows.

-- Update a specific row
UPDATE users
SET email = 'newemail@example.com'
WHERE id = 1;

-- Update multiple columns at once
UPDATE products
SET price = price * 1.1,
    updated_at = datetime('now')
WHERE category_id = 3;

Always include a WHERE clause in UPDATE statements unless you intentionally want to modify every row in the table.

Delete — DELETE

The DELETE FROM statement removes rows.

-- Delete a specific row
DELETE FROM users WHERE id = 42;

-- Delete rows matching a condition
DELETE FROM sessions WHERE expires_at < datetime('now');

-- Delete ALL rows (use with caution)
DELETE FROM temp_logs;

To delete all rows efficiently and reset the rowid counter, use DELETE FROM without a WHERE clause, or use DROP TABLE and recreate.

Checking the Last Inserted ID

After an INSERT, use last_insert_rowid() to retrieve the rowid of the most recently inserted row:

INSERT INTO orders (customer_id, total) VALUES (5, 99.99);
SELECT last_insert_rowid();

Affected Row Count