SQLite cheat sheet lists the most common SQLite statements that help you work with SQLite more quickly and effectively.
Managing databases
Attach another database to the current database connection:
1 | ATTACH DATABASE file_name AS database_name; |
Optimize the database:
1 | VACUUM |
Managing Tables
1 2 3 4 5 6 | CREATE TABLE [IF NOT EXISTS] table( primary_key INTEGER PRIMARY KEY, column_name type NOT NULL, column_name type NULL, ... ); |
Rename a table:
1 | ALTER TABLE table_name RENAME TO new_name; |
Add a new column to a table:
1 | ALTER TABLE table ADD COLUMN column_definition; |
Drop an existing column in a table:
1 | ALTER TABLE table DROP COLUMN column_name; |
Drop a table and its data:
1 | DROP TABLE [IF EXISTS] table_name; |
Managing indexes
1 2 | CREATE [UNIQUE] INDEX index_name ON table_name (c1,c2,...) |
Delete an index:
1 | DROP INDEX index_name; |
Create an expression index:
1 | CREATE INDEX index_name ON table_name(expression); |
Querying Data
Query all data from a table
1 | SELECT * FROM table_name; |
Query data from the specified column of a table:
1 2 | SELECT c1, c2 FROM table_name; |
Query unique rows
1 2 | SELECT DISTINCT (c1) FROM table_name; |
Query rows that match a condition using a WHERE clause.
1 2 3 | SELECT * FROM table_name WHERE condition; |
Rename column in the query’s output:
1 2 | SELECT c1 AS new_name FROM table_name; |
Query data from multiple tables using inner join, left join
1 2 3 | SELECT * FROM table_name_1 INNER JOIN table_name_2 ON condition; |
1 2 3 | SELECT * FROM table_name_1 LEFT JOIN table_name_2 ON condition; |
Count rows returned by a query:
1 2 | SELECT COUNT (*) FROM table_name; |
Sort rows using ORDER BY clause:
1 2 3 | SELECT c1, c2 FROM table_name ORDER BY c1 ASC [DESC], c2 ASC [DESC],...; |
Group rows using GROUP BY clause.
1 2 3 | SELECT * FROM table_name GROUP BY c1, c2, ...; |
Filter group of rows using HAVING clause.
1 2 3 4 | SELECT c1, aggregate(c2) FROM table_name GROUP BY c1 HAVING condition; |
Changing Data
1 2 | INSERT INTO table_name(column1,column2,...) VALUES(value_1,value_2,...); |
Insert multiple rows into a table in a single statement:
1 2 3 4 | INSERT INTO table_name(column1,column2,...) VALUES(value_1,value_2,...), (value_1,value_2,...), (value_1,value_2,...)... |
1 2 3 | UPDATE table_name SET c1 = v1, ... |
Update rows that match with a condition:
1 2 3 4 | UPDATE table_name SET c1 = v1, ... WHERE condition; |
1 | DELETE FROM table; |
Delete rows specified by a condition:
1 2 | DELETE FROM table WHERE condition; |
Search
Search using LIKE operator:
1 2 | SELECT * FROM table WHERE column LIKE '%value%' |
Search using full-text search:
1 2 3 | SELECT * FROM table WHERE table MATCH 'search_query'; |