作业需求:在Linux环境下利用代码完成数据库的插入、删除、修改的功能;
实现过程:
#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>
void* do_insert(sqlite3 *db);
void* do_delete(sqlite3 *db);
void* do_update(sqlite3 *db);
void* do_select(sqlite3 *db);
int main(int argc, const char *argv[])
{
sqlite3 *db=NULL;
if(sqlite3_open("./sq.db",&db)!=SQLITE_OK)
{
fprintf(stderr,"sqlite3_open failed:%d:%s\n",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");
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();
break;
case '5':
goto END;
break;
default:
printf("输入错误,请重新输入\n");
}
printf("输入任意字符清屏>>>");
while(getchar()!=10);
}
END:
if(sqlite3_close(db)!=SQLITE_OK)
{
fprintf(stderr,"sqlite3_close failed:%d:%s\n",sqlite3_errcode(db),sqlite3_errmsg(db));
return -1;
}
return 0;
}
void* do_insert(sqlite3 *db)
{
int id;
char name[20];
float score;
char str[200];
printf("请输入要插入的数据>>>");
scanf("%d%s%f",&id,name,&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);
}
printf("insert into stu values success\n");
}
void* do_delete(sqlite3 *db)
{while(getchar()!=10);
char choose=0;
printf("------------------------\n");
printf("--6.删除表格内所有信息--\n");
printf("--7.删除表格内个别信息--\n");
printf("------other.退出--------\n");
printf("------------------------\n");
choose=getchar();
while(getchar()!=10);
if(choose=='6')
{
char table[20];
printf("输入要清空的表格名>>>");
scanf("%s",table);
while(getchar()!=10);
char sql[128]="";
char *errmsg = NULL;
sprintf(sql,"DELETE FROM %s",table);
if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
{
fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
}
printf("DELETE FROM table WHERE id success\n");
}
else if(choose=='7')
{
char table[20];
int id;
printf("请输入要从那个表格删除>>>");
scanf("%s",table);
while(getchar()!=10);
printf("请输入要删除的信息的id>>>");
scanf("%d",&id);
while(getchar()!=10);
char sql[128]="";
sprintf(sql,"DELETE FROM %s WHERE id=%d",table,id);
char *errmsg = NULL;
if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
{
fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
}
printf("DELETE FROM table WHERE id success\n");
}
}
void* do_update(sqlite3 *db)
{
int id;
char table[20];
char str[200];
printf("请输入要从那个表格修改>>>");
scanf("%s",table);
while(getchar()!=10);
printf("请输入要修改的内容>>>");
scanf("%s",str);
while(getchar()!=10);
printf("请输入要修改信息的id>>>");
scanf("%d",&id);
while(getchar()!=10);
char sql[128]="";
sprintf(sql,"update %s set %s where id=%d",table,str,id);
char *errmsg = NULL;
if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
{
fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
}
printf("insert into stu values success\n");
}
实现结果:
![](https://i-blog.csdnimg.cn/blog_migrate/d28c0c66a9a93bd555ab46bb9b24dab6.png)
![](https://i-blog.csdnimg.cn/blog_migrate/c867f1a59cf2f017ae320ff489225238.png)
![](https://i-blog.csdnimg.cn/blog_migrate/2f0e6af536b0a70f52f0f6f1733aa93f.png)