Linux系统编程(六) ---- 数据库 SQLite3

一、SQLite3 基础认知

1.1 什么是数据库

数据库是用于存储、管理、统计数据的应用程序，常与服务器搭配使用。在数据库中，数据以 ** 表（Table）** 的形式组织：

• 一行 = 一条记录（Record）
• 一列 = 一个字段（Field）

1.2 数据库分类

按规模可分为大型、中型、小型数据库，常见关系型数据库如下：

1.3 核心名词解释

• DB（Database）：数据库，存储数据的集合，核心操作包括select（查询）、update（更新）
• DBMS（Database Management System）：数据库管理系统，用于管理数据库的软件
• MIS：管理信息系统，基于数据库的业务系统
• OA：办公自动化，企业常用的信息化系统

1.4 嵌入式数据库 SQLite3 特点

SQLite3 是轻量级嵌入式关系型数据库，核心特点如下：

1. 开源：C 语言开发，完全开源免费
2. 轻量：核心代码仅 1 万行左右，总大小 10M 以内
3. 免安装：绿色软件，无需配置环境，直接使用
4. 文件型：整个数据库就是一个.db文件，可自由移动、拷贝
5. 大容量：单文件最大支持 2TB 数据存储
6. 跨平台：支持 Linux、Windows、嵌入式设备等多平台

二、SQL 语言基础

SQL（Structured Query Language，结构化查询语言）是操作关系型数据库的标准语言，在 SQLite3 中按功能分为三大类：

三、SQLite3 安装与基础操作（Linux 系统）

3.1 安装（Linux 系统）

执行以下命令完成安装：

# 安装SQLite3命令行工具 sudo apt-get install sqlite3 # 安装C语言开发依赖库 sudo apt-get install libsqlite3-dev

3.2 启动与退出

# 启动：打开/创建名为aaa.db的数据库（文件不存在则自动创建） sqlite3 aaa.db # 退出交互环境（三种方式等价） .exit .quit .q

3.3 SQLite3 终端常用指令

进入sqlite3>交互环境后，常用指令如下：

四、常用 SQL 语句实战

4.1 表结构操作

1. 创建表

-- 语法：create table 表名 (字段1 类型, 字段2 类型, ...); create table user(id int, name char, age int);

2. 删除表（谨慎操作，数据会被清空）

-- 语法：drop table 表名; drop table user;

4.2 数据增删改查

1. 插入数据（INSERT）

-- 语法1：指定字段插入（推荐，可读性高） insert into user (id, age) values (1, 10); -- 语法2：全字段插入（顺序必须与建表一致） insert into user values(3, "wang", 11); -- 语法3：部分字段插入（未指定字段为NULL） insert into user (age) values (12);

2. 查询数据（SELECT）

-- 查询所有列（*代表所有字段） select * from user; -- 查询指定列 select id from user; select id, name from user; -- 条件查询（where子句） select id, name from user where not age < 30; -- 通配符模糊查询 -- %：匹配任意多个任意字符；_：匹配1个任意字符 select * from user where name like '张_' and id <5; -- 匹配姓张且名字2个字的用户 select * from user where name like '%张%' and id <5; -- 匹配名字包含张的用户

3. 修改数据（UPDATE）

-- 语法：update 表名 set 字段=值 where 条件; update user set id = 1 where name = 'li'; -- 把name为li的用户id改为1 update user set id = 1 where name = "li" and passwd = "123"; -- 多条件and update user set id = 2 where name = "li" or name = "zhao"; -- 多条件or

4. 删除数据（DELETE）

-- 语法：delete from 表名 where 条件; delete from user; -- 删除表中所有数据（清空表） delete from user where id = 1; -- 删除id=1的数据 delete from user where id =1 and name = "zhang"; -- 多条件and delete from user where id = 1 or id = 2; -- 多条件or

4.3 进阶功能

1. 时间列操作

-- 创建带时间字段的表 CREATE TABLE user1(id int, name char, age int, dt datetime); -- 插入当前时间（+8小时为北京时间） insert into user1 values (2,'张三',23,datetime('now','+8 hours'));

2. 自动增长列（主键）

-- 创建带自增主键的表（INTEGER PRIMARY KEY ASC 自动递增） CREATE TABLE user3(id INTEGER PRIMARY KEY ASC, name char, age int, dt datetime); -- 插入数据时id传NULL，自动生成递增id insert into user3 values (NULL,'李四',23,datetime('now'));

