Common SQL Statements in SQLite

[qingquan-li]

0. SQLite common commands

Show Databases

SQLite is a serverless, file-based, and lightweight SQL database engine.
SQLite doesn't have the concept of multiple databases like MySQL or PostgreSQL. Instead, each SQLite database is a standalone file.

# Show the current database file's path
sqlite> .database
main: /database/mydatabase.db r/w

# List the main database and any attached databases
sqlite> .databases
main: /database/mydatabase.db r/w

Show Tables

# Show all tables in the current database
sqlite> .tables

Show Schema

# Show the schema of a table
sqlite> .schema <table_name>

Show Settings

# Show the current settings
sqlite> .show
        echo: off
         eqp: off
     explain: auto
     headers: off
        mode: list
   nullvalue: ""
      output: stdout
colseparator: "|"
rowseparator: "\n"
       stats: off
       width: 
    filename: /database/mydatabase.db

Exit

# Exit the sqlite3 shell
sqlite> .exit

# Or
sqlite> .quit

---

1. Creating a Table

The CREATE TABLE statement is used to create a new table in the database.

CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,
    column2 datatype,
    column3 datatype
);

Example:

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

2. Inserting Data

The INSERT INTO statement is used to insert data into a table.

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

Example:

INSERT INTO users (name, age, email)
VALUES ('John Doe', 30, '[email protected]');

3. Selecting Data

The SELECT statement is used to retrieve data from a table.

SELECT column1, column2 FROM table_name;

Example:

SELECT name, age FROM users;

To select all columns:

SELECT * FROM users;

4. Updating Data

The UPDATE statement is used to modify existing records in a table.

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

Example:

UPDATE users
SET age = 31
WHERE name = 'John Doe';

5. Deleting Data

The DELETE statement is used to delete records from a table.

DELETE FROM table_name WHERE condition;

Example:

DELETE FROM users WHERE age < 20;

6. Filtering Data

The WHERE clause is used to filter records that meet a certain condition.

SELECT column1, column2
FROM table_name
WHERE condition;

Example:

SELECT name, email FROM users WHERE age > 25;

7. Sorting Data

The ORDER BY clause is used to sort the result set by one or more columns.

SELECT column1, column2
FROM table_name
ORDER BY column1 ASC;  -- or DESC for descending order

Example:

SELECT name, age FROM users ORDER BY age DESC;

8. Limiting Results

The LIMIT clause is used to limit the number of rows returned by a query.

SELECT column1, column2
FROM table_name
LIMIT number;

Example:

SELECT * FROM users LIMIT 5;

9. Counting Records

The COUNT() function is used to count the number of rows that match a condition.

SELECT COUNT(*)
FROM table_name
WHERE condition;

Example:

SELECT COUNT(*) FROM users WHERE age > 30;

10. Joining Tables

The JOIN clause is used to combine rows from two or more tables, based on a related column between them.

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

Example:

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

11. Grouping Data

The GROUP BY statement groups rows that have the same values into summary rows.

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

Example:

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

12. Creating an Index

The CREATE INDEX statement is used to create an index on a column to speed up queries.

CREATE INDEX index_name
ON table_name (column1);

Example:

CREATE INDEX idx_users_name ON users (name);

13. Dropping a Table

The DROP TABLE statement is used to delete a table and all of its data.

DROP TABLE table_name;

Example:

DROP TABLE users;

14. Dropping an Index

The DROP INDEX statement is used to remove an index.

DROP INDEX index_name;

Example:

DROP INDEX idx_users_name;