SQLite SQL API Reference

`Dot Commands`

4 methods

以点为前缀的 sqlite3 shell 命令。

CREATE TABLE [IF NOT EXISTS] tbl (col type [constraint], ..., [table_constraint])

关闭当前数据库并打开给定文件；':memory:' 用于内存数据库。

Name	Type	Description
tbl	identifier	Table name.
type	affinity	Type affinity such as INTEGER or TEXT.
constraint	constraint	PRIMARY KEY, NOT NULL, DEFAULT, UNIQUE, CHECK, REFERENCES.

void

sqlite

CREATE TABLE IF NOT EXISTS notes (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  body TEXT NOT NULL,
  created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO tbl [(cols)] VALUES (vals) | SELECT ... [ON CONFLICT(cols) DO ...]

将整个数据库（或单个表）渲染为适合备份或迁移的 SQL 文本。

Name	Type	Description
tbl	identifier	Target table.
cols	column-list	Optional column list.
vals	value-list	Literal values matching cols.

void

sqlite

INSERT INTO notes (body) VALUES ('hello')
ON CONFLICT(id) DO UPDATE SET body = excluded.body;

SELECT cols FROM tbl [WHERE condition] [ORDER BY cols] [LIMIT n]

将 CSV 或文本数据从文件导入到命名表。

Name	Type	Description
tbl	identifier	Source table or subquery.
condition	boolean	Filter predicate.

ResultSet

sqlite

SELECT id, body FROM notes WHERE body LIKE '%hello%' ORDER BY created_at DESC LIMIT 10;

PRAGMA name [= value]

显示数据库或命名表的 CREATE 语句。

Name	Type	Description
name	string	Pragma name, e.g. foreign_keys, journal_mode, table_info(tbl).
value	any	Optional new value for the pragma.

ResultSet | void

sqlite

PRAGMA foreign_keys = ON;
PRAGMA table_info(notes);