SQLite 基础语法
SQLite 是轻量级的嵌入式数据库，支持大多数标准 SQL 语法，以下是其常用语法的整理与示例。
📚 参考资料：SQLite 语法 | 菜鸟教程
一、基础语法规则
1. 大小写敏感性
SQLite 的 SQL 关键字默认不区分大小写，但字符串匹配操作符（如 GLOB）是大小写敏感的。
SELECT 'apple' GLOB 'A*'; -- 返回 0，因为 GLOB 区分大小写
SELECT 'apple' LIKE 'a%'; -- 返回 1，LIKE 不区分大小写（默认）
2. 注释语法
- 单行注释：以
--开始 - 多行注释：使用
/* ... */
-- 单行注释
/*
多行注释
可用于文档注释
*/
3. SQL 语句结构
每条 SQL 语句以关键字开头，以分号 ; 结尾：
SELECT * FROM users;
二、数据定义语句（DDL）
1. 创建表
CREATE TABLE products (id INTEGER PRIMARY KEY,name TEXT NOT NULL,price REAL DEFAULT 0.0,category TEXT
);
2. 修改表结构
ALTER TABLE products ADD COLUMN stock INTEGER DEFAULT 0;
三、数据操作语句（DML）
1. 插入数据
INSERT INTO products (name, price, category)
VALUES ('Laptop', 4999.99, 'Electronics');
2. 查询数据
SELECT name, price
FROM products
WHERE price > 1000;
3. 更新数据
UPDATE products
SET price = 4599.99
WHERE name = 'Laptop';
4. 删除数据
DELETE FROM products
WHERE price < 100;
四、查询条件与逻辑运算
1. WHERE 子句
用于设置查询条件：
SELECT * FROM products
WHERE price BETWEEN 1000 AND 5000;
2. AND / OR 运算符
SELECT * FROM products
WHERE price > 1000 AND stock > 0;
3. LIKE / GLOB 模糊匹配
SELECT name FROM products
WHERE name LIKE 'L%'; -- 匹配以 L 开头
SELECT name FROM products
WHERE name GLOB 'L*'; -- 同上但区分大小写
4. IN / EXISTS 子句
SELECT * FROM products
WHERE name IN ('Laptop', 'Tablet');
SELECT * FROM products
WHERE EXISTS (SELECT 1 FROM orders WHERE orders.product_id = products.id
);
五、分组与聚合函数
1. GROUP BY
SELECT category, COUNT(*) AS count
FROM products
GROUP BY category;
2. HAVING 子句
SELECT category, COUNT(*)
FROM products
GROUP BY category
HAVING COUNT(*) > 1;
六、排序与限制
1. ORDER BY 排序
SELECT * FROM products
ORDER BY price DESC;
2. LIMIT 限制行数
SELECT * FROM products
ORDER BY price DESC
LIMIT 5;
七、视图与虚拟表
1. 创建视图
CREATE VIEW expensive_products AS
SELECT * FROM products
WHERE price > 3000;
2. 创建全文搜索虚拟表（FTS）
CREATE VIRTUAL TABLE articles
USING fts5(title, content);
八、事务控制（ACID）
1. 开启事务
BEGIN; -- 或 BEGIN TRANSACTION;
-- BEGIN EXCLUSIVE; 阻止其他连接访问数据库
2. 提交事务
COMMIT;
3. 回滚事务
ROLLBACK;
九、数据库管理
1. 附加数据库
ATTACH DATABASE 'sales.db' AS sales;
SELECT * FROM sales.orders;
十、索引与优化
1. 创建索引
CREATE INDEX idx_name ON products(name COLLATE NOCASE);
2. 删除索引
DROP INDEX idx_name;
3. ANALYZE 统计分析
ANALYZE; -- 分析所有表
ANALYZE main.products; -- 分析特定表
十一、子查询用法
1. 子查询用于 WHERE 子句
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products
);
2. 子查询用于 FROM 子句（派生表）
SELECT category, avg_price
FROM (SELECT category, AVG(price) AS avg_priceFROM productsGROUP BY category
)
WHERE avg_price > 1000;
十二、运算符示例
1. 算术运算符
SELECT 10 + 5, 10 % 3, 20 * 2, 10 / 2;
2. 比较运算符
SELECT * FROM products WHERE price >= 2000;
3. 逻辑运算符
SELECT * FROM products
WHERE stock > 10 AND price < 1000;
🔍 SQLite 综合应用案例（含详细注释）
本案例涵盖 SQLite 多种语法的协同应用，模拟一个简易的产品销售系统，包括表结构定义、数据插入、查询、视图创建、索引优化、事务控制和高级子查询等。
🎯 场景目标：
构建一个包含产品与订单的数据库，实现如下功能：
- 插入产品和订单数据
- 查询销量与销售额
- 使用视图简化查询
- 使用子查询筛选销售额高于平均值的产品
- 使用事务保障订单插入的原子性
- 添加索引提升查询效率
1. 创建数据表
-- 创建产品表，包含主键、自增字段和价格非负约束
CREATE TABLE products (id INTEGER PRIMARY KEY AUTOINCREMENT, -- 主键，自增产品IDname TEXT NOT NULL, -- 产品名称，不能为空category TEXT, -- 产品类别price REAL NOT NULL CHECK(price > 0) -- 产品价格，必须为正数
);-- 创建订单表，包含外键关联产品ID、默认订单数量为1、默认当前日期
CREATE TABLE orders (id INTEGER PRIMARY KEY AUTOINCREMENT, -- 主键，自增订单IDproduct_id INTEGER NOT NULL, -- 外键，关联产品表quantity INTEGER DEFAULT 1, -- 订单数量，默认值为1order_date TEXT DEFAULT CURRENT_DATE, -- 订单日期，默认是当前日期FOREIGN KEY (product_id) REFERENCES products(id) -- 外键约束
);
2. 插入示例数据
-- 向 products 表插入 4 条产品记录
INSERT INTO products (name, category, price) VALUES
('Laptop', 'Electronics', 5000), -- 笔记本电脑
('Phone', 'Electronics', 3000), -- 手机
('Desk', 'Furniture', 800), -- 桌子
('Chair', 'Furniture', 400); -- 椅子-- 向 orders 表插入若干订单记录
INSERT INTO orders (product_id, quantity) VALUES
(1, 2), -- Laptop ×2
(2, 3), -- Phone ×3
(2, 1), -- Phone ×1
(3, 5), -- Desk ×5
(4, 2); -- Chair ×2
3. 查询各产品总销量
-- 使用 JOIN 连接产品和订单，统计每种产品的销量
SELECT p.name, -- 产品名称SUM(o.quantity) AS total_sold -- 销量合计
FROM products p
JOIN orders o ON p.id = o.product_id -- 联结条件：订单中的产品ID = 产品表的ID
GROUP BY p.name -- 按产品名称分组
ORDER BY total_sold DESC; -- 按销量降序排列
4. 创建视图：产品销售统计
-- 创建一个视图，将查询封装，便于后续重复使用
CREATE VIEW product_sales AS
SELECT p.name, -- 产品名称p.category, -- 产品类别SUM(o.quantity) AS total_sold, -- 总销量SUM(o.quantity * p.price) AS total_revenue -- 销售总额 = 数量 × 单价
FROM products p
JOIN orders o ON p.id = o.product_id
GROUP BY p.id; -- 按产品ID分组，确保唯一
5. 查询销售额超过 5000 的产品
-- 查询视图中销售额超过 5000 的产品
SELECT *
FROM product_sales
WHERE total_revenue > 5000;
6. 子查询：筛选销售额高于平均值的产品
-- 使用子查询获得平均销售额，再筛选高于平均值的产品
SELECT name, total_revenue
FROM product_sales
WHERE total_revenue > (SELECT AVG(total_revenue) FROM product_sales);
7. 创建索引提升查询性能
-- 为订单表中的 product_id 创建索引，加快联表查询速度
CREATE INDEX idx_orders_product_id
ON orders(product_id);
8. 使用事务安全地插入订单
-- 启动事务，确保以下多条语句作为一个整体执行
BEGIN TRANSACTION;-- 插入多条订单记录
INSERT INTO orders (product_id, quantity) VALUES (1, 1); -- Laptop
INSERT INTO orders (product_id, quantity) VALUES (2, 1); -- Phone
INSERT INTO orders (product_id, quantity) VALUES (3, 2); -- Desk-- 提交事务，保存更改
COMMIT;
9. 查询有销售记录的产品
-- 使用 EXISTS 子句判断某产品是否出现在订单表中
SELECT name
FROM products
WHERE EXISTS (SELECT 1 FROM orders WHERE orders.product_id = products.id
);
✅ 技术要点总结
| 功能模块 | 使用语法 |
|---|---|
| 数据结构定义 | CREATE TABLE, FOREIGN KEY, CHECK |
| 数据操作 | INSERT, SELECT, UPDATE, DELETE |
| 查询优化 | INDEX, GROUP BY, ORDER BY |
| 查询进阶 | JOIN, VIEW, SUBQUERY, EXISTS |
| 数据一致性 | TRANSACTION, COMMIT, ROLLBACK |
