SQLite Cheat Sheet

You organize your data. Now organize your savings.

Set savings goals, track your progress, and stay on course.

Get Savigo for iPhone ›

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:

ATTACH DATABASE file_name AS database_name;Copy

Optimize the database:

VACUUMCopy

Managing Tables #

Create a new table:

CREATE TABLE [IF NOT EXISTS] table(
   primary_key INTEGER PRIMARY KEY,
   column_name type NOT NULL,
   column_name type NULL,
   ...
);Copy

Rename a table:

ALTER TABLE table_name RENAME TO new_name;Copy

Add a new column to a table:

ALTER TABLE table ADD COLUMN column_definition;Copy

Drop an existing column in a table:

ALTER TABLE table DROP COLUMN column_name;Copy

Drop a table and its data:

DROP TABLE [IF EXISTS] table_name;Copy

Managing indexes #

Creating an index

CREATE [UNIQUE] INDEX index_name
ON table_name (c1,c2,...)Copy

Delete an index:

DROP INDEX index_name;Copy

Create an expression index:

CREATE INDEX index_name ON table_name(expression);Copy

Querying Data #

Query all data from a table

SELECT * FROM table_name;Copy

Query data from the specified column of a table:

SELECT c1, c2
FROM table_name;Copy

Query unique rows

SELECT DISTINCT (c1)
FROM table_name;Copy

Query rows that match a condition using a WHERE clause.

SELECT *
FROM table_name
WHERE condition;Copy

Rename the column in the query’s output:

SELECT c1 AS new_name
FROM table_name;Copy

Query data from multiple tables using inner join and left join

SELECT * 
FROM table_name_1
INNER JOIN table_name_2 ON condition;Copy

SELECT * 
FROM table_name_1
LEFT JOIN table_name_2 ON condition;Copy

Count rows returned by a query:

SELECT COUNT (*)
FROM table_name;Copy

Sort rows using the ORDER BY clause:

SELECT c1, c2
FROM table_name
ORDER BY c1 ASC [DESC], c2 ASC [DESC],...;Copy

Group rows using the GROUP BY clause.

SELECT *
FROM table_name
GROUP BY c1, c2, ...;Copy

Filter group of rows using HAVING clause.

SELECT c1, aggregate(c2)
FROM table_name
GROUP BY c1
HAVING condition;Copy

Changing Data #

Insert a row into a table:

INSERT INTO table_name(column1,column2,...)
VALUES(value_1,value_2,...);Copy

Insert multiple rows into a table in a single statement:

INSERT INTO table_name(column1,column2,...)
VALUES(value_1,value_2,...),
      (value_1,value_2,...),
      (value_1,value_2,...)...Copy

Update all rows in a table:

UPDATE table_name
SET c1 = v1,
    ...Copy

Update rows that match with a condition:

UPDATE table_name
SET c1 = v1,
    ...
WHERE condition;Copy

Delete all rows in a table

DELETE FROM table;Copy

Delete rows specified by a condition:

DELETE FROM table
WHERE condition;Copy

Search #

Search using the LIKE operator:

SELECT * FROM table
WHERE column LIKE '%value%'Copy

Search using full-text search:

SELECT * 
FROM table
WHERE table MATCH 'search_query';Copy