Master SQLite: Essential Table, Index, View, Trigger & Transaction Commands

Introduction

SQLite is a lightweight, server‑less database that requires no installation, supports files up to 2 TB, and is ideal for personal development and small‑scale projects.

1. Table Operations

1) Create Table

CREATE TABLE student ( ID INTEGER DEFAULT '1406061' PRIMARY KEY AUTOINCREMENT NOT NULL, NAME NVARCHAR(100) UNIQUE NOT NULL, score INTEGER NOT NULL, time TIMESTAMP NOT NULL );

Tables can be created quickly with tools such as SQLiteAdmin or via the command line.

2) Drop Table

DROP TABLE people;

3) Show Tables

.tables

4) Insert Data

insert into student(ID,NAME,score,time) values(1406063,'任性的90后boy',90,'2020-07-06 12:23:32');

Duplicate primary‑key values cause errors; using AUTOINCREMENT avoids the need to specify the ID.

5) Update Data

update people set score=100 where age=46;

6) Delete Data

delete from people;  -- delete all rows
delete from people where score<20;  -- delete rows with score < 20

7) Query Data

select * from student;                -- all columns
select NAME, score from student;      -- specific columns
select * from people where score<20 and age<40;  -- conditional query
select * from people order by score desc;        -- sort descending
select * from people limit 3 offset 2;           -- pagination
select distinct * from people where score>10;    -- remove duplicates
select NAME, max(age) from people where score>10 group by age;  -- aggregation
select NAME, max(age) from people where score>10 group by age having count(NAME)>1;  -- group filter

Column width can be adjusted with .width 10 20 15 for better display.

2. Index Operations

1) Create Index

create index user on people(score);               -- regular index
create unique index name on people(NAME);        -- unique index
create index pa on people(score,age);            -- composite index

2) View Indexes

SELECT * FROM sqlite_master WHERE type='index';

SQLite creates implicit indexes for primary keys; they improve query speed but are not always necessary for small datasets.

3) Use Index

select * from people indexed by user where score>10;

4) Drop Index

drop index user;

Note: Avoid indexes on very small tables to prevent unnecessary overhead.

3. View Operations

1) Create View

create view name as select NAME from people;

2) Query View

select * from name;

3) Drop View

drop view name;

4. Trigger Operations

Triggers allow automatic actions on table events.

1) Create Trigger

create trigger cf after insert on people begin
  insert into woman(w_ID,NAME,SCORE,date) values(new.ID,'向前插入',100,datetime('now'));
end;

2) Show Triggers

select * from sqlite_master where type='trigger' AND tbl_name='people';

3) Drop Trigger

drop trigger cf;

5. Transaction Operations

Transactions ensure atomic execution of multiple statements.

begin;                                   -- start transaction
insert into people('gf',65,datetime('now'));  -- example operation
rollback;                               -- undo changes
COMMIT;                                 -- save changes
end;                                    -- finish

The above example demonstrates a simple transaction that can be rolled back before committing.

Conclusion

This article covered SQLite’s core features: creating and managing tables, indexes, views, triggers, and transactions, providing a practical reference for developers who need a lightweight, easy‑to‑use relational database.