SQLite - 语法

SQLite - 现代 SQL 语法参考

本章提供了常见和现代 SQLite 语句的语法参考。扎实掌握这些语法对于有效的数据库管理至关重要。

一般规则

大小写敏感性： SQL 关键字（如 SELECT、FROM、WHERE）不区分大小写。但是，表名和列名可能因排序规则而区分大小写。最佳实践是保持一致性（例如，小写关键字，snake_case 标识符）。

注释： 使用注释来记录您的 SQL 代码。单行注释以 -- 开头。多行注释用 /* ... */ 包裹。

-- 这是一个单行注释。
SELECT * FROM employees;

/*
  这是一个多行注释
  用于解释复杂查询。
*/
SELECT name, salary -- 您也可以在行尾添加注释
FROM employees;

分号： 每条 SQL 语句都应以分号 (;) 结尾。虽然有些客户端会比较宽松，但在执行多条语句时它是必需的。

数据定义语言 (DDL)

CREATE TABLE

CREATE TABLE [IF NOT EXISTS] table_name (
    column1_name data_type [CONSTRAINTS],
    column2_name data_type [CONSTRAINTS],
    ...
);

ALTER TABLE (添加列)

ALTER TABLE table_name ADD COLUMN column_definition;

ALTER TABLE (重命名表)

ALTER TABLE table_name RENAME TO new_table_name;

DROP TABLE

DROP TABLE [IF EXISTS] table_name;

CREATE INDEX

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

数据操作语言 (DML)

SELECT

SELECT [DISTINCT] column_list
FROM table_name
[WHERE search_condition]
[GROUP BY column_list]
[HAVING search_condition]
[ORDER BY column_list ASC|DESC]
[LIMIT count [OFFSET position]];

INSERT INTO

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

UPSERT (INSERT ON CONFLICT)

-- 如果存在具有相同唯一键的行，则更新它。否则，插入。
INSERT INTO table_name (id, name, value)
VALUES (?, ?, ?)
ON CONFLICT(id) DO UPDATE SET
  name = excluded.name,
  value = excluded.value;

UPDATE

UPDATE table_name
SET column1 = value1, column2 = value2, ...
[WHERE search_condition];

DELETE

DELETE FROM table_name [WHERE search_condition];

高级 SQL 语法

窗口函数

窗口函数在与当前行相关的表行集上执行计算。这是用于分析的强大功能。

-- 示例：获取每个员工的薪资及其部门的平均薪资。
SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) as avg_dept_salary
FROM employees;

带 WITH 子句的公共表表达式 (CTE)

CTE 有助于将复杂查询分解为简单、可读的逻辑块。

WITH sales_employees AS (
  SELECT * FROM employees WHERE department = 'Sales'
)
SELECT name, salary FROM sales_employees WHERE salary > 50000;

JSON 函数

现代 SQLite 对 JSON 数据有出色的支持。

-- 假设 'products' 表有一个 'details' JSON 列
-- json_extract(json_column, 路径)
SELECT name, json_extract(details, '$.color') as color
FROM products;

事务和系统命令

事务控制

BEGIN TRANSACTION;
-- 您的 SQL 语句在这里...
COMMIT; -- or ROLLBACK;

PRAGMA

PRAGMA 命令用于查询或修改 SQLite 库的内部设置。

-- 最佳实践：为每个连接强制执行外键约束。
PRAGMA foreign_keys = ON;

-- 推荐用于更好的并发和性能。
PRAGMA journal_mode = WAL;

-- 获取表的列信息。
PRAGMA table_info(table_name);

EXPLAIN QUERY PLAN

-- 显示优化器选择的查询计划。对于调试性能至关重要。
EXPLAIN QUERY PLAN SELECT * FROM employees WHERE age > 30;