sqlite数据库SQL语句分类：

DDL（数据定义语言）：
├── CREATE TABLE 创建表
├── CREATE INDEX 创建索引
├── CREATE VIEW 创建视图
├── CREATE TRIGGER 创建触发器
├── ALTER TABLE 修改表结构
├── DROP TABLE 删除表
├── DROP INDEX 删除索引
├── DROP VIEW 删除视图
└── DROP TRIGGER 删除触发器

DML（数据操作语言）：
├── INSERT 插入数据
├── UPDATE 更新数据
├── DELETE 删除数据
└── SELECT 查询数据

DCL（数据控制语言）：
├── GRANT 授权（sqlite有限支持）
└── REVOKE 撤销授权（sqlite有限支持）

TCL（事务控制语言）：
├── BEGIN TRANSACTION 开始事务
├── COMMIT 提交事务
├── ROLLBACK 回滚事务
├── SAVEPOINT 设置保存点
└── RELEASE SAVEPOINT 释放保存点

sqlite数据库SQL语法特点：

1. 大小写不敏感
– 关键字不区分大小写
– 表名、列名不区分大小写（默认）
– 字符串值区分大小写

2. 标识符引用
– 使用双引号：”table_name”
– 使用方括号：[table_name]
– 使用反引号：`table_name`

3. 注释支持
– 单行注释：– 注释内容
– 多行注释：/* 注释内容 */

4. 语句分隔
– 使用分号(;)分隔语句
– 最后一条语句可省略分号

5. 特殊语法扩展
– UPSERT（INSERT … ON CONFLICT）
– WITHOUT ROWID表
– STRICT表（严格类型）

sqlite数据库五种存储类：

1. NULL
– 空值
– 占用空间：0字节

2. INTEGER
– 有符号整数
– 支持：1、2、3、4、6、8字节
– 根据数值大小自动选择

3. REAL
– 浮点数
– 8字节IEEE浮点数

4. TEXT
– 文本字符串
– 使用数据库编码（UTF-8/UTF-16）

5. BLOB
– 二进制大对象
– 完全按照输入存储

存储类特点：
├── 每个值都有明确的存储类
├── 存储类由值本身决定
├── 同一列可以存储不同类型
└── 类型可以在存储时改变

sqlite数据库类型亲和性规则：

类型亲和性分类：
├── INTEGER亲和性
├── TEXT亲和性
├── BLOB亲和性
├── REAL亲和性
└── NUMERIC亲和性

亲和性判断规则：
1. 声明类型包含”INT” → INTEGER亲和性
2. 声明类型包含”CHAR/CLOB/TEXT” → TEXT亲和性
3. 声明类型包含”BLOB”或未指定 → BLOB亲和性
4. 声明类型包含”REAL/FLOA/DOUB” → REAL亲和性
5. 其他情况 → NUMERIC亲和性

类型转换示例：
声明类型 亲和性 存储值
───────────────────────────────────────
INT INTEGER 整数
INTEGER INTEGER 整数
VARCHAR(255) TEXT 文本
CHAR(10) TEXT 文本
BLOB BLOB 二进制
REAL REAL 浮点数
NUMERIC NUMERIC 数值

sqlite数据库STRICT表（3.37.0+）：

传统表（动态类型）：
CREATE TABLE fgedu_data (
id INTEGER,
name TEXT,
value REAL
);
— name列可以存储任意类型

STRICT表（严格类型）：
CREATE TABLE fgedu_strict (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
value REAL
) STRICT;
— name列只能存储TEXT类型

STRICT表特点：
├── 强制类型检查
├── 插入错误类型会报错
├── 支持的类型：INT、INTEGER、REAL、TEXT、BLOB、ANY
├── 提高数据一致性
└── 适合需要严格类型约束的场景

STRICT表错误示例：
sqlite> INSERT INTO fgedu_strict (name, value) VALUES (123, 456);
Error: stepping, cannot store INT value in TEXT column fgedu_strict.name (19)

