sqlite3 mydatabase.db

SELECT name FROM sqlite_master WHERE type='table';

.tables

PRAGMA table_info(table_name);

PRAGMA table_info(users);

cid | name  | type    | notnull | dflt_value | pk
----+-------+---------+---------+------------+----
0   | id    | INTEGER | 0       | NULL       | 1
1   | name  | TEXT    | 0       | NULL       | 0
2   | age   | INTEGER | 0       | NULL       | 0

CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
);

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

sqlite> .schema users
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER
);

#修改表名
ALTER TABLE table_name RENAME TO new_table_name;
#新增列
ALTER TABLE table_name ADD COLUMN column_def;

ALTER TABLE users ADD COLUMN email TEXT;
sqlite> .schema users
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER,
    email TEXT
);

DROP TABLE [IF EXISTS] table_name;

DROP TABLE IF EXISTS users;

CREATE TABLE new_table AS SELECT * FROM old_table;

CREATE TABLE new_table AS SELECT * FROM old_table WHERE 0;

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

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

INSERT INFO uesrs (id, name, age, email) VALUES (1, "alice", 25, "a@.com");
sqlite> SELECT * FROM users;
id | name  | age | email
---+-------+-----+--------------------
1  | Alice | 25  | a@.com

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

UPDATE users SET age = 26 WHRER id = 1;
sqlite> SELECT * FROM users;
id | name  | age | email
---+-------+-----+--------------------
1  | Alice | 26  | a@.com

DELETE FROM table_name WHERE condition;
注意：如果不加 WHERE，會(huì)刪除整張表的數(shù)據(jù)

sqlite> SELECT * FROM users;
id | name  | age | email
---+-------+-----+--------------------
1  | Alice | 26  | a@.com
DELETE FROM users WHERE id = 1;
sqlite> SELECT * FROM users;
-- 空表，無(wú)結(jié)果

SELECT column1, column2, ... FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...];

SELECT column1, column2 FROM table_name WHERE condition;

SELECT * FROM users WHERE age > 25;
id | name  | age | email
---+-------+-----+---------------------
2  | Bob   | 30  | bob@example.com
3  | Carol | 28  | carol@example.com

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

SELECT * FROM users ORDER BY age DESC;
id | name  | age | email
---+-------+-----+---------------------
2  | Bob   | 30  | bob@example.com
3  | Carol | 28  | carol@example.com
1  | Alice | 25  | alice@example.com

SELECT group_column, aggregate_function(...) FROM table_name GROUP BY group_column [HAVING condition];

SELECT age, COUNT(*) FROM users GROUP BY age HAVING COUNT(*) > 1;
假設(shè)有兩名用戶(hù)都 30 歲
age | COUNT(*)
----+----------
30  | 2

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

表users
id | name  | age | email
---+-------+-----+---------------------
2  | Bob   | 30  | bob@example.com
3  | Carol | 28  | carol@example.com
1  | Alice | 25  | alice@example.com
表orders
user_id | amount
--------+--------
2       | 100
3       | 150
SELECT users.name, orders.amount
FROM users
JOIN orders ON users.id = orders.user_id;
-- 輸出：
name  | amount
------+--------
Bob   | 100
Carol | 150
 

SELECT * FROM table WHERE column IN (SELECT ... FROM ... WHERE ...);

SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);
輸出：
name
-----
Carol

SELECT * FROM table_name LIMIT 限制行數(shù) OFFSET 起始行偏移量;
指令說(shuō)明
   LIMIT：限制最多返回多少行結(jié)果。
   OFFSET：跳過(guò)前面多少行數(shù)據(jù)再開(kāi)始返回（可選）。

SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 0;   -- 第1頁(yè)
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10;  -- 第2頁(yè)
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;  -- 第3頁(yè)

LIMIT 每頁(yè)條數(shù) OFFSET (頁(yè)碼 - 1) * 每頁(yè)條數(shù)

原始數(shù)據(jù)為：
id | name  | age
---+-------+-----
1  | Alice | 25
2  | Bob   | 30
3  | Carol | 28
SELECT * FROM users ORDER BY id LIMIT 2 OFFSET 1;
按 id 排序后，跳過(guò)第1條數(shù)據(jù)，從第2條開(kāi)始取，最多取2條。
執(zhí)行結(jié)果：
id | name  | age
---+-------+-----
2  | Bob   | 30
3  | Carol | 28

SELECT * FROM users WHERE age > 25;

SELECT * FROM users ORDER BY name;

SELECT * FROM users JOIN orders ON users.id = orders.user_id;

