Common SQLite Commands

SELECT * FROM summer_notes_v1;                      # select all columns & rows
PRAGMA table_info(summer_notes_v1);                 # list all columns
SELECT * FROM summer_notes_v1 WHERE archived = 1;   # select all rows where archived is 1

Retrieve all columns from a table:

SELECT * FROM table_name;

Retrieve specific columns from a table:

SELECT column1, column2 FROM table_name;

Insert a new record into a table:

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

Update existing records in a table:

UPDATE table_name SET column1 = value1 WHERE condition;

Delete records from a table:

DELETE FROM table_name WHERE condition;

Create a new table:

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

Delete a table and its data:

DROP TABLE table_name;

Add a new column to an existing table:

ALTER TABLE table_name ADD column_name datatype;

Show all tables in the database:

.tables

Get a list of all columns in a specific table:

PRAGMA table_info(table_name);

Filter records based on conditions:

SELECT * FROM table_name WHERE condition;

Sort the result set:

SELECT * FROM table_name ORDER BY column1 ASC|DESC;

Limit the number of records returned:

SELECT * FROM table_name LIMIT number;

Group rows with the same values:

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

Combine rows from two or more tables:

SELECT columns FROM table1 JOIN table2 ON table1.common_column = table2.common_column;