说明：PRIMARY KEY是主键，保证唯一性，用主键作为查询条件比普通字段更快。

4.4 数据库导入导出

1. 数据导出（备份）

# 把xxx.db数据库整体导出为xxx.sql脚本 sqlite3 xxx.db .dump > xxx.sql

2. 数据导入（恢复）

# 把xxx.sql脚本导入到xxx.db数据库 sqlite3 xxx.db < xxx.sql

五、SQLite3 C 语言编程实战

5.1 编程核心步骤

SQLite3 C 语言编程遵循固定流程：打开数据库 ==> 读写数据库（增/删/改/查） ==> 关闭数据库

5.2 核心函数详解

1. 打开数据库：sqlite3_open

int sqlite3_open(char * path, sqlite3 ** db);

• 功能：打开指定路径的数据库，不存在则自动创建
• 参数：
  • path：数据库文件路径 + 名称
  • db：数据库句柄指针（用于后续操作）
• 返回值：成功返回SQLITE_OK(0)，失败返回-1

2. 关闭数据库：sqlite3_close

int sqlite3_close(sqlite3 *db);

• 功能：关闭指定数据库，释放资源
• 参数：db：已打开的数据库句柄
• 返回值：成功返回SQLITE_OK(0)，失败返回-1

3. 执行 SQL 语句：sqlite3_exec

int sqlite3_exec(sqlite3 *db, char *sql, callback fun, void * arg, char ** errmsg);

• 功能：在指定数据库上执行 SQL 语句
• 参数：
  • db：数据库句柄
  • sql：要执行的 SQL 语句字符串
  • fun：查询语句的回调函数（非查询语句传 NULL）
  • arg：回调函数的入参（无则传 NULL）
  • errmsg：错误信息指针（执行失败时返回错误原因）
• 返回值：成功返回SQLITE_OK(0)，失败返回非 0 值

5.3 基础示例：数据插入

#include <stdio.h> #include <stdlib.h> #include <string.h> #include <sqlite3.h> int main(int argc, char **argv) { sqlite3 *db = NULL; // 1. 打开数据库（相当于Linux中的文件描述符） int ret = sqlite3_open("aaa.db", &db); if (SQLITE_OK != ret) { fprintf(stderr, "open db error %s\n", sqlite3_errstr(ret)); sqlite3_close(db); return 1; } char *errmsg = NULL; // 要执行的SQL语句（非查询语句，无需回调） char sql_cmd[512] = "insert into user values(7,'帅哥',20)"; // 2. 执行SQL语句 ret = sqlite3_exec(db, sql_cmd, NULL, NULL, &errmsg); if (SQLITE_OK != ret) { fprintf(stderr, "sqlite3_exec sql_cmd:[%s] ,%s\n", sql_cmd, errmsg); sqlite3_free(errmsg); sqlite3_close(db); return 1; } // 3. 关闭数据库，释放资源 sqlite3_close(db); return 0; }

5.4 进阶示例：批量插入 + 查询（事务优化）

1. SQLite 事务（Transaction）

事务是数据库的核心特性，遵循ACID 原则：

• 原子性（Atomicity）：事务内所有操作要么全部成功，要么全部回滚
• 一致性（Consistency）：事务执行前后，数据库状态保持一致
• 隔离性（Isolation）：多个事务互不干扰
• 持久性（Durability）：事务提交后，数据永久保存

批量插入时，若不开启事务，每条插入都会自动开启 / 关闭事务，效率极低；开启事务后，批量操作一次性提交，效率提升 100 倍以上。

2. 完整代码示例

