SQLite notes

Creating a new table

CREATE TABLE table_name (property_1 TYPE, property_2 TYPE);

Create a new table with auto incrementing id

CREATE TABLE table_name (id INTEGER PRIMARY KEY AUTOINCREMENT)

Example:

CREATE TABLE apple_products (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);

Insert into table

INSERT INTO table_name VALUES (value_1, value_2);

Insert into table with auto incrementing id

INSERT INTO table_name (column_name) VALUES (value);

Example:

INSERT INTO apple_products (name) VALUES ("iPhone");

Querying the table

SELECT column_name FROM table_name;

Query all columns in the table

SELECT * FROM table_name;

Order by specific column

SELECT * FROM table_name ORDER BY column_name;

Comparison operator WHERE

SELECT * FROM table_name WHERE comparison;

Example:

SELECT * FROM groceries WHERE aisle > 5 ORDER BY aisle;

Aggregating data

SELECT aggregating_function(column_name) FROM table_name;

Read .sql file into sqlite3

sqlite3 database.db < file.sql