sqlite数据库支持的SQL标准特性：

基本特性：
├── SELECT/INSERT/UPDATE/DELETE
├── CREATE TABLE/INDEX/VIEW
├── PRIMARY KEY/UNIQUE/NOT NULL
├── CHECK约束
├── DEFAULT值
├── 外键约束（需启用）
└── 触发器

高级特性：
├── 子查询
├── JOIN（INNER/LEFT/RIGHT/FULL）
├── UNION/INTERSECT/EXCEPT
├── GROUP BY/HAVING
├── ORDER BY/LIMIT/OFFSET
├── 窗口函数（3.25.0+）
├── CTE公用表表达式
├── 递归CTE
└── UPSERT

函数支持：
├── 聚合函数：COUNT/SUM/AVG/MAX/MIN
├── 字符串函数：LENGTH/SUBSTR/REPLACE
├── 日期函数：DATE/TIME/DATETIME
├── 数学函数：ABS/ROUND/RANDOM
└── JSON函数：json_extract/json_array

sqlite数据库不支持的SQL特性：

1. RIGHT JOIN和FULL JOIN
— 不支持
SELECT * FROM a RIGHT JOIN b ON a.id = b.id;
— 替代方案
SELECT * FROM b LEFT JOIN a ON a.id = b.id;

2. 存储过程
— sqlite不支持存储过程
— 替代方案：使用应用层逻辑或触发器

3. 用户定义函数（服务端）
— 需要通过API注册自定义函数

4. 复杂权限管理
— sqlite权限管理较简单

5. 多表UPDATE/DELETE
— 不支持同时更新多个表

6. 计算列（Generated Columns）
— 3.31.0+版本支持

7. MERGE语句
— 使用UPSERT替代

sqlite数据库表设计规范：

命名规范：
├── 表名：小写字母，下划线分隔，如fgedu_users
├── 列名：小写字母，下划线分隔，如user_name
├── 索引名：idx_表名_列名，如idx_users_email
├── 主键名：pk_表名
└── 外键名：fk_表名_列名

主键设计：
├── 使用INTEGER PRIMARY KEY
├── 利用ROWID自动递增
├── 避免使用UUID作为主键
└── 单列主键优于复合主键

列设计：
├── 每列指定明确类型
├── 合理使用NOT NULL约束
├── 设置合理的DEFAULT值
├── 避免过多列（建议<100列）
└── 大字段考虑单独存储

约束设计：
├── 主键约束：PRIMARY KEY
├── 唯一约束：UNIQUE
├── 非空约束：NOT NULL
├── 检查约束：CHECK
├── 外键约束：FOREIGN KEY
└── 默认值：DEFAULT

sqlite数据库索引设计规范：

索引创建原则：
├── 为WHERE条件列创建索引
├── 为JOIN关联列创建索引
├── 为ORDER BY排序列创建索引
├── 为外键列创建索引
└── 避免过度索引

索引类型选择：
├── 普通索引：CREATE INDEX idx_name ON table(col)
├── 唯一索引：CREATE UNIQUE INDEX idx_name ON table(col)
├── 复合索引：CREATE INDEX idx_name ON table(col1, col2)
├── 部分索引：CREATE INDEX idx_name ON table(col) WHERE condition
└── 表达式索引：CREATE INDEX idx_name ON table(upper(col))

索引命名规范：
├── 单列索引：idx_表名_列名
├── 复合索引：idx_表名_列名1_列名2
├── 唯一索引：uk_表名_列名
└── 部分索引：idx_表名_列名_partial

索引维护：
├── 定期执行ANALYZE更新统计信息
├── 使用EXPLAIN QUERY PLAN检查索引使用
├── 删除未使用的索引
└── 监控索引大小

sqlite数据库类型选择指南：

