Here’s a concise cheat sheet for operating SQLite3, covering basic commands, common functions, and query examples.

SQLite3 Cheat Sheet

Starting SQLite3

sqlite3 database_name.db

• Creates the database if it doesn’t exist or opens it if it does.

Basic Commands

• .help
  Displays a list of available SQLite3 commands.

• .tables
  Lists all tables in the current database.

• **.schema [table_name]**
  Shows the SQL CREATE statement for the specified table.

• .exit
  Exits the SQLite3 shell.

• .databases
  Lists all databases attached to the current session.

Creating a Table

CREATE TABLE table_name (
    column1_name column1_datatype,
    column2_name column2_datatype,
    ...
);

Example:

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER
);

Inserting Data

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

Example:

INSERT INTO users (name, age) 
VALUES ('Alice', 30);

Querying Data

• Basic Select Query:

SELECT * FROM table_name;

Example:

SELECT * FROM users;

• With Conditions:

SELECT * FROM table_name WHERE condition;

Example:

SELECT * FROM users WHERE age > 25;

• Select Specific Columns:

SELECT column1, column2 FROM table_name;

Example:

SELECT name, age FROM users;

• Order By:

SELECT * FROM table_name ORDER BY column_name [ASC|DESC];

Example:

SELECT * FROM users ORDER BY age DESC;

• Limit Results:

SELECT * FROM table_name LIMIT n;

Example:

SELECT * FROM users LIMIT 5;

Updating Data

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

Example:

UPDATE users SET age = 31 WHERE name = 'Alice';

Deleting Data

DELETE FROM table_name WHERE condition;

Example:

DELETE FROM users WHERE name = 'Alice';

Joining Tables

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

Example:

SELECT orders.id, users.name 
FROM orders 
JOIN users ON orders.user_id = users.id;

Aggregate Functions

• Count:

SELECT COUNT(*) FROM table_name;

• Sum:

SELECT SUM(column_name) FROM table_name;

• Average:

SELECT AVG(column_name) FROM table_name;

• Min/Max:

SELECT MIN(column_name) FROM table_name;
SELECT MAX(column_name) FROM table_name;

Grouping Results

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

Example:

SELECT age, COUNT(*) 
FROM users 
GROUP BY age;

Transactions

• Start a Transaction:

BEGIN TRANSACTION;

• Commit a Transaction:

COMMIT;

• Rollback a Transaction:

ROLLBACK;

Useful .commands

• .mode [mode]
  Set output mode (e.g., csv, column, json, insert, line, list, tabs, table).

• **.output file_name**
  Redirects query output to a file.

• **.read file_name**
  Executes SQL commands from a file.

• .import filename tablename
  Imports data from a CSV file into the specified table.

• **.backup [database_name]**
  Creates a backup of the SQLite database.

Importing Data

1. Create a table that matches the CSV structure.
2. Use the .mode and .import commands.

.mode csv
.import data.csv table_name

Conclusion

This should give you a solid foundation for working with SQLite3. Don’t forget to refer to the SQLite Documentation for more details and advanced features!