SQLite基础语法 #

-- SQL 语句的基本结构
SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column;

-- 语句以分号结尾
-- 可以跨多行书写

-- SQLite 不区分关键字大小写
SELECT * FROM users;    -- 推荐
select * from users;    -- 可行
Select * From Users;    -- 可行

-- 但标识符（表名、列名）区分大小写
-- 推荐统一使用小写或大写

-- 多条语句用分号分隔
SELECT * FROM users;
SELECT * FROM orders;

-- 单条语句末尾分号可选（命令行中）
SELECT * FROM users

-- 这是单行注释
SELECT * FROM users;    -- 行尾注释

-- 注释可以独占一行
-- SELECT * FROM deleted_users;

/*
 * 这是多行注释
 * 可以跨越多行
 * 用于详细说明
 */
SELECT * FROM users;

/*
临时禁用的代码
SELECT * FROM old_table;
UPDATE stats SET count = 0;
*/

-- 好的注释习惯

-- 1. 解释复杂逻辑
-- 使用递归CTE计算层级结构
WITH RECURSIVE hierarchy AS (
    ...
)

-- 2. 说明业务规则
-- 只查询活跃用户（状态为1或2）
SELECT * FROM users WHERE status IN (1, 2);

-- 3. 标注重要信息
-- TODO: 添加索引优化
-- FIXME: 处理NULL值情况
-- NOTE: 此查询影响性能

-- 4. 文档化存储过程
/*
 * 函数：calculate_total
 * 功能：计算订单总金额
 * 参数：order_id - 订单ID
 * 返回：总金额
 * 作者：Developer
 * 日期：2024-01-15
 */

-- 标识符：表名、列名、索引名等

-- 规则：
-- 1. 以字母或下划线开头
-- 2. 后续可以是字母、数字、下划线
-- 3. 不能是保留关键字（除非引用）
-- 4. 长度无限制

-- 合法的标识符
users
user_name
_user
User123
user_name_2

-- 不合法的标识符
123users      -- 以数字开头
user-name     -- 包含连字符
user name     -- 包含空格
select        -- 保留关键字

-- 使用双引号或反引号或方括号引用标识符

-- 双引号（标准SQL）
SELECT "name" FROM "users";

-- 反引号（MySQL兼容）
SELECT `name` FROM `users`;

-- 方括号（SQL Server兼容）
SELECT [name] FROM [users];

-- 使用保留关键字作为标识符
CREATE TABLE "select" (
    "from" TEXT,
    "where" TEXT
);

-- 包含特殊字符的标识符
CREATE TABLE "user-info" (
    "first name" TEXT,
    "last name" TEXT
);

-- 推荐：使用小写字母和下划线
CREATE TABLE user_orders (
    user_id INTEGER,
    order_id INTEGER,
    created_at DATETIME
);

-- 不推荐：使用驼峰命名（需要引用）
CREATE TABLE "UserOrders" (
    "userId" INTEGER,
    "orderId" INTEGER
);

-- 不推荐：使用保留关键字
CREATE TABLE "table" (
    "select" TEXT,
    "from" TEXT
);

-- SQLite 保留关键字（部分列表）
-- 这些关键字不能直接用作标识符

ABORT, ACTION, ADD, AFTER, ALL, ALTER, ALWAYS, ANALYZE,
AND, AS, ASC, ATTACH, AUTOINCREMENT, BEFORE, BEGIN,
BETWEEN, BY, CASCADE, CASE, CAST, CHECK, COLLATE,
COLUMN, COMMIT, CONFLICT, CONSTRAINT, CREATE, CROSS,
CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, DATABASE,
DEFAULT, DEFERRABLE, DEFERRED, DELETE, DESC, DETACH,
DISTINCT, DO, DROP, EACH, ELSE, END, ESCAPE, EXCEPT,
EXCLUDE, EXISTS, EXPLAIN, FAIL, FILTER, FIRST, FOLLOWING,
FOR, FOREIGN, FROM, FULL, GENERATED, GLOB, GROUP, GROUPS,
HAVING, IF, IGNORE, IMMEDIATE, IN, INDEX, INDEXED,
INITIALLY, INNER, INSERT, INSTEAD, INTERSECT, INTO, IS,
ISNULL, JOIN, KEY, LAST, LEFT, LIKE, LIMIT, MATCH,
MATERIALIZED, NATURAL, NO, NOT, NOTHING, NOTNULL, NULL,
NULLS, OF, OFFSET, ON, OR, ORDER, OTHERS, OUTER, OVER,
PARTITION, PLAN, PRAGMA, PRECEDING, PRIMARY, QUERY,
RAISE, RANGE, RECURSIVE, REFERENCES, REGEXP, REINDEX,
RELEASE, RENAME, REPLACE, RESTRICT, RETURNING, RIGHT,
ROLLBACK, ROW, ROWS, SAVEPOINT, SELECT, SET, TABLE, TEMP,
TEMPORARY, THEN, TIES, TO, TRANSACTION, TRIGGER, UNBOUNDED,
UNION, UNIQUE, UPDATE, USING, VACUUM, VALUES, VIEW,
VIRTUAL, WHEN, WHERE, WINDOW, WITH, WITHOUT

