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

一、SQLite3 基础认知1.1 什么是数据库数据库是用于存储、管理、统计数据的应用程序常与服务器搭配使用。在数据库中数据以 ** 表Table** 的形式组织一行 一条记录Record一列 一个字段Field1.2 数据库分类按规模可分为大型、中型、小型数据库常见关系型数据库如下规模代表数据库大型ORACLE、DB2中型MySQL、MSSQL小型SQLite、powdb1.3 核心名词解释DBDatabase数据库存储数据的集合核心操作包括select查询、update更新DBMSDatabase Management System数据库管理系统用于管理数据库的软件MIS管理信息系统基于数据库的业务系统OA办公自动化企业常用的信息化系统1.4 嵌入式数据库 SQLite3 特点SQLite3 是轻量级嵌入式关系型数据库核心特点如下开源C 语言开发完全开源免费轻量核心代码仅 1 万行左右总大小 10M 以内免安装绿色软件无需配置环境直接使用文件型整个数据库就是一个.db文件可自由移动、拷贝大容量单文件最大支持 2TB 数据存储跨平台支持 Linux、Windows、嵌入式设备等多平台二、SQL 语言基础SQLStructured Query Language结构化查询语言是操作关系型数据库的标准语言在 SQLite3 中按功能分为三大类分类全称核心功能常用操作DDLData Definition Language数据定义CREATE TABLE建表、DROP TABLE删表DMLData Manipulation Language数据操作INSERT新增、UPDATE修改、DELETE删除DQLData Query Language数据查询SELECT查询三、SQLite3 安装与基础操作Linux 系统3.1 安装Linux 系统执行以下命令完成安装# 安装SQLite3命令行工具 sudo apt-get install sqlite3 # 安装C语言开发依赖库 sudo apt-get install libsqlite3-dev3.2 启动与退出# 启动打开/创建名为aaa.db的数据库文件不存在则自动创建 sqlite3 aaa.db # 退出交互环境三种方式等价 .exit .quit .q3.3 SQLite3 终端常用指令进入sqlite3交互环境后常用指令如下指令功能说明.database列出当前数据库关联的物理文件路径.tables列出当前数据库中所有已创建的表.schema 表名查看指定表的结构不写表名则显示所有表结构.headers on查询数据时显示列名方便查看字段对应关系.exit/.quit/.q退出 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; -- 多条件or4. 删除数据DELETE-- 语法delete from 表名 where 条件; delete from user; -- 删除表中所有数据清空表 delete from user where id 1; -- 删除id1的数据 delete from user where id 1 and name zhang; -- 多条件and delete from user where id 1 or id 2; -- 多条件or4.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.sql2. 数据导入恢复# 把xxx.sql脚本导入到xxx.db数据库 sqlite3 xxx.db xxx.sql五、SQLite3 C 语言编程实战5.1 编程核心步骤SQLite3 C 语言编程遵循固定流程打开数据库 读写数据库增/删/改/查 关闭数据库5.2 核心函数详解1. 打开数据库sqlite3_openint sqlite3_open(char * path, sqlite3 ** db);功能打开指定路径的数据库不存在则自动创建参数path数据库文件路径 名称db数据库句柄指针用于后续操作返回值成功返回SQLITE_OK(0)失败返回-12. 关闭数据库sqlite3_closeint sqlite3_close(sqlite3 *db);功能关闭指定数据库释放资源参数db已打开的数据库句柄返回值成功返回SQLITE_OK(0)失败返回-13. 执行 SQL 语句sqlite3_execint sqlite3_exec(sqlite3 *db, char *sql, callback fun, void * arg, char ** errmsg);功能在指定数据库上执行 SQL 语句参数db数据库句柄sql要执行的 SQL 语句字符串fun查询语句的回调函数非查询语句传 NULLarg回调函数的入参无则传 NULLerrmsg错误信息指针执行失败时返回错误原因返回值成功返回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(cant find\n); } } // 3. 关闭数据库释放资源 sqlite3_close(db); return 0; }六、总结SQLite3 作为轻量级嵌入式数据库凭借开源、轻量、免安装、文件型的特点成为嵌入式设备、小型应用、本地存储的首选数据库。本文从基础概念、SQL 语句、C 语言编程三个维度完整覆盖了 SQLite3 的核心知识点从入门到实战可直接用于嵌入式 Linux 项目开发。核心要点回顾批量操作必须开启事务否则效率极低主键查询比普通字段快优先用主键作为查询条件数据库操作遵循「打开 - 操作 - 关闭」的固定流程SQLite3 是文件型数据库备份 / 迁移直接拷贝.db文件即可