嵌入式数据库SQLite 3配置使用具体条记教程 - IT评测·应用市场-qidao123.com技术社区

//打开数据库,如不存在则会创建数据库
int ret = sqlite3_open("project_data.db", &db);
if( ret )
{
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
exit(-1);
}
fprintf(stderr, "Opened database successfully\n");

复制代码

//CREATE TABLE 告诉数据库系统创建一个新表的关键字。CREATE TABLE 语句后跟着表的唯一的名称或标识。
CREATE TABLE database_name.table_name(
column1 datatype PRIMARY KEY(one or more columns),
column2 datatype,
column3 datatype,
.....
columnN datatype,
);
char table_name[200] = {0};
char *err_msg = NULL;
snprintf(table_name, sizeof(table_name), "create table if not exists camera(time_stamp integer primary key, action text, x integer, y integer, z integer, vx integer, vy integer, vz integer, time integer);");
int ret = sqlite3_exec(db, table_name, NULL, NULL, &err_msg);
if(ret){
fprintf(stderr, "create table err:%s\n", err_msg);
return -1;
}
fprintf(stderr, "create table successfully\n");

复制代码

//SQLite 的 DROP TABLE 语句用来删除表定义及其所有相关数据、索引、触发器、约束和该表的权限规范。
//DROP TABLE 语句的基本语法如下。
DROP TABLE database_name.table_name;
char table_name[200] = {0};
char *err_msg = NULL;
snprintf(table_name, sizeof(table_name), "DROP TABLE database_name.table_name;");
int ret = sqlite3_exec(db, table_name, NULL, NULL, &err_msg);
if(ret){
fprintf(stderr, "delete table err:%s\n", err_msg);
return -1;
}
fprintf(stderr, "delete table successfully\n");

复制代码

INSERT INTO 语句有两种基本语法，如下所示：
INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)] VALUES (value1, value2, value3,...valueN);//在这里，column1, column2,...columnN 是要插入数据的表中的列的名称
或
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);
//确保值的顺序与列在表中的顺序一致。
char table_value[200] = {0};
int ret = 0;
char *err_msg = NULL;
snprintf(table_value, sizeof(table_value),"insert into camera values(%lld, '%c', %d, %d, %d, %d, %d, %d, %d);", get_current_timestamp_ms(), action, x, y, z, vx, vy, vz, time);
ret = sqlite3_exec(db, table_value, NULL, NULL, &err_msg);
if(ret)
{
fprintf(stderr, "insert value to table err:%s\n", err_msg);
return -1;
}
fprintf(stderr, "insert value to table successfully\n");

复制代码

//SQLite 的 SELECT 语句用于从 SQLite 数据库表中获取数据，以结果表的形式返回数据。这些结果表也被称为结果集。
//SQLite 的 SELECT 语句的基本语法如下：
SELECT column1, column2, columnN FROM table_name;//在这里，column1, column2...是表的字段，他们的值即是您要获取的。
SELECT * FROM table_name; //获取所有可用的字段
char *err_msg = NULL;
sprintf(sql, "select * from table_value;");
ret = sqlite3_exec(db, sql, NULL, NULL, &err_msg); //执行 SQL 命令的快捷方式
if(ret)
{
fprintf(stderr, "Can't select sqlite value: %s\n", sqlite3_errmsg(db));
return -1;
}

复制代码

//SQLite 的 DELETE 查询用于删除表中已有的记录。可以使用带有 WHERE 子句的 DELETE 查询来删除选定行，否则所有的记录都会被删除。
//带有 WHERE 子句的 DELETE 查询的基本语法如下：
DELETE FROM table_name
WHERE [condition];
//可以使用 AND 或 OR 运算符来结合 N 个数量的条件。
char *err_msg = NULL;
sprintf(sql, "DELETE FROM camera WHERE time_stamp = 123456789;");
ret = sqlite3_exec(db, sql, NULL, NULL, &err_msg); //执行 SQL 命令的快捷方式
if(ret)
{
fprintf(stderr, "Can't DELETE sqlite data: %s\n", sqlite3_errmsg(db));
return -1;
}

复制代码

//SQLite 的 UPDATE 查询用于修改表中已有的记录。可以使用带有 WHERE 子句的 UPDATE 查询来更新选定行，否则所有的行都会被更新。
//带有 WHERE 子句的 UPDATE 查询的基本语法如下：
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
char *err_msg = NULL;
sprintf(sql, "UPDATE camera SET action = 't' WHERE time_stamp = 123456789;");
ret = sqlite3_exec(db, sql, NULL, NULL, &err_msg); //执行 SQL 命令的快捷方式
if(ret)
{
fprintf(stderr, "Can't DELETE sqlite data: %s\n", sqlite3_errmsg(db));
return -1;
}

