2022.11.28---sqlite数据库

完成数据库的增删改查

#include <stdio.h>
#include <string.h>
#include <sqlite3.h>
#include <stdlib.h>
int insert_data(sqlite3 *db,char *msg);
int delete_data(sqlite3 *db,char *msg);
int change_data(sqlite3 *db,char *msg);
int main(int argc, const char *argv[])
{
	//打开数据库
	sqlite3 *db=NULL;
	if(sqlite3_open("./wang.db",&db)!=SQLITE_OK)
	{
		printf("%d--",__LINE__);
		printf("sqlite3_open:%s\n",sqlite3_errmsg(db));
		return -1;
	}
	printf("数据库成功打开\n");
	//在数据库中创建表
	char *sql="create table if not exists stu \
			   (id int primary key,name char,score float);";
	char *msg=NULL;
	if(sqlite3_exec(db,sql,NULL,NULL,&msg)!=SQLITE_OK)
	{
		printf("%d--sqlite3_exec:%s\n",__LINE__,msg);
		return -1;
	}
	printf("stu表创建成功\n");
	char ch=0;
	while(1)
	{
		printf("1---添加数据\n");
		printf("2---删除数据\n");
		printf("3---修改数据\n");
		printf("4---查找数据\n");
		printf("5---退出\n");
		printf("请输入:");
		ch=getchar();
		while(getchar()!=10);
		switch(ch)
		{
		case '1':insert_data(db,msg);break;
		case '2':delete_data(db,msg);break;
		case '3':change_data(db,msg);break;
//		case '4':find_data();break;
		case '5':goto END;
		default:printf("输入错误\n");
		}
		printf("请输入任意字符清屏:");
		ch=getchar();
		while(getchar()!=10);
		system("clear");
	}
END:
	//关闭数据库
	if(sqlite3_close(db)!=SQLITE_OK)
	{
		printf("%d--",__LINE__);
		printf("sqlite3_close:%s\n",sqlite3_errmsg(db));
		return -1;
	}
	printf("数据库成功关闭\n");
	return 0;
}
int insert_data(sqlite3 *db,char *msg)
{
	int id=0;
	char name[10]="";
	float score=0;
	printf("请输入id:");
	scanf("%d",&id);
	printf("请输入name:");
	scanf("%s",name);
	while(getchar()!=10);
	printf("请输入score:");
	scanf("%f",&score);
	char sql[128]="";
	sprintf(sql,"insert into stu values (%d,'%s',%f);",id,name,score);
	if(sqlite3_exec(db,sql,NULL,NULL,&msg)!=SQLITE_OK)
	{
		printf("%d--sqlite3_exec:%s\n",__LINE__,msg);
		return -1;
	}
	printf("数据添加成功\n");
	strcpy(sql,"select * from stu;");
	if(sqlite3_exec(db,sql,NULL,NULL,&msg)!=SQLITE_OK)
	{
		printf("%d--sqlite3_exec:%s\n",__LINE__,msg);
		return -1;
	}
	return 0;
}
int delete_data(sqlite3 *db,char *msg)
{
	int id=0;
	printf("请输入要删除数据的id:");
	scanf("%d",&id);
	char sql[128]="";
	sprintf(sql,"delete from stu where id=%d;",id);
	if(sqlite3_exec(db,sql,NULL,NULL,&msg)!=SQLITE_OK)
	{
		printf("%d--sqlite3_exec:%s\n",__LINE__,msg);
		return -1;
	}
	printf("数据删除成功\n");
	strcpy(sql,"select * from stu;");
	if(sqlite3_exec(db,sql,NULL,NULL,&msg)!=SQLITE_OK)
	{
		printf("%d--sqlite3_exec:%s\n",__LINE__,msg);
		return -1;
	}
	return 0;
}
int change_data(sqlite3 *db,char *msg)
{
	int id=0;
	char name[10]="";
	float score=0;
	char sql[128]="";
	printf("请输入要修改数据的id:");
	scanf("%d",&id);
	printf("1---修改name\n");
	printf("2---修改score\n");
	printf("请选择要修改的内容:");
	char ch=getchar();
	while(getchar()!=10);
	if(ch=='1')
	{
		printf("请输入修改后的name:");
		scanf("%s",name);
		while(getchar()!=10);
		sprintf(sql,"update stu set name='%s' where id=%d;",name,id);
		if(sqlite3_exec(db,sql,NULL,NULL,&msg)!=SQLITE_OK)
		{
			printf("%d--sqlite3_exec:%s\n",__LINE__,msg);
			return -1;
		}
	}
	else
	{
		printf("请输入修改后的score:");
		scanf("%f",&score);
		sprintf(sql,"update stu set score=%f where id=%d;",score,id);
		if(sqlite3_exec(db,sql,NULL,NULL,&msg)!=SQLITE_OK)
		{
			printf("%d--sqlite3_exec:%s\n",__LINE__,msg);
			return -1;
		}
	}
	printf("数据修改成功\n");
	strcpy(sql,"select * from stu;");
	if(sqlite3_exec(db,sql,NULL,NULL,&msg)!=SQLITE_OK)
	{
		printf("%d--sqlite3_exec:%s\n",__LINE__,msg);
		return -1;
	}
	return 0;
}