本文介绍了如何使用SQLite3进行数据库操作，包括下载SQLite3、创建数据库test.db、建立数据表temp_info，以及插入、查询、更新和删除数据。通过示例代码详细展示了如何使用C语言接口执行SQL语句，例如插入数据、更新数据的结构体转换等操作。同时，还提供了在命令行查看数据的方法。
sqlite3的使用
一、sqlite3下载
我是直接在虚拟机上测试的，sqlite3官网下载的离线安装包，下载后解压即可，文件test.c是在解压目录中测试的
如下图，右侧框是一些API的介绍
二、sqlite3命令行基础语法
在此之前你需要先编译一下sqlite3才能使用命令行工具，参考这篇博文
命令行使用在文章开头有链接。
下面就当你会使用命令行和一些API了
三、工作要求及实现
1、要求
先说一下，数据库，其实没什么神秘的，你就当它是一个简陋的excle工具！用来存储一些数据信息的东东
设计一个数据库，可以写入和读取以下内容
1、测温信息（类型、坐标、发射率，距离，报警温度）
2、云台预制点（关联的测温信息）
这要求不就存储一些数据嘛，有啥难的！
2、实现
下面就是一个设计好的表，第一行是表头，第二行是数据，就那么简单（数据是我乱写的，测试用的）
Preset Type Idx Coordinate Emissivity Distance Alarm
------ ---- --- ------------- ---------- -------- -----
1 2 1 0(1,2) 2(3,3) 22.0 22.0 22.0
1 2 1 0(1,2) 2(3,3) 22.0 22.0 22.0
2.1创建数据库test.db
有数据库才有数据表，数据库是目录中真实存在的一个文件，如下图，创建后可以ls看到该文件
使用函数sqlite3_open(file, &db);来创建，参数file就是库名，db是一个sqlite3指针，这个指针有点文件句柄那意味，数据表的增删改都需要这个“句柄”
返回值：0表示成功，非0表示失败
这代码内部也只是调用了官方APIsqlite3_open进行封装一下，若数据库不存在则创建，存在则打开
怎么判断创建成功了呢
方法一：直接在目录中看看有没有对应的文件
方法二：有官方API可以调用，偷个懒，我用过但被我删了，你们自己去找吧
static sqlite3* sql_open()
{
int ret, cnt;
char file[64];
sqlite3 *db;
snprintf(file, sizeof(file), "%s", SQL_NAME);
cnt = 0;
__OPEN_AG__:
ret = sqlite3_open(file, &db);
if(ret){
if(cnt++ >= 3){
printf("%s(), can't open %s\n", __FUNCTION__, file);
sqlite3_close(db);
return NULL;
}else{
printf("%s(), try open %s, cnt=%d\n", __FUNCTION__, file, cnt);
goto __OPEN_AG__;//__OPEN_AG__前有个向上的箭头不显示，复制到代码工具就可以看到了
}
}
printf("%s, file = %s\n", __FUNCTION__, file);
return db;
}
2.2创建表
sql_table_create(sqlite3 *db)函数，参数就一个，上一步生成的db，也是封装一下
下面两个snprintf也只是拼接一些字符串而已，打印出字符串就是
create table temp_info (Preset INTEGER, Type INTEGER, Idx INTEGER, Coordinate TEXT, Emissivity REAL, Distance REAL, Alarm REAL);
这句sql语句表示创建一个数据表temp_info，表头有Preset Type Idx等等，INTEGER，TEXT，REAL分别表示要存储的数据类型为整形，字符串，浮点数，
紧接着执行sqlite3_exec(db, sql, NULL, NULL, &err);这个函数就是把上边的sql语句执行了…简单吧
提一嘴，sql语句在sqlite3的命令行中可以直接执行
static void sql_table_create(sqlite3 *db)
{
int ret;
char sql[256];
char para[256];
char *err;
snprintf(para, sizeof(para), "(%s INTEGER, %s INTEGER, %s INTEGER, %s TEXT, %s REAL, %s REAL, %s REAL);",
SQL_PRESET, SQL_TYPE, SQL_IDX, SQL_COORDINATE, SQL_EMISSIVITY, SQL_DISTANCE, SQL_ALARM_TEMP);
snprintf(sql, sizeof(sql), "create table %s %s", SQL_TABLE_NAME, para);
printf("%s: %s\n", __FUNCTION__, sql);
ret = sqlite3_exec(db, sql, NULL, NULL, &err);
if(ret != 0){
printf("%s: sqlite3_exec failed! ret = %d",__FUNCTION__, ret);
}
}
2.3插入数据
该函数参数就是db和表头需要的数据，然后拼接字符串去执行
INSERT INTO "temp_info" VALUES(1, 1, 1, "0(1,2) 2(3,3)", 22.000000, 22.000000, 22.000000);
该sql语句也很简单，在temp_info中插入值，值在VALUES的括号内，跟表头数据类型对应即可
static int sql_table_insert(sqlite3 *db, int preset, int type, int idx, char* coord, float emiss, float dis, float alarm)
{
int ret;
char sql[256] = {};
char judge[256] = {};
sql_table_create(db);
/* 数据表--插入 */
snprintf(sql, sizeof(sql), "%s \"%s\" VALUES(%d, %d, %d, \"%s\", %f, %f, %f);", "INSERT INTO", SQL_TABLE_NAME,
preset, type, idx, coord, emiss, dis, alarm);
printf("%s: %s\n", __FUNCTION__,sql);
ret = sqlite3_exec(db, sql, NULL, NULL, NULL);
return ret;
}
2.3.1命令行查看数据
1、输入sqlite3进入命令行
2、.open test.db打开数据库
3、.table查看当前数据库下有哪些表
4、为了显示对齐，使用.header on和.mode column
5、select * from temp_info;显示temp_info表所有内容，*是通配符
其余命令：drop table table_name----删除表
.databases----查看数据库的情况
book@100ask:~/code/sqlite3/sqlite-autoconf-3390400$ sqlite3
SQLite version 3.39.4 2022-09-29 15:55:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open test.db
sqlite> .table
temp_info
sqlite> .header on
sqlite> .mode column
sqlite> select * from temp_info;
Preset Type Idx Coordinate Emissivity Distance Alarm
------ ---- --- ------------- ---------- -------- -----
1 1 1 0(1,2) 2(3,3) 22.0 22.0 22.0
2.4数据更新
跟前面一样的套路，拼接字符串然执行，差异在sql语句上
update temp_info set Coordinate = "0(1,2) 2(3,3)", Emissivity = 22.000000, Distance = 22.000000, Alarm = 22.000000 where Preset = 1 and Type = 2 and Idx = 1;
该句表示更新表temp_info中的Coordinate, Emissivity, Distance, Alarm值，在哪一行呢？
在Preset = 1 and Type = 2 and Idx = 1即Preset等于1，Type等于2，Idx等于1的那一行，中间用and连接。上一步插入的数据不符合这条件，就更新失败了呗！
我记得当时找教程的时候，教程中只有一个条件来判断是哪一行，我这里有仨，于是我用了&& & ，三种符号来尝试连接，结果都不行，多百度一会儿才找到，我怎么就没想到and呢！
2.5数据删除
DELETE FROM temp_info WHERE Preset = 1 and Type = 1 and Idx = 1;
从表temp_info中删除符合Preset = 1 and Type = 1 and Idx = 1的行，当然你的条件可以使一个，也可以比这个更多
2.6整体代码
#include <sqlite3.h>
#include <stdio.h>
#include <stdlib.h>
#include <unistd.h>
#define SQL_NAME "test.db"
#define SQL_TABLE_NAME "temp_info"
#define SQL_PRESET "Preset"
#define SQL_TYPE "Type"
#define SQL_IDX "Idx"
#define SQL_COORDINATE "Coordinate"
#define SQL_EMISSIVITY "Emissivity"
#define SQL_DISTANCE "Distance"
#define SQL_ALARM_TEMP "Alarm"
#define SQL_DELETE_MODE0 0
#define SQL_DELETE_MODE1 1
/* 创建数据表
**/
static void sql_table_create(sqlite3 *db)
{
int ret;
char sql[256];
char para[256];
char *err;
/* 创建数据表，flag=1创建预置点表，=0创建温度信息表 */
snprintf(para, sizeof(para), "(%s INTEGER, %s INTEGER, %s INTEGER, %s TEXT, %s REAL, %s REAL, %s REAL);",
SQL_PRESET, SQL_TYPE, SQL_IDX, SQL_COORDINATE, SQL_EMISSIVITY, SQL_DISTANCE, SQL_ALARM_TEMP);
snprintf(sql, sizeof(sql), "create table %s %s", SQL_TABLE_NAME, para);
printf("%s: %s\n", __FUNCTION__, sql);
ret = sqlite3_exec(db, sql, NULL, NULL, &err);
if(ret != 0){
printf("%s: sqlite3_exec failed! ret = %d",__FUNCTION__, ret);
}
}
/* 插入
** 返回值: 成功返回0，失败返回其它，比如1
**/
static int sql_table_insert(sqlite3 *db, int preset, int type, int idx, char* coord, float emiss, float dis, float alarm)
{
int ret;
char sql[256] = {};
char judge[256] = {};
sql_table_create(db);
/* 数据表--插入 */
snprintf(sql, sizeof(sql), "%s \"%s\" VALUES(%d, %d, %d, \"%s\", %f, %f, %f);", "INSERT INTO", SQL_TABLE_NAME,
preset, type, idx, coord, emiss, dis, alarm);
printf("%s: %s\n", __FUNCTION__,sql);
ret = sqlite3_exec(db, sql, NULL, NULL, NULL);
return ret;
}
static int sql_table_update(sqlite3 * db, int preset, int type, int idx, char* coord, float emiss, float dis, float alarm)
{
char sql[256] = {}; //拼接操作命令
char *errmsg;
sprintf(sql, "update %s set Coordinate = \"%s\", Emissivity = %f, Distance = %f, Alarm = %f where Preset = %d and Type = %d and Idx = %d;",
SQL_TABLE_NAME, coord, emiss, dis, alarm, preset, type, idx);
printf("%s: %s\n", __FUNCTION__,sql);
int ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
if(ret != SQLITE_OK)
{
printf("%s\n", errmsg);
}
else
{
printf("Update done.\n");
}
return 0;
}
/* 删除，flag == 0表示删除具体的某个点，线，面，== 1表示删除整个预置点
**/
static void sql_table_delete(sqlite3 *db, int preset, int type, int idx, int flag)
{
int ret;
char sql[256] = {};
/* 数据表--删除 */
if(0 == flag){
snprintf(sql, sizeof(sql), "DELETE FROM %s WHERE %s = %d and %s = %d and %s = %d;",
SQL_TABLE_NAME, SQL_PRESET, preset, SQL_TYPE, type, SQL_IDX, idx);
}else if(1 == flag){
snprintf(sql, sizeof(sql), "DELETE FROM %s WHERE %s = %d;",
SQL_TABLE_NAME, SQL_PRESET, preset);
}
printf("%s: %s\n", __FUNCTION__,sql);
ret = sqlite3_exec(db, sql, NULL, NULL, NULL);
printf("%s, ret=%d\n", __FUNCTION__,ret);
}
/* 创建数据库，xxx.db */
static sqlite3* sql_open()
{
int ret, cnt;
char file[64];
sqlite3 *db;
snprintf(file, sizeof(file), "%s", SQL_NAME);
cnt = 0;
__OPEN_AG__:
ret = sqlite3_open(file, &db);
if(ret){
if(cnt++ >= 3){
printf("%s(), can't open %s\n", __FUNCTION__, file);
sqlite3_close(db);
return NULL;
}else{
printf("%s(), try open %s, cnt=%d\n", __FUNCTION__, file, cnt);
goto __OPEN_AG__;
}
}
printf("%s, file = %s\n", __FUNCTION__, file);
return db;
}
static int sql_close(sqlite3 *db)
{
sqlite3_close(db);
return 0;
}
int sql_insert(int preset, int type, int idx, char* coord, float emiss, float dis, float alarm)
{
int ret;
sqlite3 *db;
if(0){
//判断数据库是否存在，可以不判断，不存在使用open直接就创建了
ret = sqlite3_open_v2(SQL_NAME, &db, SQLITE_OPEN_READWRITE, NULL);
if(ret != SQLITE_OK){
printf("SQL is not exist, creat it!\n");
db = sql_open();
if(NULL == db){
return -1;
}
printf("sql_open ok!\n");
}
}
printf("SQL is exist!\n");
db = sql_open();
ret = sql_table_insert(db, preset, type, idx, coord, emiss, dis, alarm);
if(ret != SQLITE_OK){
printf("sql_table_insert falied\n");
}
sql_close(db);
return 0;
}
int sql_update(int preset, int type, int idx, char* coord, float emiss, float dis, float alarm)
{
int ret;
sqlite3 *db;
db = sql_open();
ret = sql_table_update(db, preset, type, idx, coord, emiss, dis, alarm);
if(ret != SQLITE_OK){
printf("sql_table_update falied\n");
}
sql_close(db);
return 0;
}
int sql_delete(int preset, int type, int idx, int flag)
{
int ret;
sqlite3 *db;
db = sql_open();
sql_table_delete(db, preset, type, idx, flag);
sql_close(db);
return 0;
}
int main()
{
int preset = 1;
int type = 1;
int idx = 1;
char *coord = "0(1,2) 2(3,3)";
float emiss = 22;
float dis = 22;
float alarm = 22;
//插入功能
//sql_insert(preset, type, idx, coord, emiss, dis, alarm);
//更新数据，数据存在才能更新，不添加创建数据表的功能
//sql_update(preset, type, idx, coord, emiss, dis, alarm);
//删除，直接删除
//sql_delete(preset, type, idx, SQL_DELETE_MODE0);
sql_delete(preset, type, idx, SQL_DELETE_MODE1);
//sql_close(db);
return 0;
}
四、新增函数
新增int preset_set(int idx, int num, Temp_Info *info)和int preset_set(int idx, int num, Temp_Info *info)，这俩函数用来设置某个预置点的信息和获得某个预置点的信息，如用set设置4条Preset为2的数据，其实就是整体的插入，获取也类似。
这都比较简单，稍微有点难的是get函数
Preset Type Idx Coordinate Emissivity Distance Alarm
------ ---- --- ---------- ---------- -------- -----
2 0 0 0(1,0) 0.0 0.0 0.0
2 0 0 1(1,1) 0.0 0.0 0.0
2 0 0 2(1,2) 0.0 0.0 0.0
2 0 0 3(1,3) 0.0 0.0 0.0
get函数
对我这种菜鸡来说有一点技术含量，它在哪呢？
从数据库获取数据时，可以通过sqlite3_get_table知道你想要的数据有几条（如上，预置点为2的有4条数据）那你传进去一个结构体的话，怎么知道这个结构体有多大呢？没法确定了吧
这里就要用到二级指针，在preset_get中获取数据的数量rows并分配内存，就是下面这两句
*p = rows;
*info = (Temp_Info *)malloc(sizeof(Temp_Info)*num);
其中
int num = 0;
int *p = #
上边两句是代码中定义的全局变量
static int preset_get(int idx, int *p, Temp_Info **info)
{
//sqlite3_query();
char temp[64] = {0};
char **tb;
int i = 0;
int rows = 0;
int cols = 0;
int ret;
char sql[256] = {};
sqlite3 *db;
sqlite3_open(SQL_NAME, &db);
if(db == NULL){
sqlite3_db_create(db);
}
sprintf(sql, "select * from %s where Preset = %d;", SQL_TABLE_NAME, idx);
printf("%s: %s\n", __FUNCTION__,sql);
ret = sqlite3_get_table(db, sql, &tb, &rows, &cols, NULL);
if(ret != 0){
printf("sqlite3_get_table error!\n");
}
printf("%d %d \n", rows, cols);
*p = rows;
*info = (Temp_Info *)malloc(sizeof(Temp_Info)*num);
printf("num = %d\n", num);
int j = 0;
int index = cols;
for(i = 0; i < rows; i++)
{
for(j = 0; j < cols; j++)
{
if(index%7 == 0){
}else if(index%7 == 1){
(*info)->type = atoi(tb[index]);
}else if(index%7 == 2){
(*info)->idx = atoi(tb[index]);
}else if(index%7 == 3){
strcpy(temp, tb[index]);
strcpy((*info)->coord, temp);
memset(temp, 0, sizeof(temp));
}else if(index%7 == 4){
(*info)->emiss = atof(tb[index]);
}else if(index%7 == 5){
(*info)->dis = atof(tb[index]);
}else if(index%7 == 6){
(*info)->alarm = atof(tb[index]);
}
index++;
}
(*info)++;
}
while(rows--)
{
(*info)--;
}
sqlite3_free_table(tb);
return 0;
}
扫一扫
8916
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