整数类型：
├── 主键ID：INTEGER PRIMARY KEY
├── 状态标志：INTEGER（0/1或枚举值）
├── 计数器：INTEGER
├── 时间戳：INTEGER（Unix时间戳）
└── 布尔值：INTEGER（0=false, 1=true）

浮点类型：
├── 金额：REAL（注意精度问题）
├── 百分比：REAL
├── 坐标：REAL
├── 科学计算：REAL
└── 精确金额：建议用INTEGER存储分

文本类型：
├── 用户名：TEXT
├── 邮箱：TEXT
├── 手机号：TEXT
├── 地址：TEXT
├── JSON数据：TEXT
└── 长文本：TEXT

日期时间：
├── ISO格式：TEXT（’2024-04-08 10:00:00’）
├── Unix时间戳：INTEGER
├── Julian日期：REAL
└── 推荐使用TEXT或INTEGER

二进制类型：
├── 图片：BLOB
├── 文件：BLOB
├── 加密数据：BLOB
└── 序列化对象：BLOB

sqlite数据库版本兼容性规划：

向后兼容性：
├── sqlite保证向后兼容
├── 旧版本数据库文件可被新版本读取
├── 建议使用稳定版本
└── 避免使用过旧版本

版本特性检查：
sqlite> SELECT sqlite_version();
sqlite_version()
—————-
3.45.2

sqlite> SELECT sqlite_source_id();
sqlite_source_id()
————————————————————
2024-03-12 11:06:23 d8cd6d49b46a395b13955387d05e9e1a8a87e8ed7996bbd593f5c8b1cde0bd9a

特性版本要求：
├── 窗口函数：3.25.0+
├── STRICT表：3.37.0+
├── JSON1扩展：3.38.0+内置
├── UPSERT：3.24.0+
└── CTE：3.8.3+

编译选项检查：
sqlite> SELECT * FROM pragma_compile_options();

创建基础表：
sqlite> CREATE TABLE fgedu_users (
…> id INTEGER PRIMARY KEY AUTOINCREMENT,
…> username TEXT NOT NULL UNIQUE,
…> password_hash TEXT NOT NULL,
…> email TEXT UNIQUE,
…> phone TEXT,
…> status INTEGER DEFAULT 1 CHECK(status IN (0, 1)),
…> created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
…> updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
…> );

创建带外键的表：
sqlite> CREATE TABLE fgedu_orders (
…> id INTEGER PRIMARY KEY AUTOINCREMENT,
…> order_no TEXT NOT NULL UNIQUE,
…> user_id INTEGER NOT NULL,
…> total_amount REAL DEFAULT 0,
…> status TEXT DEFAULT ‘pending’
…> CHECK(status IN (‘pending’, ‘paid’, ‘shipped’, ‘completed’, ‘cancelled’)),
…> created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
…> FOREIGN KEY (user_id) REFERENCES fgedu_users(id)
…> ON DELETE CASCADE
…> ON UPDATE CASCADE
…> );

创建STRICT表：
sqlite> CREATE TABLE fgedu_products (
…> id INTEGER PRIMARY KEY,
…> name TEXT NOT NULL,
…> category TEXT NOT NULL,
…> price REAL NOT NULL CHECK(price > 0),
…> stock INTEGER NOT NULL DEFAULT 0 CHECK(stock >= 0),
…> created_at TEXT DEFAULT (datetime(‘now’))
…> ) STRICT;

创建WITHOUT ROWID表：
sqlite> CREATE TABLE fgedu_config (
…> config_key TEXT PRIMARY KEY,
…> config_value TEXT,
…> description TEXT
…> ) WITHOUT ROWID;

查看表结构：
sqlite> .schema fgedu_users
CREATE TABLE fgedu_users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL UNIQUE,
password_hash TEXT NOT NULL,
email TEXT UNIQUE,
phone TEXT,
status INTEGER DEFAULT 1 CHECK(status IN (0, 1)),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

