提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
前言
一、开发环境配置
1.window环境
https://www.sqlite.org/download.html
下载sqlite3源码文件、tools文件、dll文件,后在添加sqlite.exe的环境变量
可以下载sqlite可视化工具SQLiteStudio
2.Linux环境
sudo apt-get install sqlite3
apt-get install libsqlite3-dev
二、命令
1. 系统命令
.database(查看数据库信息)
.tables(查看数据库里面有哪些表格)
.schema(查看表格的结构)
.help(查看系统命令帮助)
.q/.quit(退出)
2.sql命令
create table student(num integer,name varchar,class integer,sex char); //创建student表格
drop table student; //删除student表格
insert into student(num,name,class,sex) values (1,'zhangsan',701,'m'); //插入张三的数据
select * from student where sex='m'; //显示性别为男的
select * from student; //显示所有
select * from student where sex='m' and class=701; //显示701班男生
delete from student where class=701; //删除701班所有记录
delete from student; //删除表
update student set sex='w' where name='zhangsan' //把张三变成女的
三、C语言开发
sqlite3_open("MyTest.db", &db); //打开创建,返回值0代表失败
sqlite3_close(db); //关闭数据库
sqlite3_errmsg(db); //错误原因
int sqlite3_exec(
sqlite3*, //数据库指针
const char *sql, //sql命令字符串
int (*callback)(void*,int,char**,char**), //回调函数地址
void *, //回调函数的形参地址
char **errmsg //错误信息
);
int sqlite3_get_table(
sqlite3 *db, //打开的数据库的句柄
const char *zSql, //要执行的SQL语句
char ***pazResult, //结果写入该指针指向的char***
int *pnRow, //结果集中行的数目
int *pnColumn, //结果集中列的数目
char **pzErrmsg //错误信息
);
gcc编译sqlite3程序
gcc xxxx.c -l sqlite3
gcc xxxx.out -o xxx.c -l sqlite3
四、开发例程
#include <stdio.h>
#include <string.h>
#include <sqlite3.h>
#include <stdlib.h>
static int callback(void *NotUsed, int argc, char **argv, char **azColName){ //便利展现成员是需要用回调函数
int i;
for(i=0; i<argc; i++){
printf("%-11s",argv[i]);
}
printf("\n");
return 0;
}
int main(void)
{
sqlite3 *db;
char *zErrMsg = 0;
int rc;
rc = sqlite3_open("MyTest.db", &db);
if( rc ){
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
exit(0);
}else{
fprintf(stderr, "Opened database successfully\n");
}
char sql[128]={};
strcpy(sql,"create table student(num integer,name varchar,class integer,sex char);");
if(sqlite3_exec(db,sql,NULL,NULL,&zErrMsg)!=SQLITE_OK){
printf("%s\n",zErrMsg);
}
else{
printf("create table ok\n");
}
while(1)
{
printf("input cmd:\n");
printf("----------------------------------\n");
printf("1:insert 2:delete 3:show 4:quit\n");
printf("----------------------------------\n");
int cmd;
scanf("%d",&cmd);
getchar();
if(cmd==1){
memset(sql,0,128);
int num,class_num;
char name[20]={},sex[10];
printf("insert a memble\n");
printf("input id:\n");
scanf("%d",&num);
getchar();
printf("input name:\n");
scanf("%s",name);
getchar();
printf("input class_num:\n");
scanf("%d",&class_num);
getchar();
printf("input sex:\n");
scanf("%s",sex);
getchar();
memset(sql,0,128);
sprintf(sql,"insert into student values (%d,'%s',%d,%s);",num,name,class_num,sex);
if(sqlite3_exec(db,sql,NULL,NULL,&zErrMsg)!=SQLITE_OK){
printf("%s\n",zErrMsg);
}
else{
printf("insert memble ok\n");
}
}
else if(cmd==2){
int num;
printf("input delete num:\n");
scanf("%d",&num);
getchar();
memset(sql,0,128);
sprintf(sql,"delete from student where num=%d;",num);
if(sqlite3_exec(db,sql,NULL,NULL,&zErrMsg)!=SQLITE_OK){
printf("%s\n",zErrMsg);
}
else{
printf("delete memble ok\n");
}
}
else if(cmd==3){
memset(sql,0,128);
if(sqlite3_exec(db,"select * from student",callback,NULL,&zErrMsg)!=SQLITE_OK){
printf("%s\n",zErrMsg);
}
else{
printf("show memble ok\n");
}
}
else if(cmd==4){
sqlite3_close(db);
exit(0);
}
else{
printf("input cmd error");
}
}
return 0;
}
总结
sqliite3在嵌入式比较常用,学会可备不时之需,没必要深入需要用时再看。