概述
sqlite3是一个小型数据库，代码量少，运行占内存也比较少，采用C 编写，所以天生适合嵌入式系统中，尤其是嵌入式linux，相当支持，sqlite3可以直接通过shell运行，不过这个也只限于测试使用，在实际的项目编程中，我们还是要使用sqlite3提供的C/C++接口函数，也就是API接口。
sqlite3 移植
sqlite官网下载C源码：https://www.sqlite.org/download.html
解压压缩包：tar -zxvf sqlite-autoconf-3360000.tar.gz
运行配置文件：./configure --host=arm-linux --prefix=/work/src_packages/sqlite-autoconf-3071401/dist-sqlite3
–host：指定交叉编译工具， 和编译 Linux 的编译器一致
–prefix： 指定安装目录， 编译后生成的文件放在此目录， 必须是绝对路径
编译：执行make，再执行make install
编译结束过后，在源码目录下生成dist-sqlite3文件夹
将/dist-sqlite3/bin/sqlite3拷贝到开发板的/bin目录下，并执行chmod 777 sqlite3。
将/dist-sqlite3/lib/所有文件拷贝到开发板的/lib目录下。
终端输入”sqlite3”，进入sqlite3。
sqlite3 常用命令
进入sqlite3命令终端：#sqlite3
获取点命令清单：sqlite>.help
退出sqlite3操作：sqlite>.exit 或sqlite>.quit
打开或创建数据库：sqlite>.open test.db
查看数据库文件信息：sqlite>.database
创建表：CREATE TABLE语句；
查看所有表：sqlite>.tables
查看所有表的完整信息：sqlite>.schema
删除表：DROP TABLE语句
某个表中添加新的数据行：INSERT INTO语句
数据库表中获取数据：SELECT语句
列出当前显示格式的配置：sqlite>.show
设置显示格式之打开表头：sqlite>.header on
设置显示格式之列款：sqlite>.width 2
示例：
# sqlite3
SQLite version 3.36.0 2021-06-18 18:36:39
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> .databases
main: /backup0/test.db r/w
sqlite> create table demo (id int,name char,age int);
sqlite> .tables
demo
sqlite> .schema
CREATE TABLE demo (id int,name char,age int);
sqlite> insert into demo values(100,'WangWu',23);
sqlite> insert into demo values(101,'Tommm',25);
sqlite> .header on
sqlite> .mode column
sqlite> .show
echo: off
eqp: off
explain: auto
headers: on
mode: column
nullvalue: ""
output: stdout
colseparator: "|"
rowseparator: "\n"
stats: off
width:
filename: test.db
sqlite> select * from demo;
id name age
--- ------ ---
100 WangWu 23
101 Tommm 25
sqlite> .exit
#
sqlite3 API使用
打开数据库
int sqlite3_open( 文件名, sqlite3 ** );
关闭数据库
int sqlite3_close(sqlite3 *);
执行sql语句
int sqlite3_exec(sqlite3*,const char sql,sqlite3_callback,void,char **errmsg );
查询数据库
int sqlite3_get_table(sqlite3*,const char *sql, char ***resultp, int *nrow, int *ncolumn, char **errmsg);
sqlite3 编程实例
实现思路：
- 打开数据库，执行sqlite3_open；
- 创建数据库表格，利用sqlite3_exec执行create table语句；
- 根据输入指令，执行插入、删除、查询、退出操作；
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <string.h>
#include <time.h>
#include <strings.h>
#define DATABASE "fru.db"
typedef struct{
char time[20];
char name[16];
float cost;//成本
float price;//售价
char in_out[5];//进货in,出货out
float number;//交易量
float money;//交易金额（成本或售价*交易量）
float total;//库存总量
}fruit_t;
void operate_table_cmd_menu()
{
puts("/******************************************/");
puts("*operate table cmd menu:");
printf("*1:insert 2:delete 3:query 4:update 5:quite \n");
puts("/******************************************/");
printf("Please input cmd:");
}
const char *get_local_time(char *time_str);
fruit_t *get_last_row(sqlite3 *db,fruit_t *fruit);
int query_callback(void *para, int f_num, char **f_value, char **f_name);
int do_insert_sqlite3(sqlite3 * db);
int do_delete_sqlite3(sqlite3 * db);
int do_query_sqlite3(sqlite3 * db);
void get_columns_name(sqlite3 * db);//获取字段名称
int do_update_sqlite3(sqlite3 * db);
int main(int argc, const char *argv[])
{
sqlite3 * db;
char *errmsg;
int cmd;
char sql[128]={};
//打开sqlite3数据库
if(sqlite3_open(DATABASE,&db) != SQLITE_OK)
{
printf("%s\n",sqlite3_errmsg(db));
exit(-1);
}
else
{
printf("open %s success.\n",DATABASE);
}
//创建一张数据库的表格 将ID设为主键值，并设为自增字段(保证字段唯一性)
sprintf(sql,"create table fru(time char,name char,cost Integer,price Integer,in_out char,number Integer,money Integer,total Integer);");
if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK)
{
printf("%s\n",errmsg);
}
else
{
printf("create or open table success.\n");
}
while(1)
{
operate_table_cmd_menu();
while(scanf("%d",&cmd) == 0)
{
getchar();
operate_table_cmd_menu();
}
getchar();
switch(cmd)
{
case 1:
do_insert_sqlite3(db);
break;
case 2:
do_delete_sqlite3(db);
break;
case 3:
do_query_sqlite3(db);
break;
case 4:
do_update_sqlite3(db);
break;
case 5:
sqlite3_close(db);
exit(0);
default:
printf("Error cmd.\n");
}
}
return 0;
}
const char *get_local_time(char *time_str)
{
time_t tim_t;
struct tm loc_t;
time(&tim_t);
loc_t = *localtime(&tim_t);
sprintf(time_str,"%04d-%d-%02d %02d:%02d:%02d",loc_t.tm_year+1900,loc_t.tm_mon+1,loc_t.tm_mday,loc_t.tm_hour,loc_t.tm_min,loc_t.tm_sec);
return time_str;
}
fruit_t *get_last_row(sqlite3 *db,fruit_t *fruit)
{
char **resultp;
int nrow;
int ncolumn;
char sql[128];
int index_base;
char *errmsg;
sprintf(sql,"select * from fru where name='%s';",fruit->name);
sqlite3_get_table(db,sql,&resultp,&nrow,&ncolumn,&errmsg);
if(nrow == 0)
{
return NULL;
}
else
{
index_base = (nrow)*(ncolumn);
fruit->cost = atof(resultp[index_base+2]);
fruit->price = atof(resultp[index_base+3]);
fruit->total = atof(resultp[index_base+7]);
}
return fruit;
}
int do_insert_sqlite3(sqlite3 * db)
{
fruit_t fruit;
char sql[128];
char *errmsg;
char type[10];
bzero(&fruit,sizeof(fruit));
get_local_time(fruit.time);
printf("input fruit name:");
scanf("%s",fruit.name);
getchar();
if(get_last_row(db,&fruit) != NULL)//系统中已经有这种水果的记录
{
puts("*************************************");
printf("%s cost:%.2f price:%.2f\n",fruit.name,fruit.cost,fruit.price);
puts("*************************************");
printf("if you change it[y|n]:");
scanf("%s",type);
getchar();
if(strncasecmp(type,"y",strlen("y")) == 0)
{
char value[20];
printf("change cost:%.2f of %s [cost|n]:",fruit.cost,fruit.name);
scanf("%s",value);//输入成本修改成本，输入n放弃修改
getchar();
if(strncasecmp(value,"n",strlen("n")) != 0)
fruit.cost = atof(value);
printf("change price:%.2f of %s [price|n]:",fruit.price,fruit.name);
scanf("%s",value);//输入单价修单价，输入n放弃修改
getchar();
if(strncasecmp(value,"n",strlen("n")) != 0)
fruit.price = atof(value);
}
printf("Please input the number of %s:",fruit.name);
scanf("%f",&fruit.number);
getchar();
fruit.total = fruit.total +fruit.number;
}
else//系统中没有的水果种类
{
printf("Please input the cost of %s [cost]:",fruit.name);
while(scanf("%f",&fruit.cost) == 0)
{
getchar();
printf("Please input the cost of %s [cost]:",fruit.name);
}
getchar();
printf("Please input the price of %s [price]:",fruit.name);
while(scanf("%f",&fruit.price) == 0)
{
getchar();
printf("Please input the price of %s [price]:",fruit.name);
}
getchar();
printf("Please input the number of %s:",fruit.name);
scanf("%f",&fruit.number);
getchar();
fruit.total = fruit.number;
}
sprintf(fruit.in_out,"in");
fruit.money = fruit.cost * fruit.number;
sprintf(sql,"insert into fru values('%s','%s',%.4f,%.4f,'%s',%.4f,%.4f,%.4f);",fruit.time,fruit.name,fruit.cost,fruit.price,fruit.in_out,fruit.number,fruit.money,fruit.total);//自增字段的值传NULL
if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK )
{
printf("%s\n",errmsg);
return -1;
}
else
{
printf("insert:\n ");
sprintf(sql,"select * from fru where time='%s';",fruit.time);
get_columns_name(db);//获取字段名称
sqlite3_exec(db,sql,query_callback,NULL,&errmsg);
printf("done\n");
return 0;
}
}
int query_callback(void *para, int f_num, char **f_value, char **f_name)
{
int i;
for(i=0;i<f_num;i++)
{
if(i == 0)
printf("\t%-19s ",f_value[i]);
else
printf("%-10s ",f_value[i]);
}
puts("");
return 0;
}
void get_columns_name(sqlite3 * db)
{
char **resultp;
int nrow;
int ncolumn;
char *errmsg;
int i;
sqlite3_get_table(db,"select * from fru",&resultp,&nrow,&ncolumn,&errmsg);
printf("\t%-19s ",resultp[0]);
for(i=1;i<ncolumn;i++)
{
printf("%-10s ",resultp[i]);
}
puts("");
}
int do_delete_sqlite3(sqlite3 * db)
{
fruit_t fruit;
char sql[128];
char *errmsg;
char value[20];
bzero(&fruit,sizeof(fruit));
get_local_time(fruit.time);
printf("input fruit name:");
scanf("%s",fruit.name);
getchar();
if(get_last_row(db,&fruit) != NULL)//系统中有这种水果
{
printf("input the out number of %s:",fruit.name);
scanf("%s",value);
getchar();
if(atof(value) > fruit.total)//剩余的数量不够
{
printf("The total number:%.4f of %s is not enough, please stock it.\n",fruit.total,fruit.name);
return -1;
}
else
{
fruit.number = atof(value);
fruit.money = fruit.price * fruit.number;
fruit.total = fruit.total - fruit.number;
}
}
else//系统中没有这种水果
{
printf("We don't have this fruit:%s. Please stock it.",fruit.name);
return -1;
}
sprintf(fruit.in_out,"out");
printf("are you sure delete number:%.4f of %s[y|n]:",fruit.number,fruit.name);
scanf("%s",value);
getchar();
if(strncasecmp(value,"y",strlen("y")) == 0)
{
sprintf(sql,"insert into fru values('%s','%s',%.4f,%.4f,'%s',%.4f,%.4f,%.4f);",fruit.time,fruit.name,fruit.cost,fruit.price,fruit.in_out,fruit.number,fruit.money,fruit.total);//自增字段的值传NULL
if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK )
{
printf("%s\n",errmsg);
return -1;
}
else
{
printf("delete done\n ");
return 0;
}
}
else
{
printf("delete cancelled.\n");
return 0;
}
}
int do_query_sqlite3(sqlite3 * db)
{
fruit_t fruit;
char sql[128];
char *errmsg;
char value[96];
bzero(&fruit,sizeof(fruit));
puts("*************************************");
printf("*Examples of query types:\n");
puts("*[all | name='apple' | in_out='in']");
//puts("*[all total | apple total | banana total]");
puts("*[time<'2018-12-5 17:08:08' | total>90 and total<200]");
puts("*[name='apple' and in_out='in']");
puts("*************************************");
printf("Pease input query type:");
scanf("%s",value);
getchar();
if(strncasecmp("all",value,strlen(value)) == 0)
sprintf(sql,"select * from fru;");
else
sprintf(sql,"select * from fru where %s;",value);//自增字段的值传NULL
get_columns_name(db);//获取字段名称
if(sqlite3_exec(db,sql,query_callback,NULL,&errmsg) != SQLITE_OK )
{
printf("%s\n",errmsg);
return -1;
}
else
{
printf("query done\n ");
return 0;
}
}
int do_update_sqlite3(sqlite3 * db)
{
fruit_t fruit;
char sql[128];
char *errmsg;
int type;
bzero(&fruit,sizeof(fruit));
get_local_time(fruit.time);
printf("input fruit name:");
scanf("%s",fruit.name);
getchar();
if(get_last_row(db,&fruit) != NULL)//系统中已经有这种水果的记录
{
puts("*************************************");
printf("%s cost:%.2f price:%.2f\n",fruit.name,fruit.cost,fruit.price);
puts("*************************************");
printf("if you update it[1:yes 2:cancel]:");
scanf("%d",&type);
getchar();
char value[20];
if(type == 1)
{
printf("update cost:%.2f of %s [cost|n]:",fruit.cost,fruit.name);
scanf("%s",value);//输入成本修改成本，输入n放弃修改
getchar();
if(strncasecmp(value,"n",strlen("n")) != 0)
fruit.cost = atof(value);
printf("update price:%.2f of %s [price|n]:",fruit.price,fruit.name);
scanf("%s",value);//输入单价修单价，输入n放弃修改
getchar();
if(strncasecmp(value,"n",strlen("n")) != 0)
fruit.price = atof(value);
}
else
{
printf("update cancelled\n");
return 0;
}
sprintf(fruit.in_out,"update");
sprintf(sql,"insert into fru values('%s','%s',%.4f,%.4f,'%s',%.4f,%.4f,%.4f);",fruit.time,fruit.name,fruit.cost,fruit.price,fruit.in_out,fruit.number,fruit.money,fruit.total);//自增字段的值传NULL
if(sqlite3_exec(db,sql,query_callback,NULL,&errmsg) != SQLITE_OK )
{
printf("%s\n",errmsg);
return -1;
}
else
{
printf("update:\n");
get_columns_name(db);//获取字段名称
sprintf(sql,"select * from fru where time='%s';",fruit.time);
sqlite3_exec(db,sql,query_callback,NULL,&errmsg);
printf("update done\n ");
return 0;
}
}
else//系统中没有的水果种类
{
printf("We don't have this fruit:%s. Please stock it.",fruit.name);
}
return 0;
}
sqlite3 测试验证
交叉编译：
arm-linux-gcc -I /home/ubuntu/sqlite3/install/include/ -L /home/ubuntu/sqlite3/install/lib/ -o sql testSql.c -lsqlite3 –ldl
-I 指定了 sqlite3.h 所在路径，
-L 指定 sqlite3 的 lib 库路径，即libsqlite3.a所在目录。
-o 编译生成的文件名为sql ，testSql.c是源程序文件;
-lsqlite3指明要链接静态库文件libsqlite3.a。
将生成的可执行文件拷贝到开发板,运行测试：
友情链接，sqlite3菜鸟教程：https://www.runoob.com/sqlite/sqlite-create-table.html
友情链接，sqlite3 C代码示例：https://blog.csdn.net/m0_37542524/article/details/84842154
友情链接，sqlite3 基础概念：https://www.cnblogs.com/xishuichangliu/p/6270291.html
478
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
