#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>
#include <string.h>
#include <unistd.h>
int do_insert(sqlite3 *db,char *errmsg);
int do_delete(sqlite3 *db,char *errmsg);
int update(sqlite3 *db,char *errmsg);
int main(int argc, const char *argv[])
{
//创建并打开数据库
sqlite3 *db=NULL;
if(sqlite3_open("./my.db",&db)!=SQLITE_OK){
fprintf(stderr,"line:%d %s\n",__LINE__,sqlite3_errmsg(db));
fprintf(stderr,"line:%d %d\n",__LINE__,sqlite3_errcode(db));
return -1;
}
printf("sqlite3_open success\n");
//创建表格存储数据
char *errmsg=NULL;
char buf[128]="create table if not exists stdent (id int primary key,name char,age int,score float);";
if(sqlite3_exec(db,buf,NULL,NULL,&errmsg)!=SQLITE_OK){
fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
return -1;
}
printf("sqlite3_exec success\n");
char c;
while(1){
system("clear");
printf("*********************\n");
printf("*******1.增**********\n");
printf("*******2.删**********\n");
printf("*******3.改**********\n");
printf("*******4.查**********\n");
printf("*******5.exit*********\n");
printf("*********************\n");
printf("请输入>>>");
scanf("%c",&c);
while(getchar()!=10);
switch(c)
{
case '1':
do_insert(db,errmsg);
break;
case '2':
do_delete(db,errmsg);
break;
case '3':
update(db,errmsg);
break;
case '4':
break;
case '5':
goto END;
default:
printf("输入有误\n");
break;
}
printf("输入任意字符清屏\n");
while(getchar()!=10);
}
END:
if(sqlite3_close(db)!=SQLITE_OK){
fprintf(stderr,"line:%d %s\n",__LINE__,sqlite3_errmsg(db));
fprintf(stderr,"line:%d %d\n",__LINE__,sqlite3_errcode(db));
return -1;
}
return 0;
}
int do_insert(sqlite3 *db,char *errmsg)
{
int id,age;
char name[20],score[20],buf[128];
bzero(buf,sizeof(buf));
printf("请输入学生的学号、姓名、年龄以及分数\n");
scanf("%d %s %d %s",&id,name,&age,score);
sprintf(buf,"insert into stdent values(%d,\"%s\",%d,%s);",id,name,age,score);
printf("%s\n",buf);
if(sqlite3_exec(db,buf,NULL,NULL,&errmsg)!=SQLITE_OK){
fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
return -1;
}
return 0;
}
int do_delete(sqlite3 *db,char *errmsg)
{
int id,age;
char name[20],score[20],buf[128];
bzero(buf,sizeof(buf));
printf("请输入要删除的学号和名字\n");
scanf("%d %s",&id,name);
sprintf(buf,"delete from stdent where id=%d and name=\"%s\";",id,name);
if(sqlite3_exec(db,buf,NULL,NULL,&errmsg)!=SQLITE_OK){
fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
return -1;
}
return 0;
}
int update(sqlite3 *db,char *errmsg)
{
int id,age;
char name[20],score[20],buf[128];
bzero(buf,sizeof(buf));
printf("请输入学号修改姓名、年龄以及分数\n");
scanf("%d %s %d %s",&id,name,&age,score);
sprintf(buf,"update stdent set name=\"%s\",age=%d,score=%s where id=%d;",name,age,score,id);
if(sqlite3_exec(db,buf,NULL,NULL,&errmsg)!=SQLITE_OK){
fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
return -1;
}
return 0;
}