2023年4月25日 星期二 网络编程作业

完成数据库的增删查

代码

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

int main(int argc, const char *argv[])
{
	//创建并打开数据库
	sqlite3* db = NULL;
	if(sqlite3_open("./my.db",&db) != SQLITE_OK){

		printf("line:%d sqlite3_open:[%d] %s\n",__LINE__,sqlite3_errcode(db),sqlite3_errmsg(db));
		return -1;
	}
	printf("sqlite3_open success\n");

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

	char c = 0;
	int id = 0;
	int dele_id = 0;
	char name[128] = "";
	int score = 0;


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

		switch(c){
		case '1':
			printf("按格式输入要插入的字段：");
			scanf("%d %s %d",&id,name,&score);
			sprintf(sql,"INSERT INTO stu VALUES (%d,\"%s\",%d);",id,name,score);
			if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK){
				printf("line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
				return -1;
			}
			printf("INSERT INTO stu success\n");
			break;
		case '2':
			printf("输入要删除的id：");
			scanf("%d",&dele_id);
			sprintf(sql,"delete from stu where (id=%d);",dele_id);
			if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK){
				printf("line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
				return -1;
			}
			printf("delete from stu success\n");
			break;
		case '3':
			printf("按格式输入要修改的字段：");
			scanf("%d %s %d",&id,name,&score);
			sprintf(sql,"update stu set name=\"%s\",score=%d where id=%d;",name,score,id);
			if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK){
				printf("line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
				return -1;
			}
			printf("update stu success\n");
			break;
		case '4':
		   /* sprintf(sql,"select * from stu;");*/
			/*if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK){*/
				/*printf("line:%d sqlite3_exec:%s\n",__LINE__,errmsg);*/
				/*return -1;*/
			/*}*/
			/*printf("select * from stu success\n");*/
			break;
			break;
		case '5':
			goto END;
			break;
		default:
			printf("输入有误，重新输入\n");

		}

	}

END:

	//释放数据库内存空间
	if(sqlite3_close(db) != SQLITE_OK){
		printf("line:%d sqlite3_close:%s\n",__LINE__,sqlite3_errmsg(db));
		return -1;
	}
	printf("sqlite3_close success\n");

	db = NULL;
	return 0;
}