添加列：
sqlite> ALTER TABLE fgedu_users ADD COLUMN nickname TEXT;

sqlite> .schema fgedu_users
CREATE TABLE fgedu_users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL UNIQUE,
password_hash TEXT NOT NULL,
email TEXT UNIQUE,
phone TEXT,
status INTEGER DEFAULT 1 CHECK(status IN (0, 1)),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
nickname TEXT
);

重命名表：
sqlite> ALTER TABLE fgedu_users RENAME TO fgedu_members;

sqlite> .tables
fgedu_config fgedu_members fgedu_orders fgedu_products

sqlite> ALTER TABLE fgedu_members RENAME TO fgedu_users;

重命名列（3.25.0+）：
sqlite> ALTER TABLE fgedu_users RENAME COLUMN phone TO mobile;

删除列（3.35.0+）：
sqlite> ALTER TABLE fgedu_users DROP COLUMN nickname;

注意：sqlite不支持直接删除列（3.35.0之前版本）
替代方案：重建表
sqlite> BEGIN TRANSACTION;
sqlite> CREATE TABLE fgedu_users_new (
…> id INTEGER PRIMARY KEY AUTOINCREMENT,
…> username TEXT NOT NULL UNIQUE,
…> email TEXT UNIQUE
…> );
sqlite> INSERT INTO fgedu_users_new SELECT id, username, email FROM fgedu_users;
sqlite> DROP TABLE fgedu_users;
sqlite> ALTER TABLE fgedu_users_new RENAME TO fgedu_users;
sqlite> COMMIT;

创建普通索引：
sqlite> CREATE INDEX idx_users_email ON fgedu_users(email);

创建唯一索引：
sqlite> CREATE UNIQUE INDEX idx_users_username ON fgedu_users(username);

创建复合索引：
sqlite> CREATE INDEX idx_orders_user_status ON fgedu_orders(user_id, status);

创建部分索引：
sqlite> CREATE INDEX idx_orders_pending ON fgedu_orders(user_id)
…> WHERE status = ‘pending’;

创建表达式索引：
sqlite> CREATE INDEX idx_users_email_lower ON fgedu_users(lower(email));

查看索引：
sqlite> .indices fgedu_users
idx_users_email
idx_users_email_lower
idx_users_username

sqlite> .schema idx_users_email
CREATE INDEX idx_users_email ON fgedu_users(email);

删除索引：
sqlite> DROP INDEX IF EXISTS idx_users_email_lower;

sqlite> .indices fgedu_users
idx_users_email
idx_users_username

基本插入：
sqlite> INSERT INTO fgedu_users (username, password_hash, email)
…> VALUES (‘fgedu01’, ‘hash123’, ‘fgedu01@fgedu.net.cn’);

sqlite> SELECT * FROM fgedu_users;
id username password_hash email phone status created_at
— ——– ————- ——————– —– —— ——————-
1 fgedu01 hash123 fgedu01@fgedu.net.cn 1 2024-04-08 10:00:00

多行插入：
sqlite> INSERT INTO fgedu_users (username, password_hash, email) VALUES
…> (‘fgedu02’, ‘hash456’, ‘fgedu02@fgedu.net.cn’),
…> (‘fgedu03’, ‘hash789’, ‘fgedu03@fgedu.net.cn’),
…> (‘fgedu04’, ‘hashabc’, ‘fgedu04@fgedu.net.cn’);

sqlite> SELECT COUNT(*) FROM fgedu_users;
COUNT(*)
——–
4

从SELECT插入：
sqlite> INSERT INTO fgedu_orders (order_no, user_id, total_amount)
…> SELECT ‘ORD’ || printf(‘%06d’, id), id, 0 FROM fgedu_users;

