SQLite Cheat Sheet

Here’s a cheat sheet for some common SQLite commands:

Basic Commands

Start SQLite:

sqlite3

Open Database:

.open database_name.db

Exit SQLite:

.exit

SQL Commands

Create Table:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);

Insert Data:

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

Select Data:

SELECT * FROM table_name;

Update Data:

UPDATE table_name SET column1 = value1 WHERE condition;

Delete Data:

DELETE FROM table_name WHERE condition;

Query Examples

Select Distinct Values:

SELECT DISTINCT column FROM table_name;

Filter Rows:

SELECT * FROM table_name WHERE condition;

Order by:

SELECT * FROM table_name ORDER BY column1 ASC, column2 DESC;

Aggregation Functions

SUM():

SELECT SUM(column) FROM table_name;

AVG():

SELECT AVG(column) FROM table_name;

COUNT():

SELECT COUNT(column) FROM table_name;

MIN():

SELECT MIN(column) FROM table_name;

MAX():

SELECT MAX(column) FROM table_name;

Group By

Group and Aggregate:

SELECT column, COUNT(*) FROM table_name GROUP BY column;

Joins

Inner Join:

SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;

Indexes

Create Index:

CREATE INDEX index_name ON table_name (column1, column2, ...);

Transactions

Begin Transaction:

BEGIN;

Commit Transaction:

COMMIT;

Rollback Transaction:

ROLLBACK;

Pragma Commands

List Tables:

PRAGMA table_info(table_name);

Show Database Schema:

.schema

Import and Export Data

Import from CSV:

.mode csv
.import filename.csv table_name

Export to CSV:

.mode csv
.output filename.csv
SELECT * FROM table_name;
.output stdout

These commands cover the basics of working with SQLite databases. For more details and advanced features, refer to the official SQLite documentation.