1.数据库增删改查
#include<myhead.h>
#include<sqlite3.h>
typedef struct
{
int id;
char name[20];
double salary;
}worker;
int do_insert(sqlite3 *ppdb)
{
worker work;
printf("输入插入的ID:");
scanf("%d",&work.id);
printf("请输入插入的姓名:");
scanf("%s",work.name);
printf("输入插入的薪水:");
scanf("%lf",&work.salary);
char *errmsg = NULL;
char sqlite[100];
snprintf(sqlite,sizeof(sqlite),"insert into Work values(%d,\"%s\",%lf);",work.id,work.name,work.salary);
if(0!=sqlite3_exec(ppdb,sqlite,NULL,NULL,&errmsg))
{
perror("sqlite3_exec");
printf("错误码:%d错误信息:%s出错行:%d\n",
sqlite3_errcode(ppdb),sqlite3_errmsg(ppdb),__LINE__);
return -1;
}
printf("添加工人信息成功\n");
return 0;
}
int do_delete(sqlite3 *ppdb)
{
int id;
printf("请输入要删除工人的id:");
scanf("%d",&id);
char *errmsg = NULL;
char sqlite[100];
snprintf(sqlite,sizeof(sqlite),"delete from Work where id=%d;",id);
if(0!=sqlite3_exec(ppdb,sqlite,NULL,NULL,&errmsg))
{
perror("sqlite3_exec");
printf("错误码:%d错误信息:%s出错行:%d\n",
sqlite3_errcode(ppdb),sqlite3_errmsg(ppdb),__LINE__);
return -1;
}
printf("删除工人信息成功\n");
return 0;
}
int do_modfiy(sqlite3 *ppdb)
{
printf("\t\t\t **********修改**********\n");
printf("\t\t\t **********1.id**********\n");
printf("\t\t\t **********2.name**********\n");
printf("\t\t\t **********3.salary**********\n");
int id,temp_id,ch;
char temp_name[20],sqlite[100];
double temp_salary;
char *errmsg = NULL;
scanf("%d",&ch);
switch(ch)
{
case 1:printf("请输入要修改的id:");
scanf("%d",&id);
printf("请输入修改后的id:");
scanf("%d",&temp_id);
snprintf(sqlite,sizeof(sqlite),"update Work set id=%d where id=%d;",temp_id,id);
break;
case 2:printf("请输入要修改的id:");
scanf("%d",&id);
getchar();
printf("请输入修改后的name:");
fgets(temp_name,sizeof(temp_name),stdin);
temp_name[strlen(temp_name)-1]='\0';
snprintf(sqlite,sizeof(sqlite),"update Work set name=\"%s\" where id=%d;",temp_name,id);
break;
case 3:printf("请输入要修改的id:");
scanf("%d",&id);
printf("请输入修改后的salary:");
scanf("%lf",&temp_salary);
snprintf(sqlite,sizeof(sqlite),"update Work set salary=%lf where id=%d;",temp_salary,id);
break;
case 4:exit(0);
break;
default:;
}
if(0!=sqlite3_exec(ppdb,sqlite,NULL,NULL,&errmsg))
{
perror("sqlite3_exec");
printf("错误码:%d错误信息:%s出错行:%d\n",
sqlite3_errcode(ppdb),sqlite3_errmsg(ppdb),__LINE__);
return -1;
}
printf("修改工人信息成功\n");
return 0;
}
int callback(void *arg,int n,char **msgtext,char **msgtable)
{
int i,j;
for(i=0;i<n;i++)
{
printf("%s\t",*(msgtable+i));
}
printf("\n");
for(j=0;j<n;j++)
{
printf("%s\t",*(msgtext+j));
}
printf("\n");
printf("查找成功\n");
return 0;
}
int do_reseach(sqlite3 *ppdb)
{
char key[20];
printf("请输入你要查找的工人姓名:");
scanf("%s",key);
char *errmsg = NULL;
char sqlite[100];
snprintf(sqlite,sizeof(sqlite),"select * from Work where name==\"%s\";",key);
if(0!=sqlite3_exec(ppdb,sqlite,callback,NULL,&errmsg))
{
perror("sqlite3_exec");
printf("错误码:%d错误信息:%s出错行:%d\n",
sqlite3_errcode(ppdb),sqlite3_errmsg(ppdb),__LINE__);
return -1;
}
return 0;
}
int show_all(void *arg,int n,char **msgtext,char **msgtable)
{
int i,j;
for(i=0;i<n;i++)
{
printf("%s\t",*(msgtable+i));
}
printf("\n");
for(j=3;j<n+n;j++)
{
printf("%s\t",*(msgtable+j));
}
printf("\n");
return 0;
}
int do_show_all(sqlite3 *ppdb)
{
char *errmsg = NULL;
char sqlite[100]="select * from Work";
if(0!=sqlite3_exec(ppdb,sqlite,show_all,NULL,&errmsg))
{
perror("sqlite3_exec");
printf("错误码:%d错误信息:%s出错行:%d\n",
sqlite3_errcode(ppdb),sqlite3_errmsg(ppdb),__LINE__);
return -1;
}
return 0;
}
void mnue(sqlite3 *ppdb)
{
int ch;
printf("\t\t\t **********吸特乐的名单**********\n");
printf("\t\t\t**********1,插入工人信息**********\n");
printf("\t\t\t**********2,删除工人信息**********\n");
printf("\t\t\t**********3,修改工人信息**********\n");
printf("\t\t\t**********4,查找工人信息**********\n");
printf("\t\t\t**********5,所有工人信息**********\n");
printf("\t\t\t **********退出**********\n");
scanf("%d",&ch);
getchar();
switch(ch)
{
case 1:do_insert(ppdb);
break;
case 2:do_delete(ppdb);
break;
case 3:do_modfiy(ppdb);
break;
case 4:do_reseach(ppdb);
break;
case 5:do_show_all(ppdb);
break;
case 0:exit(0);
break;
default:;
}
}
int main(int argc,const char *argv[])
{
sqlite3 *ppdb;
int k=sqlite3_open("./stu1.db",&ppdb);
if(k!=SQLITE_OK)
{
perror("sqlite3_open");
printf("错误码:%d错误信息:%s出错行:%d\n",
sqlite3_errcode(ppdb),sqlite3_errmsg(ppdb),__LINE__);
return -1;
}
printf("数据库打开成功\n");
char *errmsg = NULL;
char sqlite[100]="create table if not exists Work(id int,name char,salary double);";
if(sqlite3_exec(ppdb,sqlite,NULL,NULL,&errmsg)!=0)
{
perror("sqlite3_exec");
printf("错误码:%d错误信息:%s出错行:%d\n",
sqlite3_errcode(ppdb),sqlite3_errmsg(ppdb),__LINE__);
return -1;
}
while(1)
{
mnue(ppdb);
}
sqlite3_close(ppdb);
return 0;
}