#include <stdio.h> #include <stdlib.h> #include <string.h> #include <sqlite3.h> // 查询回调函数：处理select语句的结果 int find_word(void* arg, int col, char** result, char** title) { *(int*)arg = 1; // 标记查询到数据 printf("%s:%s\n", result[1], result[2]); // 打印单词和释义 return 0; } int main(int argc, char **argv) { sqlite3 *db = NULL; // 1. 打开数据库 int ret = sqlite3_open("aaa.db", &db); if (SQLITE_OK != ret) { fprintf(stderr, "open db error %s\n", sqlite3_errstr(ret)); sqlite3_close(db); return 1; } char sql_cmd[1024] = {0}; char *errmsg = NULL; // 创建字典表（不存在则创建） strcpy(sql_cmd, "create table IF NOT EXISTS dict(id INTEGER PRIMARY KEY ASC,word char,mean text);"); ret = sqlite3_exec(db, sql_cmd, NULL, NULL, &errmsg); if (SQLITE_OK != ret) { fprintf(stderr, "sqlite3_exec sql_cmd:[%s] ,%s\n", sql_cmd, errmsg); sqlite3_free(errmsg); sqlite3_close(db); return 1; } // 清空表中旧数据 strcpy(sql_cmd, "delete from dict"); ret = sqlite3_exec(db, sql_cmd, NULL, NULL, &errmsg); if (SQLITE_OK != ret) { fprintf(stderr, "sqlite3_exec sql_cmd:[%s] ,%s\n", sql_cmd, errmsg); sqlite3_free(errmsg); sqlite3_close(db); return 1; } // 开启事务，批量插入优化 strcpy(sql_cmd, "BEGIN TRANSACTION;"); ret = sqlite3_exec(db, sql_cmd, NULL, NULL, &errmsg); if (SQLITE_OK != ret) { fprintf(stderr, "sqlite3_exec sql_cmd:[%s] ,%s\n", sql_cmd, errmsg); sqlite3_free(errmsg); sqlite3_close(db); return 1; } // 从字典文件批量插入数据 FILE *fp = fopen("/home/linux/dict.txt", "r"); if (NULL == fp) { perror("fopen"); return 1; } int i = 1; while (1) { char line_buf[1024] = {0}; if (NULL == fgets(line_buf, sizeof(line_buf), fp)) { break; } // 分割单词和释义 char *word = strtok(line_buf, " "); char *mean = strtok(NULL, "\r\n"); // 拼接插入SQL sprintf(sql_cmd, "insert into dict values(NULL,\"%s\",\"%s\");", word, mean); ret = sqlite3_exec(db, sql_cmd, NULL, NULL, &errmsg); if (SQLITE_OK != ret) { fprintf(stderr, "sqlite3_exec sql_cmd:[%s] ,%s\n", sql_cmd, errmsg); sqlite3_free(errmsg); sqlite3_close(db); fclose(fp); return 1; } // printf("num:%d\n", i++); } fclose(fp); // 提交事务，保存所有插入 strcpy(sql_cmd, "COMMIT;"); ret = sqlite3_exec(db, sql_cmd, NULL, NULL, &errmsg); if (SQLITE_OK != ret) { fprintf(stderr, "sqlite3_exec sql_cmd:[%s] ,%s\n", sql_cmd, errmsg); sqlite3_free(errmsg); sqlite3_close(db); return 1; } // 交互式查询 while (1) { printf("input word:"); char word[50] = {0}; fgets(word, sizeof(word), stdin); word[strlen(word) - 1] = '\0'; // 去除换行符 if(0 == strcmp("#quit",word)) { break; } int flag = 0; // 模糊查询单词 sprintf(sql_cmd,"select * from dict where word like \"%s\";",word); ret = sqlite3_exec(db, sql_cmd, find_word,&flag , &errmsg); if (SQLITE_OK != ret) { fprintf(stderr, "sqlite3_exec sql_cmd:[%s] ,%s\n", sql_cmd, errmsg); sqlite3_free(errmsg); sqlite3_close(db); return 1; } if(0 == flag) { printf("can't find\n"); } } // 3. 关闭数据库，释放资源 sqlite3_close(db); return 0; }

六、总结

SQLite3 作为轻量级嵌入式数据库，凭借开源、轻量、免安装、文件型的特点，成为嵌入式设备、小型应用、本地存储的首选数据库。本文从基础概念、SQL 语句、C 语言编程三个维度，完整覆盖了 SQLite3 的核心知识点，从入门到实战，可直接用于嵌入式 Linux 项目开发。

核心要点回顾：

1. 批量操作必须开启事务，否则效率极低
2. 主键查询比普通字段快，优先用主键作为查询条件
3. 数据库操作遵循「打开 - 操作 - 关闭」的固定流程
4. SQLite3 是文件型数据库，备份 / 迁移直接拷贝.db文件即可