SQLite Cheat Sheet

SQLite is a popular open-source relational database management system that is widely used in various applications. It is a lightweight and self-contained database engine that requires no separate server process or configuration. SQLite is designed to be embedded into applications and is used in a wide range of software, including web browsers, mobile devices, and desktop applications.

One of the key features of SQLite is its simplicity and ease of use. It is a single-file database that can be easily integrated into an application without requiring any complex setup or administration. SQLite supports a wide range of data types, including integers, floating-point numbers, strings, and binary data. It also supports SQL queries and transactions, making it a powerful tool for managing data.

This cheat sheet provides an extensive list of SQLite commands and functions. The first column lists the command or function, and the second column provides a brief description.

Table Creation and Modification

Command	Description
CREATE TABLE table_name (column1 datatype, column2 datatype, ...)	Creates a new table with the specified columns and data types.
ALTER TABLE table_name ADD COLUMN column_name datatype	Adds a new column to an existing table.
ALTER TABLE table_name RENAME TO new_table_name	Renames an existing table.
DROP TABLE table_name	Deletes an existing table.

Data Manipulation

Command	Description
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...)	Inserts a new row into the specified table with the specified values.
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition	Updates one or more rows in the specified table with the specified values.
DELETE FROM table_name WHERE condition	Deletes one or more rows from the specified table that meet the specified condition.
SELECT column1, column2, ... FROM table_name WHERE condition	Retrieves data from the specified table that meets the specified condition.

Data Types

Data Type	Description
NULL	Represents a null value.
INTEGER	Represents a whole number.
REAL	Represents a floating-point number.
TEXT	Represents a string of text.
BLOB	Represents binary data.

Operators

Operator	Description
=	Tests for equality.
<> or !=	Tests for inequality.
<	Tests for less than.
>	Tests for greater than.
<=	Tests for less than or equal to.
>=	Tests for greater than or equal to.
AND	Tests if both conditions are true.
OR	Tests if either condition is true.
NOT	Negates a condition.

Functions

Function	Description
COUNT(column_name)	Returns the number of rows that meet the specified condition.
SUM(column_name)	Returns the sum of the values in the specified column.
AVG(column_name)	Returns the average of the values in the specified column.
MAX(column_name)	Returns the maximum value in the specified column.
MIN(column_name)	Returns the minimum value in the specified column.

Indexes

Command	Description
CREATE INDEX index_name ON table_name (column_name)	Creates an index on the specified column in the specified table.
DROP INDEX index_name	Deletes an existing index.

Transactions

Command	Description
BEGIN TRANSACTION	Starts a new transaction.
COMMIT	Commits the current transaction.
ROLLBACK	Rolls back the current transaction.

References

• SQLite Documentation