sqlite> SELECT * FROM fgedu_orders;
id order_no user_id total_amount status created_at
— ——– ——- ———— ——- ——————-
1 ORD000001 1 0 pending 2024-04-08 10:05:00
2 ORD000002 2 0 pending 2024-04-08 10:05:01
3 ORD000003 3 0 pending 2024-04-08 10:05:02
4 ORD000004 4 0 pending 2024-04-08 10:05:03

UPSERT（插入或更新）：
sqlite> INSERT INTO fgedu_users (username, email)
…> VALUES (‘fgedu01’, ‘new@fgedu.net.cn’)
…> ON CONFLICT(username) DO UPDATE SET email = excluded.email;

sqlite> SELECT username, email FROM fgedu_users WHERE username = ‘fgedu01’;
username email
——– —————–
fgedu01 new@fgedu.net.cn

基本查询：
sqlite> SELECT id, username, email FROM fgedu_users;
id username email
— ——– ——————
1 fgedu01 new@fgedu.net.cn
2 fgedu02 fgedu02@fgedu.net.cn
3 fgedu03 fgedu03@fgedu.net.cn
4 fgedu04 fgedu04@fgedu.net.cn

条件查询：
sqlite> SELECT * FROM fgedu_users WHERE status = 1 AND email LIKE ‘%@fgedu.net.cn’;

排序和分页：
sqlite> SELECT * FROM fgedu_users
…> ORDER BY created_at DESC
…> LIMIT 2 OFFSET 1;

聚合查询：
sqlite> SELECT status, COUNT(*) as count
…> FROM fgedu_users
…> GROUP BY status
…> HAVING count > 0;

关联查询：
sqlite> SELECT u.username, o.order_no, o.total_amount
…> FROM fgedu_users u
…> INNER JOIN fgedu_orders o ON u.id = o.user_id
…> ORDER BY o.total_amount DESC;

子查询：
sqlite> SELECT * FROM fgedu_users
…> WHERE id IN (SELECT user_id FROM fgedu_orders WHERE status = ‘pending’);

CTE公用表表达式：
sqlite> WITH active_users AS (
…> SELECT * FROM fgedu_users WHERE status = 1
…> )
…> SELECT u.username, COUNT(o.id) as order_count
…> FROM active_users u
…> LEFT JOIN fgedu_orders o ON u.id = o.user_id
…> GROUP BY u.id;

UPDATE基本语法：
sqlite> UPDATE fgedu_users
…> SET email = ‘updated@fgedu.net.cn’,
…> updated_at = datetime(‘now’)
…> WHERE username = ‘fgedu01’;

sqlite> SELECT username, email FROM fgedu_users WHERE username = ‘fgedu01’;
username email
——– ——————–
fgedu01 updated@fgedu.net.cn

UPDATE使用子查询：
sqlite> UPDATE fgedu_orders
…> SET total_amount = (
…> SELECT SUM(price * 2) FROM fgedu_products WHERE category = ‘电子’
…> )
…> WHERE status = ‘pending’;

UPDATE使用CASE：
sqlite> UPDATE fgedu_orders
…> SET status = CASE
…> WHEN total_amount > 500 THEN ‘high_value’
…> ELSE ‘normal’
…> END;

DELETE基本语法：
sqlite> DELETE FROM fgedu_users WHERE status = 0;

sqlite> SELECT COUNT(*) FROM fgedu_users;
COUNT(*)
——–
4

DELETE使用子查询：
sqlite> DELETE FROM fgedu_orders
…> WHERE user_id IN (SELECT id FROM fgedu_users WHERE status = 0);

TRUNCATE替代方案：
sqlite> DELETE FROM fgedu_orders;
sqlite> VACUUM;

创建测试数据：
sqlite> CREATE TABLE fgedu_sales (
…> id INTEGER PRIMARY KEY,
…> salesperson TEXT,
…> region TEXT,
…> amount REAL,
…> sale_date DATE
…> );

