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