2023-1-5作业

使用sqlite3的C-APIs，用C语言实现数据库的插入、删除、修改等功能

代码实现如下：

#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>
#include <string.h>
#include <unistd.h>

//全字段插入
void do_insert(sqlite3*db);
//删除记录
void do_delete(sqlite3*db);
//修改记录
void do_update(sqlite3*db);
//查询记录
void do_select(sqlite3*db);

int main(int argc, const char *argv[])
{
	//创建并打开一个数据库
	sqlite3 *db = NULL;
	if(sqlite3_open("./sq.db", &db) != SQLITE_OK)
	{
		fprintf(stderr, "sqlite3_open failed:%d %s\n",\
				sqlite3_errcode(db), sqlite3_errmsg(db));
		return -1;
	}
	printf("sqlite3_open success\n");

	//创建一个表格
	char sql[128] = "create table if not exists stu (id int primary key, name char, score float);";
	char *errmsg = NULL;
	if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		fprintf(stderr, "line:%d sqlite3_exec failed: %s\n", __LINE__, errmsg);
		return -1;
	}
	printf("table stu create success\n");

	char choose = 0;
	while(1)
	{
		printf("--------------------------\n");
		printf("----------1.插入----------\n");
		printf("----------2.删除----------\n");
		printf("----------3.修改----------\n");
		printf("----------4.查询----------\n");
		printf("----------5.退出----------\n");
		printf("--------------------------\n");
		printf("请输入>>>");
		choose = getchar();
		while(getchar() != 10);

		switch(choose)
		{
		case '1':    
			do_insert(db); 		//插入
			break;
		case '2':
			do_delete(db); 		//删除
			break;
		case '3':
			do_update(db); 		//修改
			break;
		case '4':
			//do_select();
			break;
		case '5':
			goto END;
			break;
		default:
			printf("输入错误，请重新输入\n");
		}
		printf("输入任意字符清屏>>>");
		while(getchar() != 10)
			system("clear"); 	//清屏
	}

END:
	//关闭数据库连接
	if(sqlite3_close(db) != SQLITE_OK)
	{
		fprintf(stderr, "sqlite3_close failed:%d %s\n",\
				sqlite3_errcode(db), sqlite3_errmsg(db));
		return -1;
	}

	return 0;
}

//全字段插入
void do_insert(sqlite3*db)
{
	int id = 0;
	char name[20] = "";
	float score = 0;
	char sql[128] = "";
	char *errmsg = NULL;

	printf("请输入id 姓名 成绩\n");
	scanf("%d %s %g", &id, name, &score);
	while(getchar() != 10);

	//拼接SQL命令
	sprintf(sql, "insert into stu values (%d, \'%s\', %g);", id, name, score);

	//运行SQL命令
	if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		fprintf(stderr, "line:%d sqlite3_exec failed: %s\n", __LINE__, errmsg);
		return ;
	}
	printf("insert values success\n");
}

//删除记录
void do_delete(sqlite3*db)
{
	int id = 0;
	char sql[128] = "";
	char *errmsg = NULL;

	printf("请输入要删除记录的id>>>");
	scanf("%d", &id);
	while(getchar() != 10);

	//拼接SQL命令
	sprintf(sql, "delete from stu where id=%d;", id);

	//运行SQL命令
	if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		fprintf(stderr, "line:%d sqlite3_exec failed: %s\n", __LINE__, errmsg);
		return ;
	}
	printf("delete values success\n");
}

//修改记录
void do_update(sqlite3*db)
{
	int id = 0;
	char name[20] = "";
	float score = 0;
	char update[30] = "";
	char sql[128] = "";
	char *errmsg = NULL;

	printf("请输入id>>>");
	scanf("%d", &id);
	while(getchar() != 10);

	printf("您需要修改(name/score)>>>");
	scanf("%s", update);
	while(getchar() != 10);

	printf("修改成>>>");
	if(strcmp(update, "name") == 0)
	{
		scanf("%s", name);
		while(getchar() != 10);
		//拼接SQL命令
		sprintf(sql, "update stu set %s=\'%s\' where id=%d;", update, name, id);
	}
	else if(strcmp(update, "score") == 0)
	{
		scanf("%g", &score);
		while(getchar() != 10);
		//拼接SQL命令
		sprintf(sql, "update stu set %s=%g where id=%d;", update, score, id);
	}

	//运行SQL命令
	if(sqlite3_exec(db, sql, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		fprintf(stderr, "line:%d sqlite3_exec failed: %s\n", __LINE__, errmsg);
		return ;
	}
	printf("update values success\n");
}

运行结果如下：