9.10作业

#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 callback(void *arg,int n,char **msgtext,char **msgtable)
{
/*    printf("n=%d\n",n);
    printf("*a=%s\n",*(a+1));
    printf("*b=%s\n",*(b+1));
*/
    int i,j;
    for(i=0;i<n;i++)
    {
		 printf("%s\t",*(msgtable+i));
    }
	putchar(10);
	for(j=0;j<n;j++)
	{
	printf("%s\t",*(msgtext+j));
	}
	putchar(10);
	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));
	 }
		 putchar(10);	 
/*		 
		 for(j=0;j<n;j++)
	{
		printf("%s\t",*(msgtext+j));
	}
	 
	 putchar(10);
	 */
	return 0;
}
int do_showall(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;
}
int do_dele(sqlite3 *ppdb)
{
	char *errmsg = NULL;
	char key[20];
	printf("请输入要删除的工人姓名:");
	scanf("%s",key);
    char sqlite[100];
	snprintf(sqlite,sizeof(sqlite),"delete from Work where name=\"%s\";",key);
    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_change(sqlite3 *ppdb)
{
	char *errmsg = NULL;
	char a[20];
	char b[20];
//	double c;
	printf("请输入要修改的工人姓名:");
	scanf("%s",a);
	printf("请输入修改后的姓名:");
	scanf("%s",b);
//	printf("请输入该工人要修改的工资:");
//	scanf("%lf",&c);
    char sqlite[100];
	snprintf(sqlite,sizeof(sqlite),"update Work set name=\"%s\" where name=\"%s\";",b,a);
//	snprintf(sqlite,sizeof(sqlite),"update Work set salary=\"%lf\" where name=\"%s\";",c,b);
    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;


       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_dele(ppdb);

        break;
	case 3:do_change(ppdb);
        break;
    case 4:do_reseach(ppdb);
        break;
	case 5:do_showall(ppdb);
		break;
    case 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;
}