2025.1.2

练习：

1> 创建一个工人信息库，包含工号（主键）、姓名、年龄、薪资。

2> 添加三条工人信息（可以完整信息，也可以非完整信息）

3> 修改某一个工人的薪资（确定的一个）

4> 展示出工资在10000到20000之间的所有工人信息

5> 删除掉指定姓名工人的信息

6> 删除工人信息库

#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("sqlite3_open");
	}
	return ppDb; //返回数据库句柄
}
void insert_worker(sqlite3 *ppDb) //插入员工信息
{
	char sql[1024];
	char *errmsg; //存储错误信息的指针
	sprintf(sql,"%s","create table worker(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("创建表格成功\n");
	int num;
	char name[100];
	int age;
	int salary;
	printf("请输入你要添加的工人信息:\n");
	scanf("%d %s %d %d",&num,name,&age,&salary);
	getchar();
	snprintf(sql,sizeof(sql),"insert into worker values(%d,\"%s\",%d,%d);",num,name,age,salary);
	if(sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
	{
		printf("%s\n",sqlite3_errmsg(ppDb)); //错误信息
		printf("%d\n",sqlite3_errcode(ppDb)); //错误码
	}
	printf("添加成功\n");

}

void update_worker(sqlite3 *ppDb)
{
	int num;
	int salary;
	char * errmsg;
	char sql[1024];
	printf("请输入你要修改的工人的工号：\n");
	scanf("%d",&num);
	getchar();
	printf("请你输入改的值：\n");
	scanf("%d",&salary);
	getchar();
	snprintf(sql,sizeof(sql),"update worker set salary=%d where num=%d",salary,num);
	if(sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
	{
		printf("%s\n",sqlite3_errmsg(ppDb));
		printf("%d\n",sqlite3_errcode(ppDb));
	}
	printf("修改成功\n");
}
void show(sqlite3 *ppDb)
{
	char sql[1024];
	char * errmsg;
	int salary;
	const char *sq = "select * from worker where salary>10000 and salary<20000";
	sqlite3_stmt *stmt;
	snprintf(sql,sizeof(sql),"select * from worker where salary>10000 and salary<20000");
	if(sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
	{
		printf("%s\n",sqlite3_errmsg(ppDb));
		printf("%d\n",sqlite3_errcode(ppDb));
	}
	int res = sqlite3_prepare_v2(ppDb,sq,-1,&stmt,NULL);
	printf("工资在10000到20000的工人:\n");
	while((res = sqlite3_step(stmt))==SQLITE_ROW)
	{
		printf("num = %d,name = %s,age = %d,salary = %d\n",
		sqlite3_column_int(stmt,0),
		sqlite3_column_text(stmt,1),
		sqlite3_column_int(stmt,2),
		sqlite3_column_int(stmt,3));
	}
	sqlite3_finalize(stmt);
}
void delete_worker(sqlite3 *ppDb)
{
     char name[20];
     char * errmsg;
     char sql[1024];
     printf("请输入你要删除的工人的姓名：\n");
     scanf("%s",name);
     getchar();
     snprintf(sql,sizeof(sql),"delete from worker where name = '%s' ",name);
     if(sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
     {
         printf("%s\n",sqlite3_errmsg(ppDb));
         printf("%d\n",sqlite3_errcode(ppDb));
     }
     printf("删除成功\n");
}
void delete(sqlite3 *ppDb)
{
     char name[20];
     char * errmsg;
     char sql[1024];
     snprintf(sql,sizeof(sql),"drop table worker ");
     if(sqlite3_exec(ppDb,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
     {
         printf("%s\n",sqlite3_errmsg(ppDb));
         printf("%d\n",sqlite3_errcode(ppDb));
     }
     printf("删除成功\n");
}
void menu()
{
	int ch;
	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_worker(ppDb);
				}
				break;
			case 3:
				{
				update_worker(ppDb);
				}
				break;
			case 4:
				{
				show(ppDb);
				}
				break;
			case 5:
				{
				delete_worker(ppDb);
				}
				break;
			case 6:
				{
				delete(ppDb);
				}
				break;
			case 0:
				exit(0);
			break;
			default:
				printf("输入错误，请重新输入\n");

		}
	}
}
int main(int argc, const char *argv[])
{
	menu(); //调用菜单函数	
	return 0;
}