前言
数据的 输入输出 ，加工存储 是计算系统所有关心的范畴
当数据量小，数据结构简单的时候，可以直接使用扁平的文件结构来存储
但是当数据量大，结构复杂的时候，操作普通扁平文件的效率就变得低下了(时间开销大，更容易出错，维护成本高)
于是有必要将数据的存储这项事务独立分离出来，进行模块化和针对性的解决，数据库就应运而生了
目前的数据库主要分 关系型 和 非关系型 两大类，网上资料多如牛毛，我就不进行赘述了
SQLite 是一个开源的进程内库，实现了自给自足、无服务端、零配置、事务性的 SQL 数据库引擎
SQLite is a self-contained, high-reliability, embedded, full-featured, public-domain, SQL database engine
因为轻量的特性，它被大量地使用在了各类嵌入式系统中
Think of SQLite not as a replacement for Oracle but as a replacement for fopen()
这里分享一下使用C语言操作 SQLite 的相关基础
Tip: 当前的最新版本为 SQLite Release 3.16.2 On 2017-01-06，手动维护的相关基础可以参看之前的 《SQLite 基础》，C 语言的API介绍可以参看 C-language Interface Specification for SQLite，相关的函数列表可以参看 List Of Functions
概要
代码示例
要求
- 1.表结构：SID NAME SCORE
- 2.用sqlite实现增、删、改、查
- 3.用文字界面手动输入
1:insert 2:delete 3:update 4:query 0:exit
1:input 0:back
2: 1 delete 0:back
delete ID:代码示例
sqlite.c
#include <stdio.h> //printf,scanf,sprintf,fprintf,stderr 相关的函数声明在此文件中
#include <sqlite3.h> //sqlite3_open,sqlite3_errmsg,sqlite3_close,sqlite3_exec 相关的函数声明在此文件中
#define NAMESIZE 20
#define SQLSIZE 1024
int callback(void *data,int argc,char **argv,char **colname) //定义一个回调函数，用于对SQL 语句的执行结果进行逐条处理，主要作用是进行显示
{
int i=0;
for(i=0;i<argc;i )printf("s",colname[i]); //先打印每个列名
printf("n");
for(i=0;i<argc;i )printf("s",argv[i]); //再打印返回集中的每一项
printf("n");
return 0;
}
int showall(sqlite3 *db) //查询所有记录的操作
{
int res=-1,option=-1;
char sql[SQLSIZE],*errmsg=NULL;
printf("Please take follow action:n%-10s%-10sn","0.back","1.showall"); //子项提示
scanf("%d",&option); //获取选择
if(0 == option)
{
printf("back to main menu...n");
return res;
}
else if(1 == option)
{
sprintf(sql,"select * from students;"); //构建查询所有信息的SQL语句
printf("ready to process ...n[ %s ]n",sql);
if (0 != sqlite3_exec(db,sql,callback,0,&errmsg)) //执行查询所有信息的SQL语句
{
fprintf(stderr,"Can't show all from table students:%sn",sqlite3_errmsg(db));
}
else printf("Select successfully...n");
}
else printf("error option %dnplease reinput...n",option);
res=0;
return res;
}
int query(sqlite3 *db) //查询操作
{
int res=-1,option=-1,sid=0;
char sql[SQLSIZE],*errmsg=NULL;
printf("Please take follow action:n%-10s%-10sn","0.back","1.query"); //子项提示
scanf("%d",&option);
if(0 == option)
{
printf("back to main menu...n");
return res;
}
else if(1 == option)
{
printf("Please input SID of the student:n%-10sn","SID");
scanf("%d",&sid);
sprintf(sql,"select * from students where sid=%d;",sid); //构建进行查询的SQL语句
printf("ready to process ...n[ %s ]n",sql);
if (0 != sqlite3_exec(db,sql,callback,0,&errmsg)) //执行查询语句
{
fprintf(stderr,"Can't select from table students:%sn",sqlite3_errmsg(db));
}
else printf("Select successfully...n");
}
else printf("error option %dnplease reinput...n",option);
res=0;
return res;
}
int update(sqlite3 *db) //更新操作
{
int res=-1,option=-1,sid=0;
char name[NAMESIZE],sql[SQLSIZE],*errmsg=NULL;
float score=0;
printf("Please take follow action:n%-10s%-10sn","0.back","1.update"); //子项提示
scanf("%d",&option);
if(0 == option)
{
printf("back to main menu...n");
return res;
}
else if(1 == option)
{
printf("Please input SID,NAME,SCORE of the student to be update:n%-10s%-10s%-10sn","SID","NAME","SCORE"); //提示输入更新的相关信息
scanf("%d%s%f",&sid,name,&score);
sprintf(sql,"update students set name='%s',score=%5.2f where sid=%d;",name,score,sid); //构建进行更新的SQL语句
printf("ready to process ...n[ %s ]n",sql);
if (0 != sqlite3_exec(db,sql,0,0,&errmsg)) //执行更新语句
{
fprintf(stderr,"Can't update table students:%sn",sqlite3_errmsg(db));
}
else printf("Update successfully...n");
}
else printf("error option %dnplease reinput...n",option);
res=0;
return res;
}
int delete(sqlite3 *db) //删除操作
{
int res=-1,option=-1,sid=0;
char sql[SQLSIZE],*errmsg=NULL;
printf("Please take follow action:n%-10s%-10sn","0.back","1.delete");
scanf("%d",&option); //子项提示
if(0 == option) //0项退出
{
printf("back to main menu...n");
return res;
}
else if(1 == option)
{
printf("Please input SID of the student:n%-10sn","SID");
scanf("%d",&sid);
sprintf(sql,"delete from students where sid=%d;",sid); //构建删除的SQL语句
printf("ready to process ...n[ %s ]n",sql);
if (0 != sqlite3_exec(db,sql,0,0,&errmsg)) //执行删除语句
{
fprintf(stderr,"Can't delete from table students:%sn",sqlite3_errmsg(db));
}
else printf("Delete successfully...n");
}
else printf("error option %dnplease reinput...n",option);
res=0;
return res;
}
int insert( sqlite3 *db) //插入操作
{
int res=-1,option=-1,sid=0;
char name[NAMESIZE],sql[SQLSIZE],*errmsg=NULL;
float score=0;
printf("Please take follow action:n%-10s%-10sn","0.back","1.insert"); //子项提示
scanf("%d",&option); //获取输入
if(0 == option) //0选项进行退出
{
printf("back to main menu...n");
return res;
}
else if(1 == option) //1选项进行插入
{
printf("Please input SID,NAME,SCORE of the student:n%-10s%-10s%-10sn","SID","NAME","SCORE"); //提示输入
scanf("%d%s%f",&sid,name,&score); //获取输入
sprintf(sql,"insert into students(sid,name,score) values(%d,'%s',%5.2f);",sid,name,score); //构建插入的SQL语句
printf("ready to process ...n[ %s ]n",sql);
if (0 != sqlite3_exec(db,sql,0,0,&errmsg)) //执行插入操作
{
fprintf(stderr,"Can't insert into table students:%sn",sqlite3_errmsg(db));
}
else printf("Insert successfully...n");
}
else printf("error option %dnplease reinput...n",option);
res=0;
return res;
}
int main()
{
sqlite3 *db=NULL;
char *errmsg=NULL,*sql=NULL;
char *dbname="test.db";
int res=-1,option=-1; //定义变量，进行初始化
if(0 != sqlite3_open(dbname,&db)) //打开数据库，如果没有就创建
{
fprintf(stderr,"Can't open database %s :%sn",dbname,sqlite3_errmsg(db)); //打开失败则返回错误信息
sqlite3_close(db);
return res;
}
else printf("Open database %s successfully...n",dbname);
sql="create table students (sid int primary key,name varchar(20),score real )"; //构建SQL语句，在数据库中创建表
if (0 != sqlite3_exec(db,sql,0,0,&errmsg)) //执行SQL语句
{
fprintf(stderr,"Can't create table students:%sn",sqlite3_errmsg(db));
}
else printf("Create table students successfully...n");
while(1)
{
printf("Please take follow action:n%-10s%-10s%-10s%-10s%-10s%-10sn","0.exit","1.insert","2.delete","3.update","4.query","5.showall"); //显示主菜单
scanf("%d",&option); //获取选项
if(0 == option) //如果选择退出，就进行退出
{
printf("ready to exit...n");
break;
}
else if(1 == option) insert(db);
else if(2 == option) delete(db);
else if(3 == option) update(db);
else if(4 == option) query(db);
else if(5 == option) showall(db); //进入对应的处理过程
else printf("error option %dnplease reinput...n",option); //如果不在其列，就提示重新输入
}
if(0 != sqlite3_close(db)) //关闭数据库
{
fprintf(stderr,"Can't close database %s :%sn",dbname,sqlite3_errmsg(db));
return res;
}
else printf("Close database %s successfully...n",dbname);
res=0;
return res;
}