-- 非保留关键字可以用作标识符
-- 但不推荐这样做

-- 示例：可以使用但需谨慎
CREATE TABLE action (
    id INTEGER PRIMARY KEY,
    name TEXT
);

-- 1. 避免使用关键字作为标识符
-- 不推荐
CREATE TABLE "order" (
    "select" TEXT,
    "from" TEXT
);

-- 推荐
CREATE TABLE orders (
    select_text TEXT,
    from_text TEXT
);

-- 2. 添加前缀或后缀避免冲突
CREATE TABLE user_order (
    id INTEGER PRIMARY KEY
);

-- 3. 使用描述性名称
CREATE TABLE order_details (
    id INTEGER PRIMARY KEY
);

-- SQLite 只有 5 种存储类

-- 1. NULL - 空值
INSERT INTO test (value) VALUES (NULL);

-- 2. INTEGER - 整数
INSERT INTO test (value) VALUES (123);

-- 3. REAL - 浮点数
INSERT INTO test (value) VALUES (3.14);

-- 4. TEXT - 文本字符串
INSERT INTO test (value) VALUES ('Hello');

-- 5. BLOB - 二进制数据
INSERT INTO test (value) VALUES (X'01020304');

-- SQLite 使用类型亲和性系统

-- INTEGER 亲和性
CREATE TABLE t1 (col INT);           -- INTEGER
CREATE TABLE t2 (col INTEGER);       -- INTEGER
CREATE TABLE t3 (col TINYINT);       -- INTEGER
CREATE TABLE t4 (col SMALLINT);      -- INTEGER
CREATE TABLE t5 (col MEDIUMINT);     -- INTEGER
CREATE TABLE t6 (col BIGINT);        -- INTEGER

-- TEXT 亲和性
CREATE TABLE t7 (col CHAR(20));      -- TEXT
CREATE TABLE t8 (col VARCHAR(255));  -- TEXT
CREATE TABLE t9 (col TEXT);          -- TEXT
CREATE TABLE t10 (col CLOB);         -- TEXT

-- BLOB 亲和性
CREATE TABLE t11 (col BLOB);         -- BLOB

-- REAL 亲和性
CREATE TABLE t12 (col REAL);         -- REAL
CREATE TABLE t13 (col DOUBLE);       -- REAL
CREATE TABLE t14 (col FLOAT);        -- REAL

-- NUMERIC 亲和性
CREATE TABLE t15 (col NUMERIC);      -- NUMERIC
CREATE TABLE t16 (col DECIMAL(10,2));-- NUMERIC
CREATE TABLE t17 (col BOOLEAN);      -- NUMERIC
CREATE TABLE t18 (col DATE);         -- NUMERIC
CREATE TABLE t19 (col DATETIME);     -- NUMERIC

-- 使用单引号
SELECT 'Hello World';

-- 转义单引号
SELECT 'It''s a test';    -- 使用两个单引号

-- 空字符串
SELECT '';

-- 多行字符串
SELECT 'Line 1
Line 2
Line 3';

-- 整数
SELECT 123;
SELECT -456;
SELECT +789;

-- 浮点数
SELECT 3.14;
SELECT -2.718;
SELECT 1.5e10;        -- 科学计数法
SELECT 1.5E-10;

-- 十六进制
SELECT 0xFF;          -- 255
SELECT 0x10;          -- 16

-- SQLite 没有真正的布尔类型
-- 使用 0 和 1 表示

SELECT 1;    -- true
SELECT 0;    -- false

-- 也可以使用关键字
SELECT TRUE;   -- 等价于 1
SELECT FALSE;  -- 等价于 0

-- NULL 表示未知或缺失的值
SELECT NULL;

-- NULL 不是空字符串
SELECT NULL = '';     -- 结果是 NULL，不是 0
SELECT NULL IS NULL;  -- 结果是 1

-- NULL 的运算
SELECT NULL + 1;      -- 结果是 NULL
SELECT NULL || 'text'; -- 结果是 NULL

-- 十六进制表示
SELECT X'01020304';
SELECT x'414243';      -- 小写 x 也可以

-- 空BLOB
SELECT X'';

SELECT 10 + 5;    -- 加法：15
SELECT 10 - 5;    -- 减法：5
SELECT 10 * 5;    -- 乘法：50
SELECT 10 / 5;    -- 除法：2
SELECT 10 % 3;    -- 取模：1

SELECT 10 = 10;      -- 等于：1
SELECT 10 == 10;     -- 等于：1
SELECT 10 != 5;      -- 不等于：1
SELECT 10 <> 5;      -- 不等于：1
SELECT 10 > 5;       -- 大于：1
SELECT 10 < 5;       -- 小于：0
SELECT 10 >= 10;     -- 大于等于：1
SELECT 10 <= 10;     -- 小于等于：1

