Hands on Sqlite3 tutorial with examples for create insert select update delete

Welcome to the tiny world of Sqlite3 where your database is a single file and your mistakes are mostly accidental file copies. This short database tutorial walks through create table insert select update and delete steps with real commands you can copy paste and then feel slightly proud about.

Create a database and a table

Sqlite3 stores everything in a file so creating a database is as thrilling as making a new file. From your shell run the sqlite3 command to open or create a file and then declare a table schema.

sqlite3 mydb.db

Inside the sqlite prompt create a users table

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)

This produces a persistent sqlite database that is perfect for small projects prototypes and tests. No server to babysit and no mysterious background processes to blame.

Insert records and use transactions for speed

To add data use simple insert statements. If you have many rows wrap them in a transaction to avoid slow per row commits.

BEGIN TRANSACTION
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com')
COMMIT

Batch inserts are much faster and let you rollback if you test something wildly wrong. Yes rollback is your friend when you forget a WHERE clause in an earlier query.

Querying data with select

Reading data is done with select statements. Use WHERE to filter and LIMIT when you only want a preview and not an existential crisis.

SELECT * FROM users
SELECT id, name FROM users WHERE email LIKE '%example.com' LIMIT 10

Update and delete without drama

When values change update them carefully. Always check the WHERE clause to avoid surprising mass updates. Deleting is the nuclear option but sometimes necessary.

UPDATE users SET email = 'new@example.com' WHERE id = 1
DELETE FROM users WHERE id = 1

Tip do a select with the same WHERE first to confirm the rows you will affect. Sql mistakes are dramatic but reversible if you used transactions or have a backup.

Enable foreign key support

Sqlite supports foreign keys but they are off by default. Enable them at runtime to maintain integrity.

PRAGMA foreign_keys = ON

Practical tips and checklist

• Back up the database file before big changes since backups are just file copies
• Use transactions for grouped writes to improve performance
• Prefer parameterized queries in application code to avoid SQL injection and silly bugs
• Verify changes with select queries before exiting the sqlite prompt
• Exit the CLI with .exit when you are done

That is the core of CRUD in Sqlite3 create insert select update delete plus a few survival tips. You now have the basics to build a small sqlite database and not wreck it on the first day. Go forth and store some data responsibly.