SQLite 基础教程：增删改查与常用查询技巧

一、 准备测试环境与数据

在开始具体操作前，先创建测试用的数据表并写入初始样本数据。这能确保后续的增删改查有迹可循。

首先创建一张名为 Teachers 的教师表，设定主键、名称非空约束及年龄大于 22 的限制：

$ sqlite3 test.db
sqlite> CREATE TABLE Teachers(
...>   Id integer PRIMARY KEY,
...>   Name text NOT NULL,
...>   Age integer CHECK(Age>22),
...>   Country text DEFAULT 'USA');
sqlite>

向表中插入几行数据，验证约束是否生效：

sqlite> INSERT INTO Teachers VALUES(1,'Alice',25,'CHN');
sqlite> INSERT INTO Teachers VALUES(2,'Bob',25,'BRA');
sqlite> INSERT INTO Teachers(Name,Age,Country) VALUES('John',43,'USA');
sqlite>

二、 插入数据 INSERT INTO

INSERT INTO 用于向现有表中添加新记录。根据需求不同，可以指定全部字段或仅插入部分字段。

如果提供完整的列值列表，可以省略列名：

sqlite> INSERT INTO Teachers VALUES(3,'Charls',33,'USA');

若只指定部分字段，未指定的字段将使用默认值（如上面的 Country），若默认值不存在且允许为空则留空；若不允许为空且无默认值则会报错：

sqlite> INSERT INTO Teachers(Id,Name,Age,Country) VALUES(3,'Charls',33,'USA');
sqlite> INSERT INTO Teachers(Name,Age) VALUES('Jhon',43);

插入后可以通过 SELECT 查看结果：

sqlite> SELECT * FROM Teachers;
Id  Name        Age           Country
--  --------------  --------------  ----------------
1   Alice       25            CHN
2   Bob         25            BRA
3   Charls      33            USA
4   Jhon        43            USA

三、 更新数据 UPDATE

UPDATE 语句用于修改表中的数据。若不加 WHERE 子句，所有行的该列将被更新，因此务必谨慎操作。

1. 无条件的全表更新：

sqlite> UPDATE Teachers SET Country='China';
-- 此时所有人的国家都变成了 China

2. 带条件的精准更新：

使用 WHERE 限定目标行。例如只修改 ID 为 3 的记录：

sqlite> UPDATE Teachers SET Country='America' WHERE Id=3;

可以使用复杂的逻辑条件，例如更新年龄小于 30 岁的记录：

sqlite> UPDATE Teachers SET Country='India' WHERE Age<30;
-- Alice (25岁) 和 Bob (25岁) 的国家被更改为 India

四、 删除数据 DELETE

DELETE 语句用于移除表中的记录。同样需要警惕不带 WHERE 的情况，那会清空整个表！建议在删除前先运行一次同等条件的 SELECT 以预览将要删除的数据。

1. 有条件删除：

sqlite> SELECT * FROM Teachers; -- 假设当前数据如上
sqlite> DELETE FROM Teachers WHERE Age>30;
-- 年龄大于30的 Charls (33岁) 和 John (43岁) 被移除

2. 清空全表：

sqlite> DELETE FROM Teachers;
sqlite> SELECT * FROM Teachers;
-- 结果为空，所有记录已被删除

五、 读取数据 SELECT（基础与进阶）

SELECT 是最常用的 SQL 语句，功能极其丰富。为了演示后面的高级查询，我们需要建立两组关联数据（汽车表 Cars 和订单表 Orders）：

创建并导入以下结构的 data.sql 文件到数据库中：

CREATE TABLE Cars(Id integer PRIMARY KEY, Name text, Cost integer);
INSERT INTO Cars VALUES(1,'Audi',52642);
INSERT INTO Cars VALUES(2,'Mercedes',57127);
INSERT INTO Cars VALUES(3,'Skoda',9000);
INSERT INTO Cars VALUES(4,'Volvo',29000);
INSERT INTO Cars VALUES(5,'Bentley',350000);
INSERT INTO Cars VALUES(6,'Citroen',21000);
INSERT INTO Cars VALUES(7,'Hummer',41400);
INSERT INTO Cars VALUES(8,'Volkswagen',21600);