-- AND
SELECT 1 AND 1;      -- 1
SELECT 1 AND 0;      -- 0

-- OR
SELECT 0 OR 1;       -- 1
SELECT 0 OR 0;       -- 0

-- NOT
SELECT NOT 1;        -- 0
SELECT NOT 0;        -- 1

-- BETWEEN
SELECT 5 BETWEEN 1 AND 10;    -- 1

-- IN
SELECT 5 IN (1, 3, 5, 7);     -- 1

-- LIKE
SELECT 'hello' LIKE 'h%';     -- 1

-- IS NULL
SELECT NULL IS NULL;          -- 1

-- IS NOT NULL
SELECT 'x' IS NOT NULL;       -- 1

-- 连接运算符
SELECT 'Hello' || ' ' || 'World';    -- Hello World

-- LIKE 模式匹配
SELECT 'hello' LIKE 'h%';     -- 以h开头
SELECT 'hello' LIKE '%o';     -- 以o结尾
SELECT 'hello' LIKE '%ll%';   -- 包含ll
SELECT 'hello' LIKE 'h_llo';  -- _匹配单个字符

-- GLOB 模式匹配（Unix风格）
SELECT 'hello' GLOB 'h*';     -- 以h开头
SELECT 'hello' GLOB '*o';     -- 以o结尾
SELECT 'hello' GLOB 'h?llo';  -- ?匹配单个字符
SELECT 'test.txt' GLOB '*.txt'; -- 匹配扩展名

SELECT 10 + 5 * 2;           -- 20（先乘后加）
SELECT (10 + 5) * 2;         -- 30（括号优先）

-- 在查询中使用
SELECT 
    product_name,
    price,
    quantity,
    price * quantity AS total
FROM order_items;

-- CASE WHEN
SELECT 
    name,
    CASE 
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        WHEN score >= 60 THEN 'D'
        ELSE 'F'
    END AS grade
FROM students;

-- 简单 CASE
SELECT 
    status,
    CASE status
        WHEN 1 THEN 'Active'
        WHEN 0 THEN 'Inactive'
        ELSE 'Unknown'
    END AS status_name
FROM users;

-- IIF 函数（简化版CASE）
SELECT 
    name,
    IIF(score >= 60, 'Pass', 'Fail') AS result
FROM students;

-- 标量子查询
SELECT 
    name,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count
FROM users;

-- EXISTS 表达式
SELECT name FROM users
WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);

-- IN 子查询
SELECT name FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

-- 推荐：小写字母，下划线分隔，复数形式
users
user_orders
order_items
product_categories

-- 不推荐
Users          -- 大写
userOrders     -- 驼峰
user-orders    -- 连字符
user           -- 单数形式

-- 关联表命名
user_roles     -- 用户角色关联表
order_products -- 订单产品关联表

-- 推荐：小写字母，下划线分隔
user_id
first_name
created_at
is_active

-- 主键命名
id             -- 单表主键
user_id        -- 外键引用

-- 布尔列命名
is_active
has_permission
can_edit

-- 时间列命名
created_at
updated_at
deleted_at
published_date

-- 格式：idx_表名_列名
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- 复合索引
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- 唯一索引
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- 格式：trg_表名_操作_时机
CREATE TRIGGER trg_users_insert_after
AFTER INSERT ON users
BEGIN
    -- 触发器逻辑
END;

CREATE TRIGGER trg_orders_update_before
BEFORE UPDATE ON orders
BEGIN
    -- 触发器逻辑
END;

-- CREATE - 创建对象
CREATE TABLE users (...);
CREATE INDEX idx_name ON table(column);
CREATE VIEW view_name AS SELECT ...;
CREATE TRIGGER trigger_name ...;

-- ALTER - 修改对象
ALTER TABLE users ADD COLUMN age INTEGER;
ALTER TABLE users RENAME TO members;

-- DROP - 删除对象
DROP TABLE users;
DROP INDEX idx_name;
DROP VIEW view_name;

-- INSERT - 插入数据
INSERT INTO users (name) VALUES ('Alice');

-- UPDATE - 更新数据
UPDATE users SET name = 'Bob' WHERE id = 1;

-- DELETE - 删除数据
DELETE FROM users WHERE id = 1;

-- SELECT - 查询数据
SELECT * FROM users;
SELECT name, email FROM users WHERE active = 1;

-- SQLite 不支持传统的GRANT/REVOKE
-- 权限控制通过文件系统实现

-- 相关命令
PRAGMA database_list;    -- 列出数据库
ATTACH DATABASE 'file.db' AS alias;  -- 附加数据库
DETACH DATABASE alias;   -- 分离数据库

BEGIN TRANSACTION;       -- 开始事务
COMMIT;                  -- 提交事务
ROLLBACK;                -- 回滚事务
SAVEPOINT savepoint_name;-- 创建保存点
RELEASE SAVEPOINT savepoint_name; -- 释放保存点