2025.01.02（数据库）

作业：实现以下功能：

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

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

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

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

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

6> 工厂倒闭，删除整个工人信息库

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

typedef struct 
{
	int num ;
	char name[20];
	int age;
	int salary; 

}Worker;

// 回调函数
#if 0
int callback(void *NotUsed,int argc,char **argv,char **azColName)
{
	for(int i=0;i<argc;i++)
	{
		printf("%s = %s  \t",azColName[i],argv[i]?argv[i]:"NULL");
	}
	printf("\n");
	return 0;
}
#endif
int callback(void *aaa,int n,char **m,char **xin)
{
	int i,j;
	static int flag = 0;
	if(flag == 0)
	{
		for(i=0;i<n;i++)
		{
			printf("%s\t",*(xin+i));
		}
		printf("\n");
	}
	flag += 1;
	for(int j=0;j<n;j++)
	{
		printf("%s\t",*(m+j));
	}
	printf("\n");
	return 0;
}
sqlite3 * creat_squlte() //创建数据库并返回数据库句柄
{
	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));
		perror("sqlite3_open");
	}
	return ppDb;//返回数据库句柄
}

void insert_worker(sqlite3 *ppDb)//插入员工信息
{
	char sql[1024];
	Worker work;
	char *errmsg ;
	sprintf(sql,"%s","create table if not exists works(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("出错信息：%s\n",errmsg); 		//错误信息
	}

	while(1)
	{
		printf("请输入员工相关信息：\n");
		printf("请输入员工工号,输入“000”结束\n");
		int a1 = 000;
		scanf("%d",&work.num);
		while(getchar()!='\n');
		if(work.num == a1)
		{
			printf("录入完毕\n");
			break;
		}
		printf("请输入员工姓名\n");
		scanf("%s",work.name);
		while(getchar()!='\n');
		printf("请输入员工年龄\n");
		scanf("%d",&work.age);
		while(getchar()!='\n');
		printf("请输入员工工资\n");
		scanf("%d",&work.salary);
		while(getchar()!='\n');

		sprintf(sql,"insert into works values(%d,\"%s\",%d,%d);",work.num,work.name,work.age,work.salary);
		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 ;
		}
	}

}

void Revise_worker(sqlite3 *ppDb) 		//修改工人信息
{
	char sql[1024];
	Worker work;
	char *errmsg;
	printf("请输入要修改的员工工号\n");
	scanf("%d", &work.num);
	while (getchar() != '\n');

	printf("请输入新的员工姓名\n");
	scanf("%s", work.name);
	while (getchar() != '\n');

	printf("请输入新的员工年龄\n");
	scanf("%d", &work.age);
	while (getchar() != '\n');

	printf("请输入新的员工工资\n");
	scanf("%d", &work.salary);
	while (getchar() != '\n');

	sprintf(sql, "UPDATE works SET name = '%s', age = %d, salary = %d WHERE num = %d;",
			work.name, work.age, work.salary, work.num);
	if (sqlite3_exec(ppDb, sql, NULL, NULL, NULL) != SQLITE_OK) 
	{
		printf("%s\n",sqlite3_errmsg(ppDb));
		printf("%d\n",sqlite3_errcode(ppDb));
		printf("错误行：%d\n",__LINE__);
		return ;
	}
	else
	{
		printf("修改成功\n");
	}
}

//方法1
//外部传参记录次数

//方法2
#if 0
void show_worker(sqlite3 *ppDb) 			//展示所有信息
{

	char sql[1024];
	Worker work;
	char *errmsg;
	sprintf(sql,"%s","select * from works");
	if (sqlite3_exec(ppDb, sql,callback, NULL, NULL) != SQLITE_OK) 
	{
		printf("%s\n",sqlite3_errmsg(ppDb));
		printf("%d\n",sqlite3_errcode(ppDb));
		printf("错误行：%d\n",__LINE__);
		return ;
	}
}


#endif
//方法三
//使用新的函数获取信息
#if 1
void show_worker(sqlite3 *ppDb)
{
	char sql[1024];
	char *errmsg; 		//存储错误信息的指针
	char **p;
	int hang;
	int lie;
	sprintf(sql,"%s","select * from works;");
	if(sqlite3_get_table(ppDb,sql,&p,&hang,&lie,&errmsg)!=SQLITE_OK)
	{
		printf("%s\n",sqlite3_errmsg(ppDb));
		printf("%d\n",sqlite3_errcode(ppDb));
		printf("错误行：%d\n",__LINE__);
		return ;
	}
	int i,j;
	for(i=0;i<hang;i++)
	{
		for(j=0;j<lie;j++)
		{
			printf("%s\t",*(p+(lie*i)+j));
		}
		printf("\n");
	}
}
#endif
void delete_worker(sqlite3 *ppDb) 		//删除指定工人信息
{
	
	char sql[1024];
	Worker work;
	char *errmsg;
	char bbb[20];
	printf("输入要删除员工的编号\n");
	scanf("%s",bbb);
	sprintf(sql,"delete from works where num = %s",bbb);
	if (sqlite3_exec(ppDb, sql, NULL, NULL, NULL) != SQLITE_OK) 
	{
		printf("%s\n",sqlite3_errmsg(ppDb));
		printf("%d\n",sqlite3_errcode(ppDb));
		printf("错误行：%d\n",__LINE__);
		return ;
	}
	else
	{
		printf("删除成功\n");
	}

}
void delete_all_worker(sqlite3 *ppDb) 	//删除整表
{
	char sql[1024];
	char *errmsg;
	sprintf(sql,"delete from works");

	if (sqlite3_exec(ppDb, sql, NULL, NULL, NULL) != SQLITE_OK) 
	{
		printf("%s\n",sqlite3_errmsg(ppDb));
		printf("%d\n",sqlite3_errcode(ppDb));
		printf("错误行：%d\n",__LINE__);
		return ;
	}
	else
	{
		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请输入你的选择\n");
		scanf("%d",&ch);
		while(getchar()!='\n');
		switch(ch)
		{
		case 1:
			ppDb = creat_squlte(); 		//创建数据库函数
			break;
		case 2:
			insert_worker(ppDb); 		//添加工人信息
			break;
		case 3:
			Revise_worker(ppDb); 		//修改工人信息
			break;
		case 4:
			show_worker(ppDb); 			//展示所有信息
			break;
		case 5:
			delete_worker(ppDb); 		//删除制定工人信息
			break;
		case 6:
			delete_all_worker(ppDb); 	//删除整表
			break;
		case 0:
			exit(0); 				//退出
			break;
		default :printf("输入的功能有误，请从新输入\n");
		}
	}
}
int main(int argc, const char *argv[])
{
	menu();
	return 0;
}

思维导图