2022/11/28数据库的增删改查

#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>
int do_insert(sqlite3* db);
int do_delete(sqlite3* db);
int do_update(sqlite3* db);
int do_select(sqlite3* db);
int main(int argc, const char *argv[])
{
	//创建并打开数据库
	sqlite3 *db=NULL;
	if(sqlite3_open("./my.db",&db)!=SQLITE_OK)
	{
		printf("%s\n",sqlite3_errmsg(db));
		return -1;
	}
	printf("open success\n");
	//创建一张表格
	char sq[128]="create table if not exists word (id int primary key,word char);";
	char *errmsg;
	if(sqlite3_exec(db,sq,NULL,NULL,&errmsg)!=SQLITE_OK)
	{
		printf("exec=%s\n",errmsg);
		return -1;
	}
	char c;
	while(1)
	{
		system("clear");
		printf("***********1.增************\n");
		printf("***********2.删************\n");
		printf("***********3.改************\n");
		printf("***********4.查************\n");
		printf("***********5.退出***********\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':
			do_select(db);
			break;
		case '5':
			goto END;
		default:
			printf("输入错误，请重新输入\n");
			break;
		}
		printf("请输入任意字符清屏>>>\n");
		while(getchar()!=10);
		while(getchar()!=10);
	}
END:
	//关闭数据库
	if(sqlite3_close(db)!=SQLITE_OK)
	{
		printf("close:%s\n",sqlite3_errmsg(db));
		return -1;
	}	
	return 0;
}
int do_insert(sqlite3* db)
{
	printf("请输入序号，单词\n");
	int i;
	char word[128]="";
	scanf("%d,%s",&i,word);
	char acd[128]="";
	sprintf(acd,"%c%d%c%c%s%c%c",'(',i,',','"',word,'"',')');
	printf("%s\n",acd);
	char sq[128]="";
	sprintf(sq,"%s %s","insert into word values",acd);
	printf("%s\n",sq);
	char *errmsg;
	if(sqlite3_exec(db,sq,NULL,NULL,&errmsg)!=SQLITE_OK)
	{
		printf("zexec:%s\n",errmsg);
		return -1;
	}
}
int do_delete(sqlite3* db)
{
	int i;
	printf("请输入要删除的id号\n");
	scanf("%d",&i);
	char sq[128]="";
	sprintf(sq,"%s%d","delete from word where id=",i);
	char *errmsg;
	if(sqlite3_exec(db,sq,NULL,NULL,&errmsg)!=SQLITE_OK)
	{
		printf("sexec:%s\n",errmsg);
		return -1;
	}
}
int do_update(sqlite3* db)
{
	int i;
	char word[128]="";
	printf("请输入要修改的id号，修改之后的内容\n");
	scanf("%d,%s",&i,word);
	char sq[128]="";
	sprintf(sq,"%s%c%s%c %s %s%d","update word set word=",'"',word,'"',"where","id=",i);
	printf("%s\n",sq);
	char *errmsg;
	if(sqlite3_exec(db,sq,NULL,NULL,&errmsg)!=SQLITE_OK)
	{
		printf("sexec:%s\n",errmsg);
		return -1;
	}
}
int do_select(sqlite3* db)
{
	
}