第三章：基本操作¶

创建表¶

-- 创建用户表
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    age INTEGER,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

-- 创建订单表
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    amount REAL,
    status TEXT DEFAULT 'pending',
    FOREIGN KEY (user_id) REFERENCES users(id)
);

插入数据¶

-- 插入单条
INSERT INTO users (name, email, age) VALUES ('Alice', 'alice@example.com', 25);

-- 插入多条
INSERT INTO users (name, email, age) VALUES
    ('Bob', 'bob@example.com', 30),
    ('Charlie', 'charlie@example.com', 28);

-- 从查询插入
INSERT INTO users_backup SELECT * FROM users;

查询数据¶

-- 查询所有
SELECT * FROM users;

-- 条件查询
SELECT * FROM users WHERE age > 25;

-- 指定列
SELECT name, email FROM users;

-- 排序
SELECT * FROM users ORDER BY age DESC;

-- 分页
SELECT * FROM users LIMIT 10 OFFSET 20;

-- 去重
SELECT DISTINCT age FROM users;

更新数据¶

-- 更新单条
UPDATE users SET age = 26 WHERE name = 'Alice';

-- 更新多条
UPDATE users SET status = 'active' WHERE age > 25;

-- 更新多个字段
UPDATE users SET name = 'Alice2', age = 27 WHERE id = 1;

删除数据¶

-- 删除单条
DELETE FROM users WHERE id = 1;

-- 删除多条
DELETE FROM users WHERE age < 20;

-- 删除所有
DELETE FROM users;

-- 删除表
DROP TABLE users;

小结¶

基本操作要点：

创建表：CREATE TABLE
插入数据：INSERT INTO
查询数据：SELECT、WHERE、ORDER BY、LIMIT
更新数据：UPDATE SET
删除数据：DELETE FROM

下一章我们将学习高级查询。