sqlite> INSERT INTO fgedu_sales VALUES
…> (1, ‘张三’, ‘华东’, 10000, ‘2024-01-01’),
…> (2, ‘李四’, ‘华东’, 15000, ‘2024-01-01’),
…> (3, ‘王五’, ‘华北’, 8000, ‘2024-01-01’),
…> (4, ‘张三’, ‘华东’, 12000, ‘2024-01-02’),
…> (5, ‘李四’, ‘华东’, 9000, ‘2024-01-02’);

窗口函数示例：
sqlite> SELECT
…> salesperson,
…> region,
…> amount,
…> SUM(amount) OVER (PARTITION BY region) as region_total,
…> AVG(amount) OVER (PARTITION BY region) as region_avg,
…> RANK() OVER (PARTITION BY region ORDER BY amount DESC) as region_rank
…> FROM fgedu_sales;
salesperson region amount region_total region_avg region_rank
———– —— —— ———— ———- ———–
李四 华东 15000 46000 11500.0 1
张三 华东 12000 46000 11500.0 2
张三 华东 10000 46000 11500.0 3
李四 华东 9000 46000 11500.0 4
王五 华北 8000 8000 8000.0 1

ROW_NUMBER示例：
sqlite> SELECT
…> salesperson,
…> amount,
…> ROW_NUMBER() OVER (ORDER BY amount DESC) as rank
…> FROM fgedu_sales;
salesperson amount rank
———– —— —-
李四 15000 1
张三 12000 2
张三 10000 3
李四 9000 4
王五 8000 5

创建组织架构表：
sqlite> CREATE TABLE fgedu_org (
…> id INTEGER PRIMARY KEY,
…> name TEXT NOT NULL,
…> manager_id INTEGER,
…> FOREIGN KEY (manager_id) REFERENCES fgedu_org(id)
…> );

sqlite> INSERT INTO fgedu_org VALUES
…> (1, ‘CEO’, NULL),
…> (2, ‘技术总监’, 1),
…> (3, ‘销售总监’, 1),
…> (4, ‘开发经理’, 2),
…> (5, ‘测试经理’, 2),
…> (6, ‘销售经理’, 3),
…> (7, ‘开发工程师’, 4),
…> (8, ‘测试工程师’, 5);

递归查询组织架构：
sqlite> WITH RECURSIVE org_tree AS (
…> — 基础查询：顶级节点
…> SELECT id, name, manager_id, 0 as level, name as path
…> FROM fgedu_org
…> WHERE manager_id IS NULL
…>
…> UNION ALL
…>
…> — 递归查询：子节点
…> SELECT e.id, e.name, e.manager_id, t.level + 1, t.path || ‘ -> ‘ || e.name
…> FROM fgedu_org e
…> INNER JOIN org_tree t ON e.manager_id = t.id
…> )
…> SELECT id, name, level, path FROM org_tree ORDER BY level, id;
id name level path
— ——– —– ————————-
1 CEO 0 CEO
2 技术总监 1 CEO -> 技术总监
3 销售总监 1 CEO -> 销售总监
4 开发经理 2 CEO -> 技术总监 -> 开发经理
5 测试经理 2 CEO -> 技术总监 -> 测试经理
6 销售经理 2 CEO -> 销售总监 -> 销售经理
7 开发工程师 3 CEO -> 技术总监 -> 开发经理 -> 开发工程师
8 测试工程师 3 CEO -> 技术总监 -> 测试经理 -> 测试工程师

电商订单系统表设计：

用户表：
sqlite> CREATE TABLE fgedu_customers (
…> id INTEGER PRIMARY KEY AUTOINCREMENT,
…> customer_no TEXT UNIQUE NOT NULL,
…> name TEXT NOT NULL,
…> email TEXT UNIQUE,
…> phone TEXT,
…> address TEXT,
…> status INTEGER DEFAULT 1,
…> created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
…> updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
…> );

