代码:
#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>
#include <strings.h>
int do_insert(sqlite3 *db); //插入函数
int do_delete(sqlite3 *db); //删除函数
int do_update(sqlite3 *db); //修改函数
int do_select(sqlite3 *db); //查询函数
int main(int argc, const char *argv[])
{
//创建并打开一个数据库
sqlite3 *db=NULL;
if(sqlite3_open("./sq.db",&db)!=SQLITE_OK)
{
fprintf(stderr,"line:%d sqlite3_open failed:%d:%s\n",\
__LINE__,sqlite3_errcode(db),sqlite3_errmsg(db));
return-1;
}
printf("sqlite3_open success\n");
//创建一张表格,数据库中sql怎么写就怎么写
char sql[128]="create table if not exists stu(id int primary key,name char,score float);";
char *errmsg=NULL;
if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
{
fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
return-1;
}
printf("table stu create success\n");
char choose=0;
while(1)
{
system("clear");
printf("---------------------\n");
printf("-------1.插入--------\n");
printf("-------2.删除--------\n");
printf("-------3.修改--------\n");
printf("-------4.查找--------\n");
printf("-------5.退出--------\n");
printf("---------------------\n");
printf("请输入\n");
choose=getchar();
while(getchar()!=10);
switch(choose)
{
case '1':
do_insert(db);
break;
case '2':
do_delete(db);
break;
case '3':
do_update(db);
break;
case '4':
do_select(db);
break;
case '5':
goto END;
break;
default:
printf("请输入正确的选项码>>>\n");
}
printf("输入任意字符清零\n");
while(getchar()!=10);
}
END:
//关闭数据库
if(sqlite3_close(db)!=SQLITE_OK)
{
fprintf(stderr,"line:%d sqlite3_close failed:%d:%s\n",\
__LINE__,sqlite3_errcode(db),sqlite3_errmsg(db));
return-1;
}
return 0;
}
int do_select(sqlite3 *db) //查询函数
{
char sql[128]="select * from stu;";
char **pres=NULL;
int row,column;
char *errmsg=NULL;
//专门用于查询相关sql语句函数sqlite3_get_table
if(sqlite3_get_table(db,sql,&pres,&row,&column,&errmsg)!=SQLITE_OK)
{
fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
return-1;
}
printf("insert into syu success\n");
int num=0;
for(int i=0;i<row+1;i++)
{
for(int j=0;j<column;j++)
{
printf("%s\t",pres[num++]);
}
putchar(10);
}
return 0;
}
int do_insert(sqlite3 *db) //插入函数
{
//全字段插入
printf("请输入要插入的id号\n");
int id=0;
scanf("%d",&id);
getchar();
printf("请输入要插入的name\n");
char name[128]="";
scanf("%s",name);
getchar();
printf("请输入要插入的score\n");
float score=0;
scanf("%f",&score);
while(getchar()!=10);
char sql[128]="";
sprintf(sql,"insert into stu values(%d,\"%s\",%g);",id,name,score);
char *errmsg=NULL;
if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
{
fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
return-1;
}
printf("insert into syu success\n");
return 0;
}
int do_delete(sqlite3 *db) //删除函数
{
printf("请输入要删除同学的id号\n");
int id=0;
scanf("%d",&id);
while(getchar()!=10);
char sql[128]="";
sprintf(sql,"delete from stu where id=%d;",id);
char *errmsg=NULL;
if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
{
fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
return-1;
}
printf("delete stu success\n");
return 0;
}
int do_update(sqlite3 *db) //修改函数
{
char choose=0;
printf("------请输入要修改的选项------\n");
printf("------1.修改学号--------------\n");
printf("------2.修改姓名--------------\n");
printf("------3.修改分数--------------\n");
printf("------------------------------\n");
choose=getchar();
while(getchar()!=10);
switch(choose)
{
case '1':
printf("请输入要修改的同学姓名>>>");
char name[128]="";
scanf("%s",name);
while(getchar()!=10);
printf("请输入要修改后同学id>>>");
int id=0;
scanf("%d",&id);
while(getchar()!=10);
char sql[128]="";
sprintf(sql,"update stu set id=%d where name=\"%s\";",id,name);
char *errmsg=NULL;
if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
{
fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
return-1;
}
printf("update stu success\n");
break;
case '2':
printf("请输入要修改的同学id>>>");
id=0;
scanf("%d",&id);
while(getchar()!=10);
printf("请输入要修改后同学姓名>>>");
bzero(name,sizeof(name));
scanf("%s",name);
while(getchar()!=10);
bzero(sql,sizeof(sql));
sprintf(sql,"update stu set name=\"%s\" where id=%d;",name,id);
errmsg=NULL;
if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
{
fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
return-1;
}
printf("update stu success\n");
break;
case '3':
printf("请输入要修改的同学姓名>>>");
bzero(name,sizeof(name));
scanf("%s",name);
while(getchar()!=10);
printf("请输入要修改后同学分数>>>");
float score=0;
scanf("%g",&score);
while(getchar()!=10);
bzero(sql,sizeof(sql));
sprintf(sql,"update stu set score=%g where name=\"%s\";",score,name);
errmsg=NULL;
if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
{
fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
return-1;
}
printf("update stu success\n");
break;
}
return 0;
}
结果如图
1、插入信息
如图:添加了一条成员信息。
2、删除信息
如图:学号为1的同学信息被删除。
3、修改信息
如图:姓名为xin的同学分数75被修改为98。
4、查询
5、退出