0102作业

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

//创建数据库函数
sqlite3 * creat_sqlite()//创建数据库并返回数据库的句柄
{
	const char *p = "./my.db";
	sqlite3 *ppDb;

	if(sqlite3_open(p,&ppDb)!=SQLITE_OK)//调用数据库提供的第三方库函数
	{
		printf("打开库函数失败\n");
		printf("%s\n",sqlite3_errmsg(ppDb));//错误信息
		printf("%d\n",sqlite3_errcode(ppDb));//错误码
		printf("错误行:%d\n",__LINE__);
	}
	return ppDb;//返回数据库句柄
}

//创建表格，添加信息函数
int insert_sorker(sqlite3 *ppDb,int n)//插入员工信息
{
	char sql[1024];
	char *errmsg;//存储错误信息的指针
	sprintf(sql,"%s","create table work(num int Primary key,name char,age int,salary int);");
	if(sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
	{
		printf("%s\n",sqlite3_errmsg(ppDb));//错误信息
		printf("%d\n",sqlite3_errcode(ppDb));//错误码
		printf("错误行:%d\n",__LINE__);
	}
	printf("创建表格成功\n");

	//插入工人信息
	char sql1[1024];
	char *errmsg1;

	int num;
	char name[20];
	int age;
	int salary;
	printf("请输入工人的工号：");
	scanf("%d",&num);
	printf("请输入工人的姓名：");
	scanf("%s",name);
	printf("请输入工人的年龄：");
	scanf("%d",&age);
	printf("请输入工人的工资：");
	scanf("%d",&salary);

	sprintf(sql1,"insert into work values(%d,'%s',%d,%d);",num,name,age,salary);
	if(sqlite3_exec(ppDb,sql1,NULL,NULL,&errmsg)!=SQLITE_OK)
	{
		printf("%s\n",sqlite3_errmsg(ppDb));//错误信息
		printf("%d\n",sqlite3_errcode(ppDb));//错误码
		printf("错误行:%d\n",__LINE__);
		return -1;
	}
	n++;
	printf("工人信息录入成功\n");
	return 0;
}

//展示函数
int callback(void *NotUsed, int argc, char **argv, char **azColName)
{
    for (int i = 0;i<argc;i++) 
	{
        printf("%s = %s\n",azColName[i],argv[i]?argv[i]:"NULL");
    }
    printf("\n");
    return 0;
}
void show_socker(sqlite3 *ppDb)
{
	char sql[1024];
	char *errmsg;
	printf("信息展示如下：\n");
	sprintf(sql,"%s","select * from work;");
	if(sqlite3_exec(ppDb,sql,callback,NULL,&errmsg)!=SQLITE_OK)
	{
		printf("%s\n",sqlite3_errmsg(ppDb));//错误信息
		printf("%d\n",sqlite3_errcode(ppDb));//错误码
		printf("错误行:%d\n",__LINE__);
	}
}

//修改信息函数
int change_socker(sqlite3 *ppDb,int n)
{
	int num;
	printf("请输入要修改的工人的工号：");
	scanf("%d",&num);
	if(num<=0 && num>=n)
	{
		printf("您输入的工号不存在\n");
		return -1;
	}

	int k;
	int h=1;
	while(h)
	{
		printf("\t\t\t1,姓名\n");
		printf("\t\t\t2,年龄\n");
		printf("\t\t\t3,工资\n");
		printf("\t\t\t0.退出\n");
		printf("请输入修改的选项:");
		scanf("%d",&k);
		switch(k)
		{
		case 1:
			{
				char sql[1024];
				char *errmsg;
				char newname[20];
				printf("请输入新的姓名：");
				scanf("%s",newname);
				sprintf(sql,"UPDATE work SET name='%s' WHERE num=%d;",newname,num);
				if(sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
				{
					printf("%s\n",sqlite3_errmsg(ppDb));//错误信息
					printf("%d\n",sqlite3_errcode(ppDb));//错误码
					printf("错误行:%d\n",__LINE__);
				}
				printf("姓名更改成功\n");
			}
			break;
		case 2:
			{
				char sql1[1024];
				char *errmsg1;
				int newage;
				printf("请输入新的年龄：");
				scanf("%d",&newage);
				sprintf(sql1,"update work set age=%d where num=%d;",newage,num);
				if(sqlite3_exec(ppDb,sql1,NULL,NULL,&errmsg1)!=SQLITE_OK)
				{
					printf("%s\n",sqlite3_errmsg(ppDb));//错误信息
					printf("%d\n",sqlite3_errcode(ppDb));//错误码
					printf("错误行:%d\n",__LINE__);
				}
				printf("年龄更改成功\n");

			}
			break;
		case 3:
			{
				char sql2[1024];
				char *errmsg2;
				int newsalary;
				printf("请输入新的工资：");
				scanf("%d",&newsalary);
				sprintf(sql2,"update work set salary=%d where num=%d;",newsalary,num);
				if(sqlite3_exec(ppDb,sql2,NULL,NULL,&errmsg2)!=SQLITE_OK)
				{
					printf("%s\n",sqlite3_errmsg(ppDb));//错误信息
					printf("%d\n",sqlite3_errcode(ppDb));//错误码
					printf("错误行:%d\n",__LINE__);
				}
				printf("工资更改成功\n");

			}
			break;
		case 0:h=0;break;
		default:printf("您输入选项有误，请重新输入\n");continue;
		}
	}
}

//删除工人信息函数
int delete_socker(sqlite3 *ppDb,int n)
{
	int del_num;
	printf("请输入需要删除工人的工号:");
	scanf("%d",&del_num);
	if(del_num<=0 && del_num>=n)
	{
		printf("您输入的工号不存在\n");
		return -1;
	}
	

	char sql[1024];
	char *errmsg;
	sprintf(sql,"delete from work where num=%d",del_num);
	if(sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
	{
		printf("%s\n",sqlite3_errmsg(ppDb));//错误信息
		printf("%d\n",sqlite3_errcode(ppDb));//错误码
		printf("错误行:%d\n",__LINE__);
		printf("%s\n",sqlite3_errmsg(ppDb));//错误信息
		printf("%d\n",sqlite3_errcode(ppDb));//错误码
		printf("错误行:%d\n",__LINE__);
		return -1;
		return -1;
	}
	n--;
	printf("删除成功\n");
	return 0;
}

//删除所有信息函数
int deleteall_socker(sqlite3 *ppDb,int n)
{
	char sql[1024];
	char *errmsg;
	sprintf(sql,"delete from work");
	if(sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
	{
		printf("%s\n",sqlite3_errmsg(ppDb));//错误信息
		printf("%d\n",sqlite3_errcode(ppDb));//错误码
		printf("错误行:%d\n",__LINE__);
		return -1;
	}
	printf("删除成功\n");
	return 0;
}

//菜单函数
void menu()
{
	int ch;
	int count;
	sqlite3 *ppDb;//数据库句柄
	while(1)
	{
		printf("\t\t\t1.创建数据库\n");
		printf("\t\t\t2.添加工人信息\n");
		printf("\t\t\t3.展示工人信息\n");
		printf("\t\t\t4.修改工人信息\n");
		printf("\t\t\t5.删除指定工人的信息\n");
		printf("\t\t\t6.删除整表\n");
		printf("\t\t\t0.退出系统\n");

		printf("请输入您的选择：");
		scanf("%d",&ch);
		getchar();

		switch(ch)
		{
		case 1:
			{
				ppDb = creat_sqlite();//创建数据库函数
			}
			break;
		case 2:
			{
				insert_sorker(ppDb,count);//添加多人信息
			}
			break;
		case 3:
			{
				show_socker(ppDb);//展示多人信息
			}
			break;
		case 4:
			{
				change_socker(ppDb,count);//修改多人信息
			}
			break;
		case 5:
			{
				delete_socker(ppDb,count);//删除指定工人信息
			}
			break;
		case 6:
			{
				deleteall_socker(ppDb,count);//删除所有工人信息
			}
			break;
		case 0:exit(0);break;
		}
	}
}
int main(int argc, const char *argv[])
{
	menu();//调用菜单函数
	return 0;
}