当前位置：首页 > article >正文

SQLITE3数据库实现信息的增删改查

article 2024/11/12 17:48:54

#include <myhead.h>
#include <sqlite3.h>

typedef struct
{
   int id;
   char name[20];
   int age;
   int money;
}woker;
int callbake(void *arg,int n,char **a,char **b)//回调输出查找到的工人信息
{
   for(int i =0;i<n;i++)
   {
       printf("%s\t",*(b+i));
   }
   putchar(10);
   for(int i=0;i<n;i++)
   {
       printf("%s\t",*(a+i));
   }
   putchar(10);
   printf("查找成功\n");
   return 0;
}
int do_insert(sqlite3 *ppDb)
{
   woker work;
   printf("请输入工号\n");
   scanf("%d",&work.id);
   printf("请输入工姓名\n");
   scanf("%s",work.name);
   printf("请输入年龄\n");
   scanf("%d",&work.age);
   printf("请输入薪资\n");
   scanf("%d",&work.money);
   char *errmsg = NULL;//存储错误信息
   char sqlite[100];//插入一个工人信息
   snprintf(sqlite,sizeof(sqlite),"insert into Work values(%d ,\"%s\", %d, %d);",work.id,work.name,work.age,work.money);

   if(sqlite3_exec(ppDb,sqlite,NULL,NULL,&errmsg)!=0)//实现插入一个员工信息
   {
       perror("sqlite3_exec");
       printf("错误码：%d错误信息：%s,出错行:%d\n",
               sqlite3_errcode(ppDb),sqlite3_errmsg(ppDb),__LINE__);
       printf("错误信息：%s\n",errmsg);
       return -1;
   }
   printf("添加成功\n");
}
int do_reserch(sqlite3 *ppDb)
{
   printf("请输入要查的员工姓名\n");
   char key[100];
   scanf("%s",key);
   char *errmsg = NULL;//存储错误信息
   char sqlite[100];
   snprintf(sqlite,sizeof(sqlite),"select * from Work where name==\"%s\";",key);
   if(sqlite3_exec(ppDb,sqlite,callbake,NULL,&errmsg)!=0)//查找一个工人信息
   {
       perror("sqlite3_exec");
       printf("错误码：%d错误信息:%s,出错行：%d\n",sqlite3_errcode(ppDb),sqlite3_errmsg(ppDb),__LINE__);
       printf("错误信息：%s\n",errmsg);
       return -1;
   }
   printf("查找成功\n");
}
int do_delete(sqlite3 *ppDb)
{
   printf("请输入要删除的员工信息\n");
   char key[20];
   scanf("%s",key);
   char *errmsg = NULL;
   char sqlite[100];
   snprintf(sqlite,sizeof(sqlite),"delete from Work where name == \"%s\";",key);
   if(sqlite3_exec(ppDb,sqlite,NULL,NULL,&errmsg)!=0)
   {
       perror("sqlite3_exec");
       printf("错误码：%d错误信息:%s,出错行：%d\n",sqlite3_errcode(ppDb),sqlite3_errmsg(ppDb),__LINE__);
       printf("错误信息：%s\n",errmsg);
       return -1;
   }
   printf("删除成功\n");
}
int do_updata(sqlite3 *ppDb)
{
   printf("请输入你要修改的员工姓名\n");
   char key[20];
   scanf("%s",key);
   printf("请输入你想修改员工的什么信息\n");
   printf("\t1.工号\n");
   printf("\t2.姓名\n");
   printf("\t3.年龄\n");
   printf("\t4.薪资\n");
   printf("\t5.全部修改\n");
   int n;
   scanf("%d",&n);
   getchar();
   char *errmsg = NULL;
   char sqlite[100];
   switch(n)
   {
   case 1:
       printf("新工号\n");
       int a;
       scanf("%d",&a);
       getchar();
       snprintf(sqlite,sizeof(sqlite),"update Work set id =%d where name == \"%s\";",a,key);
       break;
   case 2:
       printf("新姓名\n");
       char arr[20];
       scanf("%s",arr);
       getchar();
       snprintf(sqlite,sizeof(sqlite),"update Work set name =\"%s\" where name == \"%s\";",arr,key);
       break;
   case 3:
       printf("新年龄\n");
       int b;
       scanf("%d",&b);
       getchar();
       snprintf(sqlite,sizeof(sqlite),"update Work set age = %d where name == \"%s\";",b,key);
       break;
   case 4:
       printf("新薪资\n");
       int c;
       scanf("%d",&c);
       getchar();
       snprintf(sqlite,sizeof(sqlite),"update Work set money = %d where name == \"%s\";",c,key);
       break;
   case 5:
       break;
   }
   if(sqlite3_exec(ppDb,sqlite,NULL,NULL,&errmsg)!=0)
   {
       perror("sqlite3_exec");
       printf("错误码：%d错误信息:%s,出错行：%d\n",sqlite3_errcode(ppDb),sqlite3_errmsg(ppDb),__LINE__);
       printf("错误信息：%s\n",errmsg);
       return -1;
   }
   printf("更改成功\n");
}
void mnue(sqlite3 *ppDb)
{
   printf("--------狗熊岭员工信息表--------\n");
   printf("\t1,插入一个成员的信息\n");
   printf("\t2,删除一个成员的信息\n");
   printf("\t3,修改一个成员的信息\n");
   printf("\t4,查找一个成员的信息\n");
   printf("\t0.退出\n");
   printf("请输入你的选择\n");
   int n;
   scanf("%d",&n);
   getchar();
   switch(n)
   {
   case 1:
       do_insert(ppDb);
       break;
   case 2:
       do_delete(ppDb);
       break;
   case 3:
       do_updata(ppDb);
       break;
   case 4:
       do_reserch(ppDb);
       break;
   case 5:
       //do_showall(ppDb);
       break;
   case 0:
       exit(0);
       break;
   default:
       printf("输入错误\n");
   }
}
int main(int argc, const char *argv[])
{
   sqlite3 *ppDb;//1.创建或打开一个数据库文件
   int k = sqlite3_open("./Workdata.dp",&ppDb);
   if(k != SQLITE_OK)
   {
       perror("sqlit3_open");
       printf("错误码:%d错误信息：%s, 出错行是%d\n",
           sqlite3_errcode(ppDb),sqlite3_errmsg(ppDb),__LINE__);
       return -1;
   }
   printf("数据库打开成功\n");
   //2.创建表格
   char *errmsg = NULL;
   char sqlite[100] = "create table if not exists Work(id int,name char,age int,money int);";//创建一个表格
   //printf("");
   if(sqlite3_exec(ppDb,sqlite,NULL,NULL,&errmsg)!=0)
   {
       perror("sqlite3_exec");
       printf("错误码：%d,错误信息：%s,出错的行:%d\n",
               sqlite3_errcode(ppDb),sqlite3_errmsg(ppDb),__LINE__);
       printf("错误信息：%s\n",errmsg);
       return -1;
   }
   while(1)
   {
       mnue(ppDb);//数据库指针作为参数
   }
   sqlite3_close(ppDb);

   return 0;
}