网络编程day6-CSDN博客

本文链接：https://blog.csdn.net/2302_76485417/article/details/130374967

该代码示例展示了如何在C语言中通过SQLite3库进行数据库操作，包括向stu表中插入学生(id,name,score)记录，删除指定id的学生，以及更新学生的名字和分数。程序提供了一个简单的交互式菜单供用户选择不同的操作。

摘要由CSDN通过智能技术生成

数据库的增删改

#include<string.h>
#include<stdio.h>
#include<stdlib.h>
#include <sqlite3.h>
#include <stdlib.h>
int do_insert(sqlite3 *db)
{ 
	int id;
	char name[20]="";
	float score;
	printf("请输入要插入的数据\n");
	printf("id name score\n");
	scanf("%d %s %f",&id,name,&score);
	while(getchar()!=10);
	char sql[128]="";
	sprintf(sql,"insert into stu values(%d, '%s', %f);",id,name,score);
 
	char *errmsg=NULL;
	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
	{ 
		fprintf(stderr,"sqlite_exec:%s\n",errmsg);
		return -1;
	}
	printf("insert id:%d name:%s score:%.2f success\n",id,name,score);
	return 0;
 
}
int do_delete(sqlite3 *db)
{ 	
	int id;
	printf("请输入要删除的id数据\n");
	scanf("%d",&id);	
	while(getchar()!=10);
	char sql[128]="";
	sprintf(sql,"DELETE FROM stu WHERE id=%d;",id);
	char *errmsg=NULL;
	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
	{ 
		fprintf(stderr,"sqlite_exec:%s\n",errmsg);
		return -1;
	}
	printf("delete id:%d success\n",id);
	return 0;
 
}
int do_update(sqlite3 *db)
{ 
	int id;
	char name[20]="";
	float score;
	char message[20]="";
	printf("请输入你想修改的id：");
	scanf("%d",&id);
	while(getchar()!=10);
	printf("请输入该id要修改的名字和分数");
	scanf("%s %f",name,&score);
	while(getchar()!=10);
	char *errmsg=NULL;
	char sql[128]="";
	bzero(sql,sizeof(sql));
	sprintf(sql,"UPDATE stu SET name='%s' WHERE id=%d;",name,id);
	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
	{
		fprintf(stderr,"sqlite3_exec:%s\n",errmsg);
		return -1;
	}
	bzero(sql,sizeof(sql));
	sprintf(sql,"UPDATE stu SET score=%f WHERE id=%d;",score,id);
	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
	{
		fprintf(stderr,"sqlite3_exec:%s\n",errmsg);
		return -1;
	}
	printf("update id:%d name:%s score:%.2f success",id,name,score);
	return 0;
 
}
int main(int argc, const char *argv[])
{ 
	//创建并打开一个数据库
	sqlite3* db = NULL;
	if(sqlite3_open("./my.db", &db) != SQLITE_OK)
	{
		fprintf(stderr, "sqlite3_open :%s errcode:%d\n", sqlite3_errmsg(db),sqlite3_errcode(db));
		return -1;
	}
	printf("sqlite3_open success\n");
	//创建表格
	char sql[128] = "create table if not exists stu (id int, name char, score float);";
	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;
	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':
			do_insert(db);
			break;
		case '2':
			do_delete(db);
			break;
		case '3':
			do_update(db);
			break;
		case '4':
 
			break;
		case '5':
			goto END;
			break;
		default:
			printf("输入错误，请重新输入\n");
		}
	}
 
END:
	//释放数据库的内存空间
	if(sqlite3_close(db)!=SQLITE_OK)
	{
		fprintf(stderr, "sqlite3_close :%s errcode:%d\n", sqlite3_errmsg(db),sqlite3_errcode(db));
		return -1;
	}
	return 0;
}