商品表：
sqlite> CREATE TABLE fgedu_products (
…> id INTEGER PRIMARY KEY AUTOINCREMENT,
…> product_no TEXT UNIQUE NOT NULL,
…> name TEXT NOT NULL,
…> category TEXT,
…> price REAL NOT NULL CHECK(price > 0),
…> stock INTEGER DEFAULT 0 CHECK(stock >= 0),
…> status INTEGER DEFAULT 1,
…> created_at DATETIME DEFAULT CURRENT_TIMESTAMP
…> );

订单表：
sqlite> CREATE TABLE fgedu_orders (
…> id INTEGER PRIMARY KEY AUTOINCREMENT,
…> order_no TEXT UNIQUE NOT NULL,
…> customer_id INTEGER NOT NULL,
…> total_amount REAL DEFAULT 0,
…> status TEXT DEFAULT ‘pending’,
…> payment_status TEXT DEFAULT ‘unpaid’,
…> shipping_address TEXT,
…> created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
…> updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
…> FOREIGN KEY (customer_id) REFERENCES fgedu_customers(id)
…> );

订单明细表：
sqlite> CREATE TABLE fgedu_order_items (
…> id INTEGER PRIMARY KEY AUTOINCREMENT,
…> order_id INTEGER NOT NULL,
…> product_id INTEGER NOT NULL,
…> quantity INTEGER NOT NULL CHECK(quantity > 0),
…> unit_price REAL NOT NULL,
…> subtotal REAL NOT NULL,
…> FOREIGN KEY (order_id) REFERENCES fgedu_orders(id) ON DELETE CASCADE,
…> FOREIGN KEY (product_id) REFERENCES fgedu_products(id)
…> );

创建索引：
sqlite> CREATE INDEX idx_customers_email ON fgedu_customers(email);
sqlite> CREATE INDEX idx_orders_customer ON fgedu_orders(customer_id);
sqlite> CREATE INDEX idx_orders_status ON fgedu_orders(status);
sqlite> CREATE INDEX idx_order_items_order ON fgedu_order_items(order_id);

类型转换函数：
sqlite> SELECT CAST(‘123’ AS INTEGER) as int_value;
int_value
———
123

sqlite> SELECT CAST(123.45 AS TEXT) as text_value;
text_value
———-
123.45

sqlite> SELECT CAST(‘abc’ AS INTEGER) as int_value;
int_value
———
0

日期时间处理：
sqlite> SELECT date(‘now’) as today;
today
—–
2024-04-08

sqlite> SELECT datetime(‘now’) as now;
now
——————-
2024-04-08 10:30:00

sqlite> SELECT strftime(‘%Y-%m-%d %H:%M:%S’, ‘now’) as formatted;
formatted
——————-
2024-04-08 10:30:00

sqlite> SELECT date(‘now’, ‘+7 days’) as next_week;
next_week
———
2024-04-15

布尔值处理：
sqlite> SELECT CASE WHEN 1 THEN ‘true’ ELSE ‘false’ END as bool_text;
bool_text
———
true

sqlite> SELECT typeof(1), typeof(0), typeof(NULL);
typeof(1) typeof(0) typeof(NULL)
——— ——— ————
integer integer null

插入测试数据：
sqlite> INSERT INTO fgedu_customers (customer_no, name, email) VALUES
…> (‘C001’, ‘张三’, ‘zhangsan@fgedu.net.cn’),
…> (‘C002’, ‘李四’, ‘lisi@fgedu.net.cn’);

sqlite> INSERT INTO fgedu_products (product_no, name, category, price, stock) VALUES
…> (‘P001’, ‘产品A’, ‘电子’, 199.99, 100),
…> (‘P002’, ‘产品B’, ‘服装’, 299.00, 50),
…> (‘P003’, ‘产品C’, ‘电子’, 99.00, 200);

sqlite> INSERT INTO fgedu_orders (order_no, customer_id, total_amount, status) VALUES
…> (‘ORD001’, 1, 698.98, ‘completed’),
…> (‘ORD002’, 2, 299.00, ‘completed’),
…> (‘ORD003’, 1, 99.00, ‘pending’);

