SQLite Cheat Sheet

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;Code language: SQL (Structured Query Language) (sql)

Optimize the database:

VACUUMCode language: SQL (Structured Query Language) (sql)

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,
   ...
);Code language: SQL (Structured Query Language) (sql)

Rename a table:

ALTER TABLE table_name RENAME TO new_name;Code language: SQL (Structured Query Language) (sql)

Add a new column to a table:

ALTER TABLE table ADD COLUMN column_definition;Code language: SQL (Structured Query Language) (sql)

Drop an existing column in a table:

ALTER TABLE table DROP COLUMN column_name;Code language: SQL (Structured Query Language) (sql)

Drop a table and its data:

DROP TABLE [IF EXISTS] table_name;Code language: SQL (Structured Query Language) (sql)

Managing indexes

Creating an index

CREATE [UNIQUE] INDEX index_name
ON table_name (c1,c2,...)Code language: SQL (Structured Query Language) (sql)

Delete an index:

DROP INDEX index_name;Code language: SQL (Structured Query Language) (sql)

Create an expression index:

CREATE INDEX index_name ON table_name(expression);Code language: SQL (Structured Query Language) (sql)

Querying Data

Query all data from a table

SELECT * FROM table_name;Code language: SQL (Structured Query Language) (sql)

Query data from the specified column of a table:

SELECT c1, c2
FROM table_name;Code language: SQL (Structured Query Language) (sql)

Query unique rows

SELECT DISTINCT (c1)
FROM table_name;Code language: SQL (Structured Query Language) (sql)

Query rows that match a condition using a WHERE clause.

SELECT *
FROM table_name
WHERE condition;Code language: SQL (Structured Query Language) (sql)

Rename the column in the query’s output:

SELECT c1 AS new_name
FROM table_name;
Code language: SQL (Structured Query Language) (sql)

Query data from multiple tables using inner join and left join

SELECT * 
FROM table_name_1
INNER JOIN table_name_2 ON condition;Code language: SQL (Structured Query Language) (sql)

SELECT * 
FROM table_name_1
LEFT JOIN table_name_2 ON condition;Code language: SQL (Structured Query Language) (sql)

Count rows returned by a query:

SELECT COUNT (*)
FROM table_name;Code language: SQL (Structured Query Language) (sql)

Sort rows using the ORDER BY clause:

SELECT c1, c2
FROM table_name
ORDER BY c1 ASC [DESC], c2 ASC [DESC],...;Code language: SQL (Structured Query Language) (sql)

Group rows using the GROUP BY clause.

SELECT *
FROM table_name
GROUP BY c1, c2, ...;Code language: SQL (Structured Query Language) (sql)

Filter group of rows using HAVING clause.

SELECT c1, aggregate(c2)
FROM table_name
GROUP BY c1
HAVING condition;Code language: SQL (Structured Query Language) (sql)

Changing Data

Insert a row into a table:

INSERT INTO table_name(column1,column2,...)
VALUES(value_1,value_2,...);Code language: SQL (Structured Query Language) (sql)

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,...)...
Code language: SQL (Structured Query Language) (sql)

Update all rows in a table:

UPDATE table_name
SET c1 = v1,
    ...Code language: SQL (Structured Query Language) (sql)

Update rows that match with a condition:

UPDATE table_name
SET c1 = v1,
    ...
WHERE condition;Code language: SQL (Structured Query Language) (sql)

Delete all rows in a table

DELETE FROM table;Code language: SQL (Structured Query Language) (sql)

Delete rows specified by a condition:

DELETE FROM table
WHERE condition;Code language: SQL (Structured Query Language) (sql)

Search

Search using the LIKE operator:

SELECT * FROM table
WHERE column LIKE '%value%'Code language: SQL (Structured Query Language) (sql)

Search using full-text search:

SELECT * 
FROM table
WHERE table MATCH 'search_query';Code language: SQL (Structured Query Language) (sql)