2024-3-6-数据库作业

 作业：数据库操作的增、删、改完成

源代码：

#include <myhead.h>
int do_add(sqlite3 *ppDb)
{
	int numb;
	double salary;
	char name[20];
	printf("请输入要添加员工的工号:");
	scanf("%d", &numb);
	printf("请输入要添加员工的姓名:");
	scanf("%s", name);
	printf("请输入要添加员工的工资:");
	scanf("%lf", &salary);
	printf("请输入要插入的信息:");

	char sql[128] = "";
	sprintf(sql, "INSERT INTO worker VALUES(%d,'%s',%lf)", numb, name, salary);

	char *errmsg = NULL;
	if (sqlite3_exec(ppDb, sql, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		printf("exec error : %s\n", errmsg);
		sqlite3_free(errmsg);
		errmsg = NULL;
		return -1;
	}
	printf("添加成功:%d%s\n ", __LINE__, __func__);
	return 0;
}

void do_del(sqlite3 *ppDb)
{
	char *errmsg = NULL;
	char sql[128] = "delete from Worker where numb = 1001;";
	if (sqlite3_exec(ppDb, sql, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		printf("exec error:%s\n", errmsg);
		sqlite3_free(errmsg);
		errmsg = NULL;
		return;
	}
	printf("Delete success\n");
}
void do_update(sqlite3 *ppDb)
{
	char *errmsg = NULL;
	char sql[128] = "update Worker set salary = 8000 where numb = 1001;";
	if (sqlite3_exec(ppDb, sql, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		printf("exec error:%s\n", errmsg);
		sqlite3_free(errmsg);
		errmsg = NULL;
		return;
	}
	printf("Update success\n");
}

int search_callback(void *arg, int cols, char **dataHead, char **tabHead)
{
	if (*(int *)arg == 0)
	{
		for (int i = 0; i < cols; i++)
		{

			printf("%s\t", *(tabHead + i));
		}
		printf("\n");
		*(int *)arg = 1;
	}

	for (int i = 0; i < cols; i++)
	{
		printf("%s\t", *(dataHead + i));
	}
	printf("\n");
	return 0;
}

int do_search(sqlite3 *ppDb)
{
	char sql[128] = "select * from Worker;";

	int flag = 0;
	char *errmsg = NULL;
	if (sqlite3_exec(ppDb, sql, search_callback, &flag, &errmsg) != SQLITE_OK)
	{
		printf("exec error : %s\n", errmsg);
		sqlite3_free(errmsg);
		errmsg = NULL;
		return -1;
	}

	printf("search success\n");
	return 0;
}

int main(int argc, const char *argv[])
{
	sqlite3 *ppDb = NULL;
	if (sqlite3_open("./my.db", &ppDb) != SQLITE_OK)
	{
		printf("sqlite3_open error:%s\n", sqlite3_errmsg(ppDb));
		return -1;
	}
	printf("sqlite3_open success\n");

	//创建数据表
	char sql[128] = "create table if not exists Worker(numb int,name char,salary double)";
	char *errmsg = NULL;
	if (sqlite3_exec(ppDb, sql, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		printf("exec error:%s\n", errmsg);
		sqlite3_free(errmsg);
		errmsg = NULL;
		return -1;
	}
	printf("Worker table create success\n");

	int menu = -1;
	while (1)
	{
		system("clear");
		printf("\t\t=======xx公司员工管理系统xx====\n");
		printf("\t\t ======1、添加员工信息=======\n");
		printf("\t\t ======2、删除员工信息=======\n");
		printf("\t\t ======3、修改员工信息=======\n");
		printf("\t\t ======4、查询员工信息=======\n");
		printf("\t\t ======0、退出===============\n");

		printf("Enter mode:");
		scanf("%d", &menu);
		getchar();

		switch (menu)
		{
		case 1:
		{
			do_add(ppDb);
		}
		break;
		case 2:
		{
			do_del(ppDb);
		}
		break;
		case 3:
		{
			do_update(ppDb);
		}
		break;
		case 4:
		{
			do_search(ppDb);
		}
		break;
		case 0:
			goto END;

		default:
			printf("输入错误，请重新输入\n");
		}

		printf("请输入任意键，按回车清屏\n");
		while (getchar() != '\n')
			;
	}

END:
	sqlite3_close(ppDb);
	return 0;
}

效果图：