ARTICLE DETAIL

建站实战干货

来自一线的建站与推广经验沉淀，每一条都经过真实交付验证。

首页 / 资讯中心 / 正文

嵌入式学习-C语言操作SQLite数据库

2026/8/17 18:47:49 拓冰建站 浏览量
嵌入式学习-C语言操作SQLite数据库 一、安装与编译1. 安装SQLite3sudo apt install sqlite3 # 命令行工具 sudo apt install libsqlite3-dev # 开发库C语言编程需要2. 编译C程序gcc xxx.c -o xxx -lsqlite3三、核心API1. 打开/关闭数据库sqlite3 *db; if (sqlite3_open(test.db, db) ! SQLITE_OK) { fprintf(stderr, 打开失败: %s\n, sqlite3_errmsg(db)); exit(EXIT_FAILURE); } // ... 操作 ... sqlite3_close(db);2. 执行SQL语句sqlite3_exec适合执行不返回结果集的SQL创建表、插入、更新、删除。char *sql CREATE TABLE IF NOT EXISTS tb_user(id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT, password TEXT); char *errmsg NULL; if (sqlite3_exec(db, sql, NULL, NULL, errmsg) ! SQLITE_OK) { fprintf(stderr, SQL错误: %s\n, errmsg); sqlite3_free(errmsg); } else { printf(执行成功受影响行数: %d\n, sqlite3_changes(db)); }回调函数处理查询结果适用于查询int callback(void *data, int cols, char **values, char **colNames) { for (int i 0; i cols; i) { printf(%s %s\n, colNames[i], values[i]); } return 0; } sqlite3_exec(db, SELECT * FROM tb_user, callback, NULL, errmsg);3. 预编译语句sqlite3_prepare_v2推荐方式防止SQL注入性能更好适合增删改查。步骤sqlite3_prepare_v2(db, sql, -1, stmt, NULL)编译SQL。使用sqlite3_bind_*绑定参数占位符用?。执行sqlite3_step(stmt)。增删改返回SQLITE_DONE。查询返回SQLITE_ROW表示有数据循环调用直到返回SQLITE_DONE。获取列值sqlite3_column_int,sqlite3_column_text,sqlite3_column_double等。释放sqlite3_finalize(stmt)。示例插入数据绑定参数char *sql INSERT INTO tb_user VALUES(NULL, ?, ?); sqlite3_stmt *stmt; sqlite3_prepare_v2(db, sql, -1, stmt, NULL); sqlite3_bind_text(stmt, 1, admin, -1, SQLITE_STATIC); sqlite3_bind_text(stmt, 2, 123456, -1, SQLITE_STATIC); if (sqlite3_step(stmt) SQLITE_DONE) { printf(插入成功受影响行数: %d\n, sqlite3_changes(db)); } sqlite3_finalize(stmt);示例查询数据char *sql SELECT id, username FROM tb_user WHERE id ?; sqlite3_stmt *stmt; sqlite3_prepare_v2(db, sql, -1, stmt, NULL); sqlite3_bind_int(stmt, 1, 1); while (sqlite3_step(stmt) SQLITE_ROW) { int id sqlite3_column_int(stmt, 0); const char *name sqlite3_column_text(stmt, 1); printf(id%d, name%s\n, id, name); } sqlite3_finalize(stmt);示例更新数据char *sql UPDATE tb_user SET password ? WHERE id ?; sqlite3_stmt *stmt; sqlite3_prepare_v2(db, sql, -1, stmt, NULL); sqlite3_bind_text(stmt, 1, newpass, -1, SQLITE_STATIC); sqlite3_bind_int(stmt, 2, 1); if (sqlite3_step(stmt) SQLITE_DONE) { printf(更新成功受影响行数: %d\n, sqlite3_changes(db)); } sqlite3_finalize(stmt);示例删除数据char *sql DELETE FROM tb_user WHERE id ?; sqlite3_stmt *stmt; sqlite3_prepare_v2(db, sql, -1, stmt, NULL); sqlite3_bind_int(stmt, 1, 1); if (sqlite3_step(stmt) SQLITE_DONE) { printf(删除成功受影响行数: %d\n, sqlite3_changes(db)); } sqlite3_finalize(stmt);四、完整项目实战实战1用户登录验证bool login(char *username, char *password) { sqlite3 *db; sqlite3_open(stu.db, db); char sql[200]; sprintf(sql, SELECT * FROM tb_user WHERE username%s AND password%s, username, password); // 注意实际开发应使用预编译语句防止SQL注入 bool flag false; sqlite3_stmt *stmt; sqlite3_prepare_v2(db, sql, -1, stmt, NULL); if (sqlite3_step(stmt) SQLITE_ROW) flag true; sqlite3_finalize(stmt); sqlite3_close(db); return flag; }实战2员工管理系统含部门关联表结构参考附件CREATE TABLE department ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, location TEXT, create_time DATETIME DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE employee ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, gender TEXT, age INTEGER, salary REAL, phone TEXT, hire_date DATE, dept_id INTEGER, FOREIGN KEY (dept_id) REFERENCES department(id) );查询所有员工含部门信息使用JOINEmployee* findAllEmployees(int *count) { sqlite3 *db; sqlite3_open(employee.db, db); char *sql SELECT e.id, e.name, e.gender, e.age, e.salary, e.phone, e.hire_date, d.name AS dept_name, d.location FROM employee e LEFT JOIN department d ON e.dept_id d.id; sqlite3_stmt *stmt; sqlite3_prepare_v2(db, sql, -1, stmt, NULL); // 先计数 *count 0; while (sqlite3_step(stmt) SQLITE_ROW) (*count); sqlite3_reset(stmt); Employee *emp malloc(sizeof(Employee) * (*count)); Employee *p emp; while (sqlite3_step(stmt) SQLITE_ROW) { p-id sqlite3_column_int(stmt, 0); strcpy(p-name, sqlite3_column_text(stmt, 1)); strcpy(p-gender, sqlite3_column_text(stmt, 2)); p-age sqlite3_column_int(stmt, 3); p-salary sqlite3_column_double(stmt, 4); strcpy(p-phone, sqlite3_column_text(stmt, 5)); // 日期字段用sscanf解析 sscanf(sqlite3_column_text(stmt, 6), %d-%d-%d, p-hire_date.year, p-hire_date.month, p-hire_date.day); strcpy(p-dept_name, sqlite3_column_text(stmt, 7)); strcpy(p-location, sqlite3_column_text(stmt, 8)); p; } sqlite3_finalize(stmt); sqlite3_close(db); return emp; }实战3小说管理系统含分类关联表结构CREATE TABLE tb_novel_type ( novel_type_id INTEGER PRIMARY KEY AUTOINCREMENT, novel_type_name TEXT NOT NULL ); CREATE TABLE tb_novel ( novel_id INTEGER PRIMARY KEY AUTOINCREMENT, novel_title TEXT NOT NULL, novel_author TEXT NOT NULL, novel_type_id INTEGER, novel_score INTEGER, novel_replay INTEGER, novel_process INTEGER, novel_uptime DATE, FOREIGN KEY (novel_type_id) REFERENCES tb_novel_type(novel_type_id) );添加小说使用预编译语句bool insertNovel(Novel *novel) { sqlite3 *db; sqlite3_open(novel.db, db); char *sql INSERT INTO tb_novel VALUES(NULL, ?, ?, ?, ?, ?, ?, ?); sqlite3_stmt *stmt; sqlite3_prepare_v2(db, sql, -1, stmt, NULL); sqlite3_bind_text(stmt, 1, novel-title, -1, SQLITE_STATIC); sqlite3_bind_text(stmt, 2, novel-author, -1, SQLITE_STATIC); sqlite3_bind_int(stmt, 3, novel-type_id); sqlite3_bind_int(stmt, 4, novel-score); sqlite3_bind_int(stmt, 5, novel-replay); sqlite3_bind_int(stmt, 6, novel-process); char dateStr[20]; sprintf(dateStr, %d-%02d-%02d, novel-uptime.year, novel-uptime.month, novel-uptime.day); sqlite3_bind_text(stmt, 7, dateStr, -1, SQLITE_STATIC); bool flag (sqlite3_step(stmt) SQLITE_DONE); sqlite3_finalize(stmt); sqlite3_close(db); return flag; }五.sprintf– 格式化输出到字符串函数原型int sprintf(char *str, const char *format, ...);功能将格式化的数据写入字符串str中类似于printf打印到屏幕但这里写入字符数组。参数str目标字符数组缓冲区用于存储格式化后的字符串。format格式控制字符串与printf相同如%d,%s,%f等。...可变参数待格式化的数据。返回值成功时返回写入的字符总数不包括结尾的\0。失败返回负数。示例char buf[100]; int age 25; char name[] 张三; sprintf(buf, 姓名%s年龄%d, name, age); // buf 内容姓名张三年龄25注意事项缓冲区溢出必须确保str足够大以容纳格式化后的字符串否则会导致缓冲区溢出危险。安全替代函数是snprintf可以指定最大写入长度。自动添加\0sprintf会在字符串末尾自动添加\0。2.sscanf– 从字符串中格式化读取函数原型int sscanf(const char *str, const char *format, ...);功能从字符串str中按照格式format读取数据存入后续变量中类似于scanf从键盘读取但这里从字符串读取。参数str源字符串从中解析数据。format格式控制字符串与scanf相同如%d,%s,%f等。...可变参数用于存储读取到的数据的变量的地址指针。返回值成功时返回成功匹配并赋值的参数个数。失败返回 0 或 EOF-1。示例char data[] login-admin-123; char opr[32], username[32], password[32]; sscanf(data, %[^-]-%[^-]-%[^-\n], opr, username, password); // 结果oprlogin, usernameadmin, password123格式说明符补充%[^-]读取除-以外的所有字符集合匹配。%[^-\n]读取除-和换行符以外的所有字符。%*d跳过整数不存储。%[a-z]只读取小写字母。注意事项变量必须传地址对于基本类型int, float 等要加对于字符数组数组名本身就是地址。匹配失败如果格式不匹配sscanf会停止并返回已成功匹配的项目数。空白字符sscanf会自动跳过空白字符空格、制表符、换行除非使用%c或%[]。缓冲区溢出使用%s读取字符串时如果目标数组不够大可能导致溢出。最好使用%Ns指定最大长度如%31s最多读 31 个字符留一个给\0。