SQLite 增删改查

插入数据

INSERT INTO users (name, email, age)
VALUES ('Tom', 'tom@example.com', 18);

INSERT INTO users (name, email, age)
VALUES
  ('Alice', 'alice@example.com', 20),
  ('Bob', 'bob@example.com', 22);

更新数据

UPDATE users
SET age = 19
WHERE id = 1;

删除数据

DELETE FROM users
WHERE id = 1;

查询数据

SELECT id, name, email
FROM users
WHERE age >= 18
ORDER BY id DESC
LIMIT 10 OFFSET 0;

关联查询

PRAGMA foreign_keys = ON;

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  amount REAL NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

SELECT u.id, u.name, o.id AS order_id, o.amount
FROM users AS u
INNER JOIN orders AS o ON o.user_id = u.id
WHERE u.id = 1;

事务

BEGIN;

UPDATE users SET age = age + 1 WHERE id = 1;
INSERT INTO orders (user_id, amount) VALUES (1, 99.00);

COMMIT;
-- ROLLBACK;

索引

CREATE INDEX idx_users_age_created_at ON users (age, created_at);
CREATE UNIQUE INDEX uk_users_email ON users (email);
DROP INDEX idx_users_age_created_at;

执行计划

EXPLAIN QUERY PLAN
SELECT *
FROM users
WHERE email = 'tom@example.com';

常用函数

SELECT sqlite_version();
SELECT datetime('now');
SELECT date('now');
SELECT IFNULL(NULL, 'default');