CREATE [UNIQUE] INDEX index_name ON table_name(column_name);
UNIQUE 表示不允許重復(fù)值（可選）。

CREATE INDEX idx_users_age ON users(age);
#查看是否命中索引
EXPLAIN QUERY PLAN SELECT * FROM users WHERE age > 25;
#輸出
SEARCH TABLE users USING INDEX idx_users_age (age>?)
說(shuō)明查詢(xún)使用了你創(chuàng)建的索引。

CREATE INDEX index_name ON table_name(column1, column2, ...);

SELECT * FROM users WHERE name = 'Alice' AND age = 25;
#查看是否命中索引
EXPLAIN QUERY PLAN SELECT * FROM users WHERE name = 'Alice' AND age = 25;
#輸出
SEARCH TABLE users USING INDEX idx_users_name_age (name=? AND age=?)

id | name  | age
---+-------+-----
1  | Alice | 25
2  | Bob   | 30
3  | Carol | 28
#創(chuàng)建復(fù)合索引
CREATE INDEX idx_name_age ON users(name, age);

EXPLAIN QUERY PLAN SELECT * FROM users WHERE name = 'Alice';
SEARCH TABLE users USING INDEX idx_name_age (name=?)

EXPLAIN QUERY PLAN SELECT * FROM users WHERE name = 'Alice' AND age = 30;
SEARCH TABLE users USING INDEX idx_name_age (name=? AND age=?)

EXPLAIN QUERY PLAN SELECT * FROM users WHERE age = 30;
SCAN TABLE users

EXPLAIN QUERY PLAN SELECT * FROM users WHERE age = 30 AND name = 'Alice';
SEARCH TABLE users USING INDEX idx_name_age (age=? AND name=?)

DROP INDEX [IF EXISTS] index_name;

BEGIN;

COMMIT;

BEGIN;
INSERT INTO users (name, age, city) VALUES ('Eve', 40, 'Chengdu');
UPDATE users SET age = 45 WHERE name = 'Alice';
COMMIT;

ROLLBACK;

BEGIN;
INSERT INTO users (name, age, city) VALUES ('Eve', 40, 'Chengdu');
UPDATE users SET age = 45 WHERE name = 'Alice';
-- 假設(shè)此時(shí)發(fā)生了錯(cuò)誤，我們決定回滾事務(wù)
ROLLBACK;

try {
        executeOrThrow(db, "BEGIN;");
        executeOrThrow(db, "UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A001';");
        // 故意出錯(cuò)：列名 balxxx 不存在
        executeOrThrow(db, "UPDATE accounts SET balxxx = balance + 100 WHERE account_id = 'A002';");
        executeOrThrow(db, "INSERT INTO transactions (from_account, to_account, amount) VALUES ('A001', 'A002', 100);");
        //沒(méi)問(wèn)題就提交
        executeOrThrow(db, "COMMIT;");
        std::cout << "Transaction committed.\n";
    } catch (const std::exception& ex) {
        std::cerr << ex.what() << "\n";
        //出錯(cuò)回滾
        sqlite3_exec(db, "ROLLBACK;", nullptr, nullptr, nullptr);
        std::cout << "Transaction rolled back.\n";
    }

-- 查詢(xún)最近30天訂單金額前10的用戶(hù)
SELECT * FROM top_users_last_30_days;

-- 直接查視圖
SELECT * FROM user_purchases_summary WHERE total_spent > 1000;

CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT ...;
view_name：視圖名稱(chēng)
SELECT ...：視圖對(duì)應(yīng)的查詢(xún)語(yǔ)句
TEMP：可選，創(chuàng)建臨時(shí)視圖，僅在當(dāng)前連接中可見(jiàn)

CREATE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE status = 'active';

SELECT * FROM active_users;

假設(shè) users 表如下：
| id | name  | email                                   | status   |
| -- | ----- | --------------------------------------- | -------- |
| 1  | Alice | [alice@mail.com](mailto:alice@mail.com) | active   |
| 2  | Bob   | [bob@mail.com](mailto:bob@mail.com)     | inactive |
| 3  | Carol | [carol@mail.com](mailto:carol@mail.com) | active   |
那么 active_users 視圖返回：
| id | name  | email                                   |
| -- | ----- | --------------------------------------- |
| 1  | Alice | [alice@mail.com](mailto:alice@mail.com) |
| 3  | Carol | [carol@mail.com](mailto:carol@mail.com) |

DROP VIEW active_users;

CREATE TEMP TABLE temp_summary AS
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id;

-- 避免重復(fù) JOIN 操作，提高整體查詢(xún)速度
SELECT * FROM temp_result WHERE score > 80;