CREATE TABLE Orders(Id integer PRIMARY KEY, OrderPrice integer CHECK(OrderPrice>0), Customer text);
INSERT INTO Orders(OrderPrice, Customer) VALUES(1200, 'Williamson');
INSERT INTO Orders(OrderPrice, Customer) VALUES(200, 'Robertson');
INSERT INTO Orders(OrderPrice, Customer) VALUES(40, 'Robertson');
INSERT INTO Orders(OrderPrice, Customer) VALUES(1640, 'Smith');
INSERT INTO Orders(OrderPrice, Customer) VALUES(100, 'Robertson');
INSERT INTO Orders(OrderPrice, Customer) VALUES(50, 'Williamson');
INSERT INTO Orders(OrderPrice, Customer) VALUES(150, 'Smith');
INSERT INTO Orders(OrderPrice, Customer) VALUES(250, 'Smith');
INSERT INTO Orders(OrderPrice, Customer) VALUES(840, 'Brown');
INSERT INTO Orders(OrderPrice, Customer) VALUES(440, 'Black');
INSERT INTO Orders(OrderPrice, Customer) VALUES(20, 'Brown');

在命令行执行 .read data.sql 即可将数据加载。

1. 限制数量与偏移 LIMIT 与 OFFSET

当数据量过大时，通常只需返回部分数据。

限制数量： 仅返回前 4 辆车。

sqlite> SELECT * FROM Cars LIMIT 4;

分页查询： 跳过前 2 条，从第 3 条开始返回 4 条数据（常用于实现“上一页/下一页”）。注意索引是从 0 开始的。

sqlite> SELECT * FROM Cars LIMIT 4 OFFSET 2;

2. 列别名 AS

改变输出结果的标题，使报表更易读：

sqlite> SELECT Name , Cost AS 'Price Of Car' FROM Cars;

3. 条件查询 WHERE

(1) 字符串模糊匹配 LIKE 和 GLOB

• LIKE：不区分大小写。使用 _ 匹配单个字符，% 匹配任意多个字符。

-- 查找名称恰好为 4 个字母的汽车（Audi）
sqlite> SELECT * FROM Cars WHERE Name LIKE '____';

-- 查找名称以 en 结尾的汽车（Citroen, Volkswagen）
sqlite> SELECT * FROM Cars WHERE Name LIKE '%en';
-- 即使输入 %EN 也能查出同样结果，因为不区分大小写

• GLOB：区分大小写。使用 ? 匹配单字符，* 匹配多字符。

(2) 范围判断 BETWEEN

找出价格在 20000 到 55000 之间的汽车：

sqlite> SELECT * FROM Cars WHERE Cost BETWEEN 20000 AND 55000;

(3) 集合匹配 IN

找出特定的品牌：

sqlite> SELECT * FROM Cars WHERE Name IN ('Audi','Hummer');

4. 排序 ORDER BY

按特定列排序，默认升序 ASC，降序使用 DESC：

sqlite> SELECT Name, Cost FROM Cars ORDER BY Cost DESC;
-- 从昂贵的 Bentley 排到便宜的 Skoda

5. 去重 DISTINCT

在订单中，同一个客户下了多次订单。要获取下单客户的唯一名单，可使用 DISTINCT：

sqlite> SELECT DISTINCT Customer FROM Orders;
-- 返回: Black, Brown, Robertson, Smith, Williamson

6. 分组统计 GROUP BY 与 HAVING

GROUP BY 用于将数据按某列分类，常配合聚合函数（如 SUM, COUNT, AVG）使用。

计算每位客户的总消费金额：

sqlite> SELECT sum(OrderPrice) AS Total, Customer FROM Orders GROUP BY Customer;

HAVING 用于对分组后的结果进行二次筛选（类似针对聚合数据的 WHERE）：

只找出总支出超过 1000 的客户：

sqlite> SELECT sum(OrderPrice) AS Total, Customer FROM Orders 
   ...>   GROUP BY Customer HAVING sum(OrderPrice)>1000;

六、 综合示例与注意事项

在实际开发中，经常需要将上述运算符组合使用。SQLite 支持标准的逻辑运算符 AND、OR、NOT 来构建复杂的检索条件。同时请记住：

• 在涉及大量删除 (DELETE) 或覆盖性更新 (UPDATE) 前，务必备份数据库文件或使用事务保护。
• 理解 WHERE、GROUP BY 和 HAVING 的作用顺序是避免报错的关键（先过滤行，再分组，最后过滤组）。

以上就是 SQLite 增删改查（INSERT/UPDATE/DELETE/SELECT）的详细内容，更多关于 SQLite 进阶优化与实战的资料请关注本站其它相关文章！