SQLite Cheat Sheet

I was looking for a SQLite cheat sheet. Then I found this.
I have pushed the cheat sheet to this repository, so that I can use it easily.
I’ll update it as my needs.

SQLite cheat sheet lists the most common SQLite statements that help you work with SQLite more quickly and effectively.

Creating Database:

sqlite3 database_name.db

Managing databases

Attach another database to the current database connection:

ATTACH DATABASE file_name AS database_name;

List names and files of attached databases:

.databases

Optimize the database:

VACCUM

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,  
   ...  
);

Rename a table:

ALTER TABLE table_name RENAME TO new_name;

Add a new column to a table:

ALTER TABLE table ADD COLUMN column_definition;

Drop an existing column in a table:

ALTER TABLE table DROP COLUMN column_name;

Drop a table and its data:

DROP TABLE [IF EXISTS] table_name;

List of tables:

.tables

Managing Indexes

Creating an index:

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

Delete an index:

DROP INDEX index_name;

Create an expression index:

CREATE INDEX index_name ON table_name(expression);

Querying Data

Query all data from a table:

SELECT * FROM table_name;

Query data from the specified column of a table:

SELECT c1, c2  
FROM table_name;

Query unique rows:

SELECT DISTINCT (c1)  
FROM table_name;

Query rows that match a condition using a WHERE clause:

SELECT *  
FROM table_name  
WHERE condition;

Rename column in the query’s output:

SELECT c1 AS new_name  
FROM table_name;

Query data from multiple tables using inner join, left join:

SELECT *   
FROM table_name_1  
INNER JOIN table_name_2 ON condition;

SELECT *  
FROM table_name_1  
LEFT JOIN table_name_2 ON condition;

Count rows returned by a query:

SELECT COUNT (*)  
FROM table_name;

Sort rows using ORDER BY clause:

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

Group rows using GROUP BY clause:

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

Filter group of rows using HAVING clause:

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

Changing Data

Insert a row into a table:

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

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,...)...

Update all rows in a table:

UPDATE table_name  
SET c1 = v1,  
    ...

Update rows that match with a condition:

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

Delete all rows in a table:

DELETE FROM table;

Delete rows specified by a condition:

DELETE FROM table  
WHERE condition;

Search

Search using LIKE operator:

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

Search using full-text search:

SELECT *  
FROM table  
WHERE table MATCH 'search_query';