CREATE TEMP TABLE temp_table_name (
    column1 TYPE,
    column2 TYPE,
    ...
);

CREATE TEMP TABLE temp_orders (
    id INTEGER,
    product TEXT,
    quantity INTEGER
);
INSERT INTO temp_orders VALUES (1, 'Book', 2);
INSERT INTO temp_orders VALUES (2, 'Pen', 5);
SELECT * FROM temp_orders;
查詢(xún)結(jié)果（臨時(shí)表內(nèi)容）：
| id | product | quantity |
| -- | ------- | -------- |
| 1  | Book    | 2        |
| 2  | Pen     | 5        |

-- 創(chuàng)建客戶(hù)表
CREATE TABLE customers (
    id INTEGER PRIMARY KEY,           -- 客戶(hù) ID，主鍵
    name TEXT,                        -- 客戶(hù)名稱(chēng)
    email TEXT                        -- 客戶(hù)郵箱
);
-- 創(chuàng)建訂單表
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,           -- 訂單 ID，主鍵
    customer_id INTEGER,              -- 關(guān)聯(lián)客戶(hù) ID
    product_name TEXT,                -- 商品名稱(chēng)
    price REAL,                       -- 商品單價(jià)
    quantity INTEGER,                 -- 購(gòu)買(mǎi)數(shù)量
    order_date TEXT,                  -- 下單時(shí)間（格式：YYYY-MM-DD HH:MM:SS）
    FOREIGN KEY (customer_id) REFERENCES customers(id)  -- 外鍵關(guān)聯(lián)客戶(hù)表
);

-- 插入客戶(hù)
INSERT INTO customers (id, name, email) VALUES
(1, 'Alice', 'alice@example.com'),
(2, 'Bob', 'bob@example.net'),
(3, 'Charlie', 'charlie@example.org');
-- 插入訂單
INSERT INTO orders (customer_id, product_name, price, quantity, order_date) VALUES
(1, 'Laptop', 899.99, 1, '2025-01-15 10:00:00'),
(1, 'Mouse', 19.99, 2, '2025-02-10 12:30:00'),
(2, 'Keyboard', 49.99, 1, '2025-03-05 14:20:00'),
(2, 'Monitor', 199.99, 1, '2025-03-06 15:10:00'),
(2, 'USB Cable', 9.99, 3, '2025-04-01 09:00:00'),
(3, 'Desk Chair', 129.99, 1, '2025-01-22 16:00:00');

SELECT
    c.name AS customer_name,  -- 客戶(hù)名稱(chēng)
    upper(substr(c.email, 1, instr(c.email, '@') - 1)) AS email_user,  
        -- 提取 email @ 前部分并轉(zhuǎn)為大寫(xiě)
    COUNT(o.id) AS total_orders,  -- 訂單總數(shù)
    SUM(o.price * o.quantity) AS total_spent,  -- 總消費(fèi)金額
    round(AVG(o.price * o.quantity), 2) AS avg_order_value,  -- 平均訂單金額（保留2位小數(shù)）
    MAX(o.order_date) AS last_order_date,  -- 最后一筆訂單的時(shí)間
    strftime('%Y-%m', o.order_date) AS order_month,  -- 訂單月份（用于聚合）
    -- 消費(fèi)金額區(qū)間分級(jí)：VIP / Gold / Regular
    CASE 
        WHEN SUM(o.price * o.quantity) > 1000 THEN 'VIP'
        WHEN SUM(o.price * o.quantity) > 500 THEN 'Gold'
        ELSE 'Regular'
    END AS customer_level
FROM
    customers c
LEFT JOIN
    orders o ON c.id = o.customer_id  -- 關(guān)聯(lián)訂單表
WHERE
    o.order_date >= date('now', '-6 months')  -- 僅查詢(xún)最近6個(gè)月的訂單
GROUP BY
    c.id
HAVING
    total_orders > 0  -- 排除沒(méi)有訂單的客戶(hù)
ORDER BY
    total_spent DESC  -- 按總消費(fèi)金額降序排列
LIMIT 10;  -- 僅顯示前10個(gè)客戶(hù)

-- 普通自增主鍵
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT
);
-- 帶 AUTOINCREMENT 的主鍵
CREATE TABLE logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    message TEXT
);

-- 默認(rèn)帶有 rowid
CREATE TABLE cities (
    name TEXT PRIMARY KEY,
    population INTEGER
);
-- 不使用 rowid
CREATE TABLE cities_norowid (
    name TEXT PRIMARY KEY,
    population INTEGER
) WITHOUT ROWID;