sqlite> INSERT INTO fgedu_order_items (order_id, product_id, quantity, unit_price, subtotal) VALUES
…> (1, 1, 2, 199.99, 399.98),
…> (1, 2, 1, 299.00, 299.00),
…> (2, 2, 1, 299.00, 299.00),
…> (3, 3, 1, 99.00, 99.00);

销售统计查询：
sqlite> SELECT
…> p.category,
…> COUNT(DISTINCT o.id) as order_count,
…> SUM(oi.quantity) as total_quantity,
…> SUM(oi.subtotal) as total_sales
…> FROM fgedu_products p
…> JOIN fgedu_order_items oi ON p.id = oi.product_id
…> JOIN fgedu_orders o ON oi.order_id = o.id
…> WHERE o.status = ‘completed’
…> GROUP BY p.category
…> ORDER BY total_sales DESC;
category order_count total_quantity total_sales
——– ———– ————– ———–
电子 1 2 399.98
服装 2 2 598.00

客户消费排行：
sqlite> SELECT
…> c.name,
…> COUNT(o.id) as order_count,
…> SUM(o.total_amount) as total_spent
…> FROM fgedu_customers c
…> JOIN fgedu_orders o ON c.id = o.customer_id
…> GROUP BY c.id
…> ORDER BY total_spent DESC;
name order_count total_spent
—- ———– ———–
张三 2 797.98
李四 1 299.00

sqlite数据库SQL编写规范：

1. 格式规范
– 关键字大写
– 表名、列名小写
– 适当缩进换行
– 添加必要注释

2. 查询优化
– 避免SELECT *
– 使用索引优化查询
– 合理使用LIMIT
– 避免复杂子查询

3. 事务使用
– 写操作使用事务
– 事务保持简短
– 避免长事务

4. 安全规范
– 使用参数化查询
– 避免SQL注入
– 验证输入数据

示例：
— 规范的SQL格式
SELECT
u.username,
u.email,
COUNT(o.id) as order_count
FROM fgedu_users u
LEFT JOIN fgedu_orders o ON u.id = o.user_id
WHERE u.status = 1
AND o.created_at >= date(‘now’, ‘-30 days’)
GROUP BY u.id
ORDER BY order_count DESC
LIMIT 10;

sqlite数据库类型使用技巧：

1. 日期时间存储
推荐：TEXT格式（ISO 8601）
‘2024-04-08 10:00:00’

或：INTEGER（Unix时间戳）
1712553600

2. 金额存储
推荐：INTEGER存储分
19999 表示 199.99元

避免：REAL存储金额（精度问题）

3. 布尔值存储
推荐：INTEGER（0/1）
0 = false, 1 = true

4. JSON数据存储
推荐：TEXT存储JSON字符串
使用json_extract等函数操作

5. 大文本存储
直接使用TEXT类型
注意：超过1GB需要特殊处理

6. 二进制数据存储
使用BLOB类型
注意：大文件建议存储路径

sqlite数据库开发规范总结：

表设计规范：
├── 合理选择主键类型
├── 使用适当的约束
├── 设置合理的默认值
├── 添加必要的索引
└── 考虑使用STRICT表

SQL编写规范：
├── 遵循格式规范
├── 使用参数化查询
├── 合理使用事务
├── 优化查询性能
└── 添加必要注释

类型使用规范：
├── 选择合适的存储类型
├── 注意类型转换
├── 处理NULL值
└── 统一日期格式

性能优化规范：
├── 创建必要的索引
├── 使用EXPLAIN分析
├── 批量操作使用事务
├── 定期执行VACUUM
└── 更新统计信息

安全规范：
├── 使用参数化查询
├── 验证输入数据
├── 限制查询结果
├── 处理敏感数据
└── 定期备份数据