复制代码

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <time.h>
#include <sqlite3.h>
int database_init();
int write_motor_info_to_database(sqlite3 *db, int motor_id, double target_pos, double real_pos, double real_speed, double real_current);
int write_camera_info_to_database(sqlite3 *db, char action, int x, int y, int z, int vx, int vy, int vz, int time);
long long get_current_timestamp_ms(void);
int main(void)
{
printf("sqlite3 database test!\n");
database_init();
return 0;
}
/**
* @brief 数据库初始化
* @param NONE
* @retval 成功返回0，失败返回-1
*/
int database_init(void)
{
int ret = -1;
sqlite3 *db;
char *err_msg = NULL;
char database_name[128] = {0};
//获取当前时间
struct tm t;
time_t now;
time(&now);
localtime_s(&t, &now);
snprintf(database_name, sizeof(database_name),"%02d%02d%02d.db", t.tm_year + 1900, t.tm_mon + 1, t.tm_mday);
printf("date:%s\n", database_name);
//打开数据库
ret = sqlite3_open(database_name, &db);
if( ret )
{
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return -1;
}
fprintf(stderr, "Opened database successfully\n");
char table_name[200] = {0};
//时间戳目标位置实际位置实际速度实际电流
//create table if not exists motor0 (time_stamp integer primary key, target_pos real, real_pos real, real_speed real, real_current, real);
for(int motor_id = 0; motor_id < 6; motor_id++)
{
snprintf(table_name, sizeof(table_name),"create table if not exists motor%d (time_stamp integer primary key, target_pos real, real_pos real, real_speed real, real_current real);", motor_id);
ret = sqlite3_exec(db, table_name, NULL, NULL, &err_msg);
if(ret)
{
fprintf(stderr, "create table err:%s\n", err_msg);
return -1;
}
fprintf(stderr, "create table successfully\n");
}
memset(table_name, 0, sizeof(table_name));
snprintf(table_name, sizeof(table_name), "create table if not exists camera(time_stamp integer primary key, action text, x integer, y integer, z integer, vx integer, vy integer, vz integer, time integer);");
ret = sqlite3_exec(db, table_name, NULL, NULL, &err_msg);
if(ret)
{
fprintf(stderr, "create table err:%s\n", err_msg);
return -1;
}
fprintf(stderr, "create table successfully\n");
write_motor_info_to_database(db, 0, 90.0, 87.2, 5.0, 0.85);
write_motor_info_to_database(db, 1, 90.0, 87.2, 5.0, 0.85);
write_motor_info_to_database(db, 2, 90.0, 87.2, 5.0, 0.85);
write_motor_info_to_database(db, 3, 90.0, 87.2, 5.0, 0.85);
write_motor_info_to_database(db, 4, 90.0, 87.2, 5.0, 0.85);
write_motor_info_to_database(db, 5, 90.0, 87.2, 5.0, 0.85);
write_camera_info_to_database(db, 't', 100,200,150,160,130,110,1000);
return 0;
}
/**
* @brief 写入电机数据到数据库中
* @param db：数据库文件描述符
* @param target_pos：目标位置
* @param real_pos：实际位置
* @param real_speed：实际速度
* @param real_current：实际电流
* @retval 写入成功返回0，失败-1
*/
int write_motor_info_to_database(sqlite3 *db, int motor_id, double target_pos, double real_pos, double real_speed, double real_current)
{
char table_value[200] = {0};
int ret = 0;
char *err_msg = NULL;
//insert into motor0 values(1798345, 90.0, 88.66, 45.1, 0.97);
snprintf(table_value, sizeof(table_value),"insert into motor%d values(%lld, %.2f, %.2f, %.2f, %.2f);", motor_id, get_current_timestamp_ms(), target_pos, real_pos, real_speed, real_current);
ret = sqlite3_exec(db, table_value, NULL, NULL, &err_msg);
if(ret)
{
fprintf(stderr, "insert value to table err:%s\n", err_msg);
return -1;
}
fprintf(stderr, "insert value to table successfully\n");
return 0;
}
/**
* @brief 写入相机数据到数据库中
* @param db：数据库文件描述符
* @param action:动作
* @param x：
* @param y：
* @param z:
* @param vx：
* @param vy：
* @param vz:
* @retval 写入成功返回0，失败-1
*/
int write_camera_info_to_database(sqlite3 *db, char action, int x, int y, int z, int vx, int vy, int vz, int time)
{
char table_value[200] = {0};
int ret = 0;
char *err_msg = NULL;
//insert into motor0 values(1798345, 90.0, 88.66, 45.1, 0.97);
snprintf(table_value, sizeof(table_value),"insert into camera values(%lld, '%c', %d, %d, %d, %d, %d, %d, %d);", get_current_timestamp_ms(), action, x, y, z, vx, vy, vz, time);
ret = sqlite3_exec(db, table_value, NULL, NULL, &err_msg);
if(ret)
{
fprintf(stderr, "insert value to table err:%s\n", err_msg);
return -1;
}
fprintf(stderr, "insert value to table successfully\n");
return 0;
}
/**
* @brief 获取毫秒级时间戳
* @param NONE
* @retval 成功返回时间戳值，失败返回-1
*/
long long get_current_timestamp_ms(void)
{
#if defined(_WIN32) || defined(_WIN64)
struct _timeb timebuffer;
_ftime64_s(&timebuffer);
return (long long)timebuffer.time * 1000 + timebuffer.millitm;
#elif defined(__unix__) || defined(__unix) || defined(unix)
struct timeval tv;
gettimeofday(&tv, NULL);
return (long long)tv.tv_sec * 1000 + tv.tv_usec / 1000;
#endif
}

复制代码

数据类型	亲和类型
INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8	INTEGER
CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOB	TEXT
BLOB 未指定类型	BLOB
REAL DOUBLE DOUBLE PRECISION FLOAT	REAL
NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME	NUMERIC